texts: Random selection with a "complex" subset structure

texts:
This is my first question in the whole StackExchange world.
I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question.

In my application I have to make a sequence of random selection in my data.
The structure is something like this (see db<>fiddle):

4 task (e.g. Eat, Sleep, ...)
10 mood (e.g. Calm, Excited, Happy, ...)
10 place (e.g. Bedroom, Batchroom, ...)

only a few combinations between these items make sense. I decided to use a link table to summarise all suitable combinations using foreign keys from the other tables (see db<>fiddle above). I want that only the combinations present in the link table can be chosen.

The steps to perform are the following:

Pick 1 task randomly (e.g. Eat)
Retrieve all combinations that contain that task (e.g. all combination about Eat)
Pick 1 mood randomly
Retrieve all combinations from step 2 list that contain step 3 mood
Pick 1 place randomly
Find the randomly selected combination

REMARKS
Every random pick (for each level) should be performed from a list that contains "unique" items. For example if a list with more than one combination with the same mood si accepted obviously the most numerous mood are more likely to be picked up.

All this procedure is my idea to avoid that "numerosity" of valid combination for different task can influence the probability of being selected. For example, if the selection is made simply choosing one row in link table and I got 20 combination for Eat task and only 5 for Sleep task it is more probable that an Eat task will be selected.

NOTE: I'm using MariaDB 10.3 (x64)

Real question
I'm a complete beginner with DBs (as you surely noticed).
Does it make sense to perform these kind of operations "Database-side"?
I think it would be much more easier to do something like this using the application that in the end uses these data from my DB. On the contrary I started considering the idea of performing this selection entirely with database with performance purpose.

About random selection of a row I've read: Data Sampling: Techniques for Efficiently Finding a Random Row that is a rewrite of Rick James' blog Fetching Random Rows from a Table
 and several question here on DBA.

I'm trying to write my query to perform operations in a smart way. The code is unfinished, maybe 30-40% of the final mission. Having no experience with MySQL I'm discovering new features everyday so I restarted from scratch several time. At this stage I prefer that focus goes on the question above. Obviously some advice regarding best strategy for writing this kind of queries are welcome.

create sql security invoker view filtered_table as
select *
from link_table
join (
    select task_id as id
    from task
    order by rand()
    limit 1
) as random
on link_table.task_id = random.id;
select * from filtered_table;

set @min := (
    select min(subset_id)
    from filtered_table
);
set @max := (
    select max(subset_id)
    from filtered_table
);
select @min, @max;

PS. sorry for my English, I usually eat Pasta and Pizza so... ¯_(ツ)_/¯


texts: How do you tell which host is close to being blocked in MySQL?

texts:
I have a two part question:

In cases were this error comes up, is there a way to see if a host is close to being blocked? And fire off an alert?

PDOException: SQLSTATE[HY000] [1129] Host `hostname` is blocked because of many connection errors; unblock with `mysqladmin flush-hosts`

What guidelines should one follow when increasing max_connect_errors-- ours is at 10? Is it recommended to run mysqladmin flush-hosts daily, via cron?

Running MySQL 5.5.13 (Enterprise) on Red Hat 6.


texts:
We have a production database that we have identified needs some maintenance as a colleague leaves - some column deletion and renaming, and a couple of table renames

We're trying to find the best (quickest) way to do this so that it causes the least inconvenience to the users.

Ideally we'd like to do the following

Create a copy of the current database using mysqldump
On the copy make the changes to required
Update the current applications to use the new column names and test against the copy
Somehow push the changes to production database and release updated apps into the wild

If we restrict the problem to column renames then it seems that we should be able to find an application to help us manage the change.

MySQL Workbench uses CHANGE COLUMN to rename columns.  Having looked around SQYLyog and Red Gate MySQL Compare have both been suggested as being suitable.  However while both of these recognise the columns have been renamed, they both use DROP COLUMN / ADD COLUMN which does not preserve the data

Red Gate SQL Data Compare doesnt recognise that the columns are the same after the rename so that is out as an option.

We'd like to avoid having to take the database server offline to make the changes, but is that the only option?


texts: How can I create readonly user for backups in PostgreSQL?

texts:
Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL?

I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges.  I find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't give me the results I'm looking for.  When I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!


texts: SQL Server Simplify Racing condition query

texts:
We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,

I wanted to simplify the following query, so I do not have to select for customerId twice, a local variable is not working in the Exists statement

CREATE TABLE dbo.Customer
(
    RowId bigint IDENTITY(1,1) NOT NULL,
    CustomerId guid NOT NULL,
    Name varchar(255) NOT NULL,
    CONSTRAINT PK_RowId PRIMARY KEY CLUSTERED([RowId] ASC)
)
create unique nonclustered index [UN_CustomerId] ON [dbo].[Customer] ([CustomerId] ASC) include (Name)
create nonclustered index [UN_Name] ON [dbo].[Customer] ([Name] ASC) include (CustomerId)

BEGIN TRANSACTION

IF NOT EXISTS
(
    SELECT top 1 CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)
BEGIN
    INSERT dbo.Customer(CustomerId, [Name])
    OUTPUT @CustomerId AS CustomerId
    VALUES (@CustomerId, @Name);
END
ELSE
BEGIN
    SELECT CustomerId FROM dbo.Customer WHERE [Name] = @Name;
END

COMMIT TRANSACTION;

Option with local variable not working:

declare @CustomerIdVar uniqueidentifier

BEGIN TRANSACTION

IF NOT EXISTS
(
    SELECT @CustomerIdVar = CustomerId  --- this was causing compilation error
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)
BEGIN
    INSERT dbo.Customer(CustomerId, [Name])
    OUTPUT @CustomerId AS CustomerId
    VALUES (@CustomerId, @Name);
END
ELSE
BEGIN
    SELECT @CustomerIdVar
END

COMMIT TRANSACTION;


texts: SQL Server 2014 SP2 upgrade on Alwayson + FCI

texts:
I want to upgrade my SQL Server 2014 to SP2 which is involved in SQL Server FCI and Alwayson. Below is my environment details:

    Nodes: SERVER-A, SERVER-B, SERVER-C, SERVER-D
    SQL Server clustered instances : Instance-A, Instance-B, Instance-C
    SQL Server edition and version : SQL Server 2014 EE SP1 on all 3 clustered instances
    Windows server edition and version : Windows server 2012 R2 STD

SQL Server clustered Instance-A runs on SERVER-A and is installed
between SERVER-A and SERVER-B.
SQL Server clustered Instance-B runs on SERVER-B and is installed
between SERVER-A and SERVER-B.
SQL Server clustered Instance-C runs on SERVER-C and is installed
    between SERVER-C and SERVER-D.
SQL Server clustered Instance-A has Alwayson configured for some DBs between Instance-A and Instance-C. Instance-A acts as Primary replica and clustered Instance-C acts as its secondary replica. Alwayson failover mode for DBs is manual.

Please let me know how should I go about patching both my SQL Server clustered instances and any precautions to be taken.


texts:
I have this setup

attendance table:
------------------------------------------
| member_id | attendance_date | event_id |
------------------------------------------

attendance indeces
---------------------------------------------
| p1 (member_id, attendance_date, event_id) |
| p2 (attendance_date, member_id, event_id) |
| member (member_id)                        |
| total (attendance_date, event_id)         |
---------------------------------------------

members table
-------------------------------------------------
| member_id | department_id | registration_date |
-------------------------------------------------

members indeces:
-----------------------------------------------
| PK (member_id)                              |
| registration (registration_date, member_id) |
| department (department_id, member_id)       |
-----------------------------------------------

So basically this is an attendance system. And I need to run reports based on the attendance table. I've figured out much of the queries needed except for the part where I need to know how many absences a member has for a given time. I pretty much know how to query for data that is there. But I do not know how will I query for data that is not there (absences).

I tried this:

SELECT
  attendance_date,
  COUNT(DISTINCT event_id)
FROM
  attendance
WHERE
  WEEKOFYEAR(attendance_date) BETWEEN 27 AND 31
GROUP BY
  WEEKOFYEAR(attendance_date)

which gives me the number of events a member should've been able to attend based on other's attendance record, but the problem is when there is an event with no attendees at all (unlikely) or when there is a newly registered member.

I tried this:

   EXPLAIN SELECT
     m.member_id AS id,
     COUNT(DISTINCT a.event_id)
   FROM
     members AS m
     LEFT JOIN
       attendance AS a
       ON
         m.registration_date < a.attendance_date AND
         WEEKOFYEAR(a.attendance_date) BETWEEN 27 AND 31
   WHERE
     m.department_id = 1

But EXPLAIN tells me that it will scan 4M rows so I know that it is not the way to go. I know that the query is wrong anyways since COUNT(DISTINCT) will give me multiple rows per member. Any thoughts? And any advice on how should I index the database for this query is also appreciated.

EDIT

To be more specific, this is what I want to do with the query:

Get all members of a department
Get the number of events that has a record in the attendance table between a given period but only those events that occurred later than the current member's registration date

When we subtract the total number of attendance records of the member, then we can actually get how many absences the member already has.

