perform multiple conditional join on multiple tables I have a situation where I need to get result based on conditional joins

For example,

    SELECT
      CGI.SALES_BRAND_BSNSS_ID,
      CGI.SALES_BRAND_CD,
      NVL(MTRX. matrix_score_cd,'RV1') MATRIX_SCORE_CD,
      MDL.CORP_GROUP_ID,
      MDL.MKTBRIDGE_RUN MKTBRIDGE_RUN_CD,
      MDL.MKTBRIDGE_GROW MKTBRIDGE_GROW_CD,
      MDL.MACRO_SEG MACRO_SEG_CD
FROM

      BIRTAB.CGI_ACTIVE_CUST_SEG_V MDL INNER JOIN

      BIRTAB.SBBID_CGI_HIST_XREF CGI ON

      MDL.CORP_GROUP_ID = CGI.CORP_GROUP_ID LEFT JOIN

      BIRTAB.CUST_SEG_SCORE_MATRIX MTRX ON

-- if below two join conditions matches then it need to go to 3rd join condition else ignore all joins,

            NVL(trim(MDL.ECHECK_DECILE_CD),'%') = trim(MTRX. ECHECK_DECILE_CD) AND

            NVL(trim(MDL.MACRO_SEG),'%') = trim(MTRX.MACRO_SEGMENT_CD)  AND

-- if the above two joins matches and below join doesn't matches then ignore below two joins and get result based on above 2 joins

            NVL(trim(MDL.MKTBRIDGE_RUN),'%') = trim(MTRX.MKT_BRIDGE_RUN_CD) AND

-- if the above three joins matches and below join doesn't matches then ignore below join and get result based on above 3 joins

            NVL(trim(MDL.MKTBRIDGE_GROW),'%') = trim(MTRX.MKT_BRIDGE_GROW_CD)

Can anyone suggest some ideas to perform this logic.

Ty

I got answer for my question, I did a subquery and applied rank and order by functions and get first matching values.

    SELECT
      CGI.SALES_BRAND_BSNSS_ID,
      CGI.SALES_BRAND_CD,
      SUBQRY.matrix_score_cd,
      SUBQRY.corp_group_id,
      SUBQRY.MKTBRIDGE_RUN MKTBRIDGE_RUN_CD,
      SUBQRY.MKTBRIDGE_GROW MKTBRIDGE_GROW_CD,
      SUBQRY.MACRO_SEG MACRO_SEG_CD

FROM
(
SELECT
    cgi_active_cust_seg_v.corp_group_id,
    cust_seg_score_matrix.matrix_score_cd,
    CGI_ACTIVE_CUST_SEG_V.MKTBRIDGE_GROW,
    CGI_ACTIVE_CUST_SEG_V.MKTBRIDGE_RUN,
        CGI_ACTIVE_CUST_SEG_V.MACRO_SEG,
    RANK() OVER (PARTITION BY cgi_active_cust_seg_v.corp_group_id ORDER BY cust_seg_score_matrix.mkt_bridge_run_cd DESC,
    cust_seg_score_matrix.mkt_bridge_grow_cd DESC,
    cust_seg_score_matrix.macro_segment_cd DESC,
    cust_seg_score_matrix.echeck_decile_cd DESC) VAL_MATCH
FROM
    birtab.cgi_active_cust_seg_v INNER JOIN
    birtab.cust_seg_score_matrix ON
        cust_seg_score_matrix.mkt_bridge_run_cd IN ('%', NVL(cgi_active_cust_seg_v.mktbridge_run,'%')) AND
        cust_seg_score_matrix.mkt_bridge_grow_cd IN ('%', NVL(cgi_active_cust_seg_v.mktbridge_grow,'%')) AND
        cust_seg_score_matrix.macro_segment_cd IN ('%',NVL(cgi_active_cust_seg_v.macro_seg,'%')) AND
        cust_seg_score_matrix.echeck_decile_cd IN ('%',NVL(cgi_active_cust_seg_v.echeck_decile_cd,'2')) AND
        SYSDATE BETWEEN cust_seg_score_matrix.EFFEC_DT AND cust_seg_score_matrix.END_DT

) SUBQRY
INNER JOIN
      BIRTAB.SBBID_CGI_HIST_XREF CGI ON
            SYSDATE BETWEEN CGI.SBBID_CGI_EFFEC_DT AND NVL(CGI.SBBID_CGI_END_DT,TO_DATE('31-DEC-2999','DD-MON-YYYY')) AND
            subqry.CORP_GROUP_ID = CGI.CORP_GROUP_ID
WHERE
    VAL_MATCH = 1;


Convert SQL server stored procedures to postgreSQL I have a SQL Server database that I am migrating to PostgreSQL 12.0.
I have managed to migrate the schema and the tables. The applications connecting to the database rely a lot on stored procedures, which also need to be translated to PostgreSQL.
Unfortunately, the tool I was using doesn't convert stored procedures. Any ideas on how I should go about this or is there a tool which can do this?

Unfortunately you'll probably have to handle the stored procedures by hand. If you're lucky, they're simple procedures following standard SQL syntax, and you'll be able to just copy the existing queries over to PostgreSQL, otherwise you'll have to handle any fallout on a case by case basis.
Side note: this is one benefit of some database agnostic ORMs like Dapper, if your database is mainly consumed by application code. By using the ORM framework's native calls, you decouple your database logic from the actual database system, but there's pros and cons.


Location of postgres db file for backup I would like to make a backup of a postgres database file that I created on Ubuntu 16.04.

I cannot find the file. Some other sources indicate it should be under:

/var/lib/postgresql/[version]/data

When I run show data_directory inside of postgres it shows:

/var/lib/postgresql/9.5/main

However there is no data folder there.

To find out where the PostgreSQL data folder is located, just run SQL query:
show data_directory;

You can find more details here
However, note
File System Level Backup are faster but large in size. The worst thing is that to perform a database's backup, PostgreSQL Server required to be stopped. More information about the restrictions of this operation can be found in the official documentation. On my own I will add that from such backups, as a rule, there is more harm than good.
The main method for creating backups in PostgreSQL is the pg_dump utility.
pg_dump dbname > dbname.dump

Opposite file-level backup, a backup via the pg_dump tool does not require stopping the server, it is easy to automate and does not require access to the server file system (it can be performed remotely).


Keep in mind that if CURDATE() doesn't fit any range of dates you'll get no rows.

select   id,
         min(price) price,
         special_price,
         special_from_date,
         special_to_date
from     prices
where    special_price is not null
and      special_from_date is not null
and      special_to_date is not null
and      (curdate() >= special_from_date
          and
          curdate() <= special_to_date)
group by id

  id |    price | special_price | special_from_date | special_to_date
---: | -------: | ------------: | :---------------- | :--------------
2648 | 299.0000 |       85.0000 | 2017-12-29        | 2018-02-28

dbfiddle here


Turning off Append only and snapshot on Redis I want to turn off data persistence on my Redis master, by turning off appendonly and snapshots, I want to use the replica for persistence, will turning off these two have any affect on replication?

Yes. If your primary instance crashes and restarts it will be empty; at this point your replica will also become empty, that would be a disaster.
Have a read of the replication docs https://redis.io/topics/replication . It suggests if you really want to avoid writing to storage in your primary (which really shouldn’t be a big deal with modern hardware), you must make sure you haven’t configured your primary instance to automatically restart and you’ll need to make sure you don’t manually restart it. You would make sure a secondary has taken over first and your old primary is now set to serve as a replica of that.


How to pass a newly INSERTed row to a function? I have following tables:

 table1:       table2:
| uid | did | | uid | did |
|-----+-----| |-----+-----|
| 1   | 2   | | '1' | 2   |
| 4   | 2   | | '4' | 2   |
| 2   | 3   | | '2' | 3   |
| 1   | 8   | | '1' | 8   |
...

I need to create a trigger that would copy every inserted row from table1 to table2, which is on a different schema.

So I made the following function:

CREATE OR REPLACE FUNCTION schema1."clone to table2"(uid numeric, did numeric)
 RETURNS void
 LANGUAGE sql
AS $function$
BEGIN;
        INSERT INTO schema2."table2" ("uid", "did")
        VALUES(uid::text, did);
END;
$function$
;
;

I have no clue how to pass this function properly to a trigger. After a bit of searching I found this:

CREATE TRIGGER on_insert
AFTER INSERT
ON schema1.table1
FOR EACH ROW
EXECUTE PROCEDURE schema1."clone to table2"(new."uid", new."did")

However it throws a "syntax error near ".", Position: 127". So I suppose I got it completely wrong.

I know views are perfect solution for my problem but I unfortunately can not use them in my scenario. I just need these two tables to be always identical, with a difference that table2 needs to have uid as text type.

A trigger requires a trigger function. And those can only be written in PL/pgSQL.

So you need something like this:

CREATE OR REPLACE FUNCTION schema1.clone_to_table2()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO schema2."table2" ("uid, "did")
  VALUES(new.uid::text, new.did);
  return new;
END;
$function$
;

BEGIN does not require a ; at the end.

And then a trigger definition to go with that

CREATE TRIGGER on_insert
  AFTER INSERT ON schema1.table1
  FOR EACH ROW
  EXECUTE PROCEDURE schema1.clone_to_table2();


Sync 8 tables of differents databases on same server I will use eight wordpress websites and i need to sync ther wp-user tables so each single user can connect through all websites.
All of these will be on the same server, using the same MySQL user, but they will each have their own database.
The server will be a mutualised one, so i cannot install any other software that MySQL.

What i thought of doing was this :

create a master table which will collect recursively all other
tables.
When it's finished, it is supposed to have every information of the other up to date... Isn't it ?
Then i would sync all previous databases on this master one.

What do you think about it ?
But i don't know how to do that. I thought of using CRON to run that once a day, but i don't know anything about how to write MySQL scripts, and didn't understand much about the "MySQL Replication" topics i found until now...

Thank you in advance.

EDIT : Michael - sqlbot suggested using the "merge algorithm" to do what i need. I like what it looks like, but did not succeed using it. Maybe because I run MariaDB on Localhost. What do you thnik, and n=know about this solution ?

There's actually a very simple solution to "syncing" multiple identical tables in MySQL, and that is by not trying to "sync" actual base tables, at all... but instead creating something that looks like another table but is essentially something along the lines of a "reference" or "alias" to the base table, using a simple view like this:

CREATE ALGORITHM=MERGE VIEW this_database.users AS SELECT * FROM that_database.users;

  Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table...

  — http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html

The view definition, above, meets these criteria, and would be updatable.  Queries referencing a view with a simple definition like this will behave, essentially, no differently than if they were actually written to update (and insert into, and delete from) the actual underlying base table.

So INSERT INTO this_database.users is exactly the same as INSERT INTO that_database.users, and the same is true for UPDATE and DELETE.

If there are foreign keys involved, then the child tables have to have their foreign keys declared against the actual base parent table, but MySQL (InnoDB) has no issue with foreign key constraints that cross database boundaries.

Indexes on the base table will still be used where appropriate, since the MERGE algorithm essentially causes MySQL to "merge" the logic of the view definition into the query you've actually written -- thereby accomplishing the magic of a table that functionally is a reference to another table.

You don't actually have to explicitly declare ALGORITHM=MERGE, since the optimizer will always choose it on such a simple view, but it's still probably a good choice to include that in the definition.

While it is possible that Wordpress might do something internally that prevents this from working, I can't think of anything offhand.  It seems like it would be worth testing in your environment.


How do relations work in NOSQL? I was using SQL in all my previous application and to understand the Parse.com db storage, I wanted to know NOSQL.

I tried searching the differences between SQL and NOSQL for past 2 days & I still don't understand how a relation works in NOSQL.

I learned that NOSQL is schemaless and there's no structure like SQL. Let me brief my question in an example.

Example :

In a SQL application for School management, I would create a table students which stores students' information. For marks, I'll have a marks table which has a column user_id that refers to students table. I'll have one more table subjects which will hold sub_id, sub_code and subject_name.

So, if I change sub_code in the subjects table, that will simply reflect to the other tables where FOREIGN KEY is used by using ON UPDATE DELETE CASCADE.

Now my question is,

  since NOSQL doesn't use NORMALIZATION, how will the data be stored?
  Should a single table (document in NOSQL) hold all the
  information that is in students table, marks table and subjects table?

  If we dump all the information into one single table, how efficient will it be?

If we want to change a subject code, then we need to update all the documents, right? (In SQL, we simply change subject code in one table, since other columns in other table which are referencing this column subject code will simply update right rather than updating each single entries?)

In your question you are using "SQL" and "relational" to mean the same thing.  In the same way you can think of "NoSQL" as meaning "not relational" i.e. there is no primary key / foreign key concept; there is no "normalisation" enforced or implied; there is no real meaning of the words "table" or "column".  To a key-value store all it knows about is a big collection of bytes you pass to it - the "value" - and a smaller collection of bytes - the "key" - used to index that big collection.  What is inside that big collection and how it maps onto byte in other values in the database is entirely down to your application and the quality of the code in it.

Other NoSQL DBMSs, however, do allow you to define what is inside the "value".  They hold a schema for the data, much like relational databases do.  Cassandra is an example of this.

So it is up to you and how you implement your application.  You could set up your key-value store to mimic a relational data model with surrogate ids.  This would require a lot of reads and lose the point of a NoSQL solution.  You could set up your key-values to store all denormalised values at the instant the record was written.  This will be fast for retrieval but slow for updates.  You have to choose what's best for your system and implement it accordingly.  NoSQL is not magic.  You still have a lot of design and development to do.

The term NoSQL is very broad.  It has more to do with marketing than with a technology.  Still less to do with maths such as the set theory underpinning relational databases.  Different NoSQL products work differently.  You will not know all of "NoSQL" by reading just Parse.com.

Understand your problem.  Understand the compromises you are prepared to make to achieve your performance or consistency goals and choose the product which best matches your requirement.


Execute Permission Denied Using SQLCMD I have a Windows Server 2012R2 with SQL Server Express 2014 install that supports a video app.  I am trying to create a backup solution for it using the Task Scheduler to do full and incremental backups.  I have the task scheduler executing a batch file that uses SQLCMD to run a stored procedure that does the backup.  This is the batch file:

@Echo Off

"C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SqlCmd.exe" -S SERVER\INSTANCE -i "C:\Scripts\FullBackup.sql"

The SQL that it runs (in FullBackup.sql) is as follows:

exec spBackupDatabases '\\BackupServer\Backup\Database\Server\', 'F';

The spbackupDatabases stored procedure is created in Master.  When I run this stored procedure with SSMS logged in as myself (a local administrator) it works fine.  When I try and run the same thing, still logged in as myself, from the batch file I get the following error:

C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn>\scripts\FullBackup.cmd
Msg 229, Level 14, State 5, Server SERVER\INSTANCE, Procedure spBackupDatabases, Line 1
The EXECUTE permission was denied on the object 'spBackupDatabases', database 'master', schema 'dbo'.

There is nothing in the SQL Server Logs (even with failed and successful logins turned on) so I'm not even sure what user is being used.  What might I be doing wrong?

In the end, I had to give myself explicit execute permission on the database.  Even though I was the owner of the database, a sysadmin for the database, and administrator on the server, etc. - SQLCMD would only work if I had the execute permission.


Restore data from an old rman backup - ORA-01152 I have some trouble restoring some data from an old backup.

Original machine was lost and my mission is to restore data. This is not a critical machine, but some data is still useful.

Oracle on original server: 11.2.0.2
Oracle on destination server: 11.2.0.4

Server is redhat- 5.6 original. 6.x destination.

On the new machine, with oracle installed I did:

export ORACLE_SID=oldsid
rman TARGET /

And in rman i set the sid as the old databsae:

SET DBID 3348161342;

Then restored controlfile and spfile from the backup. Important note: these files are more recent than the backup.

RESTORE CONTROLFILE from '/mount/restorepoint/3348161342_41qtltn4_1_1_20160111.bck
restore spfile from '/mount/restorepoint/sp_OLDDB-3348161342_42qtltn6_1_1_20160111.bck';

Database in mount with:

ALTER DATABASE MOUNT;

And specified where the backup files are:

catalog start with '/mount/restorepoint/rmanfiles';

Because on the original db there is also TDE.

Created a directory to put wallet
Created sqlnet.ora files with indications on the wallet location

Altered system with:

alter system set encryption wallet open identified by "thetdepassword";

And then again in rman:

alter database mount;
RESTORE DATABSE;

So far, everything fine. But when I trai to OPEN database:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Fine, my bad. Then I use: alter database open RESETLOGS; and I have the error:

ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/ltm/data/oracle/dati/olddb/users01.dbf'

What am I missing?
In the original backup script options must be:

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE

How can I read again that data?

I come from sqlserver and postgresql world, so probably I miss something...

Lets see the information provided by oerr utility.

ORA-01152: file 1 was not restored from a sufficiently old backup
Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.
Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery

According to your post you have not recovered the database. You may try the following commands to recover your database.

SQL> recover database using backup controlfile until cancel;
SQL>alter database open resetlog;

Since you are new to Oracle, you may find the following link useful to restore old backup to new host.
Restoring a Database on a New Host


OFFSET ... FETCH overlapping results on second page When using OFFSET ... FETCH, I'm having I'm getting...interesting results.

Here are two sqlfiddles to illustrate my problem.

http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8

The first fiddle is the first page, while the second is the...second page.

Here are the unpaginated results.

First thing, the paginated results are not in the same order as the unpaginated results. I'm cool with that, I guess, as I'm not showing the unpaginated results anyway.

However, event though I'm specified an offset of 0, and for some reason it decided to put them in the reverse order they were entered, it skips the first 3.

On the second page, I see see results that appear on the first page.

The fact that they all have the same value, although in this example is contrived, is a reality in the specific query I'm dealing with in real life. It's a table, and the user selected to sort by date.

This is not unexpected.  Your ORDER BY does not determine a complete (unique) order. As described in MSDN documentation:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

The underlying data that is used by the query must not change. ...

The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

You have order by date but all the rows have the same value in date. To have determinate results with TOP / OFFSET ... FETCH, you have to use an  ORDER BY that completely determines an ordering.
You could use for example:
order by date asc,
         name asc

or (if (date, name) is not unique either:
order by date asc,
         eventID asc

or:
order by date asc,
         name asc,
         eventID asc

If the query is produced by some form and can be customized by the user, you could - behind the scenes - add the primary key column(s) (I guesseventID here) in the end of the order by list, so you have an order that is determinate and stays the same across different executions of the query.


If you want to store availability in a table, you should separate the start time and end time of shift in 2 columns.

Your table would then be:
create table support_staff_schedule
( id int not null,
name varchar(50),
time_start int,
time_end int)

alter table support_staff_schedule add index time_start_end (time_start desc,time_end);

The time is set as integer because it doesn't look like you care for the date. If date is included then you would have to create a new row for every schedule for a given person. I'm assuming your business rule is: "Same schedule every day unless modified"

I use time_start in descending order because technically you should be spending most of your time looking at the recent schedule rather than past schedule. If your business requirements are different, you may want to change that.


Column must appear in the GROUP BY clause or be used in an aggregate function when using CASE statement in ORDER BY clause I have a PostgreSQL database that contains events. Events can have multiple date ranges. Date ranges are stored in a separate table. I’d like to get the IDs of current and upcoming events. IDs of current events should be outputted first, followed by IDs of upcoming events.

This is my current SQL query:

SELECT date_ranges.event_id
FROM date_ranges
WHERE date_ranges.through_date >= current_date
GROUP BY date_ranges.event_id
ORDER BY CASE WHEN date_ranges.from_date <= current_date THEN 1 ELSE 2 END ASC;

However, it doesn’t work:

ERROR:  column "date_ranges.from_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5: ORDER BY CASE WHEN date_ranges.from_date <= current_date THE...

How can I work around this problem in this case?

If all events have the same from_date:

SELECT event_id
FROM   date_ranges
WHERE  through_date >= current_date
GROUP  BY event_id, from_date  --  add it to GROUP BY
ORDER  BY from_date > current_date; -- simplified expression (assuming NOT NULL)

But then the column should be in the event table once per event.

If not, your sort order is ambiguous. I would start by asking how the same event can have different date ranges?

Your setup is broken either way.


Converting from char(26) to datetime2 In course of a change I migrated a database from Oracle 12.1 to MSSQL 2016.
Since the two databases handle date differently, I now need to move the fields from char(26) to datetime2.
Output format is e.g. 2011-05-30-13.06.56.824000 What I need is Type 21: 2016-04-01 10:20:56.8950000.
I already tried using

SELECT CONVERT(
           DATETIME2
          ,LEFT (EXP_DATE ,10) + "" +
           REPLACE (SUBSTRING(EXP_DATE ,12 ,8) ,"." ,":") + RIGHT(EXP_DATE ,7)
          ,21
       ) AS DateFromString
FROM   My-TABLE;

but this does not work (wrong column name):

  Message 207, level 16, status 1, line 1 Invalid column name " ".
  Message 207, level 16, status 1, line 1 Invalid column name ".".
  Message 207, level 16, status 1, line 1 Invalid column name ":".

Can someone help me?

You should change double-quotes by single-quotes.

'.', ':'

CREATE TABLE MyTable(EXP_DATE varchar(26));
INSERT INTO MyTable (EXP_DATE ) VALUES ('2011-05-30-13.06.56.824000');
GO

1 rows affected

SELECT CONVERT(datetime2,
               CONCAT(LEFT (EXP_DATE, 10), ' ',
                      REPLACE (SUBSTRING (EXP_DATE, 12, 8), '.', ':'),
                      RIGHT (EXP_DATE, 7)),
               21)
FROM MyTable;
GO

| (No column name)    |
| :------------------ |
| 30/05/2011 13:06:56 |

dbfiddle here


is there any point of moving tempdb files into different disk drive if all LUNs are from the same RAID pool? According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk.

I have a virtual server which had originally 4 LUNs from the same RAID 10 pool. By the help of volume manager, I converted those 4 LUNs into 4 different volumes.

Now the question is, will it make any difference by moving tempdb into separate volume, or it is just fine to keep them with other SQL Server files in terms of performance?

There are several potential benefits.

Constraining TempDb Growth
Making disk-level performance counters more informative.
Separating the IO Queues so a log write IO never waits behind TempDB IOs in Windows.
Simplifying storage reconfiguration both in Windows and on the SAN.
Scaling across SAN controllers and enabling per-lun SAN optimizations.


MySQL hangs when I query specific tables after truncate I've observed this a couple of times now.
I truncated a table, and now anytime I do any SQL operation on that table, MYSQL client will stop responding. I can open a new client and query any other table. If I truncate another table while this is going on, it will also become unresponsive. It seems to take a very long time to "fix" itself.

Eventually, it does, and I can truncate the tables and run any other query operation against them.

Does anyone know why this would happen?

See answer on StackExchange and the bug is known and related to the autoincrement.
And if you don't only want to understand the issue, but fix it, see https://dba.stackexchange.com/a/146181/220459.
mysqladmin -u root proc
mysqladmin -u root kill xxx

where xxx is the first process that is hanging.


Copy data between two databases periodically Possible Duplicate:
  How to update one database from another?

I need to know how to copy data across to another SQL Server database periodically. Both databases are in the same SQL server; I think I need something similar to MySQL events (database level) or a cron job.

I want to:

Copy data from database1..sametable to database2..sametable using a SQL command
Run this statement every 15 minutes

Assuming you have got 2 Tables with identical Structure in databases

e.g. test and test1 on the same Server

By example:

CREATE TABLE [dbo].[AA](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_AA] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
) ON [PRIMARY]

GO

You could use a trigger like that on database test1

CREATE TRIGGER [dbo].[tr_AA]
   ON  [dbo].[AA]
   AFTER  INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    Delete test2.dbo.AA from deleted where test2.dbo.AA.ID=deleted.ID

    Set identity_insert test2.dbo.AA ON
    insert into test2.dbo.AA (ID,NAME) select inserted.ID,Inserted.Name from
    inserted
    left join test2.dbo.AA a on a.ID=inserted.ID
    where a.ID is null
    Set identity_insert test2.dbo.AA OFF

    if exists(select * from deleted)
    Update test2.dbo.AA  Set Name=inserted.Name
    from inserted
    where test2.dbo.AA.ID=inserted.ID

END

Another possibility would be running a job in intervals from the task scheduler using "sqlcmd", for example calling a procedure which was created by you for synchronizing data.

http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-147SQLCMD148-150-Part-I.htm

If you are using a version with  "SQL Server Agent" (not contained in express versions) you could plan task here as simple query or as dts(x) jobs.


EXECUTE AS LOGIN failed with local windows group login I'm troubleshooting a permission issue that I tried to perform the query below
EXECUTE AS LOGIN = 'ComputerName\Windows Group Name';

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

SELECT *
FROM sys.login_token
WHERE principal_id > 0;

revert;

It throws error at the first line with the message below

Cannot execute as the server principal because the principal
"ComputerName\Windows Group Name" does not exist, this type of
principal cannot be impersonated, or you do not have permission.

I confirmed the windows group and the login exists.
I search the error message and it seems people who faced the same error are not run into this while using windows group login.
Is it some native limitation or there is something I missed?

you can only impersonate login that appears in sys.server_principals
here an example from microsoft doc
https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15#_user


In your first query you do ...orders.created  <= '2015-07-31'; which is actually

orders.created  <= '2015-07-31 00:00:00';

since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like

orders.created  <= '2015-07-31 23:59:59';


PostgreSQL 9.6, Disable archive log or minimal archive log size when do a pg_restore I have a production DB PostgreSQL 9.6 that is on master/standby structure. Every at 1 AM I restore a few huge tables(from the other DB server) to the master node during restore till finished the archive log generated around 100GB. Is there any good way to minimize the archive_log from the pg_restore task?

The best you can do is to set wal_compression = on to compress whole blocks that are written to WAL, but that will cost CPU.

The information has to be transferred from the master to the slave, so you cannot avoid generating WAL.

However, that WAL need not be stored on disk permanently, it should be streamed from master to slave directly. Perhaps the network is not strong enough to stream all that information in a timely fashion, and it needs to be kept around on disk for a while.


Unique sets of IDs from table I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified):

