Schema for Financial Database My company is starting a new initiative aimed at building a financial database from scratch.

We would be using it in these ways:

Time series analysis of: a company's financial data (ex: IBM's total fixed assets over time), aggregations (ex: total fixed assets for the materials sector over time), etc.
Single company snapshot: various data points of a single company
Analysis of multiple companies across multiple data fields for a single time frame, usually the current day.
Back-testing, rank analysis, data analysis, etc. of ideas and custom factors.

Approximate breadth of data:

3000 companies
3500 data fields (ex: total fixed assets, earnings, etc.)
500 aggregation levels

Periodicity: daily, monthly, quarterly, annual

20 year look-back that would grow over time

Question: In our PostgreSQL database, what schema should we use? Right now I am thinking one time series table per company, per category of data field for the fully normalized DB. For example, one table for, say, all the balance sheet fields for IBM, another table for IBM's cash flow items, etc for all categories of data and for each company. Timestamps as records and data fields as columns/fields. Then for fast queries, create a warehouse and views, etc. that are not fully normalized but optimized for queries for my use-cases listed above. However, if you look at my number of companies and fields above, I will probably end up with more than 200,000 tables for just my base financial data if my tables are pretty wide, which isn't great either. That's a lot of tables, but I don't see another good way to do it.

If there is a better place to ask this question, please let me know.

If you need more information, I am happy to edit my question and add it.

PS - I asked a similar question on the SO Quant site, but didn't get much schema help. Also, non-schema focused answers are okay, but note that I am looking for help with schema design here.

You can achieve this by creating 3 or 4 tables:

Company Information
Fundamental Entries
Fundamental Data
MarketCap Data (Optional)

Company information will contain information about all the companies, assign a Company ID to each company, which would be later used in to join the financial fields. Use some SCD type( slowly changing dimensions) based on your requirements, fields like Ticker changes often. So carefully assign a company ID.

Unpivot and divide your financial data between Entries and values.

In the Fundamental Entries table generate a unique ID (eg ID_FS_ENT) for each company ID and each financial entry (that would be a combination of Period End, Accounting Standard, Fiscal Period, etc.)

In the financial Data table store the financial value along with the ID_FS_ENT, Field Name (that would be shares outstanding, total fixed assets, earnings etc)

If you are also receiving daily market cap for each company then store it separately. Again use the Company ID, add trading date and value to this table.

Hope this helps!

P.S. I am already managing a similar database with more than 100,000 companies for more that 78 economies and around 100 million financial entries.


Postgres full text search on words, not lexemes I've got a table with columns of text that I'd like to search by word, not lexeme. More to the point, that I'd like to have indexed by word rather than lexeme. We've got error dumps with a lot of code references, which don't work well with any natural language dictionary.

Is there a way in Postgres to have FTS parse by word boundaries without resolving words to lexemes? If I have to define a list of boundary characters and a catalog of skip words, that could be fine. Does this require crafting some kind of custom dictionary, or is there something already like this available?

I keep thinking I'm missing something obvious, and then not finding it.

For the minute, trigram indexes are okay, but I would really prefer what amounts to a unique keyword parser for the text.

Postgres 11.4 on RDS.

Just use the simple text search configuration, which works with the simple dictionary and does not do any stemming:

CREATE INDEX ON tbl USING GIN (to_tsvector('simple', data));

And query accordingly:

SELECT *
FROM   tbl
WHERE  to_tsvector('simple', data) @@ to_tsquery('simple', 'foo');

Related:

Get partial match from GIN indexed TSVECTOR column
Finding the most commonly used non-stop words in a column
PostgreSQL full text search on many columns


Database reporting alerts I am about to make a reporting/alerting mechanism that will periodically examine state of one of my databases that is constantly updated and send reports/fire alerts if needed depending on the data in the tables.

I am not familiar with MS BI stack. I played a bit with reporting services but as far as I can see it can only send periodic reports. I need some quite moderately simple logic before sending a report that will decide whether the report needs to be sent at all.

Should I go with custom external solution, some core SQL Server stuff (triggers + CLR support?) or there is an existing tool in BI stack that will suit my needs?

Depends on how sophisticated you want your reports to be.

The simple solution is to create SQL stored procedures that generate emails with reports as embedded/attached HTML or as attached csv files.

The stored procdure can be called from SQL Agent using the flexible scheduling and error reporting that is already built in.


Backup MySQL databases to external hard drive then restore I have some issues with my new ASUS laptop. I'm running on Windows 10 and the helpdesk told me that they will reset the computer with it's original OS (Windows 8.1).

I run PHP 5.6.22 over fast CGI and PHP 7.0 over fast CGI with MySQL 5.7 on my local machine.

IIS : 10.0.10586.0

I use phpmyadmin 4.6.2

Does anybody have an idea how to export my databases to an external HDD and recover them after on my computer after a possible format of my PC?

Exporting them through phpmyadmin is really long because I have some large databases.

If someone have an idea or some links to share I will really appreciate your help.

I just use my databases, but I don't know anything about this kind of issue.
I've some innoDB DB, but I'm not sure about all the DB types.

I think that copy and paste my db's folder will not help...am I wrong?

Use mysqldump from the command line.

For example, to dump all your databases to a file, use the --all-databases switch and a result file for output:

mysqldump [options] --all-databases --result-file=dump.sql

If the DBs are big, you can split them out into their own files. The dump command is flexible like that.

(Ref: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html)

To restore, all you have to do is run the dump file as a script, which you can also do from the command line.


Exclude rows with consecutive data that may repeat I have a table showing statuses (0 - broken, 1 - live) for a particular device at a given timestamp:

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0    -- leave this
owner1         device_1     2001-01-01 09:05   0    -- exclude
owner1         device_1     2001-01-01 09:10   0    -- exclude
owner1         device_2     2001-01-01 09:15   0    -- leave this
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:35   0    -- exclude
owner1         device_2     2001-01-01 09:45   1
owner1         device_1     2001-01-01 09:55   0    --leave this
owner1         device_1     2001-01-01 10:10   0    --exclude
owner1         device_1     2001-01-01 10:11   1

and so on.

I need to exclude consecutive zero statuses, and leave the one with earliest datetime. (So that I could define datetime difference between the device broken and live.)

The result:

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0
owner1         device_2     2001-01-01 09:15   0
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:45   1
owner1         device_1     2001-01-01 09:55   0
owner1         device_1     2001-01-01 10:11   1

and so on.

Then in my further calculations, total breakdown for device_1 would be between 09:00 and 09:30, 09:55 and 10:11, for device_2 – 09:15—09:45.

DDL:

DECLARE @t TABLE
      (
          device_owner VARCHAR(10),
          device_id VARCHAR(10),
          timestamp DATETIME,
          status BIT
      );

INSERT @t ( device_owner, device_id, timestamp, status )
SELECT *
FROM
(
  VALUES ('owner1', 'device_1', '2001-01-01 09:00',   0),    -- leave this
         ('owner1', 'device_1', '2001-01-01 09:05',   0),    -- exclude
         ('owner1', 'device_1', '2001-01-01 09:10',   0),    -- exclude
         ('owner1', 'device_2', '2001-01-01 09:15',   0),    -- leave this
         ('owner1', 'device_1', '2001-01-01 09:30',   1),
         ('owner1', 'device_2', '2001-01-01 09:35',   0),    -- exclude
         ('owner1', 'device_2', '2001-01-01 09:45',   1),
         ('owner1', 'device_1', '2001-01-01 09:55',   0),    --leave this
         ('owner1', 'device_1', '2001-01-01 10:10',   0),    --exclude
         ('owner1', 'device_1', '2001-01-01 10:11',   1)
) AS x ( device_owner, device_id, timestamp, status );

I'd appreciate any advice.

This seems to be similar to a solution from Itzik Ben-Gan to gaps and islands

SELECT [device_owner], [device_id],MIN(timestamp) as timestamp,status

FROM
(
SELECT *
   ,diff = ROW_NUMBER()OVER(PARTITION BY device_owner,device_id ORDER BY timestamp ASC)
         - ROW_NUMBER()OVER(PARTITION BY device_owner,device_id , status ORDER BY timestamp ASC)
FROM @t
)A
--WhERE status = 0
GROUP BY [device_owner], [device_id],status , diff
ORDER BY timestamp

output of  it:

device_owner    device_id   timestamp           status
owner1          device_1    01/01/2001 09:00:00 False
owner1          device_2    01/01/2001 09:15:00 False
owner1          device_1    01/01/2001 09:30:00 True
owner1          device_2    01/01/2001 09:45:00 True
owner1          device_1    01/01/2001 09:55:00 False
owner1          device_1    01/01/2001 10:11:00 True

dbfiddle


Define log shipping file locations when using AWS for the secondary server We are in the process of using an instance within AWS to act as our secondary server.  This instance will act as the secondary server in our log shipping process.

How would the "drop" (written by primary) and "pickup" (read by secondary) locations be defined if they are within one of the AWS S3 buckets?  Or is there some other place to put them instead of the buckets?

My preferred solution here is to write the backups to a file share managed by a separate Windows file server (likely an EC2 VM). This way, if your SQL Server goes down, the backups are instantly already accessible on that EC2 VM (as opposed to doing backups locally on the SQL Server, which are inaccessible when the SQL Server goes down.)

Then, on that EC2 VM file server, use an S3 folder sync tool that automatically uploads your local backup folder up to an S3 folder.

On the subscriber side (which can be in a different AZ or region), use another EC2 VM file server with the same cloud file sync tool, which then makes the files accessible via UNC path for your subscribing SQL Servers.

Use the native log shipping jobs, but don't use the file-copy job (since that's handled by the two EC2 VMs syncing to S3.) The two log shipping jobs will have different targets, and that's fine.


SQL Server default collation vs database with different collation - potential problems? I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems.

Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on columns.

What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation? What about when I use REPLACE(...) COLLATE Y? Do you see any potential data loss or maybe any different problems?

... new DB that has to be in a different collation (Y Collation) than the SQL Server Instance default collation (X Collation).