I know that part of this problem stems from a fundamental error in the structure of the application (events shouldn't be defined based on attendance records) but these events are recurring and are quite irregular in their occurrence patterns. So to minimize the need for maintenance, I just decided that I derive event information (or at least their count) be based on the attendance since it is very unlikely that an event will have 0 attendees.


texts: Are temp tables automatically created when referencing to them?

texts:
I came across the following script and I am trying to understand it.

select columnA, columnB, columnC
  into #tmpTable
  from tableA

The #tmpTable is referenced after that query, but not before it. There is not a declaration for #tmpTable anywhere in the sql script.

My somewhat general question is:

Will the above create the temp table automatically and why? Is this only for T-SQL or applies generally?


texts:
So I have a new SQL Server in place.  I have a 200GB database I am trying to restore to this new server and the restore time is taking longer than my current server.  My new server has a RAID 10 for the database files, a RAID 1 for the log files and a RAID 5 (where the backup files are located).  I can't seem to figure this out. When I try and copy files directly to the RAID 10, I am getting copying speeds to 1.2GB/sec, but when restoring through SSMS, (looking at the resource monitor), it is only moving at like 50MB/sec.  I just can't figure what is happening here.  Anyone have any tips?

Thanks in advance,


texts:
I am trying to create giant script which updates bunch of tables by adding/removing/modifying columns of existing database. And one issue that has stumped me is, whenever I add new column in table in one statement and try to refer it in another statement it fails with error about invalid column xxx.

Follow is pseudo code that I am using to reproduce this issue,

--CREATE TABLE testTable (u_id int, u_name varchar(10))
--INSERT INTO testTable VALUES (1,'11')
--INSERT INTO testTable VALUES (2,'12')
--INSERT INTO testTable VALUES (3,'13')
--INSERT INTO testTable VALUES (4,'14')
--INSERT INTO testTable VALUES (5,'15')

BEGIN
ALTER table testTable ADD u_add varchar(10)
END

BEGIN
CREATE TABLE tmp_xx_xx_testTable (u_id int, u_name varchar(10), u_add varchar(10))

INSERT INTO dbo.tmp_xx_xx_testTable VALUES (6,'16','DDD'  )

INSERT INTO tmp_xx_xx_testTable (u_id,u_name,u_add)
SELECT u_id,u_name,u_add FROM TestTable

drop TABLE TestTable

EXECUTE sp_rename
                N'[dbo].[tmp_xx_xx_testTable]',
                N'TestTable';
END

--SELECT * FROM testTable
--DROP TABLE testable

First (and last) few lines are just to prepare test table that I am using.

When I run this code, I get an error about column u_add not being valid. But when I run same statements individually, they run without any issues. So it looks like that somehow I am not understanding what else needs to be done here so SQL Server can actually "see" that newly added column.


texts: (1049, u"Unknown database '`canal_manager`'") when import data from sql

texts:
Now I am using this command to import sql into my database in MyCLI(Version: 1.8.1) in my server like this:
mysql root@10.244.2.226:(none)> source /home/miaoyou/canal_manager.sql

but give me this error:
(1049, u"Unknown database '`canal_manager`'")

and I am using this command to check exists database:
mysql root@10.244.2.226:(none)> show databases;
+------------------------+
| Database               |
|------------------------|
| information_schema     |
| canal_manager          |
| mysql                  |
| performance_schema     |
| sys                    |
| xtrabackup_backupfiles |
+------------------------+

obviously the database exists in my server. why still give me this error? this is my sql:
CHARACTER SET utf8 COLLATE utf8_bin */;

USE `canal_manager`;

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for canal_adapter_config
-- ----------------------------
DROP TABLE IF EXISTS `canal_adapter_config`;

balabalabala........

why did this happen and what should I do to fix this problem? I also tried to switch the database like this but problem still exists:
mysql root@10.244.2.226:canal_manager> source /home/miaoyou/canal_manager.sql
You're about to run a destructive command.
Do you want to proceed? (y/n): y
(1049, u"Unknown database '`canal_manager`'")
mysql root@10.244.2.226:canal_manager>


texts: Slow query while using DISTINCT in MYSQL with various other conditions (10 million records)

texts:
I am working on a social Network where, as usual there is a feed for users where they are able to see the activities of users a user is following and not the users who are blocked by the users.

This is the query that is being used currently from the Activities table

SELECT DISTINCT `activities`.`post_id`
FROM `activities`
WHERE (activities.user_id IN ([followed_user_ids]) AND
activities.language_id IN ([language_ids]) AND
activities.id <= ?) AND
(`activities`.`post_user_id` NOT IN
([blocked_and_deactivated_user_ids]))
ORDER BY `activities`.`id` DESC
LIMIT 10 OFFSET 10

This is the query which is causing issues. Sometimes the Mysql CPU usage goes to 100% which causes issues to the users. I have also used index on the columns being used in the activity table.

There are 10 million records in the Activities table.

What changes can i possibly do to optimise this?

Note - followed_user_ids/blocked_and_deactivated_user_ids - can have an array of thousands of ids.

Update 1

This is the new query that i formed ignoring the blocked users from post_user_id :-

"SELECT  DISTINCT `activities`.`id` FROM `activities`
INNER JOIN `users` ON `users`.`id` = `activities`.`user_id` AND
`users`.`deleted_at` IS NULL
LEFT OUTER JOIN `follows` ON `follows`.`followable_id` = `users`.`id`
AND `follows`.`deleted_at` IS NULL AND `follows`.`followable_type` =
'User' WHERE `activities`.`language_id` IN (19, 21) AND (activities.id
< 99999999) AND `follows`.`blocked` = 0 AND `follows`.`follower_id` = 1
AND `follows`.`follower_type` = 'User' AND `follows`.`followable_type`
= 'User' AND `users`.`deactivated` = 0
ORDER BY activities.id desc LIMIT 10 OFFSET 10"

This doesn't seem to help either. I need to get all the unique post activities from the users i am following, in a pagination format.


texts: Confused about maximum connections in PostgreSQL

texts:
I am building a web application and I using PostgreSQL as the database management system.

The following page details the maximum connections setting:

https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html.

I read about tuning on the following page

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

and it mentions

  Generally, PostgreSQL on good hardware can support a few hundred connections.

Question

I am confused about what defines a "connection". Many web sites have thousands or more users and it seems if it can only support a few hundred connections at once, then significant horizontal scaling would be required.  Are connections very short?...Meaning, if a user loads a page and it queries the database, does it open and close a connection before and after?


texts: SSIS VS17 Unsupported: This version of visual studio is unable to open the following projects dtproj

texts: I get this error when trying to open VS17 With SSIS Project written on VS15:


texts: Oracle, Create table with inline constraint declarations using a different tablespace

texts:
I have two questions about inline constraints declarations for Oracle tables:

Is it a bad practice? If so, why?
How could one declare a different tablespace for the primary key and index like it's done when using the outline declaration? Something like

    create table THIS_TABLE (
        id number,
        constraint THIS_TABLE_PK (id) tablespace INDEX_TABLESPACE
    ) tablespace DATA_TABLESPACE;


texts:
I have a table with following 3 fields

1) Id      bigint
2) Name    varchar(100)
2) Country varchar(50)
3) State   varchar(50)
4) City    varchar(50)

In a country there are no. of states and in a state there are no. of city

I have two queries

a) If I want to know name of persons who are living in a city='Nagpur'

should I use index only on column city
should I use composite index on city,State & Country
should I use composite index on country, state & city

b) If I know that person with ID 1 to 100 are in Nagpur, for such case

should I create index on ID field
should I create index on City field

If you will suggest to create index on id field that reason will be

Due to it is a bigint and bigint occupy only 4 bytes
Id field is unique so it has more selectivity, so even if output of both query will be same Index on Id will give faster respnose.


texts: Hide columns that are constant across a small number of rows

texts:
I have a query that returns a small number of rows (usually 2-5) but a huge number of columns (several hundred).  The rows are mostly identical, save for a few places.  I would like to quickly identify them by hiding columns which are constant (e.g., I have 5 products, all of which cost $3, so the cost column should be suppressed). Is there an elegant way to do this using MySQL?  If it helps, most of the columns are numerical.

Here's a tiny example: Suppose my initial query returns

col1|col2|col3|col4
 1  | x  | a  | 3.14
 1  | x  | b  | 3.14

Columns 1, 2 and 4 are "constant", while column 3 is not. Therefore I would like a secondary query to return only column 3:

|col3|
| a  |
| b  |

The problem is that manually finding a small number of non-constant columns is quite difficult with a large number of constant columns.


texts: Postgresql: How to add a role that inherits from another?

texts: Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?


texts: Always On - keep secondary database readable when connection to primary node failed

texts:
By default, secondary replica brings databases into offline state when connection to the primary replica fails. We are using SQL 2016 SP1.

The goal is to reduce interruption for users on secondary async replica when primary replica goes offline for maintenance or due to network issues.

Is there a way to change this behavior and keep secondary databases always online ?


texts: Help me to better design my query so I can gain more performance

texts:
I need to find so called first entry in database for items regarding their partners.
I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed conception therefore this query can be written much better.
So I am asking you to help me redesign this query to get better performance.
My running database system is  Sql Server 2008

declare @od_datuma datetime
declare @do_datuma datetime

set @od_datuma='2011-12-01'
set @do_datuma='2011-12-31'

select
doc.PAR_ID
,sdo.art_id
,MIN(doc.id) as doc_id
from
dokumenti
inner join DOC on dokumenti.tip=DOC.TIP
inner join SDO on DOC.ID=SDO.DOC_ID
left  outer join (
    select par_id,art_id from dokumenti
            inner join DOC on dokumenti.tip=DOC.TIP
            inner join SDO on DOC.ID=SDO.DOC_ID
            where   dokumenti.NABAVA =1 and
                    dokumenti.KOL_UL=1 and
                    DOC.DATUM<@od_datuma
                )  as stari_uazi on DOC.PAR_ID=stari_uazi.PAR_ID and SDO.ART_ID=stari_uazi.ART_ID
where
dokumenti.NABAVA =1 and
dokumenti.KOL_UL=1 and
stari_uazi.ART_ID is null and
doc.datum between @od_datuma and @do_datuma
group by doc.PAR_ID,sdo.art_id


texts: Ways to process XML into a relational structure in Postgres

texts:
I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw data table. From this table I am using xpath to extract data items into their respective tables for each raw record. Each XML file has unique data which goes into a single row in a "unique" table, with the row id stored in a variable to be called later when filling the join tables.

Currently I am using a trigger function for this, called AFTER INSERT OR UPDATE ON raw_data FOR EACH ROW, of general form:

DECLARE
  #variables declared here, v_blah format
BEGIN
  BEGIN
    INSERT INTO unique (fields) VALUES (xpath statements)
        RETURNING unique_id INTO v_unique_id;
    END;
  BEGIN
    BEGIN
      INSERT INTO table1 (fields) VALUES (xpath statements)
        RETURNING table1_id INTO v_table1_id;
      EXCEPTION WHEN unique_violation THEN v_table1_id :=
        (SELECT table1_id FROM table1
        WHERE field = xpath statement from above
        AND field2 = xpath statement 2 from above AND etc);
      END;
    BEGIN
      INSERT INTO unique_table1_join (unique_id, table1_id)
        VALUES (v_unique_id, v_table1_id);
      EXCEPTION WHEN unique_violation THEN CONTINUE;
      END;
    END;

  --...other tables/joins of similar nature to table1 here...

  FOREACH v_item IN ARRAY (xpath array statement) LOOP
    v_item_name := xpath statement
    v_item_prop1 := xpath statement
    v_item_prop2 := xpath statement
    v_item_prop3 := xpath statement
    BEGIN
        INSERT INTO table2 (fields)
            VALUES (v_item_name, v_item_prop1, etc)
                RETURNING table2_id INTO v_table2_id;
        EXCEPTION
            WHEN unique_violation
                THEN v_table2_id := (SELECT table2_id FROM table2
                WHERE field = v_item_name AND field2 = v_item_prop1 AND etc);
        END;
    BEGIN
      INSERT INTO unique_table2_join (unique_id, table2_uid)
        VALUES (v_unique_id, v_table2_id);
      EXCEPTION
        WHEN unique_violation THEN CONTINUE;
      END;
    END LOOP;

    --...other tables/joins of similar nature to table2 here...

  --...creation of temporary table for combination of a couple of xml arrays into
  --1 logical array and then insertion/join similar to above (not included as too
  --arduous to sanitise)...

