How trigger update in replica database on Postgres? In both servers (master and replica) I have two tables like this:

CREATE TABLE my_table
(
    id SERIAL,
    name TEXT,
    "timestamp" timestamp without time zone,
    CONSTRAINT my_table_pkey PRIMARY KEY (id)
);

In my master server I created a publication with:

CREATE PUBLICATION sync_publication;
ALTER PUBLICATION sync_publication ADD TABLE my_table;

Then I created a use (named syncguy):

CREATE ROLE syncguy WITH REPLICATION LOGIN PASSWORD 'root';

After I granted privileges:

GRANT ALL PRIVILEGES ON DATABASE my_database TO syncguy;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO syncguy;

In postgresql.conf setup wal_level to logical:

wal_level = logical

In postgresql.conf setup wal_level to replica:

wal_level = replica

Then I insert in Master:

insert into public.my_table (id, name, "timestamp") values (
    uuid_generate_v4(),
    'foo',
    current_timestamp
);

Then in both server in my_table i get:

+--------------------------------------+------+----------------------------+
| id                                   | name | timestamp                  |
+--------------------------------------+------+----------------------------+
| 157b1849-53a5-4206-9f8b-961c1485d20d | foo  | 2020-02-04 10:07:28.048819 |
+--------------------------------------+------+----------------------------+

Then I update "name col" in Master to bar value, and I get in both server this:

+--------------------------------------+------+----------------------------+
| id                                   | name | timestamp                  |
+--------------------------------------+------+----------------------------+
| 157b1849-53a5-4206-9f8b-961c1485d20d | bar  | 2020-02-04 10:07:28.048819 |
+--------------------------------------+------+----------------------------+

All of this worked well, my problem is when I create a trigger, I'm using this function and trigger on replica server (I don't use triggers in "master server"):

CREATE FUNCTION public.my_func() RETURNS trigger
    LANGUAGE plpgsql
    AS $$DECLARE
    partition_date TEXT;
    partition TEXT;
BEGIN
    partition_date := to_char(NEW.timestamp, 'YYYY_MM_DD');
    partition := 'messages_' || partition_date;

    EXECUTE 'CREATE TABLE IF NOT EXISTS ' || partition || ' (CONSTRAINT ' || partition || '_pkey UNIQUE (id)) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;';

    RETURN NULL;
END;$$;

ALTER FUNCTION public.my_func() OWNER TO postgres;

CREATE TRIGGER my_trigger BEFORE INSERT ON public.my_table FOR EACH ROW EXECUTE FUNCTION public.my_func();

ALTER TABLE public.my_table ENABLE REPLICA  TRIGGER my_trigger;

So, my objective with this trigger is to partition my replicated data, when I insert in master with trigger in replicate server it's works, is create a table like this messages_2020_02_04

The messages_2020_02_04 contains my replicated data and my_table in replica server is just "referenced", as I said, this works well with INSERT (I tested sending several, all went to the tables generated by the trigger), however when executing "UPDATE" in master the lines are not updated in replica.

Is it possible to work on master updates with replica and triggers in "replica server"?

The problem with updates in your scenario is that my_table on the standby server does not contain the row being updated, so that the UPDATE does nothing and no row level trigger is fired. Since nothing was inserted into my_table, an UPDATE cannot be replicated.
If you want to replicate from a non-partitioned to a partitioned table with logical replication, use PostgreSQL v13 or better, where that is supported. You will bot be able to automatically generate partitions that way, but it shouldn't be a problem to create the partitions before you start replicating.


Unique full text index? Is it possible to create a UNIQUE FULLTEXT index? I am using a mysql diff tool that is complaining that I am using duplicate indexes. I have a column that needs to be unique but also used for full text search.

Since there is a limit on the length of the index, if you want to guarantee the uniqueness of the whole field's value, you may want to add a hash field of that varchar field.

Steps:

Alter the table to add an MD5 field: alter table table1 add md5_of_column char(32)
Update this field: UPDATE table1 SET md5_of_column=md5(column);
Add the unique index: ALTER TABLE table1 ADD UNIQUE (md5_of_column)
Either create a trigger, or do it at application level, to update this new field after each insert/update operation.

Note that this solution might have a light performance hit.


mongodb is not accessible through IP address 35.x.x.x I have installed mongo DB on window server that is accessible on localhost:27017  but not accessible on 35.x.x.x:27017.

I checked:

27017 Port is open.
mongoDB is running.

output of: netstat -anb | findstr :27017

TCP    127.0.0.1:27017        0.0.0.0:0              LISTENING
TCP    127.0.0.1:51186        127.0.0.1:27017        TIME_WAIT

my mongos.conf file is

dbpath = d:\MongoDB\Database
logpath = d:\MongoDB\Logs\log.txt
noauth = true # use 'true' for options that don't take an argument
bind_ip=0.0.0.0

The answer is simple, you have a typo at your config.  There should be bindIp and not bind_ip.


how do I model this in database? I am using mysql as my backend database. Here is the use case;

A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. Each offering belongs to different types of variants:

Hotels
Origin city

For starters, I want to design the use case of Origin city:

There is a GOA package. It can be offered in 3 variants based on Origin city;

From New Delhi to Goa
From Bombay to Goa
Goa itself ( Land package )

(Bold implies default selection)

a. and b. User can fly from either  New Delhi or Bombay to GOA.
c. Goa is a Land package, where a user can reach Goa by himself and is a standalone holidayPackage.

Also, we need to further support the following use cases with a variant category (Origin City):

one of the options in a variant can be marked as default by the admin, which will be a part of the offered inclusions (e.g. Bombay  to Goa)

In the database, I need to model these relationships. Here is what I have in mind.

HolidayPackage has MANY to MANY relationship with Destination (read origin city). This will take care of options like:

From New Delhi to Goa
From Bombay to Goa

My concern:

How do I handle Land packages only i.e. "Goa itself" and we don't have a travel leg ?
How can I mark an option for a package, default at the database level ?

City
----
CityID
CityName
PRIMARY KEY (CityID)

Hotel
-----
HotelID
HotelName
CityID
PRIMARY KEY (HotelID)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)

and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

and default Package Version:

PackageDefaultVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)


SP2-0667: Message file sp1.msb not found sqlplus oracle I've a linux RedHat which contains oracle database. I setted all environnement variables, but when I running sqlplus command I've this error : SP2-0667: Message file sp1<lang>.msb not found

Here are my variables ans my sqlplus commands :

[oracle@as ~]$ echo $ORACLE_HOME
/oracle/product/12.1.0/dbhome_1
[oracle@as ~]$ echo $ORA_NLS33
/oracle/product/12.1.0/dbhome_1/nls/data
[oracle@as ~]$ echo $LD_LIBRARY_PATH
/oracle/product/12.1.0/dbhome_1/lib
[oracle@as ~]$ sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Note that I'm currently running as oracle user, which seems to be the owner of the /oracle folder with read and excecute permission. I also seen that a file named sp1us.msb exists in the following folder :

[oracle@as ~]$ find $ORACLE_HOME | grep sp1
/oracle/product/12.1.0/dbhome_1/sqlplus/mesg/sp1us.msb
/oracle/product/12.1.0/dbhome_1/sqlplus/mesg/sp1us.msg

I found the solution ! As the oracle documentation said about ORACLE_HOME, My env variables was setted in the /etc/profilefile, but not visible from env | grep ORACLE_HOME or dbhomeso I executed the following command :

export ORACLE_HOME=/app/oracle/product/12.1.0/dbhome_1

and now it works


Oracle: how to query a table if I know only its (non-default) tablespace? So. SQLPlus simply gives for my first try (after a little bit of postgresql experience):

SQL> SELECT * FROM MYTABLESPACE.MYTABLE;
SELECT * FROM MYTABLESPACE.MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I've tried also SELECT * FROM MYTABLESPACE$MYTABLE (intuitive try) and simply SELECT * FROM MYTABLE as well, but nothing succeed. Googling for that gives a lot of info about the tablespace management, but nothing from this trivial question.

What to do?

Extension #1: Well, it looks there was really a minor misconception about the tablespaces and the schemas. But the problem is still very visible: I have access to the dba_tables table, I have access to one of its records, I want to query table described by that, and it doesn't work.

Extension #2: Here is what I did. I called sqlplus with the system account (sqlplus system/****):

SQL> SELECT * FROM (SELECT * FROM dba_tables WHERE tablespace_name='MYTABLESPACE') WHERE ROWNUM<=1;

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ----------
 MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- -----------
AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE                                   INSTANCES                                CACHE                TABLE_LO SAMPLE_SIZE LAST_ANA PAR IOT_TYPE     T S
------------------------- ------------------- ---------------------------------------- ---------------------------------------- -------------------- -------- ----------- -------- --- ------------ - -
NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
--- ------- ------- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ------------ --- --- --- -------
EXAMPLEOWNER                   MYTABLE                   MYTABLESPACE                                                                                VALID            10                     1
       255          65536     1048576           1  2147483645                                         NO  N
                                                       1                                        1                                   N                ENABLED                       NO               N N
NO  DEFAULT DEFAULT DEFAULT DISABLED NO  NO                  DISABLED YES                                DISABLED DISABLED              NO  NO  YES DEFAULT

SQL> SELECT * FROM MYTABLE;
SELECT * FROM MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Given your DBA_TABLES query, you have the following:

Owner (Schema Name): EXAMPLEOWNER

Table Name: MYTABLE

The SYSTEM user will not just be able to select from MYTABLE unless there is a Public Synonym pointing to it.

So, to select from the table you have to fully qualify the name by prefixing the table with its schema:

SELECT * FROM EXAMPLEOWNER.MYTABLE;

Note that tablespaces are simply the place the object is stored in, and have no bearing in DML queries.


How to store GPS coordinate and search places in a radius from a NoSQL DBMS (like DynamoDB) My team need a DBMS like DynamoDB to store large amount of data, principally places and coordinates.
I've considered to use some GIS-based DBMS (like PostGIS) with an index on the POINT, but DynamoDB seems great for our use.
What is the best method to store the coordinate and quickly retrieve all objects in a particular radius?
In PostGIS it's easy, something like this:
SELECT *
FROM places
WHERE ST_DWithin(coordinate, ST_GeomFromText('POINT(45.07085 7.68434)', 4326), 100.0);

How can I do something like that in a NoSQL DBMS?

The classic answer is to query the database for the set of points within the 100 mile BOX and then remove the subset of points that are outside the 100 mile CIRCLE in your application layer using your programming language of choice (not SQL).