Why does the new database need to be in a different collation?

  Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on cols.

Correct. Though you do not need to use COLLATE DATABASE_DEFAULT, and in some cases that can be entirely incorrect / bad. The requirement is that either A) the collation is the same for string columns being joined, or used in concatenation / UNION, etc, or B) you specify the COLLATE clause in the predicate or expression to override one or more of the columns.

Meaning, if you know that your column has a collation of Z and your database has a default collation of Y, then create the temp table specifying COLLATE Z for that column. Keep in mind that table variables use the database's default collation, not the instance's collation. And, contained databases are yet another set of rules.

  What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation?

Built-in functions usually return a string in the database's default collation, unless you pass in a column or an expression using the COLLATE clause.

  do you see any potential data loss?

It depends on what datatype(s) you are using and what collations you are using. NVARCHAR / NCHAR / NTEXT / literals prefixed with an uppercase "N" are Unicode-only (UTF-16) and so there is no potential data loss if switching collations on those. VARCHAR / CHAR / TEXT / literals not prefixed with an uppercase "N" are 8-bit encodings, the particular encoding being determined by the code page association with the collation being used. Here there is potential data loss if switching collations to one that uses a different code page. But even that is not a guarantee of data loss since data loss only occurs when there are characters in the data that are not available in the new code page. (technically speaking, you can also experience data loss when mixing various combinations of single-byte character sets, double-byte character sets, UTF-8, and UTF-16, depending on which direction you are going in, but this is not something most people run into -- though more people likely will as more folks start using the new UTF-8 collations starting in SQL Server 2019)

  or maybe any different problems?

Just keep in mind that:

Instance-level collation controls:

variable name / cursor name / GOTO label resolution. It won't matter what the database-level collation is.
instance-level metadata: login names, server names, server role names, server-level objects, database names, etc.
default collation for:

newly created databases (unless the CREATE DATABASE statement included the COLLATE clause)
tempdb metadata (names of temp object / constraints / indexes / defaults / permanent tables / etc)
columns in temp tables and permanent tables in tempdb

Database-level collation controls:

database-level metadata: user names, schema names, database role names, schema-bound object names, etc.
default collation for:

columns in table variables, permanent tables, TVF results

string literals
string variables (the contents of the variable, not the name)
return value of scalar UDFs

For a more thorough list of what collations do at the various levels and what to look out for when changing them, please see the following post of mine:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?


Performance of a self join in a temporal table with a single day's difference between end_date and start_date Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an employee_id as well. And there are gaps in the sequence. The interval can vary.

The clustered index is on employee_id, start_date.
There is an unclustered index on employee_id, end_date that isn't used by the query below.

The current join from the current record to the previous contiguous record is (simplified):

SELECT *
FROM self AS current
LEFT OUTER JOIN self AS previous
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date

There is a Seek Predicate on the employee_id. The DATEADD() is nonSARGable and stops the end_date index being used, so the join from start_date to end_date is a non-indexed Predicate.

Is there a way to get the start_date to end_date join to use an index? Either by clever indexing, a view or by rewriting the query?

I'm still on SQL Server 2008 R2, but please feel free to use the latest version in the answers.

SELECT *
FROM self AS current
LEFT OUTER JOIN self AS previous
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date

and previous.start_date <> current.start_date

or if has to be less

and previous.start_date <  current.start_date

or if can isolate on prior

and previous.start_date = LAG (current.start_date, 1, 0) OVER (PARTITION BY current.employee_id ORDER BY current.start_date DESC)


Extremly large import taking over 24hrs We have a query that we run to import a Master Do Not Call List into our database with about 200 million records.

We first mark the existing numbers in the database table with an expiration date.  This process takes about 19hrs or more, and eats up the log files, and tempdb log files.

The second part of the query is the import from a text file located on the c: drive.  This part also takes quite sometime, and last time SQL Studio crashed so the query tanked and did not finish.

Below is the query that we run:

To give some background on the server setup:
Windows 2016 standard
intel xeon 8cores 2.59ghz
128gb ram
sql 2016 standard

Data volume located on 3par 8200 SAN spinning drives.

Log volume located on 3par 8200 SAN spinning drives (current size is now 1.46tb because 3 times we ran out of space)

Temdb data and log volumes located on 3par SAN as well. (one volume)

Is there a better way to run this query and make it more efficient?  Should we create a job with two steps one for the update expiration, and second for bluk insert? Should we move data and log volumes to SSDs on the 3par...currently they are located on spinning drives.

What do you think we can do to make the process more efficient?

Any information would be greatly appreciated!
Thanks!

The first problem I see is that you are performing a massive update (19+hours in duration) in a single run, and that is why your server is running low in resources. You need to perform the updates in batch. They will be faster, and your server setup should be able to cope with the resource load.

Try the following:

DECLARE @Rows INT,
        @BatchSize INT;

SET @BatchSize = 2000;

SET @Rows = @BatchSize;

WHILE (@Rows = @BatchSize)
  BEGIN
      UPDATE TOP (@BatchSize) tab
      SET    tab.expiration = dateadd(day, 5, getdate())
      FROM  I3dialer.dbo.MasterDNC tab
      WHERE tab.expiration < dateadd(day, 5, getdate());
      SET @Rows = @@ROWCOUNT;
  END;

Before testing this, be sure that there's an index in the expiration field to accelerate the query.

For the second part of your problem, other than increasing the batch insert to say, 10000 should help a bit. You can actually experiment with this to find your optimal batch size, for example, testing against 1 million records to see if there's any performance difference. There are also other things to consider. You should first load the data into an empty table, perform whatever operations you need to clean up your data, and finally insert them into your main table.

In summary, you should now have a 3 part process:

Perform the batch update on your main table
Insert data into a temporal or stage empty table
Insert data from your stage table to your main table

You should definitively get a better performance after implementing this. Do share your results.


MySQL Linux and Windows entity changes I have a trigger on an entity that copies the row and saves it in a different entity.

I also have 2 MySQL servers one on Linux and the other on Windows. It seems the order of the columns in the same table are different between the servers. Is there a way to make them the same order? Some configuration?

I'm creating an audit table using this script:

CREATE TABLE xxx.AuditEntity (
  auditAction VARCHAR(45) NULL DEFAULT NULL COMMENT '',
  auditTime DATETIME NULL DEFAULT NULL COMMENT '',
  type VARCHAR(64)  NULL COMMENT '',
  id BIGINT(20) NOT NULL COMMENT '',
  modifiedBy BIGINT(20) NULL DEFAULT NULL COMMENT '',
  timeCreated DATETIME  NULL COMMENT '',
  currency VARCHAR(32)  NULL COMMENT '',
  nameVARCHAR(128) NULL DEFAULT NULL COMMENT '',
   INDEX auditTime (auditTime ASC)  COMMENT '',
  INDEX id (id ASC)  COMMENT '');

The trigger on the original entity:

CREATE DEFINER=`root`@`localhost`
TRIGGER xxx.entity_AINS AFTER INSERT ON Entity FOR EACH ROW
BEGIN INSERT INTO AuditEntity select 'AFTER_INSERT', now(), e.*
    from Entity e where id = NEW.id;
END

Because the columns in the 2 servers have a different order it doesn't work.

Our idea is not to explicitly write out the columns' names, is there a difference between how Linux and Windows save entities?

This is part of the table definition, you'll need to do something similar to this:

ALTER TABLE Table1 MODIFY COLUMN Column1 INT AFTER Column2;

This will move Column1 after Column2.

Alter Table Syntax


MySQL Trigger Query Not Deleting As Expected I have a test database as shown below. If date is updated in tbl1, I would like for a trigger to fire that removes all entries from tbl2 where tbl1.host = tbl2.host2 AND tbl1.date = NEW.date.

This is as close as I can get but it doesn't work. Thank you kindly in advance!

DELETE tbl2.* FROM tbl1,tbl2
WHERE (tbl1.host = tbl2.host
AND date = NEW.date)

SAMPLE DATA

CREATE TABLE IF NOT EXISTS tbl1
(
  host varchar(10) NOT NULL,
  package1 varchar(10) NOT NULL,
  date datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$
CREATE TRIGGER `Before_Insert` BEFORE INSERT ON `tbl1`
FOR EACH ROW DELETE tbl2.* FROM tbl1,tbl2
WHERE (tbl1.host = tbl2.host AND date = NEW.date)
-- Should be tbl2.host2 (no host field in tbl2) EDIT.
$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL,
  `host2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`, `host2`) VALUES
(1, 'a', '1'),
(2, 'b', '2'),
(3, 'c', '3'),
(4, 'd', '4');

ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

What you want is (something like):

DELIMITER $$
CREATE TRIGGER `Before_Insert_tbl1` BEFORE INSERT ON `tbl1`
FOR EACH ROW
BEGIN
  DELETE FROM tbl2
  WHERE (NEW.host = tbl2.host2);  -- note not tbl1.host - out of scope!
  -- AND date = NEW.date)         -- not sure what the date logic is here.  AFAICS
                                  -- it's superfluous. Could be NEW.date = DATE(NOW());?
                                  -- i.e. delete it if it's today's record, otherwise don't?
END $$
DELIMITER ;

Your problem was having tbl1.host inside the WHERE clause where it's out of scope. The NEW refers to the new tbl1 record - no need for a table reference.

Also, as I note, the 'date' is confusing - it  does nothing - tbl1.date (of the record being inserted) will automatically equal NEW.date. Finally, you appear to have made an error in your TRIGGER - there's no tbl2.host (should be "host2"?). I've edited it to make it easier for anyone else should my logic not be flawless :-).


'accidentally' set 'CHANGE MASTER TO' on wrong server while setting up the replication I accidentally did the 'CHANGE MASTER TO' on the master server instead the slave.

When I noticed the mistake I just did a 'STOP SLAVE' on the master and setup the slave then correctly, replication is working.

I am just wondering if there is a need/way to remove this 'change master to' on the master server again, I havent found anything in the Documentation and 'reset master' seems to be something else.

