texts:
My server (64GB,16CPUs) has mariadb 5.5.60.

The queries are around  500 selects /second and 300 inserts/seconds.

The queries are simple select of auth token and user details.

Both are properly indexed.

The insert query is also very simple.

However, the CPU usage is still high   %cpu usage is >600%.

The following is the mysql config.

Please suggest.

max_connections = 5000
max_user_connections=1000
key_buffer_size = 10024M
myisam_sort_buffer_size = 512M
read_buffer_size = 1G
table_open_cache = 9000
thread_cache_size = 900
wait_timeout = 300
connect_timeout = 300
tmp_table_size = 8G
max_heap_table_size = 4G
max_allowed_packet = 10G
net_buffer_length = 2048M
max_connect_errors = 100
read_rnd_buffer_size = 2048M
bulk_insert_buffer_size = 2G
query_prealloc_size = 512M
query_alloc_block_size = 128M
transaction_alloc_block_size = 128M
transaction_prealloc_size = 128M
max_write_lock_count = 16
log-error
external-locking=FALSE
open_files_limit=50000
character-set-server = utf8
innodb_buffer_pool_size=40G
table_cache=10G
innodb_file_per_table=1
skip-name-resolve


texts: Optimisation and Performance Mysql , Zend Framework 2 and Doctrine 2?

texts:
i'm facing some problem with performance in an application using zend framework2 and doctrine 2. i would like to optimis it but i don't know from where to start , i read some many questions/ answers about performance and optimisation and i got using cache and indexation But my problem is where exactly apply the indexation is on doctrine or on mysql ? the cache where on doctrine or zend cache or the both ?? i can't figure out from where to start espacially that there a high traffic on the application so also the cache might not be the right solution !!!
plz any help or idea would be appreciated.
Regards.
Thanks


texts: How should I design a multi-purpose table?

texts:
Let's suppose I have the following schema:

Table A has a 0 to N relationship with table B
Table B has a 0 to N relationship with table C
Each item on A, B or C may have comments from multiple users (another table?).

A concrete example:

Table Budgets may contain N Revisions
Each Revision of the Budget may contain N items
The users should be able to write a comment on the budget, on the revision, or on the item itself.

My take on this:

Table A

int Id

Table B

int Id
int A_Id  (foreign key)

Table C

int Id
int B_Id  (foreign key)

Table Comments

int Id
int A_Id   allow null
int B_Id   allow null
int C_Id   allow null
varchar Text

I feel this is not the best way to do it, because:

I have no guarantee at DB level that the Comments table will have no more than one foreign key
I have no guarantee at DB level that the Comments table will have at least one foreign key
This is a mess if I scaffold the context classes using a ORM like Entity Framework

FYI, this is just example code that represents the issue I'm facing. In my real project, I have at least 10 tables that will have comments. So, I want to avoid creating a Comment table for each other table (Comments_A, Comments_B and so on).

Is there a better way?


texts: I would like to understand better the difference between "sql server stand alone" and "sql server failover instance"

texts:
AS these question asks:

Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

Shouldn't Windows Server Failover Clustering already take care of SQLServer Failover Clustering?

I have some doubts about clusters.

In my first job i remember we had a failover clustering with 4 nodes. There were 3 SQL servers instances on each node and a empty node for failover. If we had a problem with a node, it would failover to that empty node and not cause performance problems with a node that has already a SQL Instance.

Question:  If I'm not mistaken we had a SHARED DISK failover cluster. So, every database was inside a single disk that was shared with all nodes. But what happened for an instance to change to the empty node? I mean, there were no SQL instalation there, no instance, but after a failover, the instance was there with all its jobs. Does this mean that the virtual machine went to the empty node?

Now I have a new job and I still can't quite understand te concept of clusters. We have a failover cluster with 2 nodes. Here I created an Always on availability groups environment. It works great ( The only problem is we have no quorum yet but we will get there ). The environment here is different. I had to install 2 SQL Server instances on each node. And this is where I would like to reach.

Does this mean that on that first job we had a sql server failover cluster installation? In this new job I just installed the normal stand-alone installation instance on both nodes.

( I had 0 sql knowledge when I started at my first job so I really cant remember what was the configuration ).