CREATE TABLE chemicals (
    name text PRIMARY KEY,
    chem_id SERIAL UNIQUE NOT NULL
);
CREATE TABLE mixtures (
    mixture_id SERIAL PRIMARY KEY,
);
CREATE TABLE mixture_chems (
    mixture_id INTEGER REFERENCES mixtures (mixture_id),
    chem_id INTEGER REFERENCES chemicals (chem_id)
);

But I also would like to enforce that there is only one (unique) mixture_id that is referred to by any particular combination of chem_id (via the rows in the mixture_chems table).

How could I implement this in PostgreSQL?

One person suggested I might need to use triggers to compute some new value, that uniquely identifies a mixture, and then enforce uniqueness on that. Thoughts on how to implement that, or whether it'd be appropriate here?

I agree with that person. Here is an implementation.
Basically, add a UNIQUE array column of all chem_ids to table mixtures and keep it current with triggers. Arrays must be sorted consistently, I use the additional module intarray for that and to optimize performance.
For lack of definition I assume frequent multi-row writes, making it a perfect candidate for transition tables introduced with Postgres 10. See:

What is a “transition table" in Postgres?

Important note: Technically, this works in Postgres 10. But while testing I ran into a bug of intarray functions that seemed oddly familiar: empty arrays would not compare equal due to incorrect internal array dimensions. Tom Lane found and fixed this for Postgres 11, but it was not backported to Postgres 10. I strongly advise to use Postgres 11 with this.
Turns out to be another instance of a bug I reported myself earlier. See here and here. Took me a while to reproduce and get the full picture.
This uses a variety of advanced features. Not recommended for beginners.
Code
CREATE TABLE chemicals (
  chem_id serial UNIQUE NOT NULL
, name text PRIMARY KEY
);

CREATE TABLE mixtures (
  mixture_id serial PRIMARY KEY
, chem_ids int[] UNIQUE  -- default NULL !
);

CREATE TABLE mixture_chems (
  mixture_id int REFERENCES mixtures (mixture_id)
, chem_id int    REFERENCES chemicals (chem_id)
);
INSERT trigger
CREATE OR REPLACE FUNCTION trg_mixture_chems_insaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE mixtures AS m
   SET    chem_ids = sort(COALESCE(m.chem_ids, '{}') + n.chem_ids)
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   new_table
      GROUP  BY 1
      ) n
   WHERE m.mixture_id = n.mixture_id;

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER mixture_chems_insaft
AFTER INSERT ON mixture_chems
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_insaft();

UPDATE trigger
CREATE OR REPLACE FUNCTION trg_mixture_chems_upaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE mixtures AS m
   SET    chem_ids = sort(COALESCE(m.chem_ids, '{}')
                        - COALESCE(o.chem_ids, '{}')
                        + COALESCE(n.chem_ids, '{}'))
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   new_table
      GROUP  BY 1
      ) n
   FULL  JOIN (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   old_table
      GROUP  BY 1
      ) o USING (mixture_id)
   WHERE m.mixture_id = COALESCE(n.mixture_id, o.mixture_id)
   AND   m.chem_ids IS DISTINCT FROM sort(COALESCE(m.chem_ids, '{}')
                                        - COALESCE(o.chem_ids, '{}')
                                        + COALESCE(n.chem_ids, '{}'));

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER mixture_chems_upaft
AFTER UPDATE ON mixture_chems
REFERENCING NEW TABLE AS new_table
            OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_upaft();

DELETE trigger
CREATE OR REPLACE FUNCTION trg_mixture_chems_delaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE mixtures AS m
   SET    chem_ids = m.chem_ids - o.chem_ids  -- assuming this does not upset sort order!
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   old_table
      GROUP  BY 1
      ) o
   WHERE m.mixture_id = o.mixture_id
   AND   m.chem_ids IS DISTINCT FROM (m.chem_ids - o.chem_ids);

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER mixture_chems_delaft
AFTER DELETE ON mixture_chems
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_delaft();

db<>fiddle here
This implementation is strict: a mixture with no chemicals (chem_ids = '{}') is just another case that is only allowed once. You may want to allow that multiple times instead. This state is only reached after deleting all existing components, newly inserted row in mixtures start out with chem_ids IS NULL to dodge this UNIQUE constraint.
And you may want to add a PRIMARY KEY constraint to disallow adding the same chemical to a mixture multiple times:
CREATE TABLE mixture_chems (
  mixture_id INTEGER REFERENCES mixtures (mixture_id)
, chem_id INTEGER REFERENCES chemicals (chem_id)
, PRIMARY KEY (mixture_id, chem_id)
);
But my implementation works either way.
Related:

Can PostgreSQL have a uniqueness constraint on array elements?
How to implement a many-to-many relationship in PostgreSQL?