I've done this with data in Amazon SimpleDB efficiently, and you could do the same in DynamoDB with a couple "index tables" one using lat and another using lon as hash keys...


How to run specific version (8.4, 9.1) of postgresql pg_* command (e.g., pg_dump) I have Postgresql versions 8.4 and 9.1 installed.  For any given Postgresql command, how do I specify a specific version of the command to run?  (e.g., psql, pg_dump, pg_ctlcluster, pg_restore, ...)

My question is motivated by wanting to do a pg_dump in preparation for an upgrade from 8.4 to 9.1, and I want to know which pg_dump version I am running.

I'm running on Ubuntu 10.04 Natty.

You are on Ubuntu and obviously have Martin Pitt's pg_wrapper installed (judging from pg_ctlcluster) that is provided by the package postgresql-common and comes with the standard Debian packages. I use the same on Debian.

On a Linux system, run which in the shell to see which executable is actually picked:

postgres@db:~$ which pg_dump
/usr/bin/pg_dump
postgres@db:~$ ls -l /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37  4. Jun 18:57 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper

pg_dump is actually a symlink to pg_wrapper, which dynamically picks the appropriate version of the client program for the db cluster you run pg_dump with. I quote the man page of pg_wrapper:

  This program is run only as a link to names which correspond to PostgreSQL programs in /usr/lib/postgresql/version/bin. It determines
  the configured cluster and
         database for the user and calls the appropriate version of the desired program to connect to that cluster and database, supplying any
  specified options to that command.

   The target cluster is selected by the following means, in descending order of precedence:
   1.  explicit specification with the --cluster option
   2.  explicit specification with the PGCLUSTER environment variable
   3.  matching entry in ~/.postgresqlrc (see postgresqlrc(5)), if that file exists
   4.  matching entry in /etc/postgresql-common/user_clusters (see user_clusters(5)), if that file exists
   5.  If only one local cluster exists, that one will be selected.
   6.  If several local clusters exist, the one listening on the default port 5432 will be selected.

   If none of these rules match, pg_wrapper aborts with an error.

IOW, the right version should be picked automagically - unless you screwed up your installation somehow. You can always add the option --cluster to be specific.


The CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backupbackup true or false? I have a question that related to SQL Server Checkpoint Log Sequence Number.

I've read some articles and they say that the CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup, but I can't find the confirmation on the https://technet.microsoft.com page.

I have a doubt about it because I read that the only possible way to anchor LOG backup to DIFF is: DIFF.DatabaseBackupLSN =< LOG.DatabaseBackupLSN, DIFF.LastLSN < LOG.LastLSN

Also, I have made the following test and it seems that the CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup.

So is it correct?

No it does not map all the time. It depends on the interval of checkpoint happening. You can test this with the code below.

To explain what I said in my comment you can test with this code.

Use the code from here to load 300 rows.

Create a new table.

USE TestChkPoint
GO

CREATE TABLE dbo.TestTableSize
(
    MyKeyField VARCHAR(10) NOT NULL,
    MyDate1 DATETIME NOT NULL,
    MyDate2 DATETIME NOT NULL,
    MyDate3 DATETIME NOT NULL,
    MyDate4 DATETIME NOT NULL,
    MyDate5 DATETIME NOT NULL
)
GO

You will see different CheckpointLSN between diff and log1 backup if you perform below action.

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--insert new data
CHECKPOINT
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'

You will see same checkpointLSN in you diff and log1 backup if you perform this actions.  Notice I commented out manual checkpoint.

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--insert new data
--CHECKPOINT
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'

Now if you set TARGET_RECOVERY_TIME to 1 second and perform below steps without manual checkpoint you will see different checkpointLSN between diff and log backup.

ALTER DATABASE TestChkPoint SET TARGET_RECOVERY_TIME = 1 SECONDS;

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--no manual checkpoint
--insert new data change 300 to 30000
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'


How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument? Assume I have a sql query like this

SELECT * FROM TABLE where country in ('USA', 'UK')

This query works as expected that is it selects all rows where country column is either 'USA' or 'UK'. Now however, if I run this query

SELECT * FROM TABLE where country IN ()

its a syntax error. So I change it to run something like this

SELECT * FROM TABLE where country IN ('')

My goal here is to use jdbcTemplate of Spring framework to dynamically run this sql query. However I am not sure what my inputs are going to be. So I want to be able to pass the countries like 'USA' or 'UK' and get only rows related to those OR pass nothing and get ALL rows. So how do I get ALL rows if nothing is passed into IN clause?

So how do I get ALL rows if nothing is passed into IN clause?

You have to use dynamic sql.

e.g. Below is a simple example to get you started.

set nocount on
declare @sqltext nvarchar(max) = N''
declare @countryInput varchar(30)

select @sqltext = N'
    select * -- good practice to use explicit column names
        from dbo.Table
        where 1 =1 '
        + case when @countryInput is null or @countryInput ='' then ''
        else ' and country in (''USA'',''UK'')'
        end

        print @sqltext


mysql - Can a inner join statement limit query results? I am attempting to write a query (mysql maria db) that pulls all rows from my main table (19m records) but also pulls categories / sub categories (in another table -> business_sub_category / categories).

I have written a number of inner joins into my statement to pull the tables but it appears that i have made a mistake. The below query only pulls 500 records. There are at least 7m emails.

Why is my query being limited to only 500 (thats how many get returned from this result set).

Even just giving me an idea of why my query might be limited would be very helpful. I'm new!

SELECT businesses_old.id, businesses_old.name, businesses_old.email, business_sub_category.sub_category_id, sub_categories.id, sub_categories.category_id, categories.id, categories.name
FROM businesses_old
INNER JOIN business_sub_category
ON businesses_old.id = business_sub_category.id
INNER JOIN sub_categories
ON business_sub_category.id = sub_categories.id
INNER JOIN categories
ON sub_categories.category_id = categories.id
WHERE email IS NOT NULL AND email <> '';

You need to perform a left outer join or a full outer join. An inner join takes the value that matches both tables and gives you back the results of both tables.

A left outer join (sometimes referred to as a left join) matches the value between both tables. When a value exists in the primary table you are selecting from but does not exist on the table you are joining to, the results from the primary table will be returned as well as any results that were matched from the joining table. If the values do not exist in the column from the table you are joining to, you will not get those results back.

This can be reversed with a right outer join (also referred to as a right join). I try to remember it as the left join refers to the from table, the right join refers to the joined table. It is generally seen as better practice to keep it to a left join so your code is consistent to read. Sometimes it could be hard to refactor and I'd look at right joins then.

A full outer join is combining all rows in both tables and matching them if possible. If the results don't match the from table, those results will be populated with NULLS. The same is true for the other table if it has no matches.

https://www.w3schools.com/sql/sql_join.asp

Try this:

SELECT
    businesses_old.id, businesses_old.name, businesses_old.email,
    business_sub_category.sub_category_id, sub_categories.id,
    sub_categories.category_id, categories.id, categories.name
FROM businesses_old
LEFT JOIN business_sub_category
ON businesses_old.id = business_sub_category.id
LEFT JOIN sub_categories
ON business_sub_category.id = sub_categories.id
LEFT JOIN categories
ON sub_categories.category_id = categories.id
WHERE email IS NOT NULL AND email <> '';

I would also try to alias each column you refer to with the table before it, this keeps your records straight for anyone else trying to read your SQL.


Database extensions vs migrations When using of database specific features (instead of 3rd party solutions) is justified, especially on the beginning stage of the project?

Example: Postgres has full-text search, which do not support some features of search engines (i.e. facets) but is pretty acceptable for my needs. It requires postgres-specific extensions. But if I use postgres extensions instead of ElasticSearch, I'll bind myself to postgres. Is that a bad practice?

Am I right if I say that all new projects should start with RDBMs (due query flexibility, data normalization) and than migrate to i.e. NoSQL if needed?
If yes, than using db-specific features interfere db migrations in future, and is a bad practice. Also extensions can break db migration tools like Flyway.

If you migrate from RDBMS to NoSQL then you will also have problems. They are not interchangeable for all types of usage.

When you start a project then just before you start the creation of your data objects you must choose the one that suits best to your needs. Changing later will nearly always cause problems as soon as you use more then the 'standard' options.

When you choose to use a third-party extension then there is always the risk that:

Support/development of the extension stops. When you upgrade your database then the extension might no longer work;
Another better third-party solution is created. When you want to use it then you face incompatibility problems with the current third-party extension;
The third-party extension gets integrated in the database that you use.

The last option is the best one.


Postgresql after 'for each row' triggers don't actually execute after each row? If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted or updated.

Why is that? And is there a way to prevent it from happening?

begin;

create table foos (state text not null);

create function f() returns trigger as $$ begin
  -- This seems to be ran after all the rows are inserted or updated?
  -- expected it to run once for each row as it was inserted or updated.
  raise notice '%', count(*) from foos where state = 'initial';
  return null;
end $$ language plpgsql;

create trigger f after insert or update on foos for each row execute procedure f();

insert into foos select 'initial' from generate_series(1, 5);
update foos set state = 'canceled';

Expected Output:

psql:/tmp/t.sql:14: NOTICE:  1
psql:/tmp/t.sql:14: NOTICE:  2
psql:/tmp/t.sql:14: NOTICE:  3
psql:/tmp/t.sql:14: NOTICE:  4
psql:/tmp/t.sql:14: NOTICE:  5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE:  4
psql:/tmp/t.sql:15: NOTICE:  3
psql:/tmp/t.sql:15: NOTICE:  2
psql:/tmp/t.sql:15: NOTICE:  1
psql:/tmp/t.sql:15: NOTICE:  0
UPDATE 5

Actual Output:

psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
UPDATE 5

Edit:

Using a before trigger behaves differently (and how I would expect it to behave):

begin;

create table foos (state text not null);

create function f() returns trigger as $$ begin
  raise notice '%', count(*) from foos where state = 'initial';
  return new;
end $$ language plpgsql;

create trigger f before insert or update on foos for each row execute procedure f();

insert into foos select 'initial' from generate_series(1, 5);
update foos set state = 'canceled';

Output:

psql:/private/tmp/t.sql:14: NOTICE:  0
psql:/private/tmp/t.sql:14: NOTICE:  1
psql:/private/tmp/t.sql:14: NOTICE:  2
psql:/private/tmp/t.sql:14: NOTICE:  3
psql:/private/tmp/t.sql:14: NOTICE:  4
INSERT 0 5
psql:/private/tmp/t.sql:15: NOTICE:  5
psql:/private/tmp/t.sql:15: NOTICE:  4
psql:/private/tmp/t.sql:15: NOTICE:  3
psql:/private/tmp/t.sql:15: NOTICE:  2
psql:/private/tmp/t.sql:15: NOTICE:  1
UPDATE 5

Since the your INSERT and UPDATE are atomic (either all rows inserted/updated or none), you cannot catch a state when the count will not be 5 or 0.  First the INSERT happens, then the trigger is fired for all the rows.


SQL Server 2016 Trigger issue I need a little help with a trigger.
Where I'm in wrong:

CREATE TRIGGER [dbo].[tr_ins_MyTriggerName]
ON [dbo].MyTableName
FOR INSERT
AS
    DECLARE
    @Number nvarchar(20) ,
    @Date datetime ,
    @Supplier nvarchar(12);

    SET @Number = (SELECT Number FROM INSERTED);
    SET @Date = (SELECT Date FROM INSERTED);
    SET @Supplier = (SELECT Supplier FROM INSERTED);
BEGIN
    IF EXISTS (
        SELECT TOP 1 1 FROM MyTableName x
        WHERE x.Number = @Number
            AND x.Date = @Date
            AND x.Supplier = @Supplier
        )
    BEGIN
        RAISERROR ('There is the same Number for the same Supplier!' ,11,1)
        ROLLBACK
    END
END

This trigger runs in all cases even the the EXITS is not true!

This trigger runs in all cases even the the EXITS is not true!

The condition will always be true since this trigger fires after the record is inserted. FOR is synonymous with AFTER for TRIGGERS, thus the EXISTS would never not be true.

  FOR | AFTER

  Specifies that the DML trigger is fired only when
  all operations specified in the triggering SQL statement have executed
  successfully. All referential cascade actions and constraint checks
  also must succeed before this trigger fires.

  AFTER is the default when FOR is the only keyword specified.

When you say it runs do you mean the trigger fires, or that the error is always raised? I'd expect both, as explained above.

It seems like you are trying to do error handling for an INSERT in which I'd do it in the procedure or code block that you are doing the INSERT via a TRY / CATCH block, or just add a constraint on the table to prevent this type of duplicate insertion. Erland Sommarskog has an extensive blog on error handling that's very helpful. Here's a basic framework via one of this posts, but be sure to read it!

Here is a stored procedure that showcases how you should work with errors and transactions.

CREATE PROCEDURE insert_data @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH


SQL Server RAID Storage I have been reading about recommended SQL Server storage and RAID 5 or RAID 10 controllers have been recommended.

On my existing Database server I can see 4 Storage controllers under Device Manager.
They are listed as:

LSI Adaper (*2)
VMWare PVSCSI Controller (*2)

Are these an alternative to RAID? Or should I be using RAID for SQL Server?

"Storage Controllers" <> "RAID".  RAID is a configuration, not hardware.

For example, RAID 5 refers to having at least 3 physical disks typically attached to a disk controller such that stripes of data and parity are written across all three disks any time data is written to the array.  If you had 3 x 76GB drives, a RAID-5 array would have 76 x 2 = 152GB of usable capacity since 1/3 of the total space is dedicated to parity data.  The parity data is used to calculate the missing data if any one of the drives in the array fails. The RAID array looks like a single physical disk to the operating system installed on the machine.

For SQL Server, RAID-5 is typically counter-indicated since calculating and writing the parity information causes writes to be slower than with RAID-10.  RAID-10 consists of at least 4 drives, 2 mirrored sets of RAID-0 arrays.  Since RAID-10 does not have to calculate parity data, it is typically faster than RAID 5, and since the array is mirrored, it can survive up to 2 simultaneous drive failures.  However, capacity of a RAID 10 array is smaller than a RAID 5 array consisting of the same number and size of drives.

To answer your question, no, these controllers do not by themselves mean you have any RAID at all in the system.  Since the server you are looking at is a virtual machine, you would need to find out from the system administrator of the physical host machine what kind of storage it has.


Faster optimize table I have a 5TB+ MySQL database on RDS which I need to optimize. One particular table 1.5TB. We ran OPTIMIZE TABLE on this table in a separate RDS instance restored from a snapshot and the results were encouraging, but the command took 32 hours to run!

Is there a way to improve performance of the OPTIMIZE TABLE command? This table has 6 indexes other than the PK. I believe removing them will offer significant speedups. We plan to bump up the to the largest instance size RDS offers (r3.8xlarge) to help speed up the process. Are there any parameter groups I can tweak to improve the performance?

If you PARTITION the table (on anything), and have no more than, say, 30 partitions, then you could REBUILD each partition in about 3% of the total time, possibly a lot faster due to the size.  (Possibly following it with a ANALYZE PARTITION would be beneficial.)

Do not use 'optimize partition', that erroneously optimizes the entire table.

So, if you get enough reclaimed space to be worth it, set up a cron or EVENT to reorg one partition each night forever.

If you provide SHOW CREATE TABLE and some important queries, I may be able to advise on how to gain some other benefit from the partitioning.


Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)? There is a project for lab.  The requirement to save the lab data (raw data and related infomation ) for research for years.

So the data must be saved for years, but the data is so big, every raw data is more than 10 GB.  If we store the raw data in SQL Server's FILETABLE and store related information in a standard SQL Server table, after months or years, the database size will be so big that we must move some history data out of the database.

Maybe we could use adding files (on a different hard disk) to the FILESTREAM filegroup for filetable (to store raw data ), but I feel that is not suitable for maintaining the SQL Server.  We must keep the raw data and related information's consistency.

We have considered using tape to save the historical raw data.  We want to use hard disk to save latest raw data and use tape to save the historical raw data.  When we move the history data to tape we record the move in a log table, so we can know where the history data is moved and pick up it as soon as possible.

Is there good advice for:

How to store the raw data and related information?
Is filetable suitable for this scenario?
Is there a better solution for this scenario?

FILETABLE data is built upon the FILESTREAM feature of SQL Server, and uses the Windows Server filesystem to store VARBINARY(MAX) data in discrete NTFS files on the filesystem of the server itself.  This data is not stored in the database, therefore the database file size will not include these 10GB+ files.  Database backups will, by default, include the data.  You can prevent SQL Server backups of the filestream data by using partial backups to exclude the filestream filegroups.  Filestream data is not cached using SQL Server buffers, the operating systems file cache is used instead.

Microsoft recommends AGAINST using FILESTREAM to store files that are on average less than 1MB since this negates the efficiency gained by using the Win32 file APIs to modify the data involved.  The maximum file size for a FILESTREAM file is only limited by the operating system maximum file size, which for current versions of NTFS is for all practical purposes unlimited (16 Exabytes).  There are, by design, no limitations placed on the size of a FILESTREAM object by SQL Server.

If the meta data you intend to store in SQL Server must be transactionally consistent with the object data stored in the FILESTREAM you should use FILETABLE/FILESTREAM.  If the object data stored in these 10GB+ files is not logically related to meta data stored in SQL Server, I would expect using FILESTREAM might be counterproductive.

In short, it depends on your exact use-case.  Add more details to your question about why you want to use a FILETABLE.

FILESTREAM docs at Microsoft:  http://technet.microsoft.com/en-us/library/gg471497.aspx

[Edit]

In light of your requirements to store binary X-Ray data along with machine parameters used to capture the x-ray data, it sounds like the precise use-case for a FILESTREAM.  However, instead of archiving to TAPE, I'd consider using DataCore SANSymphony-V Software SAN with multiple storage tiers.  SANSymphony-V allows you to easily and reliably expand your SAN with inexpensive commodity Windows-based servers using 15k RPM Enterprise drives (with an SSD tier, possibly) for the main database along with a tier configured with standard 7200 RPM SATA drives for storage of the x-ray data.  SANSymphony-V is SQL Server certified and would provide a relatively inexpensive way of maintaining access to both the new data and the older data without any complicated data-management system.  If Disaster Recovery and High Availability is a concern, the entire SANSymphony-V SAN can be mirrored, either on-site, or off-site at distances of up to around 100km.

Just for clarity, I am not in any way associated with DataCore - I simply love their design.


Postgresql - return column of referenced table that is not primary key I have tables:

CREATE TABLE users (
  id text PRIMARY KEY,
  internal_id serial,
  other columns...
)

CREATE TABLE listings (
  id bigserial PRIMARY KEY,
  created_at timestamptz default current_timestamp,
  posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,
  other columns...
)

Using node-postgres, I am inserting a new record into table listings and returning the listings.id:

 pool.query(`INSERT INTO listings(${columns}) VALUES(${args}) \
           RETURNING listings.id`, [...values]);

In addition to listings.id, who could I also return users.internal_id?

You can just use a CTE for the insert, and then check the users table via SELECT:

WITH ins AS
(
INSERT INTO
    listings
    (posted_by, other_columns)
VALUES
    ('just_me', 'and that''s what I post')
RETURNING
    listings.id, posted_by
)
SELECT
    ins.id, users.internal_id
FROM
    ins
    JOIN users ON users.id = ins.posted_by ;

dbfiddle here


How to find unused columns in MariaDB tables I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the tables, Is there a definitive way to check if a column is getting used or not?
MariaDB 10.3.8

If your table has columns that are defined to have NULL values, run this:
SELECT COUNT(given_column) FROM mytable;

This gives you the numbers of rows in mytable whose given_column is not NULL. If the result comes back as zero(0), that column is not populated with anything. You could drop that column with ALTER TABLE mytable DROP COLUMN given_column;
If your table has columns define as DEFAULT <some-value>, run this
SELECT COUNT(DISTINCT given_column) DistinctCount FROM mytable;

If this comes back as 1, you could most likely drop this column as well.
If the DistinctCount > 1 and you want to see that breakdown, run this
SELECT IFNULL(given_column,'Total Rows') ColumnsValues,COUNT(1) rowcount
FROM mytable GROUP BY given_column WITH ROLLUP;

These queries should give you a good idea which can be dropped.
CAVEAT : Please make sure your given_column is never referenced in your source code before dropping the column


Design for storing data only applicable to a small subset of records So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients.

Basically, we have a bunch of tables in some format similar to this (in pseudo-code for simplicity):