END;

I am hoping that people here who are far more knowledgable in the arcane can suggest faster ways to do this kind of insert/shredding operation. I am using Postgres 9.3.

Possible changes I have considered but not found enough information about to understand the relative speed of include:

1) Using NOT EXISTS or NOT IN searches for prevention of duplicate insertion instead of the EXCEPTIONs.

2) Sending results from the array handling sections to temporary tables before comparing the result to the live tables, to minimise the amount of work inside the FOREACH loops.

3) Splitting the trigger function up into multiple functions that each handle one unique-table pair including joins.

I found related questions here and here but neither was applicable enough for it to answer my question.

Thanks for reading


texts: SQL Server Cluster: There is no SQL Server failover cluster available to join

texts:
I have set up a SQL Server 2016 Standard cluster (Cluster-SQL) on node 1 (Cluster-Node-1) which is appearing in Windows Failover Cluster manager without issue.

When I attempt to add a second node (Cluster-Node-2) to this cluster and I reach the Cluster Node Configuration, there is no SQL Server instance name listed or available from the drop down.

My Net Admins are at a loss on this and I'm an accidental DBA from a programming background so any suggestions as to what to look for would be appreciated.

To date, we have disabled firewalls on both nodes of the cluster. The SPN's have been set up and I can access the SQL Cluster, Cluster-SQL using SSMS from both nodes.

Any other options to try that are obvious to more experienced DBA's?

Regards,
Brian


texts: Find filtered indexes on an Oracle table

texts:
How can I find if an index is filtered?
Filtered indexes (borrowing the term from SQL Server, or known as "partial indexes" in PostgreSQL) can have very high performance when they focus on small subsets of rows (a table that represents a queue of unprocessed orders or messages), or can work as concealed constraints.
For example:
create table product_price (
  product_id int not null,
  price numeric(12, 2) not null,
  since date not null,
  active int not null check (active in (0, 1))
);

Now, to ensure only one price is active per product, we can create the following filtered index:
create unique index i1 on product_price (
  case when active = 1 then product_id end
);

If I try to insert a second active price for the product (4th insert), it fails:
insert into product_price (product_id, price, since, active)
  values (123, 9.99, date '2020-06-01', 0); -- succeeds
insert into product_price (product_id, price, since, active)
  values (123, 10.99, date '2020-12-01', 1); -- succeeds

insert into product_price (product_id, price, since, active)
  values (456, 15.99, date '2020-07-01', 1); -- succeeds
insert into product_price (product_id, price, since, active)
  values (456, 17.99, date '2020-11-01', 1); -- fails as expected
-- ORA-00001: unique constraint (I1) violated

In my real use case I need to identify these concealed constraints. They were created in the DEV and TEST schemas but not in PROD. I need to find the growing bad data in PROD (400+ tables) and decide how to tackle it.


texts: SQL server actual execution plan: finding bottlenecks

texts:
I have a batch (7 queries) which executes in a couple of seconds locally by takes more than an hour in production.

I've have a .sqlplan XML file from that long running query on production and trying to find the bottleneck based on the execution plan. I'm looking for Actual number of rows = a big number, but don't see anything above a 1000 rows, I'm expecting something in the millions there considering it the query took so long.

I think this is not a deadlock because running sp_who shows the first query from the batch doing tens of millions of IO operations, but no clue about it in the actual execution plan.

What other metrics in the execution plan should I pay attention too to try find the bottleneck?


texts: Why does psql open two tcp connections to PostgreSQL?

texts:
If I for instance place a iproute, like this

sudo iptables -I INPUT -p tcp --syn -m conntrack --ctstate NEW --ctstatus CONFIRMED --dport 5433 -j LOG

I can see that there are two entries every time I try to connect to my database.

[598519.458578] IN=lo OUT= MAC=00:00:00:00:00:00:00:00:00:00:00:00:08:00 SRC=192.168.0.101 DST=192.168.0.101 LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=49795 DF PROTO=TCP SPT=58554 DPT=5433 WINDOW=43690 RES=0x00 SYN URGP=0
[598520.875428] IN=lo OUT= MAC=00:00:00:00:00:00:00:00:00:00:00:00:08:00 SRC=192.168.0.101 DST=192.168.0.101 LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=65392 DF PROTO=TCP SPT=58560 DPT=5433 WINDOW=43690 RES=0x00 SYN URGP=0

Why does psql need two connections to PostgreSQL Server on two separate ports (58554 and 58560)? That is to say, I get two messages like above when I run this once..

psql -d test -h 192.168.0.101 -p 5433


texts: Elegantly handling 'enum-like' magic numbers in T-SQL code

texts:
We run Dynamics GP, and anybody that's dealt with GP will be familiar with all of its "magic number" columns representing the various enum values inside the application. For example, a reporting query might look something like this:

...
WHERE sod.SOPTYPE = 2
    AND iv.VCTNMTHD = 3
    AND pod.POLNESTA IN (2, 3)

...which is about as far from self-documenting as possible.

At first I had the bright idea of creating a table called 'Enums', filling it with values, and making a scalar wrapper function so you could query like this:

...
WHERE sod.SOPTYPE = Enum('Sales Doc Type', 'Order')
    AND iv.VCTNMTHD = Enum('Valuation Method', 'Average Perpetual')
    AND pod.POLNESTA IN (Enum('PO Line State', 'Released'), Enum('PO Line State', 'Change Order'))

Of course, that sucked, because the optimizer couldn't see the underlying 'constant' value, and thus made some rather poor choices since it had to make cardinality guesses.

So it needs to be an actual constant value in the code, but I'm not sure if there are any nice features built into SSMS that would make this sort of thing easy. Could I (mis)use code snippets or templates in some way? If we could do some kind of lookup/insertion to make our code look something like this, that would be awesome:

...
WHERE sod.SOPTYPE = /*Order*/2
    AND iv.VCTNMTHD = /*Average Perpetual*/3
    AND pod.POLNESTA IN (/*Released*/2, /*Change Order*/3)

I'm also not opposed to checking out SSMS add-ins.


texts: Why does my INSERT fail on SELECT permissions when I have change tracking enabled?

texts:
I have a user in a SQL Server 2014 database that only has permissions to insert into a table (no select permissions). When change tracking is enabled on the table, the inserts will fail with The SELECT permission was denied on the object ...

Why is this the case?

Steps to reproduce:

USE [master];
CREATE DATABASE insert_test;
GO
USE insert_test;
GO
CREATE TABLE dbo.test(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, value NVARCHAR(50));
ALTER DATABASE insert_test SET CHANGE_TRACKING=ON;
ALTER TABLE dbo.test ENABLE CHANGE_TRACKING;
GO
CREATE LOGIN test_user WITH PASSWORD='FD3nIk1p(4$LKH!eSY';
CREATE USER test_user FOR LOGIN test_user;
GRANT INSERT ON dbo.test TO test_user;
GO
EXECUTE AS USER='test_user';
INSERT INTO dbo.test(value) VALUES(N'hello');  -- Fails with: The SELECT permission was denied on the object 'test', database 'insert_test', schema 'dbo'

/*
-- Cleanup
USE [master];
GO
DROP DATABASE insert_test;
GO
DROP LOGIN test_user;
GO
*/


texts:
I am designing a process to test my postgresql 10.8 backups by restoring them to a random point in time in a throwaway virtual machine. I have been unable to fully automate the process though. I am blocked at step 8 (section 25.3.4) of the official documentation

  Start the server.

When executing the pg_ctl start over ssh, the command hangs until it is killed. If I ssh directly onto the VM and execute pg_ctl start, then the command returns quickly as expected.

This thread from 2012 seems to describe a similar scenario. In my case however the postgres process does start successfully even if the calling session is killed while it is hanging (possibly an improvement between 9.0.5 and 10.8 though?).

This github issue seems relevant but sadly is "solved" by a long rewrite in a language I don't know and concludes finally that it's a bug in the pg_ctl binary.

The Big Question

  How can I automate step 8 so that I can continue on to subsequent validation tests of my backup media?

Is this an outstanding bug in the binary I need to hack around? Or have I missed a sensible implementation?


texts: I have a table Persons with id as pk and auto increment. Then I have a table Addresses with id as pk and fk to the Persons id. But now person can exist without an address, and I don't want a person to exist without an address. I am looking for a standard way to solve this problem. I am using MySql and InnoDB.


texts: Is this design in 3NF? Why to choose another design not in 3NF

texts:
Having the following entities: Objects, Parts, Subparts and Items, and given the following design restrictions:

A Part belongs to one and only one Object.
A Subpart belongs to one and only one Part.
An Item belongs to one and only one Subpart.

I have implemented the following tables (look at the composite primary keys marked with *):

Object (*IDObject)

Part (*IDPart, *IDObject): IDObject is a Foreign key (the prim. key of Object).

Subpart (*IDSubpart, *IDPart, *IDObject): *IDPart, *IDObject is a Foreign key (the prim. key of Part).

Item (*IDItem, *IDSubpart, *IDPart, *IDObject): *IDSubpart, *IDPart, *IDObject is a Foreign key (the prim. key of Subpart).

1.- I have understood that this design is in 3NF, isn't it?
Assuming that--by theory--Item is a weak entity depending on Subpart; Subpart is a "weak entity" depending on Part, and Part is a "weak entity" depending on Object, these would be the reasons for those composed keys, making this design in 3NF.
But for other people, this design is redundant, and the "best" way for resolving it is through the following tables specification (look at the primary keys marked with *):

Object (*IDObject)
Part (*IDPart, IDObject): IDObject is a Foreign key, but doesn't make part of the prim. key of Part.
Subpart (*IDSubpart, IDPart): IDPart is a Foreign keys, but doesn't make part of the prim. key of Subpart.
Item (*IDItem, IDSubpart): IDSubpart is a Foreign keys, but doesn't make part of the prim. key of Item.

In this specification, the restrictions of "one and only one" would be managed by code.
2.- Why would I choose this implementation, declining to use 3NF?


texts:
I need to aggregate rows with a single column of type INT[] into a single array of type INT[].

The following results in an error "could not find array type for data type integer[]":

