texts: sqlcmd returning literal 'NULL' for null columns when outputting to CSV

texts:
I'm using sqlcmd (SQL Server 2008 R2) to execute a simple query to output data to a .csv file. In the SQL file, I have the following code:

USE [OSCAR_UK]
SELECT DISTINCT ISNULL(LTRIM(RTRIM(il.[LifeInsuredFirstName])),'') AS [First Name]
  ,ISNULL(LTRIM(RTRIM(il.[LifeInsuredSurname])),'') AS [Last Name]
  ,CONVERT(varchar(26),il.[DateOfBirth],23) AS [Date Of Birth]
  ,ISNULL(LTRIM(RTRIM(il.[Gender])),'') AS [Gender]
  ,ISNULL(LTRIM(RTRIM(il.[PostCode])),'') AS [Postcode]
  ,ISNULL(LTRIM(RTRIM(ce.[PolicyNumber])),'') AS [Policy Number]
FROM [dbo].[Insured_List] il
LEFT OUTER JOIN [dbo].[Cession] ce
ON il.[pk_Cession] = ce.[pk_Cession]

My problem is that any field containing a NULL value is returned to the .csv file as the string literal 'NULL' rather than an empty string, despite my use of ISNULL to trap that condition.

My searches so far have really only found people complaining that it all worked OK in SQL Server 2005. Have I missed something simple? If anyone could point me towards a research source that would be very kind.

Thanks in advance.

EDIT: It appears that the problem occurs only when the output is redirected to a file, not if it is allowed to simply appear on-screen. I'll have to do some sort of global replace after the fact. It's a pain but if that's the way it is, it's the way it is.


texts:
We run SQL Server 2000 and we get a few of these errors every night.

Could not continue scan with NOLOCK due to data movement

The query that throws this error is a large complex query that joins over a dozen tables.  Our underlying data can be updated frequently.

The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints.

Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :)

PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008


texts:
I created a new SSIS package within Visual Studio 2013 with SSDT BI Tools (12.04.2430.0) installed bu when looking into this package I can see that the DTSPackageType is set to DTSDesigner100. Based on this documentation that means it was created in SSIS Designer in SQL Server 2008!!

I would have expected that the package was created with SSDT for SQL Server 2012

Did I miss a setting or what is the logic behind this behaviour?


texts: Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

texts:
I am currently working with a SQL Server 2008 R2 database which uses the following SET commands frequently:

SET ANSI_NULLS { ON | OFF }
SET QUOTED_IDENTIFIER { ON | OFF }
SET ANSI_PADDING { ON | OFF }

While I was researching these settings I stumbled across the following note for ANSI_NULLS on Microsoft Technet (Link):

  In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Because of this I would like to enforce a "global" setting. I have been searching for the better part of a day but as I understand it you can always change these settings. To be completely sure I would like to ask here:

Is it possible to either enforce a setting like ANSI_NULLS ON or to disable the possibility of changing these settings on "any" level?

I would like to use this to test if the database would "survive" if the ability to set a specific option to OFF is disabled.

I hope I was able to describe my problem. This is not my first language.

Thanks in advance.


texts: index for OR operator with different columns

texts:
What would be best index for this kind of query:

-- id is the PK
create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null);

select id from test3 where id=:1 and (name=:2 or lastname=:3);

Would that be depends on the number of distinct values of each column(in that case the index would be on the more distinct column)?
What if we cant tell what is the nature of the data..?


texts: Differing AUTO_INCREMENT values between 5.1 vs 5.6 slaves

texts:
I have a MySQL 5.6.21 slave and a MySQL 5.1.73 slave that are both replicating from the same MySQL 5.1.73 master.

I noticed that for several of the tables ( I said several because when I try to rerun the replication test, I noticed that different tables could get affected, so this is not exactly deterministic ) in the 5.6.21 slave's database has AUTO_INCREMENT values that are slightly off from that of the 5.1.73 slave. However, both tables have the same exact number of rows and there are no row differences ( verified using mysqldbcompare ).

For example, when I do SHOW TABLE STATUS for the same table on both the 5.1 slave vs 5.6 slave, I would see this on 5.1:

Engine: InnoDB
    Version: 10
     Row_format: Compact
       Rows: 861
 Avg_row_length: 95
    Data_length: 81920
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 7290
    Create_time: 2014-10-29 18:45:44
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
    Comment:
1 row in set (0.00 sec)

and then, 5.6

     Engine: InnoDB
    Version: 10
     Row_format: Compact
       Rows: 981
 Avg_row_length: 83
    Data_length: 81920
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 7289
    Create_time: 2014-10-29 18:45:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
    Comment:

Notice that 5.1 has an AUTO_INCREMENT value of 7290 while 5.6 has a value of 7289

Is there something different in how 5.6 replicates ( in comparison to 5.1 ) that causes the AUTO_INCREMENT values to be different.

Thanks


texts:
I am currently trying to design a table structure that can capture report data (from various sources) in an ordered fashion.

Let me give you an example:

Each day I receive three separate reports from various companies (one report per company). These reports are a dump of their database tables and include PK, FK to previous report row (of another company), Amount, CustomerID, Timestamp and some other unrelated fields.

The order of the reports is very important. The three reports create a chain that I am interested in (query later). Each record only knows about its immediate predecessor (FK to previous report row) and is NULL if it is the root record.

The problem I am running into is the fact that for certain chains, the length of the chain can be 5 reports, others are 3. I'm wondering if it is possible to design a Chain(?) definition table somehow and reference a chain id somewhere in the record so that the record knows which part of the chain it belongs to.

The previous developer created a table per report and had humongous amounts of business logic to tie report chains together. I would like to abstract this design so that the tables are agnostic to the reports and have one to four tables instead of fourty-seven.

Once everything is stored, I would like to query a specific master record and see all children records and their amounts.

I read about self referencing tables, but I am not quite sure if this is the right way to go.

SQL: SQL Server 2008R2


texts:
I asked an earlier question about modeling  MySQL data modeling help. The recommendation was the diagram labeled Multiple Tables for Role and Sponsor.

Now I'm trying to write the queries and struggling. It's the point where I kept giving up and looking at maybe easier database models, but now I know that won't work and I need some advice.

When I have an event_id and a user_id, I need to check whether this leader is allowed to edit this event. They are allowed if

Are the user_id and event_id is in the user_event_role table?
If this event also has a project_id, is the user in the user_project_role table for the project that runs this event?
Is the user in the user_organization_role table for the organizations that sponsor this project (check the project_sponsor table)?
Is the user in the user_organization_role table for the organizations that sponsor this event (check the event_sponsor table)?

If this user_id is found in any of these leader positions for the event, project, or organizations sponsoring the event or project, then it's okay for this user/leader to edit.

I'm not sure how to write such a complicated query. The following query insists that the user_id be present in all three user_role tables when it only has to be present in one.

SELECT count(*)
FROM user AS u
JOIN user_event_role AS uer ON (uer.user_id=u.id)
JOIN user_project_role AS upr ON (upr.user_id=uer.id)
JOIN user_organization_role AS uor ON (uor.user_id=upr.user_id)
JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
JOIN project_sponsor AS ps ON (ps.project_id=upr.project_id)
WHERE
  uer.term_to >= CURRENT_DATE() AND
  upr.term_to >= CURRENT_DATE() AND
  uor.term_to >= CURRENT_DATE() AND
  u.netid='{$netid}'

Do I need to do something as complicated as this union:

  SELECT count(*)
  FROM user AS u
  JOIN user_event_role AS uer ON (uer.user_id=u.id)
  WHERE
    uer.event_id=$event_id AND
    uer.term_to >= CURRENT_DATE() AND
    uer.role != 'volunteer' AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_project_role AS upr ON (upr.user_id=u.id)
  JOIN event AS e ON (e.project_id=upr.project_id)
  WHERE
    e.id=$event_id AND
    upr.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
  WHERE
    e.id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN project_sponsor AS ps ON (ps.organization_id=uor.organization_id)
  WHERE
    uer.event_id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'

Is this the best approach? Is there a way to simplify it?


texts: How do I save result of a join to another table?

texts:
In PostgreSQL I can save result if SELECT to another table with SELECT INTO:

SELECT id INTO new_record_ids FROM new_table
  EXCEPT
    SELECT id FROM old_table
ORDER BY id;

However this doesn't work for for joins:

-- doesn't work:
SELECT * INTO new_records FROM new_table
  JOIN new_record_ids ON new_record_ids.id = new_table.id
ORDER BY new_table.id;

The join itself is correct:

-- works:
SELECT * FROM new_table
  JOIN new_record_ids ON new_record_ids.id = new_table.id
ORDER BY new_table.id;

My question is: how to save joint table to another table?


texts: Saving SQL Server backup file in multiple parts

texts:
I am taking daily backups of a SQL Server database. Right now the .bak file is about 2GB and it is growing day by day. There is a scheduled job is running which moves this .bak file from one location to another.

Is there a way to save the .bak file in chunks - like part1.bak, part2.bak etc..

so it will be much easier to move small data and merge at destination?


texts: Select total number of records from common table expression

texts:
We are using common table expressions to temporarily save the search results.

  WITH Results (
                 ------
                  )
  AS
    (
          select ---
        )

 SELECT * FROM Results

        WHERE Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount

Here i want to get the toatal number of records in the Results common table expression in a variable @RowCount. Is there any way to get it without using table variable or temp table.


texts: PostgreSQL: why altering default privileges in specific schema to revoke all privileges on types from PUBLIC does nothing?

texts:
The question can pretty much translate into: why the following PostgreSQL command does nothing?

ALTER DEFAULT PRIVILEGES
    FOR ROLE ownerrole
    IN SCHEMA myschema
    REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;

It is a valid command which is executed successfully but it has no impact. Indeed, USAGE privileges is still granted to PUBLIC on domains created after the command has been executed. Nothing changed and the PostgreSQL default privileges still apply:

  PostgreSQL grants default privileges on some types of objects to PUBLIC. […] the default privileges granted to PUBLIC are as follows: […] USAGE privilege for languages and data types (including domains).

Source: https://www.postgresql.org/docs/current/static/sql-grant.html

Testing

Setup

Create roles as a superuser

CREATE ROLE ownerrole WITH INHERIT LOGIN PASSWORD 'ownerrole';
CREATE ROLE approle WITH INHERIT LOGIN PASSWORD 'approle';

