texts: How to store values of different entity types in database?

texts:
I created the schema of  ref_entity and ref_entity_type:
create table ref_entity (
id serial primary key,
name varchar(255) NOT NULL,
type_id integer,
code varchar(255)

constraint FK_REF_ENTITY_TYPE FOREIGN KEY (type_id) REFERENCES REF_ENTITY_TYPE (ID)
);

create table ref_entity_type (
id serial primary key,
name varchar(255) NOT NULL,
);

In the project, I have a batch job which receives many files every day and I need to integrate them into the final tables after some transformations.
The extracted data does not correspond to the same entity_type. There are many type of entities and each type has a corresponding values.
I'm looking for a suitable solution to extend my schema and integrate the values of the different entity type to the entity in order to return them whenever I select a given entity.
Solution:
Create a table for each type of entity :
create table type_x {
id serial primary key,
value xxxx NOT NULL,
entity_type_id integer,

constraint FK_TYPE_X_ENTITY_TYPE FOREIGN KEY (entity_type_id) REFERENCES REF_ENTITY_TYPE (ID)
}

the caveat of this solution is I need to create as many tables as entity_type and I think it's not good.
I thought to create a table contains all the values of a given type in an array, however the type is not always the same (could be number, varchar, text, etc...).
Does anyone has any suggestions to better design the schema table and optimize it in order to not create as many tables as types to store the values?


texts: Postgres inplace PITR (point in time recovery) or rollback

texts:
I have a Postgres 12.x database (that is cloud hosted).  This particular database instance is a nightly copy of production.  That is used for debugging etc ...
But during the debugging process we might want to roll it back to the time that it was created.
Is there an elegant way to do this?
I imagine like some uber transaction, that I can rollback to.  Is this crazy-talk -- or will postgres continue to amaze me.


texts: Always On Availability Groups deleted when server rebooted