using SUM on foreign view's keys grouped on multiple key/values? I had a question on executing SUM on rows from one table and inserting them in a column on another table where the calculated rows shared id with a row in the new table.

The solution worked very well for that instance. However, I have a similar situation in which that query no longer works.

I have this TABLE:

CREATE TABLE buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);

then this VIEW:

CREATE VIEW v_rooms AS
SELECT buildings.id AS building_id,
    buildings.name AS building_name,
    jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number,
    ((jsonb_array_elements(jsonb_array_elements(buildings.document -> 'levels'::text) -> 'rooms'::text) -> 'meta'::text) ->> 'sqft'::text)::numeric AS sqft
FROM buildings
GROUP BY buildings.id;

finally in this VIEW:

CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS number,
  sqft
FROM buildings

I want the COLUMN 'sqft' to be the SUM of all rows in v_rooms where v_rooms.building_id = v_levels.building_id AND v_rooms.level_number = v_levels.number.

I tried this VIEW definition:

CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS number,
  sqft
FROM buildings
JOIN (
  SELECT building_id, level_number, sum(sqft) as sqft
  FROM v_rooms
  GROUP BY ( v_rooms.building_id, v_rooms.level_number )
) v_rooms ON ( v_rooms.building_id = buildings.id and v_rooms.level_number = level_number );

but I get duplicates, one row for the matching building_id and then again every time level number matches. which is an issue because level number is not unique to the table and only to the building.

FIDDLE

You barely got it, once you have unnested the arrays you can simply aggregate the result by building.id, name and level in this way:

WITH ct AS
(
  SELECT
    buildings.id AS building_id
    ,buildings.name AS building_name
    ,jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number
    ,((jsonb_array_elements(jsonb_array_elements(buildings.document->'levels')->'rooms')->'meta')->>'sqft')::numeric AS sqft
  FROM
    buildings
)
SELECT
    building_id, building_name, level_number,
    SUM(sqft) AS sqft
FROM
    ct
GROUP BY
    building_id, building_name, level_number;

building_id | building_name | level_number | sqft
----------: | :------------ | :----------- | ---:
          1 | my blding     | 0            | 1300
          1 | my blding     | 1            | 1800

db<>fiddle here


Does PostgreSQL and other databases have a feature to reset serial/identity values? I recently started a new personal project. The first thing I do after thinking about it is to construct an install.php script which consists of a series of database calls where it does things like CREATE TABLE to "build the basic foundation".

When I felt reasonably confident about the structure, I ran that PHP script. From that point on, I instead used a GUI software to edit/add/delete columns and constraints, as I "fine-tuned" the database while developing the application.

For a while, I would edit the install.php for each change I did through the GUI, as to have them perfectly "synced", but after a while, as there were more changes, I said: "Screw it! This is only ever going to run on my computer anyway!" and deleted the install.php.

Now, I'm reaching some kind of "finished" state for the application, and realize that, after deleting the dummy records, various serials/bigserials are now starting at high numbers (instead of 0 or 1, as they do from the beginning). Of course, this means no practical problem, but it is psychologically very annoying to know that you started the "production application" with "non-vanilla" internal counters.

Is there a feature in PostgreSQL which automatically sees to it that any data remaining in the tables of the given database is "pulled back down" so they get ids like 1, 2, 3 instead of 6732, 6733, 6734, etc.? That is, not nuking the database/tables, but intelligently "resetting" them in this logical manner? I also wonder if other databases have this feature.

Note: I have foreign keys and primary keys and uniques and such all set up, so this should technically be possible without messing up the "relations" when the ids change.

I bet there isn't any such thing. Would be really nice if such is the case, though. Like a "database serial defragmenter" of sorts. It would even be sensible to run on a product database if you have deleted huge amounts of records for some reason, so that you never reach the maximum for a "serial" (integer).

In Postgres you can use ALTER SEQUENCE RESTART ...  or setval() function to change that "internal counter" without truncating the table.

See also http://www.sqlines.com/postgresql/datatypes/serial for examples.

In SQL Server you can use DBCC CHECKIDENT to see and reset identity value of the table.


Customize psqlrc to be different based on host or username Hi I would like to have my DB prompt a different color if I am logged into production or with a user with full permissions.

So I would like something like this (which doesn't work) in my psqlrc:

if [%M == PROD]; then
     \set PROMPT1 '%[%033[1;35m%]%M:PROD_IS_RED%[%033[0m%] %n@%/%R%#%x '
else
     \set PROMPT1 '%[%033[1;32m%]%M:NORMAL%[%033[0m%] %n@%/%R%#%x '
fi

Is there any way to make something like this work ? Is there some alternative can I make a script that runs psql with a different psqlrc file? like psql --psqlrc_file=psqlrc2

You can use \gset (introduced in 9.3) to query information about the server and save it in a psql variable, then set the prompt to include that variable.

select case when inet_server_addr()='10.0.0.34' then 'PROD' else 'TEST' end as host \gset
\set PROMPT1 '%:host: %/%x%x%x%R%# '

I don't know how you get this introduce color, rather than just text, in my hands your color control sequences are just presented literally.

If you want different .psqlrc files, you have to set it as an environment variable as their is no command line option.  So something like:

alias psql_prod='PSQLRC=~/.psqlrc_prod psql'


What are on.number files in MySQL? I got an alert this evening that one of our MySQL cluster disks was over 80% full - upon checking there are lots of "on.number" files created in the last 48 hours. Can anyone tell me what these are and if they can be deleted?

They look like:

-rw-rw----   1 mysql mysql 1073742020 Feb 10 02:25 ON.002859
-rw-rw----   1 mysql mysql 1073741930 Feb 10 02:53 ON.002860
-rw-rw----   1 mysql mysql 1073742080 Feb 10 03:24 ON.002861
-rw-rw----   1 mysql mysql 1073742072 Feb 10 03:53 ON.002862
-rw-rw----   1 mysql mysql 1073742001 Feb 10 04:20 ON.002863
-rw-rw----   1 mysql mysql 1073742052 Feb 10 04:49 ON.002864
-rw-rw----   1 mysql mysql 1073741889 Feb 10 05:17 ON.002865

The most recent one looks like:

-rw-rw----   1 mysql mysql        540 Feb 10 22:18 ON.index

At this rate, the disk will fill in around 24 hours so I need to try and figure out what these are, why they are being created and if I can remove them?

They are binary logs. The system variable expire_log_days can be used to limit their growth.

They are used for replication and point in time recovery. Except for these functions they are not needed for the functioning of the database.


Reorganize and Rebuild Indexes with active connections I want to rebuild or reorganize indexes in a table. What happens if there are active connections to that table at the moment?

I want to rebuild or reorganize indexes in a table

These are two different modes to remove index fragmentation.

Rebuilding an index means that a whole new set of pages is
allocated for it.

It cames in two flavours: online and offline. When rebuilding
offline your index is completely unavailable, but the operation is
faster than that online. It can be minimally logged (in simple
or bulk logged recovery models), and even when it's fully logged
it's logged very efficiently (the whole index pages when built are
written to log).

Online rebuild is available only in SQL Server Enterprise,
Developer, and Evaluation editions. The operation is always
fully logged and produce a notable amount of log as new index is
logged row by row. Besides, more exact term for it is "almost
online operation". Yes there is no lock held for the entire duration
of the index rebuild operation, but the locks are still held.

Here is a great explanation of Paul Randal:

Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

  Myth 5: online index rebuild doesn't take any locks

  This myth is untrue. The 'online' in 'online index operations' is a
  bit of a misnomer.  Online index operations need to take two very
  short-term table locks. An S (Shared) table lock at the start of the
  operation to force all write plans that could touch the index to
  recompile, and a SCH-M (Schema-Modification – think of it as an
  Exclusive) table lock at the end of operation to force all read and
  write plans that could touch the index to recompile.

  The most recent time this came up on the forums was someone noticing
  insert queries timing out after an online index rebuild operation had
  just started. The problem is that the  table lock that online index
  rebuild needs has to be entered into the grant queue in the lock
  manager until it can be acquired – and it will stay there until
  existing transactions that are holding conflicting locks either commit
  or roll-back. Any transaction that requires a conflicting lock AFTER
  the index rebuild lock has been queued but not acquired (and then
  released) will wait behind it in the lock grant queue. If the query
  timeout is reached before the transaction can get it's lock, it will
  timeout.

Reorganizing is designed to remove logical fragmentation from the leaf level of an index while
keeping the index online and as available as possible. When reorganizing an index, SQL Server acquires an Intent-Exclusive lock on the index B-tree. Exclusive page locks are taken on individual pages only while those pages are being manipulated. So you can consider reorganizing an online operation.


Can this business logic be enforced by a conditional database constraint? I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules.  This "rule" seems difficult to implement without using hacks.

CREATE TABLE CASE_STAGE
(
  ID                        NUMBER(9)           PRIMARY KEY NOT NULL,
  STAGE_ID                  NUMBER(9)           NOT NULL,
  CASE_PHASE_ID             NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  END_REASON_ID             NUMBER(9),
  PREVIOUS_CASE_STAGE_ID    NUMBER(9),
  "CURRENT"                 NUMBER(1)           NOT NULL,
  DATE_CLOSED               TIMESTAMP(6)        DEFAULT NULL
);

and

CREATE TABLE CASE_RECOMMENDATION
(
  CASE_ID                   NUMBER(9)           NOT NULL,
  RECOMMENDATION_ID         NUMBER(9)           NOT NULL,
  "ORDER"                   NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  CASE_STAGE_ID             NUMBER(9)           NOT NULL
);

ALTER TABLE CASE_RECOMMENDATION ADD (
  CONSTRAINT SYS_C00000
 PRIMARY KEY
 (CASE_ID, RECOMMENDATION_ID));

The business logic can be summed up as

When Inserting into CASE_STAGE
If CASE_STAGE.STAGE_ID = 1646
THEN
 CASE_STAGE.PREVIOUS_STAGE_ID must be found in CASE_RECOMMENDATION.CASE_STAGE_ID

Can this logic be embodied in a Check constraint or is an ugly trigger the only way?

Edit:

For all values of CASE_STAGE.STAGE_ID the value for PREVIOUS_STAGE_ID must be found in CASE_STAGE.ID
The application does not allow deletions from CASE_RECOMMENDATION once it is no longer CURRENT  ( ie when the value of CASE_STAGE.CURRENT is 0 this stage is closed and can no longer be changed, when = 1 this is the stage, or row, that is active and can be changed now.)