create temp table vals (j int[])
on commit drop;
insert into vals
values ('{1,2}'), ('{1,5}'), ('{3,6}');
select array_agg(j) from vals;

The result that I need from the example is a single row that looks like {1,2,1,5,3,6}

The order of records is unimportant as long as the paired numbers remain adjacent to each other in the final array.

I'm out of ideas. How can I get the result that I need?

(PostgreSQL 9.4, Ubuntu 15.04)


texts: SQL Error [1292] "Truncated incorrect INTEGER value" - with Varchar columns and CASE. Why?

texts:
In a MySQL 5.7 environment, when trying to insert a varchar value into a varchar field, I get the error:

SQL Error [1292] [22001]: Data truncation: Truncated incorrect INTEGER value: '1876301/347'

The source and target columns are both of the datatype varchar.
Here's a small demo to reproduce an example of the error (it occurs on the last insert):
create table SRC
(
    Z varchar(20) null,
    A varchar(63) null,
    B varchar(30) null
);

insert into SRC
select 'XYZ', '1876301/347', null
;

create table TGT
(
    C varchar(63) null
)
;

To generate the error, now run this: (is the 3rd WHEN that matches, meaning it's trying to insert the value from SRC.A into TGT.C)

insert into TGT
(
    C
)
select
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NULL THEN NULL
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NOT NULL THEN B
        WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND B IS NULL THEN A
        WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND if(Z in ('XYZ'), A, NULL) = B THEN A
        ELSE if(Z in ('XYZ'), A, NULL)
    END AS C
from
    SRC

It seems to be related to there being a case statement, as, when I try to simply insert the same value directly, it works fine.
insert into TGT
(
    C
)
select
    A
from
    SRC

It also works fine with the CASE STATEMENT if I use INSERT IGNORE INTO..., but I'd rather not use that.


texts:
Our customer gave us a .bak archive with name file_decrypted_379299.bak which we know was created with Oracle 9i in "hot" style.

We tried to recover the bak file with Oracle 9i RMAN but we don't know how to make reference to this file.

We have not found any specific reference, documentations explains we need specific parameters from how this bak file was created... but customers says "normal" parameters.

We just installed oracle 9i with default database configuration, we don't want database instead we only want SQL tables and data.

Do you know how to ask for these parameters?
How to specify in RMAN the location of this file?

Any tool to recover data without installind the database?

Thanks for your help


texts: Should I store data pre-ordered rather than ordering on the fly?

texts:
I'm using MySQL and I'm wondering if it's a good strategy to presort my data so when a user accessed the information, it's not having to sort it on the fly?

Basically, I have an HTML table with is being populated with paginated data from the database, this is ordered by a particular column and can sometimes be a little sluggish - I was thinking about reordering the table on a nightly basis so the order by can be removed from the query.

Is this general practice or should I avoid this?

Update

My query is as follows:

'select keyword, position, impressions, clicks, ctr
 from keywords where profile_id=%s
 order by impressions desc limit %s, %s', (profile_id, start, end))

My table looks like this:

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| profile_id          | int(11)       | YES  | MUL | NULL    |                |
| landing_page_id     | int(11)       | YES  | MUL | NULL    |                |
| keyword             | varchar(2083) | YES  |     | NULL    |                |
| position            | int(11)       | YES  | MUL | NULL    |                |
| impressions         | int(11)       | YES  | MUL | NULL    |                |
| ctr                 | float         | YES  | MUL | NULL    |                |
| clicks              | int(11)       | YES  | MUL | NULL    |                |
| unique_key          | varchar(200)  | YES  | UNI | NULL    |                |
| position_30_days    | int(11)       | YES  |     | NULL    |                |
| impressions_30_days | int(11)       | YES  |     | NULL    |                |
| clicks_30_days      | int(11)       | YES  |     | NULL    |                |
| ctr_30_days         | float         | YES  |     | NULL    |                |
| position_60_days    | int(11)       | YES  |     | NULL    |                |
| impressions_60_days | int(11)       | YES  |     | NULL    |                |
| clicks_60_days      | int(11)       | YES  |     | NULL    |                |
| ctr_60_days         | float         | YES  |     | NULL    |                |
| position_90_days    | int(11)       | YES  |     | NULL    |                |
| impressions_90_days | int(11)       | YES  |     | NULL    |                |
| clicks_90_days      | int(11)       | YES  |     | NULL    |                |
| ctr_90_days         | float         | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+


texts: do oracle redo logs need to be copied when all other data is copied to a new disks?

texts:
I have to migrate all data for an Oracle server from  the present SAN to a new SAN.  It is an 11g database in production on a Linux server.  Database software is on local disk but all data (including datafiles, archive logs, control files, etc) are on a SAN via NFS.

The plan is to shut down Oracle before the sysadmin copies over the data to the new SAN.  Since we are using NFS, when I start up Oracle again, the change should be transparent to Oracle.

The sysadmin has been told that the redo logs do not need to be copied.  It seems to me that the redo logs should be archived before the copy.  Should I force a log switch (alter system switch logfile)before the shutdown and then copy the archive logs?  Or will a shutdown trigger the redo logs to be archived?


texts: Is there any better approach in designing this database?

texts:
CUSTOMER:

CustomerID (PK)
Name
Email

PAYMENT:

PaymentID (PK)
Amount
Date

PRODUCT:

ProductID (PK)
Type
Name
Price

PURCHASE:

CustomerID (FK)
PaymentID (FK)
ProductID (FK)
Quantity

Or should I move CustomerID in PURCHASE to PAYMENT or else?


texts: Sync MySQL 4.0 table to MySQL 5.6 server every few minutes

texts:
I am tasked with getting a copy of a table on a MySQL 4.0 server synced to a completely different database on a MySQL 5.6 server every few minutes for the purposes of having that table available during a migration phase for a really old application.  Basically this:

10.10.10.10:oldb.table -> 20.20.20.20:newdb.table

The table is ~65,000 rows and ~18MB.

Obviously I cannot use replication with version 4.0.  I have looked at using mysqlhotcopy, but it appears that it only copies entire databases, and I need the table to live in a different database on the destination host.  I have also looked at using mysqldump, however I cannot have the table on the new server be dropped for the new data to be inserted resulting in queries that see an empty table, and if I use --insert-ignore --no-create-db --no-create-info it still does not account for deleted rows on the source table.  I also looked at selecting all rows from the existing table and doing a REPLACE INTO, or INSERT/UPDATE ON DUPLICATE KEY but this would also not account for rows that were deleted from the source table.  Any thoughts on how to accomplish this?


texts: How to monitor for MySQL select queries that return too many records?

texts: I realize there are simple ways to limit results in SQL. I have a legacy implementation. I would like to get a handle on select queries that return results exceeding some user defined limit. The goal would be to have better insights to the kind of queries sent to the database. Some of these queries can translate into performance issues in the code that processes the results and/or excessive heap consumption.  To gain insights, I'd like to monitor a production databases with a log entry for any query exceeding a threshold. Configuring an alarm/alert would even be better. The solution needs to be one that can be deployed to production. I'm not looking for a QA or Performance tool solution.


texts: Firebird2.5-superclassic on Ubuntu 16.04 LTS

texts:
I installed firebird2.5-superclassic using sudo apt-get install firebird2.5-superclassic and then ran sudo dpkg-reconfigure firebird2.5-superclassic and set the password per https://firebirdsql.org/manual/ubusetup.html. Even after doing this, I cannot gsec SYSDBA. I try running gsec -user SYSDBA -password ********* and i receive  Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
unable to open database Is there a way to ensure this gets created/fix this?  I cannot seem to find a way.


texts: to what "scale" of applications does nosql support?

texts:
Recently i came across the Nosql database concept, though i learnt how to and why of it, i did not get a proper answer to the question , to what scale of project does it support?

Will it support larger application which generally has lot of statistical usage, like google analytics (example)?
Are their solid data or application that are already running on such database like couchbase etc?


texts: Last character in column shift to next column

texts:
I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the last character from the column and append it to the next column over. I am at a loss for how to accomplish this.

I would adjust my ssis project and reload the data, however some of the data files were deleted and the infrastructure guys were not snapshotting that folder so I would be missing approx 10 days work of data in the warehouse.

edit

    create table dda
(
    comp_acct_num varchar(17)
    ,internet_code char(3)
    ,type_code char(1)
    ,cr_rate_code char(1)
)

INSERT INTO dda (comp_acct_num,internet_code,type_code,cr_rate_code)
VALUES ('00000000000000000','111','7',''),('00000365874001000','111','7',''),
       ('00000000000001000','211','7',''),('00003101118001000','211','7','')

The values inserted are the values as they exist now. They need to read like this:

comp_acct_num    |internet_code|type_code|cr_rate_code|
-------------------------------------------------------------
00000000000000000|  111        |    7    |            |
00000365874001000|  111        |    7    |            |
00000000000001000|  211        |    7    |            |
00003101118001000|  211        |    7    |            |

It should read like this:

comp_acct_num    |internet_code|type_code|cr_rate_code|
-------------------------------------------------------------
0000000000000000 |  011        |    1    |  7         |
0000036587400100 |  011        |    1    |  7         |
0000000000000100 |  021        |    1    |  7         |
0000310111800100 |  021        |    1    |  7         |


texts:
I read a couple of posts online regarding this issue but seems like no one really knows the answer.

When running a query I see
1 query 0.5 sec + 30 secs network

This is ridiculous since the client and server are both on the same machine and my select is SELECT 1 FROM ... LIMIT 100000 , no there isnt alot of data sent from the server to the client;

So what the hell is this secs network.


texts: How significant is performance difference between "DATE(mydate) = CURDATE()" vs "mydate = CURDATE()"?

texts:
DATE(mydate) = CURDATE()

vs

mydate = CURDATE()

I have a pretty performance sensitive situation with tens of millions of rows, and I'm wondering if storing only date in datetime field and time in a separate columns, just to have a simple mydate = CURDATE() comparison when looking up records by days, would be significantly faster than merging both information in datetime field and using DATE(mydate) = CURDATE().

(yes, I cannot use "date" data type, because of the abstraction layer between me and the database)

Would there be a significant difference or is there a better way to get all records from the given day when using datetime field?


texts: Have a separate database for data warehousing?

texts:
I have built a typical web application using PHP and MySQL for enterprise usage.

The MySQL database is 3NF as much as possible.

However, as time goes by, the requests for the data appear to be more for a data warehouse so that they can slice and dice the data in whatever way they want.

Hence I picked up a book the 3rd Edition Data Warehouse Toolkit of the Kimball Group.

Inside the authors described a Kimball DW/BI Architecture and using star schema for dimensional and fact tables for DW/BI purposes. See below.

My question is, since I am most familiar with PHP and MySQL, should I have the data warehouse star schema in a separate database from the original 3NF database?

No commercial BI tool will be used. Anything that looks like an OLAP or BI would likely be an open source software that I may augment myself.

Please advise.

EDIT

My entire 3NF MySQL database is currently about 20mb so far growing about 1 mb every month or so.


texts:
It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running:

show tables;

I would like to reverse that order - is it possible to do without getting the list of all tables from database; reordering that list, and specifying all tables in cmdline to mysqldump?


texts: How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

texts: I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was reduced but the server's ibdata1 file went crazy. Any ideas?


texts: PgAdmin 4 - How to connect to database via ssh tunnel as posgres when its password is empty?

texts:
I am trying to connect to a Postgres database at my DigitalOcean instance via PgAdmin 4.

I create an SSH tunnel as root, provide the PgAdmin with my identify file. I want to login into database as postgres user which doesn't have a password.

So PgAdmin doesn't let me log in and requires the password for the postgres user.

Does user postgres have to have a password if I want to connect via SSH tunnel? Is it possible? And what should I do in order to connect?

[


texts:
public function getSiteValues($value) {
    $this->db->select(
        'site',
        'id, option_name, option_value',
        NULL,
        'option_name=' . $value
    ); // database, what to take, where value
    $res = $this->db->getResult();

    echo '<pre>';
    print_r($res);
    echo '</pre>';
}

In this case I would like to put for example:

getSiteValues('sitename')

and so that this would return the value of sitename row and from option_value column, but it keep giving me error such as

[0] => Unknown column 'sitename' in 'where clause'

What can I do to fix this?

PS. I have tried to look at other solutions but they have keep giving me wrong solutions. I have tried most of them. btw I am using this crud library - https://github.com/rorystandley/MySQL-CRUD-PHP-OOP for db connections


texts: mysql prevent update other salve-master db while any record update in slave db

texts:
I have master and 3 slave (slave1,salve2,slave3) db in same host. So when I update any record in slave1 db its affected on all other master and salves db.

My Question is: How to prevent update all other db whenever I update any record in salve1 db, and whenever I update any record in master it will be updated in all db?

in short I want to replicate db in one way master to salve not slave to master..


texts:
I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a sequential scan. What is going on?

masa=# \d+ store
                                                          Table "public.store"
  Column   |          Type          |                      Modifiers                      | Storage  | Stats target |    Description
-----------+------------------------+-----------------------------------------------------+----------+--------------+--------------------
 id        | integer                | not null default nextval('store_id_seq1'::regclass) | plain    |              |
 parent_id | integer                |                                                     | plain    |              |
 name      | character varying(255) | not null                                            | extended |              |
 coords    | geography(Point,4326)  |                                                     | main     |              | (DC2Type:geopoint)
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)
    "uniq_ff5758775e237e06" UNIQUE, btree (name)
    "idx_ff575877727aca70" btree (parent_id)
    "idx_ff575877d86de985" gist (coords)
Foreign-key constraints:
    "fk_ff575877727aca70" FOREIGN KEY (parent_id) REFERENCES store(id)
Referenced by:
    TABLE "store_product" CONSTRAINT "fk_ca42254ab092a811" FOREIGN KEY (store_id) REFERENCES store(id) ON DELETE CASCADE
    TABLE "store" CONSTRAINT "fk_ff575877727aca70" FOREIGN KEY (parent_id) REFERENCES store(id)

masa=# explain SELECT * FROM store WHERE coords && ST_MakeEnvelope(24.93522187393,60.155284015594,24.95327552607,60.164258984406, 4326);
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on store  (cost=0.00..27.86 rows=206 width=58)
   Filter: (coords && '0103000020E610000001000000050000007F0863B36AEF3840AD48BC58E0134E407F0863B36AEF3840890C3B7006154E40408C68DD09F43840890C3B7006154E40408C68DD09F43840AD48BC58E0134E407F0863B36AEF3840AD48BC58E0134E40'::geography)
(2 rows)

masa=#

Apparently the reason was that table needed vacuum/analyze

masa=# VACUUM FULL ANALYZE store;
VACUUM
masa=# explain SELECT * FROM store WHERE coords && ST_MakeEnvelope(24.93522187393,60.155284015594,24.95327552607,60.164258984406, 4326);
                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_ff575877d86de985 on store  (cost=0.14..8.16 rows=1 width=82)
   Index Cond: (coords && '0103000020E610000001000000050000007F0863B36AEF3840AD48BC58E0134E407F0863B36AEF3840890C3B7006154E40408C68DD09F43840890C3B7006154E40408C68DD09F43840AD48BC58E0134E407F0863B36AEF3840AD48BC58E0134E40'::geography)
(2 rows)

masa=#


texts: How to store chess positions in a relational database (in particular, Postgres)

texts:
I'm exploring the idea of building a website that involves analysis of chess positions. Chess positions themselves are described using a format called FEN, e.g. the starting position for a chess board can be described as:

rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1

The part I'm particularly interested in ideas around how to model is the everything up to the first space. This describes the layout of each rank of the chessboard from the 1st rank to the 8th. Pieces are described using a combination of letters and upper/lower case to signify the piece kind and if it belongs to the black player (lowercase) or white (uppercase). The integer 8 indicates the number of empty spaces. After a move like 1. e4

rnbqkbnr/pppppppp/8/8/4P3/8/PPPP1PPP/RNBQKBNR b KQkq e3 0 1

You can see now how the 5th rank portion describes 4 spaces, a white pawn then 3 spaces.

So how can we go about modelling this in a relational database like Postgres? My naive approach is to start with storing the entire FEN string as a varchar, however, in the future, I'd like to migrate this to a structure that would enable me to easily search for structures within a chess position. An example could be, find me all the positions in the database where white pawns occupy the e4 and e5 positions (the rank segment for the 5th position: 3PP3).

A regex search of the FEN string could find the positions I'm interested in but would it be enough to simply index the FEN column? Should I think about storing each rank of the board as an individual column? Or could the entire board be represented as a matrix somehow.


texts: 4m Rows in MySQL, ~5 Minutes to Run Query

texts:
I have 2 tables, a users table and a log table which literally logs each time a user signs in.

What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a year. That said, I have a working query, but it's taking ~5 minutes to execute.

Here's what I'm currently working with:
SQLFiddle

SELECT u.usrid, u.username, u.rank, u.extras, l.dtime
FROM users AS u
JOIN (
    SELECT userid, MAX(datetime) dtime
    FROM log
    GROUP BY userid
) AS l
ON u.usrid = l.userid
WHERE (u.rank = 'P' OR u.extras LIKE '%W%')
AND l.dtime < DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY l.dtime DESC

It's a part of a very old and soon to be updated system... But until then, I'm trying to make the best of what I've been given to work with. That being said, I don't have access to actually alter the database schema so I'm hoping there is a way to optimize this query to work better.

Furthermore, the users table has ~500k rows while the log table has ~4m rows. With that many rows, I'm also wondering if it's a size issue. If so, what suggestions could I pass on to the DBA in order to improve it's ability to scale? I know I could simply store the last log in date somewhere separate for this particular use-case; but we use that log data for a lot of things so we need a way to be able to search it efficiently.


texts: Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

texts:
I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it:

Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a subset of the rows that were in the initial table (an example that I have seen a lot if splitting a Users table by Continent, like a sub table for North America, another one for Europe, etc...). Each partition being in a different physical location (understand 'machine'). As I understood it, Horizontal Partitioning and Sharding are the exact same thing(?).

Vertical Partitioning: From what I understood (http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx ), there are 2 sorts of Vertical Partitioning:

Normalization (which consists of removing redundancies from a the database by splitting tables and linking them with a foreign key).
Row Splitting, here is what I don't understand, what is the difference between Normalization and Row Splitting? In what those 2 techniques differ from each other?

I have also read in this post (https://stackoverflow.com/questions/11707879/difference-between-scaling-horizontally-and-vertically-for-databases ) that the difference between Horizontal Partitioning and Vertical Partitioning is that in the first you scale by adding more machines, while in the second one you scale by adding more power (CPU, RAM) to your existing machine, is that a correct definition? I thought that the core difference between those 2 techniques resides in the way you split your tables.
This answer does make sense according to MongoDB's definition of Vertical Partitioning:
http://docs.mongodb.org/manual/core/sharding-introduction/

but it goes in contradiction with other answers or articles I have come across:

https://stackoverflow.com/questions/18302773/what-are-horizontal-and-vertical-partitions-in-database-and-what-is-the-differen
http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx
http://building.wanelo.com/post/42361472646/the-case-for-vertical-sharding

I am sorry for the load of questions but I am a bit confused as a lot of different websites that I have came across say different things.

Any help clarifying would be greatly appreciated. Any link to a clear and simple demonstration with a few tables would also be very helpful.


texts: Comparing the values of two columns with different datatypes

texts:
I have two columns in a table which need to be compared against the data.The problem that I am facing is that each of the columns have different datatypes: float and datetime2.

EventID EventStartDate
------- ---------------------------
201605  2017-09-21 12:40:29.9800000
201504  2017-09-21 12:40:46.8430000
201606  2017-09-21 12:41:39.3370000
201609  2017-09-21 12:41:57.3100000

Now, I have to compare the values in the column named EventID with the values in the column named EventStartDate; e.g., I need to compare the year and month in '201605' with the year and month parts of '2017-09-21 12:40:29.9800000'.


texts:
We have a PXC environment with three nodes, and each node has 8GB of total RAM, with half of that (4G) dedicated to SWAP, and 3GB configured for Innodb-buffer_pool_size. Our developers have reported random and frequent number of connection timeout issues.

Memory Usage:
[root@mysql ~]# free -g
              total        used        free      shared  buff/cache   available
Mem:              7           1           1           0           4           5
Swap:             4

Top Info

Tasks: 227 total,   2 running, 225 sleeping,   0 stopped,   0 zombie
%Cpu(s): 14.4 us,  3.7 sy,  0.0 ni, 81.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  8009172 total,  1618164 free,  1425932 used,  4965076 buff/cache
KiB Swap:  5242876 total,  5242876 free,        0 used.  5818876 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
22021 mysql     20   0 8772380 887760  37600 S 100.0 11.1 366:09.21 mysqld
 2588 root      20   0  172268   2332   1580 R  20.0  0.0   0:00.10 top
    9 root      20   0       0      0      0 R   5.0  0.0   7:14.88 rcu_sched
  830 root      20   0  178240  36276  21988 S   5.0  0.5 198:34.47 consul
    1 root      20   0  191540   4492   2608 S   0.0  0.1  11:43.66 systemd

I have a server that was given to me with only 8GB of total RAM< with 4G dedicated to SWAP as shown above (did not setup the system, but was given as is). I have configured the innodb_buffer_pool_size of MySQL to 3GB in addition.

I am running a PXC environment, and all nodes have the same info as noted above. Developers have reported a lot of MySQL timeout connection issues, and I am not sure if this is a result of lack of memory, especially that SWAP has about half total memory dedicated to it. is it possible this might be the possible cause of the issue? Below is the MySQL error log:

2018-10-29T07:46:05.831598-00:00 8 [Note] WSREP: wsrep: committed write set (24860)
2018-10-29T07:46:06.871633-00:00 89242 [Note] WSREP: set_query_id(), assigned new next trx id: 332713
2018-10-29T07:46:07.019286-00:00 89228 [Note] Aborted connection 89228 to db: 'slashire_uat' user: 'db-awesome' host: '10.0.1.110' (Got timeout reading communication packets)
2018-10-29T07:46:10.161805-00:00 89243 [Note] WSREP: set_query_id(), assigned new next trx id: 332715
2018-10-29T07:46:15.162479-00:00 89244 [Note] WSREP: set_query_id(), assigned new next trx id: 332717
2018-10-29T07:46:16.147640-00:00 89226 [Note] Aborted connection 89226 to db: 'slashire_uat' user: 'db-awesome' host: '10.0.1.110' (Got timeout reading communication packets)
2018-10-29T07:46:18.148842-00:00 89227 [Note] Aborted connection 89227 to db: 'slashire_uat' user: 'db-awesome' host: '10.0.1.110' (Got timeout reading communication packets)
2018-10-29T07:46:20.161973-00:00 89245 [Note] WSREP: set_query_id(), assigned new next trx id: 332719


texts:
Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of these tables show highlighted (red) 'Overhead' in phpMyAdmin.

Given these conditions, should the tables be OPTIMIZED just after the purge. If not a good idea, why? (The purge occurs at lowest usage time of day.)

Let's say the tables and current (mid-month) Data & Index sizes are:

          Rows     Deleted     Data      Index

table1   17,000     7000      4.3 MiB    1.2 MiB    Holds +300/day Transaction Info
table2    6,000     1000     25.5 MiB    231 KiB    Holds User Schedule Info
table3    1,800       30      297 KiB     43 KiB    Holds User Info

This question is in response to learning about indexes and efforts to reduce slow queries and learning about high counts for:

Handler read rnd next
Handler read prev
Created tmp disk tables
Opened tables

In reading articles about these items, it seems to be a 'learned' science and requires testing.

Thanks for responding.


texts: Change language used to parse decimal number

texts:
When my application - which I cannot change - tries to execute the following ad hoc statement:

Insert into Payment(idProduct,PaymentTypeId,total,paymentdate,cost)
  VALUES ('48838','2','550',GETDATE(), '348,4')

I get this error:

  Error converting data type varchar to numeric.

This is because the cost column is a decimal, and the application is sending commas instead of decimals.

What configuration do I need to change? I tried changing default language to Spanish (advanced properties for the server) and the login to Spanish too, but it is not working. The language property in the server is English and I can't figure out how to change it.


texts: LogFile Size increases by SSIS Package execution

texts:
On production server we have 125 GB free space. The SSIS package is doing some delete in Transaction. e.g. Insert 3 columns which are mainly business keys into a staging table and then deleting the data from facts while joining with staging table so it is an Execute Sql task with four delete queries.

On server there is 125 GB free space. Once package execution has started and execution comes at point where delete happens, the disk space gets full due to log file increased from 84 MB to 125 GB and then the package fails with message

  login failed for user as well as not able to undo that delete operation.

Question

Is the huge increase in log file is due to transaction we implemented or the Delete statement in SQL Server itself logs a huge amount of data ?
How to solve this problem? what changes can i make in SSIS to improve this behavior ?

Below are the details of size of two tables out of four using sp_spaceused

rows
    7361248
reserved
    28744520 KB
data
    9072848 KB
index_size
    19444600 KB
unused
    227072 KB
------------
rows
    932549
reserved
    1038752 KB
data
    979192 KB
index_size
    45920 KB
unused
    13640 KB

Query:

Delete fpi from DWH.Factptimg fpi
inner join DWH.Dimpt dp on fpi.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer

GO

Delete fph from DWH.Facttxhstry fph
inner join DWH.Dimpt dp on fph.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer

GO

Delete fph from DWH.DimTxtran fph
Inner join DWH.Dimcourse dc on dc.DimcourseID = fph.DimcourseID
inner join DWH.Dimpt dp on dc.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer

GO

Delete df from DWH.DimFld df
Inner join DWH.Dimcourse dc on dc.DimcourseID = df.DimcourseID
inner join DWH.Dimpt dp on dc.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer


texts: Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]

texts:
Frequently in Javascript you'll have something like

[ 7,2, [6,7], 2,10 ]

How would you query that structure with OPENJSON I would like this,

0, 7
1, 2
2, 6,
2, 7,
3, 2
4, 10

I'm having a hard time conditionally unwrapping that JSON array.

Sample Data

declare @ex nvarchar(max) = '[ 7,2, [6,7], 2,10 ]';

My attempt

Find my query here

SELECT *
FROM OPENJSON(@ex, '$') AS j1

This gets you to,

key value   type
0   7       2
1   2       2
2   [6,7]   4
3   2       2
4   10      2

If I try to CROSS APPLY OPENJSON(j1."value", '$') AS j2;, I get an error about the 7, the first non-array being an invalid array,

  Msg 13609 Level 16 State 4 Line 4

  JSON text is not properly formatted. Unexpected character '7' is found at position 0.

How do I use CROSS APPLY OPENJSON to conditionally unwrap the rows that are arrays (type=4) while leaving alone non-arrays (in the above like type=2)? I don't want that [6,7] in there. I want two rows with key=2 that have values 6, and 7 respectively.


texts:
While uploading (bulk insert via BCP) a large amounts of data to Azure SQL Database I observe a long HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE waits.

select session_id,
    start_time,
    s1.text as sql_text,
    wait_type,
    last_wait_type,
    open_transaction_count,
    total_elapsed_time,
    reads,
    writes,
    logical_reads,
    row_count,
    database_id
from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle) s1