texts:
I encountered an issue in which after a server reboot (reboot was not done gracefully), the AGs on that server were automatically deleted. The error encountered was the following:
AlwaysOn: WSFC AG integrity check failed for AG ‘PROD_DB1’ with error 41041, severity 16, state 0.
AlwaysOn: The local replica of availability group ‘PROD_DB1’ is being removed.
The instance of SQL Server failed to validate the integrity of the availability group configuration in the Windows Server Failover Clustering (WSFC) store.
The only information that I found about this is in the below links:
https://techcommunity.microsoft.com/t5/sql-server-support/issue-replica-unexpectedly-dropped-in-availability-group/ba-p/318175
https://blog.toadworld.com/2017/03/17/sql-server-availability-group-missing-or-deleted-automatically
If anyone has ever encountered this, how can this be prevented if the servers reboots due to an issue? Do I need to maybe modify some group common properties (https://docs.microsoft.com/en-gb/previous-versions/windows/desktop/mscs/group-common-properties?redirectedfrom=MSDN) to prevent AGs from being deleted when maybe there is some sort of timeout or any other issue with the cluster DB?


texts: Trigger to loop and send incremental email based on condition

texts:
I have a requirement in Oracle apex to implement dynamic approval workflow in issue tracker.

I am trying to write a trigger for the same as below. I need to further modify it so that it runs sequentially.
i.e. When an issue is raised in related dept, it passes through multiple approval levels.

Trigger 1 sends email to first approver for approval.
When the approver logs in to approve, set p_it_issues.APPROVE_THIS='Y1', the next trigger sets p_it_issues.approved=1 to show it has passed first level.
The second trigger sends email notification to second approver as well. (all code mentioned below for reference).

But due to varying levels of approval this would means i will have to create more and more triggers.
Is there a way to combine this so that it would keep incrementing and sending approvals from p_it_people.approver='Approver 1'.to..'Ápprover n' based on approval_level per department? HR department has n approval levels (p_it_departments.approval_level=n , so it would only send it to the n approvers in p_it_people table with Approver column set as Approver 1 .. n respectively?

I need to make this more dynamic, so that in approve_this we have only Y and N.
And both Approvers with Approve 1 select Y in approve_This and then it would move to Approver 2

Looking forward to your help.


texts: How can I list database names within a mysqldump file?

texts: I have a large mysqldump and I need to create the databases before importing them one-by-one. How can I search and list only the database names using the linux or mysql terminal?


texts: Establishing a 1 to 1 relationship between tables

texts:
I have a table Nurse:

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [WardId]     [int]               NOT NULL,
 CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and a table Employee:

CREATE TABLE [dbo].[Employee](
    [Id]            [int] IDENTITY(1,1) NOT NULL,
    [Name]          [nvarchar](100)     NOT NULL,
    [StaffNumber]   [nvarchar](50)      NOT NULL,
    [Salary]        [money]             NOT NULL,
    [Address]       [nvarchar](max)     NOT NULL,
    [GenderId]      [int]               NOT NULL,
    [ContactNumber] [nvarchar](20)      NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [Id] 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]

How do I establish a 1:1 relationship between the two tables?

There is one nurse for one employee, only the Nurse table is separated from the Employee table as it has to be part of relationships, such as, just any employee, e.g. a Nurse belongs to a Ward, while a Doctor, also an employee, belongs to a Department.


texts: MYSQL Indexes when looking up on alternating sets of columns

texts:
I have a table that looks like:

CREATE TABLE `connections` (
`connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`service_id` smallint(5) unsigned DEFAULT NULL,
`parent_id` bigint(20) unsigned DEFAULT NULL,
`child_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`connection_id`),
UNIQUE KEY `primary_child` (`parent_id`,`child_id`),
UNIQUE KEY `child_primary` (`child_id`,`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I need to do lookups on child_id and get information about parent_id and vice versa.

Ie:

SELECT COUNT(`parent_id`) FROM `connections` WHERE `child_id` = 'x'

or

SELECT COUNT(`child_id`) FORM `connections` WHERE `parent_id` = 'x'

And some more advanced queries like:

SELECT DISTINCT `parent_id`
FROM `connections` `c1`
INNER JOIN `connections` `c2`
ON `c1`.`parent_id` = `c2`.`parent_id`
WHERE `c1`.`child_id` = 'x'
AND `c2`.`child_id` = 'y'

I'm looking to get a better idea of the most efficient index structure to get quick responses from all the above query examples.

It should be noted that the child/parent pairs will be unique.

EDIT: This table currently holds 40M rows, likely to be much larger in the near future.

Thanks,


texts:
Arabic language search in codeigniter error :   Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

In English language search is working

JOIN Query to use to search

SELECT *, `guest`.*, `guest`.`id` as `guestid`, `assistant`.`guest_id` as `assistantid` FROM `guest`
LEFT JOIN `assistant` ON `guest`.`id` = `assistant`.`guest_id`
WHERE   (
`guest`.`id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `company_name` LIKE '%محلل مالي%' ESCAPE '!'
OR  `position` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`country_code` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`mobile_no` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`email` LIKE '%محلل مالي%' ESCAPE '!'
OR  `nationality` LIKE '%محلل مالي%' ESCAPE '!'
OR  `category` LIKE '%محلل مالي%' ESCAPE '!'
OR  `gender` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest_type` LIKE '%محلل مالي%' ESCAPE '!'
OR  `location` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`remarks` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`id_user_master` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`guest_id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`country_code` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`mobile_no` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`email` LIKE '%محلل مالي%' ESCAPE '!'
 )
ORDER BY `guest`.`id` DESC
 LIMIT 10

in database "utf8_general_ci"


texts:
Learning indexing

I have made an index, consisting of usergroupid and birthdate

I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate

But what happens if I throw in other fields in the query?

SELECT * FROM mytable WHERE usergroupid = 54 AND (birthdate BETWEEN '1980-01-01' AND '1983-01-01') AND cityid = 100

Will the extra "AND cityid = 100" mess up the indexing, or will it be seen as something to be found quickly AFTER handling the index?


texts: Mongodb creating historical cluster replicaset

texts:
I've a use case which I'm trying to implement.. There is a replicaset(3 nodes) which is primary production cluster, I want to set up a historical cluster another replicaset(3 nodes) which replicates data from primary production replicaset.. But we periodically purge transaction data from it,  I don't want purge to replicate since historical replicaset should hold entire data for reporting..

What are the ways to handle it..?  Do we have selective replication in mongo or should I replicate oplog manually and skip deletes..

Thanks for suggestions!


texts: Differences between SQL Server 2005 and SQL Server 2008 deadlock handling

texts:
We recently upgraded a server to SQL Server 2008 R2. The server had been running SQL Server 2005. We are using

Transactional Replication
Management Data Warehouse and Data Collector on the Production server

We are starting to see deadlock errors in the application log. This did not happen in SQL 2005.

On the reporting database I can replicate a deadlock using an "ad-hoc" query with an inner join and table scan on an active table. However, I can run reporting procs that take minutes to complete with no problems.

Is sql 2008 more sensitive to deadlocks to sql 2005? I am going to start undoing some of the changes we have implemented and tested. Before I go that route I wanted to see if anyone else has noticed increased deadlocks when upgrading to 2008 R2.


texts: Write differences between varchar and nvarchar

texts:
Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that.

My question is are there any differences in how SQL Server writes to varchar columns vs. nvarchar columns? We have a number of backend procedures that I'm concerned about.

Edit:
Not sure if this helps, but the columns don't have indexes, f/k, or constraints on them.


texts: Slow MySQL Database Import with mysqldump and USE .. SOURCE

texts:
I have about 12 tables in MySQL innoDB, one of them has 11 million records in it.

I used this command to back things up:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

And this command to import things on the new server:

USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

Here is the pain I am afflicted with (the time is going up with each query!):

What can I do to speed things up? Is something wrong with my mysqldump command?


texts: How to restart MariaDB Galera cluster to change my.cnf

texts:
I have 3 dbs servers with mariadb galera cluster.

I need few my.cnf change but each time I try to just restart with mysql restart command , replication gets affected.

So can anybody let me know proper way to restart mysql without downtime.

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="web_cluster"
wsrep_cluster_address="gcomm://172.16.1.173,172.17.1.173,172.18.1.173"

# Galera Synchronization Configuration
wsrep_sst_method=rsync
#wsrep_sst_auth=cluster-user:clusterpass
#wsrep_sst_method=mariabackup

# Galera Node Configuration
wsrep_node_address="172.16.1.173"
wsrep_node_name="web-db-01"


texts: Which columns (and order) to choose for a clustered index to maximize query performance

texts:
In our database we have a table that looks more or less like the example below. In the past we always created the clustered index on the ID column on the table.

CREATE TABLE [Measurement]
(
    [ID] INT NOT NULL PRIMARY KEY NOT NULL,
    [ParameterID] INT NOT NULL,
    [Measuretime] DATETIME NOT NULL,
    [Value] FLOAT NOT NULL,

    CONSTRAINT [FK_Measurement_Parameter]
        FOREIGN KEY ([ParameterID]) REFERENCES [Parameter]([ID])
)

CREATE INDEX [IX_Measurement_Measuretime_ParameterID]
    ON [Measurement] ([Measuretime]) INCLUDE ([ParameterID]);

CREATE INDEX [IX_Measurement_ParameterID_Measuretime]
    ON [Measurement] ([ParameterID]) INCLUDE ([Measuretime]);

Our data gets written in 1-5 second intervals with successive timestamps for all Parameters.

We decided that it would probably be a better idea to create the clustered index on ParameterID and/or Measuretime as most queries are on those to columns.

Here are some example how most our queries look like:

Example A

SELECT *
FROM Measurement
WHERE ParameterID = 1
    and Measuretime between '2015-01-24' and '2015-01-25'

Example B

SELECT ParameterID, cast(Measuretime as date), avg(value)
FROM Measurement
WHERE ParameterID = 1
    and Measuretime between '2015-01-01' and '2015-02-01'
GROUP BY ParameterID, cast(Measuretime as date)

Example C

SELECT DISTINCT
    ParameterID,
    FIRST_VALUE(cast(Measuretime as date))
          OVER (PARTITION BY cast(Measuretime as date), ParameterID
                ORDER BY Measuretime ) Measuredate,
    PERCENTILE_CONT(.25)
         WITHIN GROUP(ORDER BY Value)
         OVER (PARTITION BY cast(Measuretime as date), ParameterID) as [q1],
    ...
FROM Measurement
WHERE Measuretime between '2015-01-01' and '2015-02-01'
    -- and ParameterID = 1
ORDER BY Measuretime, ParameterID

Which of those three ways that come to my mind for INDEX creation is the best suited for such a scenario?

CREATE CLUSTERED INDEX [CIX_Measurement] ON [Measurement]([Measuretime],[ParameterID]) as this is also the order data gets written, and both columns are queried.
CREATE CLUSTERED INDEX [CIX_Measurement] ON [Measurement]([ParameterID],[Measuretime]) as nearly all our queries need to filter by ParameterID in one way or another and only afterwards Measuretime.
On only one of those to and go for a normal INDEX for the other column.


texts: Safe to use the same temporary table name in parallel transactons?

texts:
Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. The name itself is not important, but I would like to safe myself the trouble of generating random names for every concurrent query that needs such intermediate table.

Temporary table is only used in different SELECT queries. It will be dropped at the end of each transaction with the ON COMMIT DROP clause.

PostgreSQL documentation states:

  Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names.

It is not clear to me if this applies to temporary tables too.


texts: sp_Blitz "Too Much Free Memory" on seldom-used SSRS instance

texts:
sp_Blitz (20160715) is reporting "Too Much Free Memory" on a seldom-used 2012 SP2 CU12 test instance (Developer Edition) that only contains the databases created and used by SSRS. The Details are:

  0GB of free memory inside SQL Server's buffer pool, which is 0GB. You
  would think lots of free memory would be good, but check out the URL
  for more information.

I modified the query to return the raw values and got Free = 587,896 and Total = 1,052,256. My altered query:

SELECT Free = cFree.cntr_value, Total = cTotal.cntr_value
FROM sys.dm_os_performance_counters cFree
INNER JOIN sys.dm_os_performance_counters cTotal ON cTotal.object_name LIKE N'%Memory Manager%'
    AND cTotal.counter_name = N'Total Server Memory (KB)                                                                                '
WHERE cFree.object_name LIKE N'%Memory Manager%'
    AND cFree.counter_name = N'Free Memory (KB)                                                                                '
    AND CAST(cTotal.cntr_value AS BIGINT) * .3 <= CAST(cFree.cntr_value AS BIGINT)
     AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%'

dbo.sp_BlitzCache (@BringThePain = 1) displays 316 rows, all apparently built-in SSRS housekeeping statements. Is it more likely that:

I have a problem as described at
https://www.brentozar.com/blitz/too-much-free-memory/, or
The WHERE clause of the sp_Blitz query needs tweaking to avoid a false positive in this circumstance


texts:
I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each type of content.

I would like to introduce a moderation system into my app. To do this, I will allow users to report/flag a piece of content. In the database, each report should link back to the original piece of content, so that I can retrieve information about the reported content as needed. However, this requires yet 4 more tables: message report, post report, thread report, and reply report. Each of these tables will be exactly the same except for a foreign key column to reference the correct piece of content (a message, post, thread, or reply).

Using this system, reports will be saved in a many-to-one fashion, meaning that 1 single piece of content can have multiple reports associated with it. To further complicate things, I also need to be able to collect data on a single piece of content. For instance, say thread with ID 23 got many reports, but a moderator comes along and marks the content as safe. I need to be able to somehow mark all the reports for thread ID 23 as 'safe'. This seems difficult to wrap my head around, and perhaps I need another table to track this kind of information.

Does anyone have any recommendations for how to improve/design a sane database schema for this scenario? I feel as though I am doing something wrong and it is making this far too complicated.


texts: Create SCD type 2 table from change log table using SQL

texts:
I have a change log table, which captures the transition of a state with its time, for example:

user_id,user_status_from,user_status_to,  created_date
 7cc4d      A2               A1       2019-11-03 23:04:26
 7cc4d      A1               A6       2019-11-03 23:05:28
 7cc4d      A6               I4       2019-11-16 10:00:34
 7cc4d      I4               A1       2020-03-16 10:00:36

Basically this table records the transition from one status to another, here it says that transition from
A2--> A1 occurred at 2019-11-03 23:04:26 (1st row) and then it moved from
A1 --> A6 at 2019-11-03 23:05:28 and so on.

Now I want to write a SQL through which I should be able to create table in such a way that tells the history of any status, for example: in particular status user was from this date to that date, basically a life of any status, I want to create a table like below:

   userId status statrtingdatetime  endingdatetime
   7cc4d   A1   2019-11-03 23:04:26 2019-11-03 23:05:28
   7cc4d   A6   2019-11-03 23:05:28 2019-11-16 10:00:34
   7cc4d   I4   2019-11-16 10:00:34 2020-03-16 10:00:36
   7cc4d   A1   2020-03-16 10:00:36 2020-04-02 08:24:50

If you see this table, it is nothing but some sort of logical table from above table, it says that a particular status life was from one date to another.
How can write SQL to create this final table.(please note that LAG/LEAD function doesn't work in my version of MySql Server)


texts:
I want to write a query to select all the rows where the row value changes from previous row. Please see the image attached above.

CREATE TABLE traffview.status_data (
    id serial NOT NULL,
    site_id integer,
    rtc timestamp without time zone,
    sigplan smallint,
    PRIMARY KEY (id)
    );

INSERT INTO traffview.status_data(
    id,
    site_id,
    rtc,
    sigplan
    )
    VALUES (?, ?, ?, ?);


texts: When creating asymetric key from assembly does assembly need to stay on the file system

texts:
I am deploying a SQL Server CLR project to my database and am usure of something. To give the function in the project external access I am creating an asymetric key from the projects assembly, creating a login from that key and granting access to that login. From MSDN:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'path\to\my\assembly.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin
GO

My question is, do I need to keep the assembly on the file system after the key is created for the login to work? I know I could test this by doing the above then removing the assembly and seeing if things break but the only environment I have this type of access to right now is a production one and I don't want to muck around with things there. Thus, I asked here. Could anyone tell me if I need to keep the assembly after the key is created? Thanks!


texts: Is MySQL/MariaDB getting a better IOPS deal than Postgres in RDS?

texts:
According to the documentation:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

The page size of Mysql is twice bigger than the postgresql one:

MySQL/Maria, 16 KB Pages, 30,000 max IOPS

PostgreSQL, 8 KB Pages, 30,000 max IOPS

This is a problem because the max data block considered a single IOPS is 32kb in RDS. so if you are asking for 30,000 pages pf MySQL data you may reach a 480Mb/s throughput vs 240Mb/s in Postgres for exactly the same amount of money.

Is this really how RDS works? is MySQL twice as good in disk access cost according to the AWS pricing?


texts: How to skip the current 'Relay_Master_Log_File' in AWS MYSQL RDS?

texts:
Below is the status of my slave. The master log file is at position "mysql-bin-changelog.278337" but the slave log file is at position "mysql-bin-changelog.278008". How to skip the current executing "Relay_Master_Log_File" t point to some other log file?

    mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: kdm-eink-prod.cfewehpmjefz.us-east-1.rds.amazonaws.com
                  Master_User: rdsrepladmin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.278337
          Read_Master_Log_Pos: 120
               Relay_Log_File: relaylog.377536
                Relay_Log_Pos: 1148
        Relay_Master_Log_File: mysql-bin-changelog.278008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 975
              Relay_Log_Space: 354792434
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 98230
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1113717901
                  Master_UUID: ec0eef96-a6e9-11e2-bdf0-0015174ecc8e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Sending data
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.24 sec)

ERROR:
No query specified


texts:
I'm working with NoSQL Database "Firebase", and I have a node that contains 6 categories "services" in everyone I put an array of orders well?

now when the user logged I get/fetch all orders bassed on his category lets to say "plumber",
So he can accept an order or reject it if he accepted it this order remove from this category "Plumber" and will not appear to other users it will move on to node else and that's fine,

now if he wants to reject this order, that's mean this order shouldn't appear again to you? but will appear to other users!

so how can I handle it to if he rejects an order he can't see it


texts: Create read only user without being root

texts:
I want to create read only user in MySQL without any intervention of root user.

CREATE USER ‘$user‘@’127.0.0.1′ IDENTIFIED BY ‘$password‘;

Works for me only If I am a root user. But I want it to happen from a non root user. Can this be done?


texts: How to resolve Geospatial Data over linked server causing error?

texts:
Update of the issue.

The exact error is Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later.

The error is happening on my local instance of SQL Server 2008r2 in a sp that calls a linked server (another sql server 2008r2) via an OPENQUERY call. I am executing that sp from my local instance. The code that calls the linked server fails but only when geospatial data (geometry) is included in the select part of the statement( the statement called in the open query.) When there is no geospatial data in the select statement to the linked server it executed fine. So geospatial data over a linked server connceting causes the error.

I should point out, but this may be a red herring, that the same stored procedure was called from an sql task in an ssis package and it passed. It called the procedure on my local instance of sql server. But that is not the issue now. The issue is outlined above.

I should also point out that Microsoft called that particular error a Memory Pressure error.


texts: Shrink the database only up to its initial size which is set after creating database

texts:
In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.

So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file).
But after creating the database  I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)