Edit: using all the excellent ideas and comments here is a working solution to this problem

CREATE MATERIALIZED VIEW LOG ON CASE_STAGE
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON CASE_RECOMMENDATION
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW CASE_RECOMMENDATION_MV REFRESH FAST ON COMMIT AS
  SELECT
         cr.ROWID cr_rowid, --necessary for fast refresh
         cs.ROWID cs_rowid, --necessary for fast refresh
         cr.case_id,
         cs.stage_id,
         cr.recommendation_id
         cr.case_stage_id,
         cs.previous_case_stage_id
  FROM   CASE_RECOMMENDATION cr,
         case_stage cs
  WHERE  cs.previous_case_stage_id = cr.case_stage_id (+)
  AND CS.PREVIOUS_CASE_STAGE_ID IS NOT NULL
  AND EXTRACT (YEAR FROM CS.DATE_CREATED) > 2010 --covers non conforming legacy data
  AND CR.RECOMMENDATION_ID IS NULL
  AND cs.stage_id =1646;
--this last line excludes everything but problem cases due to the outer join

ALTER TABLE CASE_RECOMMENDATION_MV ADD CONSTRAINT CASE_RECOMMENDATION_ck CHECK (
    (previous_case_stage_id IS NOT NULL AND case_stage_id IS NOT NULL)
);

When inserting a 1646 stage using existing packages without a recommendation the error was

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (APPBASE.CASE_RECOMMENDATION_MV_C01) violated
ORA-06512: at line 49

Job done!  Not what a materialized view was intended for but better than a trigger.

If CASE_RECOMMENDATION.CASE_STAGE_ID is unique, you can use referential integrity with a virtual column (11g+) to make it conditional:

alter table CASE_RECOMMENDATION ADD CONSTRAINT unique_case_stage unique (CASE_STAGE_ID);

-- virtual column only defined when stage_id=1646
alter table CASE_STAGE add
   (case_1646 as (case when stage_id=1646 then previous_case_stage_id end));

-- check that the virtual column is defined when stage_id=1646
alter table case_stage add
    constraint chk_1646 check ( stage_id!=1646 or previous_case_stage_id is not null);

-- referential integrity
alter table case_stage add
     constraint fk_1646 foreign key (case_1646)
     references case_recommendation (case_stage_id);

Let's check:

SQL> insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null, default);

1 row(s) inserted.

SQL> insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);

1 row(s) inserted.

SQL> -- fails because previous_case_stage_id is null
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null, default);

ORA-02290: check constraint (VNZ_TEST3.CHK_1646) violated

SQL> -- fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null, default);

ORA-02291: integrity constraint (VNZ_TEST3.FK_1646) violated - parent key not found

SQL> -- succeeds !
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null, default);

1 row(s) inserted.


Why is pg_stat_activity.query_start null for almost all queries? Why is pg_stat_activity.query_start null for almost all queries?  What is the meaning of a NULL query_start field?

The only meaning I know of for a NULL query_start field is that no query has ever been started on that connection.


how to implement JPA I have developed an application in Java that is registered on Google App Engine. I need to store some data on data store. But, I am completely unaware about its implementation.

Actually, I have implemented OAuth, so I want to store data of particular user who has logged in. Is it possible to separate out data stores for different users ?

Also, there is some quota limitation. How much data can we store for whole application ?

a quick search in google gave me this:

Using JPA with App Engine
Quotas

Also, in the datastore you can store only entities, this means that when you save some user data, it will be stored in the Users entity with all the other users data, what you have to do is implement relationships between entities like in a regular database (MySQL, SQL Server, etc.), check it out here:

Using the Datastore with JDO


Join two Json arrays to one with key and value I have two jsonb columns(keys,values).
Eg: keys colum value = ["key1","key2","key3","key4"]
values column = ["val1","val2","val3","val4"]
I want to write a select query to get the output as below based on the array index.
{"key1":"val1","key2":"val2","key3":"val3","key4":"val4"}

You need to unnest the arrays then aggregate everything back into a single JSON value:
select x.value
from the_table t
  cross join lateral (
    select jsonb_object_agg(k.ky, v.value) as value
    from jsonb_array_elements_text(t.keys) with ordinality as k(ky,idx)
       join jsonb_array_elements(t.values) with ordinality as v(value,idx) on k.idx = v.idx
  ) x

Online example


Bypassing LDAP for MongoDb Users Environment:

MongoDb: 3.0 Enterprise Edition

Linux: Red Hat Enterprise Linux Server release 6.4 (Santiago)

Use case:

We need to have the above three MongoDb users which are not present in LDAP (they need to be available for technical modifications)
We will have ~60 users which will be authenticated via LDAP

We need to be able to run MongoDb authentication and LDAP authentication in parallel.

We have installed MongoDb and created three different users on MongoDb:

admin
repAdmin
ROOT

Afterwards we have enabled LDAP configuration as per documentation on
http://docs.mongodb.org/manual/tutorial/configure-ldap-sasl-activedirectory/

After that we've added two LDAP Users to the MongoDb ("$external") for testing purposes.
Then we enabled the auth option in the MongoDb Configuration file and restarted MongoDb.
After we've enabled the auth option, all authentication is failing, LDAP as well as MongoDb.

Only the if the auth option is disabled the users which are present in LDAP are getting authenticated successfully.

Problem:
How can we authenticate the three users (admin, repAdmin and ROOT), which are not
available in LDAP, over the MongoDb Shell and all other users via LDAP?

Many thanks for your support :)

Solution:
the two different machanisms need to be added in the configuration. Like:

authenticationMechanisms=PLAIN,MONGODB-CR

After that, it's necessary to create the users which should be authenticated via LDAP as followed:

db.getSiblingDB("$external").createUser({ user : "syranno", roles: [ { role: "readWrite", db: "testdb" } ] } )

By adding the user to $external, MongoDb knows to authenticate this user against LDAP.


set session wait_timeout inside of phpmyadmin for export/import I need to increase wait_timeout variable in mysql only when access mysql from phpmyadmin. I don't want to edit my.cnf file or set it globaly in mysql server.

I've read documentation, but haven't found if I can run any queries after connection to specific database.

Any help is appreciated.

Application details below:

  phpMyAdmin version 4.4.1

  mysql is percona 5.6.

File:

libraries/dbi/DBIMysqli.class.php