TABLE [dbo].[SalesData]
[CampaignID] int IDENTITY(1, 1) NOT NULL, (This is the PK)
[CustomerID] int NULL,
[Calls] float NULL,
[Responses] float NULL,
[Sales] float NULL,
[Revenue] money NULL,
[Cost] money NULL,
[WebSales] float NULL,
[Channel] nvarchar(20) NULL

What happens is as we gain clients, we are sometimes forced to add in some kind of custom data collecting for each one. For this table, everything is used by every client (we have about 20) except for WebSales and Channel, which is only used for 1 client.  Another client wants a couple new pieces of data tracked, and therefore, we either need to add more columns, or spin off that data into a related table and start a supertype-subtype system.

What I want to do is also spin off WebSales and Channel, and add a table for each customer in this manner:

TABLE [dbo].[SalesData_Client1]
[CampaignID] int NOT NULL, (PK and FK to the SalesData table)
[WebSales] float NOT NULL DEFAULT 0,
[Channel] nvarchar(20) NOT NULL DEFAULT ''
ADD CONSTRAINT [CK__SalesData_Client1__CustomersCampaignID]
CHECK (dbo.CustomerOfCampaignID(CampaignID) = Client1)

Then I would drop those columns from the original table and make the second client a table like this as well. The boss thinks this means we'll have to add too many tables as we gain clients, and wants to add more columns. He would rather change the SalesData table like this:

TABLE [dbo].[SalesData]
[CampaignID] int IDENTITY(1, 1) NOT NULL, (This is the PK)
[CustomerID] int NULL,
[Calls] float NULL,
[Responses] float NULL,
[Sales] float NULL,
[Revenue] money NULL,
[Cost] money NULL,
[Unknown] char(10) NULL,
[Misc1] float NULL,
[Misc2] nvarchar(20) NULL
[Misc3] int NULL,
[Misc4] int NULL
[Misc5] int NULL

Where the "Misc" columns can be used for whatever, and then the programmers can "just figure it out based on the client". I know the reasons as to why you shouldn't do it this way, it violates normalization and will make performance worse and worse as we add columns, etc. But he keeps coming back with "It's way simpler" and doesn't like that every new client could mean another new table. He also makes the point that newer columns like Websales might be used by more than 1 client eventually, and it will be a huge pain to have it in multiple subtype tables, or to move it from multiple subtype tables to the main table if we should ever want to do that.

Is there a better method than supertype/subtype solution that I came up with that can make things easier for him and the programmers? An important note is that each client's data is basically in it's own universe and will NEVER mix. I realize it's kind of subjective and the answer will depend on how many "Misc" columns we are seriously expecting. The reason I want to go the subtype route is because the next client could require 5 new bigint columns for all I know.

To be truly flexible, you could go the Entity-Attribute-Value route, but that would probably be overkill for this and there are many who would argue that EAV is a terrible anti-pattern.

I think the client-specific tables might be the better way to go if you think you will eventually end up with dozens and dozens of client-specific columns in a single table.


Searching from the latest date in SQL Server In the following scenario, the two run dates start from 2015-01-30 to 2014-11-28 as per the database records since I am searching for last 3 months record and then trying to get the difference between x month - latest data point value which is 6 for id = 2 .

But I want to modify my query such that it starts from 1 month back from today at takes the first date of that month (e.g. 1 feb 2015 :1st date of every month) even though the first data point in the database records starts from 2015-01-30 in which the difference should be 8 - 0 = 8 rather than 6 .

How can I tweak my query to do this?

CREATE TABLE #t (ID INT, V FLOAT, D DATE)

INSERT  INTO #t
VALUES  (1, 1.2, '2014-01-01'),
        (1, 1.33, '2014-01-02'),
        (1, 1.33, '2014-01-03'),
        (2, 7, '2014-10-31'),
        (2, 5, '2014-11-28'),
        (2, 8, '2014-12-31'),
        (2, 11, '2015-01-30');

DECLARE @DealClauseString NVARCHAR(MAX)
SET @DealClauseString = ';WITH    filter
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY D DESC ) AS RN
               FROM     #t
             ),
        cte
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS
                                      BETWEEN UNBOUNDED PRECEDING
                                      AND UNBOUNDED FOLLOWING ) AS Min ,
                        MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS
                                      BETWEEN UNBOUNDED PRECEDING AND
                                      UNBOUNDED FOLLOWING ) AS Max
               FROM     filter
              WHERE    RN <= 2
             )
    SELECT  c1.ID ,
            c2.V - c1.V AS V
    FROM    cte c1
            JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D
    WHERE   ( c1.D = c1.MIN OR c1.D = c1.MAX )
            AND ( c2.D = c2.MIN OR c2.D = c2.MAX ) and c2.V - c1.V between 0.3 and 6 '
EXEC (@DealClauseString)
    drop table      #t

Any help would be really helpful. Pleasse let me know if you have any questions.

For starters, don't use @DealClauseString - just have your query.

Also, remove ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - when you're using the whole partition, there's no reason to have the ORDER BY clause at all.

Anyway - for your particular question, if I understand it correctly, you want it to be like your #t table only has records that are at least a month old. So you should put a WHERE clause in the filter CTE. So it becomes something like:

FROM #t
WHERE D <= DATEADD(MONTH,-1,SYSDATETIME())


How to run analysis on a 16M row table faster I have two tables students with 6k records and transactions with 16M records.

I have a query similar to this:

select section, count(s.students) from students as s
where upper(name) in
    (select upper(name) from transactions as t
        where (length(transaction_id) < 43 or
            lower(transaction_name) = 'NA' or
            lower(transaction_type) = 'food')
            and upper(name) not in ('JOHN DOE'))
group by section
order by section asc

The query plan for this is similar to below:

 GroupAggregate  (cost=2828274.40..2828291.32 rows=6 width=12) (actual time=101295.078..101301.568 rows=6 loops=1)
   Group Key: section
   ->  Sort  (cost=2828274.40..2828280.02 rows=2247 width=25) (actual time=101294.727..101300.274 rows=2414 loops=1)
         Sort Key: section
         Sort Method: quicksort  Memory: 285kB
         ->  Hash Join  (cost=2827697.15..2828149.32 rows=2247 width=25) (actual time=101278.618..101292.861 rows=2414 loops=1)
               Hash Cond: ((s.name)::text = (t.name)::text)
               ->  Seq Scan on students s  (cost=0.00..412.16 rows=5716 width=25) (actual time=0.007..2.887 rows=5720 loops=1)
               ->  Hash  (cost=2827669.07..2827669.07 rows=2247 width=21) (actual time=101278.578..101278.578 rows=2445 loops=1)
                     Buckets: 4096  Batches: 1  Memory Usage: 159kB
                     ->  HashAggregate  (cost=2827646.60..2827669.07 rows=2247 width=21) (actual time=101274.023..101276.849 rows=2445 loops=1)
                           Group Key: (t.name)::text
                           ->  Gather  (cost=1000.00..2804729.14 rows=9166982 width=21) (actual time=97.170..100735.029 rows=449343 loops=1)
                                 Workers Planned: 2
                                 Workers Launched: 2
                                 ->  Parallel Seq Scan on transactions t  (cost=0.00..1887030.94 rows=3819576 width=21) (actual time=130.428..100660.838 rows=149781 loops=3)
                                       Filter: (((name)::text <> 'JOHN DOE'::text) AND ((length((transaction_id)::text) < 23) OR (lower((transaction_type)::text) = 'food'::text)) OR OR (lower((transaction_name)::text) = 'NA'::text))))
                                       Rows Removed by Filter: 4897554
 Planning time: 10.027 ms
 Execution time: 101301.982 ms

All your time is spent in a sequential scan that discards almost all the rows it scans. Unfortunately you have an OR in there, so the best you can do is to create three indexes and hope for a "bitmap or" of the three index scans:

CREATE INDEX transactions_type_idx ON transactions (lower(transaction_type::text));
CREATE INDEX transactions_name_idx ON transactions (lower(transaction_name::text));
CREATE INDEX transactions_id_length_idx ON transactions (length(transaction_id::text));


Trying to optimize query for performance (MySQL) So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense.  The basic query looks like:

SELECT i.id, i.`key`, i.title, i.description,
CONCAT(ru.firstName, ' ', ru.lastName) as `name`,
CONCAT(au.firstName, ' ', au.lastName) as `name`,
p.title, pc.title, pva.title, pvo.title, pvf.title, i.durationEstimate,
i.storyPoints, i.dueDate, isl.title, i.rejectionCount,
CONCAT(uc.firstName, ' ', uc.lastName) as `name`,
i.createdTimestamp, i.updatedTimestamp, it.title, isss.title
FROM ProjectManagement2.Issues i
INNER JOIN Users ru ON ru.id = i.reporterUserUsername
INNER JOIN Users au ON au.id = i.assignedUserUsername
INNER JOIN Projects p ON p.id = i.projectTitle
INNER JOIN ProjectComponents pc ON pc.id = i.projectComponentTitle
INNER JOIN ProjectVersions pva ON pva.id = i.affectedProjectVersionTitle
INNER JOIN ProjectVersions pvo ON pvo.id = i.originalFixedProjectVersionTitle
INNER JOIN ProjectVersions pvf ON pvf.id = i.fixedProjectVersionTitle
INNER JOIN IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId
INNER JOIN IssueTypes it ON it.id = i.issueTypeId
INNER JOIN IssueStatuses isss ON isss.id = i.issueStatusId
INNER JOIN Users uc ON uc.id = i.creatorUserUsername
LIMIT 50000, 100;

This query takes about 880ms to run after I run it the first time (which take a few seconds).  The EXPLAIN of this query shows:

1   SIMPLE  pvo index   PRIMARY title   98  NULL    22  Using index
1   SIMPLE  i   ref     reporterUserUsername,assignedUserUsername,projectTitle,projectComponentTitle,affectProjectVersionTitle,originalFixedProjectVersionTitle,fixedProjectVersionTitle,issueSecurityLevelId,creatorUserUsername,issueTypeId,issueStatusId
       originalFixedProjectVersionTitle 5   ProjectManagement2.pvo.id   1136    Using where
1   SIMPLE  isl ALL     PRIMARY NULL    NULL    NULL    5   Using where; Using join buffer
1   SIMPLE  isss eq_ref PRIMARY PRIMARY 4   ProjectManagement2.i.issueStatusId  1
1   SIMPLE  pc  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.projectComponentTitle  1
1   SIMPLE  pva eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.affectedProjectVersionTitle    1
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.projectTitle   1
1   SIMPLE  pvf eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.fixedProjectVersionTitle   1
1   SIMPLE  ru  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.reporterUserUsername   1
1   SIMPLE  au  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.assignedUserUsername   1
1   SIMPLE  uc  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.creatorUserUsername    1
1   SIMPLE  it  eq_ref  PRIMARY PRIMARY 4   ProjectManagement2.i.issueTypeId

