texts: Database structure for object with dynamic attributes

texts:
I am developing a proof of concept for a personalized email sending web application (like MailChimp, but with premade dynamic templates). The general structure is that I have my templates (different email templates that the user can choose from), the email object that the user is creating (from/to/subject), and his list of recipients with different fields (firstname/lastname/email address/etc). The web application will be multi-tenant so we need to have a flexible structure. My problem is the following:
Depending on the template the user chooses, the recipients will need more or less properties (home address/insurance type/promo code/gender/etc). One template can require only the basic recipient information and another one could require 50 different attributes. I will have a table Templates, a table PersonalizedFields with the TemplateID in it, so I can know which fields are required for my recipient datasource.
I am hesitating for the structure of the recipient object/table. At first I thought about E-A-V, but after reading about it, I am not so sure it is the best structure. I will be using Entity framework to interact with the database and one list of recipients can have 50k record with 0 to 50 (fringe cases) additional properties. The structure would be like this:

Another structure I am considering is putting the additional properties in a XML column, which will be convenient because the email composition tool I am planning on using takes XML but I will still need to process it:

Or maybe a third structure I am not aware of?
The original data will be read from an XLS(X)/CSV file and then written to the database, and later on read from the database to be formatted in XML strings.
What would be the best data structure?
The technology used for the proof of concept and the future project will be C#, ASP.NET MVC for the web application, ASP.NET MVC API for the service that will do the heavy lifting of the processing, and SQL Server 2008 for the database. Entity Framework will be used to interact with it.


texts:
Currently one of our new applications is connecting to an Oracle 11g instance over 30,000 daily (at every user request) via their application server, using the same account. (it will be significantly more when its in production) We are trying to convincing the developers to use a connection pool instead of connect/disconnect. However, I want to make sure, I first understand all the overheads associated with creating a connection.

I know the large volume of connections create a lot of overhead, and a lot of process will have to be spawned. Which results in connect being a fairly expensive operation.

However, what steps are exactly happening with a connect that creates the overhead?


texts: Unable to grant privileges with admin account

texts:
I'm running MariaDB version 10.4.8 on Amazon RDS. Once the server is up and running, I need to create some databases, users and grant privileges to those users.
Creating the databases and users works well, but when I try to grant privileges as follows:
GRANT ALL PRIVILEGES ON coredata.* to 'coredata'@'localhost';

I get the following error:
ERROR 1044 (42000) at line 13: Access denied for user 'dbadmin'@'%' to database 'coredata'

If I'm reading the error correctly, the user dbadmin doesn't have the privilege to grant privileges, but dbadmin is the master user account.
I've read a number of other threads that suggested restarting the DB with the skip-grant-tables option, but as far as I can tell, there is no way for me to do that with RDS and it would make automating the process very challenging.
I assume there's a simple solution I'm overlooking. Thank you.
Update: I've connected to the database using SSL, thinking this was part of the issue, but the issue persists. I can create and drop the database, I just can't grant privileges to it. I can, however grant privileges to 'test' which was created by default.


texts: How can I insert a record into a table that depends on a foreign key linking to a previous table?

texts:
Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a subroutine to insert all of this data into an Access database. What I'm struggling with is working out how to use SQL to INSERT all of the data.

There are 20 tables, and the main table is called Entry, which all the other tables link back to. To give a simplified example of the data structure, there is also a Keyword table, which links back to the Entry. And there is the Keyword_Priority table, which links back to Keyword. Both relationships are one-to-many, as below:

My question

I can use SQL INSERT for the Entry table to create one record. But how would I then create a record for the Keyword table, referring to the primary key of the Entry record I've just created? And how would I then create a record for Keyword_Priority, referring to the Keyword record? And so on.

This is all going to be done through VB.NET - I can't use Forms because there are 180,000+ entries to input. Is there any way to store a variable in SQL that I can refer to later?


texts: DB architecture: What if a table is part of another table

texts:
My SQL Server database has the initial table named Lead. This table has all kinds of info about a lead person. It has 3 foreign key columns: IndustryId, BusinessTypeId and ReferenceId. I have a problem in the architecture concerning the tables Lead and Reference.

This is the script of the table Lead:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lead](
    [LeadId] [int] IDENTITY(1,1) NOT NULL,
    [LeadName] [nvarchar](100) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
    [Phone] [nvarchar](50) NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [DOB] [date] NULL,
    [IndustryID] [int] NOT NULL,
    [BusinessTypeID] [int] NOT NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_Lead_IsDeleted]  DEFAULT ((0)),
    [ReferenceId] [int] NOT NULL,
    [IsLead] [bit] NOT NULL,
    [Email] [nvarchar](100) NOT NULL,
    [Website] [nvarchar](100) NOT NULL,
    [RepresentativeName] [nvarchar](100) NOT NULL CONSTRAINT [DF_Lead_RepresentativeName]  DEFAULT (''),
    [IsClosed] [bit] NOT NULL CONSTRAINT [DF_Lead_IsClosed]  DEFAULT ((0)),
    [feedback] [bit] NOT NULL CONSTRAINT [DF_Lead_feedback]  DEFAULT ((0)),
    [feedbackMemo] [nvarchar](100) NULL,
 CONSTRAINT [PK_Lead] PRIMARY KEY CLUSTERED
(
    [LeadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Lead]  WITH CHECK ADD  CONSTRAINT [FK_Lead_LeadIndustry] FOREIGN KEY([IndustryID])
REFERENCES [dbo].[Industry] ([IndustryId])
GO

ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadIndustry]
GO

ALTER TABLE [dbo].[Lead]  WITH CHECK ADD  CONSTRAINT [FK_Lead_LeadReference] FOREIGN KEY([ReferenceId])
REFERENCES [dbo].[Reference] ([ReferenceId])
GO

ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadReference]
GO

ALTER TABLE [dbo].[Lead]  WITH CHECK ADD  CONSTRAINT [FK_Lead_LeadType] FOREIGN KEY([BusinessTypeID])
REFERENCES [dbo].[BusinessType] ([BusinessTypeId])
GO

ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadType]
GO