Does anybody have an idea what it can be related to? I can't find any information about this wait type on the Internet.

Thanks a lot in advance.


texts: Reclaiming space from data files in Oracle

texts: Is it good to reclaim space using resizing datafiles? What are the things to be taken care before resizing the datafile?


texts: Does modifying an access file copy affect original file?

texts:
This might me a stupid question, but it is crucial for me to know, so please bear with my limited knowledge of Access 2002 application.

Let's say I have an original database file, let's call it original.mdb. And I have Ctrl-C + Ctrl-V'd this file to the same location, with the same settings and everything, let's call it original_back.mdb.

Now, I want to understand the structure of files in Access 2002. Since both files will be pointing to the same database, will modifying a backup file affect the original? I might need to modify database for testing purposes, but I don't want to mess with original DB.


texts: Why does my SELECT DISTINCT TOP N query scan the entire table?

texts:
I've run into a few SELECT DISTINCT TOP N queries which appear to be poorly optimized by the SQL Server query optimizer. Let's start by considering a trivial example: a million row table with two alternating values. I'll use the GetNums function to generate the data:

DROP TABLE IF EXISTS X_2_DISTINCT_VALUES;

CREATE TABLE X_2_DISTINCT_VALUES (PK INT IDENTITY (1, 1), VAL INT NOT NULL);