The big issue is when I add a order by to the query, then it take several seconds after the first query.

Is there anything I can do to optimize this query (if you need any more information please let me know)?

Your original query joins everything together then traverses 50000 rows into the joined data before presenting the next 100 rows. Try this

Get the 100 keys from ProjectManagement2 upfront
join everything afterwards

Here is my proposed query

SELECT i.id, i.`key`, i.title, i.description,
CONCAT(ru.firstName, ' ', ru.lastName) as `name`,
CONCAT(au.firstName, ' ', au.lastName) as `name`,
p.title, pc.title, pva.title, pvo.title, pvf.title, i.durationEstimate,
i.storyPoints, i.dueDate, isl.title, i.rejectionCount,
CONCAT(uc.firstName, ' ', uc.lastName) as `name`,
i.createdTimestamp, i.updatedTimestamp, it.title, isss.title
FROM
(
    SELECT B.* FROM
    (SELECT id FROM ProjectManagement2 LIMIT 50000, 100) A
    INNER JOIN ProjectManagement2 B USING (id)
) i
INNER JOIN Users ru ON ru.id = i.reporterUserUsername
INNER JOIN Users au ON au.id = i.assignedUserUsername
INNER JOIN Projects p ON p.id = i.projectTitle
INNER JOIN ProjectComponents pc ON pc.id = i.projectComponentTitle
INNER JOIN ProjectVersions pva ON pva.id = i.affectedProjectVersionTitle
INNER JOIN ProjectVersions pvo ON pvo.id = i.originalFixedProjectVersionTitle
INNER JOIN ProjectVersions pvf ON pvf.id = i.fixedProjectVersionTitle
INNER JOIN IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId
INNER JOIN IssueTypes it ON it.id = i.issueTypeId
INNER JOIN IssueStatuses isss ON isss.id = i.issueStatusId
INNER JOIN Users uc ON uc.id = i.creatorUserUsername
;

Give it a Try !!!


Difference between " ANY(…)" and "NOT = ANY(…)"? Could you please explain the difference between these two operators?

sID != smth and not sID = smth.

At a first glance they seem perfectly equal. But they give different results.

NOT sid = ANY (SELECT ...)

is equivalent to:

sid <> ALL (SELECT ...)

So, your two conditions are not the same.

ALL and ANY operators are hard for me (and many people) to use. I think that's how many prefer to use IN, NOT IN, EXISTS and NOT EXISTS which result in more self-explanatory code.

If you do want to work with them, think that sid = ANY (SELECT ...) means "check if sid is equal to any (some) of the (select...) values".

Then the NOT sid = ANY (SELECT ...) is the opposite of that. But the opposite of "equal to any of them" is "different to all of them" (and not "different to some of them").


DECODE or CASE STATEMENT for PARAMETER in WHERE CLAUSE I am having an issue where I need to make 1 parameter have multiple options, almost like a case statement or decode function.
Here is a snippit of the query below, We were using 2 parameters so he can have either or, or use a wildcard but we want to narrow it down to 1 parameter.

What I am looking for is.. if he where to choose '%' for the &industry parameter, it will show spc.characteristic_code in ('ARLFT','MINE')
other wise he would put either 'MINE' or 'ARLFT' in the &Industry parameter.

If I need to clarify, please advise. Thanks in advance!

  SELECT INV.COUNTRY
    , INV.NAME
    , INV.STATE
    , INV.CUSTOMER_ID
    , INV.CUST_GRP AS DIVISION
    , INV.DISTRICT_CODE
    , INV.SLSPRT
    , SP.CATALOG_DESC
    , SPC.CHARACTERISTIC_CODE
    , SUM(INV.QUANTITY) QTY
    , SUM(INV.SALES) SALES
    , SUM(INV.COST) COSTS
    , SUM(INV.SALES - INV.COST) AS MARGIN
    FROM IFSINFO.HB_INVOICING_ALL INV
        JOIN IFSAPP.SALES_PART_CHARACTERISTIC SPC
            ON (INV.SITE = SPC.CONTRACT)
            AND (INV.SLSPRT = SPC.CATALOG_NO)
        JOIN IFSAPP.SALES_PART SP
            ON (INV.SITE = SP.CONTRACT)
            AND (INV.SLSPRT = SP.CATALOG_NO)
    WHERE (TO_DATE(INV.INVDATE)
        BETWEEN TO_DATE('&Start_Date','mm/dd/yyyy')
        AND TO_DATE('&End_Date','mm/dd/yyyy')
        OR  TO_DATE(INV.INVDATE)
        BETWEEN TO_DATE('&Start_Date2','mm/dd/yyyy')
        AND TO_DATE('&End_Date2','mm/dd/yyyy'))
    AND INV.SITE = '&Site'
    AND INV.CUST_GRP like '&Cust_Grp'
    AND SPC.CHARACTERISTIC_CODE like '&Industry'
    GROUP BY INV.COUNTRY
    , INV.NAME
    , INV.STATE
    , INV.CUSTOMER_ID
    , INV.CUST_GRP
    , INV.DISTRICT_CODE
    , INV.SLSPRT
    , SP.CATALOG_DESC
    , SPC.CHARACTERISTIC_CODE

I don't have a database on hand to test this but can't you do something like this:

and SPC.CHARACTERISTIC in ('ARLFT','MINE')
and SPC.CHARACTERISTIC in ('&industry')

With both of those predicates present you will default to both values if you enter '%' but be restricted to just one if you enter either 'MINE' or 'ARLFT'


Mongo DB backup sizes I'm backing up a MongoDB server using (for better or worse) mongodump. After running db.stats() I get a storageSize of 8.9GB and a dataSize of 44.5GB. This makes sense to me as I expect a lot of the data (at least 1/2) to be highly compressible.