And this is the script for the table in question Reference:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Reference](
    [ReferenceId] [int] IDENTITY(1,1) NOT NULL,
    [ReferenceName] [nvarchar](100) NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [DOB] [date] NULL,
    [Memo] [nvarchar](max) NOT NULL,
    [IsDeleted] [bit] NOT NULL,
    [Phone] [nvarchar](100) NOT NULL,
    [DateTimeAdded] [datetime] NOT NULL,
    [AddedByUserId] [int] NOT NULL,
    [DateTimeUpdated] [datetime] NULL,
    [UpdatedByUserId] [int] NOT NULL,
 CONSTRAINT [PK_Reference] PRIMARY KEY CLUSTERED
(
    [ReferenceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The problem is that each Lead has a Reference, but the Reference may be a Lead. In the UI while adding a new Lead, user is asked to set its Reference, but he may choose an existing Lead to be the one who referenced the new Lead, or he may choose a normal Reference.

So now I'm not sure, what is the best way to set those tables? Should I merge them in a single table Lead and when it's a simple Reference I keep the unneeded columns as Null, or keep both tables and add a new column to Reference table "LeadId" without making it a foreign key? Or maybe another method.
Please advise.


texts: Haproxy + Mysql Lost connection to MySQL server during query

texts:
I have setup a master-slave replication and added a haproxy before them.

#HA-Proxy version 1.4.24

global
    log /dev/log    local0
    log /dev/log    local1 notice
    chroot /var/lib/haproxy
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode    http
    option  httplog
    option  dontlognull
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
    errorfile 400 /etc/haproxy/errors/400.http
    errorfile 403 /etc/haproxy/errors/403.http
    errorfile 408 /etc/haproxy/errors/408.http
    errorfile 500 /etc/haproxy/errors/500.http
    errorfile 502 /etc/haproxy/errors/502.http
    errorfile 503 /etc/haproxy/errors/503.http
    errorfile 504 /etc/haproxy/errors/504.http

frontend ft_readonly_mysql
    mode tcp
    timeout client 50000
    option clitcpka
    bind *:3308
    default_backend bk_readonly_mysql

backend bk_readonly_mysql
    mode tcp
    option srvtcpka
    timeout server 50000
    option mysql-check user haproxy_check
    server mysql1 127.0.0.1:3306 check
    server mysql2 172.16.19.250:3306 check

If my pymysql client connect to mysql via 3306 directly, it works well. If connect to mysql via haproxy 3308 port, it will throw OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') every several minutes.

I doubted it was due to haproxy config so that I have added clitcpka, srvtcpka, timeout client, timeout server. But it still throws Lost connection.

Update 0:

mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+


texts: What do you call a relationship between two entities without a foreign key?

texts:
Let's say I have two tables that are related in that one table contains a column that is a key to the other table.  In other words, it would be a 1:1 or 1:* depending on constraints.

Let's call it an Customer/Orders relationship to give it some context.

However, let's say the requirement is that there be no referential integrity because the table needs to be archived without affecting the rest of the system.  In our case, they want to archive the customers, but leave the orders in the system.

Ignoring the fact we now have dangling references, which are just a fact of life when you need to prune systems in this way.  What would you call this kind of "loose" relationship where there is no actual FK?

How might you illustrate this in an ERD without implying there is a FK?  But still wishing to show that there is a relationship?

I realize that the proper method might be to use an FK anyways, and null the reference when archiving the customer, but that adds extra complexity they don't want to deal with.  Further, they don't want to update the orders table after it has been finalized.  They want to be able to find that customer number and go back in the archive and find the customer if need be.


texts: Selecting distinct rows from two tables, using multiple conditions

texts:
I'm trying to figure out a query.  I have an offices and meetings table in my DB.  Offices can have multiple meetings (office ID is a FK to the offices table).  Some meetings allow lunch, some do not.  I'm trying to find a list of all offices with meetings in January that allowed lunch, which also had meetings in February that did NOT allow lunch.

My sanitized schema looks something like this:

+---------------+   +---------------+
| meetings      |   | offices       |
+---------------+   +---------------+
| id            |   | id            |
| meeting_date  |   | name          |
| allowed_lunch |   | address_id    |
| office_id     |   | phone         |
+---------------+   +---------------+

I want a list with office id's, names, phone numbers, as well as some information from other tables I need to join to, such as street address, zip, state, etc.  So far I've only been able to come up with a clunky way of doing this; I use two queries.

The first query gets a distinct list of office ID's, using a subquery:

select distinct offices.id from offices
join meetings on offices.id = meetings.office_id
where offices.id in (
  select
    distinct offices.id
  from offices
  join meetings on meetings.office_id = offices.id
  where
    DATE(meeting_date) < '2020-01-31' and DATE(meeting_date) >= '2020-01-01' and allowed_lunch = 1
)
and DATE(meeting_date) < '2020-02-28' and DATE(meeting_date) >= '2020-02-01' and allowed_lunch = 0;

I then manually take that list of office id's and look them up again in a separate query to pull the additional information I need from other tables.  So something like this:

SELECT office.name, office.phone, ..., address.zip, address.state
FROM offices
JOIN addresses on offices.address_id = addresses.id
WHERE office.id in (
... big list from first query ...
);

What I need is a distinct list of offices, which satisfy the two conditions listed at the top.
 Is there a better way to go about doing this?  Preferably within a single query?

(I could take the first query and stick it under the WHERE clause in the second query.  I had performance issues doing this though.  The first query takes about 10 seconds, and the second query is pretty quick, but when I combine them in an additional subquery, it becomes very slow.  Plus it seems a rather messy way to handle it.)


texts:
I'm running a Galera cluster with MariaDB 10.

One of my nodes reports 100% usage disk for the /tmp table.

   /dev/mapper/vg0-LV--Tmp      1,8G    1,7G  228K 100% /tmp

I can't see files in /tmp with the ls command, but lsof reports:

mysqld     1236            mysql   14u      REG              254,5           0         17 /tmp/ibrpksHg (deleted)
mysqld     1236            mysql   15u      REG              254,5           0         19 /tmp/iboJmq1H (deleted)
mysqld     1236            mysql   16u      REG              254,5           0         20 /tmp/ibJLXol9 (deleted)
mysqld     1236            mysql   17u      REG              254,5           0         21 /tmp/ibKM4Kq5 (deleted)
mysqld     1236            mysql   21u      REG              254,5           0         22 /tmp/ibFyx8KA (deleted)

If I restart the node, the space is released, but the issue comes back after some time.

I don't understand what's happening here.

Any idea?


texts: How can I grant permissions on tables created in the future to members of a particular role?

texts:
I would like to create a role which will be granted to several individual users. This role should have full permissions on all tables, sequences, functions, etc. in a specified schema. When new tables, etc. are created in that schema in the future, I do not want to modify permissions - members of this role should by default have permissions for these newly created objects. How can I do this?

Currently, I am able to grant permissions on existing tables, but if I drop and re-create them I get the error permission denied for relation my_table.

Here is my role definition script:

create role my_role;
grant all on database my_database to my_role;
grant all privileges on schema public to my_role;
grant all privileges on all tables in schema public to my_role;
grant all privileges on all sequences in schema public to my_role;
grant all privileges on all functions in schema public to my_role;

alter default privileges in schema public
    grant all privileges on tables to my_role;
alter default privileges in schema public
    grant all privileges on sequences to my_role;
alter default privileges in schema public
    grant all privileges on functions to my_role;

grant my_role to user1;
grant my_role to user2;

If user1 creates a table, I'd like user2  (and other members of my_role) to have permissions on it, but that is currently not the case.

I am new to Postgres (coming from MySQL) - thanks in advance for any assistance, and my apologies if I am wildly misunderstanding these concepts somehow.


texts:
With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005?

To illustrate the problem, I make an example:

A tag_bundle consists of one or more than one tags.
  A unique tag combination can map to a unique tag_bundle, vice versa.

 tag_bundle                   tag            tag_bundle_relation
 +---------------+        +--------+      +---------------+--------+
 | tag_bundle_id |        | tag_id |      | tag_bundle_id | tag_id |
 +---------------+        +--------+      +---------------+--------+
 |       1       |        | 100    |      |       1       |  100   |
 +---------------+        +--------+      +---------------+--------+
 |       2       |        | 101    |      |       1       |  101   |
 +---------------+        +--------+      +---------------+--------+
                          | 102    |      |       2       |  101   |
                          +--------+      +---------------+--------+
                                          |       2       |  102   |
                                          +---------------+--------+

There can't be another tag_bundle having exactly the same combination from tag 100 and tag 101.
There can't be another tag_bundle having exactly the same combination from tag 101 and tag 102.

How can I ensure such unique constraint when executing SQL "concurrently"!!
that is, to prevent concurrently adding two bundles with exactly the same tag combination

Adding a simple unique constraint on any table does not work,
Is there any solution other than Trigger or explicit lock.

I come to only this simple way: make tag combination into string, and let it be a unique column.

tag_bundle  (unique on tags)         tag            tag_bundle_relation
 +---------------+-----------+      +--------+      +---------------+--------+
 | tag_bundle_id |  tags     |      | tag_id |      | tag_bundle_id | tag_id |
 +---------------+-----------+      +--------+      +---------------+--------+
 |       1       | "100,101" |      | 101    |      |       1       |  101   |
 +---------------+-----------+      +--------+      +---------------+--------+
                                    | 100    |      |       1       |  100   |
                                    +--------+      +---------------+--------+

but it seems not a good way :(

Is there no other solution but with triggers?


texts: I have a query which is slow when i run from SSMS and from application.I am trying to tune it.I took the execution plan and on checking I can see cost of Clustured Index Seeks on a table is 85%.That table has only two columns and one column is set as primary key.So doing Clustered Index Seek on the table is good i guess.Should i be concerned on that cost of the operation?


texts: Install and Configure Oracle 11g using c# program

texts:
I need to create a windows application program using c# which will allow me to install oracle client 11g and also configure ODBC automatically with just one click, as my customers have limitations in technology and hence the installation and configuration is very difficult.

Is it possible and also if you could help with some code reference?


texts:
When I do select query on single table then I get output rows ordered by primary key which is perfect, following query:

select * from recondata where importid=131;

but when I'm joining this table to another table output of row is random, ReconStatus contains only statusId (Primary key) and Status and in ReconData, dataID is primary key and there are no other key. I'm using following query:

select dataid,coalesce(clientPortfolioname,portfolioName) as portfolioName,
                            Status, Message, PositionId, PositionIdType,    PositionName, ClientMarketValue,
                            ClientCurrency, ClientMarketValueBase, MarketValueBase, MarketValueLocal,
                            MVDiff, SourceInstrumentType, Weight, WeightByClient, ImportType,AssetType,
                            AssetName,ExtraInfo
                            from
                            ReconData rd inner join ReconStatus rs on rd.StatusId = rs.statusid
                            where
                            ImportId=131;

Output is also not based on primary key of second table.
Can someone explain on what basis MS SQL is ordering output rows ?


texts: How to migrate InnoDB tables with 10s of partitions from the data directory onto another drive on the same server

texts:
I am trying to move some of the older partitions from larger tables located at the main data directory to another drive/mount point on the same server. I want all the partitions/tables to be accessible and live after moving.
Based on some of the research I have found online, are the following steps safe to execute:? (on a high level)

flush tables table for export
Move the /var/lib/mysql/database/table .ibd and .cfg files to an alternate location, i.e. ~/
Unlock the MySQL tables
show create table

drop table

Create table table with the DATA DIRECTORY = '/path' option
Alter table table discard tablespace
copy all those copied .ibd and .cfg files from the ~/ directory to the /path/ directory
Alter table table import tablespace

My innodb tables are partitioned monthly and the sizes are pretty big for each .ibd file which represents each partition for the table.

MySQL version = 5.7, hosted on Ubuntu 12.04.


texts: How to enforce that one row in a parent table MUST be associated with at least a row in a child table

texts:
I have a one to many relationship enforced via FK, classic example order and order_item, whereby a fk on the order_item to the order table enforces that one item belongs to one order, and one order CAN have 1 or more item. I'd need to change the "CAN" to "MUST", basically any order must at least have one item associated with it.
What would be the best way to enforce this.
Many thanks


texts: mysqld_safe not run on RHEL7, while it runs on RHEL6 when starting mariadb

texts:
I see this strange behavior when Mariadb is run on RHEL7 and RHEL6.

When I start the mariadb server on RHEL6 via service mysql start, I see mysqld_safe starts along with mysqld.

[user@divinedragon]# service mysql start
Starting mysql (via systemctl):                            [  OK  ]

[user@divinedragon]# ps aux | grep mysql
root      2398  0.0  0.0 115372  1712 ?        S    09:26   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/divinedragon.pid
mysql     2472  2.8  3.4 798208 134576 ?       Sl   09:26   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/divinedragon.err --pid-file=/var/lib/mysql/divinedragon.pid
root      2503  0.0  0.0 112644   956 pts/0    S+   09:27   0:00 grep --color=auto mysql

However, when I do the same thing on RHEL7, I don't see any mysqld_safe process coming up. Here is the ouput from RHEL 7.

[user@divinedragon ~]$ sudo service mysql start
Starting mysql (via systemctl):                            [  OK  ]

[user@divinedragon ~]$ ps aux | grep mysql
mysql     2515  2.6  6.0 774616 114412 ?       Ssl  08:51   0:00 /usr/sbin/mysqld
user      2548  0.0  0.0 112644   952 pts/0    R+   08:51   0:00 grep --color=auto mysql

Can somebody shed some light in here?


texts: How much disk space needed for 100GB of DBs?

texts:
Hard to explain in the title, but I'm planning a migration from SQL Server 2000 databases to SQL Server 2008 R2.  The databases are under 100GB total space, but this is only with the Simple recovery model.  I'd like to migrate, then change to full recovery model.

So, my question is: how much disk space will I need?  Obviously I know there is some amount of opinion here, but what are the common ways of calculating this?


texts: Quick way to validate two tables against each other

texts:
We're doing an ETL process.  When all is said and done there are a bunch of tables that should be identical.  What is the quickest way to verify that those tables (on two different servers) are in fact identical.  I'm talking both schema and data.

Can I do a hash on the table it's self like I would be able to on an individual file or filegroup - to compare one to the other.  We have Red-Gate data compare but since the tables in question contain millions of rows each I'd like something a little more performant.

One approach that intrigues me is this creative use of the union statement.  But, I'd like to explore the hash idea a little further if possible.

POST ANSWER UPDATE

For any future vistors... here is the exact approach I ended up taking.  It worked so well we're doing it on every table in each database.  Thanks to answers below for pointing me in the right direction.

CREATE PROCEDURE [dbo].[usp_DatabaseValidation]
    @TableName varchar(50)

AS
BEGIN

    SET NOCOUNT ON;

    -- parameter = if no table name was passed do them all, otherwise just check the one

    -- create a temp table that lists all tables in target database

    CREATE TABLE #ChkSumTargetTables ([fullname] varchar(250), [name] varchar(50), chksum int);
    INSERT INTO #ChkSumTargetTables ([fullname], [name], [chksum])
        SELECT DISTINCT
            '[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM MyDatabase.sys.tables T
            INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id
        WHERE
            T.name like IsNull(@TableName,'%');

    -- create a temp table that lists all tables in source database

    CREATE TABLE #ChkSumSourceTables ([fullname] varchar(250), [name] varchar(50), chksum int)
    INSERT INTO #ChkSumSourceTables ([fullname], [name], [chksum])
        SELECT DISTINCT
            '[MyLinkedServer].[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM [MyLinkedServer].[MyDatabase].sys.tables T
            INNER JOIN [MyLinkedServer].[MyDatabase].sys.schemas S ON
            T.schema_id = S.schema_id
        WHERE
            T.name like IsNull(@TableName,'%');;

    -- build a dynamic sql statement to populate temp tables with the checksums of each table

    DECLARE @TargetStmt VARCHAR(MAX)
    SELECT  @TargetStmt = COALESCE(@TargetStmt + ';', '')
            + 'UPDATE #ChkSumTargetTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + T.FullName + ') WHERE [name] = ''' + T.Name + ''''
    FROM    #ChkSumTargetTables T

    SELECT  @TargetStmt

    DECLARE @SourceStmt VARCHAR(MAX)
    SELECT  @SourceStmt = COALESCE(@SourceStmt + ';', '')
            + 'UPDATE #ChkSumSourceTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + S.FullName + ') WHERE [name] = ''' + S.Name + ''''
    FROM    #ChkSumSourceTables S

    -- execute dynamic statements - populate temp tables with checksums

    EXEC (@TargetStmt);
    EXEC (@SourceStmt);

    --compare the two databases to find any checksums that are different

    SELECT  TT.FullName AS [TABLES WHOSE CHECKSUM DOES NOT MATCH]
    FROM #ChkSumTargetTables TT
    LEFT JOIN #ChkSumSourceTables ST ON TT.Name = ST.Name
    WHERE IsNull(ST.chksum,0) <> IsNull(TT.chksum,0)

    --drop the temp tables from the tempdb

    DROP TABLE #ChkSumTargetTables;
    DROP TABLE #ChkSumSourceTables;

END


texts: How can I disable the fade in and fade out effects in pgAdmin 4 when opening windows?

texts:
Is there any way to disable the fade in and fade out effects in pgAdmin 4 when opening windows?

Example of fade in and fade out:


texts: Can SQL Server avoid subqueries or joins if other where clauses return enough results?

texts:
Consider this as example:

SELECT TOP 1 FROM customers
WHERE firstname LIKE 'John%'
   OR id IN (SELECT id_customer
             FROM customer_emails
             WHERE email like 'john.doe@%' )

Does query optimizer manage to prioritize the first clause, avoiding to do the subquery if the simpler clause is true for at least 1 record (from using Top 1)?

I am mainly asking this because the above query can be rewritten as Join, which technically would be faster according to Join vs. sub-query. However if the subquery is avoided, the above example is clearly superior to a Join one in situations where the first clause is likely to be true.


texts: Display unique column when not all values are distinct

texts:
Name Status Contact EpisodeNumber
AA   C      E       1
AA   C      F       1
AA   C      G       1
AA   D      H       1
BB   D      I       2
CC   C      J       3
DD   C      K       4

If D is present on any of the 'status' column of the given Names i don't want to see them. each 'contact' is different which is fine, i need to make sure the EpisodeNumber is the same. What happens is everytime "Status" is changed it creates a new row for that name with the contact that changed it so i can't do a simple select * where status != 'D' because it will show me the other statuses for that given episodenumber. We are running SQL server 2008 R2 with sql management studio version 10.50.6560.0 and report builder 3.0

Name Status Contact EpisodeNumber
CC   C      J       3
DD   C      K       4


texts: Sqlserver 2019 Partitioning based on Data Classification?

texts:
Now that sqlserver 2019 has been around for a while, this is a re-ask of a prior question that was not really answered in the way I had hoped:

Is it possible to partition a database based on the Sqlserver 2019 data classification feature?

Explanation/Detail:
I would like to column partition the entire database schema so that our "high sensitivity" data is processed and stored separately, perhaps in another instance in a different location.  This to improve security of our data, understanding that there would be a performance cost.

I would like to maintain the current schema for application compatibility purposes, just store the data differently for security reasons.


texts: Upgrade from MariaDB 10.3 to MariaDB 10.4 with Galera Cluster - mysql.global_priv table

texts:
I have 3-node MariaDB 10.3 Galera Cluster (Ubuntu 14.04) and I want to upgrade it to MariaDB 10.4 Galera Cluster (Ubuntu 18.04).

According to https://mariadb.com/kb/en/library/upgrading-from-mariadb-103-to-mariadb-104-with-galera-cluster/ rolling upgrade is possible (obviously, I need as little downtime as possible).

I did rolling upgrade on dev cluster - I added new server (MariaDB 10.4) to cluster and removed one old server (MariaDB 10.3). Repeated until whole cluster was running MariaDB 10.4. Everything works and looks just fine.

The thing is, in MariaDB 10.4 new authentication mysql table was introduced. According to https://mariadb.com/kb/en/library/authentication-from-mariadb-104/ users should now be stored in mysql.global_priv. Because I did rolling upgrade, myql database was replicated from old nodes to new nodes, so now I dont have mysql.global_priv table in cluster. Users are still in mysql.users table.

Another issue is, when I did new standalone installation of MariaDB 10.4, there was OS admin user mysql@localhost with these privileges

GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION

This user is not present in upgraded cluster (obviously same issue, mysql table was replicated from old nodes), but I cannot create this admin user to get rid of debian-sys-maint@localhost

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION;
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

So my question is, how can I make clean rolling upgrade of MariaDB Galera Cluster from MariaDB 10.3. to MariaDB 10.4 with all new features introduces in MariaDB 10.4? Any help would be appreciated.


texts: Is MySQL Community Server 5.7.11 compatible with SLES 11 SP3?

texts:
According to the MySQL download page for the MySQL Community Server there are only binaries available for SLES 12.

My question is if the MySQL Community Server 5.7.11 [and higher] is compatible with SLES 11 SP3 when I compile MySQL on my own?


texts:
We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual row-counts are good. No table or index scans. But it still didn't come in under 30 seconds. It was close.

I rewrote the query by decomposing into smaller steps with temp tables and no more than 2 joins per step. It screams in at 5 seconds now. All good.

My specific question is this a normal/common approach to solving this kind of problem? Why do I have to help out the optimizer in this way?


texts: What are the advantages and disadvantages of Third normal form and Boyce-Codd normal form?

texts: I know that 3NF makes a database more efficient and stores data according to relevance, which is more logical, but are there other advantages? and are there any disadvantages?


texts:
I know that basically while creating a database, the model system DB is copied so based on the pictures below:

Why the initial size is 3MB for PRIMARY file and 1MB for LOG If the documentation clearly says that It should be 8MB for versions above 2016 and 1MB for anything lower (I'm on this category as I'm using 2014)
I understand that a log file could grow to 2TB maximum but why the Model database says unlimited and the STACK database says limited to 2TB?
Assuming that the actual default size is indeed 3MB and 1MB, why on the disk, I see 2240 KB and 560 KB?


texts: Is there a way to do a textual search through all stored procedures for a table name?

texts:
I've just inherited a database with a number of tables and stored procedures.  I use Microsoft SQL Server Management Studio.

One, or possibly more, stored procedures update a specific table.  I'd like to do a textual search through all the stored procedures to look for the procedure that updates the table.

If the raw SQL was in our code, I'd just grep the code base, but with stored procedures I'm not sure how to search through all the stored procedures.

I'd also like to rename a few things and do a find/replace throughout the stored procedures.

Is it possible to search and replace in stored procedures using Microsoft SQL Server Management Studio?


texts: How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow?

texts:
In my specific case, I have just deployed my side project to AWS and using AWS MySQL Tier 2 Micro instance, which is within AWS' Free Tier;

https://aws.amazon.com/ec2/instance-types/t2/

It has: 20 GB storage, 1 GB memory, 1 vCPUs, 6 CPU Credits

So now I have a bunch of metrics I can follow in AWS;

As a developer, I know the basics of using SQL databases, but I know very little as a DBA (Database Administrator) and hosting them, but I want to learn the basics.

So my question is not a AWS specific question (its relevant for Google Cloud Platform and Microsoft Azure as well), but its a more more broad question;

As a DBA, how do you know when its time to upgrade a RDBMS infrastructure?
What key metrics should you follow and why in order to know when should try and upgrade your infrastructure?
What are the basics any new DBA should look at?


texts: How to Recover My database From Old WampServer?

texts:
Previously I am using wampserver2 with "mysql5.6.17" version.I have many database in that version.
I uninstall the wampserver and install new latest version wampserver3.1.0_x64.

Before uninstalling my old wamp i copied all files to another drive. so i have some safe backup.

Now i want all database from old wampserver.

Is there any ways to do that..


texts:
I manage a server that serves several web applications that use PostgreSQL as a data store.  Each web application has a separate role and database, and each database is stored exclusively on a dedicated tablespace on a different logical file system.

What I would like to be able to do is unmount a web app's file system without affecting postgres - if I do that at the moment CHECKPOINTs fail to run, so CREATE DATABASE, VACUUM, and other commands fail.

Is there a way to "deactivate" a database so that PostgreSQL won't try to checkpoint or autovacuum it?


texts: How do I grant execute permission to an Oracle database user?

texts:
I'm trying to call a Java function from Oracle database that does the following:

Process p = Runtime.getRuntime().exec("...");

When I try to run it, I get the following error:

ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
<<ALL FILES>> execute) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL
FILES>>', 'execute' )

How do I grant this permission?

UPDATE: When I run the following in SQL*Plus (as sysdba):

dbms_java.grant_permission( 'SCOTT',
                            'SYS:java.io.FilePermission',
                            '<<ALL FILES>>',
                            'execute' );

I get the error:

SP2-0734: unknown command beginning "/dbms_java..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "'SYS:java...." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "'<<ALL FIL..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.


texts: Where can I read about the behavior of the postgress casting operators?

texts:
I want to do something like this:

alter table foos
alter column bar type bool using bar::boolean;

bar is currently a text column. Some experimentation shows that postgres will do things like convert a string "t" to boolean true. In my case this is great, but I'd like to read the full behavior before using this. I can't seem to find where this documentation is.


texts: Psycopg2 - PGBouncer - Postgresql > Server does not support SSL but SSL was required

texts:
I've setup my Django application to use SSL while connecting to the Postgresql database via pgbouncer. I'm using Psycopg2 library.

I've done this before successfully, so I just did the same steps again.

But! After installing certificates to both servers and clients and making the installations, when I tried to run my application, I've got the error:

django.db.utils.OperationalError: server does not support SSL, but SSL was required

When I entered the command

psql --set=sslmode=verify-full -h DBHOST -p DBPORT -U USERNAME DBNAME

I can successfully connect to database by entering my password

But when I write the command like this;

psql "sslmode=verify-full host=DBHOST dbname=DBNAME port=DBPORT user=USERNAME"

or when I entered the code from python shell

import psycopg2
conn = psycopg2.connect(database='DBNAME', user='DBUSER', password='DBPASS', host='DBHOST', port='DBPORT', sslmode='verify-full')

I got the stack trace;

/virtual_environment_path/local/lib/python2.7/site-packages/psycopg2/__init__.pyc in connect(dsn, connection_factory, cursor_factory, **kwargs)
    128
    129     dsn = _ext.make_dsn(dsn, **kwargs)
--> 130     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
    131     if cursor_factory is not None:
    132         conn.cursor_factory = cursor_factory

OperationalError: server does not support SSL, but SSL was required

What may be the problem? I've compared the installated packages between previous installation which is succesful, versions of packages, certificates, file permissions etc. But I'm stuck in this issue.

Thank you very much for your help.


texts: Want to know details and meaning about the trace of SQL Server

texts:
I referred to this article: sp_trace_setevent (Transact-SQL).

And trace my SQL Server on production machine for months.

EventId of most of the traces were: 10，12，14，17

The MSDN says:

10 RPC:Completed
12 SQL:BatchCompleted
14 Audit Login
17 ExistingConnection

Please tell me what do they mean in the performance perspective. Which ones should I keep an eye on and examine in detail. Or am I missing something. Thanks!


texts: PostgreSQL Reduce Writes on Flash Storage (TBW)

texts:
I'm currently a startup and using non server grade NVMEs/SSD on my database server.
My motherboard , CPU , DDR (ECC) etc are server grade except for the flash part.
I'm looking for ways to reduce the amount of writes on my Postgresql Database to preserve the lifespan of Flash Drives hopefully to reduce the failure rate.
My requirements

Write speed (Low Priority)
High Read speed & low latency (High Priority)
Low Writes (High Priority)

Questions

What kind of storage format do you recommend for a non-raided ubuntu system - ext4/xfs
What are the configuration settings I should look into
Any form of settings would improve usage of ram over flash drives
Should I reduce maintenance / vacuum frequency.
Block Size for Disk.
Separate disk for WAL ?


texts: Grouping over multiple data dimensions (pivot)

texts:
I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise.

The two dimensions are following:

Datetime values (per Day)
ServerId (per Server)

I think I have to group Datetime values by a time part (e.g. by minutes, or per hour) and retrieve a list of occourences of events per server, but I dont know how to write the query? Do I need a having by clause?

I've already written the create statements, the structure is as following:

CREATE TABLE [dbo].[events](
    [guidEventId] [uniqueidentifier] PRIMARY KEY,
    [dtStart] [datetime2](7) NOT NULL,
    [nDirection] [int] NOT NULL,
    [nServerId] [int] NOT NULL
)

INSERT INTO events
    ([guidEventId],[dtStart],[nDirection],[nServerId])
VALUES
    (newId(),'2013-08-01 00:01:00','0','1'),
    (newId(),'2013-08-01 00:02:00','1','1'),
    (newId(),'2013-08-01 00:03:00','0','1'),
    (newId(),'2013-08-01 00:04:00','1','2'),
    (newId(),'2013-08-02 00:01:00','0','1'),
    (newId(),'2013-08-02 00:02:00','1','2'),
    (newId(),'2013-08-02 00:03:00','0','2'),
    (newId(),'2013-08-02 00:04:00','1','2');

http://sqlfiddle.com/#!6/25e74

The end table should look something like that (daywise):

DateTime     | Server1 | Server2 |     Sum |
--------------------------------------------
2013-08-01   |       3 |       1 |       4 |
2013-08-02   |       1 |       3 |       4 |


texts: Database structure user service connection

texts:
I am working on a SQL database structure for an application that connects users to different API's of services. So one user can connect to for example 2 API's and the data of one service will be used inside the other service via the user.

I have drawn a simple database structure with a user, service and connection table. My idea is to make the connection by storing the user_id and service_id in the connection table together with the credentials.

The problem is that some services use OAuth (so based on access tokens) and some use a username and password. I don't know how to arrange my connections table to provide both options.


texts: How to get the sequence name for a serial column

texts:
As far as I can tell, this query should show the expression for the new value of a serial column:

select d.adsrc
from (
 SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname
 FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c
 WHERE a.attnum > 0
 AND NOT a.attisdropped
 AND a.attrelid = c.oid
 and c.relkind not in ('S','v')
 and c.relnamespace = n.oid
 and n.nspname not in ('pg_catalog','pg_toast','information_schema')
) x
left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum
where x.relname = 'table_name' and x.nspname = 'schema_name' and x.attname = 'column_name'
;

It works most of the time, but if I rename the sequence, the new name is not reflected in the result of the query - it continues to show the original name of the sequence. Any ideas why?


texts: Do we have to run auto update statistics after index rebuild?

texts:
I want to rebuild one of my indexes because fragmentation has reached 97 percent.

I want to know if after I rebuild my index, do I have to update the statistics as well? My auto update statistics feature is on.

And is it necessary to do the auto update?


texts: Substitute names for IDs stored in two columns referencing the same table

texts:
I have 2 tables:

table1:

id  no  name
1   501  john
2   354  sam
3   201  anderson

and
table2:

status  type      createdby  approvedby
1       recharge     2         1
1       topup        3         1

I need a query that would replace the createdby and approvedby references with corresponding names from table1, like this:

type           status           createdby         approvedby
recharge        1                 sam                john
topup           1                anderson            john

This is my attempted query:

SELECT `table2`.*, `tabl1`.`no`
FROM `table2`
JOIN `tabl1` ON `table2`.`createdby` =`table1`.`id`
JOIN `table1` `myTable` ON `table2`.`approvedby` =`orgTable`.`id`

But I am getting only createdby name, not approvedby name.

How can I solve this?


texts: SQL Delete duplicate rows based on column

texts:
I need to remove the duplication of rows based on column to have unique data in this column, additionally I need another condition when the value equal specific information.

In my example I want to filter by TagName to avoid duplication plus I need the value of Alarmstate = 'UNACK_ALM'.

Please refer to the pictures for more clarification, I did it using Excel but I need to write it in SQL view.

Thanks for coopearation.


texts: Does an index where the last column is unique and already indexed make sense?

texts:
I found a unique index on (RetailerID, ID) in our database.  The ID column is the primary key so it's already uniquely indexed.  I can't see how an index on (RetailerID, ID) could help when looking up an individual record by RetailerID and ID (or just ID).

I suspect it might be helpful when querying all the IDs under a particular RetailerID, since it covers such a query.  Is that true?

But then why not just make the index on RetailerID include ID?  Perhaps the unique index on (RetailerID, ID) covers the query in the same way but performs better as a result of being a unique index.

As I said, there's a unique primary clustering key on ID alone.  There's also a unique index on (RetailerID, ID). Since ID is already unique, querying where RetailerID = x and ID = y shouldn't be any more efficient than simply querying where ID = y.  (ID) is unique and (RetailerID, ID) is unique, so ID is associated with at most one RetailerID.  Therefore, if a predicate has ID, then including RetailerID in the predicate is redundant.  The clustered index would always be faster.

An index on (RetailerID, ID) could be helpful for a query where the predicate is just on RetailerID and the select is on just ID, because it would cover the query, but for queries where ID is in the predicate, RetailerID in the predicate is redundant, and since it has no other included columns, it would be less-efficient than the clustered index when selecting any column other than ID.  That's why I'm not seeing the point of this index, given the predicates used (which always involve ID).


texts: SSMS Connect to remote SQL instance with Windows Authentication

texts:
I have a remote SQL instance

(Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)

I can connect to the instance fine locally on the box through SSMS using both Windows and SQL Authentication. I can connect to the instance fine remotely using SQL Authentication. However I am unable to connect to the box using Windows Authentication from anywhere on the domain.

I get the following error message which seems to mean it can be several things but nothing which describes my specific issue:

The same error happens when using the server name and IP address. I do not have this issue with any other SQL instances on the domain, just this one. The fact I can connect using SQL Authentication suggests it is not network or firewall related and is an issue with the configuration on this specific VM / instance. Nothing gets logged to the SQL error logs.

Any ideas?


texts: Need to get identify all tables and columns in a SQL Query

texts:
I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or disappear completely.

Is there any product of script that anyone can recommend that will allow me to do this, taking into consideration that whatever program/script is used will need to take into account alias as well as ensuring that it can differentiate between blocks of select code or union.

I have hundreds of queries being used for reports. I need to go through each query and identify tables and columns that are being used within each query.


texts: Data Compression Enhancement in SQL Server 2012

texts:
Is there any specific enhancements in SQL Server 2012 for Data Compression Feature?

Last time I checked SQL Server 2008 R2 introduced support for Unicode characters so columns with NCHAR data types (excepts CLOBS and BLOBS) can now take advantage of compression technology in SQL Server.


texts: MySQL FROM_UNIXTIME() gives wrong date in certain time zones?

texts:
The timestamps 1382836533 and 1382832933 correspond to the same wall clock time 2013-10-27 02:15:33 in cet timezone (because of daylight savings time).

Therefore the conversion with FROM_UNIXTIME is lossy.

mysql> set time_zone = 'CET';
Query OK, 0 rows affected (0.00 sec)

mysql> select unix_timestamp(from_unixtime(1382836533));
+-------------------------------------------+
| unix_timestamp(from_unixtime(1382836533)) |
+-------------------------------------------+
|                                1382832933 |
+-------------------------------------------+
1 row in set (0.00 sec)

I have a field of type timestamp in my database. If I want to update it, with my time zone settings, I end up writing the wrong value to the database:

mysql> update test set begins_at = from_unixtime(1382836533);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select unix_timestamp(begins_at) from test;
+---------------------------+
| unix_timestamp(begins_at) |
+---------------------------+
|                1382832933 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

The problem does not occur when I manually set the connection time zone to `UTC.

It does occur when I manually set the connection time zone to CET.

But it does not occur when I do nothing. Which is strange because the server and client have both CET as their default time zone.

So the question is: Why does the problem not occur when I don't manually set the timezone? And what precise combinations of server and connection time zones produce this problems and which do not?

The log below reflects the behaviour.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CET    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(from_unixtime(1382836533));
+-------------------------------------------+
| unix_timestamp(from_unixtime(1382836533)) |
+-------------------------------------------+
|                                1382836533 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382836533);
+---------------------------+
| from_unixtime(1382836533) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382832933);
+---------------------------+
| from_unixtime(1382832933) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'CET';
Query OK, 0 rows affected (0.00 sec)

mysql> select unix_timestamp(from_unixtime(1382836533));
+-------------------------------------------+
| unix_timestamp(from_unixtime(1382836533)) |
+-------------------------------------------+
|                                1382832933 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382832933);
+---------------------------+
| from_unixtime(1382832933) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1382836533);
+---------------------------+
| from_unixtime(1382836533) |
+---------------------------+
| 2013-10-27 02:15:33       |
+---------------------------+
1 row in set (0.00 sec)

mysql>


texts: Transaction Time Tables in MSSQL and Constraints

texts:
I am trying to implement transaction time tables on a MSSQL Server 2016 following the book by Richard Snodgrass (Developing Time-Oriented Database Applications in SQL).
A table with a sequenced primary key through a trigger could be implemented like this:
CREATE TABLE test_table (
a [tinyint] NOT NULL,
b [date] NOT NULL,
c [float] NOT NULL,
[tt_start] [datetime2](7) NOT NULL,
[tt_end] [datetime2](7) NOT NULL)
GO

CREATE TRIGGER Seq_Primary_Key_tt ON test_table FOR INSERT, UPDATE, DELETE AS
  BEGIN
    IF (( EXISTS ( SELECT * FROM test_table AS b1
    WHERE 1 < (SELECT COUNT(b2.a) FROM
        test_table AS b2
        WHERE b1.a = b2.a AND
        b1.b = b2.b AND
        b1.tt_start < b2.tt_end AND b2.tt_start < b1.tt_end) )))
    BEGIN
        RAISERROR('Transaction violates sequenced constraint', 1, 2)
        ROLLBACK TRANSACTION
    END
 END

 GO

The trigger is pretty slow on larger tables. Therefore, using multiple INSERTs leads to long waiting times. Furthermore, many operations like updates require more than one operation (e.g. INSERT, then UPDATE). This doesn't work with this trigger, because the trigger would start already after the INSERT and fail, although the operation would perfectly work out if the trigger would start after the UPDATE.
That is why Snodgrass writes that the constraints/assertions (or here the trigger) have to be DEFERRABLE INITIALLY DEFERRED. In that way, the constraints would be checked after all operations have finished. This would at the same time also increase the performance of multiple INSERTs.
However, as far as I see, MSSQL Server doesn't implement DEFERRABLE INITIALLY DEFERRED. How could a similar constraint or trigger be then implemented? Or could the "temporal tables" feature be a replacement for what I am trying to do?


texts:
I have a local XAMPP server with mysql, trying to connect to remote mysql database. I have added the allowed host (I am using a dynamic DNS server as the IP is dynamic) to the remote mysql server. However, when I try and connect from my local server it says access denied. I try with the IP and it works fine. So i went to etc/my.conf and the skip name resolve option isn't there either, does anyone have any idea what's going on?

Thanks!


texts: When designing the topology of a data model, does the database implementation matter?

texts:
This application seems to be keeping data design separate from data storage. But I am curious if this even makes sense?
If you click the code button you should be able to see clearly what question I am trying to ask. It outputs code for different databases, but shares the same model. What about the difference between SQL and NOSQL databases? https://www.schesign.com/o/examples/example_online_store/master/class/billinginformation


texts:
Is there a way to pass tnsname.ora while launching the oracle sql developer Client via command prompt?

Or is there a way to overwrite the usr directory location where the connection.xml folder is present to another location and pass it via command prompt. The sql developer. Exe - help gives certain parameters that can be passed, conf file and over write directory value.. Can any one let me know how to use those parameter for passing different connection depending on what is being passed in conf file or connection.xml.


texts:
I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application database, I know that the referential integrity is in check.

What is the impact of primary key and foreign key relationships in this copy? If I carefully set up the indexing I require, is there any benefit to foreign keys?  Any detriment?


texts: How to select a subset from a query result

texts:
I have a query which calculates the Time Difference and Distance Between the Next and Previous Row:

SELECT  * FROM (
            SELECT
                iVehicleMonitoringId AS PreviousId,
                LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentId,
                iGeofenceId AS GeofenceId,
                eEventCode as 'EventCode',
                iAssetId AS AssetId,
                LEAD(iAssetId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousAsset,
                sDigitalInputValue AS PreviousIgnition,
                LEAD(sDigitalInputValue, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentIgnition,
                dtUTCDateTime AS StartDate,
                LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS EndDate,
                DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
                sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

                FROM VehicleMonitoringLog
                    WHERE dtUTCDateTime > @utcStartDate AND dtUTCDateTime < @utcEndDate
                            AND iAssetId in (Select iAssetID From #tblAssets)
        ) AS dt
        ORDER BY dt.AssetId, dt.StartDate

Which returns the following result

Which is fine - Now I need to calculate the Total Distance and Time in between the selected section (Highlighted in Blue).

So basically it is the  Total Distance and Total Time From eEventCode = 6 To eEventCode = 7. There can be more than one set with start event = 6 and end event = 7.

SQL Fiddle

How do I perform this operation?

EDIT
Okay this is the new fiddle with a dataset: http://sqlfiddle.com/#!6/40a9e1

Now this data has two datasets where start eventcode = 6 and end eventcode = 7

Now if I execute this query - to get distance between two adjacent points:

SELECT
    iVehicleMonitoringId AS PreviousId,
    LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentId,
    iGeofenceId AS GeofenceId,
    iAssetId,
    dtUTCDateTime,
    eEventCode as 'EventCode',
    DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
    sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

    FROM VehicleMonitoringLog
        WHERE
        iAssetId = 6024 AND
        dtUTCDateTime > N'2016-01-26 04:44:00' AND dtUTCDateTime <  N'2016-01-26 12:39:45'
        Order by dtUTCDateTime

The result should add all the Distances columns for each set.


texts: Sort Warnings, finding out the query behind them

texts:
I'm troubleshooting a SQL Server 2012 that has started timing out as of a few weeks ago.

The server is filled with thousands of sort warnings, at a rate of about 15 warnings per minute.

To get the sort warnings I used the following query:

DECLARE @log NVARCHAR(1000)

SELECT
    @log = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) + '\log.trc'
FROM
    sys.traces
WHERE
    id = 1

SELECT
   g.TransactionID, c.name as category, e.name as event,
   g.hostname as servername, g.databasename as using,
   g.sessionloginname as login, g.starttime, g.textdata as query
FROM
   ::fn_trace_gettable(@log, 0) as g
inner join
   sys.trace_events e on g.eventclass = e.trace_event_id
inner join
   sys.trace_categories as c on e.category_id = c.category_id
where
   c.type not in ('0')
order by
   starttime desc

However the textdata column is always empty.

I'm almost certain that the sort warnings are originating from old stored procedures. However I can't find out the exact cause, as there are over 100 stored procedures being triggered every minute, how would I go about finding the query that's behind the warnings?

Update:

SELECT
    p.name, p.object_id, p.create_date, p.modify_date,
    s.last_elapsed_time, s.last_worker_time,
    (s.last_elapsed_time - s.last_worker_time) as time_difference,
    s.execution_count, last_execution_time
FROM
    SYS.dm_exec_procedure_stats as s
INNER JOIN
    sys.procedures as p on p.object_id = s.object_id
WHERE
    database_id = 11 AND s.last_worker_time < s.last_elapsed_time
ORDER BY
    time_difference desc

Shows me that a few procedures (3) have last_elapsed_time of 200'000+ and worker time much lower. One stands out as being executed 15'000+ times, while having 400'000+ longer elapsed_time than worker_time. And this query does indeed sort on a rather large table.

Could someone tell me if what I'm looking at is a possible cause of the sort warnings, or if I'm going in circles.


texts: MySQL Load Balancing what do i need Replication or Cluster?

texts:
So My MySQL database server is reaching it's limits with the number of queries selects, updates, inserts etc it can concurrently take resulting in high CPU usage more than what a single machine can take any more.

I have been seeking out my options and have come to the conclusion I should go about either setting up a Replication or Cluster.

But as with anything everyone's needs are different, So I will inform you about my current application. Joomla CMS, InnoDB database handler. (Pretty straight forward.)

Now the way I can think of it being the easiest setup for my needs is something like this. I know replication and clustering probably does not work like this but I ask this question to be corrected and to be pointed in the direction of the setup I should pursue.

I drew a lovely little bit of art in Windows Paint in order to show you what I am after.

I want to load balance all forms/types of queries across multiple machines and have them all somehow sharing the same database across a private network like Network Sharing mapped hard drives what ever.

That way instead of a single machine's CPU being utilized to select update and insert it will load balance between multiple utilizing multiple servers resources in order to return the query results.

Thanks in advanced for reading this much appreciated for any help that can be provided on what I should go with and type of setup I require.


texts:
Is there any DBMS that can be installed as an OS instead of as an app on a OS?
I wasn't able to find any.

Later edit, just to clarify:
I'm used to installing a DBMS (MySQL, MSSQL) as a server(ice) onto an OS(Windows, Ubuntu, etc.). I was trying to find out if there are some DBMS' that do NOT require an underlying OS and are 'installed' as the OS directly on hardware. I was asking here because googling didn't help.
Thanks for the answers I got, it seems that the answer is close to 'NO'


texts: MariaDB throws error with documented 'alter user' ... 'password expire' option

texts:
Normally I use mysql-community on RHEL7, but today I installed MariaDB 10.3.20 on Ubuntu 19. In selectively cloning a database from the RHEL-mysql cluster to the Ubuntu-MariaDB cluster, I used Percona's pt-show-grants tool. It created for me lines such as:

CREATE USER IF NOT EXISTS 'xyz'@'%';
ALTER USER 'xyz'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*157C19AA12C99769CC33EEB0B46XF5A29C000000' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON `xyz`.* TO 'zzzz'@'%';

It fails on both password expire and account unlock. Only by removing both these mariadb not fail with:

ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PASSWORD EXPIRE NEVER ACCOUNT UNLOCK' at line 1


texts: Scalable way to manage aggregated statistics over time in hierarchy?

texts:
I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value can also be easily calculated at any given time, thanks to the closure table.

Let's say our parent node has 4 children, each of which have 4 children, each of which have 4, etc to the point of 5 layers of depth.

Each node has a node score table in which we update their score with new entries that can change a couple times per day, and a calculated column called family score that is the sum of its sum of that value for all descendent nodes.

So here's my challenge: In this example, the very top parent has 1363 children whose changing scores influence it's own family score calculated value, along with the family score of every parent between that one and the one who's node score just changed.

I need a way to be able to map any node's family score over time. I've come up with two solution, neither of which I think are likely to scale well:

Add a family score table, and store a new static value for each ancestor node any time a node's score changes. Thus each write is multiplied by the depth of the node whose score is changing. The structure could easily get to 150-300 in depth.
When loading the data for the yearly score chart, first get an array of timestamps for each node score row within the date range specified and with the given parent specified, and then for each timestamp, find the latest node score row for every descendent and sum the values.

Option 1 seems like its less likely to break, but I'm guessing there's a best practice for this type of thing? Searching hasn't produce anything that quite addresses this use case. Any insights would be massively appreciated!!


texts: Non-RAC single instance with ASM startup query

texts:
How to start a non-rac single instance with ASM file system. As in the case of RAC after reboot of server, the ASM and CRS resources startup automatically.. but with non-RAC but with ASM storage, what is the right way to start the database?

Is there anything specific that should be given attention to start the database?

Start the ASM instance and mount all the diskgroups and then start the database?

Correct me/add if i'm wrong.

Server- RHEL Database - 12c


texts: Which TLS protocol and ciphers are supported by SQL Server 2005?

texts: What is the latest TLS protocol (TLS 1.0 or TLS 1.1) to be used in SQL Server 2005? Also what ciphers are supported?


texts:
i have a TSQL code to move DATA from ORACLE to SQL SERVER. It is scheduled to run daily. But, for some reason it is not working as expected like taking a long time to finish execution and getting error like ERROR 3902 COMMIT TRANSACTION request has no Corresponding BEGIN TRANSACTION. The code was tested on the local server and was working properly.
I have some doubts regarding certain parts of the code.

SOME CODE REGARDING MOVEMENT OF DATE FROM ORACLE TO SQL SERVER
GO
    begin transaction
    commit;
    go

My doubts are as follows:

I have read that GO is not a TSQL command. So, does it work in a scheduler?
Begin TRANSACTION should start a transaction explicitly. But there is no code after BEGIN TRANSACTION. So how does it understand the above code should be executed?

I have solved the issue. Issue was with commit statement. After, every insert statement i added a commit(similar to pl/sql). I added begin transaction before every commit and it worked. I think commit works differently in pl/sql and TSQL.


texts:
I'm practicing for enrich my knowledge, and i would like to have more databases than actually i have (2).

Where i can found free databases for practice? If it's possible, big databases.

I'm traing to looking for some kind of comunity where people upload their databases for practice, If exist.


texts: How to create a clustered index on a schema bound view with the least amount of contention caused?

texts:
I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 million rows or so. I want to create a clustered index on the view that incorporates the primary key and a few other fields from the table.

The table is highly transactional, and can't really be locked for more than a minute at a time. Will the creation of the index on the view cause contention directly on the underlying table during the creation? If so, is it possible for me to create the index on my schema bound view in either an incremental manner (i.e. somehow pause part of the way through creation, to allow the table to catch up to it's transaction backlog, then resume creation). Also I have to be careful with overall server contention as well, I've seen heavy operations against one table like this slow up my whole server before.

Essentially I'm looking for the most efficient way to create the index on my view to minimize contention.


texts: Permissions issue in Docker SQL Server 2017 while restoring certificate

texts:
Docker SQL Server 2017 container @latest. Using master database.

The error I am facing is the following:

[S00019][15208] The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

The closest thing I have found to this exact question is this issue on Stackoverflow. However the answer doesn't work for me. This question has a similar answer.

I have also tried the instructions here, and here.

So going through the parts of the error:

I have recreated the files twice, so I don't think it's the "invalid" part. And it's obviously not the "does not exist" part (if I put in the wrong password, it tells me it's the wrong password).
I have backed up and restored the SMK and Master Key without issue, so I don't think it's the permissions issue. The files have the exact same permissions.

I can't get the certificate to restore no matter what I try. I have searched the GitHub issues to no avail so I don't think it's a bug. I must be doing something wrong.

Relevant code:

--on Prod
BACKUP CERTIFICATE sqlserver_backup_cert
TO FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.cer'
WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.key',
    ENCRYPTION BY PASSWORD = 'foobar'
    )

--on Test
CREATE CERTIFICATE sqlserver_backup_cert
FROM FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.crt'
WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.key',
    DECRYPTION BY PASSWORD = 'foobar'
    )

It's noteworthy that /var/opt/mssql/certs is a Docker volume. However I have also tried creating my own directory inside the container and using docker cp. No change.


texts:
I want to insert same data in db_two.tab_emp table automatic by trigger or anything.

create database db_one;
create table db_one.tab_emp(id int, name text);

create database db_two;
create table db_two.tab_emp(id int, name text);

insert into db_one.tab_emp values(101,'king');

Is there any possible way to insert or update foreign database table if I insert or update own database table at a time?


texts:
I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance.

How does NHibernate handle execution plan?
Are execution plan reused in NHibernate?


texts:
What is the difference between the search in search and replace, (the replace function) and full text search (tsvector) in Postgres? Or, put another way, can tsvector be used in search and replace (and would there be any advantage in doing so)?

UPDATE

There must be something about trying to formulate a question that helps clarify it in your own mind. Yes, there is ts_rewrite. The docs say: "In essence this operation is a tsquery-specific version of substring replacement".

2nd Update

Ok, now I do have a question. In the docs examples, the replacement comes after the original string, even if the part being replaced was at the front of the original string:

ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)
'b' & ( 'foo' | 'bar' )

So instead of foo | bar taking a's position, it follows b's position, which almost always changes the meaning of the text and might make it nonsense. Are there options to control this?


texts: 1 time shrink of abused databse or restore from backup

texts:
I have a database that I have inherited which has been left for a long time to grow and grow. 95% of this growth is caused by 1 log table used by our application. The database is 120GB and in full recovery mode. The compressed backups are around 10GB. The server is 2008. The log file is 3.5GB

After asking I have found that I can remove all data from this log table over 3 months old. This gives me 100GB of free space. I have also written an agent job to keep the log table at this level.

This means I will have roughly 100GB sat in this file forever (as long as no other table starts growing in way it hasn't previously or the agent job fails)

I would like to free some of this space up while leaving enough room for future growth which should be much less then it was before my agent job was put in.

So I don't want to shrink and then defrag indexes as this may require a lot of downtime and is almost always a bad idea.

Is dropping the database and restoring the 10GB backup an option? I'm not even sure if this will give me the desired result which is a smaller database with something like 2GB free in it's file for future growth.

Thanks


texts: Double Entry Bookkeeping Database Design - Views vs Derived Tables vs Python Processing

texts:
I'm starting to learn a bit about databases, and I've been reading for while about what is the best way to design a database for a simple accounting system with double entry.

For those who are not familiar with accounting, I have a General Journal, and I need to extract a few reports out of it - basically a bunch of SUM IFS (sum this account if this date).

I currently have my journal table designed as a one-liner, but I found this post and this answer that got me thinking.

Some parts of the answer that I would like to understand a bit better:

  "...design a data structure that can be the numerical repository for
  multiple journals..."

What would be an example of a numerical repository for multiple
journals?

  "...In a modern RDBMS... ...the General
  Ledger, and even the specialized subledgers, can become Indexed Views
  on the Journal, completely eliminating the requirement to code a
  Posting Process (the step where a Journal transaction is locked and
  has its account totals transcribed to the various ledgers)..."

How exactly should I deal with the "Indexed Views" approach?
Create an indexed view for each Income Statement/Balance Sheet account?
Maybe materialize a Pivot Table with all the acounts of the Income
Statement/Balance Sheet in the columns (don't think that would be
reasonable since I have about 150 accounts total)?

My query to extract a stock account (one that accumulates over time, such as Balance Sheet accounts):

SELECT Y, M,(@total := @total + Flow) AS TotalValue
FROM (
        SELECT year(dateacc) AS Y, month(dateacc) AS M,
            (
                SUM(IF(Credit='Account', valor, 0))-
                SUM(IF(Debit='Account', valor, 0))
            ) AS BS_Account
        FROM journal
        GROUP BY YEAR(dateacc), MONTH(dateacc)
    ) AS T,
(SELECT @total:=0) AS n;

My query to extract a flow account (such as Income Statement Accounts):

SELECT DATE_FORMAT(datecash, '%m-%y') as DATA,
    (
        SUM(IF(credit='Account', value, 0))-
        SUM(IF(debit='Account', value, 0))
    )
AS Flow
FROM journal_test
GROUP BY YEAR(datecash), MONTH(datecash)

In a nutshell:

What is the best way to design this?
View vs Materialized View vs. Derived Table
How to approach the general journal vs. subledgers dilema


texts: Update view using derived key in Database

texts:
Is it possible to update this view by specifying the MessageTech and the txtmessageID? (txtmessageID and MessageId are the primary keys from the respective base tables.)

(SELECT
    txtmessageID,
    'S' AS MessageTech,
    MessageType,
    Message,
    MessageDate,
    MessageMobile,
    MessageRead,
    MessageSender
FROM
    MessageCentre)

UNION ALL

(SELECT
    MessageId,
    'E' AS MessageTech,
    Type,
    Text,
    Date,
    Address,
    [Read],
    Sender
FROM
    MessageCentreEmail)


texts: What is the significance of root and page level fragmentation in a clustered b-tree index?

texts:
In investigating fragmentation on some of our large tables in a SQL Server 2005 database, I was using a query based on sys.dm_db_index_physical_stats. The query returns a row for each level in the b-tree for an index. In evaluating the clustered indexes, I noticed that fragmentation increased the further up the tree I went. At the leaf level, fragmentation was only 1%, but at the root level, it was 50% fragmented or greater. Here's an example:

TableName         IndexName             avg_fragmentation_in_percent   record_count
MyTableName       MyClustered_Index     91.66666667                    1,901
MyTableName       MyClustered_Index     35.77064703                    528,681
MyTableName       MyClustered_Index     1.948812233                    3,616,888

So looking at this, I was curious: is the fragmentation I am seeing at the upper levels because the pages that the index values are occupying are acting the same way that really small tables do, occupying mixed extents, or something similar to that?

If that is not the case, do I need to be concerned with those fragmentation numbers in the upper levels, or is the leaf level fragmentation the only thing that is significant here?

Thanks for your time.

EDIT 1:

Adding the query results with page counts:

TableName           IndexName           avg_fragmentation_in_percent    page_count
MyTable             MyClusteredIndex    0.2687447                       823086
MyTable             MyClusteredIndex    6.389452333                     1972
MyTable             MyClusteredIndex    90.90909091                     11


texts:
A few months back I installed SQL Server 2008R2. At the time of installation I used

1.Mixed Authentication
2.NT Authority\NetworkService as the SQL Agent user name, and the same user name for all other components also.

At that time everything worked.  Now when I start the Agent I am getting this below error.

  Windows could not start the SQL Server Agent (MSSQLSERVER) service on Local Computer.
  Error 1053: The service did not respond to the start or control request in a timely fashion.

I followed this link (http://support.microsoft.com/kb/322140).  The steps are not clear to me but I tried a few. When I run by using cmd as admin I get this error:

  The program can't start because sqlncli10.dll is missing from your computer. Try reinstalling the program to fix this problem.

Please can anyone help me with easy steps?


texts:
I have a few production SQL Server instances and I get around 100 deadlock alert emails a day (up to 100 for one server, 150 in total).

Is this a problem I should investigate? How many deadlocks are problematic and what can be the possible consequence of ignoring this issue?


texts: DB2 MQT not rewriting role playing columns

texts:
If in a MQT, if I have 2 referential constraints to the same table (role playing) , usage of one of the constraints doesnt allow the query to be optimized

Example

FACT_SALES

DIM_BILL_TO_ID --> DIM_ADDRESS

DIM_SHIP_TO_ID --> DIM_ADDRESS

SALES_AMOUNT

DIM_ADDRESS

DIM_ADDRESS_ID

COUNTRY_NAME

I create a MQT with the sql query definition as

MQT 1

SELECT COUNTRY_NAME,
       SUM(SALES_AMOUNT)
FROM
      FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_BILL_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID
GROUP BY
      COUNTRY_NAME

When a query is fired, automatically query rewrite happens and MQT is used

Lets say my definition was

MQT 2

SELECT a1.COUNTRY_NAME AS BILL_TO_NAME,
       a2.COUNTRY_NAM AS SHIP_TO_NAME,
       SUM(SALES_AMOUNT)
FROM
      FACT_SALES INNER JOIN DIM_ADDRESS a1 ON FACT_SALES.DIM_BILL_TO_ID = a1.DIM_ADDRESS_ID
      INNER JOIN DIM_ADDRESS a2 ON FACT_SALES.DIM_SHIP_TO_ID = a2.DIM_ADDRESS_ID

GROUP BY
      a1.COUNTRY_NAME,a2.COUNTRY_NAME

and the query that was fired was

SELECT COUNTRY_NAME,
       SUM(SALES_AMOUNT)
FROM
      FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_SHIP_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID
GROUP BY
      COUNTRY_NAME

The query rewrite doesn't happen... but instead if I used BILL_TO_ID in the query, then query rewrite happens...

Is there any logic where the DB2 optimizer cant understand role playing columns?


texts: Query not showing in dba_hist_active_sess_history

texts:
The application team claims there was a statement that took about 30s to execute at about a certain time.

I tried to find out what queries had been executed in during this time with the following statement:

select a.sql_id, sample_time from dba_hist_active_sess_history a, dba_hist_sqltext b
        where sample_time between to_date('20170330:13:15','yyyymmdd:hh24:mi')
        and to_date('20170330:13:25','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
        order by 2;

However, only statements concerning rman, or the cloud control agent show up and no statement that seems to be from the application.

Could it be that some queries executed do not show up in the dba_hist_active_sess_history table and where else could I look for it?


texts:
May the following my.cnf file be fine for a dedicated server with 64GB of RAM (on Debian)?

I'm running just one website on the server and on high load data insert in some table takes centuries...it seems that the DB can't handle some table lock (50Mb tables, not so much).

If it is fine, then i'll step to analyze every single query.

Any help would be truly appreciated!

[client]

port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

back_log = 50

skip-networking

max_connections = 100

max_connect_errors = 10

table_open_cache = 4096

table-definition-cache = 4096

skip-external-locking

skip-name-resolve

max_allowed_packet = 32M

binlog_cache_size = 2M

max_heap_table_size = 64M

read_buffer_size = 4M

read_rnd_buffer_size = 32M

sort_buffer_size = 16M

join_buffer_size = 8M

thread_cache_size = 20

thread_concurrency = 16

query_cache_type=1

query_cache_size = 1024M

query_cache_limit = 2M

ft_min_word_len = 4

default-storage-engine = MYISAM

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 4G

log-bin=mysql-bin

binlog_format=mixed

slow_query_log

long_query_time = 2

server-id = 1

key_buffer_size = 16G

bulk_insert_buffer_size = 256M

myisam_sort_buffer_size = 246M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 12G

innodb_data_file_path = ibdata1:10M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192


texts: How can I set a default now() timestamp in a timestamp array?

texts:
I want to have a timestamp array which tracks a series of events. The first event always happens when the row is created, so I want the array to be instantiated with an initial default value. I've tried these two things and they both result in now() being executed at the time of the statement. Is it possible for me to achieve this with a simple default value, or do I need to write a hook?

# ALTER TABLE messages ADD COLUMN testarray TIMESTAMP[] DEFAULT '{NOW()}';
ALTER TABLE
# ALTER TABLE messages ADD COLUMN testarray2 TIMESTAMP[] DEFAULT '{"NOW()"}';
ALTER TABLE

freedom_development=# \d messages
                                                 Table "public.messages"
    Column     |             Type              |                                Modifiers
---------------+-------------------------------+-------------------------------------------------------------------------
 id            | integer                       | not null default nextval('messages_id_seq'::regclass)
 testarray     | timestamp without time zone[] | default '{"2017-12-30 21:00:09.622951"}'::timestamp without time zone[]
 testarray2    | timestamp without time zone[] | default '{"2017-12-30 21:00:19.936001"}'::timestamp without time zone[]


texts:
According to this Microsoft document:

http://msdn.microsoft.com/en-us/library/ms174205.aspx

I am supposed to be able to use ctrl+K then ctrl+D to format my SQL documents in SQL Server Management Studio 2012 but when I use that combo I get the error:

  The key combination (Ctrl+K, Ctrl+D) is bound to command (Format
  Document) which is not currently available.

I am trying to make modifications to an existing SQL document that has no formatting to it at all which makes it extremely difficult to read. Does anyone know how to make the Format Document command available so I can have SQL format this code for me?


texts: How to connect external database to AppEngine?

texts:
Are there any ways (better than using UrlFetch) to connect to any external database from AppEngine?

I already know about hosted SQL databases in App Engine for Business, but they are not free and will be avialable in Q2 2011, so it's not an option right now.

I like datastore very much, but customers don't like vendor lock or have other reasons.


texts: SQLPLUS: SELECT INTO prompting for variable value

texts:
Any idea why this is prompting me for the value?  I'm calling the script via batch file:

DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
END;
/

-- NUMBER OF MEC/MVP RECORDS
SPOOL logs\MVP_MEC_UPDATED.log APPEND
PROMPT &&CLIENTID   &&precount  &&nowcount
SPOOL OFF;

Thanks!


texts:
My problem is that I want to delete a DB from our production server. It is a test DB that needed to be there for some reason, so the mdf and ldf are on LUNs that are insecure and oftentime lost connection to the server, not like our production LUNs that are OK.

The problem is that I can't delete the database. It throws:

  El sistema operativo ha devuelto el error 1117(failed to retrieve text for this error. Reason: 15105) en SQL Server durante leer en el desplazamiento 0x00000687a10000 del archivo 'T:\XXX_XXXXX_xx.mdf'

We are using SQL Server 2008 [10.0.5500.0].

It won't let me go into DB settings or read from tables. I can't manually delete the files either. I think a server restart should help. Today is Tuesday and we got a schechuled restart on Saturday 04.

Have any of you encontered this issue?


texts:
I am connecting to MongoDB hosted on EC2 instance of AWS. I am connecting through ssh command.
Upon entering the command "show collections", I am getting uncaught exceptions. Please see the sequence below. Can you let me know how can I start seeing the collections again when putting in the "Show collections" command?

  show dbs
  admin    0.031GB
  cool_db  (empty)
  local    0.031GB
  users    0.031GB
  use users
  switched to db users
  show collections
  2019-08-26T18:46:15.280+0000 E  QUERY    [js] uncaught exception: Error: listCollections failed: {
      "ok" : 0,
      "errmsg" : "no such cmd: listCollections",
      "code" : 59,
      "bad cmd" : {
          "listCollections" : 1,
          "filter" : {

    },
    "nameOnly" : true,
    "authorizedCollections" : true
}

} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DB.prototype._getCollectionInfosCommand@src/mongo/shell/db.js:834:15
DB.prototype._getCollectionNamesInternal@src/mongo/shell/db.js:906:12
DB.prototype.getCollectionNames@src/mongo/shell/db.js:915:12
shellHelper.show@src/mongo/shell/utils.js:893:9
shellHelper@src/mongo/shell/utils.js:790:15
@(shellhelp2):1:1


texts: Problems restoring backup from base backup + WAL with WAL-E

texts:
I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files.

On the master I have

wal_level = hot_standby
archive_command = 'wal-e --s3-prefix=s3://... wal-push %p'

and I've done a full backup with:

wal-e --s3-prefix s3://... backup-push <pg data>

On the backup node I have done:

PGDATA=.. initdb
wal-e backup-fetch $PGDATA LATEST

which works and I'm able to connect and select data. Nice.

So to get it to consume the latest WAL archives I added a $PGDATA/recovery.conf:

restore_command = 'wal-e --s3-prefix ... wal-fetch "%f" "%p"'
standby_mode = 'off'

When start the server I expected it to go ahead and look for WAL segments to consume before starting in non-recovery mode, but I'm getting this:

LOG:  database system was interrupted; last known up at 2015-03-21 12:20:50 CET
wal_e.operator.backup INFO     MSG: begin wal restore
        STRUCTURED: time=2015-03-21T12:16:38.820461-00 pid=23630 action=wal-fetch key=s3://.../wal_005/00000005.history.lzo prefix=...
/ seg=00000005.history state=begin
lzop: <stdin>: not a lzop file
wal_e.blobstore.s3.s3_util WARNING  MSG: could no longer locate object while performing wal restore
        DETAIL: The absolute URI that could not be located is s3://.../wal_005/00000005.history.lzo.
        HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration.
        STRUCTURED: time=2015-03-21T12:16:39.829223-00 pid=23630
wal_e.operator.backup INFO     MSG: complete wal restore
        STRUCTURED: time=2015-03-21T12:16:39.830287-00 pid=23630 action=wal-fetch key=s3://.../wal_005/00000005.history.lzo prefix=.../ seg=00000005.history state=complete
LOG:  starting archive recovery
wal_e.operator.backup INFO     MSG: begin wal restore
        STRUCTURED: time=2015-03-21T12:16:39.900355-00 pid=23638 action=wal-fetch key=s3://.../wal_005/00000005000000050000009D.lzo prefix=.../ seg=00000005000000050000009D state=begin
lzop: <stdin>: not a lzop file
wal_e.blobstore.s3.s3_util WARNING  MSG: could no longer locate object while performing wal restore
        DETAIL: The absolute URI that could not be located is s3://.../wal_005/00000005000000050000009D.lzo.
        HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration.
        STRUCTURED: time=2015-03-21T12:16:40.896215-00 pid=23638
wal_e.operator.backup INFO     MSG: complete wal restore
        STRUCTURED: time=2015-03-21T12:16:40.899320-00 pid=23638 action=wal-fetch key=s3://.../wal_005/00000005000000050000009D.lzo prefix=.../ seg=00000005000000050000009D state=complete
WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
LOG:  consistent recovery state reached at 5/9D009DA8
LOG:  record with zero length at 5/9D009DA8
LOG:  redo is not required

So the questions are;

is my approach fundamentally wrong? Is there an alternative way to get this to work?
PostgreSQL is claiming I'm having wal_level=minimal, how can I check the level of the produced WAL files? On the main server I get this when checking it's actual settings:

select * from pg_settings where name='wal_level';
-[ RECORD 1 ]------------------------------------------------
name       | wal_level
setting    | hot_standby
unit       |
category   | Write-Ahead Log / Settings
short_desc | Set the level of information written to the WAL.
extra_desc |
context    | postmaster
vartype    | enum
source     | configuration file
min_val    |
max_val    |
enumvals   | {minimal,archive,hot_standby}
boot_val   | minimal
reset_val  | hot_standby
sourcefile | /.../postgresql.conf
sourceline | 155

EDIT: adding details on archive_command on master


texts: Does Microsoft release its SQL Query Hash Algorithm..?

texts:
We use SQL Spotlight in our environment. it's pretty handy.
we use particularly the output of sys.dm_exec_requests and sys.dm_exec_query_stats.

Spotlight pulls the query plan from the plan cache for you using the hash which is nice.

Problem is, unless you're experienced with the code base, it's quite difficult to know where that query came from.

I had an idea that if I could:

Parse the codebase
Pull the SQL queries
Hash them the same way microsoft does

This way I could do quick matches of the hash to be able see where in the codebase a particular query came from.

Alternatively I'd have to do some very slow regex...


texts: How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;

texts:
I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line:

mysqldump --add-drop-table --add-locks --allow-keywords --complete-insert=TRUE --compress --create-options --databases [database] --default-character-set=utf8mb4 --disable-keys --dump-date --extended-insert=FALSE --hex-blob --lock-tables=FALSE --no-autocommit --no-data --set-charset --order-by-primary --quote-names --routines --single-transaction --skip-quick --triggers -u [user] -h [server] > dump.sql

This works well and produces the output I want, with one exception. My dump is littered with this:

/*!40101 SET character_set_client  = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection  = utf8_general_ci */;

I have read up on conditional comments, and as I understand it, this will always get executed on our clients/servers (Server Version: 5.6.46 - MySQL Community Server (GPL)). I don't understand why this is set to utf8 instead of utf8mb4, and would like it to reflect our proper charset.

None of the existing answers to this problem have helped me.

Edit: I originally had an incorrect version in my post


texts: The use of NOT logic in relation to indexes

texts:
According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:

  Neither leading wildcard characters not NOT logic allow the query optimizer to use indexes to optimize the search. For optimal performance, you should avoid using the NOT keyword and leading wildcard symbols.

So I took that to be NOT IN, NOT EXISTS etc

Now with regards to this SO question, I thought that the chosen solution by @GBN would violate the statement given above.

Apparently, it does not.

So my question is: Why?


texts:
We have a number of websites using mysql dbs. We are moving these sites to a new server. We also moving the dbs to a new server seperate server. To make this easier we have setup master slave replication.

The question is as we start moving the sites to the new server is there any problem with the using the slave db until we have moved all the sites across and can turn off the master?


texts: INSERT/SELECT slow after TRUNCATE TABLE on table with 6 over mllion rows

texts:
Seeing slowness in insert/select after truncating a table that has over 6 million rows.

I daily insert 5 to 6 millions records into a table and I was able to insert/select data without any issue for some 7 or 8 days but when the table size went above 10 GB / 30 million rows, there were a few timeout issues.

So I thought of truncating the table daily before data uploading since 1 day's data is enough for me. Now I am seeing extreme slowness after truncating. I am using sql server 2008.

do i need to update table stats? or is it some thing related to ghost rows?


texts: Why is the 'AS' table alias keyword required in Visual Studio (Database projects)

texts:
Up till now I have been working directly on SQL Server instances, and am now playing around with how best to keep databases in source control. The visual studio database project seems to be exactly what I want.

However, on trying to build a fairly small database I get several hundred SQL71561 errors. The code is this:

CREATE VIEW [dbo].[View Name] AS
SELECT
    EA.colName,
    ...
FROM
    DbName.dbo.TableName EA

And the error is this:

  SQL71561: View: [dbo].[View name] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects:
  [DbName].[dbo].[TableName].[colName] or [DbName].[dbo].[TableName].[EA]::[colName].

If I add an AS to the table alias, then the code seems to be accepted. It seems that column aliases are allowed to skip the 'AS' keyword.