But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?

In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.

Note: I know that shrink is bad and should not be done.But I want to know how it can be done? If sql server considers only the initial size set during creation of database then what is the use of setting the initial size after creating database?

EDIT:

From microsoft shrink related article:
Using DBCC SHRINKDATABASE you can not shrink database below its initial size.

My question is Can I change the initial size of existing database itself?

EDIT:

I have about 5 sql server instance. All the database where created with default size of 3 mb for data and 1 mb for log(about 3 years back). Now all the databases are in the range of 100-1000 mb. I wont shrink the database normally as it is not good practice. But some database grow more than 10 GB(mdf file itself as i insert so much data in it). So when database is large I will shift unimportant and old contents from this database to another server as it is not required in that server for me. So that database size reduces from 10 GB to 1 GB. Now the remaining 9 Gb left unused. Now I  want to specify database size to be reduced to 2 GB so that i will get 1 GB free space(Also 2 Gb is the ideal size for this database), Which can be used to avoid autogrowth till next 1 Gb of data is filled. Also i have set 100 MB auto-growth for this kind of large database which has more writes. So instead of specifying size as 2 GB in shrinkfile command,if it was possible to change initial size to 2 GB (which shrink considers) then it would have been better. During this database creation there was no plan about size ,autogroth etc. As it was created about 3 years back. Now i am planning to set autogrowth etc. I cant again create new database with new initial size and shift data. So I was asking whether I can change the Initial size for existing database?


texts: The underlying provider failed on Open. Error in Windows Service

texts:
I have a Windows Service(c#) that receives data and inserts it into DB using Entity Framework 6. Database is SQL Server 2014 Enterprise Edition. Both (Service and DB) are hosted on VPS( Windows Server 2008 R2 with 4 GB RAM). The load on the server and service is too low, approx 60 records per minute.

Now intermittently I get the below error in Logs:

  Exception:
  The underlying provider failed on Open.

  Inner Exception: System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired.  The timeout period elapsed
  during the post-login phase.  The connection could have timed out
  while waiting for server to complete the login process and respond; Or
  it could have timed out while attempting to create multiple active
  connections.  The duration spent while attempting to connect to this
  server was - [Pre-Login] initialization=0; handshake=7145; [Login]
  initialization=0; authentication=0; [Post-Login] complete=7387;  --->
  System.ComponentModel.Win32Exception (0x80004005): The wait operation
  timed out
       connection)

Any help regarding fixing this shall be greatly appreciated.

EDIT

When I digged into the SQL server logs I found the below entry whenever I get the error.

  Date      05-12-2014 00:28:24 Log     SQL Server (Archive #1 - 05-12-2014
  14:10:00)

  Source        spid57

  Message Starting up database 'ReportServerTempDB'.

Has that got anything to do with the Issues?


texts: I need to store count of ROWS in table to variable in which value of a column called 'procossed' =0, if value of variable >0 will execute next step

texts:
create PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
declare @sql1 VARCHAR(1000)
declare @counts numeric(4)
set @counts=0;
SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT '+@counts+' = count(*) FROM '+@TableName+' where processed=0"';
EXEC MASTER..XP_CMDSHELL @sql1;
IF @ COUNTS>0
SET @SQL='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
EXEC MASTER..XP_CMDSHELL @SQL;
END;

Error occured is:

  Msg 8115, Level 16, State 6, Procedure tablepass12, Line 9 Arithmetic
  overflow error converting varchar to data type numeric.


texts: Use same Postgres WITH in multiple queries

texts:
I would like to use same WITH in multiple queries.

WITH x AS (INSERT ... RETURNING id)
INSERT INTO t2 VALUES (x);
INSERT INTO t3 VALUES (x);

How would I do that? Do I have to create a temporary table instead?


texts: How to get the size of the result of a SQL query?

texts:
How do you get the size (in bytes) of the result of a SQL query, in MySQL 5.6?

EXPLAIN will return the number of fetched rows, but not their size.

My aim is to evaluate common queries to know a lower bound value for query_cache_limit so that they can be served by the Query Cache.


texts: Does Azure SQL natively support being the subscriber in transactional replication where Oracle is the publisher?...and if PostgreSQL is the publisher?

texts:
Is there a native way to setup transactional replication where an Azure SQL database is the subscriber and the publisher database is in Oracle?

What if the publisher database is in PostgreSQL?

If there's no native way for either of these, is there a not-so-hacky way to do it?


texts: PostgreSQL: Allow connection by host name and not IP?

texts:
As background when connecting from remote (eg home mostly using JDBC) due to how the VPN works I always get a different IP address and hence I can't connect because the IP isn't listed in pg_hba.conf. Hence I tried with host name but that doesn't work either.

How can I allow access to PostgreSQL by hostname and not IP address?

EDIT:

I found the following which seems more suitable to place here than the comments:

https://www.postgresql.org/message-id/369ED3B4-CAF3-4BBB-904F-E86999249D8E@visi.com

  If a host name is specified (anything that is not an IP address range or a special key word is treated as a host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match. (The host name that is used in pg_hba.conf should be the one that address-to-name resolution of the client's IP address returns, otherwise the line won't be matched. Some host name databases allow associating an IP address with multiple host names, but the operating system will only return one host name when asked to resolve an IP address.)

So while ping with host name works, the reverse dns lookup doesn't work and hence according to above text explains why connection then fails. So it for sure seems the way the VPN works is to blame for this as nslookup indeed fails to find a name.


texts: Troubleshooting Resource Governor / Classification Function

texts:
I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this troubleshooting guide: http://technet.microsoft.com/en-us/library/cc627395(v=sql.105).aspx

My classification function, originally conditional, now simply returns my resource pool name in all cases in an attempt to get something to run in it.

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RGClassifier]    Script Date: 7/25/2014 7:26:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[RGClassifier] ()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @GroupName SYSNAME
SET @GroupName = N'in-memory'

RETURN @GroupName

END

Results of some of the troubleshooting follow:

USE master
SELECT
  object_schema_name(classifier_function_id) AS [schema_name],
  object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

Returns:

schema_name function_name
dbo RGClassifier

Queries of sys.dm_resource_governor_configuration and sys.resource_governor_configuration indicate it is enabled and no reconfiguration is pending.

More or less stuck in the mud at this point. If anyone has configured the resource governor successfully before I'd love some pointers.


texts: Is searching faster if indexed values are the same?

texts:
Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123).
Because my users search a lot by date, I added an index on this attribute. There are probably hundred of thousands items in my collection and most of these events have a different createdAt date (which means a different index value).

My users are complaining that it takes too much time to retrieve some data, so I'm looking for a way to improve that.

As my users only search by date (never with time), I was wondering if creating another attribute 'createdAtDateOnly' (keep only the date and set the time to 00:00, for example: 2019/09/09 00:00:00) would be faster to search because many 'events' in my collection would have the same index.


texts: Give me suggestion about backup of schema and full backup in oracle

texts:
I need your help about backup in oracle database. Currently I am using Export Utility of Schema Backup but taking more time to complete and size is 40GB.

For full database backup using Image Copy and taking time more than 15 hours to complete and size is 650GB.

Which backup method is best for schema backup and full database backup which take less time and less size.

Database version Oracle Database 11gR2

Regards


texts: SSRS - Embed Rich Text (including images) in report

texts:
An application my organization is developing includes a rich text editor. The content from this editor (text, tables, images, etc.) is HTML5 and is saved to the database. I'd like to find a way of embedding all of this content into some SSRS reports (sometimes as the core content, others as a sub-report), but it appears that only a limited subset of HTML tags and CSS attributes is supported. Are there any better ways (or off the shelf components) to help me achieve this goal? (I am considering rendering the HTML to an image and embedding the whole image but can't imagine this will work well).
Thank you in advance!