I can't see any issues currently, so maybe I don't need to bother about it. If possible I'd stil; like to know how to correct it.

Thanks

In earlier versions of MySQL, you would run this on the Master

STOP SLAVE;
CHANGE MASTER TO master_host = '';

and restart mysqld.

For MySQL 5.5/5.6, you would run this on the Master

STOP SLAVE;
RESET SLAVE ALL;

MySQL restart not required

For more details on this, please read Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. RESET SLAVE ALL) from PERCONA MYSQL PERFORMANCE BLOG


How to obtain all distinct items but limit each distinct to max n and ordered by date? I have a table where I have uuid as PK, command_name string, count_successful integer and command_start date.

I can grab all records by:

select
    uuid,
    command_name,
    count_successful,
    command_start
from
    sync_logs
order by
    command_start desc;

But above query will give me for example 78 records where command_name equals to foo and 45 records with command_name equals to bar.

How can I get max of 10 recent records per command_name?

Apparently below query solves the issue and gives me recent 10 records for each command_name ordered by command name and when it started.

select
    s1.*
from
    sync_logs as s1
left join
    sync_logs as s2
on
    s1.command_name = s2.command_name and
    s1.command_start <= s2.command_start
group by
    s1.uuid
having
    count(*) <= 10
order by
    command_name asc,
    command_start desc;


How to retrieve a view column derivation in PostgreSQL or Greenplum I want to retrieve the derivation expression of a column in a view, and I want to do it dynamically from within a stored procedure, so psql \d+ or pgAdmin is no use to me.

Where can I retrieve this information from? The full text is in pg_views but I'd rather not get into slicing this string up to try and find each of the column expressions.

From the docs

  Views in PostgreSQL are implemented using the rule system.

So essentially, they get rewritten to simply SELECT statemetns. Nothing in the catalog stores the column expression. Internally, when you run \d on a view, it calls pg_catalog.pg_get_viewdef. You can't go further from that with the system catalog. You're going to have to parse that query to get to the column expression, perhaps with something like libpg_query.