INSERT INTO X_2_DISTINCT_VALUES WITH (TABLOCK) (VAL)
SELECT N % 2
FROM dbo.GetNums(1000000);

UPDATE STATISTICS X_2_DISTINCT_VALUES WITH FULLSCAN;

For the following query:

SELECT DISTINCT TOP 2 VAL
FROM X_2_DISTINCT_VALUES
OPTION (MAXDOP 1);

SQL Server can find two distinct values just by scanning the first data page of the table but it scans all of the data instead. Why doesn't SQL Server just scan until it finds the requested number of distinct values?

For this question please use the following test data that contains 10 million rows with 10 distinct values generated in blocks:

DROP TABLE IF EXISTS X_10_DISTINCT_HEAP;

CREATE TABLE X_10_DISTINCT_HEAP (VAL VARCHAR(10) NOT NULL);

INSERT INTO X_10_DISTINCT_HEAP WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_10_DISTINCT_HEAP WITH FULLSCAN;

Answers for a table with a clustered index are also acceptable:

DROP TABLE IF EXISTS X_10_DISTINCT_CI;

CREATE TABLE X_10_DISTINCT_CI (PK INT IDENTITY (1, 1), VAL VARCHAR(10) NOT NULL, PRIMARY KEY (PK));

INSERT INTO X_10_DISTINCT_CI WITH (TABLOCK) (VAL)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_10_DISTINCT_CI WITH FULLSCAN;

The following query scans all 10 million rows from the table. How can I get something that doesn't scan the entire table? I'm using SQL Server 2016 SP1.

SELECT DISTINCT TOP 10 VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);


texts: MySQL Workbench Administrator - GUI "Access denied for user root " gotcha

texts:
Here is a small newbie problem I found and resolved. The behavior of the GUI is a little confusing so I figure I will share.

Running some Java examples, I have MySQL set up on Windows. After making a little progress I ran into a problem with a sample database JDBC connection. I wanted to look at that database connection's admin user info in the Workbench to see what's going on.

So I click  "Users and Privileges" - the gui pops up a message:
"Access denied for user 'root'@'localhost' (using password: YES)"  - what? I know the root password, set it myself. There was no opportunity to enter it in the GUI just now, only a rude popup.

So I went hunting for some tip on how to update a config file for the GUI to read the root password.


texts: Purchased in Previous 12 Months from Date of Purchase - SQL

texts:
I'm new, and not even sure how to ask this question, but here goes:

For each customer/product purchase, I need to determine with SQL whether the same customer purchased that product in the 12 months prior to this purchase. So, if purchase is on 9/1/2018, I need to know if the customer purchased the same product in previous 12 months.

I've tried combinations of RANK() OVER(PARTITION BY but haven't really accomplished what I need to.

Edit: adding attempted code below:

select
   s.CUST_ACCT_ID
  ,s.PRODUCT
  ,s.BUS_MO
  ,s2.BUS_MO

from INS_REF001_SALES_SUM_R48 s