texts: Do I need to worry about RESTORE FILELISTONLY types other than L and D?

texts:
According to https://msdn.microsoft.com/en-us/library/ms173778.aspx, there are four types, L, D, F and S.

All of the actual database instances I've seen so far (SQL Server 2008 and up) only have types L and D. I've written a script to backup and restore databases very quickly (there are a wealth of answers on Stack Overflow with instructions) but I'm wondering if I'll ever need to worry about the other two types: F and S.

I've read that full-text catalogues (F) are virtual from SQL Server 2008 onwards, so it seems like I will never encounter those. The documentation says that S can be an in-memory OLTP container, but I'm not sure if FileStream and FileTable are also virtual or not.


texts: sp_BlitzFirst fails when outputting values to tables

texts:
I have had an agent job running sp_AskBrent for some time, running with the following parameters:
EXEC sp_AskBrent @Expertmode=1
,@OutputDatabaseName = 'DBName'
, @OutputSchemaName = 'dbo'
, @OutputTableName = 'AskBrentResults'
,@seconds = 30
, @OutputTableNameFileStats='AskBrentResultsFileStats'
,@OutputTableNamePerfmonStats='AskBrentResultsPerfmonStats'
, @OutputTableNameWaitStats='AskBrentResultsWaitStats'

Today I wanted to update to the new renamed procedure sp_BlitzFirst. After creating the new procedure, I changed the code in the agent job to run sp_BlitzFirst instead, with the same parameters.
Everything seems to go OK until it is time to output values to the tables, then there seem to be some kind of conversion error.
The whole history output is as follows:

Setting up configuration variables [SQLSTATE 01000] (Message 50000)
Now starting diagnostic analysis [SQLSTATE 01000] (Message 50000)
Capturing first pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Beginning investigatory queries [SQLSTATE 01000] (Message 50000)
Finished running investigatory queries [SQLSTATE 01000] (Message 50000)
Waiting to match @Seconds parameter [SQLSTATE 01000] (Message 50000)
Capturing second pass of wait stats, perfmon counters, file stats [SQLSTATE 01000] (Message 50000)
Analyzing changes between first and second passes of DMVs [SQLSTATE 01000] (Message 50000)
Analysis finished, outputting results [SQLSTATE 01000] (Message 50000)
Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241).  The step failed.

Anyone have any suggestions to what might cause this? Have the output tables changed somehow since the procedure was called sp_Askbrent? The version of sp_Askbrent was reported this way: sp_AskBrent (TM) v20 as of Jan  1 2016 12:00AM.


texts:
I have the following tables and columns:

Users

id | other-non-important-columns

Containers

id | Users_id  | other-non-important-columns

Content

id | Container_id | other-non-important-columns

This is a 3 level hierarchy, however I would like to get rid of the second tier (Containers), and associate Content directly with Users i.e.

Content

id | Users_id

What query can I construct to populate the Content table with Users_id?

I am planning to keep Containers. Right now, content must belong to one container. The ultimate solution moving forward will be:

Content belongs to 1 user.
Content can belong to 0 or more containers

...but I'm trying to think through this one step at a time.

The reason I have the columns named as generic 'Id', is because this seems to be what a lot of ORM tools seem to want. I have tried a few ORMs. There are ways of course that you can override that behavior and specify your own primary key, but it seems like it was common/standard practice to just create every table with an auto-incrementing primary key named id.


texts: How to determine the cost of index creation?

texts:
I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table.

How can I test whether the index has had any significant detrimental impact on DUI operations on the table?

The table is a log table, so it is written to frequently. Is it a case of running a particular INSERT or UPDATE query against that table before and after creating the desired index?


texts:
We have a monster view that contains joins to many (many) tables in order to return the data that is required. In some cases, to get a column of information, we've have to join onto 4 tables, just to get to the value we need (the database structure isn't great, and we can't change it).

In order to improve the performance, I am extracting certain components and constructing them into their own View, in order to simplify the execution plan, and hopefully optimise the queries.

If I write a query that joins these views together, do I lose the benefit of the query optimisation for each view? i.e. do I end up back where I started by combining the views into a single select, or will it inherently perform better because they are acting atomically from one another?


texts: SQl Server 2008 R2 - sa can login but not access database

texts:
Up until this morning I had no problems accessing our database.

This morning, something has changed and I can no longer access our database (called EMS_Main) using the sa login. I can log into the instance quite happily, but cannot access the database itself. This goes for both my C# application and SQL Server Management Studio. In SSMS I get "The database EMS_Main is not accessible. (Object Explorer)" if I try and expand the icon for EMS_Main.

Seems to make no difference whether I'm logging in via LAN, VPN, or even remote desktop on the server itself.

The logs don't show much out of the ordinary other than last night around 22:05 I got the following:

Four "IO is frozen on database" messages for master, EMS_Main, msdb, and model.

Four "IO was resumed on database" messages for the above.

Then (with the same timestamp):

"A timeout occurred while waiting for buffer to latch -- type 2"

"Error: 3041, Severity: 16, State: 1"

"BACKUP failed to complete the command BACKUP DATABASE EMS_Main. Check the backup application log for detailed messages."

Then nothing until this morning when I got:

"Error: 18456, Severity: 14, State: 38"

"Login failed for user 'sa'. Reason: Failed to open the explicitly specified database."

Any idea what is going on/how I can rectify this?

It smells like a permissions issue to me; I've tried creating a new login in SSMS so I can do a "GRANT VIEW ANY DATABASE TO sa" (a solution suggested on another website), but creating a new user hangs in SSMS (clicking the 'User Mapping' page also hangs it as well, not sure if that's related) - the 'Progress' pane just shows 'Executing' with the progress indicator going round indefinitely.

If I double click on the sa login, I can see (after a bit of a wait) that it has permission to connect to the database engine, the login is enabled, the box for 'Login is locked out' is not checked (but is greyed out), it only has 'public' and 'sysadmin' checked for Server Roles, default database is set to 'master'.

The server is running MS Windows Server 2003 for Small Business Server SP2. We're on SQL Server 2008 R2.

I am new to being a dba (got thrown into it by my employer) - my background is more in coding business apps, so go gentle on me! I don't necessarily know all the dba jargon too well ;)

Thanks in advance.


texts: MariaDB 10.3 slave fails to replicate from a MariaDB 10.2 master

texts:
We have a 10.2.18 master and 4 slaves with 10.2.18. Because of this bug that I've reported last week (https://jira.mariadb.org/browse/MDEV-17420) we tried to upgrade one of the replicas to 10.3.10 to see if the bug was still there in that version.

After the upgrade, the slave began to sync binary log events from the master, but after a few seconds it stopped with the following errors:

mysqld[15138]: 2018-10-17  9:22:28 156 [ERROR] Slave IO thread did not receive an expected Rows-log end-of-statement for event starting at log 'main.010755' position 89390527 whose last block was seen at log 'main.010755' position 89390527. The end-of-statement should have been delivered before the current one at log 'main.010755' position 89390610
mysqld[15138]: 2018-10-17  9:22:28 156 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Internal MariaDB error code: 1595

We checked the binary log in the master and does not seem corrupted and the other 3 replicas are working ok (still with 10.2.18). Also the relay log on the slave seems ok.

The slave is stopped with the following status:

mainro [(none)]> show slave status\G
*************************** 1. row ***************************
           Master_Log_File: main.010755
       Read_Master_Log_Pos: 89411517
            Relay_Log_File: relay-bin.000002
             Relay_Log_Pos: 550
     Relay_Master_Log_File: main.010755
          Slave_IO_Running: No
         Slave_SQL_Running: Yes
       Exec_Master_Log_Pos: 89410432
           Relay_Log_Space: 1657
             Last_IO_Errno: 1595
             Last_IO_Error: Relay log write failure: could not queue event from master
            Last_SQL_Errno: 0
            Last_SQL_Error:
   Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
          Slave_DDL_Groups: 16763
Slave_Non_Transactional_Groups: 7974
    Slave_Transactional_Groups: 107415

Does someone has experimented something similar with these combination of master and slave versions?

How can I overcome the situation?

We have already tried with RESET SLAVE, clearing relay logs and executing CHANGE MASTER TO again. But the result is the same.

I've dumped the master binary log at that position and, interestingly, slave IO thread stops in a binlog block (master binlog format is MIXED).

Downgrade is not and option because data is already upgraded.

To clarify a little more our environment:

The master/slave set that is failing is the main database server for our organization. It has 1.2 TB of data with 38 databases, views, stored procedures, triggers and complex queries.

In the same servers, we have 3 other small sets of master/slaves with the same combination (10.2.18 master and 10.3.10 slaves) and are working perfectly. Of course, these sets are smaller and simpler than the instance that is failing.

Thanks


texts: On which machine we should install WAL-G?

texts:
We are using PostgreSQL version 12.1. Currently we have high availability and automatic failover using Patroni and it's working fine.
Now we are planning for disaster recovery and we are planning to use WAL-G for this.
We read some article to know that on which machine we should install WAL-G master, slave or both? But couldn't get a clear idea.
Can you please help us to know on which machine should we set up WAL-G?


texts: Need to fetch latest record always and ignore if there is no latest data

texts:
Basically i want to send mail whenever there is a new record in this table throughout the day with list of all records got created.

I have a table with one date column. i have to always fetch the latest data based on that column and it should not give nay result if there is no latest record after the last one.

For ex:- latest record pulled at 9 AM and after that there is no update then it should not pull the same data again.