Create database test1 as a superuser

CREATE DATABASE test1 ENCODING = 'UTF8' OWNER = ownerrole;

Create database test2 as a superuser

CREATE DATABASE test2 ENCODING = 'UTF8' OWNER = ownerrole;

Test 1

Connect to test1 as ownerrole and execute these commands:

CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO ownerrole;
GRANT ALL PRIVILEGES ON SCHEMA myschema TO approle;

ALTER DEFAULT PRIVILEGES
    FOR ROLE ownerrole
    IN SCHEMA myschema
    REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;

CREATE DOMAIN myschema.comment AS text;
ALTER DOMAIN myschema.comment OWNER TO ownerrole;

Now, connect to test1 as approle and execute the command:

CREATE TABLE myschema.table(
    comment myschema.comment
);

Unfortunately, this execute successfully and a check with psql \ddp command shows nothing:

$ psql -d test1 -h localhost -p 5432 -U ownerrole
psql (10.3 (Ubuntu 10.3-1.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test1=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

test1=> \q

Test 2

This new test is identical to the first one except that the ALTER DEFAULT PRIVILEGES is not specific to any schema.

Connect to test2 as ownerrole and execute these commands:

CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO ownerrole;
GRANT ALL PRIVILEGES ON SCHEMA myschema TO approle;

ALTER DEFAULT PRIVILEGES
    FOR ROLE ownerrole
    REVOKE ALL PRIVILEGES ON TYPES FROM PUBLIC;

CREATE DOMAIN myschema.comment AS text;
ALTER DOMAIN myschema.comment OWNER TO ownerrole;

Now, connect to test2 as approle and execute the command:

CREATE TABLE myschema.table(
    comment myschema.comment
);

This time, the command fails as expected

ERROR:  permission denied for type myschema.comment
SQL state: 42501

And a check with psql \ddp command shows the altered default privileges:

$ psql -d test2 -h localhost -p 5432 -U ownerrole
psql (10.3 (Ubuntu 10.3-1.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test2=> \ddp
             Default access privileges
   Owner   | Schema | Type |   Access privileges
-----------+--------+------+-----------------------
 ownerrole |        | type | ownerrole=U/ownerrole
(1 row)

test2=> \q


texts: how to use with clause results as a temporary table

texts:
+----+-----------+--------------+-----------+
| id | file_name |  file_value  | file_type |
+----+-----------+--------------+-----------+
|  1 | file1     |  8,624       | 1         |
|  2 | file2     |  0,025       | 1         |
|  3 | file3     |  1,005       | 1         |
|  4 | file4     |  0,655       | 2         |
|  5 | file5     |  1,251       | 2         |
|  6 | file6     |  0,148       | 2         |
|  7 | file7     |  1,146       | 3         |
|  8 | file8     |  2,485       | 3         |
|  9 | file9     |  0,512       | 3         |
+----+-----------+--------------+-----------+

+--------+
| lineal |
+--------+
; WITH vw AS
(SELECT id, UPPER(file_name) [Name], CAST(file_value AS INT) [Value]
 FROM test_example
 WHERE id % 2 = 0 )

SELECT vw2.* , vwt.* FROM vw AS vw2
CROSS APPLY (SELECT SUM(vw.file_value) AS type_total FROM vw AS cavw
             WHERE vw2.file_type = cavw.file_type ) AS vwt ;

+----------------+
| this IS faster |
+----------------+
; WITH vw AS
(SELECT id, UPPER(file_name) [Name], CAST(file_value AS INT) [Value]
 FROM test_example
 WHERE id % 2 = 0 )

 SELECT * INTO #example FROM vw --or @example AS table...

SELECT vw2.* , vwt.* FROM #example AS vw2
CROSS APPLY (SELECT SUM(vw.file_value) AS type_total FROM #example AS cavw
             WHERE vw2.file_type = cavw.file_type ) AS vwt ;

Any way to optimize speed for firt query?


texts: MySQL cluster Replication and Failover via docker-compose

texts:
I am using docker-compose to create MySQL cluster (NDB). After cluster gets created. This is my cluster configuration:
ndb_mgm> show
Connected to Management Server at: mysql-manager-1:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11   @172.xx.0.5  (mysql-5.7.25 ndb-7.6.9, Nodegroup: 0, *)
id=12   @172.xx.0.8  (mysql-5.7.25 ndb-7.6.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @172.xx.0.2  (mysql-5.7.25 ndb-7.6.9)
id=2    @172.xx.0.3  (mysql-5.7.25 ndb-7.6.9)

[mysqld(API)]   2 node(s)
id=21   @172.xx.0.4  (mysql-5.7.25 ndb-7.6.9)
id=22   @172.xx.0.7  (mysql-5.7.25 ndb-7.6.9)

When I bring down one of the SQL nodes, the cluster becomes unreachable.
E.g when node with id=21 is shut down, data should still be accessible from node id=22 because all other data nodes and management nodes are up and running.
Is this behavior supported out of the box or I have to make some configuration change for it?
I have gone through the MySQL documentation.
Am I referring to the correct documentation?
They advise executing the steps on the host machine but in my case each node is deployed as a separate docker container.  How do I achieve this failover in my case?


texts: Only backup drive and differential backups

texts:
Is it possible to restore the differential backup without a full backup, but having backup of the entire hard disk?

Example:

2017-01-01: DIFF backup
2017-01-02: entire hard disk backup (NO FULL backup)
2017-01-03: DIFF backup
2017-01-04: DIFF backup

Any suggestions?


texts:
I have a weird issue and i can't find the fault. I have a MariaDB table with 4 rows

[id] [clientid] [lastcheck] [Version]

The id field is set to AutoIncrement. Everything is working fine with my script for adding and updating values

INSERT INTO $dbtable_update
    (clientid, lastcheck, currentversion)
VALUES
    ('$clientid', '$lastcheck', '$currentversion')
ON DUPLICATE KEY UPDATE
    lastcheck = '$lastcheck',
    currentversion = '$currentversion'

But as soon as I update values on an existing entry, the AutoIncement is counting the id row. Can someone help me out, what I am doing wrong?

Note (from Comments):  There is UNIQUE(clientid).


texts: module_end extended event enough for stored procedure logging?

texts:
Using extended events I plan to capture stored procedure executions particular the time of execution, duration and who executed it.

Is the module_end event enough to capture this data? In a test session I have created I don't see what more the module_start event would provide though would a module_end event occur if a stored procedure execution is terminated?


texts: Centos 5.11 mysql-server 5.0.95 upgrade to mysql55w-server

texts:
Here's my thought process on my upgrade:

mysqldump --all-databases > backup.sql

service mysqld stop

yum remove mysql mysql-server

yum install mysql55w mysql55w-server

service mysqld start

mysql_upgrade

theoretically, I should be good after that right? Anything I'm missing here? I'm scared to death of this upgrade. All the ones I've been reading about are from 5.1 to 5.5, not 5.0.95.

Can someone confirm/deny I'm going about this ok, or am I missing some huge steps?


texts: INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

texts:
I am trying to write my first trigger.  It is an insert trigger as follows:

<b>Fatal error</b>:  postgres7 error: [-1: ERROR:  INSERT has more target columns than expressions at character 78

INSERT INTO
dash_pre_queue
            (ord_obj_guid,
             ord_entry_type_guid,
             client_obj_guid,
             installer_obj_guid,
             installer_serial_number,
             datetime,
             ord_proc_state_guid,
             created,
             ord_esc_type_guid,
             in_error,
             ord_entry_guid)
SELECT
    (ord_obj_guid,
            NULL,
            client_obj_guid,
            inst_obj.inst_obj_guid,
            inst_obj.installer_serial_number,
            clock_timestamp(),
            ord_proc_state_guid,
            NULL,
            NULL,
            NULL,
            NULL)
    FROM
            ord_obj
    JOIN
            inst_obj ON inst_obj.inst_obj_guid = ord_obj.inst_obj_guid
    WHERE
            ord_obj.ord_obj_guid = NEW.ord_obj_guid

I left the error message on top.  The structure of the target table matches the INSERT INTO columns.  I have verified the NULL values are allowed.

If you count the columns in the Insert clause, they match the SELECT statement, yet, I get the error above stating that there is somehow a disconnect.  Any ideas as to what I am doing wrong?


texts: Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck

texts:
We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10.

Database is running on a server with 4GB total memory. When the script is not running the total available memory is around 200MB. When the script is running it can go down to 160MB.

We have a script that most of the time is able to complete on smaller operations but gets stuck because of a query / queries that's being awaited and doesn't finish on larger operations.

The queries that don't finish have a state of 'active' when we do a select on pg_stat_activity. Other connections from the same pool (based on client_addr) have a state of 'idle'.

The node script running these queries does not appear to have become unresponsive when this happens, (we will verify this as it has a message client in it).

In the node script there is a query that fetches 1000 records and these records are iterated over with some logic executed on each one.  The loop does a progress update after every 500 records processed and then fetches another 1000 records.  This is done to process large numbers of records. Eg; 2 - 3 million of records.

The progress updates are done as async database calls that consist of one update and one select.  The update query is asyc/awaited but the select is just async fire and forget, and then loop continues.

We are seeing that when we process a large batch of records that some of the two update/select queries for progress get stuck at the same time. A select query and an update query on the same table. The update is for updating one of the progress columns, and the select computes total progress that is then sent to the client.

Sometimes it happens on the maindata table while doing a select of the 1000 records to be processed but that doesn't happen often.

Sometimes when there is a query that gets stuck we also see autovacuum: VACUUM tablename in pg_stat_activity. And usually once that autovacuum disappears, one
 pair of select,update query gets to finish and the script it is running in runs to completion. BUT not all pair of select,update or other query not related to the table finishes.

Also, after a query(s) is stuck, we can still run select/update/insert queries on that same table from another node script without issue.

We have no idea what is causing this or how to identify/fix the issue.
Any help would be much appreciated.

Thank your for your time.

Update:

Screenshot of pg_stat_activity showing stuck select statement and all rows in pg_locks. Couldn't get the select,update combo that get stucks; for now seems to get eventually unstuck.

Select progress query:

select
    ((tn.progress1 + tn.progress2 + tn.progress3) * 100 / (3 * 100)) total_percent
    ,concat(
            'Total Progress: ', ((tn.progress1 + tn.progress2 + tn.progress3) * 100 / (3 * 100)), E'\n',
            'Status: ', E'\n', tn.message, E'\n',
            E'\n\n'
    ) final_message
    ,(case coalesce(tn.state,0) <> [ERRORCODE] when TRUE then 0 else 1 end) error
from table tn
where
    tn.id = [id]
    and tn.guid1 = [guid1]
    and tn.guid2 = [guid2]
group by
    tn.progress1, tn.progress2, tn.progress3, tn.message, tn.state;


texts: Getting DPM Alerts for Failed Recovery Point for Database that has Been Deleted

texts:
Deleted a defunct database in SharePoint, now getting alerts from DPM saying:

The following alert occurred at 2/16/2016 12:16:54 PM.

Status: Active
Data source type:
Data source: SharePointServer\Database_I_Just_Deleted
Computer: SharePointServer
Description: Last 1 recovery points not created.

For more information, open DPM Administrator Console and review the alert details in the Monitoring task area.

Cannot find where to disable monitoring of this deleted database.


texts: MySQL Replication With single Master DB to multiple Slave DBs

texts:
I am creating the MySQL master-slave replication on CentOS 7 with MySQL 5.6

I have one Database like DBMaster on the Master server and multiple Database on the slave like DBSlave1, DBSlave2 and more. All the tables are same into the Master and Slave Databases.

I want when ever any record insert, update or delete in the Master DB tables that replicated to all the Database of slave.

For example: I have table name like student in Master and slave Databases. When ever I insert data into the student table in Master Database it will be replicated into the all the Databases student table of slave.

I have already got success in single Database and same name on Master and slave server.

Below are the configuration files details of both the server:

Master server's my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave Server's my.cnf

[mysqld]
server-id=2
replicate-wild-do-table=DBSlave%.dbtb1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Database details given below:

Master Server

create database DBMaster;
create table DBMaster.dbtb1(name varchar(100));

Slave Server

create database DBSlave1;
create table DBSlave1.dbtb1(name varchar(100));

create database DBSlave2;
create table DBSlave2.dbtb1(name varchar(100));

AS you can see the Database name on both the sever are different for that I have use the below statement into the slave server for replication.

replicate-wild-do-table=DBSlave%.dbtb1

But when I try to insert data into the Master Data base server I got the below error or slave server status:

Error 'Table 'DBMaster.dbtb1' doesn't exist' on query. Default database: ''. Query: 'insert into DBMaster.dbtb1 values ('Punu')'

Please tell me what is wrong in the configuration. I am trying this this first time.


texts: Accessing the insert statement which triggers a trigger inside the trigger's body

texts:
I have some table and I have enabled auditing on this table. Currently, I am collecting the data about who has inserted records and at what time. Now I also want to log what was the exact query he/she used to insert the record. I created a trigger which at present only logs the user and date to audit data collection table. This is code to the trigger:

create or replace trigger auditer
after insert on user01.sometab
for each row
insert into sys.auditlogs values(user,sysdate)

How can I use the same trigger to access the insert statement and insert that statement into the audit table? Is it even possible?


texts:
I'm trying to figure out what is happening but at this point I cannot troubleshoot this issue...

I have 2 servers with same specs, actually also with almost same users connected (Only me)...

One of the servers is stable at 20% CPU usage while the other is at 1%... Same Databases, same Processes...

How I can know what is actually consuming this high amount of CPU on SQL Server compared to the other server if both of them are idle?


texts:
I have a table in a MariaDB database with ~700,000,000 rows. I have indexed some columns of it.

My question is whether my DB behaves "normaly" or not - because I have not had previous experience with such an amount of data.

Take a look at these queries and please let me know if you think this is normal response time:

SELECT count( DISTINCT(QualityLevel) ) FROM patient_records;
+---------------------------------+
| count( DISTINCT(QualityLevel) ) |
+---------------------------------+
|                          265595 |
+---------------------------------+
1 row in set (1.248 sec)

This I think is great response time. However, look below:

select count(QualityLevel) from patient_records where QualityLevel>10.14;
+---------------------+
| count(QualityLevel) |
+---------------------+
|           700756562 |
+---------------------+
1 row in set (3 min 10.324 sec)

Not so great (I assume?). And, more:

select count(recordID) from patient_records where QualityLevel>10.14 and snpID='.';
+-----------------+
| count(recordID) |
+-----------------+
|        56627747 |
+-----------------+
1 row in set (23 min 53.028 sec)

Quite slow this one, although both columns (QualityLevel and snpID) are indexed.

I will need to build a web interface for these queries, but it can't happen that it takes 25 minutes to execute. What am I missing here? Maybe DB partiotining would help (don't know anything about it, so any suggestion would be highly appreciated).

My my.cnf file:

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/home/ssd/mysql_datadir/mysql.sock
innodb_buffer_pool_size=4GB
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256k

[client]
port=3306
socket=/home/ssd/mysql_datadir/mysql.sock

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

My SHOW CREATE TABLE is:

patient_records | CREATE TABLE `patient_records` (
  `recordID` int(11) NOT NULL AUTO_INCREMENT,
  `MRD_sample_FORMAT_id` int(11) NOT NULL,
  `DataType` char(1) NOT NULL,
  `SequencingOrigin` char(1) NOT NULL,
  `ChrNo` varchar(2) NOT NULL,
  `ChrPos` int(10) unsigned NOT NULL,
  `snpID` varchar(20) NOT NULL,
  `NuclREF` varchar(500) NOT NULL,
  `NuclALT` varchar(3000) NOT NULL,
  `QualityLevel` float NOT NULL,
  `FilterString` char(1) NOT NULL,
  `InfoString` varchar(1000) NOT NULL,
  `GT` varchar(20) DEFAULT NULL,
  `AD` varchar(20) DEFAULT NULL,
  `DP` varchar(20) DEFAULT NULL,
  `GQ` varchar(20) DEFAULT NULL,
  `PL` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`recordID`),
  KEY `fk_patients_idx` (`MRD_sample_FORMAT_id`),
  KEY `ChrNo` (`ChrNo`) USING BTREE,
  KEY `snpID` (`snpID`),
  KEY `idx_patient_records_ChrNo` (`ChrNo`),
  KEY `position_chrom` (`ChrPos`),
  KEY `quality` (`QualityLevel`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=700756618 DEFAULT CHARSET=latin1 |


texts: SQL Server Configuration manager doesn't show the IP

texts:
Sorry for stupid question.
I'm trying to set up the remote connection to the SQL Server.
In all tutorials I watched people have their IPs shown in the TCP/IP properties window(under SQL Server Network Configuration-->Protocols for MSSQLSERVER). Please see the picture.
But I have only the port.
Where can I see the IP? What should I do to see it?


texts: Problems Linking the max value of an attribute in a table to its primary Key

texts:
I need to extract the order_id with the max number of items, I've figured out how to extract the max value but  I'm having problems linking it to the particular order_id.

PS: I'm a uni student so please go easy on me.

select MAX(f), x.order_id
from ( select count( distinct z.item_id) f, z.order_id as g
       from  Order_item z, Item i, Ordert o
       where o.order_id=z.order_id
       group by z.order_id
      ) AS T, Ordert x
having x.order_id=g;


texts: Database schema size result differs from file size when exported

texts:
I used this query to find out my database size

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;

it returned as 849MB but when I exported the database it was only 501MB.

Being that there seemed to be over 300MB of info missing I decided to export in parts all the a,b,c etc, once done the total size of the folder the parts where stored in it was 606MB

This was better 100MB more but that still leaves around 200MB unaccounted for, I didn't export as zip or gzip archive just straight .sql file so really it should not differ and when the difference is over 200MB I'm thinking there is something wrong but I haven't a clue what or why each try returns a different size ...

...any suggestions on why this is happening?


texts: How to handle master changed during mongorestore?

texts: I have a very large dump data(18GB) need to restore. I tried to use mongorestore to restore the data to a replica set. But the restore process canceled because of error restoring from orders.bson: insertion error: not master. I think this is because the master get changed in the replica set. Is there a way for me to restore the data and stop changing primary on the replica set roles?


texts: Mongo Storage Engine from mmap to wiredTiger

texts:
We have a mongo(v3.0.3) database->collections which is in mmap storage engine.

We are trying to change it to wiredtiger

we tried the process below.But we still see the same old storage engine

http://docs.mongodb.org/master/release-notes/3.0-upgrade/#change-storage-engine-to-wiredtiger

Can some one help us with the steps to follow in migrating?

When I restore data it gives the below error messages

mongorestore --collection testCollections --db TestDataBase /backup/TestDataBase/testCollections.bson

error: E11000 duplicate key error index: TestDataBase.testCollections.$_id_ dup key: { : ObjectId('55f50ea2f7ec8be2e035800f') }


texts: MySQL Coalesce date missing results, why?

texts:
Running MySQL 8.0.16, settings left at defaults as far as I know.

I have a table, let's call it the_table, with columns

id: bigint primary key auto_increment

the_time: datetime

I have a record with the_time of 2019-12-19 00:00:00.

When I run either of

SELECT id, the_time
FROM the_table
WHERE the_time BETWEEN '2019-12-19' AND '2019-12-19';

SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, DATE('9999-1-1')) BETWEEN '2019-12-19' AND '2019-12-19';

I get that record. However, with

SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-19' AND '2019-12-19';

I do not, though changing it to

SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-18' AND '2019-12-20';

I suddenly do again. Why does MySQL behave this way? Why does the extra DATE() make a difference? I would have thought converting between date and string formats would be bijective and shouldn't affect the computation, but it seems it does. And why does just the coalesce make a difference in the not-null record being found or not?


texts:
I have a database with about 40-50 tables.  All but 5 are part of a giant hierarchy of 1:M relationships that all point back to one solitary parent (call it "Project").  Each table is joined to its direct parent using a foreign key to the parent's primary key, which is an identity field.  Some branches go as many as 6 or 7 levels deep.  Many of the tables have millions of rows.  Nearly all queries require returning all records of a particular table that pertain to a single project.  Queries normally only return fields from the single entity in question.  Thus a typical query (practically all queries other than random ad hoc) looks like this:

SELECT a.* FROM a
JOIN b ON a.id=b.id
JOIN c ON b.id=c.id
JOIN d ON c.id=d.id
JOIN Project p ON d.ProjectID=p.ProjectID
WHERE p.ProjectID = 12345

As you can imagine, the farther you go down the hierarchy the worse the queries perform.  I considered denormalizing on the one field only by redundantly persisting the ProjectID field throughout all levels of the hierarchy, and creating the clustered index on ProjectID in each table.  This would allow me to satisfy all data requests with a single index seek.  Doing so dramatically improves query performance.  However, I have no idea if doing this negatively impacts the fidelity of the schema design.  I'm hoping someone can provide me some insight in case I am missing something.


texts: SQL Server trusted connection additional security

texts: Using Windows authentication, is there a way to prohibit users from connecting via ODBC to the database?


texts: What database and setup can handle several million inserts per minute?

texts:
We have come to a point where for a few of our customers' systems, at peak times, events are generated at that crazy rate. That is, millions of new events per minute (between 1M and 2M).

The records/docs size are 1.5Kb on average.

Is there a database that can handle such radical write speed out there (16K - 32k inserts per second)?

There are several candidates out there, Cassandra, Hive, Hadoop, HBase, Riak and perhaps a few others, but I would like to know from someone else's experience and not just quoting from each database system's website self testimonials.

If you have handled such load at your work, your advice is highly appreciated.


texts: Postgres join where foreign table has ALL records

texts:
I have this people and tags table, like this,

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

If I want to find all people that contain both the np and yw tags in the tags table using a join, how would I do this efficiently in Postgres 9.6?

In this scenario, I should only get Joe's record from the people table.


texts: postgres \dt functionality change from v9.4 to 9.6?

texts:
I have a postgres 9.4 database and when I use the:

\dt

command, tables in the 'public' schema, and other user-created schemas are shown.

I have the exact same database on a Postgres 9.6 server and when I use:

\dt

only the tables in the 'public' schema are shown, and in order to see tables in the other user-created schema, i have to use:

\dt schemaname.*

to see those tables. Is this a difference between 9.4 and 9.6, or any i missing something else. permissions?


texts: Grouping results and combining duplicates as array


texts:
I have server with db(36Gb data), only mysql access. When i'm trying to use mysqldump utility, i get connection timeout(or something like this).

I doesn't have ssh access for copy /var/mysql/data.
It's a production server and i want copy data without any loads on server CPU/IOdisk.

Now i have only one solution. Read batch of data (1000-10000 lines) with script, insert in local database, wait xxx ms.

May be exists ready solutions?


texts: How to get the first or last row in ordered result set, depending by column value?


texts:
I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of  from Current Date, based off of the value of another field:

CASE WHEN DaysPD = 30 THEN 1
     WHEN DaysPD = 180 THEN 6
     WHEN DaysPD = 360 THEN 12
 END AS MonthsPD

CASE WHEN RateCode = 'AC' THEN RateResetDate - MonthsPD

In this example, if the RateResetDate = '03/08/2019' and MonthsPD = 6, then I would want the calculation to be '03/08/2019' - 6 Months = '10/8/2018'


texts: Confused about behaviour of variable in WHERE statement when value is null

texts:
I have a table like (SQLServer 2008):

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED
(
    [productId] ASC,
    [purchaseId] ASC
))

having around 10 milions rows.

I want a query that returns the total number of rows for a product or, if the product is not secified, the total number of rows for all the products.
Something like:

declare @productId int

set @productId = 320

select count(*)
from my_test_table t with(nolock)
where productId = @productId
or @productId is null

The problem is that query takes much more time than the equivalent query:

select count(*)
from my_test_table t with(nolock)
where productId = 320
or 320 is null

How can we explain this behaviour ?

Here are the execution plans:


texts: Can I partially invalidate the MySQL table cache?

texts:
I want to set a fairly large table to MyISAM mode to keep it cached proactively.  The table file is approximately 3GB.

After several hours, MySQL invalidates the entire table from cache, while < 0.1% of the table actually changed in the meantime.

Sometimes we need to do ±100,000 SELECT statements very quickly, which causes a cache stampede. When cached, very nice. When not cached, terrible.

What can I do to ensure the table is kept in memory?


texts:
I am designing a database system for my current project and got this situation.
I am having a functional dependency as

  D -> E

Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination. And Unique combination of D will define unique value of E. So i am interested in knowing what is the best way of achieving this requirement.


texts: How to only restore a specific filegroup?

texts:
I am using SQL Server 2008R2 (but I can upgrade if necessary)

I have a database with multiple schemas and each schema is on a different filegroup (I wish I could just backup/restore a schema but that doesn't seem to be possible).

The recovery model is full.

I am performing backups of the filegroups.

How do I perform a filegroup to a previous state (without affecting the data on other filegroups?).
When I try to restore a file group I need to restore the transaction log which adds all changes since the last backup.

Is what I am asking even possible?


texts: How does trigger file in recovery.conf work?

texts:
Can someone explain how the trigger_file in recovery.conf works?

As far as I know the standby server will become the primary server when it detects the trigger_file. But how will the trigger_file be created when the primary server fails?


texts:
I need to check if a folder exists and if it does not create it.  So if C:\CSV\red\ does not exist create, and so on.  I use this DDL - how can I create folder if not there?

Create Table bonniebell
(
   name varchar(max)
)

Insert Into bonniebell values ('red'), ('blue'), ('green')

Declare @foldername varchar(max), @fulldirname varchar(max)

Declare folder cursor for

  Select name
  from bonniebell
  order by name asc

  Open folder

  fetch next from folder into @foldername

  while @@fetch_status = 0
  Begin

    Set @fulldirname = "C:\CSV\"

    Set @fulldirname = @fulldirname + @folder

  Fetch Next from folder into @foldername

  END

Close folder

Deallocate folder


texts: SQL Database Snapshot before code depolyment faster than backup

texts: We have code deployments every Thursday, some of the changes involve database changes - before the code is depolyed we do a SQL backup in case there are errors, this usually takes 45 minutes and everyone has to wait till it finishes.  Could I take advantage of taking a database snapshot, and if the code tests good just delete the snapshot, if there is a problem use the snapshot to roll back any changes made?


texts: Disk page size for MySQL and MySQL cluster

texts:
As I know database reads data from disk in some constant page size (even if one byte is needed, whole page will be copied from disk to memory).
What are disk page sizes for MySQL and MySQL Cluster?
Is this value configurable or is fixed and depends on db engine/operating system?


texts:
I have a lot of soccer betting odds (hundreds per match and thousands of matches), for example:

<_10Bet_Home_Home>8.00</_10Bet_Home_Home>
<_10Bet_Home_Away>1.39</_10Bet_Home_Away>
<_10Bet_Home_Draw>4.50</_10Bet_Home_Draw>
<Bet_At_Home_Home>7.50</Bet_At_Home_Home>
<Bet_At_Home_Away>1.40</Bet_At_Home_Away>
<Bet_At_Home_Draw>4.30</Bet_At_Home_Draw>
<Bet365_Home>7.50</Bet365_Home>
<Bet365_Away>1.40</Bet365_Away>
<Bet365_Draw>4.50</Bet365_Draw>
<Bwin_Home>7.00</Bwin_Home>
<Bwin_Away>1.45</Bwin_Away>
<Bwin_Draw>4.25</Bwin_Draw>
<MyBet_Home>7.50</MyBet_Home>
<MyBet_Away>1.43</MyBet_Away>
<MyBet_Draw>4.50</MyBet_Draw>
<NordicBet_Home>7.85</NordicBet_Home>
<NordicBet_Away>1.40</NordicBet_Away>
<NordicBet_Draw>4.50</NordicBet_Draw>
....

What is the best datatype to store them? I need to occupy less storage as possible but also be efficient with sums, means etc.

They don't have a theoretical max value but I don't think they'll go above 100... Decimal(4,2) unsigned?

I know the question may be silly but I'm looking for a confirm from database experts...


texts: What is different between fixed-size disk pages and disk-based?

texts:
As per my knowledge, i understand fixed page size as  size of page which used for read and write data but i am not able to understand what does mean of disk based ?

I read these words from this paper which compared about sql and noSQL

http://www.labouseur.com/courses/db/Stonebraker-SQL-vs-NoSQL-2010.pdf


texts: How do I get TABLE OF TOURNAMENT POSITIONS according to score table

texts:
Buen dia Comunidad.

Good morning Community.

I am having doubts about how to obtain the POSITION CHART of the following SCORE table in MYSQL.

1: This is my table where the user records the escort of each sport played.

2: Now this is where I have doubts, how do I get the POSITION CHART according to the attached model where the following is calculated: PJ = Matches played PG = Matches Won PP = Lost Matches PE = Draws Matched GF = Goals For GC = Goals Against DFG = Total Goal Difference = where the winning team obtains 3 Points.

.

I wait for your kind support

I could only make the amount of encounter that 1 team has made

select category, report, gender, Team1, count (*) from score where Team1 = 'red' and category = 'CAT 3'


texts: How to design indexes for columns with NULL values in MySQL?

texts:
I have a database with 40 million entries and want to run queries with the following WHERE clause

...
WHERE
  `POP1` IS NOT NULL
  && `VT`='ABC'
  && (`SOURCE`='HOME')
  && (`alt` RLIKE '^[AaCcGgTt]$')
  && (`ref` RLIKE '^[AaCcGgTt]$')
  && (`AA` RLIKE '^[AaCcGgTt]$')
  && (`ref` = `AA` || `alt` = `AA`)
LIMIT 10 ;

POP1 is a float column that can also be NULL. POP1 IS NOT NULL should exclude about 50% of the entries, that's why I put it at the beginning. All other terms reduce the number only marginally.

Amongst others, I designed an index pop1_vt_source, which seems to be not used, while an index with vt as first column is used. EXPLAIN-output:

| id | select_type | table | type | possible_keys                          | key                 | key_len | ref         | rows     | Extra       |
|  1 | SIMPLE      | myTab | ref  | vt_source_pop1_pop2,pop1_vt_source,... | vt_source_pop1_pop2 | 206     | const,const | 20040021 | Using where |

Why is the index with pop1 as the first column not used? Because of the NOT or because of NULL in general. How can I improve the design of my indices and WHERE clauses? Even when limiting to 10 entries, the query takes more than 30 seconds, although the first 100 entries in the table should contain the 10 matches.


texts: SELECT max value for each group of values order by grouped by MYSQL 8.x

texts:
I need to get the max age of a group of records, even is the age is duplicate ORDER BY the age DESC
Person | Group | Age
---
Bob  | 1     | 32
Jill | 1     | 34
Shawn| 1     | 42
Jake | 2     | 29
Paul | 2     | 36
Laura| 2     | 39
Lalo |3      |50
Julia|3      |50
Travis|3     |18

Wanted results:
Julia | 3     | 50
Shawn | 1     | 42
Laura | 2     | 39

I tried this solutions by when applying to my real table i brings duplicated age records and i cant get to Order by 'column' DESC
I also tried by doing GROUP BY Group and ORDER BY Age DESC at the end of the query, this brings the values grouped correctly but the Age values are not right


texts: PostgreSQL stored procedure to return rows or empty set

texts:
I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do

RETURN QUERY EXECUTE 'some dynamic query'

And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to return them, but if it returns only < 10 rows, I don't want to return anything (empty set of ct_custom_type).

I tried:

RETURN QUERY EXECUTE 'some dynamic query'

GET DIAGNOSTICS variable = ROW_COUNT;
IF variable < 10 THEN

    # I don't know what to do here or how to accomplish this

END IF;

If I do RETURN QUERY SELECT 0, 0, ''::text; in the IF block (because ct_custom_type is a composite type of (integer, integer, text), it just adds this 'empty row' to previous query result, but I don't want to return anything in this case, I can do RETURN;, but it will return that previous result and I want to discard it.

I have it like this:

EXECUTE 'dynamic query';

GET DIAGNOSTICS variable = ROW_COUNT;
IF variable >= 10 THEN
    RETURN QUERY EXECUTE 'dynamic query';
END IF;

and it works, but I didn't want to do this query two times.


texts: Possible reasons for irregularly high query execution time?

texts:
The above is a graph showing the average response times per second (including error bars) for an experiment where I simulate 30 clients which are all invoking the same query multiple times. I ran this experiment over and over and over again using also different queries but the result is always that around 20 seconds there is this one peak.

I'm not that familiar with postgres. The only thing that I thought of which could be responsible for this behavior is the vacuum daemon. However I enabled logs for the deamon and it turns out that it's not active at all in the period of the experiment. Is there anything else running at arbitrary points which could cause a high query time for a short period?

The function:

CREATE OR REPLACE FUNCTION insert_message(_queue_id BIGINT, _source_client_id BIGINT, _receiver_client_id BIGINT, _content TEXT, OUT status SMALLINT) AS $$
    BEGIN
                INSERT INTO message VALUES(DEFAULT, _queue_id, _source_client_id, _receiver_client_id, _content, DEFAULT);
                status = 0;
    END;
$$ LANGUAGE plpgsql;

The respective table:

CREATE TABLE message(
    id BIGINT DEFAULT nextval('message_id_seq'),
    queue_id BIGINT NOT NULL,
    sender_client_id BIGINT NOT NULL,
    receiver_client_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    insertion_time TIMESTAMP NOT NULL DEFAULT current_timestamp,
    PRIMARY KEY(id),
    FOREIGN KEY(queue_id) REFERENCES queue(id) ON DELETE CASCADE,
    FOREIGN KEY(sender_client_id) REFERENCES client(id) ON DELETE CASCADE,
    FOREIGN KEY(receiver_client_id) REFERENCES client(id) ON DELETE CASCADE
);

Index on the table:

CREATE INDEX message_index ON message(queue_id, receiver_client_id, sender_client_id);

EDIT: Ran it on AWS. Same pattern. Thus it's no hardware thing.


texts: In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What is the best way to transfer the database during/after SQL Server update?


texts: What is the best way to get all data for a date range, plus the last event just before the range?

texts:
For this question assume the following:

There is a table of events [Event]
There is a table of employees [Employee]
There is a table that relates the two [EventEmployee] (EventID + EmployeeID)
Each event is assigned to n employees
These events can occur at random dates/times

I need to query for the events that start and/or end within a specified time frame. I also need to include the first event prior to the specified date range PER EMPLOYEE assigned to the event.

For example, if I query from 03/01/2014 to 04/01/2014 and I return 15 events with 5 employees assigned to each. I also need to return the most recent event prior to 03/01/2014 for each of those employees.

I am currently accomplishing this by:

Getting all of my events for the date range into a temp table
Selecting everything out of the temp table
UNION with a query that gets the DISTINCT [EmployeeID]s based on the temp table and does a CROSS APPLY on [Event] joined with [EventEmployee] to get the most recent event prior to the start date of the range.

Is there a more efficient way to accomplish this task?

Please tell me if some sample code would help the explanation.

EDIT:
My Query:

Assuming @StartDate and @EndDate are DATETIME parameters,

-- Get all events that start or end within a given time frame
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

SELECT
    #EventTemp.[EventID],
    #EventTemp.[EventStart],
    #EventTemp.[EventEnd],
    #EventTemp.[EventTypeID]
FROM
    #EventTemp
UNION
SELECT
    PrevEvent.[EventID],
    PrevEvent.[EventStart],
    PrevEvent.[EventEnd],
    PrevEvent.[EventTypeID]
FROM
    -- Get a DISTINCT list of employeeIDs from those events
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    -- Get the most recent prior event for each distinct employeeID
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID],
            e.[EventStart],
            e.[EventEnd],
            e.[EventTypeID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY
            e.[EventEnd] DESC
    ) AS PrevEvent

Lets say we end up with 4 events in the temp table #EventsTemp. Each of those events could be tied to any number of employees. For this example, lets assume between all the events in #EventsTemp we are dealing with 3 employees.

For the final select I would want to get the list of events from #TempEvents, in addition I would also want the most recent event, prior to @StartDate, that each of those 3 employees were at. If 2 of the employees were at the same event I would expect to get a single extra row for that event, plus an extra row for the 3rd employee:

EventID,EventStart,EventEnd,EventType
5,'2014-04-01 13:00','2014-04-01 15:00',1
6,'2014-04-01 16:00','2014-04-01 19:00',1
7,'2014-04-02 10:00','2014-04-02 14:00',1
8,'2014-04-02 16:00','2014-04-02 18:00',1
2,'2014-03-26 13:00','2014-03-26 15:00',1
1,'2014-03-25 10:00','2014-03-25 14:00',1

In this example, the first four rows were straight from #EventsTemp, and the last two rows are the distinct prior events attended by any combination of the employees that attended the original 4 events from #EventsTemp


texts: How to appy database security patches in an oracle failsafe environment

texts:
We have 4 databases in oracle failsafe cluster.
2 are dc(primary dbs) in failsafe cluster and 2 are dr(standby dbs) in failsafe cluster.
Can anyone provide steps to apply patches to these databases.


texts: How to add a Default constraint while creating a table? SQL Server

texts:
I am trying to create a new table with columns followed by their constraint as shown below.

Create tblTest(
columns..
..
..
Gender int,
Constraint DF_tblTest_Gender Default 3 For Gender,
..
..
..
)

However, I am getting an error message near the default constraint as,

  'Incorrect syntax near 'for''


texts: Windows Clustering and SQL Availability Groups

texts:
I am new to Clustering and Availability groups.  I am creating a 4 node Windows 2012 Cluster with SQL 2016 enterprise.  SQL will not be clustered we are just using the availability group option.  The cluster is setup like: 3 servers on one site and 1 server offsite.  Our goal is to have multiple databases on the servers Server 1 would be primary for availability group 1 and server 2 would be primary for availability group 2.  Server 3 and 4 would be primary failover availability groups as well as server 1 being a failover for server 2 and server 2 being a failover for server 1.  Server 4 would also be the offsite DR server.

I have the windows cluster setup to have the Quorum drive as a file share that all 4 servers can get too.  One of the databases on the servers will need access to the file system for import and export of csv, xls, etc. files.

How can all 4 servers have access to this drive, when I have one server in a different location on a different subnet?

Any help or direction would be appreciated.


texts: How to use reference to another table in aggregate function

texts:
I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write:

SELECT  [GroupId] = cr.[GroupId],
        [OnCreateCount] =  SUM(CASE WHEN cr.StatusOfRequest = 1 THEN 1 ELSE 0 END),
        [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END),
        [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND THEN 1 ELSE 0 END),
        [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END),
        [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END),
        [TotalCount] = COUNT(*)
FROM [FK].[CertificateRequest] cr
GROUP BY cr.[GroupId]

Here OnCreateCount and CreatedCount are the same, but it's wrong. CreatedCount is number of rows with status 1 which are referenced from table [Certificate]. OnCreate is number of rows with status 1 which are not referenced from table [Certificate]

Here is how I solved it:

SELECT  [GroupId] = cr.[GroupId],
        [OnCreateCount] =  SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NULL THEN 1 ELSE 0 END),
        [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END),
        [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NOT NULL THEN 1 ELSE 0 END),
        [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END),
        [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END),
        [TotalCount] = COUNT(*)

FROM [FK].[CertificateRequest] cr
OUTER APPLY
(
  SELECT TOP 1 *
  FROM [FK].[Certificate] c
  WHERE c.CertificateRequestID = cr.ID
) c
GROUP BY cr.[GroupId]

But I really don't like OUTER APPLY here which is used just to determine whenever there is any row in referenced table.

What is the easiest way to get required result?


texts: How can I ensure the user can insert and edit data while updating table

texts:
I am designing schema. Any idea is welcome!
Right now I have tableA, tableA_cache, tableB, tableC, tableD. If any row insert to tableB tableC or tableD, the trigger will insert data to tableA.
For data integrity, periodically I have to delete all the rows in tableA then re-populate data from tableB, tableC...etc.
Normally, user query tableA_cache so that even if tableA has no data, user can query tableA_cache. However, during re-population, tableA will be keep writing. At the same time, if user insert a row in tableB (or C, D), the trigger should insert data to tableA. Since tableA is locked, the response time for insert will be extremely slow.
Is there a better way to handle this case? or a direction I can look into. (There is no guarantee that in specific timing no user will use the system.)


texts: Right Distribution of Databases amongst Multiple Instances

texts:
So it's obvious that many times you'll have multiple instances (even if on the same server) to have a logical separation of databases for security purposes.

But when does it make sense on the performance side to separate databases amongst multiple instances?  For instance, say you have 200 databases: 100 of them are heavy OLTP and 100 of them are mostly just OLAP.

What is wise in this scenario?  What is the general rule of thumb?  Is there such a thing as instance performance tuning?


texts: how do I change this query from SQL Server 2016 to MySQL?

texts:
This is a sample of a query that I currently use regularly in SQL Server 2016 (Standard edition).  I need assistance converting this script to MySQL 5.6 syntax that will provide the same result.

Create the table:

CREATE TABLE zip_unique
(     store_nbr integer
     ,zipcode varchar(30)
     ,distance float);

Populate the table:

INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521)

SQL Server Query to Rank Zipcode based on the distance value:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
  FROM zip_unique

Output:

+-----------+---------+----------+----------+
| store_nbr | zipcode | distance | ROW_RANK |
+-----------+---------+----------+----------+
|       123 |   76135 |    2.356 |        1 |
|       456 |   76135 |    3.002 |        2 |
|       789 |   76135 |    9.521 |        3 |
+-----------+---------+----------+----------+


texts:
I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database.

select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct   me.id) mecount, count(distinct me.hotpointid) hpcount
from upme me, appmday pm
where pm.apid = me.id
          and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
          to_date('2012-1-31', 'yyyy-MM-dd')
group by pm.city, to_char(pm.sampledate, 'yyyy-MM')

above i get the count of my data.but i also i want to get the overall result like

select   to_char(pm.sampledate, 'yyyy-MM'), count(distinct me.id), count(distinct me.hotpointid)
from upme me, appmday pm
        where pm.apid = me.id
              and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
              to_date('2012-1-31', 'yyyy-MM-dd')
              group by to_char(pm.sampledate, 'yyyy-MM')

basically i want get some result from sql ,in the same sql i want to get the count from the previous result,they both show in the result in one sql. i did not want to union these two sql ,because each sql is very slow.

did someone have a idea? or it cannot be done by one sql without union?

thank you for the reply.


texts: How to Determine Chunk Distribution (data and number of docs) in a Sharded MongoDB Cluster?

texts:
Having done all the right things - picked an appropriate shard key, scaled horizontally, distributed my data across several shards, I find I now have no real way to determine how well balanced the data is in terms of document counts, and data size.  The sh.status() command will tell me how chunks are distributed in terms of counts, but not what makes up those chunks.

There are plenty of ways to infer these things - but they all have a downside.  The vagaries of how database size are calculated means that if there have been significant deletes in a database, the stats may not be an accurate reflection of data distribution.  If I look at traffic hitting each shard, that might give me some clue, but only if my traffic is nice and predictable.

So, how do I determine the distribution of documents in each chunk, the relative sizes of each chunk and (of course) where those chunks currently reside?


texts: Can a maintenance workload after a SQL Server reboot cause SQL outage or issue when application load resumes?

texts:
Configuration:

Hardware: HP ProLiant DL980 G7
CPU: 4 Xeon X6550 @ 2.00GHz
Memory: 393,206 MB
SQL Version/Ed, OS: SQL Server 2008 Enterprise Ed (SP1) - 10.0.2757.0 (X64)
Windows: Microsoft Windows Server 2008 R2 Enterprise

Recently our primary SQL Server (on a cluster) experienced an outage on a Monday morning. Over the weekend work had been done (to upgrade NIC drivers & firmware). The server failed over and rebooted and failed over again.

Everything seemed to be working just fine through Sunday; Clean logs for the light load of work & maintenance that is performed.  However, on Monday morning, as application and DB activity ramped up the database experienced some blocking that caused connections to spike to the thousands. As the connections spiked there was memory pressure, the blocking got worse, and connections to the database started to fail and timeout. I was engaged while the blocking was peaking and tried to kill some SPIDs, but we decided to fail over to return to service more quickly.

My question is: could the RE-INDEX maintenance workload after the reboot fill the query plan cache and create memory pressure and blocking when the application workload resumes on Monday morning?

Our application service windows reflect operating hours, so we have a flood of activity as customer systems come online M-F 7am to 8pm. If the application timeouts lead to aggressive log-in attempts and a spike in database connections could we be creating a self-inflicted Denial-Of-Service on the database which leads to more memory pressure, lack of threads to do work and eventually bringing down the database?

Has anyone seen behavior like this before in large memory systems where application workload occurs in very specific windows?

The only two errors found occur 30 minutes after the application server reported connections failing:

Message: Error: 18056, Severity: 20, State: 29.
The client was unable to reuse a session with SPID XXX,
which had been reset for connection pooling. The failure ID is 29.
This error may have been caused by an earlier operation failing.
Check the error logs for failed operations immediately before this error message.


texts: WHENEVER OSERROR suppresses error messages

texts:
$ sqlplus <connstring> 2>&1 <<!
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET ECHO ON;
@nonex.sql;
!

Produces

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13 19:02:37 2015
<etc>

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
<bla-bla-bla>

SQL> SQL> SQL> SQL> Disconnected from Oracle Database <etc>

So, if an error occurs, I cannot see the cause. I tried piping the output to a file to no avail so a message being overwritten by the following text is not an issue.

Commenting out the WHENEVER OSERROR line adds the expected message:

SQL> SQL> SQL> SQL> SP2-0310: unable to open file "nonex.sql"
SQL> Disconnected from Oracle Database 11g <etc>

But now, the error code is 0.

Is this a known problem? Are there any fixes/workarounds?


texts: Non RAM based in-browser database solutions?

texts:
I want to decentralize my databases and upload it to Sia Skynet to give the users the ability to search on front-end. It is important to be able to deal with large amounts of data, but the RAM-based in-browser SQL engines (MiniSearch, AlaSQL, etc) are limited.
Yeaterday I made a demo: I sorted and partitioned my key-value database into 1000 line files and I made an index for the files also. You can try out my demo here, or check it out on GitHub
So my question is that are there any better solutions for large-in-browser databases? (I don't really want to reinvent the wheel )


texts:
I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are FK to the first table.

I want to check if there are any users in user_profile which are not in user_friend table, neither in user_profile1_id, nor user_profile2_id.

What query should I use?

EDIT:

CREATE TABLE user_profile (
  --fields
)

CREATE TABLE user_friend (
  user_profile1_id INT,
  user_profile2_id INT
)


texts: MySQL optimizations on local server with many databases

texts:
I'm running a local mysql server for development. I have around 300 databases with about 152 tables per database.

I've noticed that adding so many database has slowed down some queries. I've noticed this happening particularly when I'm migrating a database from the Laravel app.

Is there any configuration I can update to improve the performance locally? I have 32gb of ram so I don't mind it using more resources. I also don't mind if it's something quick and dirty since this is just a local development server.


texts:
I have 2 varchars like:

20160316131824
20160316080002

How can I subtract to see if there is more than 24 hours of difference between the 2 values?

I tried using the symbol - but it gives me an error. What is the best way?


texts: What is the CURRENT_DATE or current date value function for SQL Server?

texts:
What is the SQL Server functionality that allows you to get the current date as a DATE type like the SQL Standard's CURRENT_DATE feature. What is the <current date value function> for SQL Server?

Using PostgreSQL, I'm looking for something like,

SELECT CURRENT_DATE;
 current_date
--------------
 2018-06-27
(1 row)


texts: Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'

texts:
I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a wiki.

User id of the new wiki user table register and default group id (2) should also be inserted in another wiki table, so that a new user belongs to a default group.

I coded this:

USE users_web;

DELIMITER |
CREATE TRIGGER doku_users_ins AFTER INSERT ON users_web.users
       FOR EACH ROW
       BEGIN
                INSERT INTO users_web.doku_users (login, pass, email)
                       VALUES (NEW.user, md5(NEW.pass), NEW.email);

                INSERT INTO users_web.doku_usergroup (uid, gid)
                       SELECT du.uid, "2" FROM doku_users du
                            INNER JOIN users_web.users u ON NEW.user = du.login;

       END |

DELIMITER ;

If the trigger is composed only of the first INSERT statement it works well, as the doku_users table is inserted of one more register.

But when I create the trigger as above, with the two INSERT statements, I receive an error as feedback: Duplicate entry '521-2' for key 'PRIMARY'.

I guess that the second sql statement tries to read the NEW value before it is available somehow.

How can I solve this issue?


texts: replica set in MongoDB using docker, primary has error and stops being primary when another member is added to the set

texts:
I have two docker containers running a mongo instance each, they were initialized like this:

docker run --name mongodb-shard-1-node-1 -d -v mongodb-shard-1-node-1:/data/db -p 27031:27017 mongo --replSet rs0 --smallfiles --oplogSize 128

when i do docker inspect mongodb-shard-1-node-1 it shows the ip 172.17.0.2

docker run --name mongodb-shard-1-node-2 -d -v mongodb-shard-1-node-2:/data/db -p 27020:27017 mongo --replSet rs0 --smallfiles --oplogSize 128

when i do docker inspect mongodb-shard-1-node-2 it shows the ip 172.17.0.4

So i proceed to access mongodb-shard-1-node-1 by using docker exec -it mongodb-shard-1-node-1 mongo and i initialize it as the primary member like this:

rs.initiate()
{
    "info2" : "no configuration specified. Using a default configuration for the set",
    "me" : "ee3c41ef76b2:27017",
    "ok" : 1
}

Then I proceed to add the mongodb-shard-1-node-2 to this member for it to work as a secondary member, at first it looks like it worked:

rs0:PRIMARY> rs.add("172.17.0.4:27017")
{ "ok" : 1 }
rs0:PRIMARY> rs.status()
{
    "set" : "rs0",
    "date" : ISODate("2016-05-20T01:04:02.095Z"),
    "myState" : 1,
    "term" : NumberLong(1),
    "members" : [ervalMillis" : NumberLong(2000),
            {       "_id" : 0,
                    "name" : "ee3c41ef76b2:27017",
                    "state" : 1,,
                    "uptime" : 27,PRIMARY",
                    "optime""ts" : Timestamp(1463706237, 1),
                    },      "t" : NumberLong(1)
                    "infoMessage" : "could not find member to sync from",
                    "electionDate" : ISODate("2016-05-20T01:03:43Z"),
                    "self" : truen" : 2,
            {,
                    "name" : "172.17.0.4:27017",
                    "state" : 0,,
                    "uptime" : 4,"STARTUP",
                    "optime""ts" : Timestamp(0, 0),
                    },      "t" : NumberLong(-1)
                    "lastHeartbeat" : ISODate("2016-05-20T01:04:01.187Z"),
                    "pingMs" : NumberLong(0),Date("1970-01-01T00:00:00Z"),
            }       "configVersion" : -2
    "ok" : 1
}

but right away it fails for some reason and i have no idea why, here's what i get:

rs0:PRIMARY> rs.status()
2016-05-20T01:04:18.007+0000 E QUERY    [thread1] Error: error doing query:
failed: network error while attempting to run command 'replSetGetStatus' on host '127.0.0.1:27017'  :
DB.prototype.runCommand@src/mongo/shell/db.js:135:1
DB.prototype.adminCommand@src/mongo/shell/db.js:153:16
rs.status@src/mongo/shell/utils.js:1090:12
@(shell):1:1

2016-05-20T01:04:18.012+0000 I NETWORK  [thread1] trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2016-05-20T01:04:18.018+0000 I NETWORK  [thread1] reconnect 127.0.0.1:27017 (127.0.0.1) ok
rs0:SECONDARY

What is wrong? How do I fix it?

Edit: just to clarify, i had already tried the connections between the containers by doing what this part of the documentation says at: Test Connections Between all Members


texts:
In troubleshooting why a database application is working infinitely better on test than production, I ran across this phenomenon: I cannot query the X$ tables in production.

SQL> desc x$ksppi
ERROR:
ORA-04043: object "SYS"."X_$KSPPI" does not exist

SQL> select * from dba_synonyms where synonym_name = 'X$KSPPI';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------------------------------------------------------
PUBLIC                         X$KSPPI                        SYS                            X_$KSPPI

SQL> desc sys.x$ksppi;
ERROR:
ORA-04043: object sys.x$ksppi does not exist

On test, I actually receive the table description back.

It is my limited understanding that these are actually defined outside of the database as C structures or some such thing and are created upon startup to populate the V$ views. If my production database, specifically the optimizer, does not have access to the hidden parameters, I could definitely see why it could have a negative impact on performance.

Why wouldn't the X$ tables be created upon startup? Issues with this application began some time after it was upgraded to 11.2.0.4, so could a botched upgrade have done this? What troubleshooting steps can I take to analyze the root issue?


texts: Requirement is that I need to clean up my database in which my application has extensively tested on, and now I will have to clean up the data and have a database which will be ready to go live. By clean up, I mean the metadata of the tables, users will stay but their data will have to cleaned. What is the fastest and the best method to go about with? Truncate? Any help would be appreciated.


texts: Mysql key_buffer_size already 16GB but some fulltext searches are still slow

texts:
I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB.
Table has 15 million records and has a compound fulltext index on fields: categories and company.

Table Size:Data Length - 0.9GBIndex Length - 1.1GB

    SELECT c.id FROM basetable c
    WHERE MATCH(c.categories, c.company)
    AGAINST('+keyword1 +keyword2 +keyword3' IN BOOLEAN MODE)
    LIMIT 100

Query Results:only 88 rows

Query Status:
Key_read_requests - 4414336
Key_reads - 1029
Key_write_requests - 12
Key_writes - 2
Handler_read_next - 89

Query Explain:
1 | SIMPLE | c | fulltext | categories_company | categories_company | 0 |  | 1 | Using where

Mysql Global Variables:
key_buffer_size - 16GB
key_cache_age_threshold - 300
key_cache_block_size - 1024
key_cache_division_limit - 100
max_heap_table_size - 6gb
tmp_table_size - 6gb

Fulltext Variables:
ft_boolean_syntax - + -><()~*:""&|
ft_max_word_len - 20
ft_min_word_len - 2
ft_query_expansion_limit - 20
ft_stopword_file - none

Server:
Dual XEONS CPUs
32GB RAM
HD 15kRPM.

Note that not all queries are slow. Some are less than a second; it depends on the keywords used on Full Text search.

Do you have any thoughts on how to improve this?


texts: database management system vs database vs tables vs file

texts:
I have been trying to understand the way a database system works. Digging through the internet, I have come to following picture.

Database system is required because we need a way to store data, mainly on large scale, so that we can update it, query it, delete it, manipulate it plus perform other operations easily. The traditional file system is not designed for this and fails to serve the purpose.
We can implement the above database system in different ways, which leads to different data models, RDBMS being the most dominant one.
There is a database management system, which is a software and is in a way like file management system, and it manages the requests of creation, deletion, insertion of data etc. Eg. SQLite.
We interact with database using an interface, which is provided by database management system, and a language which is compatible with the database management system. Eg. SQL.

I hope my understanding till now is correct.
Now, the database management system interacts with database and I am not able to understand what database is exactly. I used to think the collection of all the tables is termed a database and is just a name. But then I found out there are several databases possible and a database can be created using DBMS just like files. Further research indicated me that database is a file.
My doubts are following:

If database is a file, how does it differ from ordinary files (in terms of implementation)?
I believe that tables are also files. But tables are stored in a database. How can a file be inside a file and both can be considered separate.
The implementation of different data models, like relations to store data in rdbms, objects to store data in OOP based database systems, are they implemented in database management system or database?

And if database is not a file what is it exactly and how tables, database and database management system are related in terms of implementation?
I have tried to find answers to these but it seems like the content on these are not much and I have had no luck. Also, please explain it avoiding very involved code snippets, as I am an analys


texts: How is INFORMATION_SCHEMA implemented in MySQL?

texts:
The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?

When I connect to a fresh installation I see two databases: mysql and information_schema. After using SHOW CREATE TABLE statements on the information_schema database, it looks like it's not implemented as a set of views but with base tables instead. Is this assumption correct? Or there are other system tables that are hidden from the user?


texts: I'm noticing this wait stat on my Azure SQL database, and I've never seen it before. I've tried Googling this wait stat but haven't been able to come up with any good information on what this is, although I saw a couple mentions of it possibly being related to exceeding the Azure performance tier set for the database.


texts: Detecting the locked table or row in SQL Server

texts:
I'm trying to understand/learn how to track down the details of a blocked session.

So I created the following setup:

create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;

Now I connect to the database twice from two different clients.

The first session issues:

begin transaction
update foo set some_data = 'update'
  where id = 1;

I explicitly do not commit there in order to keep the locks.

In the second session I issue the same statement and of course that one waits due to locking. Now I'm trying to use the different queries floating around in order to see that session 2 is waiting for the foo table.

sp_who2 shows the following (I removed some columns to only show the important information):

SPID | Status       | BlkBy | DBName   | Command          | SPID | REQUESTID
-----+--------------+-------+----------+------------------+------+----------
52   | sleeping     |   .   | foodb    | AWAITING COMMAND | 52   | 0
53   | sleeping     |   .   | foodb    | AWAITING COMMAND | 53   | 0
54   | SUSPENDED    | 52    | foodb    | UPDATE           | 54   | 0
56   | RUNNABLE     |   .   | foodb    | SELECT INTO      | 56   | 0

This is expected, session 54 is blocked by the un-committed changes from session 52.

Querying sys.dm_os_waiting_tasks also shows this. The statement:

select session_id, wait_type, resource_address, resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;

returns:

session_id | wait_type | resource_address   | resource_description
-----------+-----------+--------------------+---------------------------------------------------------------------------------
        54 | LCK_M_X   | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associatedObjectId=72057594046054400

Again this is expected.

My problem is, that I can't figure out how to find the actual object name that session 54 is waiting for.

I have found several queries that are joining sys.dm_tran_locks and sys.dm_os_waiting_tasks like this:

SELECT ....
FROM sys.dm_tran_locks AS l
  JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address

But in my above test scenario this join does not return anything. So either that join is wrong or dm_tran_locks doesn't actually contain the information I'm looking for.

So what I am looking for is a query that returns something like:
"session 54 is waiting for a lock in table foo".

Some background info:

The real life problem I'm trying to solve is a bit more complicated, but boils down to the question "on which table is session 54 waiting for". The problem in question involves a largish stored procedure that updates several tables and a select from a view that accesses some of those tables. The select statement is blocked even though we have snapshot isolation and read committed snapshot enabled. Figuring out why the select is blocked (which I thought would not be possible if snapshot isolation is enabled) will be the next step.

As a first step I'd like to find out on what that session is waiting.


texts:
I have the following query that accesses a JSONB column in the table datasets:

SELECT count(*), my_jsonb_column->0->'PARAM1'
FROM datasets
GROUP BY my_jsonb_column->0->'PARAM1'

The explain analyze output is

HashAggregate  (cost=4778.92..5577.57 rows=53243 width=40) (actual time=1189.083..1189.135 rows=10 loops=1)
  Group Key: ((my_jsonb_column -> 0) -> 'PARAM1'::text)
  ->  Seq Scan on datasets  (cost=0.00..4510.44 rows=53696 width=32) (actual time=0.052..1165.469 rows=53696 loops=1)
Planning time: 0.081 ms
Execution time: 1189.242 ms

A similar query that uses a plain column is roughly 50x faster, with a pretty much identical query plan also using a sequential scan.

The JSONB column contains an array with typically 1-3 elements, each one an object containing between 10 to 300 keys.

I expected the JSONB version to be significantly slower than the plain column version, but I didn't expect it to be that slow. Am I doing something particularly inefficient here?

Is there anything I can do to speed up this kind of access to a JSONB column? I'll need to access different keys, not just one specific one.

Does the size of the JSONB column play a role here? I would have assumed that this kind of access to a specific key should be mostly independent of the column size, but I'm not entirely sure.


texts: Oracle 9i historical long queries since instance startup

texts: please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several that show those that are currently running and active but can not find any that show a historical of these queries since the instance was started, it is usually very simple from Toad with ADDM/AWR Reports but the database in question is Oracle 9i and does not give that option. Thank you


texts: Disabling SKIP_CORRUPT after using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

texts:
Recently I was involved in recovering data after several blocks in production DB got corrupted.

Disclaimer: I'm by no means a competent DBA, but rather a PL/SQL developer with quite a bit (I guess) of experience, so maybe I'm missing something quite obvious here. Sorry for that.

DBMS_REPAIR package provides the procedure SKIP_CORRUPT_BLOCKS which is pretty much self-explanatory (allows accessing table despite it having corrupted blocks).

After it is used you can check DBA_TABLES.SKIP_CORRUPT column (which becomes ENABLED instead of being DISABLED by default).

Now that the dust has settled, all the data has been recovered and, hopefully, lessons has been learnt, I can't seem to find a way to switch it back without recreating the table (if some day another block in this segment gets corrupted, I'd rather have the queries fail than silently skip rows from corrupted blocks).

Is there a way to disable SKIP_CORRUPT without recreating the table?


texts:
I need help putting together a query so I can take action when the following occurs:
"Trigger an email when regression result fails three or more label versions consecutively for a given block"
EDIT- had to remove img
\The example above is a good case. But if I have a block with
"IP_BLOCK_X.info#9"
"IP_BLOCK_X.info#8"
"IP_BLOCK_X.info#11"
The result of the query will be ordered incorrectly (9, then 8, then 1 is evaluated, not '11').
EDIT: Here is the table with column names and data types for reference. Table name is 'dbo.regression_full'

The BIG Picture
In my mind, what I need to do for a given block:

Get the last 3 unique labels from Regression_name (top/distinct?)

Out of those, get Block, Timestamp, Regression_name, and Result

ANSWER:
I got it!

Query

Results

Thanks for all the help, this website is amazing! I'll definitely contribute more in areas I know. This was my first post, thanks!


texts: selecting records that are 3 days from expiring datetime

texts:
I have a table that contains a datetime timestamp. What I am looking for is to select records that are exactly 3 days from the 30 day expiration based on the datetime timestamp.

Here is what I have so far although I don't think it is correct:

    SELECT jobs.*,occupations.title
    FROM jobs
    LEFT JOIN occupations on occupations.id = jobs.occupation_id
    WHERE DATE_ADD(jobs.`date_created`,INTERVAL 27 DAY) = DATE(NOW())

Help please :)


texts:
How do I export the structure of a mysql database I have, keeping the structure for all the tables but only exporting the data for some of the tables?
After exporting how do I import it into another mysql database on a different machine.

I can have the list of table names if it helps and I would prefer it if the solution will use command line but GUI is also acceptable (on windows).

Thanks


texts: SQL Server Query Plan XML: QueryPlanHash Length

texts:
UPDATE: This is definitely a bug. For full details see this Connect Item.

While testing some changes to sp_BlitzCache (full disclosure, I'm one of the authors), I came across what I thought was a bug in our code.

At one point, we're matching the Query Plan Hash to get query cost. We do that about like so:

statement.value('sum(/p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) =
    xs:hexBinary(sql:column("b.QueryHash"))]/@StatementSubTreeCost)', 'float')

This has, as far as I've seen, worked. However, In one weird case, the substring in the XML was throwing a NULL value, and the plan was showing a cost of 0, despite it being rather high.

Digging into the execution plan (full disclosure, I work for the company that hosts Paste The Plan), I noticed that the Query Plan Hash for the one problem hash was 17 characters long, while the rest are 18. Here are examples:

QueryPlanHash="0x4410B0CA640CDA89"
QueryPlanHash="0x2262FEA4CE645569"
QueryPlanHash="0xED4F225CC0E97E5" -- Problem!
QueryPlanHash="0xBF878EEE6DB955EA"
QueryPlanHash="0x263B53BC8C14A452"
QueryPlanHash="0x89F5F146CF4B476F"
QueryPlanHash="0xEF47EA40805C8961"
QueryPlanHash="0xB7BE27D6E43677A5"
QueryPlanHash="0x815C54EC43A6A6E9"

Query Plan Hash is listed as a BINARY 8 -- presumably this should always be the same length, but what does a guy like me know about binary values?

Playing with the XQuery a little bit, I found that by changing the substring to start at the second position, it would come up with a valid (albeit incorrect) hash value.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        **q.n.value('substring(@QueryPlanHash, 2)', 'BINARY(8)')**
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        **q.n.value('substring(@QueryPlanHash, 3)', 'BINARY(8)')**
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

I'm running SQL Server 2016, SP1 (13.0.4001).

Has anyone run into this before?

Is 17 characters a valid length for a BINARY 8 value?

Does this look like a bug that should get a Connect item?


texts: How to know what index is set on the table with command line in informix?

texts:
I'm using the Informix DB. I want to detect what index the table has using command line. I used

dbschema -d dbName -t  tableName | grep index

to detect the index. What's my problem is that the dbschema output format is not specified (hard to automatically parse it) so I don't think it is reliable. Is there any better way to automatically detect the index instead of read by human?


texts: Choosing what table fields to partition on

texts:
I'm having a tough time deciding whether or not to partition my table based on a unix timestamp or by a descriptor attribute. It's just over a billion rows currently.

My table basically contains:

BIGINT id
DOUBLE value
SMALLINT device_id
BIGINT unixtime
(and others...)

I can either choose to partition it based on the device_id or some modulus value on the unixtime field. On all of the examples I've seen on partitioning, everybody seems to do it on a timestamp field.

Right now, I'm constantly doing searches based on the device_id and a range of unixtime values: a user chooses a device, then picks out a time frame that they want to view (most of the time, it's the past 24 hours).