138     public function connect(

before return link you can run query:

223         $link->query("SET SESSION wait_timeout = 600");
224         return $link;

This would set any session to mysql db from phpmyadmin with wait_timeout = 600.


Should I split timestamp parts into separate columns? I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.

At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.

id                       | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 |   4   | 13  |  15  |    0    |   19

vs

id
-------------------------
Fri Apr 13 2018 15:00:19

My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:

SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/

I would appreciate some best practices input on this.

Keep the timestamp and don't add columns for the parts.

If you need to search for part of a timestamp, you can always create indexes on extract expressions.

Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.


mysql database, 2400 records for 1500 rows I need to create a new database, recording data for around 1500 sensors.

I need to store hourly data, and keep a rolling database for the past 100 days.

My first thought was to create a database with one row for each sensor (so around 1500 rows), and one column per hour - so 2400 columns.

Reading this other SO question (Too many columns in MySQL), this is clearly a bad idea!!

So how can I best organise and set up my table, to store 2400 data points for each of 1500 sensors, while creating a manageable table that is efficient?

Rolling 100 days -- I recommend using PARTITIONing and break it into weekly partitions.  It will be about 17 partitions.  Details:  http://mysql.rjweb.org/doc.php/partitionmaint .  DROP PARTITION is much faster than DELETE.

It is possible to map between "hour" and "datetime" (or "timestamp") with some arithmetic involving FLOOR( ... / 3600).  Then that will fit nicely in MEDIUMINT UNSIGNED and replace 3 of Vérace's columns.

Don't have an AUTO_INCREMENT if you have a 'natural' PK.  Suggest

PRIMARY KEY(sensor_id, hour)

and

PARTITION BY (hour)

You may, but probably won't, need

INDEX(hour, ...)


MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated I am trying to create a trigger so that when a column in a table called bookings is updated it will update a a calculated price value in table departments. I currently have my tables set up as so

CREATE TABLE bookings    (
    idBookings int(4) AUTO_INCREMENT,
    idMember int(4),
    departure_date DATE,
    idVehicle int (2),
    location VARCHAR(45),
    checkout_signed_faculty BOOLEAN,
    checkout_signed_tfbs BOOLEAN,
    odemeter_start int(5),
    odemeter_end int(5),
    maintenance_needed BOOLEAN,
    fuel_purchased DOUBLE(3,2) NOT NULL,
    card_number BIGINT(16),
FOREIGN KEY (idMember) REFERENCES faculty_members(idMember),
FOREIGN KEY (idVehicle) REFERENCES vehicles(idVehicle),
FOREIGN KEY (location) REFERENCES locations(location),
    PRIMARY KEY (idBookings)
);
CREATE TABLE vehicle_type (
    type VARCHAR (20),
price DECIMAL(3,2)
PRIMARY KEY (type)
);
CREATE TABLE vehicles (
    idVehicle int (2) AUTO_INCREMENT,
    type VARCHAR (20),
    registration VARCHAR(7),
    price DOUBLE(2,2),
PRIMARY KEY (idVehicle)
);
CREATE TABLE departments (
department VARCHAR (20) NOT NULL,
owed_to_tfbs DECIMAL(3,2) NOT NULL,
PRIMARY KEY (department)
);
CREATE TABLE faculty_members (
    idMember int (4) AUTO_INCREMENT,
    member_name VARCHAR(45) NOT NULL,
    department VARCHAR(20),
PRIMARY KEY (idMember),
    FOREIGN KEY (department) REFERENCES departments(department)
);

And my trigger contains a lot of joins (I am unsure whether this is because of a possible flaw in the design) but is as so...

DELIMITER $$
CREATE TRIGGER owed AFTER UPDATE ON bookings FOR EACH ROW
IF NEW.odemeter_end <> OLD.odemeter_end THEN
UPDATE departments
   left join faculty_members on departments.department = faculty_members.department
   inner JOIN bookings on faculty_members.idMember = bookings.idMember
   inner JOIN vehicles on vehicles.idVehicle = vehicles.idVehicle
   inner JOIN vehicle_type on vehicles.type = vehicle_type.type
SET departments.owed_to_tfbs = ((((NEW.odemeter_end - NEW.odemeter_start)
    * vehicle_type.price)
    + departments.owed_to_tfbs) - bookings.fuel_purchased)
WHERE NEW.odemeter_end <> OLD.odemeter_end;
END IF
$$

While the calculation is correct it is applying it to all the owed columns that appear in the department table that has a booking. I am unsure as to how to only apply this to only the updated rows
Can any body help or offer any advice? I so far cannot find anything that works

Triggers are procedural, so my inclination would be to perform the join and update as separate steps; this IMHO would make for simpler and more readable DML.This code should do what you want:

DELIMITER $$

CREATE TRIGGER owed
    AFTER UPDATE ON bookings
    FOR EACH ROW
  BEGIN
    DECLARE dept VARCHAR(20);
    DECLARE rate DECIMAL(3, 2);
    IF NEW.odemeter_end <> OLD.odemeter_end THEN
      SELECT fm.department, vt.price
        INTO dept, rate
        FROM faculty_members fm, vehicles v, vehicle_types vt
        WHERE NEW.idMember = fm.idMember
          AND NEW.idVehicle = v.idVehicle
          AND v.type = vt.type;

      UPDATE departments
        SET owed_to_tfbs = (((NEW.odemeter_end - NEW.odemeter_start) * rate)
                           + owed_to_tfbs - NEW.fuel_purchased)
          WHERE department = dept;
    END IF;
  END;
$$

DELIMITER ;

Note that I haven't tried to compile this, so I make no promises regarding syntax errors, but the semantics are sound. Also, I've used a WHERE-clause join instead of a JOIN-clause join, as I'm not sure you can use the latter to join on NEW.


Tips for Optimizing Oracle Warehouse Builder 11.1 with Oracle DB 10.2 Are there any tips for improving performance of Control Center and Design Center in Oracle Warehouse Builder? At the client side it is very slow to open and refresh Design center and control center and table/dimension/cube mapping deployments made to a remote database are also slow. Several users (<30) over the network are accessing OWB remote repository and make deployments to remote Oracle 10.2 DB using locally installed Design Center and Control Center.

Please suggest any performance optimization measures at Oracle DB level and at OWB level for the administrator.

Make sure that open_cursors is sufficient. What is the current value for open_cursors and session_cached_cursors? They should be high, start with 500.


ODBC Connection As Clustered Resource First step in a long process of creating a Linked Server to an Informix database on a Sql 2008 (Win 2003 x64) cluster.

I was wondering how to go about creating an ODBC connection as a clustered resource, or if I even have to do that.

The OLE DB provider "MSDASQL" for linked server "__server__" reported an error. The provider reported an unexpected catastrophic failure.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "__server__". (Microsoft SQL Server, Error: 7399)

More info: Using IBM's OLEDB and ODBC drivers. It worked on a dev instance but not on a nearly identically-configured prod instance.

The linked server "lives" in the clustered SQL Server instance, so whichever node the SQL Server happens to be running on, the linked server should run fine....as long as you've installed the required ODBC/OLEDB driver(s) on each node.

Looking at that error, I'm assuming the linked server doesn't work at all on any node. I suspect you'd have the same problem on a standalone SQL Server too - it seems to me to be a driver issue. Whose OLEDB driver are you using?

I'm sure you've read the following IBM KB:
http://www-01.ibm.com/support/docview.wss?uid=swg21195578


Concurrently update with limit mysql I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db.

After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned.
I don't want clients to overlap results, so every client will have different records each time the query the db with the SELECT after the update.

This depends on table engine?

The update locks the table/records? (I am sure this one depends on engine).

From my opinion UPDATE LIMIT should not overlap results, dbs have ACID property...I want to confirm this.

This depends on table engine?

Yup. MyISAM will lock the table, InnoDB will lock rows

If you're looking for ACIDity, you'll want to be using InnoDB. How InnoDB handles locking is described in this doc page

The following user comments on the above document page illustrate a flaw in InnoDB performance on large tables:

  Major gotcha: Rows are locked during updates using indexes. For example, UPDATE tab SET col1=3 WHERE col2=17; will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).

and

  To expand on the above comment, any operation that does a table scan for update/delete will lock all the rows in the table

Extra curricular reading:

  TL;DR: InnoDB index lock is major architectural performance flaw, and that is why you hear that large tables are slower. There’s a big chance that there’re more scalable engines for on-disk writes out there, and all the large InnoDB write/insert benchmarks were severely hit by this.


How to Recover truncate table in MySQL? I am getting the "wrong input table name" when use TRUNCATE as following:

TRUNCATE `stats`

The table should bans.

How to recover stats table?

I am using CentOS 6, and MySQL-Server.

Truncate Operation Drops the existing table and recreates it .its a fast process . so we cannot get the deleted records.

Solution to this issue is to do a regular backup of the DB on a a daily or weekly basis , in order to save or restore the tables data  as per requirement . and reuse it

Another solution is to export the table data before truncate and keep the data for Future reference


Effect of TRUNCATE TABLE different from DELETE on subsequent queries I always thought of TRUNCATE TABLE as an equivalent of DELETE, except that:

1) TRUNCATE can be used only in some cases (no FK referencing table, user must have ALTER permission etc)

2) TRUNCATE is faster to execute

Apart from that, I expected them to have identical results.
But from my experience, it seems that a TRUNCATE improves the performance of subsequent queries over a DELETE.
I have a script that clears some tables, then fills them with millions of rows from another database on the same server, like:

INSERT NEWDB..newtable(a,b,c) SELECT a,b,c FROM OTHERDB..oldtable

Switching the clearing of tables from DELETE to TRUNCATE TABLE made the deletion much faster (as obvious) but also made the subsequent data copying at least 10 times faster.
Interesting to note, the recovery model for the database is SIMPLE, so it is not a matter of logs.
Can someone explain why this is happening? Or maybe I am missing something and the gain in speed is caused by some other factor?

OTHERDB..oldtable --> the data could be in buffers from the first run.

Try running

DBCC DROPCLEANBUFFERS --Not in production though !!

and then your insert query.


MongoDB 3.2 replica set with arbiter version 2.6 I have to set up a MongoDB replication set with 3 nodes, 1 of them arbiter. Authentication has to be enabled. Node1 and Node2 are fine. On the arbiter I have a 2.6 installation and it is arbiter for another configuration. I started a new mongod on that server on a different port and tried to add it to the 3.2 replication set. It is added but it says "not reachable".

From any node I can connect to the new instance in the arbiter host so communication between servers is not a problem. I don't see anywhere in the documentation that all the nodes must be the same version.

Any ideas what am I doing wrong? I could add a 3.2 arbiter with no problem and the replica set is working perfectly, but for some reason I cannot get it to work with a 2.6 arbiter.

In general MongoDB only supports mixing adjacent major versions (eg. 2.6 and 3.0, 3.0 and 3.2, 3.2 and 3.4) in a replica set for the purposes of a rolling upgrade to a new version of MongoDB. Mixed version requirements are currently only mentioned in the upgrade notes but I expect the underlying assumption is that new deployments would always use start with the same version.

If you create a new replica set deployment using MongoDB 3.2, the replica set will be using a new election protocol ("version 1") that older versions of MongoDB do not support. For more information, see: Replication Election Enhancements.

The proper solution is to add a MongoDB 3.2 arbiter to your new 3.2 replica set. For any new deployment I would definitely recommend using identical versions of MongoDB to avoid unexpected issues.


How to enforce uniqueness of compound primary key made up of two foreign keys to the same table? I have two tables: a user table and a friendship table. Say the friendship table looks like this:

friendship
------------
user_one_id
user_two_id
other_fields

I need to enforce the uniqueness of the combination of values of (user_one_id, user_two_id) and disregard the ordering, so:

user_one_id | user_two_id
------------+------------
          1 |          2
          2 |          1  -- the DBMS should throw a unique constraint error when trying to insert a row like this one.

Another important point is that user_one_id represents the initiator of the friendship and user_two_id represents the recipient, so I cannot just make the "smaller" of the two ids user_one_id.

The question

Is there a way to do this using constraints or should I implement this some other way?

In light of your comment,

  For my use case, user_one_id represents the initiator of the friendship, and user_two_id represents the recipient of the friendship. So I can't just use the lowest value as user_one_id.

Well, you can still do it. Your use case just excludes a row-constraint to ensure that. What you want is to use a table-constraint, something like this.

CREATE TABLE friendship (
  user_one_id int NOT NULL,
  user_two_id int NOT NULL,
  CHECK (user_one_id != user_two_id ),
  PRIMARY KEY (user_one_id, user_two_id)
);
-- you can do least first if you want. doesn't matter.
CREATE UNIQUE INDEX ON friendship (
  greatest(user_one_id, user_two_id),
  least(user_one_id, user_two_id)
);

We have a lot going on here.. We make sure.

Both are NOT NULL
Both are not equal to each other
Both are UNIQUE (user_one_id, user_two_id)

That leaves one remaining problem of commutative uniqueness we solve that with a custom unique table-constraint implemented with an index.

Proof in the pudding

INSERT INTO friendship VALUES ( 1,2 );
INSERT 0 1
test=# INSERT INTO friendship VALUES ( 2,1 );
ERROR:  duplicate key value violates unique constraint friendship_greatest_least_idx"
DETAIL:  Key ((GREATEST(user_one_id, user_two_id)), (LEAST(user_one_id, user_two_id)))=(2, 1) already exists.

As an important friendly note your names are all kinds of silly. The relationship is fine. In production, please give them better names..

friendship
----------
request_initiator
request_target
other_fields


SELECT with comparison operator, based on an order, skipping row(s) I have a resultset from a SELECT query, based on a certain ORDER BY condition, and I’m trying to check the integrity of the order in which the resultset is rendered. First, I’m specifying a LIMIT, on the basis of which, the resultset size is limited. Then, I’m selecting a random PK value from the resultset, get a particular row and use the comparison operator on that row value(s), for the next SELECT. Something like this(Let’s suppose a is the random row from above):

SELECT * FROM tbl where tbl_columnX > a.fieldX and tbl_columnY > a.fieldY
ORDER BY certain fields

Basically, what I’m trying to do here, is to ensure that for a resultset (R) of certain order, when choosing a random PK value, and issuing a SELECT statement with comparison operator(s) on that PK based row value(s), the resulting set(S) contains the rows that are in R, after/before the PK, depending whether the order is ascending or descending.