texts:
There was a situation of 108,368,168 executions along with 7,373s elapsed time for "update seq$ set increment$=:........" statement while loading massive(i.e. populating million rows) data in the table. It means - it definitely spending some fraction of the time during each call to acquire the latch to be able to update the data dictionary when a new set of values need to be generated.

I diagnose the situation and found that it was happening because of Sequence NOCACHE and started to increase its CACHE value until it's not included in the SQL ordered by CPU/Elepsed in AWR report, finally it's settled when CACHE value set to 100000. Now there is no query in AWR related to Sequence and concluded that CACHE value is given positive performance gain.

Can anyone tell me what are the items I can check in Oracle to confirm such a large sequence cache is not back fired in the database.


texts: How to design a relational DB to capture the same entity as reported by multiple sources?

texts:
I need a DB that can capture instruments as reported by a bank and two accounting firms. Let's take Walmart stock as an example. The bank may have a instrument named "Wal-Mart Stock". One of the accounting firms may have it named as "Walmart Stock". The other accounting firm may have "Wal-mart common stock".

Here's the diagram that I have so far:

* Table contains five additional columns. These five column names are the same for MasterInstrument and DataSourceInstrment.

The DataSource represents the bank, accounting firms, or any other entity that can provide data.

The DataSourceInstrument represents the instrument data as provided by the DataSource.

MasterInstrument is a version of the instrument specific to the application itself. So, in our example, it may contain "Walmart Common Stock" for the instrument name. It's different than DataSourceInstrument b/c unlike DataSourceInstrument, it is used extensively throughout the application and it must maintain a history of changes.

The Instrument table provides a way to know that several different data sources are referring to the same instrument. DataSourceInstrument and MasterInstrument with the same instrumentId are referring to the same instrument. (Instrument.id is a surrogate key b/c some instruments do not have a natural key, and the DataSourceInstruments and a MasterInstrument may need to be manually mapped as referring to the same instrument.) So, using the Walmart example, we can know that the bank, two accounting firms and the internal system are referring to the same Walmart instrument.

The Issuer represents the issuer of the instrument such as "Wal-Mart Stores, Inc." Note: It only needs to be captured for MasterInstruments.

I have a couple of doubts on this design:

Is it poor design to have a table (i.e. Instrument) with only a single surrogate column? If this is a problem, how would it be designed differently?
Are the various *Instrument table names confusing? If so, how could it be more clear?

Overall how should the design account for the need to capture multiple sources referring to the same instrument along with the need to capture an internal version of that instrument with history?

Update

The system's primary function / raison d'être is reporting. It must report for a given date what the internal instrument (i.e. MasterInstrument) data was on that date. This is why I have MasterInstrument as a history /temporal table and do not have the columns in Instrument.


texts: All columns of all views defined as "1" in phpmyadmin export

texts:
MySQL 5.7.24, phpmyadmin 4.8.3

phpMyAdmin appears to export corrupted definition on all views of one of my MySQL database. Eg:
show create view v_contacts returns

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_contacts` AS select `d`.`rowseq` AS `rowseq`,`d`.`airline_rowseq` AS `airline_rowseq`,`a`.`name` AS `airline_name`,`c`.`name` AS `country_name`,`d`.`i_type` AS `i_type`,`ab`.`hint` AS `contact_type`,`d`.`i_index` AS `i_index`,`d`.`i_value` AS `i_value`,`d`.`updated` AS `updated` from (((`atdb6`.`directory` `d` join `atdb6`.`airline` `a`) join `atdb6`.`country` `c`) join `atdb6`.`abbreviations` `ab`) where ((`d`.`airline_rowseq` = `a`.`rowseq`) and (`a`.`country` = `c`.`rowseq`) and (`ab`.`type` = 'D') and (`d`.`i_type` = `ab`.`code`) and (`d`.`i_type` <> 'LG'))  utf8    utf8_general_ci

Exporting with phpMyAdmin generates a file which contains:

DROP TABLE IF EXISTS `v_contacts`;
/*!50001 DROP VIEW IF EXISTS `v_contacts`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `v_contacts` AS SELECT
 1 AS `rowseq`,
 1 AS `airline_rowseq`,
 1 AS `airline_name`,
 1 AS `country_name`,
 1 AS `i_type`,
 1 AS `contact_type`,
 1 AS `i_index`,
 1 AS `i_value`,
 1 AS `updated`*/;
SET character_set_client = @saved_cs_client;

and later in the export file, the correct definition:

/*!50001 DROP VIEW IF EXISTS `v_contacts`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_contacts` AS select `d`.`rowseq` AS `rowseq`,`d`.`airline_rowseq` AS `airline_rowseq`,`a`.`name` AS `airline_name`,`c`.`name` AS `country_name`,`d`.`i_type` AS `i_type`,`ab`.`hint` AS `contact_type`,`d`.`i_index` AS `i_index`,`d`.`i_value` AS `i_value`,`d`.`updated` AS `updated` from (((`atdb6`.`directory` `d` join `atdb6`.`airline` `a`) join `atdb6`.`country` `c`) join `atdb6`.`abbreviations` `ab`) where ((`d`.`airline_rowseq` = `a`.`rowseq`) and (`a`.`country` = `c`.`rowseq`) and (`ab`.`type` = 'D') and (`d`.`i_type` = `ab`.`code`) and (`d`.`i_type` <> 'LG')) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

However only the first part is used when importing on another computer with phpMyAdmin 4.0.10.14, so all columns are set to 1.

Same for all views in the same database. Is this a bug, and no export file generated by phpMyAdmin 5.7 should be reused for import by phpMyAdmin 4.8 ?


texts: Can an InnoDB page contain data from different indexes?

texts:
(In this question, I assume that the data of a table is acťually an index (PRIMARY), so I don't differentiate between table data and column indexes)

I've compacted an "old" InnoDB table (via null ALTER TABLE), and observed the space taken by the indexes, before and after, via the mysql.innodb_index_stats table.

The size decreased (for some indexes, significantly); this lead to a question: can a single InnoDB page contain data from different indexes?


texts: Cannot disable CDC - sp_cdc_disable_db_internal gives error 8152: 'String or binary data would be truncated'?

texts:
Trying to disable CDC and getting this error:

  Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 264
  Could not update the metadata that indicates database … is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_drop_objects]'. The error returned was 8152: 'String or binary data would be truncated.'. Use the action and error to determine the cause of the failure and resubmit the request.


texts:
I'm trying to load data from a very large table (200 million rows) into a Presentation Datamart (PDM) for an Enterprise Data Warehouse using Teradata 14.10. I want to split this large table into several separate tables containing 30 million rows each.

I've simplified things down, but here's the table structure:

CREATE SET TABLE MYDB.LARGE_TABLE ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
(
    CUSTOMERID INTEGER TITLE 'CUSTOMER IDENTIFIER' NOT NULL
    , FULLNAME VARCHAR(30) NOT CASESPECIFIC TITLE 'FULLNAME'
);

I've gotten as far as using ROW_NUMBER() so I know how many actual rows there are in the table:

SELECT
    ROW_NUMBER() OVER (PARTITION BY CUSTOMERID, ORDER BY CUSTOMERID) AS RANK_CUST
    , CUSTOMERID
    , FULLNAME
FROM
    MYDB.LARGE_TABLE AS MYTBL

Because of the SQL standards that are enforced we have to follow these restrictions:

Cannot use Stored Procedures
No single piece of code to contain more than 3 table joins
No single piece of code to result in more than 30 million rows of output

I'm preparing this SQL script for a very stringent review, and it definitely won't be approved unless I can find a way to split this large table into smaller rows of data. With these restrictions in mind, does anyone have any ideas?


texts:
As I understand it, one way to 'abstract' a parent/child relationship in SQL would be to have a table set up with a joining foreign key table. Ie in this case, the swimsets are children of different style_tags:

swimset_styletag

id          swimset_id  styletag_id
----------  ----------  -----------
1           1           1
2           2           2
3           3           1
4           3           2

swimset

id          set_description
----------  -----------------------
1           4 x 100 Free Interval A
2           4 x 100 Back Interval A
3           6 x 100 free/back on in

styletag

id          tag_name
----------  ----------
1           Free
2           back

However, I'm testing the idea of just using Booleans and therefore one single table instead of the foreignkey:

swimset

id          set_description          free_tag  back_tag
----------  -----------------------  --------  --------
1           4 x 100 Free Interval A  True      False
2           4 x 100 Back Interval A  False     True
3           6 x 100 free/back on in  True      True

My understanding is that the queries would be faster (one table), and that it will still be easy enough to query all of the swimsets that are tagged "free" (ie look up all of the children of a certain parent). Are there any downsides that I'm missing here by lumping everything into one table?


texts: Removing large image files from production database

texts: The database design for a retail organization has several tables with image columns. These account for around 40% of the table size due to their large size.  (Many a DBA issues come from inheriting the existing environment).  This is a production database.  I would like to know of a way to archive the current images from the database. Is there a better way besides FILESTREAM to  store or have access to image files than this?  I am thinking of a column with link to file system where the image is stored.  How does one get to that point? Can someone point me in the right direction


texts: Why is SQLite adding unused disk space to "free-list" after deleting a lot of data in a database?

texts:
The SQLite FAQ states the following:

  (12) I deleted a lot of data but the database file did not get any
  smaller. Is this a bug?

  No. When you delete information from an SQLite database, the unused
  disk space is added to an internal "free-list" and is reused the next
  time you insert data. The disk space is not lost. But neither is it
  returned to the operating system.

Can you explain to me the motives behind that decision (as opposite to returning free space to the OS immediately)? What are the pros and cons of such decision (including non-obvious ones)?


texts: how to verify that next sequence value can be used as a new primary key

texts:
I have many tables with a trigger attached like this