So basically, whats the difference between sql server stand-alone installation and a `sql server failover cluster installation.

and One more question. almost weekly we are suffering from failover. I'm sure node1 is losing connection with node2 and then the quorum is lost. The concept with the quorum is, if node1 lose connection with node2, the quorum can say" oh, they are not communitacing with each other, but node1 is still alive so I will not failover them. Is this right?


texts: How to make date intervals from a list of date (Group by columns A and C, the date range consists of a continuous date sequence):


texts: Help required with query (join) optimization

texts:
I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause which converts the date format. It only has 7 tables joined on simple On condition (No crazy stuff).

Going forward I need to add couple of more tables to the join list. This will only make the performance way worse. I need to fine tune the current query before I starting adding to it.

Here is the query:

 SELECT [dbo].[dFK](oew.StartDate) AS StartDate, -- INTEGER DATE!
    [dbo].[dFK](oew.EndDate) AS EndDate,
    [dbo].[dFK](oew.EffectiveDate) AS EffectiveDate
FROM    OpenEnrollmentWindow oew
    INNER JOIN ProductYear py ON oew.OrganizationProductYearID = py.ID
    INNER JOIN Marketplace m ON py.MarketplaceID = m.ID
    INNER JOIN Organization o ON m.OrganizationID = o.ID
    INNER JOIN Consumer c ON c.OrganizationID = o.ID
    LEFT JOIN OpenEnrollmentWindowProduct oewp ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
    LEFT JOIN OpenEnrollmentWindowProductType oewpt ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID

Here is the definition of the function:

CREATE FUNCTION [dbo].[dFK]
(@dt as sql_variant)
RETURNS int
AS
BEGIN
    DECLARE @type varchar(128)
    DECLARE @iDate int
    SET @type = CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
    SET @iDate =
        CASE
            WHEN @type = 'int' AND @dt >= 19000101 AND @dt <= 20451231 THEN CONVERT(int, @dt)
            WHEN @type = 'int' AND @dt < 19000101 OR @type = 'int' AND @dt > 20451231 THEN 1
            WHEN @dt IS NULL THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME) OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME)) AND @type = 'datetime' THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE) OR @dt > CAST('2045-12-31' AS DATE)) AND @type = 'date' THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    RETURN @iDate
END

GO

This is used as a fact table source. The date is converted to avoid a reverse lookup against date dimension. Let's just say it has to be converted at server side only. It is spitting out some 6 million rows. Now I do understand that's quite a lot, and that's why I am seeking some query optimization suggestions here.


texts: 9 digit zip codes are reporting LEN of 12

texts:
I am attempting to resolve some difficulties in getting zip codes to display properly.

The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit zip codes are reporting a length of 12 digits. Now when I try to add a hyphen '-' to the 9 digit zip codes I'm getting abnormal results and errors because of the wrong length and various data type conversion issues.

The import was performed using an openrowset method for importing the data from a spreadsheet.

When I query the newly imported data, I see the zip codes showing the same as they were in the spreadsheet, but the length is wrong.

SELECT ZIP,
    LEN(LTRIM(RTRIM(Zip))) AS ZIPLENGTH
  FROM XLS_IMPORT

ZIP         ZIPLENGTH
45750       5
432013256   12
441153221   12
44120       5
441351362   12

If I select the left 9 characters of the data, everything gets converted to float and the zip codes are now unreadable.

SELECT LEFT(ZIP,9) FROM XLS_IMPORT
WHERE LEN(LTRIM(RTRIM(ZIP))) = 12

ZIP
4.32013e+
4.42034e+
4.56637e+
4.41153e+
4.36045e+
4.41133e+

How can I get these zip codes back to the correct 9 digits? Or how can I add a hyphen to those 9 digit zip codes which are reporting length of 12? My end goal is to simply get the 9 digit zip code to have a hyphen in the middle.

The datatype of the Zip column is float.

I just discovered that some of my spreadsheets like NJ and NY have an apostrophe before the leading 0 in the zip code. I will need to investigate how to handle the '0xxxx zip codes to get this to work on some of my spreadsheet imports.


texts: Scalable DBaaS that can scale to hundreds of terabytes with easy backup and restore

texts:
I've gone over many DBaaS solutions, including Xeround (limited to 50G, in memory), NuoDB (currently in beta 7), DrawnToScale (not out yet), MongoDB (in memory), RavenDB (for ASP.NET, but NoSQL) and many others, DynamoDB (costly for real-time web app and doesn't offer backup/restore).

I find everyone lacking in some ways. I search for a scalable and affordable Database as as service solution using NewSQL (RDMBS), but I will ocnsider NoSQL if there aren't any solutions for my needs.

Amazon DynamoDB seems like a good solution because I can scale to terabytes of data without worrying about disk space. However, DynamoDB costs a lot for high read / write applications and it doesn't offer easy backup/restore option in case of data corruption that is very important to me.

In short what I need:

1) DBaaS (can be self hosted too) the can easily scale to terabytes of data (ie. big data)

2) Capable of very high throughput for real-time application that does many read/writes (although I will use caching too to reduce overload)

3) Being able to query the data using SQL, a RDBMS DB. Although if a NewSQL solution isn't available, I will go with NoSQL

4) Easy backup and restore big amount of data in case of data corruption. I know that many solutions are safe against server failure, but I worry about data corruption and I want to be able to restore the data back if that happens - IMPORTANT!

5) I will be developing my web app / mobile app in ASP.NET, so I prefer a solution that have a .NET connector

6) If hosted, I prefer hosting my DB on Amazon AWs.

7) I don't want a self-managed solution and handle sharding and all the other complicated stuff

8) I want an affordable solution, not like Clustrix that costs thousands of dollars per node. I want a cost-effective solution where I can start small and pay more when my database grows (all idea behind cloud architecture, right?)

Right now the only solution that looks ok is RavenDB, NuoDB (still in beta). Solution like Xeround is amazing, and I am using it right now, but it's in-memory DB and limited for 50GB, so it's not good for a bog data hosting.

It seems that new solutions appear every month and it seems like there isn't any good solution out there (a new startup maybe?). I wanted to know what are my options. As a developer, I really want to focus on building big applications, but I can't start until I have a reliable DB solution that can handle very high traffic and can scale in size without me needing to build all the DB server architecture myself.

I spent weeks trying to find a solution with no luck. A need a solution like Xeround but for big data. Who knows, maybe there is one up there that I missed. Please help me out.


texts: We have oracle db which unfortunately we did'n make backup and all control files and other files except all data-files are damaged. Is there any solution to recover db from all of these intact data-files? Thank you for helping me.


texts: Methodology & Architecture to Create ETL Process

texts:
What book or webpage can I read the architecture and methodology for creating ETL process?

In other words, I'm looking for "how to do it" to create a ETL process when you have many source system to be involved and you also have a data warehouse.


texts:
I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM.

I am unable to index the table and this is causing serious problems.

I thought that partitioning might be the answer to the problem, but I'm not sure because I SELECT on all kinds of different column combinations.

The table structure is:

CREATE TABLE research_storage1b (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `word1` mediumint(8) unsigned NOT NULL,
  `word2` mediumint(8) unsigned NOT NULL,
  `origyear` smallint(5) unsigned NOT NULL,
  `cat` tinyint(3) unsigned NOT NULL,
  `pibn` int(10) unsigned NOT NULL,
  `page` smallint(5) unsigned NOT NULL,
  `pos` smallint(5) unsigned NOT NULL,
  `num` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pibnpage` (`pibn`,`page`),
  KEY `word21pibn` (`word2`,`word1`,`pibn`),
  KEY `word12num` (`word1`,`word2`,`num`),
  KEY `year1` (`origyear`,`word1`),
  KEY `catyear` (`cat`,`origyear`),
  KEY `pibn` (`pibn`,`word1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
DATA DIRECTORY='/storage/researchdb'
INDEX DIRECTORY='/storage/researchdb';

The indexes are representative of the different SELECT statements, i.e. sometimes I'm retrieving pibn based on the word1, and sometimes I'm retrieving word1 based on pibn.

If I had to choose something to split the table on, it would probably be word1 because this is most searched for. But there are some queries that will be selecting word1, or counting word1, in which case would I be correct in assuming the partitioning would drastically slow those queries down? There are around 500,000 unique values for word1.

My main questions are:

Should I use partitioning (will it solve my indexing issue)?
Should I partition on word1?
How many partitions should I have? (I'm thinking maybe 100?)
How do I modify the CREATE TABLE to have 100 (or however many) partitions split on word1 (or another column)?
Since word1 is not a primary key... do I have to change the primary key to an index, or something like that? (I didn't fully understand this part of the partitioning docs/tutorials.)

Please help if you can, thank you.


texts: relationship between transaction chaining and autocommit

texts:
I am trying to understand the relationship between the concepts of transaction chaining and autocommit. Autocommit is usually exposed in a client-side programmatic interface. E.g. in JDBC with the Connection#setAutoCommit method.

Namely I would like to know whether:

these concepts are simply aliases of one another (and what is the correspondence)

or

these concepts are orthogonal and all 4 combinations make sense and yield different results (and what these results are)

There's a number of resources that suggest the case is 1. The same resources seem to suggest that unchained corresponds to autocommit on. E.g.
this post says that:

  set chained off (default in Sybase, = set autocommit on)

This more extensive post similarly says:

  Some database programming interfaces have an autocommit mode, also called unchained mode. In this mode, each statement is a transaction, and is committed after execution. If you wish to use transactions in your applications, you need to be using manual commit mode, or chained mode.

This has been my own understanding so far. For example, whenever I try to execute (using JDBC) a stored procedure and I run into the following message:

  java.sql.SQLException: Stored procedure 'whatever' may be run only in unchained transaction mode.

… then setting in my code:

conn.setAutoCommit(true);

… fixes the problem which seems to confirm that indeed unchained is the same as autocommit.

However looking at the Sybase ASE documentation one reads:

  The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.

This seems to be the exact opposite of autocommit - unless the above text is understood as having the following meaning:

  The default mode, called unchained mode or Transact-SQL mode, requires
  explicit begin transaction statements paired with commit transaction
  or rollback transaction statements to complete the transaction when one wants to place more than a single statement in a transaction.

Finally, I've come across this post which suggests that chaining and autocommit are mostly orthogonal.


texts: Get Multiple Records Within Date Range Between Two Dates


texts: Pivot add-on in cross-tab table Postgresql

texts:
Given the schema here I wonder how I can use Pivot add-on to calculate a cross-tab table in the below format please?

Africa   America  Asia       Australia/Oceania   Europe     Total
#cities  #cities  #cities    #cities             #cities    #cities

The below script returns a normal result but I want it to appear in a cross-tab

SELECT  en.continent,count(ci.name)
FROM encompasses AS en
INNER JOIN city AS ci ON en.country = ci.country
GROUP BY en.continent
Union all
select 'Total', COUNT(ci.name)
from city AS ci;

The create/insert commands can be found here:

CREATE TABLE Country
(Name VARCHAR(35) NOT NULL UNIQUE,
 Code VARCHAR(4) CONSTRAINT CountryKey PRIMARY KEY,
 Capital VARCHAR(35),
 Province VARCHAR(35),
 Area NUMERIC CONSTRAINT CountryArea
   CHECK (Area >= 0),
 Population NUMERIC CONSTRAINT CountryPop
   CHECK (Population >= 0));

CREATE TABLE City
(Name VARCHAR(35),
 Country VARCHAR(4),
 Province VARCHAR(35),
 Population NUMERIC CONSTRAINT CityPop
   CHECK (Population >= 0),
 Longitude NUMERIC CONSTRAINT CityLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Latitude NUMERIC CONSTRAINT CityLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province));

CREATE TABLE encompasses
(Country VARCHAR(4) NOT NULL,
 Continent VARCHAR(20) NOT NULL,
 Percentage NUMERIC,
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT EncompassesKey PRIMARY KEY (Country,Continent));

 INSERT INTO country VALUES ('Albania','AL','Tirane','Albania',28750,3249136);
 INSERT INTO country VALUES ('Greece','GR','Athens','Attiki',131940,10538594);
 INSERT INTO country VALUES ('Macedonia','MK','Skopje','Macedonia',25333,2104035);
 INSERT INTO country VALUES ('Serbia','SRB','Belgrade','Serbia',77474,7379339);
 INSERT INTO country VALUES ('Montenegro','MNE','Podgorica','Montenegro',14026,672180);
 INSERT INTO country VALUES ('Kosovo','KOS','Pristina','Kosovo',10887,1804838);
 INSERT INTO country VALUES ('Andorra','AND','Andorra la Vella','Andorra',450,72766);
 INSERT INTO country VALUES ('France','F','Paris','Ile de France',547030,58317450);
 INSERT INTO country VALUES ('Spain','E','Madrid','Madrid',504750,39181114);

 INSERT INTO encompasses VALUES ('LS','Africa',100);
 INSERT INTO encompasses VALUES ('RM','Africa',100);
 INSERT INTO encompasses VALUES ('MW','Africa',100);
 INSERT INTO encompasses VALUES ('MOC','Africa',100);
 INSERT INTO encompasses VALUES ('MS','Africa',100);
 INSERT INTO encompasses VALUES ('MAYO','Africa',100);
 INSERT INTO encompasses VALUES ('SD','Africa',100);
 INSERT INTO encompasses VALUES ('REUN','Australia/Oceania',100);
 INSERT INTO encompasses VALUES ('HELX','Australia/Oceania',100);
 INSERT INTO encompasses VALUES ('STP','Africa',100);
 INSERT INTO encompasses VALUES ('SY','Africa',100);

INSERT INTO city VALUES ('Tirane','AL','Albania',192000,19.8,41.3);
 INSERT INTO city VALUES ('Shkoder','AL','Albania',62000,19.2,42.2);
 INSERT INTO city VALUES ('Durres','AL','Albania',60000,19.3,41.3);
 INSERT INTO city VALUES ('Vlore','AL','Albania',56000,19.3,40.3);
 INSERT INTO city VALUES ('Elbasan','AL','Albania',53000,20.1,41.1);
 INSERT INTO city VALUES ('Korce','AL','Albania',52000,20.5,40.4);
 INSERT INTO city VALUES ('Komotini','GR','Anatoliki Makedhonia kai Thraki',NULL,NULL,NULL);
 INSERT INTO city VALUES ('Kavalla','GR','Anatoliki Makedhonia kai Thraki',56705,NULL,NULL);
 INSERT INTO city VALUES ('Athens','GR','Attiki',885737,23.7167,37.9667);
 INSERT INTO city VALUES ('Piraeus','GR','Attiki',196389,NULL,NULL);

It the sample data it should only include 2 continents Africa and Australia/Oceania.


texts: Creating non-clustered Index on Partitioned Table that already has an existing Primary Key

texts:
When I create a Primary Key as so:

ALTER TABLE dbo.TABLEA
ADD [MY_KEY] UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID()

And then proceeded to create the following index:

CREATE NONCLUSTERED INDEX [IDX1_TABLEA] ON [dbo].[TABLEA]
(
    [COL_1] ASC,
    [COL_2] ASC,
    [COL_3] ASC,
    [COL_4] ASC,
    [COL_5] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

The following exception is thrown:

  Column [Col_1] is partitioning column of the index PK_TABLE_A_1234.
  Partition columns for a unique index must be a subset of the index
  key.

I think that in order to solve this issue, all I need to do is:

A. Include column MY_KEY as part of the IDX1_TABLEA or ...

B. Include all columns from IDX1_TABLEA as part of the PK as well as including the PK (MY_KEY) as part of the IDX1_TABLEA?

Unfortunately I cannot test either option reliably since our TABLE_A in our DEV environment is not partitioned whereas in our TEST environment the same table is partitioned.

The exception actually occurred while the script to create the PK and the INDEX was being executed in our TEST environment but the same script ran perfectly fine in DEV.

FYI: I already tried option A (adding the MY_KEY column as part of the index IDX1_TABLEA) and it worked in DEV, but again, I'd like to confirm with some expert here that this will work fine when I execute the modified script in the TEST environment again.

So my modified version is this:

CREATE NONCLUSTERED INDEX [IDX1_TABLEA] ON [dbo].[TABLEA]
    (
        [MY_KEY] ASC,
        [COL_1] ASC,
        [COL_2] ASC,
        [COL_3] ASC,
        [COL_4] ASC,
        [COL_5] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

Can anyone confirm whether this is enough to get rid of that exception?

Thanks.

Update

Issue solved. See my own answer below.

In summary, I was creating a new PK in a table that was already partitioned on one of its columns, the new PK needed to include the partitioned column as part of it.


texts: Best way to allow update exceptions on a cell level

texts:
I've got 2 tables, a base table - tBase - and a holding table used to update that base table - tUpdate.  (Note: actual tables will have around 25 columns)

Table tBase

ID  |  name  |  type  |  color  |  size
----------------------------------------
 1      A        Z        red       big
 2      B        Y        blue      tiny
 3      C        X        red       tiny
 4      D       NULL      NULL      NULL

Table tUpdate

ID  |  name  |  type  |  color  |  size
----------------------------------------
 1      A        Z        red       big
 2      B        Z        blue      tiny
 3      C        X        NULL      tiny
 4      D        W        gold      big
 5      E        Y        grey      cat

I would like to design a way to say specific cells should not be updated, but the best I can come up with at the moment involves multiple updates.  Basically, my idea involves having a third table - tExclude that would look like this:

ID  |  ColName
---------------
 2      type
 4      size
 4      color

The actual UPDATE statements would then run for each column and exclude any IDs that are in the tExclude table.  I'd rather not have to make 25 different updates that use the same 2 tables, and I feel like there's got to be a better way of doing it that I'm not finding.

I've looked into CROSS APPLY a bit but couldn't figure out how it could help.  I also looked into MERGE but that also didn't seem to have a way to do what I'm looking for.


texts: Minimizing and Handling Deadlocks Strategies

texts:
I want to discuss deadlock minimizing strategies. From isolation level to retries to different insert strategies.

Lets say we have table A:

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT NULL,
  `changing_data_a` VARCHAR(100) DEFAULT NULL,
  `changing_data_b` VARCHAR(100) DEFAULT NULL,
  UNIQUE KEY `name` (`name`),
  PRIMARY KEY (`id`),
) ENGINE=INNODB

And lets say we get data from multiple providers(100k+ new/updated rows per day), name is unique and changing_data_a and changing_data_b data can be updated from time to time.

Main queries to run against this table:

The instrets of course.
Selects by id
Selects by name
Updating changing_data columns by name

What would be your inserting strategy?

IODKU small batches from any number or threads(same as number of providers) with isolation level Read Committed and 3 retries in case of a deadlock.
Caching the data in memory of the application, inserting from a single thread every x minutes.
Caching the data in a help table without unique key, summarizing the data every x minutes

Any other strategy?

Also would like to add to this post the MySQL documentation regarding deadlocks handling: How to Minimize and Handle Deadlocks


texts: How does Postgres compute the row count estimate during query planning

texts:
I have a simple query such as:
SELECT
    *
FROM
    example
WHERE
    filter_1 = ?
    AND filter_2 = ?
LIMIT
    10

The table is quite large (about 100 millions row) and it has an index similar to the following (the actual index has one more column on the right side but it shouldn't make any difference):
CREATE INDEX example_idx
ON public.example
USING btree (filter_1, filter_2, (...));

So now let's describe the issue:
when I run my query in a prepared statement, the first 5 executions use a custom plan. Then the generic plan is seen as less costly and it is used for the reminder of the prepared statement's lifetime.
Here is an EXPLAIN ANALYZE when Postgres uses the custom plan:
Limit  (cost=0.57..39.35 rows=10 width=78) (actual time=0.024..0.034 rows=8 loops=1)
  ->  Index Scan using example_idx on example c0  (cost=0.57..12345.29 rows=3183 width=78) (actual time=0.024..0.032 rows=8 loops=1)
        Index Cond: (((filter_1)::text = 'rare_value_1'::text) AND (filter_2 = 'frequent_value_2'::custom_enum))
Planning Time: 0.098 ms
Execution Time: 0.045 ms

Here is an EXPLAIN when Postgres uses the generic plan:
Limit  (cost=0.00..11.31 rows=10 width=78)
  ->  Seq Scan on example_idx c0  (cost=0.00..3469262.28 rows=3067235 width=78)
        Filter: (((filter_1)::text = $1) AND (filter_2 = $2))

Here, we can clearly see that the cost of the generic plan is lower.
My problem is how the row count estimate in the Index Scan and the Seq Scan are computed.
The documentation explains how and if I follow their calculation, I arrive at 3183, which is the estimated row count for the custom plan:
rare_value_1 and frequent_value_2 are both in the MCV list. And their frequency is 0.00002667 and 0.99783 respectively. Also, the estimated table row count is 119622152.
0.00002667 * 0.99783 * 119622152 = 3183

The remaining question is, how is it done for the generic plan?
I found that, for some unknown reason, the MCV frequencies are ignored. And Postgresql just looks at the n_distinct values for columns filter_1 and filter_2 (13 and 3 respectively):
estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
                    = 119622152 / (13 * 3)
                    = 3067235

My question is why? Why does Postgresql use such a primitive way to estimate the row count since it has access to better statistics in the form of MCV frequencies?
Postgresql version: 11 (so using the "force_custom_plan" option is not possible for us at the moment).


texts: what files should I exclude/add/copy mysql dbs (without mysqldump)

texts:
I need to have an exact copy of my databases in a clone VM of my server (just in case the server goes down, have the VM ready with the dbs). I know that mysqldump is the best alternative to migrate, but I want to have another alternative. So I decided to do it with ROBOCOPY, and copy data folder. This is my batch

@echo off
set exclude=*.pid *.err *.cnf *logfile* *log_file*
set ORIGIN="mysql_data_path\data"
set DEST="mysql_data_path_mirror\data"

NET STOP mysqld
robocopy "%ORIGIN%" "%DEST%" /E /COPYALL /PURGE /MIR /IS /IT /TEE /FFT /ETA /R:10 /W:5 /ZB /V /XF %exclude%

The problem is that I am not sure which files I should exclude/add/copy, that does not affect the operation when I do the reverse process.

For example, I have put some exclusions (*.pid *.err *.cnf *logfile* *log_file*), but I do not know if they are correct. I would like help in this point. And, apart from data folder, is there anything else that I should copy in order to function in the VM?

Thanks


texts: Using SELECT to call a function in a jdbc migration

texts:
I'm looking to set up some Postgres/PostGIS migrations with clojure/jdbc.

running side-effect functions with SELECT is proving to be an issue, with most migration libs eventually throwing the A result was returned when none was expected error, because at some point they use clojure.java.jdbc/execute! or clojure.java.jdbc/db-do-commands, which seems understandable, but frustrating when you need to call a function that's critical to the migration.

The PostGIS's docs encourage using SELECT statement to create a spatial column:

SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );

Has anyone run into this or found an appropriate workaround for using functions in a clojure/jdbc and Postgres migration?

related tidbits:

this description for manually registering a spatial column looks promising but seems remarkably heavy-handed for something that already has a supporting function
there's also PL/PgSQL's PERFORM statement that I stumbled across but it seems like I'm grasping at straws at that point, despite it looking promising
clojure.java.jdbc/execute! docs give a specific heads-up about only using "general (non-select) SQL operation[s]"


texts: Reinitialize transactional replication using powershell

texts:
Recently I was asked to develop a solution by management to reinitialize a transactional replication whenever it fails using powershell.  The request was made because our 3rd party vendor restores our replicated database as part of their deployment.

I understand there are different versions of SQL servers and Powershell around our server, and develop a script for all versions will be not only hard but also risky.  Also reinitialize will cause the source and destination databases become unusable/locked.  Is anyone aware of this type of powershell solution somewhere?


texts:
This is my current mySQL query:

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread
  FROM TAB_EVENT e
  LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id
  WHERE ( m.`read` IS NULL OR m.`read` = 0)
  GROUP BY e.id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;

I get: All TAB_EVENT rows which have no TAB_MESSAGE messages at all or have at least one unread one (read = BOOLEAN DEFAULT 0).

I need: All TAB_EVENT rows no matter of their messages AND the datetime of the newest unread and the count of unread messages for each TAB_EVENT.

There may be thousands of TAB_EVENT rows and many more TAB_MESSAGE rows and I'd like to avoid running one subquery for each single event.

Alternate 1: Using UNION using the query above plus another one with m.read = 1.
Drawback: Two JOINed heavy queries plus another temporary table.

Alternate 2: SELECT e.* unconditional plus another SELECT event_id, MAX(), COUNT() FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX() and merge everything within the calling Perl script.
Drawback: Loosing the ability of a server-side LIMIT.

UPDATE/RESOLUTION

Here is my final query doing the job:

SELECT e.*, m.unread_last, m.unread
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT event_id,MAX(`datetime`) AS unread_last, COUNT(*) AS unread FROM TAB_MESSAGE
    WHERE `read` = 0
    GROUP BY event_id
    ORDER BY `datetime` DESC
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC
LIMIT 10;

The read IS NULL condition caught up events not having any messages in the original query, the read column is boolean NOT NULL.

This query now returns all events and adds the newest unread message timestamp plus the number of unread messages. Both message columns are NULL for events not having any messages which is perfectly ok (will be translated to "" for datetime and 0 for the count on the Perl side).

Thanks to RolandoMySQLDBA, my query is based on his answer.


texts: How can I split Date column values in Access into 2 columns?

texts:
I have a date column that stores values for begining to end date as:
070314-073114
So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114, any suggestions?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

Thanks in advance


texts: Error when building documentation of Postgres

texts:
I am trying to build the documentation of Postgres on OSX

After getting the source, installing node and jade, I do:

cd /doc/src/sgml
make html

This results in the error:

   fs.js:684
  return binding.lstat(pathModule._makeLong(path));

         ^
Error: ENOENT, no such file or directory '/Users/thomas/Source/postgres/doc/src/sgml/-p'
    at Object.fs.lstatSync (fs.js:684:18)
    at Object.realpathSync (fs.js:1272:21)
    at /usr/local/lib/node_modules/jade/node_modules/monocle/monocle.js:41:26
    at Array.forEach (native)
    at Object.watchFiles (/usr/local/lib/node_modules/jade/node_modules/monocle/monocle.js:39:16)
    at Object.<anonymous> (/usr/local/lib/node_modules/jade/bin/jade.js:98:13)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)

The directory that the make script is expecting to find looks really odd. What am I doing wrong?

My jade --version is 1.3.1.


texts: How can I mask a CMD-Variable reference $(myVar)?

texts:
It is kind of an accademic question, but if I write a SQL-Script for SQL-Server in CMD-Mode, how could I create an column-alias like [$(myVar)]?

SELECT 1 AS [$(myVar)] --error: myVar is not defined


texts: Percona/MySql Table does not exist after innobackupex restore on Vagrant Ubuntu 16.04

texts:
This question is cross posted here from stack overflow at the suggestion of stack overflow commenters (https://stackoverflow.com/questions/47086329/percona-mysql-table-does-not-exist-after-innobackupex-restore-on-vagrant-ubuntu)

I have an odd problem that doesn't make any sense to me. I have a mysql (percona 5.7) galera cluster that consists of 3 nodes.

Full backups are taken regularly using:
/usr/bin/innobackupex --user=${MYSQLROOT} --password=${MYSQLPASS} --no-timestamp ${BACKUP_PATH}${BACKUP_DIRNAME} --parallel=4

This works great an I have tested restoring on production with the following sequence of steps, and the restore works perfectly. The production server nodes are AWS instances based on Ubuntu 16.04 amd64. I have also tested the restore script on other AWS instances with the same setup as prod and it works perfectly:

#!/bin/bash

service mysql stop

s3cmd sync s3://${S3BUCKET}/${NEWEST_BACKUP_DIR} full_backup.tar.gz

tar -xzf full_backup.tar.gz

rm -rf /var/lib/mysql/*
innobackupex --apply-log --redo-only db_backup_full/

innobackupex --apply-log db_backup_full/
innobackupex --copy-back db_backup_full/

chown -R mysql:mysql /var/lib/mysql

service mysql bootstrap-pxc

Everything works as expected so far, now in order to do some local development work, I created a Vagrant config that builds a Ubuntu 16.04 amd64 node using the exact same Ansible role that was used to build the production server. Then when I run my restore script above on my local Vagrant instance it seems like the backup file is downloaded and restored correctly. The /var/lib/mysql/ directory on my local instance looks the same as the /var/lib/mysql/ directory on my production nodes. When I login into mysql I can see all my tables when I use mysql> show tables;. However, when i try to query a table I get the following error:

mysql> select * from Record limit 100;
ERROR 1146 (42S02): Table 'franchisedirect.Record' doesn't exist
mysql>

My research tells me that the issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1).

However, when I look in /var/lib/mysql/ those files are present I can't see how they would not be there as the exact same restore script works on the AWS instances..

Am I missing something here ? Is this a Vagrant issue, a Percona/MySql issue, a innobackupex issue, Ubuntu 10.04 issue ? Im completely stumped, Thanks!


texts:
My DB have a main table that is has lots of commits on a daily basis, with about 15 indexes on this table.
The table has 25M rows and the dead row count seem to exceed the number of live rows at the moment.

The auto-vacuum seems to run for over a day on this table, and doesn't seem to catch up with the new generated dead rows.

I diverted some actions that I used the DB for filtering to be done in memory, as in, instead of inserting rows and later clean them up using DB queries, I now do it in-memory in-advance.

I'm thinking of removing some indexes since they seem to get scanned to and add a significant overhead.

Any tips on how to improve the speed?

Additional details:

The DB has 8GB RAM, the table is around 30GB, but the indexes are big, each is around 5GB.
The dead row count is now around 40M while the rowcount seems to be stuck exactly the same number for a while 25M (and some change).
I use automatic vacuum tuned up to get triggered frequently; this is the "nightly vacuum" I mention above.

Update:
It seems what I tried is working, previously I was committing lots of rows to the DB, and delete shortly after by launching a cleanup run.
I changed it to filtering the data in advance and not commit data I will cleanup later, causing much less work on the DB and less dead rows.

It seems the auto-vacuum which is starting to catch up slowly, but it takes significant time since each vacuum on this table runs for almost two days.

(unfortunately since I'm on Heroku, I can't control many parameters)


texts: Does SQL Server redistribute data after adding an additional data file?

texts:
Imagine a database about 30GB in size using 3 data files on the same disk.

Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three more data files, how's the data redistributed to the new files? Do I have to do something?


texts:
I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server:

+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| tweetid     | varchar(255)   | NO   | PRI | NULL    |       |
| user_id     | varchar(255)   | NO   | PRI | NULL    |       |
| screen_name | varchar(255)   | NO   | PRI | NULL    |       |
| date        | datetime       | NO   | PRI | NULL    |       |
| json_src    | varchar(15000) | YES  |     | NULL    |       |
+-------------+----------------+------+-----+---------+-------+

When I select rows by user_id, query time ranges from a few seconds to more than 2 minutes, which is completely unacceptable. Initially I thought it was a memory problem, since I do not have this problem when indexes take up less space. So I made sure innodb_buffer_pool_size is higher than the total index size but that did not solve it. As total index size becomes large, some indexes are fast to get, others very slow. It does not make sense to me because all indexes should be fitting into memory.

Many posts seem to be about the same problem, and I tried all suggestions I could find, but nothing worked.

Any idea ?

Edit:

Here is the output of 'show create table name;'. As you can see, user_id is a primary key so it should be indexed.

CREATE TABLE `test` (
  `tweetid` varchar(255) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `screen_name` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `json_src` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`tweetid`,`user_id`,`screen_name`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Edit 2:

The exact command is:

  select * from twittertweets where user_id=3678084920 limit 2;

With a select in front I get:

  | id | select_type | table         | type | possible_keys
  | key  | key_len | ref  | rows    | Extra       |
  |  1 | SIMPLE      | twittertweets | ALL  | ix_twittertweets_user_id
  | NULL | NULL    | NULL | 2231775 | Using where |

Since I created a new table, let me should you the output of 'show create table':

  | twittertweets | CREATE TABLE `twittertweets` (
  `tweetid` varchar(50) NOT NULL,
  `user_id` varchar(25) DEFAULT NULL,
  `screen_name` varchar(25) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `json_src` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`tweetid`),
  KEY `ix_twittertweets_user_id` (`user_id`),
  KEY `ix_twittertweets_screen_name` (`screen_name`),
  KEY `ix_twittertweets_date` (`date`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


texts: Trying to connect to AWS RDS from an AWS EC2 instance

texts:
I am trying to connect to an AWS RDS instance from an AWS EC2 instance. I ssh into the AWS EC2 instance, and then issue the following command:

mysql -h xxxxxx.ciy4oapkpytm.us-east-1.rds.amazonaws.com -P 3306 -u root -p

and when I enter my password, I get the following:

Access denied for user 'root'@'ip-10-xxx-xxx-50.ec2.internal' (using password: YES)

The ip address: 10.xxx.xxx.50 is already in my RDS instance security group.

Any ideas?


texts: Running a command in psql `-c` that requires identifier quoting

texts:
I have created a database schema as "mytest.us" and inside this schema, I have created a table "a.b.c".

When I do

select * from "mytest.us"."a.b.c";
 year | mydata
------+--------
 2011 | test1
 2010 | test1
(2 rows)

but when I do

\\! $TARGET/sql -p $PGPORT -d $PGDBNAME -c "select * from "mytest.us"."a.b.c"";

sh: -c: line 0: unexpected EOF while looking for matching `"'

sh: -c: line 1: syntax error: unexpected end of file

How can I specify my table in that situation?


texts: Messaging Database - How to handle multiple recipients

texts:
So I am trying to develop a relational database (I'm using MySQL, planning on a Java front end) where I have 3 tables (People, where I store names and relevant personal information... and Messages (where for every message sent, the relevant information is stored. Each message is given an ID# from which you can draw the date and time sent, the content of the message, the sender of the message, and the recipient of the message).

My problem is setting up the database such that each message can have just one sender but multiple recipients. For instance, I can easily handle message ID 73 being sent from User 6 to User 3. But how would I handle if message ID 74 was sent from user 4 to users 2,3, and 8? Is there a way to allow multiple recipients? Would I need to rethink how the database is structured? Anyone got some advice/tips?


texts: How to determine how much disk-space postgresql 9.1 requires as temp_space to run a query?

texts:
Given a query q to execute. Is there a way to determine how much disk-space postgresql will (or used) as temporary_space in order to execute it (during its execution) ?

I'm under Ubuntu 12.0.4 with postgresql 9.1


texts: export SQL statements from Oracle 11g database via command line

texts:
I'd like to export an Oracle 11g database as a series of SQL statements via the command line.  I'd like the functionality of SQL Developer (the process described here https://dba.stackexchange.com/a/10071/7660) but via the command line.

Note that the data must be in a flat file and not a binary format (as with imp/exp or impdp/expdb (Datapump)).

Is this possible?  If so, how?


texts: I have just updated my MacBook to Mountain Lion. Since then, my MAMP installation does not work properly so I decided to use the build httpd/php stuff. For this, I have additionally installed a new mysql server (following this tutorial). Everything runs very well. Now my question is, how to use my old databases in my new server? I tried to create a new database which creates a folder "databasename". Into this folder I moved all the .frm files, corrected rights and owner and started mysql, but unfortunately, phpmyadmin does not recognize the tables... did I something wrong? Alternatively, I would just set the data directory of the new mysql server to my old directory, but I cannot find the my.cnf file :-( Where is it stored?


texts: MySQL: What is SQL profiler, does it different for MySQL?

texts:
Whenever SQL profiling comes in mind, focus goes to two below sections:

GUI tools like - MONyog, Neor Profile SQL
set profiling =1; Execute Query; show profiles; show profile;

I am really confuse between these two about SQL profiling.

Which one is correct or it is something other than these two?
SQL profiling in MySQL different than SQL profiling in common RDBMS?
How SQL profiling is useful and why we do we use it?


texts: Changing compatibility level from 100 to 120 - Lots of performance issues!

texts:
We have been testing to change the compatibility level of our OLTP production databases from 2008 (100) to 2014 (120).

We are experiencing a lot of performance issues with queries that with high cardinality that are taking 60 seconds or more. The problems appear to originate from the changes to the cardinality estimator that Microsoft has introduced as part of SQL Server 2014.

In one instance, I am seeing SQL Server performing an index scan on an index with 200 million records (as opposed to doing a seek for a handful of records). The query is relatively straightforward and we are very confused as to how SQL Server can miss it.

We have 1000s of queries and testing all of them in all scenarios is impossible.

We have tried to optimize each of the bad performing queries. The only thing that seems to work is to either:

Use ugly join hints
Use trace flag 9481 at the query/global level (which basically forces queries to use the legacy cardinality estimator)

We have SQL Server 2014 SP1 installed. We tried using trace flag 4199 and 9476 with no luck.

At this point I see the following options:

Postpone the upgrade of the compatility level until we migrate to SQL Server 2016 (or a future version)
Use trace flag 9481 globally
Use trace flag 9481 at the query level (I am more in favor of turning it on at the instance level though)

My understanding is that SQL Server 2014 SP2 does not have fixes to the cardinality estimator.

We noticed that SQL Server 2016 comes with another update to the cardinality estimator. Do we know if that update addresses most of the issues introduced as part of the SQL Server 2014 cardinality estimator?

Could someone please advise?

Thanks


texts:
We're trying to track down a performance issue in our PostgreSQL database.
We suspect of some under-performing queries, but which?
Is it possible to find the most popular queries being executed on the database?
What about their performance, how long they're taking?
It's PostgreSQL 11, hosted in Google Cloud's CloudSQL.


texts: SQL Server performance for tables having large number of columns

texts:
I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, nvarchar etc.

Also about 50% XML column will have about 40 to 60 rows and other will have less number of rows. The entire table will have about 2000 to 3000 rows. Theoretically SQL allows maximum of 1024 columns. But what will be the performance in my scenario? Does this design will effect performance?

Also does SQL Server have limitation for row size?


texts:
I've a table in which data is like this for single user

ID - Number - SubNumber - Name

1   101        201101     Jack
2   101        201102     Jack
3   101        201103     Jack
4   101        201107     Jack
5   101        201111     Jack
6   101        201112     Jack
7   101        201113     Jack
8   101        201161     Jack
9   101        201162     Jack
10  101        201163     Jack
11  101        201164     Jack
12  101        201165     Jack

I want to get records like this without using any kind of loop.

Number - Name -  SubNumber

101     Jack     (201101-201103, 201107, 201111-201113, 201161-201165)

Currently I'm able to get records in form of this

Number - Name -  SubNumber

101     Jack    (201101,201102,201103, 201107, 201111.201112,201113, 201161,201162,201163,201164,201165)

Query to get upper result is

SELECT  Number, Name
,STUFF((SELECT ', ' + CAST(SubNumber AS VARCHAR(50)) [text()]
     FROM [Table]
     WHERE Number= t.Number
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') SubNumber
FROM [Table] t
GROUP BY Number,Name
having Number= '101'

Am totally stuck over here. Any kind of help will be appreciated.


texts: SSRS Subscription Error: "Failure sending mail: Report processing has been canceled by the user. Mail will not be resent."

texts:
On the report Subscriptions page, under "Status" for Last Run, it has the following error:

  Failure sending mail: Report processing has been canceled by the user. Mail will not be resent.

It's a subscription, no person canceled this. Maybe there is a problem with a service.

I can run the report fine right now.
The only log I can find is here:

  C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

There are two lines for the report:

  notification!WindowsService_508!d24!02/10/2014-05:45:05:: i INFO: Handling subscription 75b94483-618d-4463-b6c0-90916b92cb6 to report ReportName, owner: domain\user, delivery extension: Report Server Email.
  library!WindowsService_508!d24!02/10/2014-05:45:05:: i INFO: RenderForNewSession('/Reports/ReportName')

These lines don't indicate an error occured.
I've been looking through all of the logs I could find and I can't find any sort of description as to what would have caused this.

I would like to find out where this information is logged, so I can create a job that looks for messages like this to alert users of a failed report.

I would also like to know where the logs are in case I do need to troubleshoot something vague like this.

I really would appreciate any help :)


texts:
I has a small database structure with an Account and Person. I want apply the following requirement:

Account have one Person

Person do not have necessarily one Account (can be null)

When I delete an Account I don't necessarily delete Person

When I delete a Person I want delete Account

How to do that ? (one to one relationship ? on delete cascade ?)

This is good ?

Some clarification after getting some answers:

Thank for your answers.
I try to explain more the contextual meaning of Account and Person.

A Person is a contact with personal profile (Physical person, Customer, Company).

An Account is specified by an username, password, email. It's really an account for sign in on a app.

Therefore a Person can be exist without a account. A Person can only have a maximum of 1 Account. In a other direction Account have necessarily a Person.

Consequently when we remove an Account the Person is not also remove. But When I remove Person this associated Account no longer needed.

PS: I use PostgreSQL.


texts:
What is the most space efficient way to store arbitrary length univariate polynomials? I have a large amount of polynomials that look like

3*x^7  + 12*x - 13
9*x^32 - x^14 + 2*x^3 - 9
...

Each polynomial is composed of an arbitrary number of integer coefficients for a given power, e.g. the following polynomials could be stored as

(3,7) (12,1) (-13,0)
(9,32) (1,14) (2,3) (-9, 0)

which is in fact exactly what I do now. I have a SQLite schema that looks something like

 CREATE TABLE poly_lookup (
     poly_id INTEGER PRIMARY KEY AUTOINCREMENT
 );
 CREATE TABLE poly (
     poly_id INTEGER,
     coeff   INTEGER,
     power   INTEGER
 );
 CREATE INDEX idx_poly  ON poly(poly_id);

This works, but I'm not sure this is the most space efficient solution. caveat responsum, I only know SQLite, but I would be willing to learn a completely new type of database system (mongodb, neo4j, ...) if it would help the solution.


texts: SQL server bulk insert fail code 5 [SQLSTATE 42000] (Error 4861)

texts:
Following error occurs on running sql job.

Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861).  The step failed.

Looks like permission error. Scenario is that Source File is located on Server-A and SQL Server is installed on Server-B. How can I give access to file. SQL User is not showing on below window.


texts:
I need a little help with a trigger.
Where I'm in wrong:

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

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

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


texts:
I am trying to understand why my query takes a really long time even though I have the required columns indexed:

SELECT entity_id,
       id,
       report_date
FROM own_inst_detail
WHERE ( own_inst_detail.id = 'P7M7WC-S' )
  AND ( own_inst_detail.report_date >= '2017-02-01T17:29:49.661Z' )
  AND ( own_inst_detail.report_date <= '2018-08-01T17:29:49.663Z' )

The cached result of EXPLAIN ANALYZE is as follows:

Bitmap Heap Scan on own_inst_detail (cost=20.18..2353.55 rows=597 width=22) (actual time=1.471..6.955 rows=4227 loops=1)
  Recheck Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
  Heap Blocks: exact=4182
  ->  Bitmap Index Scan on own_inst_detail  (cost=0.00..20.03 rows=597 width=0) (actual time=0.901..0.901 rows=4227 loops=1)
        Index Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
Planning time: 0.123 ms
Execution time: 7.801 ms

This part of the query takes 4 out of 5 total seconds that my full query takes.

I have combined index for id and report_date. I also have two standalone indexes for those columns.

I've tried playing around with setting high work_mem as well as lowering the random_page_cost  but nothing really helps.

Any additional suggestions are greatly appreciated.

I found similar question How to index WHERE (start_date >= '2013-12-15') which recommends adding a B-Tree index, but I already do have index for report_date.

Create table script:

CREATE TABLE IF NOT EXISTS public.own_inst_detail (
    entity_id character(8) NOT NULL,
    id character(8) NOT NULL,
    report_date date NOT NULL,
    PRIMARY KEY(report_date)
);

Index:

CREATE INDEX indx_own_inst_detail_report_date_desc ON own_inst_detail (report_date DESC NULLS LAST)

CREATE INDEX indx_own_inst_detail_id_report_date_desc ON own_inst_detail (id, report_date DESC NULLS LAST)


texts: Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

texts:
I have a text file (or an xls) with a 35k lines, each containing a single ID.
I'm required to copy every entry of TableA to TableB where the ID is in said list.

Would it be best if I'd create a new table, with the IDs, in order to use this table TableC in the query as WHERE ID IN TableC? If so, whats the easiest way to fill the new table with the IDs from the txt file?

Or is there a way to use a text file or the list itself in the query?

I thought something like this query should be appropriate, but I have no clue how to use the text file.

SELECT * INTO TableB
FROM TableA
WHERE ID IN text_file

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2


texts:
The following query is looking for all towns that are between 5 and 10 kilometers from a hard-coded latitude/logitude (=another town actually). I have 37010 towns.

I'm using Symfony 2 which "creates" a query through Doctrine. This query is flagged by my MariaDB server as "not using indexes". I dont know what's going on, because:
(1) Here's the query:

SELECT
    v0_.id AS id0,
    v0_.nom AS nom1,
    v0_.url AS url2,
    v0_.cp AS cp3,
    v0_.insee AS insee4,
    ROUND(
        6371 *
        ACOS(COS(RADIANS(50.58907000)) *
        COS(RADIANS(v0_.lat)) *
        COS(RADIANS(v0_.lng) -
        RADIANS(3.16710500)) +
        SIN(RADIANS(50.58907000)) *
        SIN(RADIANS(v0_.lat))), 2
    ) AS sclr5
    FROM ville v0_
    HAVING sclr5 > 4 AND sclr5 <= 10
    ORDER BY sclr5 ASC LIMIT 20 OFFSET 0;

Here's the time:

# User@Host: x[x] @ localhost []
# Thread_id: 1514  Schema: mydatabase  QC_hit: No
# Query_time: 0.071503  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 37030

And here's the table:

MariaDB [mydatabase]> desc ville;
+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| id_origine      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| date_v_creation | datetime            | YES  | MUL | NULL    |                |
| date_v_debut    | datetime            | YES  | MUL | NULL    |                |
| date_v_fin      | datetime            | YES  | MUL | NULL    |                |
| article         | varchar(4)          | YES  |     |         |                |
| nom             | varchar(150)        | NO   | MUL |         |                |
| url             | varchar(150)        | NO   | MUL |         |                |
| cp              | varchar(10)         | NO   | MUL | NULL    |                |
| insee           | varchar(10)         | NO   |     |         |                |
| id_region       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| id_departement  | bigint(20) unsigned | NO   | MUL | NULL    |                |
| lat             | decimal(15,8)       | NO   | MUL | NULL    |                |
| lng             | decimal(15,8)       | NO   |     | NULL    |                |
| sound           | varchar(252)        | YES  | MUL | NULL    |                |
+-----------------+---------------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)

MariaDB [mydatabase]>

If I do an explain on this query i get:

+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|    1 | SIMPLE      | v0_   | ALL  | NULL          | NULL | NULL    | NULL | 36510 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+

1 row in set (0.00 sec)

I have 37010 towns and MariaBN tells me Rows_examined: 37030. I dont get that. Anyway, how would you optimize it?


texts: Why is my Postgres database working for a while and then not able to “start server” once restarted?

texts:
Recently, I've started playing around with an old Raspberry Pi 3 b+, and I thought it would be good practice to host a Postgres database on my local network and use it for whatever I want to work through. I understand that running Postgres on a Raspberry Pi with 1GB of memory is not ideal and can take a toll on the SDcard, but I've updated the postgresql.conf file and specified that the data directory path is to utilize a 1TB SSD. Additionally, I've installed zram and log2ram to try and curb some of the overhead on SDcard.
Overview of tech I'm working with:

Raspberry Pi 4 B
Postgres 12
Ubuntu server 20.04 (no gui, only working from terminal)
1TB SSD

Yesterday, I was writing to the Postgres db from a python notebook without any issue, but once I restarted the Raspberry Pi, I was unable to reach the db from DataGrip and would receive the following error from my terminal in Ubuntu:
psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I checked the status of the postgres server and that seemed to be alright...:
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-01-28 13:34:41 UTC; 20min ago
    Process: 1895 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1895 (code=exited, status=0/SUCCESS)

Jan 28 13:34:41 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
Jan 28 13:34:41 ubuntu systemd[1]: Finished PostgreSQL RDBMS.

This is what is provided in the postgresql-12-main.log:
2021-01-28 13:17:23.344 UTC [1889] LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-01-28 13:17:23.362 UTC [1889] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-01-28 13:17:23.362 UTC [1889] LOG:  listening on IPv6 address "::", port 5432
2021-01-28 13:17:23.365 UTC [1889] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-01-28 13:17:23.664 UTC [1899] LOG:  database system was shut down at 2021-01-28 01:43:38 UTC
2021-01-28 13:17:24.619 UTC [1899] LOG:  could not link file "pg_wal/xlogtemp.1899" to "pg_wal/000000010000000000000002": Operation not permitted
2021-01-28 13:17:24.670 UTC [1899] FATAL:  could not open file "pg_wal/000000010000000000000002": No such file or directory
2021-01-28 13:17:24.685 UTC [1889] LOG:  startup process (PID 1899) exited with exit code 1
2021-01-28 13:17:24.686 UTC [1889] LOG:  aborting startup due to startup process failure
2021-01-28 13:17:24.708 UTC [1889] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

I think it could have something to do with permissions. If I try uploading a ~30MB csv to the postgres db, it will crash and the server will not start again because files cannot be written to the pg_wal directory. The only file in that directory is 000000010000000000000001 and does not move on to 000000010000000000000002 etc. while writing to a table.
Please let me know if you have any questions or if you would like for me to include any additional information. I appreciate any pointers you may have for head ahead of time.


texts: Why is my DB size increasing when rebuildling an index using sort in tempDB in SQL Server?

texts:
I am running Ola Hallengren's SQL Maintenance scripts using the following command:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'MyDatabase'
, @FragmentationLow = NULL
, @FragmentationMedium = 'INDEX_REBUILD_OFFLINE'
, @FragmentationHigh = 'INDEX_REBUILD_OFFLINE'
, @FragmentationLevel1 = 10
, @FragmentationLevel2 = 30
, @UpdateStatistics = 'ALL'
, @OnlyModifiedStatistics = 'Y'
, @LogToTable = N'Y'
, @TimeLimit = 23400
, @MinNumberOfPages = 1000
, @Indexes = 'ALL_INDEXES'
, @MaxDOP = 7
, @SortInTempdb = 'Y'

Up until recently a number of the indices were unable to rebuild due to space constraints so I shifted tempDB to its own drive, configured tempDB to use 8 different files, and started sorting in tempDB (each tempDB file is roughly 10GB and growth set to 500MB each). Some indexes that were difficult to rebuild previously were able to complete relatively quickly, however, i have one index that I have been running for more than an hour in MyDatabase and that database is rapidly increasing in size. Nothing else is currently running and TempDB has not increased in size at all.

Can anyone help me figure out why MyDatabase is increasing so rapidly? Does anyone have any suggestions about what it could be causing this?

Running SQL Server 2012 if that helps.
Thanks


texts:
A node was evicted from a windows cluster without first uninstalling the sql first. When the node was put back, an error was produced when we tried to failover. It was reporting a missing dll. Feels like removing the node before uninstalling sql from it has corrupted or deleted part of the build.

Has anyone else encountered this situation? Can this be repaired or is it best to uninstall the sql from the node and start again installing a new sql node.

Thanks!


texts: SQL Server + Microsoft Business Intelligence -- free version or online learning environment?

texts: I have some experience writing queries and stored procedures in SQL server. I would like to learn how to use the Microsoft Business Intelligence tools (SSIS/SSAS/SSRS). There are loads of books, videos, and online courses out there to learn these tools (including free courses on the Microsoft website). My challenge is that I do not have access to a SQL server database with these tools. I see that one can download a free 'Express' version of SQL server from the Microsoft website, but this version does not have SSIS/SSRS/SSAS. Is there a free version of SQL server that has these tools--either a version one would download and install oneself, or an online environment with a full version of SQL server+BI tools that one could access for learning/practice purposes?


texts:
Does Sybase have a tool like AWR in Oracle which can be used to identify high load SQL, tell the important waits that are happening, help in understanding the workload?
What are the main tools to be used to identify high load SQL and tune query performance?


texts: pgpool load balancing is sending all queries only to master

texts:
My two postgresql servers are configured for streaming replication, which is working fine.

Pgpool is configured for Master Slave mode / Load Balance Mode.

pgpool.conf:

listen_addresses = '*'
port = 9999
backend_hostname0 = 'master-postgres-ip'
backend_port0 = port-no
backend_weight0 = 1
backend_data_directory0 = 'data-dir'
backend_hostname1 = 'slave-postgres-ip'
backend_port1 = port-no
backend_weight1 = 1
backend_data_directory1 = 'data-dir'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode='stream'

I expected all write queries will go to primary and read queries will be distributed between two. But, all the queries are going only to master. However, if I stop master, queries are going to slave.

Can somebody tell me what might be going wrong?

pgpool gives below log on startup:

2015-11-03 17:25:56: pid 21284: LOG:  find_primary_node: checking backend no 0
2015-11-03 17:25:56: pid 21284: LOG:  find_primary_node: checking backend no 1
2015-11-03 17:25:56: pid 21284: DEBUG:  SSL is requested but SSL support is not available
2015-11-03 17:25:56: pid 21284: DEBUG:  authenticate kind = 3
2015-11-03 17:25:56: pid 21284: ERROR:  failed to authenticate
2015-11-03 17:25:56: pid 21284: DETAIL:  invalid authentication message response type, Expecting 'R' and received 'E'
2015-11-03 17:25:56: pid 21284: DEBUG:  find_primary_node: no primary node found


texts:
I have SQL 2016 Standard Edition instance with 40 logical processors in 2 nodes. (2 physical processors x 20 cores = 40 cores total)
Per Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server I want to set MaxDOP to 8

Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

BUT per max degree of parallelism Option I can only set it 4, but it looks like that applies to 2008 and I am at 2016

The maximum value for the degree of parallelism setting is controlled by the edition of SQL Server,

In reality, I changed it to 8, SQL did not complain and the value stayed at 8.  I set it back to 0 while continuing to research.
My concern is that if I set to 8, I might really be setting it to 4.  Before I make any changes I want to know that 8 = 8. I have searched and not found anything else saying that MaxDOP limits in newer versions of SQL is edition based.
Is the MaxDOP limit in SQL 2016+ Edition based?
EDIT Soft NUMA is currently setting to 4 nodes at startup. still learning but If I understand correctly I think this means a single node with 10 logic processors is the most that could be applied to any one statement (query) so setting the MaxDOP to 8 in order to preserver two processors is probably not worth the effort, without some existing problem to solve.
EDIT2 Nodes do not limit parallelism

Place threads on multiple nodes;
This is the most common decision made by SQL Server.  The decision spreads the workers across multiple nodes as required.  The design is similar to full except the starting position is based on the saved, next node, global enumerator.
source


texts: MySQL connection timeout error retrieving big results

texts:
All of the sudden our MySQL 5.6 server (on Windows server) started to give us this error: "Lost connection to server during query".

We never had this problem before and we haven't touch MySQL config file for months.

This only happens with queries that return a lot of data.

Let's say table "NSA_email_log" has 250 000 rows and 8 longtext columns.
Query like this:

Select * from NSA_email_log

will time out (and it will time out after like ~5 seconds of running),

but query like:

Select * from NSA_email_log group by email_address

(given that it will return less results but will run longer) will not.

Heck, right know I'm running this crazy query

 Select count(*) from NSA_email_log
        join NSA_email_log
        join NSA_email_log

and no time out after 5 minutes of running.

Again, never had such problem (even running long queries returning a lot of data) and never changed any of the config options (regarding read timeout, connection timeout, packet size and so on).

Any way to troubleshoot this?


texts: Cannot login to SQL Server as a member of AD group

texts:
I have created a login for an AD group:

CREATE LOGIN [MYDOMAIN\Development Admins] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

One of the members of that AD group is a user named DBGuy. I can see the DBGuy user in this AD group if I execute

xp_logininfo 'MYDOMAIN\Development Admins', 'members'

But if I try to login using the DBGuy account, I get an error:

  Error Number: 18456
  Severity: 14
  State: 1
  Line Number: 65536

And in the error logs I see:

  Login failed for user 'MYDOMAIN\DBGuy'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.50.127]

Some other info....

Other AD accounts seem to work fine as Windows logins, I am only
having an issue with this group.
I found an article from Aaron Bertrand
describing a similar issue regarding the default database in the
connection- I have set the default database to master in the
connection settings.
One blog post suggested using exec sp_change_users_login Report to look for orphaned users; this returns zero rows for me.


texts: Need to create an SQL result suitable for data mining

texts:
I have a database of supermarket transactions that I wish to use for associative data mining purposes. Below is an image of my current SQL query however I am not sure if what I require is possible with SQL (Explained below image).

I need my results to be in the following format - Each unique ProductName needs to have its own column with a binary 0/1 representation of whether the CustomerID purchased the product.

Is this possible? If so where exactly would I start?


texts: How to Create users in sql server for accessing only one database using Management Studio

texts:
How to Create users in sql server 2008 R2 for accessing only one database using Management  Studio.

regards,
salman


texts: SQL Find Code for Each Separate Column in View

texts:
We have a SQL View, trying to find the calculation/business logic for each column into a select or table.

create dbo.CustomerVw
as
   FirstName + ' ' LastName as FullName
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason
from dbo.Customer

Expected Results:

Row 1: FullName: FirstName + ' ' + LastName
Row 2: Age: datediff(yy,BirthDate,GETDATE())
Row 3: City: New York

Code below only displays table code as large text. How would separately display each calculation logic for columns? Each Column row can be a text.

SELECT
    v.TABLE_NAME,
    v.VIEW_DEFINITION
FROM
    INFORMATION_SCHEMA.VIEWS v
WHERE
    v.TABLE_NAME LIKE '%%'


texts: For Oracle, we mount the instance to the database or mount the database to the instance?

texts:
The following text is extract from oracle documentation.

  Start the instance, and mount and open
  the database. This can be done in
  unrestricted mode, allowing access to
  all users, or in restricted mode,
  allowing access for database
  administrators only.

  ....

  To mount a database to a previously
  started, but not opened instance, use
  the SQL statement ALTER DATABASE with
  the MOUNT clause as follows:

  ALTER DATABASE MOUNT;

You can read it directly from http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/start.htm

The first part gives me an impression that we are mounting the instance to the database. But the To mount a database to a previously started, but not opened instance part gives me an impression that we are mounting database to an instance.

Which one understanding is right? Anyone can help to explain it please?

Thanks


texts: How one can install a given R package in SQL Server

texts:
I was asked to write a R code to do computations. I managed to do it such that it works well in R. Next, I was asked to do the same function in SQL Server. I was unable to do it as it requires a package psych that SQL Server won't find. How can I get R running properly in SQL Server such that it find the package psych? Microsoft SQL Server Management Studio v17.0 outputs

  An external script error occurred:
  Error in library(psych) : there is no package called 'psych'
  Calls: source -> withVisible -> eval -> eval -> library`


texts: Seperate Address Table for Patient and Emergency Contact Person?

texts:
Is it good to separate address table for patient and emergency contact person?

Currently this are my tables

Patients

id

first_name

last_name

status

gender

PatientAddresses

id

patient_id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

EmergencyContactPersons

id

patient_id

first_name

last_name

EmergencyContactPersonAddresses

id

emergency_contact_person_id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

OR THIS IS BETTER?

Addresses

id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

EmergencyContactPersonAddresses

id

emergency_contact_person_id

address_id

PatientAddresses

id

patient_id

address_id


texts: *Why* Does the SQL Server Debugger Require Sysadmin Privileges

texts:
I recently posted a question looking to find an alternative to granting devs sysadmin privileges just to use the sql server debugger. I am finding that there really isn't a feasible solution to this.

My question here is, WHY does the sql server debugger require sysadmin privileges? I want to understand the reasons behind designing it this way.

In a dev environment, developers should be able to do dev activities such as using the built-in debugger tools without granting sysadmin, instead of being told to basically build his own debugging mechanism using print statements, etc.

SQL server has privileges for so many specific roles/tasks, etc, it's odd not to see a separate one for this e.g. which could be granted only in a dev environment, etc.

I don't think this question is a duplicate of the others, since this question is not about how to use the debugger if you aren't a sysadmin, but rather, it is so I can understand why it is designed this way.


texts: What kind of SSD to speed up querying a lot of data?

texts:
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).

This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.

Is there a difference between PCIe SSDs and M.2 SSDs?

What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?


texts:
According to this it is impossible to use alter table for moving tables. So if I understand correctlly - to move table it is neccessary to do:

Create new table
Copy all row from old one
drop old table
Rename new table created.

First of all - it these steps are correct?
Second - is there more beautiful way to this operation?


texts: Oracle convert multiple rows to column using pivot


texts: Complex MySQL query through relational tables

texts:
I am generating a query that returns a set of profiles that meet all the conditions posted below

I am receiving the data like so in Ruby, then making a dynamic MySQL query based on the contents -

[{ attribute_id: 58, parent_profile_name: 'Douglas-Connelly' },
{ attribute_id: 26, parent_profile_name: 'Brekke LLC' },
{ attribute_id: 35, val: 'Asa' },
{ attribute_id: 38, val: 'Stanton' }]

These are the current contents of the database

profile_attribute_values
profile_id  attribute_id    parent_profile_id   val
6           58              2
6           26              5
6           35              nil                 'Asa'
6           38              nil                 'Stanton'

profile
id     name
2      Douglas-Connelly
5      Brekke LLC
6      nil

I need to return all profiles that meet all the conditions - profiles that have a relation to profile_attribute_values, where the attribute_id is x, and the val is y, AND where the attribute_id is x, and the parent_profile name = y

WHAT I CURRENTLY HAVE

SELECT * FROM

(SELECT P2. *
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
) A,

(SELECT P1. *
FROM profiles P1
INNER JOIN profile_attribute_values PAV ON P1.id = PAV.profile_id
INNER JOIN profile_attribute_values PAV2 ON P1.id = PAV2.profile_id
WHERE (PAV.ne_attribute_id = '35' AND PAV.val = 'ASA')
AND (PAV2.ne_attribute_id = '38' AND PAV2.val = 'Stanton')
) B

WHERE A.id = B.id

This will return

profile
id    name
6     nil

which is exactly what I want, though the tricky part is the second parent_profile condition where I need attribute_id 26, and parent_profile_name: 'Brekke LLC'

I know this wont work, but I need this to do something like this

SELECT * FROM

(SELECT P2. *
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
AND (PAV.ne_attribute_id = '26' AND P1.`name` = 'Brekke LLC')
) A,
.....

I am generating the SQL statement dynamically, so I really need it to be as clean as possible. I generally use ruby active record for everything, so I am a little babied when it comes to SQL statements. Thanks!


texts:
I have a job that runs for multiple regions across the globe. Depending on what region is running and what time zone it's in, the job might not run. It uses GOTO to get to the end and reports that it was skipped due to time and completes.

This runs as expected with no issues.

The Issue: there are times when a linked server farther down the proc is under really heavy load. We know about it and cannot avoid it - so we don't query the linked server at this time.

But, we're still getting an error as if it's queried:

  Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB
  provider "SQLNCLI11" for linked server   returned message "Unspecified
  error". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI11" for
  linked server   returned message "Query timeout expired". [SQLSTATE
  01000] (Error 7412).  The step failed.

Are linked servers validated when a proc starts causing this schema check that times out?


texts: Does Postgresql have any tools that translates a schema creation script to a dump form?

texts:
Given some create table script as

create table t_foo (
    id bigserial primary key,
    bar_id bigserial references t_bar
)

Postgresql will automatically create the primary key and foreign key that I can see if I pg_dump the schema.

Is there a way for me to get the final schema creation script without having to run the initial script on a real db and dumping it?


texts: Why does removing these LOWER calls change the execution plan like this?

texts:
While troubleshooting a slow query I was misled for a while by the actual execution plan telling me a sort late in the query where there should be few rows was taking >80% of the query time. At the time the query was built dynamically in a stored procedure and the bulk of the slowness was being caused by parameter sniffing, but for a long time I tried to figure out why it was taking so long to sort these rows.

Here's the query:

SELECT Id
 , FirstName
 , LastName
 , FullName
 , DateOfBirth
 , CityStateZip
 , DriversLicenseState
 , DriversLicenseNumber
FROM (SELECT c.EntityId Id
       , p.FirstName
       , p.LastName
       , p.FullName
       , c.DateOfBirth
       , c.UpdateDate
       , CityStateZip = CASE a.EntityAddressId
           WHEN NULL THEN NULL
           ELSE CONCAT(a.City, ', ', s.Code, ' ', a.Zip)
           END
       , ds.Value DriversLicenseState
       , dn.Value DriversLicenseNumber
       , ROW_NUMBER() OVER (
               PARTITION BY p.FirstName, p.LastName, c.DateOfBirth
                    , ds.Value, dn.Value
               ORDER BY c.UpdateDate DESC) RowNum
  FROM Store.Customer c
  INNER JOIN Entity.Person p ON c.EntityId = p.EntityId
  LEFT JOIN Entity.EntityAddress a ON c.EntityId = a.EntityId
  LEFT JOIN Vendor.StateProvince s ON a.StateProvinceId = s.StateProvinceId
  LEFT JOIN (
         SELECT ca.CustomerId, ca.Value
         FROM Store.CustomerAttribute ca
         INNER JOIN Models.Attribute a ON ca.AttributeId = a.AttributeId
         WHERE a.AttributeCode = 'DriversLicenseState') as ds
             ON c.EntityId = ds.CustomerId
  LEFT JOIN (
         SELECT ca.CustomerId, ca.Value
         FROM Store.CustomerAttribute ca
         INNER JOIN Models.Attribute a ON ca.AttributeId = a.AttributeId
         WHERE a.AttributeCode = 'DriversLicenseNumber') as dn
             ON c.EntityId = dn.CustomerId
  WHERE LOWER(FirstName) LIKE '%bob%'
    AND LOWER(LastName) LIKE '%smith%'
    AND a.EntityAddressTypeId = 0 ) c
WHERE RowNum = 1
ORDER BY UpdateDate DESC

And here's the relevant part of the execution plan:

Notice the large disparity between the estimated and actual number of rows.

If I remove the 2 LOWER calls from the inner WHERE clause and change nothing else, the execution plan steps are identical, but the cost of the steps are different. The same bits of the execution plan:

I cleared the plan cache between executions. Is this a bug?


texts: New percona xtradb node shuts itself down shortly after launch

texts:
We currently have a 3 node percona xtradb cluster running on premises. We are trying to add a 4th, remote node ( Obviously split-brain is a concern in this situation but it is only temporary while we get the new node running ).

The node starts fine and completes the SST, but then the wsrep_local_recv_queue continues to grow in size until the server crashes with an Error: semaphore wait has lasted > 600 seconds

I have tried setting wsrep_desync=ON which helps by not putting the rest of the cluster into flow control mode but the queue still continues to grow on the new node. From what I've read, it should be applying the queries despite being in desync mode.

I have also tried setting wsrep_provider_options="evs.send_window=512;evs.user_send_window=512" but that didn't appear to make any noticeable difference in this case.

Is there something I'm missing here? I haven't been able to get much information out of the logs but I'm not sure what to look for.


texts: MongoDB aggregate performance compared to Postgres

texts:
I have collection with about 700,000 documents. These documents represent locations in a hex grid. There are only 500 different locations (ll_16k).  The aggregation of the 700,000 documents into 500 documents takes about 2 seconds.

  { $group:
    { _id: "$properties.ll_16k",
      total:
        { $sum: "$properties.price" },
      count:
        { $sum: 1 }
    }
  }

The same action in Postgres is flying... This query performs routinely under 500ms.

SELECT
  ll_16k,
  count(ll_16k),
  sum(price)
FROM ppd_2015_perf
GROUP BY ll_16k;

I am just getting started with MongoDB. Am I doing something wrong here or is this the expected performance difference?

This is a test subset. The actual dataset will be 20,000,000 documents/records in size and expected to grow about 1,000,000 records per year.


texts: Difference of write authorization on file system with mySQL

texts:
On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this:

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE 'key01_en.html'

By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default.

Now, I want the file to be saved in my user let say /home/userone/dump_html folder

If I say this (I launch the mysql command from the shell, and logged as "userone":

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

It makes an error that the process does not have enough rights to write there.

But now still from the mysql> prompt, if I make:

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE '/tmp/key01_en.html'
;

-- and then
system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html
;

It works without any issue.

It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why?
(The discussion started on this point from this answer on SO)


texts: Script file execution through Power shell

texts:
We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell.

Question: is following activity possible through power shell ?

Get the list of selected databases.
Run a script file which may contain DDL and DML scripts on that list.
And results should be saved in text file.

I found this link but this is for all DBs of server but we need execute script on filtered DBs set.


texts: Handling expiration in a course registration or ticketing system

texts:
I want to restructure the database for a web application I inherited for handling summer camp registrations, but my question is relevant to any event registration or ticketing system.

My question is about how to best handle the need for registrations to expire if unpaid for X amount of time.

Currently the database design is something like this (some columns omitted for brevity):

PK = primary key
FK = foreign key

sale_header (
    PK id
    FK account_id
    payment_status
    total
    payment_method
    payment_time
)

sale_item (
    PK (sale_header_id, camper_id)
    price
)

class_registration (
    PK (camper_id, class_id)
    time_added
    registered (boolean)
)

When payment is completed, the registered flag is set to true. There is a dump script that runs regularly to clear out unpaid registrations by checking for registrations where `class_registration.registered = 0 AND sale_header.payment_status='not paid'" and for which the configured time allowed until expiration has elapsed.

There are currently some bugs related to this, and anyway it seems to me to be a somewhat strange design given that the registered flag is only set to true when payment is completed, so the payment_status column seems to be unnecessary; it seems like it would be much cleaner to have the sale_header table only contain transactions that were actually completed. Also, even if I kept this approach, it seems it would be better if there were a FK linking sale_item and class_registration.

I've been researching potential database designs for this scenario and one thought I had was to add price info to the class_registration table and get rid of the sale_item table, since the system is only for selling class registrations, nothing else:

sale (
    PK id
    FK account_id
    total
    payment_method
    payment_time
)

class_registration (
    PK (camper_id, class_id)
    FK sale_id
    price
    time_added
)

In this design, the FK sale_id would initially be null while the parent was still choosing classes for their camper(s), so finding expired, unpaid registrations would be done by querying for registrations where sale_id is NULL, and which had exceeded the expiration time.

Another option I thought of, inspired by this example (scroll down to "Data Modeling in the Sample Application"), would be to have a separate class_availability table, which might be good for querying performance when determining available capacities:

class_registration (
    FK camper_id
    FK class_id
    FK sale_id
    price
    time_added
)

class_availability (
    FK class_id
    FK camper_id
    FK registration_id
)

Finally, I considered what it would look like if I kept the sale_item table, although I'm not sure what would be the advantage of doing so:

class_registration (
    PK (camper_id, class_id)
    FK sale_item_id
    time_added
)

sale_item (
    PK sale_item_id
    FK sale_header_id
    price
)

So my specific concerns are:

What are the pros and cons of these approaches?
Should expired registrations stay in the database and just be excluded when querying, or actually be deleted?
A requirement to keep in mind is that it should be possible to change the configured expiration time in a live system (in case of bugs, which has happened before with this system), so having a column like expiration_time in the database isn't an option; it needs to be calculated based on time_added.
(Bonus :) Feel free to skip this one and focus on the above questions.) In my new proposed designs (or some other design), how could I handle the possible need for admins to be able to extend the expiration time for a particular camper in case there was some issue with payment that needed to be worked out, and their registration should be held in the meantime?


texts:
We want to update the condition of a virtual column and to minimize error posibilites we want to extract the exact function working today to add new parameters to take into account.

I've firstly test with desc tablename but doesn't work, only shows column types.


texts: How to change the Postgres hostname in pgadmin 4?

texts:
By default, the database hostname is localhost after the Postgres has been installed.
Can the hostname be changed to something else like localhost\\abc?
If yes, how can I do it in Windows/pgadmin?
Thanks


texts: 'Non-unique column' error when creating a third table using SELECT INTO and FULL JOIN

texts:
I'm attempting to create a new table from two existing tables, using the following code:

SELECT * INTO CustAndOrders from SalesLT.Customer
FULL JOIN
SalesLT.SalesOrderHeader
ON Customer.CustomerID=SalesOrderHeader.CustomerID;

However, I'm getting the error:

Column names in each table must be unique. Column name 'CustomerID' in table 'CustAndOrders' is specified more than once.

My intention is to create a third table with all the fields from each of the joined tables. Obviously 'CustomerID' appears twice, that's what I am joining on. And if I remove the INTO part and execute as a SELECT statement, it works as expected.


texts:
First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career.  One of the things I remember (maybe incorrectly) is that when designing the ERD that you take the real world into account as it serves as the context for your design.

I have a situation where we have the concept of a customer, who can have two and only two phone numbers.  A customer can have zero to many addresses as well, and each address can have only two phone numbers (and a flag indicating if the number is a mobile number).  The numbers on an address would only be used to contact someone at that particular address, while the numbers for the customer are used to contact the customer (who's address may not be in the system).

The design I came up with was to have a Phone1 Phone2 as columns on the customer table as well as on the address table.  I've had others suggest that this isn't a good design and that I should have created a PhoneNumber table instead (I'm not sure how they were suggesting I relate the numbers to the other records).

Certainly I can see that being valid as well, but I either still need to have Phone1Id and Phone2Id in the customer and address table, or have something in the Phone table that tells me which record owns the number.  The issue I have though is that of course makes the appliction logic more complex since I now need to add, remove or update the record in the phone table instead of just blanking or nulling the value in the respective tables.

Is my design also acceptable?  Is one or the other preferable?  Or are both just as valid?


texts: mysqlslap running very slow on a specific computer

texts:
I've recently discovered the tool mysqlslap and have run some simple tests with it. I currently have three test environments (2 Win 7, 1 Linux Ubuntu) where I can run these tests. However, running a no-op test on one of the Win 7 machines takes approximately 2 seconds. Running a similar query from for example MySQL Workbench takes no time, just as it should. This must have something to do with the configuration of my machine or MySQL (5.5).

Running a more heavy query takes longer time on the already slow machine, which is at least a little bit logical.

Does anybody have a clue why this happens?

Any help appreciated!


texts: Is there a need to index the short string code for a faster query?

texts:
We have a large number of tables for setup forms.
In each form, there is a need to fill in the Code and the Description.

The Code consists of a varchar(10) datatype.

Now, the thing is, every time a user creates new data or updates the data, there is a need to check whether the Code conflicts with the others or not.

In LINQ, this code checking is inevitable:

string ExistingCode = "ITEM01";
int ExistingKey = 1;

var recordWithConflictedCode = (from a in db.MyTable
                               where a.Code == ExistingCode &&
                               a.Key != ExistingKey).ToList();

if(recordWithConflictedCode.Count >= 1)
    return BadRequest("Duplicated Code found");

Now, since this involves a string comparison on every process, I think it makes sense to index the column Code to make the query faster.

Is this a common practice for you guys?


texts:
Was wondering what would be the most efficient way to get data from today, does converting the date something like

WHERE CONVERT(CHAR(8),[DateTimeColumn],112) = CONVERT(CHAR(8),GETDATE(),112)

Not slow down the process as SQL essentially has to do a RBAR compare? Any thoughts or suggestions?

Please note that the column is datetime and it contains record insert date and time


texts: SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with "ReportWrongProviderType" error

texts:
We've recently upgraded to SQL Server 2012 (from SQL Server 2008 R2) on Windows Server 2012 (from Windows Server 2008 R2), and a SQL Server Job Agent job that was previously running without error is now failing.

The job has a Powershell job step:

$ErrorActionPreference = 'Stop'
$DateStamp = Get-Date -format "yyyyMMdd"

$BizUnit = 'SHB'

# Target File directory
$BaseDir = '\\MyFileServer\MyDirectory'

$Query = "EXEC dbo.usp_MyQuery '$BizUnit'"
$TargetFile = "MyTargetFile_" + $BizUnit + "_$DateStamp.xml"
$TargetDir = (Join-Path -Path $BaseDir -ChildPath $BizUnit)

$Query | Out-File -FilePath (Join-Path -Path $TargetDir -ChildPath $TargetFile) -Force -Encoding "utf8"

When the job is run, it fails with this error message:

  A job step received an error at line 14 in a PowerShell script. The
  corresponding line is '$Query | Out-File -FilePath (Join-Path -Path
  $TargetDir -ChildPath $TargetFile) -Force -Encoding "utf8"'. Correct
  the script and reschedule the job. The error information returned by
  PowerShell is: 'Cannot perform operation because operation
  "ReportWrongProviderType" is invalid. Remove operation
  "ReportWrongProviderType", or investigate why it is not valid.

I can't find any references to "ReportWrongProviderType" when I search the interwebz. Any ideas on what is wrong with the Out-File call?