It is basically working, except for a few cases where the next row(s) after the PK based row, have the same value(s) as the PK based row, in a few column(s). For example, these are the last few rows, from the resultset R:

ID(varchar)     VALUE(varchar)  DESC1(text)     DESC2(text)     NAME(text)
-------------   -------------   -------------   -------------   -------------
62E212AF            C09A        F/T/K              AH            AN
CAFFA217            C09AA       F/T/K 2            Ah            An
D90222CC            C09BA       F/T/K 2d           Ahod          Anod

Now, if I try to get a new resultset(S) after the ID 62E212AF, it skips the ID CAFFA217, and the first ID in the resultset is D90222CC. The similar pattern is repeated in a few other places. This is how I’m doing the order by, in this particular case:

order by
  NAME asc,
  VALUE asc,
  DESC1 asc,
  DESC2 asc,
  ID asc

I’d really appreciate some help with this.

All sorting are alphanumeric, and follow the ascii code
that means

1 decimal representation 49
N decimal representation 78
n decimal representation 110

1 comes before N and that before n and so on, so far as i can tell it is sorting correctly.

This link shows how you can sort the text "correctly"


Designing an aggregation schema The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it might give me some insight on how to solve my own similar problem.

How would one design a schema in a MySQL database to hold all the activities of a specific user. In FB's case, you have many activities that a user might be able to do, like something, comment on something, add a friend, use an app, etc. All these activities have their own database schema, I'm sure, so I'm assuming that the activities log schema would need to reference these other data somehow.

So what I'm asking is for ideas. The only ideas I can come up with is to create an activities table and multiple "connecting tables", tables that reference the activities table and the activity data (e.g. comments on something, likes something, etc.). However, this schema would require a new table for every new kind of activity and new business logic to handle these new tables. Maybe one can't get around this, but I'm interested in a schema that would require very little updating to the schema and business logic when new kinds of activities are added to the system.

Just to be clear, this question isn't intended to sound like I'm just simply trying to build another Facebook clone. I only use FB as a reference to something similar to what I'm trying to do. To be more abstract about my question: I'm trying to aggregate various kinds of data in my database into a single schema that could be queried, and anytime there is a new kind of data, there should little if any schema changes, otherwise what's the point? FB has two instances of this kind of aggregation, activity logs and notifications (that I can think of).

Any help is appreciated.

Relational DBMS is not ideally suited to this type of application.  RDBMS is great for tracking and enforcing referential integrity for relations between two entities, THING A and THING B.  What you want to do is to track relationships between THING A and every other thing in your database (or lots of other things, at least).

Your options, each of which include some form of compromise, include:

Sparse Foreign Key Columns: Create an activity table that has mutually exclusive foreign key columns that point to the various kinds of activity that you want to track.  This is a fully 3NF way of doing things, but it still requires new columns to be added for new kinds of activities and it results in sparse columns, which people try to avoid as a rule.
Forrest of Tables: Create individual activity tables each of which track a particular type of activity.  You can then create a query or view that uses UNION ALL to bring the various kinds of activity together into one place for querying.  This is also very by the book with respect to normal forms, but you end up with a database cluttered with activity tables and you have to create a new table and alter a view whenever you add a new type of activity.
Data Driven Joins: Use a single activity table that captures the type of the activity and the instance of the activity in one place.  You would need to have more sophisticated logic built for retrieval of the activity details, since this approach foregoes formal foreign keys.  There is no declarative referential integrity and the approach smells a lot like EAV, which makes many people's skin crawl.  On the other hand, you wind up with a single table, no sparse columns, and the addition of a new activity type requires no schema changes, only a tweak to one piece of code - the one that reads type of activity and knows what to do to go and retrieve the details of that activity.

You need to look at where your sensitivities are going to lie.  How many types of activity will you ultimately be tracking?  How often are new types of activity going to be added to the mix?  Are you prepared to endure the scorn of colleagues when they see you've implemented a solution with sparse columns (or worse) EAV?  When you answer these questions you'll be able to pick the approach which makes the most palatable compromises for your application.


Which system databases should I checkdb and defrag indices? I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented.  These are typical maintenance best practices.  I'm wondering which of the system databases I should apply these jobs to?  Right now I run CHECKDB only on master, and I vaguely remember setting it up because I saw that in a book somewhere.

So, for which of the following should I perform CHECKDB and/or index defrag?

master
model
msdb
tempdb

All system and user databases should be check using CHECKDB. The only exception will be tempdb as you cannot generate a snapshot of tempdb and checkdb behind the hood generates a DB Snapshot.

The tricky part is for tempdb (When Tempdb is corrupted) as we ALL know that it gets recreated when a SQL Server restart is done, but Tempdb files are not deleted when you restart SQL Server and are also not 0 (zero) initialized so the corruption can persist.

Tempdb corruption (which is rare .. and most likely will be due to a bug in SQL Server) is described here.


Mysql error while creating FK here is structure of both tables