BEFORE INSERT
ON BRULES
...snip...
  if inserting and :new.ID is NULL then
  SELECT BRULES_SEQ.nextval into :new.ID FROM DUAL;
  end if;

Due to an interesting coding style the application does inserts on some tables with a null ID which fires the trigger and uses the existing sequence.  For other tables it gets the ID of the newest record and adds 1.  In this case the sequence is not used and gets out of synch with the number of records in the table.

How can I tell if the next value of a sequence is valid for an insert?  I could read all triggers for the sequence name and then do some dynamic SQL to compare the maximum ID and the current value of the sequence but it seems a bit clunky.


texts: MySQL server upgrade, how to adapt variables?

texts:
I am about to upgrade my MySQL server because I have currently used it mainly with a couple of people in the development stage. Before we launch, however, we would like more capacity to avoid the server crashing. I currently run on a Ubuntu system with 2 GB of RAM, 2 vCPU's, 60 GB disk and 3 TB of monthly transfer.

My variables are set to:

innodb_buffer_pool_size = 256M
innodb_io_capacity      = 1900
innodb_flush_method     = O_DIRECT
key_buffer_size         = 10M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 100
myisam-recover-options  = BACKUP
query_cache_limit       = 10M
query_cache_size        = 0
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 2
wait_timeout            = 300
interactive_timeout     = 300

I am about to ugprade my server to 8 GB RAM, 4 vCPU's, 160 GB of disk space and 5 TB of monthly transfer. The size of my table is currently 1.3 MB, but this will of course increase as soon as more users become active. How much this will increase and how fast, I can not really say for sure.

So I am now trying to figure out how I should change my variables to get the best performance. I know I should allocate my memory correctly, and these settings work fine for the setup I currently have, but if I need to change anything for the upgraded system, I would really appreciate some insights.

Cheers!


texts: Designing a database for the academic activities of individuals or departments

texts:
I have a database which stores the academic activities in which some individuals or departments are involved.
An activity could be any among some of these types:

article (written)
course (imparted)
book (written)
prize (received)
project (developed)
...

and can involve a whole department (as an entity) or individuals (1 to n). It's a XOR, or departments or individuals, e.g.:

an article is written by 1:n individuals, never by a department.
a prize is received by a department, never by individuals.
...

Each Activity is defined by multiple properties: some are common to all types, some are specific for each type:

common properties: title, description, year, valoration, ...
specific properties:

for article: magazine, month, ISSN, URL,...
for course: institution, city, hours, course_type, ...
...

¿What is the proper way to design the database?
I came up with two ideas:

A) Isolate each type in one table with all columns it needs ( common and specific ). Each table manages its own ID, non unique between all activities.

Each table has its own relationship table with individual OR department.

B) One "master" table for common columns and primary ID, and one "child" table for the specific columns of each activity type:

Everything is shared unless its own specific columns.

Option a) seems proper way to do it, option b) seems ugly to me.

But in real world I prefer b). If I need a list of all activities in year 2017, a simple query to master is enough. If later I need a concrete activity, consulting very few tables I get all info (common + specific columns, individuals or department involved, etc).

¿Is a bad practice to design the database thinking of my app requirements?
I need some advice here.


texts: Multi-insert batch executed remotely 5x slower than locally

texts:
I am inserting 30.000 rows into a table in one batch using INSERT .. VALUES statement for each row.
See the testing environment:
Table creation:
CREATE TABLE [dbo].[TestInsert](
    [Col1] [int] NOT NULL,
    [Col2] [varchar](16) NOT NULL,
    [Col3] [varchar](15) NOT NULL,
    [Col4] [int] NULL,
    [Col5] [datetime] NOT NULL,
    [Col6] [nvarchar](128) NOT NULL
)

Batch inserting rows:
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491073,N'D0058A79AE',N'OCIP',51849,'20100823 10:02:04.683',N'TUVWXYZabcdefgh')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491074,N'D00559B4C4',N'OCIP',62488,'20100823 10:02:04.710',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491075,N'D005AB75B6',N'OCIP',52836,'20100823 10:05:17.070',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491076,N'D0070B9F25',N'OCIP',62554,'20100825 08:03:08.260',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491077,N'D00753F2D7',N'OCIP',62554,'20100825 08:03:58.733',N'UVWXYZabcdefghi')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491078,N'D0070B979A',N'OCIP',62554,'20100825 08:04:09.917',N'STUVWXYZabcdefg')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491079,N'D0070B6F37',N'OCIP',62554,'20100825 08:04:21.043',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491080,N'D0070B86F3',N'OCIP',62554,'20100825 08:05:28.460',N'GHIJKLMNOPQRSTU')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491081,N'D00708D1E1',N'OCIP',62554,'20100825 08:06:50.030',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491082,N'D0070B7DFA',N'OCIP',62554,'20100825 08:11:13.507',N'VWXYZabcdefghij')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491083,N'D0070B7FCE',N'PRON_OCIP',62555,'20100825 09:13:26.563',N'XYZabcdefghijka')
...
more (30.000 rows)

You can download the whole batch here: https://filebin.net/zg499h4iv1m44z6v
The table has no indexes, constraints, foreign keys, triggers…nothing.
When I run the batch in SSMS locally, it takes 20 seconds and produces these wait stats:

But when I run it in SSMS from a remote computer over LAN, it takes 100 seconds (5x slower) with almost the same wait stats:

Please, mention there is no ASYNC_NETWORK_IO wait type.
SET NOCOUNT ON is not set intentionally!
Testing in other environment it works just fine with no big differences in time.
What could be the real source of the slowness? Why it's not captured by wait statistics?
Please be more specific than just saying „network infrastructure“.


texts: How can I implement full-text search on a single value in a JSONB column?

texts:
I have a table that stores a conversation between two people.

The data will look something like this:

CREATE TABLE foo
AS
  SELECT $$[
    { "user": 1, "timestamp": 1, "message": "First message" },
    { "user": 2, "timestamp": 2, "message": "Second message" },
    { "user": 2, "timestamp": 3, "message": "Debounced message from same user" },
    { "user": 1, "timestamp": 4, "message": "Last message" }
  ]$$::jsonb AS jsondata;

I never need to look up each message individually, so I just want to store the whole conversation in a single jsonb field. I need to perform a full-text search across all of the messages.

My first thought was to create a new text column, concat all of the messages into one long string, and create a trigram GIN index on that column.

That seems like a hack that wastes a lot of space, so I would like to avoid the intermediate column. How can I create the index directly from the jsonb column?


texts:
I have a table message. I saw that queries for this table where long running and caused a memory leak (scheduled job in java app).
In pg_stat_activity I saw:
wait_event_type wait_event       state
IO              DataFileRead    active

So I increased available disk space, but it didn't solve this problem.
I saw that there was long running autovaccum worker for this table. I checked table size and it was about 90% of available RAM. I renamed this table and created new one with same schema.
So now I have a new table with few records, but the long running autovaccum worker for this table still occurs.
Do you have any ideas what to check, how to tune this table?


texts: Postgres denormalize tables into json objects for node

texts:
Heres my problem. I have the following tables
CREATE TABLE inventory(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    owner text NOT NULL references profile(name)
);

CREATE TABLE inventory_item(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    description text,
    amount bigint,
    inventory uuid nOT NULL references table(id)
);

Now I would like to query for a single inventory

all items in that inventory
id and owner of that inventory

So I basically get a result of the following form, to send to the client:
{
   "id":"1234123-1234-1234",
   "owner":"tom",
   "items":[
      {
         "id":"2344-123412-34",
         "description":"sword",
         "amount":33
      },
      {
         "id":"6434-123412-34",
         "description":"apple",
         "amount":23
      },
      {
         "id":"45454-1123412-34",
         "description":"bow",
         "amount":32
      },
      {
         "id":"234234-1232412-34",
         "description":"arrow",
         "amount":1
      }
   ]
}

I was thinking of querying id and owner directly and then adding the items as a subquery using ARRAY(). Unfortunately, ARRAY() only works with columns of the same type.
So how can I denormalize my table structure into a JSON array for node using a postgres query?


texts: When is it worth it to use Triggers and stored procedures/functions?

texts:
As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post table)

I know these things can be quite circumstantial, but generally, do simple tasks like this belong in triggers/functions, or should they just be done in the application code?

As for updating views and replies I was thinking of offloading them to a queue, since the systems functionality doesn't really depend on them.


texts: Changing Postgres 9.6 to behave more like 9.5

texts:
I don't hold myself out as being as a professional database administrator, however I recently upgraded a non-trivial size database (about 100 gigs on disk) from Postgres 9.5 to 9.6.

The database was handling OK under load in Postgres 9.5, however it becomes unusably slow in 9.6.   The only thing that changed was the version of Postgres (which was upgraded using the pg_upgrade tool) - same Postgresql.conf file.

We have run a VACUUM ANALYZE, and this does not seem to have made any difference.

When I analyze a typical query which was taking > 5 seconds under load, it executes in 50ms.  When the system is under load, uptime (on an 8 core cpu) reports values of > 30 and VMSTAT shows all available CPU is being used (with no swap and negligible disk IO)

On the advice of "experts" the Postgres configuration (in 9.5) was tuned with the following options - which are in the 9.6 config as well -

autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 100
autovacuum_max_workers = 10
autovacuum_naptime = 5min
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 100
vacuum_cost_limit = 800

bgwriter_delay = 100ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4

checkpoint_timeout = 15min
checkpoint_warning = 5min
cpu_tuple_cost = 0.03
effective_cache_size = 50GB
maintenance_work_mem = 1GB
shared_buffers = 8GB
wal_buffers = 64MB
work_mem = 60MB