Analyze table in procedure I have many partitions to analyze and don't want to paste every single line to terminal so I'm trying to run a procedure for it, but I got complitaion error:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    END;
    /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANALYZE_TABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         ( ; is with default authid as cluster compress order using
         compiled wrapped external deterministic parallel_enable
         pipelined result_cache accessible rewrite

What is reason for that?

You can not place DDL commands directly in PL/SQL code. You can run them as dynamic SQL, for example:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    END;
    /


How to read metadata from Sqlite database Sometimes I use SQLite database but it's quite common that I don't know anything about its structure.
I need to retrieve all metadata like the name of a database or table, the data type of a column, primary keys, foreign keys, or access privileges.
MSSQL has information_schema for this purpose but is there something similar for SQLite?

The equivalent of, for instance, MySQL describe t1; is

PRAGMA TABLE_INFO(t1);

and

SELECT * FROM sqlite_master WHERE tbl_name = 't1';

is also useful. Most of the time, however, I simply use

.schema t1

which outputs the SQL definition of the table or view, even with the original comments (exactly the same as SELECT sql FROM sqlite_master WHERE..., with less typing. .sch works too.)


How to remove the second repetition of combination in mySQL I have a datable like above and in that, unfortunately, repetitions occurs. How can I remove the repeated combination of 2nd, 3rd and 4th column and I need to make those three columns unique in combination.

Please help to write a query to do this, because there are 13000+ rows, so manual editing is hardly not possible.

Thanks in advance!

Try this

DELETE n1
    FROM tablename n1, tablename n2
    WHERE n1.id > n2.id
      AND n1.exam = n2.exam
      AND n1.name = n2.name
      AND n1.subject = n2.subject


Why is tnsping not reliable from ports 20, 21, 22? I think it's not reliable because those ports are used for FTP, SSH and SFTP but I am not sure that's the correct response in respect for Oracle DBA

Read this carefully: Using the TNSPING Utility to Test Connectivity from the Client

Take a look at the Stack Overflow answer: When using tnsping, can I specify a port number along with the SID?

You can specify destination port in tnsnames.ora or I think you can use EZCONNECT syntax.


Why is my newly installed but isolated SQL Server 2016 instance filling disk with transaction log This is an entirely new install of SQL server of an entirely new server. The setup is intended to replace another ageing server and we are in the process of setting up the server environment.

Last Thursday we installed SQL Server and restored the clients database, but there are no [nil, none, not any, zero] client users, management jobs, or scheduled processes configured.  Effectively the system is isolated from the world living in its own universe.

On Monday I got a shout from the systems team telling me an out-of-space alert had been raised from the transaction log drive and that it was nearing the top of its 80Gb capacity.

80Gb of transaction log seems like a lot for an untouched SQL database. In a live environment we will have management jobs running to backup the trans log every 15 mins so we will not see the issue.

Obviously something is running to cause SQL to write so much to the trans log. I am curious as to what it might be. Apart from anything else I always assumed that it is user activity and occasional DBA work that causes the translog to grow but it would seem that some part of the growth is due to something else.

Can anyone offer any insight, or suggest how I can analyse the transaction log contents to get a sense of what is happening?

UPDATE: Couple of updates from when I first wrote the question.

Why can't I examine the translog file - it had to be killed to get the space back - not my decision.
There is a VEEEM backup solution in play here. It turns out that the login credentials for VEEEM to access SQL server were not enabled with SQL Server access. The event log shows the VEEEM attempting to log in 4 times them abandoning. The disk space usage graph shows an almost exponential consumption of disk space.

Sounds like there may be a maintenance job running intermittently in the background which is filling your log. Do you have access to the job system to look? On non-production systems you'll also want to put the database in simple recovery mode in most circumstances.


Oracle tables partition related Here is my doubt. Is it possible to apply a partition to one table depending in the partitions values of another one?

Why I am asking?
I have one table where information headers are persisted, and other table where information details are stored.
As you can imagine, the second one is much bigger than the first one. The one containing headers, has an automatic partition by date, so I guess it would be desirable to partition the one with the details in order to perform queries.
Here the example:

  HEADER_TABLE ID    DATE
  ----------------------------- 1   10/10/2010 2   10/10/2010 Partition 1 3   10/10/2010
  ----------------------------- 4   11/10/2010 5   11/10/2010 Partition 2
  -----------------------------

  DETAIL_TABLE ID  HEADER_FK
  ----------------------------- 01     1 02     1 03     2      Partition 1 04     2 05     3 06     3
  ----------------------------- 07     4 08     4      Partition 2 09     5 10     5
  -----------------------------

One way I think this could be done, is adding the date field to the details table, and to partition it in the same way than the header one, but the thing is that altering the details table in production and updating the rows (hundred millons) could be a problem.

Any idea?

You can use partition by reference option
Example :

create table trans (
  trans_id     number not null,
  res_id        number not null,
  trans_date  date not null,
  amt           number,
  constraint fk_trans_01
    foreign key (res_id)
    references res
)
partition by reference
   (fk_trans_01);

Some restricions :

Parent table Primary Key and Unique constraints used for  reference
partitioning must be enabled and non-deferrable.
Child table “partitioning constraints” must be enabled and
non-deferrable
All child table columns used in “partitioning constraints” must  be
defined as NOT NULL.
This is required to assure that every child row maps to exactly one  parent row.
Child tables created using reference partitioning can never  be
disassociated from their parents without being dropped. – There is no
ALTER TABLE command to convert a reference  partitioned table into a
non-reference partitioned table.
As with other tables referenced via foreign key constraints,
parent tables cannot be dropped until all foreign key  constraints
referencing them are removed.  – In the case of reference
partitioning, this means all descendent  tables must first be
dropped, since the partitioning constraints on  the child tables
cannot be disabled or dropped.

NOTE:

Reference Partitioning has strong advantages and strong limitations,
so its use requires careful planning.

Reference Partitioning should not simply be used wherever possible.
Use of Reference Partitioning appears best suited for very large
tables  and related tables, whose contents share the same life cycle.


Count() versus keeping a column with a total count Possible Duplicate:
  Storing vs calculating aggregate values

Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos in each album, I could simply do a Count() on the photo table where the albumId = the id from the album table. However, to accomplish this same task, I could keep a photoCount column in the album table. Is it worth keeping that extra column in the album table instead of using the Count() function?

You can also consider materialized views (not all RDMS support this feature though)


How to change the path of temp file,redo log file when restore an rman backup on another machine? I am trying to migrate my database (11.2.0.4) A to another machine by using rman backup. At present, in the source machine, the redo log file, temp file is stored under the directory foo (/foo). In the target machine, I would like to store these files under the directory bar (/bar).
In the past practice, what I did is to create foo for these files and move these files to bar manually after the recovery is complete. RMAN is such a powerful tool that I think there must be some an easy way to satisfy my requirement.
So far, by searching the google, I got a useful rman command for temp file:

set newname for tempfile <fileno> to '<path>'

It seems that there is no similar command for redo file.
Before using this command to perform a recovery, I have several questions to ask:

When we take an full backup, the backup did not go with temp tablespace and redo files, but in the past migration I experienced, I found that when I open (alter database open resetlogs)the database after recovery is complete, redo log files and temp files exist. Will rman create this automatically for database? I can`t find the reliable oracle documents for this topic.
To change the path for redo is so boring, I would the finish this task in the rman scripts, is there any command available to rename the redo log file in the rman scripts?

You can either use a pfile and set the following in the pfile, or you can use an spfile and do a startup nomount to set the following variables. You can have as many directories as you want the pattern will still be: '','','','','',''...

alter system set db_file_name_convert='/foo/','/bar/' scope=spfile;
alter system set log_file_name_convert='/foo/','/bar/' scope=spfile;

You can also specify the redo on the clone/restore command.

DUPLICATE DATABASE TO mytest BACKUP LOCATION '/mybackup/mytest'
LOGFILE
       GROUP 1 ('/mybackup/mytest/redo01a.log',  '/mybackup/mytest/redo01b.log') SIZE 256M REUSE,
       GROUP 2 ('/mybackup/mytest/redo02a.log',  '/mybackup/mytest/redo02b.log') SIZE 256M REUSE,
       GROUP 3 ('/mybackup/mytest/redo03a.log',  '/mybackup/mytest/redo03b.log') SIZE 256M REUSE
NOFILENAMECHECK;


SQL Server "Max Worker Threads" Setting I am helping troubleshoot some problems on a SQL Server box.  An application that used to run without problem is now failing with connection pool errors, and no changes have been made to the application that could cause this new problem.

In investigating possible causes of the connection pool error, I came across this post, which gave the "max worker thread" setting as a possible cause.  I checked the production server, and this setting was 54 (it was 89 in dev and 79 in QA).

Is there any way of seeing a log/history of when this setting was changed?
Is there any reason to have the setting this low (this MSDN article recommends a minimum setting of 256 for 32-bit processors, 512 for 64-bit)?
Does the low setting generally seem odd, since someone would have had to consciously set it?

There is no log of when this was changed.  By default, and for 99.9% of systems this should be set to the default value of 0 so that SQL Server can manage this by itself.  Set it back to 0, restart the SQL Server instance and be done with it.


Any way around unique index 16 column max According to the CREATE INDEX documentation:

  Up to 16 columns can be combined into a single composite index key.

We've got a table with ~18 columns that need to form a unique combination. This table is not performance sensitive -- we rarely update values/insert records. We just need to ensure that we avoid duplicating our records... and thought we could impose a simple uniqueness constraint.

Any ideas? I'm open to avoiding the unique index/constraint entirely if there is a better way.

Add a persisted computed column that combines the 18 keys, then create an unique index on the computed column:

alter table t add all_keys as c1+c2+c3+...+c18 persisted;
create unique index i18 on t (all_keys);

See Creating Indexes on Computed Columns.

Another approach is to create an indexed view:

create view v
with schemabinding
as select c1+c2+c3+...+c18 as all_keys
from dbo.t;

create unique clustered index c18 on v(all_keys);

See Creating Indexed Views.

Both approaches allow for a partial key aggregate: aggregate c1+c2+c3 as k1, c4+c5+c6 as k2 etc. then index/create indexed view on (k1, k2, ...). Thia could be beneficial for range scans (index can be used for search on c1+c2+c3.

Of course, all + operation in my example are string aggregation, the actual operator to use depends on the types of all those columns (ie. you may have to use explicit casts).

PS. As unique constraints are enforced by an unique index, any restriction on unique indexes will apply to unique constraints as well:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    constraint unq unique
      (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18));
go

Msg 1904, Level 16, State 1, Line 3
The index '' on table 't' has 18 column names in index key list.
The maximum limit for index or statistics key column list is 16.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, creating the constraint on a persisted computed column works:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    all_c as
        c1+c2+c3+c4+c5+c6+c7+c8+c9+c10+c11+
        c12+c13+c14+c15+c16+c17+c18
        persisted
        constraint unq unique (all_c));
go

Obviously, the persisted column consumes the space on disk so the approach may be bad for a very large table. The indexed view approach does not have this problem, it only consumes the space for the index, not the space for the computed column and index.


oid vs bytea in postgres I have a table called tblA and its having two columns

id_ (numeric 50,0), obj_ oid

For example, tblA having 1 entries

id_  | obj_
1    | 1001

In pg_largeobject table, there are three entries against loid 1001 (3 page nos) which is fine. But, pg_largeobject table is having three columns (

loid  | pageno    | data
(oid)   (integer)   (bytea)

I read about oids and bytea, both are different data types and being used in that way. But I am confused about pg_largeobject table structure, why its using both data types for same data?

Is postgres internally storing oids as bytea, and just returning oids for reference? If its working in this manner, why can't use just numeric type for reference instead of oid type.

If not, what is the use of oid here and is any data sitting behind the oid somewhere in the database, and what is the bytea column data about.?

oid as a type is a 32-bit unsigned integer that stands for object id, and is used by the system as an all-purpose surrogate key to refer to different objects and sometimes rows in pg_catalog. Being the type for keys to large objects  is just one of its many uses. Other "objects" like tables, sequences, types and more are refered to by their oid.

  But I am confused about pg_largeobject table structure, why its using
  both data types for same data?
  Is postgres internally storing oids as bytea, and just returning oids
  for reference? If its working in this manner, why can't use just
  numeric type for reference instead of oid type.

It stores the contents of large objects as small bytea pages (~2000 bytes)in the pg_largeobject table, with loid as the ID or the large object, and (loid,pageno) as the unique key for that table.
The generator of new values for loid can be compared to a integer sequence, except that after wrapping around at 2^32, it can avoid conflicts with existing values. As users, we don't have to care about this, it's managed by the internals.

  If not, what is the use of oid here and is any data sitting behind the
  oid somewhere in the database, and what is the bytea column data
  about.?

The oid is used as a handle and the bytea column holds the actual binary data.


mySQL 5.1 changed innodb_log_file_size getting error on restart In order to optimise my MySQL 5.1 (running on Window 2008 WebServer). I changed a few InnoDB options in my.ini namely the innodb_buffer_pool_size and innodb_log_file_size. But now when I am trying to restart the service it won't start. In the event viewer I can see the following errors:

Unknown/unsupported table type: INNODB

I have read about deleting log files and then starting the service but I have no clue where those log files are on windows machine. Also what is the best way to recover from this trauma without loosing any table data ?

Yup, you'll need to remove the log files to change their sizes.  They're in where ever your DATADIR is.  Just make sure you remove them when the server is shutdown.  Then restart and you'll be fine (it will recreate them).

DO NOT remove any ibdata files, though.  That has your actual persistent data.


Postgresql large transaction OOM killer We have a Postgresql 9.5 running on CentOS release 6.5 (Final)
The server has 16GB of ram.
We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides this transaction no query is running on the DB in this stage but it can be run during other queries so it must be transactioned.

It works fine until a point where the OOM killer decides it's enough and kills the postmaster process:

Out of memory: Kill process 1766 (postmaster) score 890 or sacrifice child
Killed process 1766, UID 26, (postmaster) total-vm:24384508kB, anon-rss:14376288kB, file-rss:138616kB

Here are the relevant postgres configurations:

#-------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#-------------------------------------------------------------------------

# - Memory -

shared_buffers = 128MB                  # min 128kB
                                        # (change requires restart)
#huge_pages = try                       # on, off, or try
                                        # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB                  # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
                                        # supported by the operating system:
                                        #   posix
                                        #   sysv
                                        #   windows
                                        #   mmap
                                        # use none to disable dynamic shared memory

# - Disk -

#temp_file_limit = -1                   # limits per-session temp file space
                                        # in kB, or -1 for no limit

# - Kernel Resource Usage -

#max_files_per_process = 1000           # min 25
                                        # (change requires restart)
#shared_preload_libraries = ''          # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0                  # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1           # 1-1000; 0 disables prefetching
#max_worker_processes = 8

Also the vm.overcommit_memory is set to 2

One solution I found is to disable the OOM killer for the specific process which I'm trying to avoid since it feels to me like not the correct solution.
I didn't find any way to force postgres to flush the memory to disk or to limit the total amount of memory used.

Any suggestions?

Ok,
So after trying a lot of things, I found the solution.

The issue was hiding in our use of the JDBC library.

We had a long connection with auto commit set to false:

connection.setAutoCommit(false)

During that time we were doing a lot of small queries and a few queries with a cursor:

statement.setFetchSize(SOME_FETCH_SIZE)

In JDBC you create a connection object, and from that connection you create statements. When you execute the statments you get a result set.

Now, every one of these objects needs to be closed, but if you close statement, the entry set is closed, and if you close the connection all the statements are closed and their result sets.

We were used to short living queries with connections of their own so we never closed statements assuming the connection will handle the things once it is closed.

The problem was now with this long transaction (~24 hours) which never closed the connection. The statements were never closed.
Apparently, the statement object holds resources both on the server that runs the code and on the PostgreSQL database.

My best guess to what resources are left in the DB is the things related to the cursor. The statements that used the cursor were never closed, so the result set they returned never closed as well. This meant the database didn't free the relevant cursor resources in the DB, and since it was over a huge table it took a lot of RAM.

The only thing I don't understand is why PostgreSQL didn't save some of the resources on the DISK (not swap) when it was failing to allocate memory.

Hope it will help some one in the future with similar problem.


MySQL equivalent of PostgreSQL's psql \c command I'm learning PostgreSQL though oddly enough figured out to switch users while in the psql command line to use \c database username; what is the equivalent to switch from one user to another in MySQL's command prompt?

In the context of MySQL, you cannot switch users. You can switch database and host.

According to the MySQL Documentation on the MySQL Client

  connect [db_name host_name]], \r [db_name host_name]]

  Reconnect to the server. The optional database name and host name arguments may be given to specify the default database or the host where the server is running. If omitted, the current values are used.

This looks similar to how PostgreSQL does it.


Materialized View does not import properly when importing a db for a second time in another schema When I import a database with materialized view mv_mt in just one schema I get no errors.

create materialized view mv_mt
refresh complete
next trunc( sysdate ) + 1
as
SELECT sysdate, media_type.*
from media_type;

But when I try to import the same database to a copy in another schema in the same tablespace I get the following errors:

IMP-00017: following statement failed with ORACLE error 1:
 "BEGIN   DBMS_JOB.ISUBMIT(JOB=>438,WHAT=>'dbms_refresh.refresh(''"ALEXANDRA""
 "."MV_MT"'');',NEXT_DATE=>TO_DATE('2012-07-02:14:22:36','YYYY-MM-DD:HH24:MI:"
 "SS'),INTERVAL=>'sysdate + 1 / 24 / 60 / 6  ',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 23421:
 "BEGIN   dbms_refresh.make('"ALEXANDRA"."MV_MT"',list=>null,next_date=>null,"
 "interval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>438,rollback_seg=>NUL"
 "L,push_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,par"
 "allelism => 0,heap_size => 0); END;"
IMP-00003: ORACLE error 23421 encountered
ORA-23421: job number 438 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 793
ORA-06512: at "SYS.DBMS_REFRESH", line 86
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 23410:
 "BEGIN   dbms_refresh.add(name=>'"ALEXANDRA"."MV_MT"',list=>'"ALEXANDRA"."MV"
 "_MT"',siteid=>0,export_db=>'ORCL01'); END;"
IMP-00003: ORACLE error 23410 encountered
ORA-23410: materialized view "ALEXANDRA"."MV_MT" is already in a refresh group
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_IREFRESH", line 484
ORA-06512: at "SYS.DBMS_REFRESH", line 140
ORA-06512: at "SYS.DBMS_REFRESH", line 125
ORA-06512: at line 1

Anyone any ideas?

This is happening because it's trying to create a duplicate refresh DBMS_JOB with the ID 438.

Just re-run the failing statements manually and specify an unused job ID.

SELECT MAX(JOB)+1 FROM DBA_JOBS;

... will get you the next available ID.

(You may be able to omit the JOB=> parameter and have Oracle auto-generate the ID, but I can't remember off-hand if that'll work).


Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments? In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records:

SELECT COUNT(id) shareLikeNo FROM user_share_like WHERE jusid=$jusid

Or update a field like comment_no in shares(posts) table?
I have fields like shares_no,comments_no,likes_no. Everytime a user click like button the likes_no field will be incremented and other fields as I explained.
This way I have to update every record each time a user post a comment or shares a post or likes a post.
Should I use SELECT COUNT(id) FROM tblname and so on for getting the record count or just updating a record.
There will about 10 to 20 posts each second. Site's traffic will rose.
Which approach should I use? Which one is more expensive?

EDIT:
I've used use index(PRIMARY) as below to speed things up:

SELECT COUNT(id) shareLikeNo FROM user_share_like USE INDEX(PRIMARY) WHERE jusid=$jusid

Another approach is:

SELECT SUM(1) shareLikeNo FROM user_share_like WHERE jusid=$jusid

Is this a better approach?

How frequent do you need to perform this query?

If it is very rarely, then, perhaps it doesn't matter. And you could do "SELECT COUNT(1) FROM ...", do no need to use "SELECT COUNT(id)". SELECT COUNT(id) is not necessary in your case. Please refer here for more about when to use SELECT COUNT(1) etc.
As pointed out by Justin Cave above, does it need to be accurate all the time?

If you need it to be accurate, you might have to suffer the cost of the query.

If not, perhaps you could consider caching the value and only perform the query once every time period (once per 5 seconds?)


Azure Readable Secondary - How to: Different Access Policy to Primary? We have primary databases. They are in a mix of elastic pool, and standalone.
We have successfully created active geo replicated secondaries. (Our use for these is solely workload isolation.)
We want want to have different access policies. e.g A reader (by whatever principal - AAD group user etc) can only access the secondary.
This Microsoft Docs page implies it is possible to use different credentials for the secondary:

An application can access a secondary database for read-only
operations using the same or different security principals used for
accessing the primary database"

There are no further instructions. When attempting to use T/SQL grants on the secondary, an error is given that the DB is read only. This is expected behaviour, however the MS documentation quoted above suggest a different mechanism is possible.
How can we manage access at the secondary level, other than granting at the primary? Or granting excessive rights at the secondary, such as Active Directory Admin?
(We are aware of intent read only, but that does not meet our need, which is to deny certain principals access to the primary, regardless of intent)

The only way to have different access policies. e.g A reader (by whatever principal - AAD group user etc) can only access the secondary-

Make the 'Active Directory admin' of the logical server hosting the secondary replica an AAD group.
Make the user or group that needs to read from the secondary replica is a member of the group mentioned in step 1.
As a pre-requisite you need to set the properties of secondary to read-only or all.


How to combine two queries? I can't explain this very well except by spelling it out.

I start a query on ID, and from there I can get TITLE, DATE, and REGNUMBER.

The problem is I also want to get 'where,' which is also stored in TITLE. So I need to get TITLE from REGNUMBER this time, instead of from ID.

In the end I want

1) ID->TITLE | 2)DATE | 3)REGNUMBER | 4)WHERE(REGNUMBER->TITLE)

to be more clear, TITLE is on the same table in both cases. In the first case, I need to get TITLE from a join on ID, and in the second, from a JOIN on REGNUMBER.

id   -   title    -   date   -   regnumber   -   title(same field as col2)
-------------------------------------------------------------------------------
1   -   event name   -   2/2/12   -   432   -   venue name

While there's still some missing info (it would help to know if there is only one table, or if there are two tables, the names of those tables, and which columns are held in each), I think this might help.
From what you wrote, it sounds like there are two tables (let's call them 'table1' and 'table2'), and table1 has columns 'id', 'title', 'date', while table2 has columns 'regnumber' and 'title'. Again, if I'm not understanding this, please correct me by providing more information about the tables and the structures of those tables.
It appears that you're missing information linking the 'id' of the event to the 'regnumber' of the venue. I think the best way of linking the event id to the venue regnumber is through a third table (we'll call it 'table3') with two foreign keys: 'event_id' (which would reference the 'id' column in table1) and 'venue_id' (which would reference the 'regnumber' in table2).
Before I move on to the SQL, let's make sure the structure of our three tables looks like the following:

table1: id, title, date
table2: regnumber, venue
table3: event_id, venue_id

With each event 'id' paired with a 'venue_id' in table3, we can move on to writing the following query.
SELECT t1.id, t1.title, t1.date, t2.regnumber, t2.venue FROM table1 t1, table2 t2, table3 t3 WHERE t1.id = t3.event_id;

The reason you'll want a third table is because multiple events will happen in the same venue, and events can happen across multiple venues (like the concert series SXSW).


Postgres Minor Version Upgrade from 11.4 to 11.7 I am new to PostgreSQL database. I want to upgrade the PostgreSQL version from 11.4 to 11.7. what are the steps involved can anybody tell. It will be helpful if anyone can share SOP.

Thanks

The documentation advises:

  Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6. To update between compatible versions, you simply replace the executables while the server is down and restart the server. The data directory remains unchanged — minor upgrades are that simple.

In addition, you should check the release notes for additional requirements.
In your case, additional activities are necessary if you are using the intarray contrib module.


SQL server compress backup for lower version databases Recently our development team migrated couple of sql server 2000 Databases onto SQL2K8R2.

Now we creating the backup strategy, and just had a doubt, that:

1) Compatibility level  of those DB's still show 80, we asked the team if that can be changed to current , they had no clue. I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's.

2) For  the Migrated DB's having compatibly 80, are we good to take compressed backups? I am just curious because if we try to restore on the same or upper version , it might not have any conflict.?

Please advice, thanks!

I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's.

As a good practice and suggested by Microsoft you should change compatibility level of database to match with server version on which it is hosted. There are few legacy applications which might require version as 80 for that you have to test, I cannot tell upfront whether you change it or not. You should see what all features were deprecated in 2008 R2 as compared to SQL Server 2000 below two articles would help you

Breaking Changes to DB feature in SQL server 2008 R2

Deprecated database engine features in SQL Server 2008 R2

Deprecated SQL Server features in SQL Server 2008 R2

Regarding changing compatibility level if you face any issue you can immediately change it back to 80, that is why i said a round of testing is required. If you read Change compatibility Level of database it would really help you

  For the Migrated DB's having compatibly 80, are we good to take compressed backups?

Yes you can take compressed backup of 80 compatibility database hosted on SQL Server 2008 R2 subject to condition SQL Server edition is Standard or higher edition


SSMS 2014 "Deploy Database" feature has old SQL Azure settings I'm attempting to test-deploy an existing SQL Server 2005 database into SQL Azure using the "Deploy Database to Windows Azure SQL Database" task in SQL Server Management Studio 2014.

When the wizard launches, it has incorrect (outdated) database settings available:

SQL Azure no longer uses "Web" and "Business" models, and when I try to perform the deployment it fails with the error message

  The service objective 'Business' specified is invalid. (Microsoft SQL Server, Error: 40804)

I receive the same error when I attempt to run "Import Data-tier Application" using a .bacpac of my database. The settings are also old for that interface.

It appears SSMS is using an older deployment wizard. How do I correct this?

Edit:

I fired up a SQL Server 2014 VM on Azure and tried performing the same task. I observed the VM version of SSMS has a newer version number (right) than my local copy of SSMS (left):

I see no pending updates from Windows Update, however.

I wouldn't have been able to find the solution to this problem if you hadn't decided to open the VM on azure and check the version numbers, so thank you!

After I saw your post, I googled "sql 12.0.2430.0" and found a hotfix from microsoft that fixed the problem.

You can find the hotfix Here

The update added full support for the new Azure SQL database tiers.

Cheers!


Join a table to itself, each row needs to join to the ordered last record I have a logging table which for whatever reason logs the column and table that changed, the date it changed and also the value it was changed FROM (the old value). I want to join these records together so that I can see what the record changed TO. (The very newest value is obviously not going to appear as that is never logged). Each record has a unique identifier also.

so I have

| UID | historyFor | DateUpdated | Table      | Column    | oldValue |
| 1   | uid=123    | 01/01/2019  | Person     | firstname | Joe      |
| 2   | uid=123    | 02/01/2019  | Person     | firstname | Bill     |
| 3   | uid=123    | 03/01/2019  | Person     | firstname | Elliot   |

I want to add a column so that it would be

| UID | historyFor | DateUpdated | Table      | Column    | oldValue | nextRecord |
| 1   | uid=123    | 01/01/2019  | Person     | firstname | Joe      | Bill |
| 2   | uid=123    | 02/01/2019  | Person     | firstname | Bill     | Elliot     |
| 3   | uid=123    | 03/01/2019  | Person     | firstname | Elliot   | <blank>    |

nextValue should contain the oldValue from the next record when sorted ascending on DateUpdated. Note there are all different records in here so it has to join on historyFor and find the next most recent oldValue and put it in nextRecord. Is there a way to do this? I'm thinking a top 1 greater than DateUpdated will come into play but I'm just not sure how this is going to work.

You can use LEAD() function to get the next value:

Quoted from docs:

  Accesses data from a subsequent row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

SELECT
  UID,
  historyFor,
  DateUpdated,
  [Table],
  [Column],
  oldValue,
  LEAD(oldValue) OVER (PARTITION BY [Table], [Column]
                       ORDER BY DateUpdated) nextValue
FROM
  t;

UID | historyFor | DateUpdated         | Table  | Column    | oldValue | nextValue
--: | :--------- | :------------------ | :----- | :-------- | :------- | :--------
  1 | uid=123    | 01/01/2019 00:00:00 | Person | firstname | Joe      | Bill
  2 | uid=123    | 01/02/2019 00:00:00 | Person | firstname | Bill     | Elliot
  3 | uid=123    | 01/03/2019 00:00:00 | Person | firstname | Elliot   | null

db<>fiddle here


MySQL - Maintain History of Records but Using Most Recent Let's say I'm developing a database for a transportation service which owns certain vehicles and hires drivers which are assigned to specific vehicles. The database comprises of the following tables:

vehicles

id | number | brand | model | color | date_registered

drivers

id | passport_number | first_name | last_name | dob | address | date_registered

vehicle_drivers

id | vehicle_id | driver_id | date_assigned

daily_vehicle_reports

id | vehicle_id | milage | remaining_fuel | problems_detected | observations | recommendations | report_date

Where daily_vechicle_reports records the state of the vechile at the end of each day.

At the end of each month, I would generate data on all reports for the given month. Now my problem is, given that the possibility that at any given day, the driver of any vehicle can be changed, the data on the previous drive remains in the database, and normally, to generate the data I would use a query similar to the following:

SELECT
    dvr.*,
    v.number,
    v.date_registered,
    d.passport_number,
    d.first_name,
    d.last_name,
    d.dob,
    d.address
FROM daily_vehicle_reports dvr
INNER JOIN vehicles v ON(v.id = dvr.vehicle_id)
INNER JOIN vehicle_drivers vd ON(vd.vehicle_id = v.id)
INNER JOIN drivers d ON(d.id = vd.driver_id)
WHERE MONTH(dvr.report_data) = 4
AND YEAR(dvr.report_data) = 2019

This would give me repeated data for the reports are multiple drivers would be found for the same vehicle. How can I modify the query so as to include ONLY the last driver assigned by that date (the date of the report)?

This is well known problem "Max by group" that can be solved by subselect:

SELECT
       dvr.*,
       v.number,
       v.date_registered,
       d.passport_number,
       d.first_name,
       d.last_name,
       d.dob,
       d.address
  FROM daily_vehicle_reports AS dvr
  JOIN vehicles              AS v  ON v.id = dvr.vehicle_id
  JOIN vehicle_drivers       AS vd ON vd.vehicle_id = v.id

  JOIN ( SELECT vehicle_id                            -- here is additional join with subselect
              , MAX(date_assigned) AS date_assigned   -- last assignment for each vehicle
           FROM vehicle_drivers
          GROUP BY vehicle_id
       ) AS vdm  ON vdm.vehicle_id = vd.vehicle_id    -- joined to the full table `vehicle_driver`
                AND vdm.date_assigned = vd.date_assigned -- to exclude NOT LAST rows

  JOIN drivers               AS d ON d.id = vd.driver_id
 WHERE MONTH(dvr.report_data) = 4      -- Too bad, can't be indexed
   AND  YEAR(dvr.report_data) = 2019   -- The same thing

To speedup the filtering by date you have to use the following approach:

WHERE dvr.report_data BETWEEN '2019-04-01' AND '2019-04-30'

This kind of date range filtering is covered by index (if exists) and way more faster


best practice with RAID0 configuration - to keep all of them in separate drives i.e. tempdb, mdf, ldf, backups Database size - 145GB
Currently our database mdf file, tempdb, backups are in D drive and log files are in C drive.
Can we go for the below configuration:

D drive - tempdb, E drive - mdf, f drive - backups, C drive - logs

or

D - tempdb, E - mdf ,F - logs & backups

We have 26 servers with full recovery model which works 24/7 for our clients.
and more than 100 for other clients with Simple recovery model which works only 9 to 6.
Currently, we have available only 4 hard drives to allocate with this configuration C, D: Mirrored (RAID 1) and E. Can we fit our database all files in this kind of configuration or avoid RAID 1?
Front end application crashes frequently during the day time and inaccessible during night time as maintenance tasks runs which are database integrity, rebuild/reorganize, backup and delete old files.
Could someone please help me with best suitable approach for this?

From performance and safety point of view, 1st solution is better (if you have 4 physical disks available).
For sure you need to separate as soon as possible backups from mdf files in differents disks: in case of a disk crash you lose both the DB and the backup!


Enable Service Broker when deploying solution I'm having an issue deploying the latest build of a database solution after creating some service broker objects. I am thinking that it could be related to not having service broker enabled on the database, as it gets created as part of the build, then deployed straight away.

Where can I set the "ENABLE BROKER" option so it get's executed before deploy, but after the database is created by SQLPackage ?

In the latest SSDT version, the EnableBroker option is under Database Settings-->Miscellaneous-->Service broker options.  I would expect SqlPackage to honor this specification.


How to enable sharding for all databases/collections by default We are doing sharding process for 6 servers which includes 1 master process, 2 config process, 6 sharding processes.
We enabled the sharding by doing sh.enableSharding(database_name) and sh.shardCollection('mydb.collection_name', {_id: hashed}) It worked.
Now, if i add any other database/collection to the cluster, is there any way that, the database/collection get sharded automatically.

Answer is simple. NO!
Every DB and collection in them, must be shard individually. Just because all collections are not created as equal and _id: hashed is not best solution to everything.


Heterogeneous ordered list of value I have the following object model:

class A {
    List<Thing> orderedListOfThings;
}

interface Thing {
}
class X implements Thing {
   int attr;
}
class Y implements Thing {
   String value;
}
class Z implements Thing {
   B b;
}
class B {
   ...
}

The order of the list orderedListOfThings must be preserved. This list can contains X, Y, Z instances. There isn't other Thing implementations.

how this model could be design in a relational database ?
Is there any No-SQL database (key-value, columnar, graph, triple store) that fits better for this kind of model ?

As others have mentioned, this can be done rather easily via a SubClass / Inheritance model.  I would advocate something closer to what Pieter Geerkens suggested than what VDohnal suggested as I prefer not having lots of NULL fields per row, depending on the "type" that the row represents (i.e. the [Value] field has to be NULL on an X row as X does not have that property, Y does but does not have the [attr] field so [attr] has to be NULL on rows representing Y objects). I just feel that is harder to maintain over time as someone needs to be VERY familiar with the app layer object model to know if the NULL field is a valid data point for that row or to be ignored for that row.  Even if you have CHECK constraints to enforce NULL, that is yet something else to maintain as you add properties to objects over time and it is still not easily visible when writing queries against these tables. Please note that the issue, in the past, would have also been a needless waste of space related to fixed-length data types at the data layer, but that has been mostly alleviated by the SPARSE option which was introduced in SQL Server 2008 (well, assuming you are using SQL Server).

Notes:

The essential aspect of this model is that the [Thing] table is the "base" table for your "Thing" object.
The [Thing] table has a [ThingType_ID] field which is not a functional requirement but is a data-dictionary / lookup-table so that you can associate meaningful information for people doing ad hoc queries or reports, etc. It also helps to determine in which subclass table to look for the extension properties, assuming there is a reason why you don't already know and are first looking in the [Thing] table for general info.
The [Thing] table has a [A_ID] field which is an FK back to the [A] table. This maps to your structure of having an A class that contains a collection of Thing.
The subclass tables of [ThingX], [ThingY], and [ThingZ] have the same PK as the base class table [Thing] (i.e. [Thing_ID]). The reason to not have separate IDENTITY values is that the subclass tables are not an entity by themselves but are merely an extension of the base [Thing]. Their identity IS the identity (the concept of identity, not the SQL keyword IDENTITY) of [Thing].
Going along with the prior point, the PK field of the subclass tables [ThingX], [ThingY], and [ThingZ] is also an FK back to the [Thing] table.
There is no need for a separate [B] table as those properties are all 1 to 1 with the
definition of the [ThingZ] class / sub-table.
All 4 of the base-class and sub-class tables have a [ModifiedDate] audit field as it is possible to update the properties of either the base [Thing] table OR a subclass table but not both in the same operation (e.g. updating the [ThingX].[Attr] field doesn't require updating the [Thing] table).
Only the base class table--[Thing]--has the [CreatedDate] audit field because rows in the subclass tables cannot be created independently (because it is only the combination of the [Thing] table and one of the subclass tables that make up an object).  I suppose it is possible to create a row in the [Thing] table only (i.e. no INSERT into any subclass tables) and later come back to INSERT into one of the subclass tables, but that is just silly ;-).

Example SQL using Microsoft T-SQL syntax (but no features highly-specific to SQL Server):

CREATE TABLE A
(
   A_ID  INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
   -- other fields?
   CreatedDate DATETIME NOT NULL DEFAULT (GETUTCDATE()),
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingType
(
   ThingType_ID TINYINT NOT NULL PRIMARY KEY,
   ThingType NVARCHAR(100) NOT NULL
);

CREATE TABLE Thing
(
   Thing_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
   ThingType_ID TINYINT NOT NULL REFERENCES ThingType (ThingType_ID), -- FK to ThingType
   A_ID INT NOT NULL REFERENCES A (A_ID), -- FK to A
   -- other fields?
   CreatedDate DATETIME NOT NULL DEFAULT (GETUTCDATE()),
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingX
(
   Thing_ID INT NOT NULL PRIMARY KEY REFERENCES Thing (Thing_ID), -- FK to Thing
   Attr INT,
   -- other fields?
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingY
(
   Thing_ID INT NOT NULL PRIMARY KEY REFERENCES Thing (Thing_ID), -- FK to Thing
   Value NVARCHAR(500),
   -- other fields?
   ModifiedDate DATETIME NULL
);

CREATE TABLE ThingZ
(
   Thing_ID INT NOT NULL PRIMARY KEY REFERENCES Thing (Thing_ID), -- FK to Thing
   Something DATETIME,
   -- other fields?
   ModifiedDate DATETIME NULL
);

Side Notes:
Just to be clear about differences between this suggestion and Pieter's (since they are very similar):

I have no Table Type for [B] as I don't see a need for it
My interpretation of your structure (i.e. class A having List<Thing>) is such that both of the other answers have misconstrued that relationship:

Pieter's does not allow for having singular (i.e. non-repeated) properties of A while at the same time having multiple instances of Thing.
VDohnal's has the relationship backwards such that a single Thing will have multiple A s. (this has since been corrected).

On a practical level, I cannot see a case for doing the LEFT JOIN query at the bottom of Pieter's answer, but maybe on occasion there is. In such a case I have opted instead to query each type individually via INNER JOIN and then combining via UNION ALL (fields in one table that aren't in the other subclass table(s) need to be given literal values):

 SELECT base.field, subclass.field, subclass.[Attr]
 FROM [Thing] base
 INNER JOIN [ThingX] subclass
         ON subclass.Thing_ID = base.Thing_ID
 UNION ALL
 SELECT base.field, subclass.field, CONVERT(INT, NULL) AS [Attr]
 FROM [Thing] base
 INNER JOIN [ThingY] subclass
         ON subclass.Thing_ID = base.Thing_ID

EDIT:
Here are two more notes that I thought of:

One benefit of this broken out design, as opposed to the single table model, is that it allows for parts of the object model to change (as business needs change over time) without impacting the parts that aren't changing. For example, adding a new property to the X object does not impact either of the [ThingY] or [ThingZ] tables nor the queries that make use of [ThingY] and [ThingZ].
For simplicity of getting data that represents the full X, Y, or Z objects, you can create Views that are a simple CREATE VIEW [X] AS SELECT * FROM [Thing] base INNER JOIN [ThingX] sub ON base.Thing_ID = sub.Thing_ID and so on for the Y and Z objects.

EDIT 2:
Renamed the [X], [Y], and [Z] tables to be [ThingX], [ThingY], and [ThingZ] respectively. This naming convention for the subclass tables is more representative of them being extensions and not complete objects / entities by themselves. This should also make the model (and working with the tables) conceptually clearer in that [X] (the View suggested directly above) = [Thing] + [ThingX], and so on with [Y] and [Z].

Also, the tables will be grouped together in SSMS, queries against sys.tables / sys.objects / INFORMATION_SCHEMA.TABLES and hopefully in your source control system where you have individual SQL scripts for each table :-).


Copy/move data from one cassandra server to another I'm in the midst of replacing a kubernetes stack (to more specific: moving it from a locally hosted cluster to an AKS cluster in Azure), and as a result have to re-create all of my services, including a Cassandra service. All of the data that is contained in the existing Cassandra has to be copied into a new one. However, I'm having a great deal of trouble finding a way to actually do this. With SQL databases it's trivial; with postgres for example you can just use pg_dump on the old database and just echo out the file into the new db.

However there seems to be no equivalent method for Cassandra and googling hasn't really given me a solution. It seems COPY FROM and COPY TO would require manually copying every single table which is ridiculous. Also should be noted that if possible, I really want a solution that does not require directly accessing the docker containers (ie. some solution that I can run from a remote box that has a connection to both cassandra's), because getting direct container level access in AKS can be a huge PITA.

This is how I did by moving data from one VM to another in azure. Didn't test this in kubernates.

Ex:- Below is process to migration 4 node old cluster to 4 node new cluster.

OLD CLUSTER (node A) => NEW CLUSTER (node A),
OLD CLUSTER (node B) => NEW CLUSTER (node B),
OLD CLUSTER (node C) => NEW CLUSTER (node C),
OLD CLUSTER (node D) => NEW CLUSTER (node D),

On old cluster, enable incremental backups on each node.
On old cluster, take snapshots on each node.
On old cluster, take backup of schema.
On old cluster, take backup of tokens (tokens of each node).
Push snapshots and incrementals to azure blob (az copy).
On new cluster, import schema.
On new cluster, import tokens (like OLD CLUSTER (node A) => NEW CLUSTER (node A)).
On new cluster, import snapshots (like OLD CLUSTER (node A) => NEW CLUSTER (node A)). Copy files direct to table folder.
On new cluster, import incrementals. Copy files direct to table folder.
On new cluster, do rolling restart of nodes.

Cassandra version should be same on old and new cluster. You can automate this using scripts.


Trigger Logic (from SQL 6.5 ) I have a database that was originally implemented in SQL 6.5 and has been migrated to SQL2000, SQL2005 and now SQL2008R2 (Compatibility Mode = 10).  Through the migration many new tables, functions, and procedures have been added and many of the newer T-SQL features are in use (e.g., Merge, Windowing Functions, Try-Catch, Table Variables, ...)

However, most of the underlying Referential integrity is still implemented in Triggers. :(

I have run across a T-SQL Code pattern that puzzles me.  I knew some of the original implementers (from circa 1995) and they were not dummies but the reason for this pattern escapes me.  Any suggestions will be appreciated.
This example is from an After Insert Trigger but it is representative.

Declare @validCnt int
, @nullCnt int
, @rowCnt int
Select @rowCnt = @@rowCount
If update (mycol)
Begin
  Select @validCnt = count(*)
  From inserted, myLookupTable
  Where inserted.myCol = myLookupTable.myCol
  Select @nullCnt = 0
  If @validcnt + @nullCnt != @rowCnt
  Begin
    RaisError()
  End
End

I understand what the code is trying to accomplish but I don't understand the reason for @nullCnt.  I hesitate to refactor the code for fear that there is some hidden purpose for the code as is.

My inclination is to change the test to read something like

If Update(myCol)
and @rowCnt != (Select count(*) From ... Where...)
RaisError()

Any thoughts or suggestions?
BTW, we are talking about ~200 triggers total.
Thanks
Ray

Why would I refactor working code?  Well, as I understand the term you would only refactor working code.  If it weren't working you would be debugging or fixing. :)  Isn't the purpose of refactoring is to make the working code "better" in some sense?  Efficiency, readability, reliability, ...
However, the code is not working if we plan to migrate to some more current version of SQL (like even 2012).
It seems the RaisError syntax in the trigger generates a run-time error in 2012 and later.  SQLServer lets me restore the database and change the compat mode to 2012 or 2014 but when a trigger is executed there are a variety of runtime issues.
Also, most of the triggers are implementing actions that could be performed directly in the T-SQL or by Constraints/Foreign Keys.
For example, the sample I posted just checks to see if I actually inserted the correct number of rows.

I'm not sure if this code is fully accurate for columns that have more than 1 row with the same value, but I suppose that's a different subject.  What I see is that the only value the null counter is ever initialized to is 0.  That means it will never have an effect on the addition.  Putting on my dev hat, I could see the start of an unfinished concept.

If I needed to update a column to NULL, the where clause on line 9 wouldn't work.  The test NULL = NULL is false.  That means the join logic fails for update values of null.  That being said, it appears the error handling for that was never implemented beyond having a count column to use.


TSQL to get a list of all users and when they last logged on in crowddb We have a crowddatabase and are trying to get a list of every user and when they last logged on. On Atlassians website there is a query but only for MySQL, the query is:

SELECT
  cwd_user.user_name,
  from_unixtime(cwd_user_attribute.attribute_value/1000)
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated'

I need to rewrite it for mssql and tried with

SELECT
  cwd_user.user_name,
  dateadd(s,12345,1/1/1970)
  select cwd_user_attribute.attribute_value
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated'

but get error The multi-part identifier "cwd_user.user_name" could not be bound.

Anyone who can help with the query?

You have an extra SELECT in your SQL.

Remove the SELECT and change the DATEADD function slightly.

SELECT
  cwd_user.user_name,
  DATEADD(SECOND,(CAST(cwd_user_attribute.attribute_value AS BIGINT)/1000),'1970-01-01')
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated';

Additionally, you should probably rewrite to support the 'new' JOIN syntax (by 'new', I mean ANSI-92)

SELECT
  cwd_user.user_name,
  DATEADD(SECOND,(CAST(cwd_user_attribute.attribute_value AS BIGINT)/1000),'1970-01-01')
FROM cwd_user
INNER JOIN cwd_user_attribute
   ON cwd_user_attribute.user_id = cwd_user.id
  AND cwd_user_attribute.attribute_name = 'lastAuthenticated';


Mysql: Count, Group By, and limiting to first instance of an ID I am trying to write a query to generate a report of sorts and I'm stuck on the logic of it. The summary of it is:

I need to select the count of activities, where post_type is 'invoice' AND event = 'new', grouping by user_id that occurred between two dates based on the ts column. However, I need to make sure that when I'm counting, I only count the FIRST instance of each contact_id (Which comes from the invoices table, which is joined on activities.post_id = invoices.id).

In layman's terms: I need to figure out  how many new invoices were created by each user_id in a given date-range. But I need to only count the FIRST instance of each contact_id.

So, given the following data:

activities

+----+---------+------------+-----------+-------+---------------------+
| id | user_id | post_id    | post_type | event |         ts          |
+----+---------+------------+-----------+-------+---------------------+
|  1 |     190 |       1000 | invoice   | new   | 2018-06-19 14:01:18 |
|  2 |     190 |       1001 | invoice   | new   | 2018-06-19 14:01:19 |
|  3 |     190 |       1002 | invoice   | new   | 2018-06-19 14:01:20 |
|  4 |     230 |       1003 | invoice   | new   | 2018-06-19 14:01:21 |
|  5 |     230 |       1004 | invoice   | new   | 2018-06-19 14:01:22 |
|  6 |     230 |       1005 | invoice   | new   | 2018-06-19 14:01:23 |
|  7 |     190 |       1006 | invoice   | new   | 2018-06-20 14:01:24 |
|  8 |     230 |       1007 | invoice   | new   | 2018-06-20 14:01:25 |
|  9 |     190 |       1008 | invoice   | new   | 2018-06-20 14:01:26 |
| 10 |     230 |       1009 | invoice   | new   | 2018-06-20 14:01:27 |
+----+---------+------------+-----------+-------+---------------------+

invoices

+------+------------+
|  id  | contact_id |
+------+------------+
| 1000 |        500 |
| 1001 |        600 |
| 1002 |        700 |
| 1003 |        800 |
| 1004 |        500 |
| 1005 |        500 |
| 1006 |        500 |
| 1007 |        600 |
| 1008 |        700 |
| 1009 |        800 |
+------+------------+

If I ran the query to find the count between 2018-06-19 and 2018-06-20, I should get the following output:

+---------+-------+
| user_id | count |
+---------+-------+
|     190 |     3 |
|     230 |     1 |
+---------+-------+

user_id 190 should get counted for id: 1, 2, 3 because id 7 and 9 have duplicate contact_ids.

user_id 230 should get counted for id 4 because all of his other rows were counted for user_id 190 already because they were the first instances of this contact_id.

I have an SQL fiddle of the data at https://www.db-fiddle.com/f/9smEW6TwA6UUk7Fh1anAN6/1

It takes 2 steps:

SELECT  user_id, COUNT(DISTINCT contact_id) AS 'count'
    FROM
    (
        SELECT  i.contact_id, MIN(a.user_id) AS user_id
            FROM  activities AS a
            JOIN  invoices AS i  ON i.id = a.post_id
            GROUP BY  i.contact_id
    ) x
    GROUP BY  x.user_id;

(Possibly the DISTINCT can be removed.)


MYSQL database replication Error MY-002061 I've been trying to get two databases in docker containers to replicate. I've been working off this article:
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
(and others).

The state I've been able to get to is that the master and slave dbs have loaded cnf files that set the server-ids, and after the DBs are stood up, I am able to run a few commands that should get the DBs to be connected:

MS_STATUS=`docker exec mysql-master sh -c 'export MYSQL_PWD=password; mysql -u root -e "SHOW MASTER STATUS"'`
CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
CURRENT_POS=`echo $MS_STATUS | awk '{print $7}'`

start_slave_stmt="CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='replication_user',MASTER_PASSWORD='replicationPW',MASTER_LOG_FILE='$CURRENT_LOG',MASTER_LOG_POS=$CURRENT_POS; START SLAVE;"

start_slave_cmd='export MYSQL_PWD=password; mysql -u root -e "'

start_slave_cmd+="$start_slave_stmt"

start_slave_cmd+='"'

docker exec mysql-slave sh -c "$start_slave_cmd"

This sets the master_log_file and Read_master_log_pos to the same values in both the master and slave dbs.

The error I am getting is:

mysql-slave               | 2018-09-20T20:13:41.547886Z 11 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'replication_user@mysql-master:3306' - retry-time: 60  retries: 1, Error_code: MY-002061

I've looked at the documentation, but I don't see any mention of that error code (2061). What am I missing?

I've named the replication user in both database the same... could that be the issue?

Also, you just can run

CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;

but this solution has a restriction (see the documentation https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

Use of a trusted local copy of the public key enables the client to avoid a round trip in the client/server protocol, and is more secure than requesting the public key from the server. On the other hand, requesting the public key from the server is more convenient (it requires no management of a client-side file) and may be acceptable in secure network environments.


Alter view after table drop I want to alter the view for the drop tables.

For an example:

View:

create view View1 as
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from table4

I droped table1 and want create MasterView contains View1.

Note: Need to alter view view1 as table1 has been droped.

My try:

create procedure udsp_createallview
as
begin

declare @sql varchar(max) = ''
declare @tbl varchar(max) = ''
declare @view varchar(max) = ''
declare @create_view varchar(max) = ''
declare @query nvarchar(max) = ''
declare @create_view1 varchar(max) = ''
    begin try

        set @sql = 'create view dbo.MasterView AS
                    select * from View1
                    union all
                    select * from View2';

        print(@sql);
        exec(@sql);

    end try
    begin catch

        select @tbl = replace(SUBSTRING(ERROR_MESSAGE(),CHARINDEX('''',ERROR_MESSAGE()),LEN(ERROR_MESSAGE())),'''.',''''),
               @view = ERROR_PROCEDURE();

    end catch

    if @tbl <> '' and @view <> ''
    begin

        select @create_view = VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS WHERE Table_name = @view

        --set @create_view = 'replace(replace('+@create_view+','+@tbl+','''')';
        set @query = 'select @Result = replace('''+@create_view+''','+@tbl+',''''),''create'',''alter'')';

        --exec(@query);
        exec sp_executesql @query, N'@Result varchar(max) out', @create_view1 out
        print(@create_view1);

        print(@tbl);
        print(@view);
    end

end
go

exec udsp_createallview;
go

Unable to remove rest of the select statement part. Sometimes select statement may come with where clause.

Working under the assumption that you are dealing with some sort of code generation (mostly because this is the only time I have encountered this sort of issue), here is the general strategy I would employ:

But first,

All initial code should follow strict formatting.  TSQL is on the best TSQL parser, so specific limitations need to be followed to simply the process.  The limitations I am assuming are:

The views do not have leading comments (if you want to tweak my
example to incorporate them, I strongly suggest using either single
line comments, as they are easier to work with).
The create view will have "CREATE VIEW .view" as the first
line, follow by "AS" on the second line, and the remaining code on
the following lines (no extra spaces, tabs or CRLF)
Unioned statements will be seperated by "UNION ALL", on it's own
line.
All the views that can be regenerated will be under the schema 'Regenerate'
All table references will be in the form of "[schema].[table]"

With that in mind,

I suggest that you add a drop table trigger.  Inside that tirgger I suggest that you get all views within the appropriate schema that have a reference to the table that was just dropped.  I would fetch the definition for each and split out the view body based using "UNION ALL as the deliminator, using XML.  I'd then remove all statements that have reference to the removed table.  I would recombine the statements into a Create Views, then execute the whole set as a block of dynamic SQL.

Here is some sample code that follows that approach.  Note, I didn't test the trigger, nor the final generated code (you get what you pay for [grin], so it may need some tweaking), but I simulate a trigger call and did a quick inspections of the resulting SQL.

CREATE TRIGGER db_Drop_Table
ON db
FOR DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    DECLARE @Removed NVARCHAR(MAX) = CONCAT(QUOTENAME(@Schema), QUOTENAME(@Table))

    -- These may need to be tweak to accomdate how the underlying views are written
    DECLARE @UnionDelim NVARCHAR(100) = 'UNION ALL'
        , @AsDelim NVARCHAR(100) = CONCAT(CHAR(13), 'AS', CHAR(13))
        , @CreateView NVARCHAR(100) = 'CREATE VIEW '
        , @RegenerateSQL NVARCHAR(MAX);

    WITH ViewDefinitions AS (
        SELECT
          OBJECT_DEFINITION(object_id) as ViewText
        FROM
     sys.views as v
        WHERE OBJECT_SCHEMA_NAME(object_id) = 'Regenerate'
            AND OBJECT_DEFINITION(object_id) LIKE CONCAT('%', @UnionDelim ,'%')
     )
    , NormalReturnsViewDef AS (
        SELECT REPLACE(ViewText, CONCAT(CHAR(13), CHAR(10)), CHAR(13)) ViewText FROM ViewDefinitions
    )
    , ViewParts AS (
        SELECT
            SUBSTRING(ViewText, CHARINDEX(@CreateView, ViewText)+ LEN(@CreateView) + 1,  CHARINDEX(@AsDelim, ViewText) - (CHARINDEX(@CreateView, ViewText)+ LEN(@CreateView)))  AS ObjectName
            , LEFT(ViewText,  CHARINDEX(@AsDelim, ViewText) + LEN(@AsDelim) - 1)  AS CreateText
            , RIGHT(ViewText, LEN(ViewText) - (CHARINDEX(@AsDelim, ViewText) + LEN(@AsDelim) - 1)) AS ViewBody
        FROM NormalReturnsViewDef
    )
    , XmlViewBody AS (
    SELECT
        ObjectName
        , CreateText
        ,  CAST(CONCAT('<def><statement><![CDATA[', REPLACE(ViewBody, @UnionDelim, ']]></statement><statement><![CDATA['), ']]></statement></def>') AS XML) as ViewBodyXml
    FROM ViewParts

    )
    , Statements AS (
         SELECT
            ObjectName
            , CreateText
            , XmlStatementText.value('.', 'NVARCHAR(MAX)') StatementText
         FROM XmlViewBody
            CROSS APPLY ViewBodyXml.nodes('/def/statement') Statements(XmlStatementText)
    )
    , StatementsFiltered AS (
        SELECT *
        FROM Statements
        WHERE StatementText NOT Like CONCAT('%',  @Removed, '%')
    )
    , RebuiltStatements AS (
        SELECT
            CONCAT(
                'DROP VIEW IF EXISTS ', ObjectName, ';', CHAR(13),
                CreateText, CHAR(13),
                STRING_AGG(StatementText, @UnionDelim)

            ) as RecreateView
        FROM StatementsFiltered
        GROUP BY ObjectName, CreateText
    )
    SELECT @RegenerateSQL = STRING_AGG(RecreateView, CONCAT(CHAR(13), CHAR(13))) FROM RebuiltStatements

    EXEC (@RegenerateSQL)

END

Notes:  The query optimizer will not produce a nice plan... however this should not be running often, so that is less of a concern.  You may be able to reduce the number of CTEs, but I find it easier to follow the logic. This should work for 2008 R2 if you replace STRING_AGG with a UDF.