left outer join INS_REF001_SALES_SUM_R48 s2
on s.CUST_ACCT_ID = s2.CUST_ACCT_ID
and s.PRODUCT = s2.PRODUCT
and s.BUS_MO < s2.BUS_MO
and s.BUS_MO >= dateadd(mm, datediff(mm, 0, s2.BUS_MO) - 12, 0)

inner join INS_REF006_ACCT a
on a.CUST_ACCT_ID = s.CUST_ACCT_ID
and a.CUST_ACCT_ID = s2.CUST_ACCT_ID

where s.BUS_MO between '11/01/2016' and '10/31/2018'

Thanks!
Scott


texts: My workstation is Ubuntu 14.04. I have Oracle Express 11g installed. Whenever i run the define command in SQLPlus, is see the define _editor = "ed". Is there a way to change that permanently to gedit instead of defining my preferred editor with every new session.


texts: How to connect UDL file to a Msaccess DB 2007 (Protected with password)?

texts:
The UDL file was connected to MS Access DB 2007 successfully. However, when I have protected the DB with a user the connection failed.
Can any one show us how to solve this problem ?


texts: Backup of database X on SQL Server 2008 on server A - restore to SQL Server 2012 database Y on server B

texts:
I want to copy a SQL Server 2008 database to SQL Server 2012 with a different database name.

Took a backup of 'production' and copied it to the SQL Server 2012 machine.
Created a new SQL Server 2012 database with a 'test' name.
Tried to restore the .bak file but SQL Server says 'test' is busy

What is the correct way top do this?

Basically getting a snapshot of a production 2008 database into a test 2012 environment.

Thanks

Bob


texts:
I have reason to believe the transaction log file on my database is corrupt.

This database is involved in transaction based replication (both subscription and publishing), and I'm unable to start its log reader agent with an error message which cites "Operating System Error 23".

I essentially just want to do what it takes to be able to restart the log reader agent, and my best idea so far is to somehow clear the log file, detach from it and reattach to a new log file. I'm not sure what the data loss implications of this would be though. Is this the best solution or is there a better way to deal with a corrupt log file? It is not a production database so avoiding data loss is not absolutely necessary (though preferable). There are no backups.


texts: Select last non-null value for given partition - Postgres 10

texts:
I have a table with the following structure:

date                city cntry  free  occ
2018-08-13 16:30:00 2    12           5
2018-08-13 16:30:00 2    12     0
2018-08-13 14:30:00 2    12     1
2018-08-13 11:00:00 2    12     2
2018-08-12 13:00:00 2    12     1
2018-08-12 13:00:00 2    12           4
2018-08-12 08:00:00 2    12           3
2018-08-12 08:00:00 2    12     2
2018-08-10 15:30:00 2    12           4

date is timestamp without timezone
the rest are numbers

I want to get the previous non-null value for a given city_id/country_id combo, for both the free and occupied:

date                city cntry  free  occ
2018-08-13 16:30:00 2    12     0     5
2018-08-13 14:30:00 2    12     1     4
2018-08-13 11:00:00 2    12     2     4
2018-08-12 13:00:00 2    12     1     4
2018-08-12 08:00:00 2    12     2     3
2018-08-10 15:30:00 2    12     1     4

partition is by city_id, country_id, order by date
for the first date of each partition, if there is a NULL values, it becomes zero
for an existing partition, it uses the previous value
I'd like the ability to filter by city_id and country_id AFTERWARDS

Basically I'm merging two timelines and I want to keep the previous values (free/occupied) for each partition of <city_id, country_id>.

I tried playing with window functions to no avail. I can get my data for either free values or occupied values, but not both.

How can I accomplish this?


texts:
Hi guys i have a big problem. I know i did such stupid and unforgivable mistake.
And know please help me if you can.
I wanted to make copy of database. I should make it by backup and then restore as another database but faster option was to stop mssql server service, copy files and then attach as new database.
The problem is that few minutes eariel i have created new mdf file on primary filegroup in my database i wantedo to copy. That was empty mdf file, just new one. Then i stoped mssql service and deleted that new empty mdf file from hard drive manually. I dont know why, i thought that: "this was just for tests we dont need anymore". When i copied files, i started mssql service and my database is in "recovery Pending" mode and cant find that empty mdf file. Even if i create the same file from another database ofcourde mssql server knows that this is not proper file. I cant make alter table remove file beacuse of error:

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Is there any possibilities to recover that database? I have backup but i will lost data from one day.

Please help if you can


texts:
I need to be able to pass a list of undefined size that will contain numbers like so ('1900'),('1920'),... to and OLE DB source in SSIS.

as of right now I am trying a lot of different things that aren't working.

declare @fieldLogID nvarchar(256) = ?
declare @accountTypes int = ?

select
      fl.[Version]
      ,fl.ObjectID
      ,[FieldLogID]
      ,Cast(fl.WorkStartDateTime as date) as WorkStartDate
      ,Cast(fl.WorkStartDateTime as time) as WorkStartTime...

...
where fleja.FieldLog_FieldLogID IN (@fieldLogID)

The @fieldLogID will contain all the numbers that are passed in. The numbers format does not matter so much, they can be strings, ints, etc. Just as long as we are passing a list.

Any tips on how this is done?


texts: Huge index for covered queries (indexOnly: true) or (indexOnly: false) with collection scan

texts:
I have two questions, I'll give you the facts, then my questions.

The actual index I'm using for one of my queries has the following characteristics :

Multikey (contains 4 keys).
The average key length is 14 characters.
2 indexes values contains 2 letters (country code and state code).
1 index values contain numerical values (between 0 and 100).
The index size is : ~395 MB.

Here's an example of what should be indexed (I don't know how mongodb actually is storing it's indexes, so I'm going to represent it like if it was a collection document):

{
    "geolocation.statecode": "AL",
    "personnu_field": 50,
    "geolocation.countrycode": "US"
    "field1.sufield": "Awesome value"
}

Informations about the collection concerned by the index :

The collection is a 6M documents.
Fast growing.
It's actually a collection of Twitter users with some additional business related fields.
field1 (see the example given above) is an array of subdocuments.
avgObjSize: 3 KB.
totalIndexSize: ~2.9 GB.
storageSize: ~19 GB.

I've done an explain to this long query :

db.crawler_users.find(
    {
        "geolocation.statecode": "AL",
        personnu_field: { "$lte": 65, "$gte": 30 },
        "geolocation.countrycode": "US"
    },
    {
        personnu_field:1, _id:0
    }
).hint(
    {
        "geolocation.countrycode" : 1,
        "personnu_field" : -1,
        "geolocation.statecode" : 1,
        "field1.sufield" : 1
    }
).explain()

And here's the result:

{
    "cursor" : "BtreeCursor geolocation.countrycode_1_personnu_field_-1_geolocation.statecode_1_field1.sufield_1",
    "isMultiKey" : true,
    "n" : 216,
    "nscannedObjects" : 788609,
    "nscanned" : 788609,
    "nscannedObjectsAllPlans" : 788609,
    "nscannedAllPlans" : 788609,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 128,
    "nChunkSkips" : 0,
    "millis" : 127451,
    "indexBounds" : {
        "geolocation.countrycode" : [
            [
                "US",
                "US"
            ]
        ],
        "personnu_field" : [
            [
                65,
                -1.7976931348623157e+308
            ]
        ],
        "geolocation.statecode" : [
            [
                    {
                    "$minElement" : 1
                },
                    {
                    "$maxElement" : 1
                }
            ]
        ],
        "search.keyword" : [
            [
                    {
                    "$minElement" : 1
                },
                    {
                    "$maxElement" : 1
                }
            ]
        ]
    },
}

As you can see the query takes much time to be executed (>2 minutes). And it's hitting the collection. even if the chosen fields already exists on the index.

I have two questions somehow related :

Why is indexOnly: false. Isn't it supposed to be a covered index query? (see the explain later)
I need to retrieve some additional fields from the collection (the id and the profile_picture url). Should I add them to the index to avoid hitting the collection, even if I'll never have to query them?


texts: PostgreSQL Database Data File Integrity Check

texts:
When running a PostgreSQL database system how do I know my database as a whole has 100% integrity?  Basically how do I know if my data files and pages are all 100% good with no corruption?

In the Microsoft SQL Server world there's a command that you can execute DBCC CHECKDB that will tell you if there're issues.  Here's a link if your intrested in learning more on the command. DBCC CHECKDB (Transact-SQL)

I'm a paranoid database integrity minded person (which anyone who works with the database in a DBA type role should be) and this type of stuff makes it hard for me to sleep well at night.  A utility like this is a must!  Searches on google have found a few attempts at tools like this and in my opinion unless it's an official accepted tool by the PostgreSQL project I won't trust it for something this important.

Here are some links to people asking similar questions with what I consider no real definitive answer. And in my opinion shows that PostgreSQL needs to have some tools in place that Oracle and Microsoft SQL Server seem to have.

The first link is the most interesting I have found on this subject.  I think a comment on the article that probably sums it up states: "Postgres is pretty lame when it comes to identifying database corruption and repairing it. The only way to detect it is by dumping the database or select * from every table in the database."

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption - Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

I believe there is a chance 9.3 might have some corruption checking features. It appears there may be hope to having page files check summed if one chooses.  So things are looking bright if you consider using ZFS and/or a future version of Postgres with page check summing.
https://commitfest.postgresql.org/action/patch_view?id=759

UPDATE: 14-JAN-2012 - Seems like using a file system based on ZFS can detect corruption by check summing each block of data.  I will have to look into this further and see if this is a work around to allow one to sleep well at night knowing their database data is not silently going corrupt.

UPDATE: 17-JAN-2012 - How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3


texts: How to secure my data queried from my local MySQL server on a remote machine with root shared with untrusted entity?

texts:
I have a remote client machine put in a server center owned by an entity who must also have the root of the client machine according to industry regulations. The client machine runs a C++ program that uses libmysqlclient.so and periodically queries a MySQL server located in my office via the internet.

The data is highly sensitive so I need to protect the data from being disclosed in the whole process of data transfer. Nobody is trusted in the server center including its owner who has the root of the client machine.

At first, the json format database config of the C++ program contains explicit IP, port, user, and password. Anyone who has root can read those and use them to connect to our server from the client machine. Since IP and port must be disclosed to the owner of the server center who manages the intranet, gateway, and firewall according to regulations, I replace user and password with encrypted strings in the config and the C++ program will decrypt them at startup to obtain the real user and password to connect to the server.

The root user, however, may replace libmysqlclient.so in the system with a modified version so that the user and password are printed out when a connection is being established. Then I change the C++ program to static linking to libmysqlclient.a at compile time.