Are there any settings I can change to make Postgres 9.6 behave more like 9.5, so that I can work out what is causing the issue?

Does anyone have an insight into what might be causing Postgres to eat up all the available CPU ?

How can I go about debugging/getting a grip on the problem in a production environment ?


texts:
I am working with a database in MS-SQL 2008 R2, and I'm running into a problem.

The basic setup is this:  There are two tables of interest: dbo.Assesments and audit.Assesments.  dbo.Assesments is the working data table, and has triggers for data changes (insert, update, delete) to update audit.Assesments with a log of changes.  As far as I can tell, this has worked for years, so far so good.

Our L2 support has a Role called SupportRole that they get into with WinAuth, and that role has some select rights and execute rights for stored procedures.  One of those stored procedures does an update to dbo.Assesments.  This is where the problem comes in:  The update goes in, the stored procedure fires to insert a audit row to audit.Assesments, and then they promptly get a "Insert denied, insufficient rights to Insert", the transaction roles back, etc.

This is mystifying to me: I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing, but this has me thinking twice.  I don't want to give insert rights to the support role for the audit table/schema.  What should I do?


texts: New Database vs New Table for User Registrations

texts:
Here's an example, there is this application that completes a task (mostly data-enrty stuff). Users can sign-up on a website to begin using the application straight away.

At the moment, I was looking at setting up a new database for each user that signs up and loading the tables that are required in etc at this first signup.

However, in my previous questions about permissions, someone bought up the issues of scalability when using new databases for each user, it certainly isn't an impossible task but updating (for example, 500 users) all of those databases is going to be a huge task.

What is the best way to create these databases or tables for these new signups?

Each user can probably have 7 or 8 tables minimum from the outset.
There could be about 12 tables pretty soon too

The only other way I can see of how to do it is to have those 12 tables (or whatever) and just have many rows with respective user id's associated with them. However, should I do this, would there be an issue, if 30 or 40 people were logged on and updating/modifying information, with speed etc? vs. having new databases for each?


texts: CSV or DB is quicker to read several lines from a file with 1,5 billion lines

texts: I have a program which would read several lines according to the indices of lines within a CSV file with 1,5 billion lines(using c fgets, or is there any tool in C faster than fgets?). Compare with storing records into a DB (postgesql or any other) table then reads the ID without any join, would the first propose the better way to do it?


texts: What does node eviction mean in the Oracle RAC world?

texts:
Software environment: Oracle 12c with RAC running on RHEL

The question might sound rather banal, but I wasn't able to find any word on this in the Oracle documentation I read thus far.

By searching the net, I came to the conclusion that node evictions usually imply a node reboot, but that doesn't always seem to be the case, some suggest node evictions without node reboots are also possible.
If that is indeed the case, when will the node be rebooted and when not? If a node cannot access the minimum required number of voting disks, then that node will be evicted. Will that node always be rebooted because of this eviction? Can one configure this behavior (i.e. change from eviction with reboot to eviction without reboot)?


texts: Some records are missing while selecting from table

texts:
I am trying to match the records having MAX(WORKITEM_ID) but some PROCESS_INSTANCE_ID are not coming though they are present in WORKITEM table.

select WI.WORKITEM_ID as WORKITEMID,
       PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL as WSNAME

from  WORKITEM WI,PROCESSINSTANCE PI,TASKASSIGNMENT_V4TA,
       SDI_WIDGET_WORKSTEP WS

where WS.WS_NAME = WI.WORKSTEP_NAME and
      TA.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID and
      PI.status in ('PI_ACTIVATED','PI_COMPLETED')and
      WI.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID
      and WI.status in ('I_ASSIGNED','I_AVAILABLE')
      and WI.WORKITEM_ID =
(select MAX(WORKITEM_ID)from (select WITEM.WORKITEM_ID from WORKITEM WITEM
 where **WITEM.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID**) as WORKITEMID)


texts: What is the fastest way to duplicate inserts of an identity column to another table?

texts:
I have two tables: A data table with an auto-incrementing id column, and an id table. I would like every id in the data table to show up in the ids table on insert. I have many such data tables and I wish to ensure that the ids in each cannot clash with each other. I also wish to foreign key to the global list of ids in the ids table. I would like a system to do this automatically so I don't need to script explicit inserts to the ids table. I know this is straight forward to achieve using triggers, but I want to know the most performant way to do this for data sizes beyond 100m rows. Simplified example code follows:

The tables:

create table data(
  id bigint generated always as identity(start 1 minvalue 1 maxvalue 100000000 no cycle cache 1 increment 1),
  name text
  );

create table ids(id bigint);

I tried using a rule, but had an issue with the volatile nature of the identity column:

create rule url_id_sync as on insert to data do also insert into ids(id) values (new.id);

insert into data(name) values ('some value');

The data:

select * from data;

|id|name      |
--------------
|1 |some value|

select * from ids;
|id|
----
|2 |

...which is not correct of course.

So, I tried a trigger approach:

create or replace function ufnt_internal_identifier_insert() returns trigger as
$$
begin
  insert into ids(id)
  values (NEW.id);
  return new;
end;
$$ language plpgsql
;

create trigger utr_ids_insert after insert on data for each row execute function ufnt_internal_identifier_insert();

This works fine of course:

insert into data(name) values ('some value');

select * from data;
select * from ids;
|id|name      |
--------------
|1 |some value|

select * from ids;
|id|
----
|1 |

Some perf metrics: Doing the insert of 10m rows to the data table without the trigger takes ~24s on my system. With the trigger enabled, it takes ~52s. I guess this is because its doing 2x the number of inserts shrug.

I tried creating a 'sync' statement:

insert into ids(id)
select d.id from data d
left join ids i on d.id = i.id
where i.id is null;

Total time ~22s (~13s for the initial insert, ~9s for the 'sync').

My issues are as follows:

Over time both tables are expected to become large (>1bn rows) and doing a left join in the sync function is going to spill to disk. Its not restricting the records in the data table to ones that have been inserted, its checking all of them every time. However, the operation is batch based.

The trigger approach is firing on ever row insert, I don't know how I can write the trigger to function on a statement level.

Are there any other techniques I can follow up? Would partitions help? Indexes?

Of course in the full production system the ids columns will be primary keys. I left this out of the example code, but I've seen performance improvement by scripting out the constraints before the inserts, and then applying them again.


texts:
I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.

Given that sysprocesses is deprecated, I'm curious why anyone would continue to use it, particularly MVPs.  Is it just that using sysprocesses is less hassle than joining three DMVs or is there a better reason for using it?

The two blog posts I mentioned were:

Adam Machanic: Smashing a DMV Myth

Tim Chapman: Find blocking processes using recursion in SQL Server 2005


texts:
I've been searching SE forums in order to solve that, however I wasn't able to find out where I was wrong.

In my DB I want to link in a strong way a parent table to a child table : to do so, I want all the child table rows that reference the parent to be deleted when the parent is deleted.

My app is not in production, it uses MariaDB, and PHP mysqli-like functions. All the tables use InnoDB engine. The keys involved (parent primary, and foreign key) belong to the same type.

Here is how the install script creates the tables (simplified). The rows are simply concatenated in order to produce a cleaner view of the query. PHP $ are not shown here.

Parent table :

       "CREATE TABLE Campagne( ".
       "id_campagne INT(3) NOT NULL AUTO_INCREMENT, ".
       "nom VARCHAR(100) NOT NULL, ".
                ...
       "PRIMARY KEY (id_campagne),".
       "FOREIGN KEY (utili_num) REFERENCES Utilisateur (utili_num)) ENGINE=INNODB;"

Child table :

       "CREATE TABLE ChildTable ( ".
       "id_child INT(9) NOT NULL AUTO_INCREMENT, ".
                ...
       "id_campagne INT(3) NOT NULL, ".
       "CONSTRAINT `FK_campagne_id_campagne` FOREIGN KEY (`id_campagne`) REFERENCES Campagne (`id_campagne`) ON DELETE CASCADE ,".
       "PRIMARY KEY (id_donnee)) ENGINE=INNODB;"

First of all, I can't use PHP my admin to make a quick delete of a Campagne row (no row is affected when I click on delete). When I use the SQL console, to do something like "DELETE FROM Campagne WHERE id_campagne=3" the corresponding row is properly deleted, however this has no effect on the child rows from the child table that have the matching foreign key.

What am I doing wrong ? Thanks for reading


texts: Oracle generated column aliases different on two DB's

texts:
We have a legacy application we cannot modify that connects to Oracle 11g and at some point runs a query and returns a result. The application however is using the "generated" column name from Oracle to read the result.

Consider the following query:

select nvl(1,0.0) from DUAL;

As this query does not specify an alias, the generated column name would be "nvl(1,0.0)"

However on another server the generated column name is "nvl(1,0)" (notice 0 and not 0.0) and the application fails.

Is there a configuration that can be changed for Oracle? I've searched for formatting and locale configurations and they are equal on both servers.

Any help would be appreciated


texts: Does "use AWE to allocate memory" do anything on a SQL Server 2008 R2 64bit Windows 2003 system?

texts:
After checking the box I noticed that queries that would timeout after 30 seconds would complete execution.

The TechNet article says "AWE is not required and cannot be configured on 64-bit operating systems."

So did checking the box do anything?

SQL Server 2008 R2 / Version 10.52.4000.0


texts: Query Last Value in Fixed Length Array With Empty Values

texts:
I have a table that generally looks like:

Name varchar       | Location varchar[]
---------------------------------------------
Lemoney    | {Home,Work,Home,Stack Exchange,,,,,,}
Other Dude | {Home,,,,,,,,,}
Busy Dude  | {Home,Work,Work,Work,Home,Work,,,,}