My question is this, why would my backup's be 45GB in size, if the storage size is only 9GB - I've also checked my actual disk usage (the disk is used ONLY for mongo and has slightly over 9GB of disk usage.

I'm running mongodump with the following options:

/usr/bin/mongodump -h $HOST -d $DBNAME --username=$DBUSER --password=$DBPASS --archive="$OUTPUT"

I'm aware of the gzip option, but if the data is stored compressed, surely it should be backed up compressed too - and gzip should simply be a second level of compression?

Actually, if you don't use --gzip parameter, data is at bson format what is not compressed and you can look that data with bsondump program.
What mongodump do, is read data from database and that result is of course not compressed.

Here is different between those two from my system

-rw-r--r-- 1 root root 61M Mar 16 06:48 data.bson
-rw-r--r-- 1 root root 5.3M Mar 16 06:48 data.bson.gz

Over 10 fold compression ration.


Access exporting to CSV is weirdly slow I have searched but could not find anything similar. If I just run the query, I can get the result pretty quickly (~5 mins); but after I saved the query to export as CSV, the exporting part is extremely slow (>30mins).

The result only has 3 columns and approximately 30 records; I can easily copy and paste the query result I get. However, I would like to understand the strange behaviour. It's a huge database of about 7GB, that's why it's split into 4 different files. I am not allowed to use other software such as POSTgreSQL or mySQL at work. Here is my code anyway (modified the variable names):

SELECT var1, var2, count(*)
FROM (
SELECT var1,var2
    FROM [XXX10_2006-2010] INNER JOIN [XXXLong_2006-2010]
        ON [XXX10_2006-2010].ID = [XXXLong_2006-2010].id
    WHERE [XXXLong_2006-2010].var4 = 'hamburger'
        AND [XXXLong_2006-2010].cat1 = 'c'
        AND [XXXLong_2006-2010].cat2 BETWEEN 0 AND 96
        AND [XXX10_2006-2010].var5 = 'eggs'
UNION all
SELECT var1,var2
    FROM [XXX10data_2001-2005] INNER JOIN [XXXLong_2001-2005]
        ON [XXX10data_2001-2005].ID = [XXXLong_2001-2005].id
    WHERE [XXXLong_2001-2005].var4 = 'hamburger'
        AND [XXXLong_2001-2005].cat1 = 'c'
        AND [XXXLong_2001-2005].cat2 BETWEEN 0 AND 96
        AND [XXX10data_2001-2005].var5 = 'eggs'
UNION all
SELECT var1,var2
    FROM [XXX9_data_1997-2000] INNER JOIN [XXXLong_1997-2000]
        ON [XXX9_data_1997-2000].ID = [XXXLong_1997-2000].id
    WHERE [XXXLong_1997-2000].var4 = 'hamburger'
        AND ([XXXLong_1997-2000].cat1 ='1' AND [XXXLong_1997-2000].cat2 BETWEEN 40 AND 99)
        OR ([XXXLong_1997-2000].cat1 ='2' AND [XXXLong_1997-2000].cat2 BETWEEN 0 AND 39)
        AND [XXX9_data_1997-2000].var5 = 'eggs'
UNION all
SELECT var1,var2
    FROM [XXX9_data_1993-1996] INNER JOIN [XXXLong_1993-1996]
        ON [XXX9_data_1993-1996].ID = [XXXLong_1993-1996].id
    WHERE [XXXLong_1993-1996].var4 = 'hamburger'
        AND ([XXXLong_1993-1996].cat1 ='1' AND [XXXLong_1993-1996].cat2 BETWEEN 40 AND 99)
        OR ([XXXLong_1993-1996].cat1 ='2' AND [XXXLong_1993-1996].cat2 BETWEEN 0 AND 39)
        AND [XXX9_data_1993-1996].var5 = 'eggs'
)
GROUP BY var1, var2

EDIT: In case this little bit of detail helps: It takes slightly more than 10 mins for the export text wizard to show up, then the rest of the process takes another 20 mins or so. (sometimes much longer though)

Off the top of my head I might think it to be related having 4 different DB files and large ones at that (not sure why that would be). Can you easily scope the query to hit 1 vs 4 DB files to see if the preformance is increased?


Can't set up mirroring for a specific database We have several databases. All of them are mirrored. Until now, we never had issues with this.

One database had some problems, and now when we try to enable mirror again, it just stops. Message in the SQL Log is Database mirroring has been terminated for database 'databasename'.

What we do:

  - Create full backup of the primary database
  - Create backup of the transaction log, in the same backup file
  - Restore that backup on the secondary location, with the norecovery option
  - Set partner on mirror, then primary.

The primary gets status 'Principal, synchronizing' for a moment, but then it just stops and terminates the mirroring. Placing the mirror back in restoring state and the principal just as a database with no mirror.

What can we do to investigate what is going wrong here? I think the endpoints are fine, because the other databases have no problem at all.

Thanks for any advices.

Create full backup of the primary database
  Create backup of the transaction log, in the same backup file
  Restore that backup on the secondary location, with the norecovery option
  Set partner on mirror, then primary.

When the mirroring session is established the primary must have the current LSN of the mirror-to-be in the active log. This is necessary so that the primary can start sending log records to the mirror to bring it up to date, w/o any interruption.

In your sequence of events you have two LSNs saved: one after the full backup (LSN1), one after the log backup (LSN2). The primary will be able to accommodate a mirror that is at LSN2, but not one at LSN1. If you restore the full backup with no-recovery and then also restore the log, also with no-recovery, that should end up with a proper mirroring candidate database. But if you only restore the full backup then the mirror will be at LSN1 and the primary will reject it.


How does MongoDB store data in RAM? I'm trying to sift through the remains of an event that was causing us a lot troubles. Aside from report to management about what happened, I'm trying to gain a much better understanding of how this situation arose and how to avoid it in the future. Also, sorry for the long story, I'm just trying to provide detail.

We have two databases on our Mongo servers.
Our server is:

Virtualised
initially had ~75GB free space on the mongo data drive (that increased)
has 4x Xeon X5650@2.67GHz (virtualised)
has 3.7GB of RAM in total
and is a dedicated Mongo instance.

Database A contains data which is more or less a LIFO queue, data is inserted, and processed in batches over the day with the oldest being processed first. After it's processed it's deleted. This is the bigger of the two, and the one with the higher Read/Write activity, being about 1.3Gb. In the past it has reached up to 10-13Gb. It's essential a user activity tracking log on a website. For all intent and purposes it's a very large number of 1 dimensional array and comparatively small document sizes.

Database B contains a transaction log for users. Data is read and written randomly, but in contiguous times blocks. While a user is logged in, that particular user's document is more likely to be accessed. It's about 0.3Gb, and has grown, but only minimally.

We'd been experiencing high Disk Io for a while, but in the last couple of days, it took a jump from about 28Mbps to 35Mbps, which is at our limit (registering at 100%). When this happened, our CPU rose steadily, but the RAM stayed where it was, at about 750Mb.

Looking at MMS, during this period, Database A seemed to be chugging along happily, but Database B suddenly started getting a lot of page_faults, accessesNotInMemory, and pageFaultExceptionsThrown. We were not getting any b-tree misses. Our totalOpen and clientCursor_size rose steadily, but never hit their peak.

The storageSize for the database was about 13Gb, so about an order of magnitude larger than the actual data size.

We restarted the mongo server, and this caused our global RAM use to jump from 705Mb to about 1530Mb, and stayed there, but the CPU rose up again, and diskio jumped almost straight up.

MMS showed the same kind of behaviour as above for hours after the restart.

We attempted to re-create the indexes one by one on Database B (rather than reIndex() to ease load as it was during our peak period). We found one index, which after we dropped it, we would try to ensureIndex, it would run for a few seconds, and then send us back to the console, but db.collection.stats() would not show the index. We managed to recreate it in a sense by creating the reverse ordered index.

During a low period we ran db.repairDatabase() on both the database and this has fixed all our problems. DiskIO has dropped to nearly nothing; RAM is sitting marginally higher now about 1.7GB; load and CPU are barely registering; the DB storageSize has dropped to about 1.2Gb; no more page faults; and our cursor count sits around 0.

I am completely at a loss as to explain what happened. The indexes were in memory. MongoDB  looked like it had both the database and indexes entirely stored in RAM (after the restart). But we were still getting what looked to be lots of disk access on Database B.

My question: why was MongoDB not storing Database B in RAM, like it should, and kept going to disk to records?

EDIT: Attached NewRelic and MMS Graphs.

There are a couple of separate points here, but I don't think how MongoDB stores data in RAM is really relevant here - MongoDB just uses the mmap() call and then lets the kernel take care of the memory management (the Linux kernel will use Least Recently Used (LRU) by default to decide what to page out and what to keep - there are more specifics to that but it's not terribly relevant).

In terms of your issues, it sounds like you might have had a corrupt index, though the evidence is somewhat circumstantial.  Now that you have done a repair (the validate() command would have confirmed/denied beforehand), there won't be any evidence in the current data but you may find more evidence in the logs, particularly when you were attempting to recreate the index, or using the index in queries.

As for the spikes in the page faults, btree stats, journal, lock percentage, and average flush time, that has all the hallmarks of a bulk delete that causes a lot of index updates, and causes a large amount of IO.  The fact that mapped memory drops off later in the graphs would suggest that once you ran the repair the storage size was significantly reduced, which usually indicates significant fragmentation (bulk deletes, along with updates that grow documents are the leading causes of fragmentation).

Therefore, I would look for a large delete operation logged as slow in the logs - it will only be logged once complete, so look for it to appear after the end of the events in MMS.  One of the quirks of not running in a replica set is that a bulk operation like this is relatively non-obvious - it shows up as a single delete operation in the MMS graphs (usually lost in the noise).

These bulk delete operations usually tend to be run on older data that has not been recently used and has hence been paged out of active memory by the kernel (LRU again).  To delete it you must page it all the data back in, then flush the changes to disk, and of course deletes require the write lock, hence the spikes in faults, lock percentage etc.

To make room for the deleted data, your current working set is paged out, which will hit performance on your normal usage until the deletes complete and the memory pressure eases.

FYI - when you run a replica set, bulk ops are serialized in the oplog and hence replicated one at a time - as such you can track such operations by their footprints in the replicated ops stats of the secondaries.  This is not possible with a standalone instance (without looking in the logs for the completed ops) and other secondary indications.

As for managing large deletes in the future, it is generally far more efficient to partition your data into separate databases (if possible) and then drop the old data when it is no longer needed by simply dropping the old databases.  This requires some extra management on the application side but it negates the need for bulk deletes, is far quicker to complete, limits fragmentation, and dropped databases also remove the files on disk, preventing excessive storage use.  Definitely recommended if possible with your use case.


How to chop off X number of characters across multiple records? Hello all!
I have a field that contains geographical locations in the city-state format:

Boston, MA
Los Angeles, CA
Helena, MT
...

The field spans for some 230 records. My task is to chop off the four final characters in each record. How do I go about it? Any ideas?
Thank you!

DECLARE @tab TABLE(statename VARCHAR(20))
INSERT INTO @tab
(
      statename
)
select 'Boston, MA' UNION ALL
select 'Los Angeles, CA' UNION ALL
select 'Helena, MT'

SELECT SUBSTRING(statename,0,LEN(statename)-3) FROM @tab t

SELECT LEFT(statename,LEN(statename)-4)FROM @tab t


DATETIME2 and IDENTITY as composite key I am currently developing a new database schema with an opportunity to do things right.  The purpose of the development effort is to collect data (events) from various sensors that will be recorded with a time/date stamp.

Currently there will be two disconnected databases at two different locations capturing data that may or may not occur on the same day.  On a monthly basis, the drives will be pulled and sent to the customer's main office to ingest the data and merge into one database.

Once the data is available, the customer will replay the data for analysis as if they were actually there when it was captured.

Since recalling this data needs to be performance driven, I know right off the bat I want a clustered index on DATETIME2.

This leads me to the problem of designing the primary key to account for merging.

Would a composite primary key of DATETIME2 and an IDENTITY column be unique enough to avoid any collisions when the data is merged? For example, some data may be captured at a 50hz to 100hz rate.

Or is that too much of a wild card and it would be best to use a GUID as the PK?   If that is the case, how should I handle a clustered index for performance (for example) where a child table would have a PK of two columns:  a GUID from the parent table and a 3 char column "product" identifier?

Would it be best to add a DATETIME2 column to become the clustered index or am I making a mountain out of a mole hill?

Don't confuse the primary key with the key of the clustered index. Though they are often the same, they are independent.  You just need your primary key to uniquely identify a record in your table across all the databases that have a copy of it.

  Since recalling this data needs to be performance driven, I know right off the bat I want a clustered index on DATETIME2.

I agree with this choice, since you will primarily be writing, reading, and merging data by this column.  Be sure to pick the right precision for your DATETIME2.

As for the primary key, I suggest making it (system, id), where system is a unique system identifier and id is an IDENTITY value. A CHAR(1) or TINYINT will suffice for the system identifier if you have only a handful of systems.

Don't bother managing the ranges of the IDENTITY values across the different systems.  They can overlap as they please since the system identifier will distinguish records from different systems that share the same id.


for Insert multiple rows in one single insert MySQL I have done bulk-insertion in MySQL using multiple row insertions method by using python script. My testing csv file consists of 100,000 lines only (real one consists of millions lines).
so I perform bulk-insertion.

and by using unix time command the results looks like

for 10 lines            100 lines               1000 lines

user 13.675 seconds     user 11.948 seconds     user 9.908 seconds
sys  0.192 seconds      sys  0.076 seconds      sys  0.080 seconds

for 10,000 lines        100,000 lines

user 11.672 seconds     user 12.024 seconds
sys  0.072 seconds      sys  0.079 seconds

to make it more simple I add user + sys and results are

10 rows           13.867 seconds
100 rows          12.024 seconds
1000 rows         9.988 seconds
10000 rows        11.744 seconds
100,000 rows      12.103 seconds

I am trying to figure out what is main reason that multiple rows insertion is better around 500-1000 lines and it becomes counter productive below 500 lines and above 1000 lines. I search over internet and found different answer.
some mention that it depends on

max_allowed_packet, bulk_insert_buffer_size, key_buffer_size .

I try these parameters but unable to see any effects.

My question is why bulk-insertion is optimum between 500-1000 rows per insert and its counter productive other than this range and what are the main factors.
I am already using this settings

 max_allowed_packet=16M

there are some parameters also.

Table     Non_unique Key_name  Seq_in_index  Column_name
roy_table    0       PRIMARY   1                id

Collation Cardinality  Sub_part  Packed  Null  Index_type
 A       100650        NULL     NULL           BTREE