However, the data being transferred is still clear-text. So I enable SSL on the MySQL server and alter the user as REQUIRE X509. Then man-in-the-middle attack is supposed to be not easy.

To make the two-way TLS work, I also need to put ca, client-cert, and client-key files to the client machine. I'm wondering if the server center owner can capture the encrypted traffic being transferred and use those files to decrypt the data without any knowledge of the database username and password?

Also is there any obvious vulnerability on the client side after having all these setups? Let's assume that the net structure and the use of MySQL are not to be changed.


texts: In phpMyAdmin, how can I execute mysqldump query?

texts:
I wanted to execute

mysqldump -u username -p –all-databases > dump.sql

However, I can't see the appropriate field in PHPMYADMIN. Where I can do that?

SQL error


texts: Assigning blank values on joins_query design

texts:
I am using query design to join data, however, when I use the sql code below, where ever there is no value, the query assigns a value to it (see image below). see the blue section where the AIBL code should only have one residential land use for 500m, yet it duplicates the data to align with the other aibl code (with more land use types).

SELECT *
FROM dbo_AIBLVIC_LUM_PORTION_500 LEFT JOIN dbo_AIBLVIC_LUM_PORTION_1600
         ON dbo_AIBLVIC_LUM_PORTION_500.AIBL = dbo_AIBLVIC_LUM_PORTION_1600.AIBL;


texts:
Last week I started testing dbccheck on my test system. I found one strange thing, and I have no idea what is the problem, last fullbackup is few hours old, but dbccheck print an error.

Following you can see my steps:

installation dba checks

based on the blog from chrissy lemaire (thank you chrissy,for that great job)

change configuration

Set-DbcConfig -Name policy.backup.fullmaxdays   3
Set-DbcConfig -Name policy.backup.logmaxminutes 720

first check

$server = "ALET21"
Invoke-DbcCheck -SqlInstance $server -Check LastBackup

here the result:

result DbcCheck

The dbccheck print an error

I did the same test with PS Command, following you can see the results:

result PS command

any idea what is the problem?

AdminDB DB is not new, creation date was on 23.11.2016. I also updated the policy.backup.newdbgraceperiod to 1, but I have the same problem, as you can see below:

It looks like the dbacheck module is up to date:


texts: SQL Server lock_escalation flag on ALTER TABLE -- can I really restructure without a table lock?

texts:
SQL Server 2014 Standard Edition

In my situation, I have a huge table (100 million+ rows), and need to:

add column to it
set default value in that column

I see in
https://technet.microsoft.com/en-us/library/ms190273(v=sql.110).aspx
that there is a new lock_escalation option.

Does this allow me to, for example, do the following without a table lock:

e.g.

ALTER TABLE Protocols set (LOCK_ESCALATION = DISABLE);
go

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

However, the concerns are:

a) LOCK_ESCALATION = DISABLE -- what exactly is it for, does it apply here? (The SQL Server docs are a bit thin... perhaps it only applies to partitioned tables?)

b) Does LOCK_ESCALATION = DISABLE affect the transaction size? (Appears it will still create one huge transaction?)

c) Will LOCK_ESCALATION = DISABLE even apply and avoid a table lock?

d) Is there a way to chunk it? (Can't imagine, but there is so much new magic in newer SQL versions... I should ask!)


texts: MySQL: InnoDB data dictionary has unknown flags

texts:
I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs:

InnoDB: Warning: table 'sampledb/table1' InnoDB: in InnoDB data dictionary has unknown flags 50.

What could be the possible cause why I'm encountering this error?

Here is the sample error logs generated when I'm trying to start the service I have recreated:

200121 13:32:48 [Note] Plugin 'FEDERATED' is disabled.
200121 13:32:48 InnoDB: The InnoDB memory heap is disabled
200121 13:32:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
200121 13:32:48 InnoDB: Compressed tables use zlib 1.2.3
200121 13:32:48 InnoDB: Initializing buffer pool, size = 2.0G
200121 13:32:49 InnoDB: Completed initialization of buffer pool
200121 13:32:49  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 48 MB
InnoDB: Database physically writes the file full: wait...
200121 13:32:49  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 48 MB
InnoDB: Database physically writes the file full: wait...
200121 13:32:50 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
200121 13:32:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 386, file name mysql_bin.000027
200121 13:32:53  InnoDB: Waiting for the background threads to start
200121 13:32:54 InnoDB: 5.5.60 started; log sequence number 77829315084
200121 13:32:54 [ERROR] Aborting

200121 13:32:54  InnoDB: Warning: table 'sampledb/table1'
InnoDB: in InnoDB data dictionary has unknown flags 50.
200121 13:32:54  InnoDB: Starting shutdown...
200121 13:32:55  InnoDB: Warning: table 'sampledb/table2'
InnoDB: in InnoDB data dictionary has unknown flags 50.
200121 13:32:55  InnoDB: Shutdown completed; log sequence number 77829315084
200121 13:32:55 [Note]

This is how I recreated the MySQL service:

mysqld --install MySQL56 --defaults-file=D:\MySQL\5.6.40\my.ini


texts: Should you update child states based on the parent?

texts:
I have the following table structure

Event --* Session --* Membership

An event can be cancelled - reflected via a status column
A Session can be cancelled - reflected via a status column
A user can attend or leave an event via the membership row

My design questions

If an event is cancelled, should all of the session rows be marked as cancelled as well?

Likewise, if a session is cancelled should the membership row also be marked as cancelled?

What are the pros and cons to these methods?


texts:
I have 4 servers running MySQL 5.7 in total , servers 1,2,3 each has single database on them For example:

Server 1 has the DB x_1
Server 2 has the DB x_2
Server 3 has the DB x_3

Server 4 has 3 databases in it:

x_1
x_2
x_3

What i want to achieve is master to master replication between the 3 servers and server 4 each to his own database:

Changes in x_1 in server 4 or server 1 will replicate both ways
Changes in x_2 in server 4 or server 2 will replicate both ways
.
.
.

Appreciate any guidance to the correct way to achieve this (Note: I already know about multi source replication single slave and multiple masters but i am not sure if it can be used here)


texts: Can you use CDC on a subscriber for transactional replicaiton

texts: Is there a way with SQL Server to have a limited number of tables replicated to another server using transactional replication and then use CDC or Change Tracking ONLY in the server replicated to?


texts: Postgresql - Multiple counts within a join

texts:
I am modeling tokens extracted from tweets and have the below table structure:

CREATE TABLE process (
        id SERIAL PRIMARY KEY,
        ...
);

CREATE TABLE tweet (
        id SERIAL PRIMARY KEY,
        process_id INTEGER NOT NULL REFERENCES process(id) ON DELETE CASCADE ON UPDATE CASCADE,
        cleaned_text TEXT,
        process_tweet_id INTEGER NOT NULL,
        constraint tbl_statusid_processid unique(status_id, process_id)
        ...
);

CREATE TABLE token (
    id SERIAL PRIMARY KEY,
    tweet_id INTEGER NOT NULL REFERENCES tweet(id) ON DELETE CASCADE ON UPDATE CASCADE,
    type TEXT,
    value TEXT,
    ...
);

The token table has a foreign key for the tweet table and the tweet table has a foreign key for the process table.

The 'type' field in the token table can be 1 of 7 or 8 possible values (e.g. 'LOCATION', 'PERSON', etc.).  The possible types are static and enforced elsewhere within the application, so there is no chance of any diversion from those set types.

I am trying to create a query that will return all of the tweets referencing a given process_id, the total number of tokens referencing each tweet as well as the number of each TYPE of token referencing each tweet (each type having its own column in the result set).

  id   |     status_id      | tokencount | loctoken | persontoken
-------+--------------------+------------+----------+-------------
  6158 | 672636476602122241 |          3 |        2 |           1
 10295 | 674183390430552064 |          4 |        2 |           2

The above result set is basically what I am hoping to get back.  So, tweet ID# 6158 has a total of 3 tokens, 2 of which are location tokens and 1 is a person token.  Tweet ID# 10295 has a total of 4 tokens, 2 locations and 2 persons.

What I have so far is a simple JOIN, but I am unsure how to include the count for each type of token per tweet...

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount
FROM        tweet
INNER JOIN  token on token.tweet_id = tweet.id
WHERE       tweet.process_id = 5
GROUP BY    tweet.id;

I thought I could simply add count(token.type = 'LOCATION') like the below, but that simply repeated the count(token.id) column.

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount, count(token.type = 'LOCATION) AS locToken, count(token.type = 'PERSON') AS personToken
FROM        tweet
INNER JOIN  token on token.tweet_id = tweet.id
WHERE       tweet.process_id = 5
GROUP BY    tweet.id;

Can anyone provide the query that would return what I am looking for?  Hopefully with a good explanation of the solution?

I would also really like to know why count(token.type = 'LOCATION') did not work and simply returned the same number as count(token.id).  I tried it on a whim and really thought I was just going to get an error.


texts: RIGHT() works in a SELECT but not in an UPDATE?

texts:
I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use it in a SELECT..FROM but when I use that same code inside an UPDATE it fails with

  Invalid length parameter passed to the RIGHT function

why is it failing in the UPDATE but not the SELECT...FROM?

USE JobSight;

DROP TABLE #ReleaseTemp;

SELECT  A.MajRelease,
        A.MinRelease,
        A.Build,
        Split.a.value('.', 'VARCHAR(MAX)') AS ReleaseNotes
INTO #ReleaseTemp
FROM
        (
            SELECT  MajRelease,
                    MinRelease,
                    Build,
                    CAST ('<M>' + REPLACE(RelNotes, ';;', '</M><M>') + '</M>' AS XML) AS ReleaseNotes
            FROM JobsDB.dbo.ReleaseData
        ) AS A
    CROSS APPLY ReleaseNotes.nodes ('/M') AS Split(a);

UPDATE #ReleaseTemp
SET ReleaseNotes = RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2);

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(#ReleaseTemp.ReleaseNotes);

SELECT  MajRelease,
        MinRelease,
        Build,
        RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2) AS ReleaseNots
FROM #ReleaseTemp
WHERE #ReleaseTemp.ReleaseNotes <> '';


texts: Is there a Postgres admin GUI that can execute a master file containing multiple sql files

texts:
I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.

example: master.sql

contents (I don't know the syntax to use):

file1.sql
file2.sql
file3.sql

I've found several tutorials about using psql -f in the command-line and \i to include these files, but the preference here is to avoid the terminal and use a GUI to manage my database and execute the master.sql script.

Is this possible in any postgres admin GUI? Presumably it's not in pgAdmin.