category table
CREATE TABLE category (
`catID` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(60) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`catID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

and the second table projects

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`industryID` int(11) NOT NULL,
 `dsID` int(11) NOT NULL,
`countryID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
KEY `industryID` (`industryID`),
KEY `dsID` (`dsID`),
KEY `countryID` (`countryID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now to add FK to tables here is the query but i keep getting the error

Can't create table .... (errno: 150)
ALTER TABLE `projects`  ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID)  REFERENCES category (catID)

When you create a foreign key between two tables the data type of the columns that are taking part in the relationship must have the same data type. In the example you have posted above it is failing because the Categories table defines CatID as a BIGINT wheras in the Projects table you have defined it as a INT. Changing the data type of CatID in the Projects table to BIGINT should resolve your problem.

I hope this helps you.


MySQL shows CPU usage over 40% though total CPU usage is less than 30% as per TOP - How to debug this issue? Not sure if I am reading this incorrectly. In the below screenshot of TOP command, it shows that mysql process CPU usage is 41.2% but at the 4 core CPU shows a total of 26% usage.

is MYSQL not configured correctly? Or is MySQL not using all cores? How do we ensure that MySQL is configured properly and uses the resources optimally?

Interpreting the readings from top can be confusing and the details are a bit off-topic for here (perhaps ask questions on https://superuser.com/ if you need more information than you can find elsewhere) so I'll not delve too far, but...
Firstly: the readings in top and similar programs can be misleading as they are only samples. It takes "how many cycles used" for processes at the start of the loop, waits, then takes those counts again, and uses the differences to work out how much each process has used in that second (or longer if you have it set to update slower). There are various issues with this: it will completely miss short lived processes that start and stop in the intervening time, on a really busy system it can miscount because its loop to read the state of each process takes a little while to run through (so the reading for process 123 is not taken at the same instant as that for process 124, etc.). This also means that the total CPU used if you tot it up from the list could be quite different sometimes to the amount used shown in CPU summary lines, for instance indicating more is used than the summary suggests. tl;dr: take any figure presented for a process as a sample or estimate, not an absolutely correct & precise fact.
The CPU used per process is not "% of total" but "% or a CPU core". So that sample says mySQL used 41.2% of available CPU time in the period things were counted for which is 10.3% or your 4-core processor. The rest of that 26% will be other processes doing work or waiting in "IO wait" states. The CPU time it used could be all from one core, or more likely spread around them all. You will sometimes see processes taking more than 100% because they are used more CPU time than is offered by a single core during the sampling period. tl;dr: make sure you know what are per-system, per-CPU, and per-core figures.
You don't say where you get the 26% figure from - that isn't displayed in your screenshots. If you are reading that from another program then it probably isn't based on the same time period as the figures shown in top so the comparison is invalid anyway.
tl;dr: the CPU % figures you have may no mean anything useful =.

is MYSQL not configured correctly?

What makes you think that it might not be, with regard to mySQL's behaviour not just these figures?
Is something running slowly?
Are you expecting it to be busy at that moment so using more CPU time?
If you are not currently asking it to do much work, it won't be using much CPU time. It won't use it just because it is available.


updateable table from function I have a plpgsql function that returns a table. One of its columns is dynamically generated based on the parameters passed to the function. Is there a possibility to have the user have the ability to update the non-dynamically generated columns of that returned table, like with an instead of update trigger with a view?

Here's a simplified version:
In reality, the function is a lot more complex, and the value of the calculated column for the currently processing row is dependent on the previous rows, which is why I have to use a plpgsql function.

create table test(id integer, startdate date);

CREATE FUNCTION test2 (IN compare_date date)
    RETURNS TABLE(id integer, startdate date, is_before boolean)
    LANGUAGE plpgsql
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT id, startdate FROM test LOOP
        IF rec.startdate < compare_date THEN
            test2.is_before := true;
        ELSE
            test2.is_before := false;
        END
        test2.id := rec.id;
        test2.startdate := rec.startdate;
        return next;
    END LOOP;
END;

The user just accesses the function, I want him to be able to update the columns in the original table. With a view, this could be done using a trigger, but I can't have dynamically generated columns in a view, can I?

Your function doesn't return a "table" in the usual sense. As noted in the docs, RETURNS TABLE (...) is just a syntactic shortcut for RETURNS SETOF record, plus a bunch of OUT params.

The output of the function is just a collection of values in memory, with no connection to the source of the data, so while callers can transform those values as they please, there is no way to propagate anything back to the original table via the function output.

If you want a different presentation of the underlying data which the user is still able to apply updates to, the only way is with an updatable view. Including generated columns should be no problem, as long as your trigger is written to handle these appropriately. The main obstacle in your case is that, unlike a function, you cannot (directly) pass parameters to a view.

The only workaround that I know of is to define your view in terms of a custom configuration variable, and require that users set this variable before querying the view, e.g.:

CREATE VIEW test2 AS
  SELECT *
  FROM test2(current_setting('my_vars.compare_date')::date);

SET my_vars.compare_date TO '2000-01-01';
SELECT * FROM test2;

Your UPDATE trigger may also query this variable if needed.


Query produces two query plans (at different times). How do I always produce the better query plan? I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.

I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.

The query plans are very similar (and big) so I will include a screenshot of only the parts that are different.  I can post the entire XML if requested.

Partial query plan - before flush (bad performance)

Edited: Link to full query plan (sorry, it doesn't seem to have rendered as well as some query plans I've seen)

Partial query plan - after flush (good performance)

Edited Link to full query plan

Query

SELECT Records.[Id],
   Property.Id AS [PropertyId],
   CASE
       WHEN Records.Id IS NOT NULL THEN Records.[Name]
       ELSE '(In Transit)'
   END AS [Name],
   Records.[Area],
   Records.[AreaUnitId],
   Records.[Lng],
   Records.[Lat],
   Records.[Zoom],
   Records.[Code],
   Records.[Guid],
   [Property].[SecurityId],
   Records.[DateCreated],
   Records.[Modified],
   Records.[DateModified],
   Records.[ModifiedByUserId],
   Records.[SortOrder],
   Records.[Deleted],
   COALESCE(Property.Name, '') AS PropertyName,
   COALESCE(Unit.Name, '') AS UnitName,
   COALESCE(Unit.Code, '') AS UnitCode,
   COALESCE(AggregateData.Head, 0) AS Head,
   COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
   CASE
       WHEN COALESCE(AggregateData.Head, 0) > 0 THEN AggregateData.TotalWeight/AggregateData.Head
       ELSE 0
   END AS AverageWeight
FROM
(
    SELECT Pen.Id,
       Pen.PropertyId
    FROM Pen
    LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
    WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
    AND (@Deleted IS NULL
      OR Pen.Deleted = @Deleted)

   -- Add null row per property, so animals in transit are included further down
   UNION ALL
   SELECT 0 AS Id,
      Property.Id AS PropertyId
   FROM Property
   LEFT JOIN [Security] O ON Property.SecurityId = O.Id
   WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND Property.[External] = 0
   AND Property.Deleted = 0
) AS PenIds
LEFT JOIN
(
   SELECT L.PropertyId,
       COALESCE(L.PenId, 0) AS PenId,
       COUNT(M.BeastId) AS Head,
       SUM(WeightLog.[Weight]) AS TotalWeight
   FROM BeastMaster M
   LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
   LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
   LEFT JOIN [Security] O ON L.SecurityId = O.Id
   WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND (@Soh IS NULL
      OR M.Soh = @Soh)
   AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)
   GROUP BY L.PropertyId,
        COALESCE(L.PenId, 0)
) AS AggregateData ON PenIds.Id = AggregateData.PenId
   AND PenIds.PropertyId = AggregateData.PropertyId
LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
LEFT JOIN Property ON PenIds.PropertyId = Property.Id
LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
WHERE (@Id IS NULL
   OR PenIds.[Id] = @Id)
AND (@PropertyId IS NULL
   OR PenIds.[PropertyId] = @PropertyId)
AND
(
   @ManagementAreaId IS NULL
   OR EXISTS
   (
      -- Properties linked to Management Area
      SELECT *
      FROM [Permission] Pm
      WHERE Pm.Deleted = 0
        AND Pm.UserGroupId = @ManagementAreaId
        AND Pm.RequestedSecurityId = Property.SecurityId )
)
ORDER BY Records.Name

If you were on 2016 or higher, you could have used Query Store to track your plans and force the good one through Query Store functionality. You can still force the plan,though, easiest is probably sp_create_plan_guide_from_handle: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-from-handle-transact-sql?view=sql-server-2017 .

Other options could be to use OPTIMIZE FOR or even RECOMPILE query hints, depending on the circumstances and what suits you best.


How does SSMS display varbinary data? When you store a .jpg file in a varbinary(max) column and then view the data in SQL Server Management Studio, it appears like "0xFFD8FFE000..."
Is this Hex Encoded or something?  What is SQL Server doing to generate this string?  Can it be converted to a valid JPG file?

It's binary data with some additional Hex characters for padding, etc per the Microsoft Docs: binary and varbinary

Padding is achieved by using hexadecimal zeros.


InnoDB Cluster Read-Only Lag Behind Writer For software testing purposes, I'm using MySQL v5.7 and MySQL Router with a sandbox deployment of an InnoDB cluster with one read-write instance and two read-only instances.

I'd like to be able to determine the amount of time between when data is written to the read-write instance and when that data is available in a read-only instance.  From what I read, it sounds like SHOW SLAVE STATUS it what I'm looking for; it returns Seconds_Behind_Master.  Unfortunately that command returns zero rows.

The cluster configuration is whatever the MySQL wizard created for me which appears below for one of the instances.  The other two instances have different port numbers.

[client]
protocol = TCP
port = 3310
user = root

[mysqld]
transaction_write_set_extraction = XXHASH64
disabled_storage_engines = BLACKHOLE,FEDERATED,ARCHIVE
binlog_checksum = NONE
gtid_mode = ON
server_id = 1746145354
auto_increment_offset = 3
basedir = C:/Program Files/MySQL/MySQL Server 5.7
port = 3310
datadir = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/sandboxdata
log_error = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/sandboxdata/error.log
pid_file = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/3310.pid
log_bin
loose_mysqlx_socket = mysqlx.sock
log_slave_updates = ON
relay_log_info_repository = TABLE
secure_file_priv = C:/Users/mjk99/MySQL/mysql-sandboxes/3310/mysql-files
master_info_repository = TABLE
binlog_format = ROW
plugin_load = mysqlx.dll
enforce_gtid_consistency = ON
loose_mysqlx_port = 33100
socket = mysqld.sock
report_port = 3310
log_syslog = OFF

Is there a setting here that's my problem or does SHOW SLAVE STATUS just not work with this setup?

SHOW SLAVE STATUS is only applicable to master/slave replication channels.

For Group Replication, you would look at the Replication_group_member_stats table. In particular the two queue sizes, the certification queue and the applier queue. The applier queue length tells you how many transactions behind this node is in applying the transactions which the group has agreed/promised to apply (in the same order) through the consensus protocol.

That column is new in MySQL 8. You can see how I calculated it by hand here in 5.7 (the gr_applier_queue_length() function).

We also plan to add the applier queue length (lag indicator) to the status output in the MySQL Shell AdminAPI.


I'm a MS SQL DB Developer looking to get more permissions on the server So I am trying to use Visual studio and I can't import the DEV database into a project. I run into this issue a couple times a week since I am either creating SSIS Packages, or building a database from scratch, or creating SSAS models that require me to hit many databases, create job schedules....

My current error is that I can't reverse engineer the DEV database in Visual Studio because I don't have view definition. This is probably one of the more frequent issues I run into. I had the same error when I tried to see an Execution plan.

So based on this article http://sqlmag.com/database-security/security-options-developers-devsandbox-environments
I am wondering if Grant Control on Schema will cover everything I need.

Another issue that comes up is that I can't query the database to see who might be locking my query. Running SP_who2 only tells me what I am doing. It is quite frustrating to have to turn in a ticket and wait for a DBA to figure out the issue. What right do I need to have to run SP_WHO2 or run a query to see what is hitting the system.

And should I not be able to kill my own SPID when something goes awry? The stop button in SSMS does not always work.

I'd also like to run Brent Ozar's spBlitz tools so I can tune my own queries. Should that not be a developer's task. I'd like to know if I create an index and it is ever hit and how often. Or should I include a column I need in another covering index.

Thoughts? Will Grant Control give me everything I need as a Database developer, not to be confused with a report writer, or someone writing basic procs for an app.

I am wondering if Grant Control on Schema will cover everything I
need
Will Grant Control give me everything I need as a Database developer.

But you didn't write what exactly you need as database developer.
If you update your question with your tasks to accomplish, I'll answer on that. For now I can give you only general answer.
Having CONTROL on schema you have access to this schema objects only, i.e. you can

SELECT INSERT UPDATE DELETE and ALTER tables (but you cannot CREATE
new table because this requires CREATE TABLE permission)
ALTER and EXECUTE modules (but not CREATE new ones because you don't
have CREATE PROCEDURE/VIEW/FUNCTION permission)
GRANT permissions on your schema objects to user but not CREATE USER

You are still lacking SHOWPLAN permission having only CONTROL ON SCHEMA
and of course you continue to see only your own sessions using sp_who2
To see all the sessions on the server you need VIEW SERVER STATE server level permission.
To be able to see plans you need SHOWPLAN permission in all databases involved in your query (so if you use temp tables you cannot see the plan if you have no SHOWPLAN in tempdb)
To be able to create jobs you must be a member of SQLAgentOperatorRole
To be able to kill any session you need ALTER ANY CONNECTION server level permission.
To use stop button in SSMS you don't need any special permission

I'd like to know if I create an index and it is ever hit and how often

To be able to select from sys.dm_db_index_usage_stats you need VIEW SERVER STATE permission.
.............
It seems to me I touched all the actions mentioned in your question, if I miss smth please ask in a comment or update your question


How is my database being backed up? I'm using SQL Server 2008 R2.  Someone else set everything up.  I encountered an error today: Autogrow of file 'ASPState_log' in database 'ASPState' was cancelled by user or timed out after 1748 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

This was a very large transaction log (much bigger than the table) trying to grow and as a result it shutdown our website.  The recovery model is set to Full but the logs are not being backed up and truncated.  But somehow the database is being backed up nightly as there is a timestamp under Last DB Backup.  However, under Management there are no maintenance plans...so I'm not sure how the DB is getting backed up nightly unless there is an outside task or server doing it (which I couldn't find).

Should I do a full backup including logs and then truncate them?  And where can I find how I am currently being backed up?  Thanks, my first question.

In general you should have full backups set on one schedule, and transaction log backups set on another (more frequent) schedule, so yes do both of these as soon as you can.

The issue you will run into is if another process is performing backups without "COPYONLY", as this will through off sequencing (making a full point in time restore impossible unless you can track down the location of the mystery backups).

The code snippet below will search the default trace for all BACKUP activity and should allow you to track things down.

SET NOCOUNT ON;
--this version reads all trace files
declare @path  nvarchar(100)
set @path = (select top 1 [path] from sys.traces where is_default = 1)
set @path = (select reverse(right(reverse(@path), (Len(@path) - (PATIndex('%[_]%', reverse(@path)))))) + '.trc')

SELECT  STE.name AS EventClassName,
       ST.StartTime ,
        ST.LoginName ,
        ST.HostName ,
        ST.ApplicationName ,
        ST.TextData
FROM    sys.fn_trace_gettable((@path), DEFAULT) ST
INNER JOIN sys.trace_events STE ON ST.EventClass = STE.trace_event_id
WHERE   TextData LIKE '%backup%' AND SPID <> @@SPID
ORDER BY StartTime DESC;