Can some body please point out a benchmark about bulk insertion efficieny or any idea how can I address why this is counter productive above certain point. I had to give solid reason in my report. I will really thankful for any small hint or idea. thanx

The elapsed time, not the CPU time is more interesting.
You are measuring the client time, not the server time.
There are many factors on the server that get 'bad' for large chunks: buffers, undo log, replication delays, etc.
The parse time is the main overhead (on the server) for short chunks.
You have not specified the row size; this has some impact on the client, network, and server.
What connection between client and server?  Socket?  LAN?  WAN?  What is the latency between them?  On a WAN, the network delays would overwhelm perhaps all other considerations.
`key_buffer_size applies only to MyISAM; I hope you are not using that Engine.
Were there any indexes on the table?  If so, what?  If, for example, the PRIMARY KEY were a UUID, there could have a huge impact.  Also, UNIQUE keys need to check for duplicates.  (Again, this is a server task.)

My experiments show that chunks of 100-1000 rows is close to optimal in many cases.  (This happens to somewhat agree with your findings, but that is coincidental, since I looked at different timings.)


Constant MysQL crashes. How to fix? MysQL is frequently going down. Error logs contain:

[ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it  | 149 occurrences
[ERROR] Native table 'performance_schema'... has the wrong structure                    | 500+

I did a research on the internet. It is suggested to fix it by running a mysql_upgrade script like this:

/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables &
mysql_upgrade
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

It is strongly advised to create a backup before running any upgrades. But my databases do not look healthy.

There is no snapshot option for this server.

Questions:

How do I preserve the information?
How would I go about it?
Is it okay to do mysqldumps for such databases?

So I finally fixed it.

I ran upgrade script as described in question.

The logs complained only about the wrong structure of performance_schema which is a system database that contains only performance metrics and a missing proxies_priv table inside other system database.

The errors are gone.

And of course before making any changes I did a backup of all databases with mysqldump.

Thank you all for help! :)


creating a mysql.cnf file for import I have created a config file for dumping .sql with no password prompt, is it possible to do this when using import?

I have read somewhere to just add on the cnf file "mysqlimport" then the credential details, but gets me back with an mysql error code 2000 'user'@'localhost' access denied, using password (NO).

I also tried granting this user access, but no success. This user is different from the user I use for sql dumps.

This is how I run the import command:

mysqlimport --defaults-extra-file=~/.mysql/mysqlimport.cnf reports < /tmp/testdump.sql

error message is

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)

I've decided to not use the cnf file, instead I used .yaml in solving this.

Thanks for answering.


How to check for Non-Ascii Characters What is the best way to check if a VARCHAR field has Non-Ascii Characters?
CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255).

I tried using PATINDEX and have run into the following issue.

Checking the lower range worked correctly.

SELECT *
FROM mbrnotes
WHERE PATINDEX('%[' + CHAR(1)+ '-' +CHAR(31)+']%',LINE_TEXT) > 0

My data had three records with 0x1E and all three where returned.

But when I check just the upper range:

SELECT *
FROM mbrnotes
WHERE PATINDEX('%[' + CHAR(127)+ '-' +CHAR(255)+']%',LINE_TEXT) > 0

It returns close to all the records in the table (table count 170737 and returned count 170735) and since my data did not have any values in this range I would think it should have returned no records.

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *
FROM mbrnotes
WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2


SQL Audit and AlwaysOn Replicas We would like to enable SQL Audit on a SQL Server 2014 database. This database is replicated to a read replica via AlwaysOn. We don't need, or want, the replica audited. If we enable audit on the main database, will it automatically get replicated to our replica? If so, is there a way to prevent this?

Reference: SQL Server Audit (Database Engine)

  If we enable audit on the main database, will it automatically get
  replicated to our replica?

No it will not automatically get replicated.

The Database Audit Specification object also belongs to a SQL Server audit.

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope.


How convert database from SQL Server 2012 TO SQL Server 2005 I have SQL Server 2012 database which I need to use. But I can't install SQL Server 2012 on my PC.

I use SQL Server 2005 only.

How can I change the database SQL Server 2012 To SQL Server 2005 OR 2008

Or help with installing SQL Server 2012

If you can't install SQL Server on your PC the only choice you have (if the database is already 2012) is to script out the databases objects, change anything that isn't compatible with 2005, and then run the scripts for your 2005 instance.

Because there are many changes between 2005 and 2012, datatypes among other database objects may not align properly. This means the application(s) being used with the database if put into 2005 from 2012 may not function correctly or at all.

Your best option is to get a waiver to install 2012 developer edition or some other test system for your 2012 needs.


Following mysql query is taking too much time for excecution I have a MySQL table which is described in the following question.
Counting number of ids present in one table which is not presented on another for a particular user
The query which I am using is as follows.

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id)

This query is taking too much time for considerably bigger table how can I improve the performance of this particular query.

table2 needs INDEX(tableid, user_id) (in either order).

If this does not solve the problem, please provide SHOW CREATE TABLE for both tables, plus EXPLAIN SELECT ....


Copy Oracle database 10g to 12c I need to copy an Oracle 10g (10.2.0.4.0) database (150GB) to an Oracle 12c (12.1.0.2.0) database. Both are Standard Editions. What method should I use?

Target is on a different server - empty database. File system structure is different. Target is a Testserver which needs a consistent state but does not need to be up-to-date to production.

In your case you could not use transportable tablespace because the source database have to be Enterprise edition.

In general, I would prefer upgrade using rman. But in your case it is not so easy because direct upgrade from 10.2.0.4 to 12.1.0.2 is not supported.
You would have to upgrade to intermediate release (e.g. 10.2.0.5) that can be directly upgraded to 12.1.0.2.
See https://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD12359

So for you the best approach will be upgrade using old import (imp) or data pump import (impdp).

I have one recipe using old import. Upgrade using data pump import will be similar:

Assume the source database name is "sourcedb" and target "targetdb".

make full export on source database:

  set oracle_home=c:\oracle\product\10.2.0\db_1

  set oracle_sid=sourcedb

  c:\oracle\product\10.2.0\db_1\bin\exp system/pass buffer=1200000 file=d:\dump\full_sourcedb.dmp log=full_sourcedb.log full=y
  consistent=y

  copy full_sourcedb.dmp to the target machine

create new target database "targetdb" using dbca (custom database - select or remove all components what you need oracle spatial etc.)

choose the right character set. You can find out character set on your sourcedb by this query:

  select parameter, value from nls_database_parameters where parameter
  like '%CHARACTERSET%';

on the target machine run following command. It will produce the log with sql stamenets used by import, where you can find the create statements for creating tablespaces.
You have to edit them (with new datafiles locations) and run manually before import because old import cannot remap datafiles:

  create tablespace "TEST" blocksize 8192 datafile
  'd:\oradata\targetdb\test01.ora' size 2000m       extent management
  local  autoallocate  online permanent  nologging segment space
  management auto;

  etc.

after all tablespaces are created, you can run import on the target machine:

  imp system/pass full=y log=d:\dump\full_import_sourcedb.log
  file=d:\dump\full_sourcedb.dmp

check out d:\dump\full_import_sourcedb.log for errors

after sucessful import on the target machine:

  --recompile objects

  @%ORACLE_HOME%/rdbms/admin/utlrp.sql

  --check out all database components

  select  owner, object_type, object_name from dba_objects where status != 'VALID' order by 1,2,3;

  --gather statistics
       shutdown immediate startup

  begin
   dbms_stats.gather_database_stats(
            method_opt => 'FOR ALL COLUMNS SIZE AUTO',
            estimate_percent => 100,
            cascade => TRUE   );

  end;

  /

Note:
import using data pump import (expdp, impdp) may be easier:

instead of consistent=y you have to use flashback_time="to_timestamp('04-04-2016 11:55:00', 'DD-MM-YYYY HH24:MI:SS')" .........

During import you don't have create tablespaces manually and you can use remap_datafile parameter.


mysql /dev/shm question I have this in my.cnf:

tmpdir = /dev/shm

and I'm getting this in my error logs:

[23-Apr-2011 10:22:08] WordPress database error Incorrect key file for table '/dev/shm/#sql_30f1_0.MYI'; try to repair it for query  SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY RAND() DESC LIMIT 0, 5 made by require, require_once, include, get_posts, WP_Query->query, WP_Query->get_posts

The file size is very large - over 3 GB in size, but there are other tables in that directory getting the same error are 1024 bytes - not sure what is going on here

How do I try to repair a temp table? I'm getting this error A LOT!

Dan

PS on a separate note - anybody know where in thewordpress core this call is being made? I haven't been able to track it down - I want to disable it because of the ORDER BY RAND() call in it.

/dev/shm is shared memory. That will compete with OS for available RAM. You simply do not have enough RAM to hold that large temp table.

You need to comment out that line in /etc/my.cnf

[mysqld]
#tmpdir=/dev/shm

and restart mysql. That way, tmpdir default to the OS tmp folder, which is /tmp. That will put temp tables on disk.

If you still want /dev/shm, instead of commenting out tmpdir, just increase RAM on the server.


Troubleshooting TempDB for Performance Bottlenecks While trying to investigate some performance issues with our SQL Server database, I've noticed our wait statistics seem high for ASYNC_IO_COMPLETION and PAGEIOLATCH_*. In Resource Monitor I noticed that tempDB is frequently (at least 1 time per hour) Reading/Writing large amounts of data, Total (B/Sec) range from 5GB - 25GB.

The tempDB is on a separate Virtual Disk than the Database and Log Files. TempDB size is 4 GB. Our main DB and Log file is 40GB in size each. I've tried researching perfmon counters for this but haven't been able to find anything I can make sense of in order to figure out why I'm getting certain numbers.

Is it unusual that the total data for tempDB is so high through resource monitor? Our main Database is only growing about 50MB per day. What should I be looking for to figure out what is causing such high data on tempDB and if this is causing an I/O Bottleneck? If the best method is by using specific perfmon counters, which counters should I be looking at and what values are good or bad?

Tempdb is used to hold:

Temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.To capture sessions which are causing tempdb allocation ,try to use sp_blitz or refer this article more insight into tempdb. https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/


Ops Manager Install in Windows and connect to mongod instance started in linux Can we install ops manager in windows and connect to mongodb instance started in Linux operating system? Does ops manager installation on windows require separate installation of mongoDB instance on windows also?
Can we install ops manager on linux and access it from windows using URL?Please guide.

Can we install ops manager in windows and connect to mongodb instance
  started in Linux operating system?

No, I have not done as practically , as per my knowledge due to platform & their connection string used to access the Ops Manager Application Database.

  Does ops manager installation on windows require separate installation
  of mongoDB instance on windows also?

No, Because, the databases require dedicated MongoDB instances. Do not use MongoDB installations that store other data. The Backup Database is required only if you will use the Backup feature.

  Can we install ops manager on linux and access it from windows using
  URL?

Yes,For example you are going to access ops manager through the following URL

http://<OpsManagerHost>:8080

You have to make sure that the host is the fully qualified domain name of the server.

For further your ref here , here and here


How to convert unsigned int accidentally stored as signed int? During a data conversion process, a field that was apparently an unsigned int was stored in a Sql Server int field(which is signed).

To remedy this apparently requires some combination of bitwise operators and type conversion that I'm struggling with.

For example the value 141 is stored as

  -29440 or 0x000000000000000000000000000000000000000000000000000000008D00

and the value 362 is stored as

  27136 or 0x000000000000000000000000000000000000000000000000000000006A00

Presumably I need to convert to binary first to thwart any implied casting.

A smallint in SQL Server is stored as a signed int16, or 2 bytes, or 16 bits with the 16th bit reserved to indicate the sign (0 = positive, 1 = negative).

Here are your two examples, converted to the original un-signed int (uint), and int16 values: It looks like in your examples that your bits have been shifted to the left by 8 bits. Noticed the bolded items below are the same.

Original Value 1: uint = 141    = 0x008D = 00000000 10001101
Was converted to: int16  = -29440 = 0x8D00 = 10001101 00000000

Original Value 2: uint = 362   = 0x016A = 00000001 01101010
Was converted to: int16  = 27136 = 0x6A00 = 01101010 00000000

Unfortunately, in your second scenario (the conversion from 362 that resulted in 27136), the 1 in the 9th bit position (00000001 01101010) has been dropped during your data conversion.

It appears your conversion process was something like:

Shift the original # to the left by 8 bits (this will add 8 bits of 0 padding added on the right-hand side),
Convert to smallint (16 bit data type), in a way that truncated the 8 left-most bits.

In this case, then any # greater than 256 in your original dataset would have been converted to a # that represents only the 8 right-most bits with 8 bits of 0 padding adding on the right, and you will not be able to recover any number from your original data that was above 256 because it looks like the left-most 8 bits have been truncated during the failed data conversion to a 16 bit data type.

For example, given the # 27136 (01101010 00000000) in your current data, you cannot know if this original # was 106 (00000000 01101010) or 362 (00000001 01101010) or 874 (00000011 01101010), or any # that originally had a 1 within the position of bits 9-16.


How to efficiently sort a column from two tables? In our app, we have assignments. We create this by unioning two other tables with a due_date column: checklists and tasks. We want to order assignments by the due_dates. Right now, we are doing something like this:

select * from (
  select a.type, t.id, t.due_date from assignments a
    inner join tasks t on t.id = a.task_id
  where a.type = 'Task' and a.user_id = 'X'
  union
  select a.type, c.id, c.due_date from assignments a
    inner join checklists c on c.id = a.checklist_id
  where a.type = 'Checklist' and a.user_id 'X'
)
order by due_date desc
limit 20

Obviously, this goes very slow because it needs to generate all the task and checklist assignments (which can be in the 100000s) and then sort them and grab the top 20.

Adding an index doesn't help because the due_date field is in separate tables.

We looked at table inheritance, but it seems to just be syntactic sugar for unions, so doesn't really help.

Also, because there are so many assignments, it seems like materialized views wouldn't help because they'd have to be refreshed every 100ms or so.

What is a good way to optimize this type of query?

You might get improved performance by doing the following:

Use UNION ALL instead of UNION (as suggested by Thangamani Eraniyan).
Make sure both tables have an index on due_date (or with due_date as the first column, if there are more columns indexed).
Use the ORDER BY and the LIMIT 20 on the separate queries of the two tables, as well as on the final combined result. The individual queries do have to be subqueries; otherwise, we can't use ORDER BY on them.

The principle is to limit the number of records that need to be looked at in both individual tables. With an index on due_date in both tables, it should be able to find the most recent records quickly. We know that the top 20 records from both tables can't include more than 20 tasks or more than 20 checklists, so if we just grab the top 20 from each table, we've definitely got the top 20 form the combined tables. The final ORDER BY ... LIMIT simply drops out rows 21-40.

Try this; I tested the same basic query via db-fiddle, and it worked (so, barring any cut-and-paste errors combining that with your actual query, this should work as well).

SELECT type, id, due_date FROM
   (SELECT a.type, t.id, t.due_date
      FROM assignments a
             INNER JOIN tasks t on t.id = a.task_id
     ORDER BY t.due_date ASC
     LIMIT 20) sq1
UNION ALL
SELECT type, id, due_date FROM
   (SELECT a.type, c.id, c.due_date
      FROM assignments a
             INNER JOIN checklists c on c.id = a.checklist_id
     ORDER BY c.due_date DESC
     LIMIT 20) sq2
 ORDER BY due_date ASC
 LIMIT 20;


Using table inheritance instead of mapping tables This seems like a pretty common scenario: several types that all compose the same child type.

This could typically look like so:

-- 'name' is unique per parent record
CREATE TABLE sometype (
  sometype_id serial PRIMARY KEY,
  name        text
);

CREATE TABLE foo (foo_id serial PK);
CREATE TABLE bar (bar_id serial PK);

CREATE TABLE foo_sometype (
  foo_id      int4,
  sometype_id int4
);

CREATE TABLE bar_sometype (
  bar_id      int4,
  sometype_id int4
);

Which is fine, but cumbersome to query. I'm thinking this could be cleaner:

-- 'name' is unique per parent record
CREATE TABLE sometype (
  name        text
);

CREATE TABLE foo_sometype (
  foo_id      int4
) INHERITS(sometype);

CREATE TABLE bar_sometype (
  bar_id      int4,
) INHERITS(sometype);

What I like about this:

simple to join (with USING)
no need to add a surrogate key to 'sometype', it is explicitly a component of 'foo' and 'bar'

Seems like an atypical use of inheritance, though.

Any reason not to do this?

On the "deficiencies" of inheritance

Note that the standard caveats to Pg inheritance are only relevant when table inheritance is used to directly model class inheritance, which is not what I am doing here. In fact, for this to work I need inheritance to behave the way it does.

I almost wish they called it something other than "inheritance", since the behavior is quite logical, and the "shortcomings" are only relevant to one use-case.

Benefits over manually duplicated table structure

As Evan points out, I could just manually create 'foo_sometype' and 'bar_sometype' that look exactly like what I describe, but I think there are a couple of significant benefits to the inherited structure:

The inherits relationship explicitly defines 'foo_sometype' and 'bar_sometype' as being of the same type, not just two tables that happen to have the same columns.
Making future schema changes through the parent table lessens the chance of accidental divergences (with a little work, this could actually be enforced).
More importantly, client code can be generated against the parent table, and applied to the children with only a change in the table name, with (again) confidence that the table structure is enforced.

So, as a contrived example, Foo and Bar could have a HasSomeTypeList trait, which abstracts all 'sometype' operations, and knows that both tables can be mapped to the SomeType class.

Representing the Foo/Bar relationship, whether it's modeled as a trait or as inheritance, is the ultimate goal here.

Incidentally, to the naive user/query-writer - who is not expected to make schema changes - these two ways will look the same.

Inheritance is one of those features that I wouldn't touch. AFAIK, it's used internally for replication and partitioning in some capacity. I'm not sure if it was even designed with the intent to be used by the end-user.

Concrete Technical Drawbacks

Drawbacks on UNIQUE and REFERENCES

The docs covers some of the drawbacks in the CAVEAT section (below is important).

If we declared parent.name to be UNIQUE or a PRIMARY KEY, this would not stop the child table from having rows with names duplicating rows in parent. And those duplicate rows would by default show up in queries from parent. In fact, by default child would have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to child, but this would not prevent duplication compared to parent.
Similarly, if we were to specify that parent.name REFERENCES some other table, this constraint would not automatically propagate to child. In this case you could work around it by manually adding the same REFERENCES constraint to child.
Specifying that another table's column REFERENCES parent(name) would allow the other table to contain parent names, but not child names. There is no good workaround for this case.

Slow progress developing INHERITs

These deficiencies were first mentioned in the docs to 7.3 released in 1996 though they existed since inheritance was implemented

  This deficiency will probably be fixed in some future release.

And the only change was to make the deficiencies more explicit and verbose in the docs to 8.0 released in 2010.

  These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your problem.

Good luck waiting for that some future release. And, some of the things you talk about features just aren't unique to composition,

Saving a "key" is moot

no surrogate key on 'sometype', it's explicitly a composition

How is that different from making sometype an attribute list, and linking directly to it?

CREATE TABLE sometype (sometype_name text PRIMARY KEY);
CREATE TABLE foo (foo_id serial PRIMARY KEY);
CREATE TABLE foo_sometype (
  foo_id int REFERENCES foo,
  sometype_name text REFERENCES sometype,
  PRIMARY KEY ( foo_id, sometype_name )
);

Now you don't even have to join foo_sometype to sometype to get sometype.sometype_name.

Table Partitioning

All of those problems aside, it gets even worse with the upcoming PostgreSQL 10 release of table partitioning

  Multiple inheritance is not allowed, and partitioning and inheritance can't be mixed

So you want inheritance? Forgo partitioning, which actually has real planner advantages.

ALTER TABLE

Alas, ALTER TABLE has quite a few drawbacks listed in its notes as well,

  If a table has any descendant tables, it is not permitted to add, rename, or change the type of a column, or rename an inherited constraint in the parent table without doing the same to the descendants. That is, ALTER TABLE ONLY will be rejected. This ensures that the descendants always have columns matching the parent. [...] A recursive DROP COLUMN operation will remove a descendant table's column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column. A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited. [...] The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT.

Conclusion

I don't think many people use inheritance. I've never seen it in the wild. Inheritance in the db adds to the learning curve and some features are just better left alone. You don't have to find an application for them.

You may find this post on Stack Overflow useful, "When to use inherited tables in PostgreSQL?".


database roles name convention i want to ask if there is any name convention for sql server database roles, what is best practise or what i should follow. We have databases and a lot of people and i want to suggest some rules. thank you

The default roles all have db_ and then something descriptive of the roles permissions. I would stick with that.