Would it even make sense to partition it based on the device_id column? Or would using the unixtime field be the way to go?


texts: How much memory does the MySQL server variable table_open_cache cause the DBMS to use?

texts:
Simple question, but I have been unable to find simple answers.
So the default value for the table_open_cache server variable is 2000. Which means 2000 slots for 2000 files. We can assume each slot takes a few bytes. As we are not discussing Table row data, but Table information. Perhaps 1K or memory each slot.
So, if we increase the cache size by a factor of 10, how much memory is then required by MySQL 5.7? And is the usage linear?


texts: How to calculate average of list of qualifications of each pupil MYSQL

texts:
I have this database and query:

http://sqlfiddle.com/#!2/b4cf72/116

And I would like to add a column with the average of the exam qualifications. However, I try doing this but it tells me there are more than 1 row on query:

http://sqlfiddle.com/#!2/b4cf72/115

    SELECT pupils.id_pupil, NAME, surname
       ,IFNULL(qua.media, 0)     media
       ,IFNULL(qua.count, 0)     number_class_qualifications
       ,(
         SELECT AVG(qualification)
           FROM   qualifications
                  JOIN pupils USING (id_pupil)
           WHERE  type_qualification     = 'exam'
                  AND id_trimester       = 1
                  AND LEVEL              = 1
                  AND class              = 'A'
           GROUP BY id_pupil
       ) AS exam_average,
       SUM(CASE WHEN type_incident = 'miss' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS misses,
       SUM(CASE WHEN type_incident = 'delay' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS delays,
       SUM(CASE WHEN type_incident='attitude' AND incidents.id_trimester = 1 THEN 1 ELSE 0
           END) AS attitude,
       SUM(CASE WHEN type_incident='miss_justif' AND incidents.id_trimester=1 THEN 1 ELSE 0
           END) AS misses_justificadas
FROM   pupils
       LEFT JOIN incidents
            ON  incidents.id_pupil = pupils.id_pupil
       LEFT JOIN (
                SELECT id_pupil,
                       id_trimester,
                       COUNT(qualification) COUNT,
                       ROUND(AVG(qualifications.qualification), 2) media
                FROM   qualifications
                WHERE  type_qualification = 'class'
                GROUP BY
                       id_pupil,
                       id_trimester
            )                AS qua
            ON  (qua.id_pupil = pupils.id_pupil)
WHERE  LEVEL = 1
       AND class = 'A'
       AND qua.id_trimester = 1
GROUP BY pupils.id_pupil

The problem is on this sentence:

SELECT AVG(qualification)
FROM   qualifications
   JOIN pupils USING (id_pupil)
WHERE  type_qualification = 'exam'
       AND id_trimester   = 1
       AND LEVEL          = 1
       AND class          = 'A'
    GROUP BY id_pupil

How can I solve it?


texts: Transaction log is full changing column type in a large table

texts:
I've got a table of more than 400 million rows and want to convert the datatype of one of its columns, specifically datetime to datetime2(2).

If I execute my statement, I get the following error:

  The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'

So is here any possibility to update this table?

(My log file can have a maximum size of 150GB, I don't have any more free space available.)


texts:
I get timeout errors every couple of days or so between IIS7 and an SQL 2008 Server. The error message I get is:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Sometimes this sorts itself out, other times I need to manually restart SQL Server. Is there any way I can trace down the cause of this error. I'm 90% sure it's somewhere without our ASP.NET webforms app where a connection is being opened and not closed, but I'm at a loss to pin down the cause.


texts:
I have a database that was originally implemented in SQL 6.5 and has been migrated to SQL2000, SQL2005 and now SQL2008R2 (Compatibility Mode = 10).  Through the migration many new tables, functions, and procedures have been added and many of the newer T-SQL features are in use (e.g., Merge, Windowing Functions, Try-Catch, Table Variables, ...)

However, most of the underlying Referential integrity is still implemented in Triggers. :(

I have run across a T-SQL Code pattern that puzzles me.  I knew some of the original implementers (from circa 1995) and they were not dummies but the reason for this pattern escapes me.  Any suggestions will be appreciated.
This example is from an After Insert Trigger but it is representative.

Declare @validCnt int
, @nullCnt int
, @rowCnt int
Select @rowCnt = @@rowCount
If update (mycol)
Begin
  Select @validCnt = count(*)
  From inserted, myLookupTable
  Where inserted.myCol = myLookupTable.myCol
  Select @nullCnt = 0
  If @validcnt + @nullCnt != @rowCnt
  Begin
    RaisError()
  End
End

I understand what the code is trying to accomplish but I don't understand the reason for @nullCnt.  I hesitate to refactor the code for fear that there is some hidden purpose for the code as is.

My inclination is to change the test to read something like

If Update(myCol)
and @rowCnt != (Select count(*) From ... Where...)
RaisError()

Any thoughts or suggestions?
BTW, we are talking about ~200 triggers total.
Thanks
Ray

Why would I refactor working code?  Well, as I understand the term you would only refactor working code.  If it weren't working you would be debugging or fixing. :)  Isn't the purpose of refactoring is to make the working code "better" in some sense?  Efficiency, readability, reliability, ...
However, the code is not working if we plan to migrate to some more current version of SQL (like even 2012).
It seems the RaisError syntax in the trigger generates a run-time error in 2012 and later.  SQLServer lets me restore the database and change the compat mode to 2012 or 2014 but when a trigger is executed there are a variety of runtime issues.
Also, most of the triggers are implementing actions that could be performed directly in the T-SQL or by Constraints/Foreign Keys.
For example, the sample I posted just checks to see if I actually inserted the correct number of rows.


texts: Two databases same server with different time zones

texts: I want to have two databases on same server but want the data in each to reflect the time zone of the client.  Do I need to adjust database parameters or can this be handled with client (JDBC or OCI) settings?