I have been trying to write a query that will return the Last Value for each Name like:

Lemony     | Stack Exchange
Other Dude | Home
Busy Dude  | Work

I haven't been able to figure out how to return that yet. Does anyone have any ideas?


texts: Killed wrong session and cannot connect to oracle anymore

texts:
I guess I killed the wrong session. Trying to connect with SQL Developer, it says ORA-12505.

Search results say you may set wrong SID, but that's not for my case.

How can I get my oracle back?

My web service is also down with same error.


texts:
I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server 2012), so no other process is going to disturb it.

Currently, I have  default settings on my MySQL instance - the only setting I've changed is a mode when you install it - "standalone" mode, if I remember correctly.

Which settings should I change? I am mostly interested in giving MySQL as much resources as it can take - I really want to give it everything possible.

Can you point me to some settings, manuals, etc.?

I will be testing on multiple hardware configurations (from commodity hardware (starting with i7-based laptop) to Gen9 HP Servers with "top-notch" disks (througput > 1GB/s)).

I will be testing on MySQL 5.7 and InnoDB engine.

I am going to test some SQL statements on a fairly big dataset (2-3 15+ million rows tables). I will be testing (BULK) INSERT, SELECT, UPDATE and DELETE statements.


texts: "dateformat" independent form of datetime string in query for SQL Server?

texts:
I am unfamiliar with SQL Server, and would like to do some ad-hoc searches in a dockerized legacy SQL Server database using SQL Server Management Studio where one of the fields is a datetime and I would like to use the BETWEEN operator to extract for a given period.
So something like
/****** Script for SelectTopNRows command from SSMS  ******/
select card, sum(amount) as sum, count(*) as count
  FROM ... where cardused between '01-03-2021' and '03-03-2021' group by card

Now my problem is that this is interpreted as MDY dates and not DMY dates (which is what we use here).  Server is TZ'ed to Europe/Copenhagen.  My Windows 10 speaks Danish.  If I run set dateformat dmy; first it is interpreted as expected.
Is there a documented format that always interprets correctly when used in a query string regardless of server settings?
Based on earlier work with Excel this might be something like
'2021-03-01 15:36'

to get this in my local timezone.  Server and client will both run in the same timezone eventually - would this be an issue?


texts: Best practice for managing multiple Postgres DBs with FDW

texts:
Requirements: our application (Django app, let's say) has a Postgres instance with a single DB (main_db). For every new account that signs up on the site, a new DB should be created within this DB instance (db_a for user a, db_b for user b, etc). However a single table in main_db (public.shared_table) should be accessible to all other DBs. Two (not great) options I can think of:

Use FDW. It would work, but it implies that the Django app must use a postgres superuser account in order to CREATE SERVER, etc for each new DB created. Exposing a superuser account to the app is not great.
Have a separate table in each DB and programmatically sync the data over. Also bug-prone and not great.

Is there another option I'm not considering?


texts: How to get the next auto-increment id in MySQL?

texts:
How to get the next id in mysql to insert it in the table ?

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("chaminda", id))


texts: How can I design a database to track each individual's stat changes weekly, in a group?

texts:
There is a group of people, and I need to track each person's individual numbers weekly such as their weight lost, steps walked, and bad foods eaten.  I plan on implementing this database into highcharts models where they can view their progress in a visual manner, further motivating them to keep doing well.

The thing is,  how can I do this?  Initially, I was thinking of creating a table named Person, and include their name, age, steps walked, weight loss etc.  But if I were to update the steps walked, it would overwrite the prior steps walked data instead of adding a separate instance. If I do one table per stat, that seems kind of inefficient as I would end up with 52 tables per even 10 people, thats 520 tables!

I hope this makes sense, as it is a little difficult for me to wrap my head around the total scope of it to see what is needed...any help is MUCH appreciated!

Enclosed is what I kind of mean


texts: mysqlbackup mysql enterprise utility issue

texts:
I recent took a backup using mysqlbackup.

While restoring it, I noticed that the files that were copied into datadir are with root:root (user:group) instead of mysql:mysql.

Is anything wrong with taking backup or what?....


texts: Is it possible to disable new version checking in pgAdmin 4?

texts: New versions of pgAdmin 4 are being released every month, and it's so annoying to see a popup with notification about another update. There's no option in preferences to disable it. Is there a trick to disable this particular popup, except installing the new version?


texts: Oracle 12: Using compound trigger to update data in same table on each insert/update

texts:
I need some help fixing the code of a compound trigger.
I have a table with two columns that are used for auditing (upd_by and upd_time) and I'm trying to create a trigger that would update these columns on each insert/update to the table.
The table is:
CREATE TABLE customer_info(
    id NUMBER NOT NULL,
    name VARCHAR(50) NOT NULL,
    last_upd_by VARCHAR(25) NOT NULL,
    last_upd_time TIMESTAMP NOT NULL,
    PRIMARY KEY(id)
);

I found some examples on using compound triggers, and this is the code I have so far:
CREATE OR REPLACE TRIGGER customer_info_trg
    FOR UPDATE OR INSERT ON custommer_info
    COMPOUND TRIGGER
    TYPE r_customer_info_type IS RECORD (
        id   custommer_info.id%TYPE
    );

    TYPE t_customer_info_type IS TABLE OF r_customer_info_type
        INDEX BY PLS_INTEGER;

    t_customer_info   t_customer_info_type;

    AFTER EACH ROW IS BEGIN
        t_customer_info (t_customer_info.COUNT + 1).id := :NEW.id;
    END AFTER EACH ROW;

    AFTER EACH STATEMENT IS BEGIN
        FOR indx IN 1 .. t_customer_info.COUNT
        LOOP
            UPDATE customer_info
            SET last_upd_by = USER, last_upd_time = SYSTIMESTAMP
            WHERE id = t_customer_info (indx).id;
        END LOOP;
    END AFTER EACH STATEMENT;
END;

But when I run update on the table I get ORA-04098: trigger 'customer_info_trg'k is invalid and failed re-validation


texts: I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB?

texts:
FYI: I don't believe this is a duplicate question, I've looked and most are general questions with vague answers. See more specifics below:

Currently a database I am in charge of locks up every once in awhile to a state that becomes unresponsive, and I need to manually restart the MySQL server to make it become responsive again (Yuck). I believe the culprit is an UPDATE query that is being run, which is being called every 1 minute for each active user in the system.

The query is something like this:

UPDATE logging_table SET log_time = log_time + 60 WHERE id = X;

The table is not small (approx 0.5 million records) and is both updated and selected from quite often; it is a "Main operations" type table that is related to quite a few other tables (No FK's, just relations in the PHP framework we are using). The monitoring app I use shows the above query consuming about 40% of the overall database time in our app, followed by a SELECT query that gets information from that table.

Currently the table is MyISAM as it worked fine for a lower number of users, but as the active users participate during peak times of the day, the database locks up.

Does this sound like a table I should convert over to InnoDB (The rest of the tables were setup as MyISAM by default)? I should note that it is not feasible at this point to split this table so that frequent updates can happen on another table.

Thanks for any suggestions.


texts: Oracle: procedures+functions code base not overlapping package body code base

texts:
I intend to find all code in my database containing some string.
I run a query such as:

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text) LIKE UPPER(‘%string%’);

and a lot of results, let's say 100 results, contain 'PACKAGE BODY' on the TYPE column. Because
a package body is too large, I want to get only the name of the procedure/function containing that text.

So I add the following clause

AND TYPE IN ('PROCEDURE', 'FUNCTION')

now i get only a few results, let's say 10 results, and I am confused.

Since a package body can contain only functions and procedures (apart from package constants), if 100 packages contain the text 'abc', how can only 10 procedures contain the same text? Text 'abc' withing a package can only fit within a procedure or a function, so if there are 100 occurences withing packages, there must be at least 100 occurences within functions/procedures.

I would need some explanation of this phenomena and, more importantly,I would like to know how can I narrow the search of text, to get the  name of the containing procedure/function, instead of the parent package?

(a colleague suggested that some procedures are only imported, but not compiled; could this be a cause?)


texts:
First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data.  (Hundreds of millions of records)  The current implementation is a very simple data warehouse that was created long ago in Oracle.  The existing table has no primary key but each record is unique.  The table is indexed by the first two columns listed below.

The data itself is fairly simple:

device – there are multiple devices with a unique number indicator
data generation time – each device generates a set of data multiple times a day at different, random times.  Each data set covers multiple days. Sample times for individual data points in the set can be up to every second.  For times prior to the data generation time, these are measured results.  For times after the data generation time, these are the device’s prediction of what the data will be.  Queries are often pulled for a full day that compare measured verses predicted data for a given device (e.g. how well did the device predict future needs)
Date/Time of the data points
Data point 1
Data point 2

.
.
.
Data point 23

The major types of queries are:

Give me the latest data generated by a device
Give me all the data for a device for a given day (as previously described above.)
Give me the data generation times for a device on a given day

My idea to speed up queries would be to split the table up into two tables as follows:

MetaData Table (each of the first 3 will be indexed)

device
data generation time
day – This would be a new, indexed data point
Primary Key – a number with the device number, data generation time (141230073205 – for 2014 Dec 30 07:32:05), and day (150102 – for 2015 Jan 02)

Data Points Table (There will be 10s of thousands of these for each entry in the MetaData table above)

Foreign Key – that points to the Primary Key in the above MetaData table for which this particular point is valid
Date/Time of the data points
Data point 1
Data point 2
.
.
.
Data point 23

So, long story short (too late!):

Is this a valid approach to speeding up queries?
Is there a better way to organize the data?
What other things can I do to cut down on the query times?
Any sqlplus coding tips would also be greatly appreciated.