texts: Cassandra not returning the row of the specified primary key

texts:
First of all, my table is structured like this:

CREATE TABLE timeseries_data.test_data (
    date timestamp PRIMARY KEY,
    data1 float,
    data2 float
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

With data looking like this:

date                             | data1 | data2
---------------------------------+-------+-------
 2018-01-01 10:27:00.000000+0000 |    15 |    30
 2018-01-01 13:47:00.000000+0000 |    94 |    45
 2018-01-01 17:14:00.000000+0000 |    13 |     3

I inserted a time series data for the date "2018-01-01" with the frequency in minutes. That is the data is ranging from "2018-01-01 00:00:00" to "2018-01-01 23:59:00". So in total, I have 1440 rows of data.

Problem is when I try to query for a specific date like

cqlsh:timeseries_data> SELECT * FROM test_data WHERE date='2018-01-01 12:00:00';

 date                            | data1 | data2
---------------------------------+-------+-------
 2018-01-01 06:30:00.000000+0000 |    34 |    62

It returns a result for a different time. This probably somehow influences the count as well.

cqlsh:timeseries_data> SELECT count(*) FROM test_data WHERE date>='2018-01-01 00:00:00' and date<='2018-01-01 23:59:00' ALLOW FILTERING;

count
-----
1110

It reports 1110 rows instead of 1440 rows.
I'm totally new to Cassandra, so any suggestions will help.
Thank you


texts: How to update views outside of update/insert transaction in SQL Server

texts:
Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it?

There will be about 10 views with 20 indexes that depend on the same table. I don't want to wait for updating views and indexes on views when I do an update on dependent table. I want to be sure that tables are updated within a transaction and views are updated after in some kind of a daemon mode.


texts: Duplicate table names after restoring a backup

texts:
I got this weird issue today when I restored a full .sql backup from one server to another server using dbForge Studio for MySQL (third party software).

Both are having same properties like
table type: MYISAM
latin1_swedish_ci: latin1_swedish_ci
latin1: latin1
I am not using any temporary tables
Is it possible to save same table names in MySQL?

Here is an illustration of the issue:


texts: How badly does innodb fragment in the face of somewhat out-of-order insertions?

texts:
Let's say there's a composite primary key on (field1, field2)

Also, let be X a theoretical set of n numbers

Inserts look like this:

INSERT INTO table(field1, field2)
VALUES([a number picked at random from X], [an incrementing number]);

Every m inserts, a number is randomly removed from X, and another one is
randomly added.

Let's say n=1000 and m=500. In other words, every 500 inserts, one element in set X changes, but set X always has 1000 elements.

How badly will the B+tree become fragmented?


texts: Need advice on Primary and Clustering Keys of a table

texts:
I need advise on a table which is part of a development project I'm involved.
A brief summary on the use of the table:

Data from multiple databases will be consolidated in this table for the purpose of full text searching. Full text index will be on a single column.

I'm trying to decide on the best candidate for the primary (and the clustering) key.   There will be two important fields: ID and Type.    ID is an identity field (which is also primary+clustering keys) on the source tables. Using just this field will result in a conflict because several source tables have the same ID (identity) field.
Type is a look-up table.
Id and Type together is unique.

I came up with the following scenarios:

Id and Type as primary+clustering key.
Create an identity field on the target table (primary+clustering) as well as a ID (synced with source's identity field) and Type.
Create a separate Id field on the target table (clustering). Primary key will be source's ID and type.

What would be the best choice here?


texts: SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

texts:
Consider the following query plan in SQL Server 2014:

In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 rows and just one distinct value of fId (accurately reflected in the statistics). Therefore, this join produces the full cross product of rows (~424MM rows), causing the query to run for several hours.

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Any ideas?

Initial investigation and additional detail

Based on Paul's answer here, it seems like both the SQL 2012 and SQL 2014 heuristics for estimating join cardinality should easily handle a situation where two identical histograms need to be compared.

I started with output from trace flag 2363, but was not able to understand that easily. Does the following snippet mean that SQL Server is comparing histograms for fId and bId in order to estimate the selectivity of a join that uses only fId? If so, that would obviously not be correct. Or am I misreading the trace flag output?

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0

Note that I have come up with several workarounds, which are include in the full repro script and bring this query down to milliseconds. This question is focused on understanding the behavior, how to avoid it in future queries, and determining whether it is a bug that should be filed with Microsoft.

Here is a full repro script, here is the full output from trace flag 2363, and here is the query and table definitions in case you want to quickly look at them without opening the full script:

WITH cte AS (
    SELECT ar.fId,
        ar.bId,
        MIN(CONVERT(INT, ar.isT)) AS isT,
        MAX(CONVERT(INT, tcr.isS)) AS isS
    FROM  #SQL2014MinMaxAggregateCardinalityBug_ar ar
    LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
        ON tcr.rId = 508
        AND tcr.fId = ar.fId
        AND tcr.bId = ar.bId
    GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s
JOIN cte t
    ON t.fId = s.fId
    AND t.isT = 1

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
    fId INT NOT NULL,
    bId INT NOT NULL,
    isT BIT NOT NULL
    PRIMARY KEY (fId, bId)
)

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
    rId INT NOT NULL,
    fId INT NOT NULL,
    bId INT NOT NULL,
    isS BIT NOT NULL
    PRIMARY KEY (rId, fId, bId, isS)
)


texts: How to make gauge label stretch across full width of gauge panel?

texts:
I have a gauge panel (grey) onto which I am trying to put a gauge label (green) that should stretch across the entire panel. However, when I put 100 into the Width property, it doesn't actually stretch across the entire panel -- see the sliver of grey on the right.

The documentation claims that "When parent elements have different width and height values, the size of the gauge element is calculated as a percentage of the smaller of the two values." However, this too is apparently false, as the height is 85% of the width in my panel, and the green background of my label clearly stretches more than 85% of the way across. I've also tried manually setting parent items, to no effect.

Is there something I'm doing wrong here, or is this the typical crappy SSRS bug?


texts: Cannot insert row into table after creating a trigger

texts:
I get an error when inserting a row into a table after creating this trigger:

DELIMITER $$
CREATE TRIGGER aio_mtltp2016 AFTER INSERT ON master_table_linfomastp2016
BEGIN
        IF master_table_linfomastp2016 (IMHQ_solicitado = 'SI')
        THEN
        INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
        VALUES (NEW.ID_CASO, NEW.Tipo_de_estudio, NEW.Año, NEW.Codigo_Interno, NEW.Iniciales_Px, NEW.Sexo, NEW.Edad);
        END IF;
END;$$
DELIMITER ;

The record im inserting is as follows:

INSERT INTO `master_table_linfomastp2016` (`ID_CASO`, `Tipo_de_estudio`, `Año`, `Codigo_interno`, `Iniciales_Px`, `Sexo`, `Edad`, `Servicio_solicitante`, `Roche_Hospital`, `Especimen`, `Macro`, `Diagnóstico`, `subtipo_ltp`, `FISH_solicitado`, `IMHQ_solicitado`, `laminilla_encontrada`, `laminilla_revisada`, `bloque_recibido`, `punch_realizado`, `en_microarreglo`, `validado_por`, `n_marcadores_solicitados`, `Localizacion`, `registro_HU`) VALUES (NULL, 'P', '15', '9106', 'IAS', 'M', '34', 'Hospital Universitario', NULL, 'BIOPSIA', 'REVISION DE LAMINIALLAS', 'LINFOMA NO HODGKIN T PERFIERICO NO ESPECIFICADO', 'Celulas T perifericas (Sin otra especificacion)', 'NO', 'SI', 'NO', 'NO', 'NO', 'NO', 'NO', 'JPF', '12', 'NODAL', '1383250-3')

and it says:

#1305 - FUNCTION linfomas_t_bd.master_table_linfomastp2016 does not exist

where did i go wrong? the insertion of records works if i remove the trigger, but I cannot insert records after creating the trigger and it gives me that error.

If i change the syntax from ` to ' it gives me syntax error. It also says sometimes that the table does not exist. What am i doing wrong? Im using phpMyAdmin but the trigger I made was using direct mysql input, not the trigger creator. The trigger is created without problems.


texts: Migrate Management Data Warehouse to a New Instance

texts:
Is there a defined procedure to follow for moving a SQL Server 2012 Management Data Warehouse to a new server\instance?

We intend to setup MDW on a test SQL Server instance first, but will then want to migrate it to a production installation. I can't find any information on this. Two points which are concerns are:

To migrate the databases without losing data.
How to update the registered instances to report their data to the new MDW instance.


texts: How would I model the Twitter lists functionality?

texts:
I'm working on an application which would mimic the Twitter Lists functionality in which someone can follow someone else. Each user can also create objects which can be followed by users. The Twitter Lists functionality is the best example of the functionality I am wanting to create.

What would this database model look like? I was planning to use Parse.com, but would be open to any representation of the domain model.

Edit: I'll try to add to why I'm asking.
Right now I have this structure (only showing the relevant relationship fields):

User
- username
- activities : Relation<Activity>
Activity
- name
- owner : Pointer<User>
- followers : Relation<User>
Follow
- from : Pointer<User>
- to : Pointer<User>
Subscription
- from : Pointer<User>
- to : Pointer<Activity>
ActivityPost
- activity : Pointer<Activity>
- from : Pointer<User>
- to : Relation<User>

I read through the Parse Anypic tutorial found here: Anypic tutorial. They did something strange to me by combining three relationships into a single table. This makes me think I've perhaps gone overboard with my join tables.


texts: Different sort despite same collation in Postgres

texts:
I am getting different sort results for items with diacritics even though the collation being used is apparently the same.

So, after installing Postgress.app in my mac box for development I create the following table:

create table names (
    id int primary key,
    name varchar
)

and then insert a few values

insert into names values (1, "Francisco");
insert into names values (2, "Ana");
insert into names values (3, "Ágata");

and then query them like so:

select name from names order by name;

I get a wrong sorting order like so:

Ana
Francisco
Ágata

I can confirm with

select datname,
       datcollate
from pg_database;

that the datcollate value for all databases is en_US.UTF-8.

To help me debug this problem, I quickly spinned up an RDS instance, with same data, query and collation, but this time I get the proper expected sort order of:

Ágata
Ana
Francisco

What am I missing here, and how can I configuration postgres in my dev box to behave as expected?


texts:
Two tables called Chip and Dale have m2n-relations to each other. I am used to build a link table called

CHIP2DALE (or Dale2Chip) containing:

id NUMBER UNIQUE NOT NULL
chip_id NUMBER NOT NULL
dale_id NUMBER NOT NULL

and an UNIQUE INDEX CHIP2DALE_UQ ON (chip_id, dale_id), because multiple relations don't make sense.

My question is: Do I still need the id column here? Is there any good reason for having this column? My rows are ultimatly identified by the combination of chip_id and dale_id GIVEN THAT I have absolutely no other columns here but the two.


texts: Unable to store query result inside a variable

texts:
I am trying to query user by email and save the result inside a variable. I tried using await and async, callback and promise but none of them actually work.
const checkEmailUsed = (email) => {
  let sql = "SELECT * FROM users_signup WHERE user_email = ? LIMIT 1";
  return new Promise((resolve, reject) => {
    conn.query(sql, email, (err, res) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(res[0]);
    });
    conn.end();
  });
};

var user = [];
checkEmailUsed("mikias@email.com")
  .then((res) => user.push(res))
  .catch((err) => console.log(err));

console.log(user); // Still []


texts: Driver properties for db2jcc in Squirrell and MON_GET_CONNECTION table function

texts:
I'm trying to set driver properties like:

clientAccountingInformation
clientApplicationInformation
clientProgramId
clientProgramName

in Squirrell 3.6 (http://squirrel-sql.sourceforge.net/). See https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052607.html for a more detailed description of these properties.

I was under the impression that it should be possible to inspect these values with for example:

select APPLICATION_HANDLE, APPLICATION_NAME, APPLICATION_ID
     , CLIENT_WRKSTNNAME, CLIENT_ACCTNG, CLIENT_USERID
     , CLIENT_APPLNAME
from table(MON_GET_CONNECTION(cast(NULL as bigint), -2))

but no matter what values I use, I get null back from the query.

The db cfg regarding metrics are:

Request metrics                       (MON_REQ_METRICS) = BASE
Activity metrics                      (MON_ACT_METRICS) = BASE
Object metrics                        (MON_OBJ_METRICS) = BASE
Unit of work events                      (MON_UOW_DATA) = BASE
Lock timeout events                   (MON_LOCKTIMEOUT) = HISTORY
Deadlock events                          (MON_DEADLOCK) = HISTORY
Lock wait events                         (MON_LOCKWAIT) = HISTORY

Thoughts anyone, what am I missing?


texts: Apache Phoenix: Using MINUS throws an error

texts:
I'm using Apache Phoenix to query Hbase. I tried to use a simple MINUS operator like we do in good old SQL but it produces an error that I couldn't wrap my head
around.

Here's the query:

select * from NOTIFICATION MINUS select * from NOTIFICATION where SUBJECT = 'datanode';

Here's a screenshot:

  Note: It's not this specific query I'm looking to solve, I just want
  to get MINUS to work. Thanks.


texts: How to change the Write Ahead Log directory on Windows

texts:
On postgres 9.6 for Windows server 2008. How do I change the Write Ahead Log directory?

Thanks.


texts: "When specifying a local instance, always use (local)" -- why?

texts:
On the subject of SQL Server connection strings, Microsoft says:

  When specifying a local instance, always use (local).

The implication is that using localhost is a bad idea.  But why?  Or am I reading too much into the wording?


texts: Database modelling best practice question

texts:
I am modelling a database for an application I am developing and I am in front of a choice for which I would like to know what the best practice is.

Let's say I have a Documents table and a Users Table. Documents can be generic or related to a user in three ways:

Created by the user, in which case he has access to the document;
About the user, in which case access to the document may or may not have been granted to him;
Tailored for that user, in which case access to the document may or may not have been granted to him yet;

I could model a Many-to-Many relationship with a table containing a DocumentId field, a UserId field, and a bit telling whether the user has access to the document:

￼

Another approach would be to have a Many-to-Many table with a UserId field and a DocumentId field to link Documents to Users that have access to them, and to also have a UserId field in the Documents table that is Null if the document is generic or that contains the ID of the user it is related to.

￼

The first approach looks tidier because there is only one UserId FK. The second approach however allows to filter Documents by user without the need to join a table.

What is the best practice here?


texts:
I'm trying to execute the following dynamic query code:

  declare @sql nvarchar(max)
  set @sql = 'DECLARE @DatePartitionFunction nvarchar(max) = N''CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
                                             AS RANGE RIGHT FOR VALUES ('';
    DECLARE @incremental_date nvarchar(150);

    select @incremental_date = '+CONVERT(VARCHAR(24),GETDATE(),112)+'
    declare @counter int = 0;
    WHILE @counter < 3
    BEGIN
        SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112) + '''' + N'', '';
        SET @counter += 1;
    END

    SET @DatePartitionFunction += '''' +  CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112)  + '''' + N'');'';
    print (@DatePartitionFunction)
    end;'

exec (@sql);

However, it returns an error stating incorrect syntax.

When I execute the same code outside the dynamic query context, it returns output. Stated below is the same code outside the dynamic query context

        DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
                                                         AS RANGE RIGHT FOR VALUES (';
        DECLARE @incremental_date nvarchar(150);

        select @incremental_date = CONVERT(VARCHAR(24),GETDATE(),112)
        declare @counter int = 0;
        WHILE @counter < 3
        BEGIN
            SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112) + '''' + N', ';
            SET @counter += 1;
            END

        SET @DatePartitionFunction += '''' +  CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112)  + '''' + N');';
        print (@DatePartitionFunction);

It produces the desired output text as imparted below:

CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
 AS RANGE RIGHT FOR VALUES ('20150923', '20150924', '20150925', '20150926');

This is what i want the console to output in case of the aforementioned dynamic query, however I'm being met with errors.
Help!


texts: Changing MySQL Master in Replication: with or without MASTER_LOG?

texts:
Let's say I have server A,B,C. Both B and C have Master A
A - Master
B - Slave
C - Slave

If I am going to change C's Master to Master B, can I change it immediately with query without Master Log information (edit: and also without dumping and importing the database)?

CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='rep', MASTER_PASSWORD='password';

Or do I still need the Master Log Information ?

CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='rep', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000100', MASTER_LOG_POS=100;

If I need to Master Log information when switching Master, the MASTER_LOG_POS is running fast. How can I ensure that the number is not running?
If I enter the wrong MASTER LOG information, what will happen to my database?

Thank you for reading.


texts: Sqlite query slow whilst it should be fast

texts:
I have the following query:

SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z
  FROM BlocksBrokenByExplosion
  WHERE dimension=0
    AND y>60
    AND y<70
    AND x>150
    AND x<250
    AND z>150
    AND z<250
    AND date > '2015-05-08 00:10:00'

With a query plan:

"0","0","0","SEARCH TABLE BlocksBrokenByExplosion
 USING INDEX BlocksBrokenByExplosion_Indexes (dimension=? AND y>? AND y<?)"

The index I am using is:

  CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON
       'BlocksBrokenByExplosion' ('dimension' ASC,'y' ASC,'x' ASC, 'z' ASC)

Analyze shows the following information:

  "BlocksBrokenByExplosion","BlocksBrokenByExplosion_Indexes","78193 78193 2114 25 2"

The problem i'm facing

This query takes 200ms to execute and return 4409 rows which is very poor performance in my opinion and I can't help but feeling this can be optimised.

The table only contains 78.000 rows and according to the query plan the index is used.

The question

How do I optimise this query so it will execute in more acceptable speed, or is this the best I am going to get with sqlite?

And does anyone know why the x and z indexes are not being used according to the query plan?

EDIT 1

I managed to shave off 30ms by reformatting the query in this form

SELECT *
FROM
  (SELECT breakType,date,blockname,
                         modname,
                         metadata,
                         nbtdata,
                         x,
                         y,
                         z
   FROM BlocksBrokenByExplosion indexed BY BlocksBrokenByExplosion_IndexesX
   WHERE dimension=0
     AND x>150
     AND x<250
     AND dimension = 0
     AND date > '2015-05-08 00:10:00')
WHERE z>150
  AND z<250
  AND y>60
  AND y<70

And index

CREATE INDEX 'BlocksBrokenByExplosion_IndexesX' ON
   'BlocksBrokenByExplosion' ('x' ASC)

So now it runs in 170ms, but that's still slower than i'd want it. My target is 50ms at least... So i'm still open to tips and tricks how to make this faster.


texts: Tyring to get a better understanding of ORACLE admin accounts

texts: In reviewing some configuration information for an ORACLE 12C server, I see that the only user assigned the DBA role appears to be SYS account. Also, the only user shown with SYSDBA and SYSOPER privileges appears to be SYS account. My understanding is that these are built in accounts that can perform all administrative functions. Absent other user accounts,  it seems to indicate that the SYS account is used to perform day to days admin functions. Shouldn't there be a "named" user with appropriately assigned privileges,versus using the SYS account?


texts: mysqldump between servers with different timezones (tz-utc)

texts:
If I am moving databases between servers datetime/timestamp fields with different timezones; I have been doing:
mysqldump --tz-utc=false database_name;

then reloading it on the server with different timezone. This appears to work and the dates are correct.
It seems that according to sql manual I shouldn't have to do this:
From manual:

--tz-utc
This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc.

I guess I don't understand exactly how this works.
What is the proper way to move data between servers that have different timezones?
EDIT to show that --tz-utc=false is a real statement
cmuench@cmuench:~$ mysqldump --tz-utc=false pos > 1.sql;
cmuench@cmuench:~$ mysqldump --tz-utc=true pos > 2.sql;
cmuench@cmuench:~$ mysqldump --tz-utc pos > 3.sql;
cmuench@cmuench:~$ diff 1.sql 2.sql

10a11,12
> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
> /*!40103 SET TIME_ZONE='+00:00' */;
2903a2906
> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2913c2916
< -- Dump completed on 2017-07-12  0:29:54
---
> -- Dump completed on 2017-07-12  0:29:57

cmuench@cmuench:~$ diff 2.sql 3.sql
2916c2916
< -- Dump completed on 2017-07-12  0:29:57
---
> -- Dump completed on 2017-07-12  0:30:01


texts: Should I be regularly shrinking my DB or at least my log file?

texts:
My question is, should I be running one or both of the shrink command regularly,

DBCC SHRINKDATABASE

OR

DBCC SHRINKFILE

=============================

background

Sql Server:  Database is 200 gigs, logs are 150 gigs.

running this command

SELECT name ,size/128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0
AS AvailableSpaceInMB FROM sys.database_files;`

produces this output..

MyDB:           159.812500 MB free

MyDB_Log:   149476.390625  MB free

So it seems there is some free space.

Our backup schedule is as follows:

Transaction Logs Once an hour
Full Backups twice a week
Differential Backups 5 times a week


texts: SQL Get All Data With The Given IDs And With The Same Data On Single Column

texts:
So, basically, I have this table
+-------------------
|id  | col1 | name |
|----|------|------|
| 1  |  a   |  la  |
| 2  |  b   |  ria |
| 3  |  w   |  la  |
| 4  |  q   |  la  |
| 5  |  y   |  ria |
|____|______|______|

And, what I want to do is, I want to get all the data based on id 1, 3 and 5 but with the same name. So, since id 5 is different, and id 1 and 3 is the same, I would like to exclude the id 5. I tried with ...HAVING COUNT(name) >=1 but it only shown 1 data. Is it possible? If so, how to achieve this? Thank you
EDIT:
So, what I mean is, I would like to get the result with the same name. Like:
+-------------------
|id  | col1 | name |
|----|------|------|
| 1  |  a   |  la  |
| 3  |  w   |  la  |
|____|______|______|

But, if, for instance in the future, there are case like

"select all data from table where id [1,2,3,5] with the same data in name column"

this will cause a conflict (i guess). the point is, I would like to get the data with the same name where id is given (because I have php app, and the input from the user is only id, and user don't want the different name appear in the result).


texts: Should I record ID numbers in a table where I record who look whom profile page

texts:
This is how I have designed:

visitorID
visitedprofileID
datetime

Is it a good idea to add an ID (primary key) to the table, I mean recordID (primary key).

(MySQL with PHP)


texts: Finding out what permissions are required by the SQL Server Agent Jobs

texts:
I've been looking at this for a while and I can't work out an easy way to do this.

As it stands every job in the database runs under SA level rights (horrifying thought)

I'm sure some of the jobs need quite high level rights for what they're doing but I'm also certain quite a lot do not. I was thinking to create 3 or 5 domain accounts to better control the jobs (its not necessary but having them all run SA is personally worrying).

Anyone know a way that this can be done without simply ripping out all the permissions and granting them one by one until I stop getting errors?

Ste


texts: No advantage of using Cross Apply or CTE over inline sub-query

texts:
I came across a query like this:

SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i

I changed it to following

;WITH cte_count
AS
(
    SELECT COUNT(1) c, OrderId FROM Orders Group By ItemId
)
SELECT a.c [Count], i.Name
FROM Items i
INNER JOIN cte_count c ON (c.ItemId = i.ItemId)

But the execution plan for both is same as shown below:

Similarly there was another query selecting TOP 1 Order By Id. I tried moving this one to CROSS APPLY But for this one as well I got same execution plans.

Of course there were other joins and columns in query.

My dilemma is about utility and advantages of using CROSS APPLY and CTE. Are there any or its just exotic?


texts: Best way to get the latest Identity when inserting through View

texts:
I am using View and View trigger to insert data into multiple tables.
In this case, how can I get the inserted ID of the specific table?
I was using IDENT_CURRENT but it's not recommended.
How can I implement this using SCOPE_IDENTITY?


texts: Is there a good reason to create an additional table to realize a 1:N relation?

texts:
I have the following tables:
Users
_____
UserID
Data
...

Roles
_____
RoleID
Data
...

UsersRoles
_____
UserID
RoleID

Users and Roles are in a M:N relationship. As such I have the UsersRoles table to connect them.
Not connected to the previously mentioned tables, I also created the following tables:
Invoices
_____
InvoiceID
Data
...

Items
_____
ItemID
InvoiceID
Data
...

In this case I did not create an additional table since I am realizing a 1:N relationship here. My question is, if there is any reason for me to remove the InvoiceID from Items and create an InvoicesItems table similar to the UsersRoles table to connect them, even though I would not need it.


texts: Large No of MySQL running threads killing response times

texts:
Yesterday, our MySQL's response time increased 20 times- select queries which earlier used to take 0.6-0.7 ms took 20ms, and updates even more- 60ms.

The MySQL running threads count which usually stays around 10-15 shot up to 300-400, for a good five hours (before coming down when traffic cooled).

Server version: 5.6.25-log MySQL Community Server (GPL), with 32 core machine, 128GB machine (90 GB buffer pool)

Storage engine is Innodb.

Some output from show engine innodb status captured by ptstalk-

  SEMAPHORES
  ---------- OS WAIT ARRAY INFO: reservation count 159291194 OS WAIT ARRAY INFO: signal count 1074930257 Mutex spin waits 1220535470,
  rounds 2921695607, OS waits 26779994 RW-shared spins 291539762, rounds
  1856674850, OS waits 31197621 RW-excl spins 300274870, rounds
  5700270052, OS waits 95339247 Spin rounds per wait: 2.39 mutex, 6.37
  RW-shared, 18.98 RW-excl
  ------------ TRANSACTIONS
  ------------ Trx id counter 20746118705 Purge done for trx's n:o < 20746118666 undo n:o < 5 state: running but idle History list length
  1154

Also,there are large number of os waits, specifically at

  InnoDB  dict0dict.cc:2606       os_waits=60628 InnoDB
  dict0dict.cc:2606       os_waits=131984 InnoDB  dict0dict.cc:2606
  os_waits=81342 InnoDB  dict0dict.cc:2606       os_waits=230958

Also at:

  InnoDB  trx0rseg.cc:196 os_waits=491 InnoDB  trx0rseg.cc:196
  os_waits=458 InnoDB  trx0rseg.cc:196 os_waits=489 InnoDB
  trx0rseg.cc:196 os_waits=483 InnoDB  trx0rseg.cc:196 os_waits=459

Some other information:
1. Query Cache:

| have_query_cache             | YES                               |
| long_query_time              | 0.500000                          |
| query_alloc_block_size       | 8192                              |
| query_cache_limit            | 10485                             |
| query_cache_min_res_unit     | 4096                              |
| query_cache_size             | 0                                 |
| query_cache_type             | OFF                               |
| query_cache_wlock_invalidate | OFF                               |
| query_prealloc_size          | 8192

Adaptive Hash Index is also ON.

What could the issue be? Please let me know if you need more details.

Vmstats snapshot from that time:

procs   -----------memory----------   ---swap--  -----io----  --system-------cpu-----
 r  b   swpd   free    buff   cache    si   so    bi    bo    in     cs us sy id wa st
17  0 685588 2254064 310852 23913820    0    0    96   404     0      0  3  1 97  0  0
 5  0 685588 2256376 310856 23915576    0    0  6816 14092 53915 142794 18  3 79  0  0
 7  0 685588 2256284 310860 23916752    0    0  6288 13136 51433 143163 15  2 82  0  0
 7  0 685588 2254044 310860 23917792    0    0  7904 18572 52465 142161 16  2 82  0  0
 8  0 685588 2254044 310860 23918800    0    0  6512 18244 51958 146916 15  2 82  0  0
 6  0 685584 2252828 310860 23919844    4    0  5444 16820 51750 147625 16  2 82  0  0
 5  0 685584 2251820 310860 23920884    0    0  6864 14084 53609 146373 15  2 82  0  0
 6  0 685584 2251952 310860 23921996    0    0  6016 13112 52862 147601 15  2 82  0  0
10  0 685584 2250164 310864 23923056    0    0  6096 14468 52406 147109 15  2 82  0  0
 7  0 685584 2248936 310864 23924112    0    0  6576 13664 51638 142051 15  2 83  0  0


texts: Select query - group by and sub group by

texts:
CREATE TABLE [dbo].[temptable]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [uid] INT NOT NULL,
    [pid] INT NOT NULL,
    [event] INT NOT NULL,
    [timeStamp] DateTime NOT NULL
)

I want to select count of uid (group by pid) where event = @event AND timeStamp = [MAX of timestamp](group by uid).

Note that pid..uid is 1..many relationship

Id,uid,pid,event,timeStamp
1,20,300,100,'yesterday'
2,20,300,200,'today'
3,20,300,400,'daybefore'
4,30,300,100,'yesterday'
5,30,300,400,'daybefore'
6,40,300,100,'today'
7,40,300,200,'yesterday'
8,40,300,400,'daybefore'

For the above data, I should get below results if @event = 200

pid, count
300, 1

It shouldn't count uid 30 because there is no event 200 for uid 30. And it should also NOT count uid 40, because event for max(timestamp) i.e. today is 100 which doesn't match to @event i.e. 200.

TimeStamp is DateTime field but I've kept is relative in sample data for simplicity.


texts:
I have a table where I need to update only 1 field per employee.  Below is sample ddl and my desired output, how should an update query be written in order to update only the Top 1 row?

Declare @Table1 Table (empName varchar(100), fieldtoupdate varchar(10))

Insert Into @Table1 (empName) VALUES ('Blue'), ('Blue'), ('Blue'),
('Blue'), ('Blue'), ('Blue'), ('Red'), ('Red'), ('Red'), ('Red'),
('Green'), ('Green'), ('Green')

Select * from @Table1

empName fieldtoupdate
Blue    Top 1
Blue
Blue
Blue
Blue
Blue
Red     Top 1
Red
Red
Red
Green   Top 1
Green
Green


texts: Ola Scripts installation in "Multi-server" Jobs Folder

texts: I am installing the Ola scripts on a Central Management Server for multi-server administration. How can I get the jobs to show up under "Multi-server" Jobs Folder instead of the "Local Jobs" folder?


texts: Can I show a relation - "Either in this table or in that " in ER diagram?

texts:
I am making a database for handling leave applications in an academic institute. Given that there is hierarchy existent in such institutes, and I need to keep record of the people that were at these posts, say 'Dean', I have implemented a table 'archived_cross_cutting_faculty' in addition to 'current_cross_cutting_faculty', where cross_cutting_faculty means the guy is dean or something else in addition to being a faculty.
To have a record of retired faculty, i have table retired_faculty in addition to faculty table.

Now, the faculty listed in archived_cross_cutting maybe a retired faculty, hence he maybe in the retired_faculty table. Or he maybe present in the faculty table, i.e. he is currently serving as a faculty but say, he is not a dean now.

Can this relation be shown in ER Diagram? That a person in archived_cross_cutting_faculty is either in the faculty table or in the retired_faculty table? How?


texts:
We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this replicated table Foo which references a non-replicated table Bar:

ALTER TABLE Foo WITH CHECK ADD  CONSTRAINT [FK_Foo_Bar2]
FOREIGN KEY([BarId2])
REFERENCES Bar ([BarId])

After executing this script on the main database, the merge replication seems to send it to the subscriber (and a new line is added to the sysmergeschemachange table). And this is where things go wrong: Since Bar is not being replicated, the constraint/fk cannot be added on the subscriber, and thus replication stops completely, because the schema change script fails. This has caused us to re-setup the whole replication several times now!

I wonder why SQL Server 2008 would send this script to the subscriber in the first place? After all, other relations to non-replicated tables had (correctly) not been added in the initial snapshot either.


texts: Is it possible to attach a description field to the records in INFORMATION_SCHEMA.TABLES?

texts:
In SQL Server, [database name].INFORMATION_SCHEMA.TABLES has the following columns:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE

This table has no additional fields, such as "DESCRIPTION"

This is confirmed by MSDN.

My question is this: other than making a manual database or a Word document of my own, is there a system or other column or table that I can use to store a "description" or "memo" value per record in the INFORMATION_SCHEMA.TABLES table?

To deal with this as much as possible I use database, table, and field naming conventions, but that can go only so far. Some of the names have a "back story" that can be explained to future team members in a "description" field.

I have tried so far:

Google searches for related questions.
Looking for a "description" field of the table properties in SSMS (see screenshot below), but it wasn't what I had in mind.

Is there any preexisting field in a system or other table that I am missing and matches my need?


texts: Why Are My Mirrored Databases Changing States on Their Own?

texts:
Setup:

SQL Server 2005 w/SP4 64-bit Standard for all three servers
Windows Server 2008 R2 Enterprise w/SP1 64-bit for all three servers
01 is designated as the Principal
02 is designated as the Mirror
03 is designated as the Witness

My issue is that there are Principal and Mirrored DBs on both servers and it is causing Maintenance Plans and SQL scripts to fail.

My questions:

Why are the DBs failing from one to the other when 01 should have all Principal DBs?
How do I stop this behavior
What is the least invasive process of changing the Principal DBs on 02 back to 01?


texts: Should I run multiple DBCC Shrinkfile if files are in different disk arrays?

texts:
We just deleted about 45% of the data in a database, and we want to reduce the size of the mdf files (now we have a "delete old data job", so it won't grow that much again in the near future).

My question is:

If I execute more than one DBCC Shrinkfile at the same time, and the files are on different array disks, will the total time of shinking be shorter?
Compared against executing DBCC Shrinkfile one file at a time.

This will be executed on a maintenance window, and the idea is to have this maintenance window as short as possible.

Regards


texts:
A while ago I've created a trigger in Postgresql, update_user, which sets the update_date to now() for table users. Now I want to drop this trigger.

The following queries don't work:

drop trigger update_user;
drop trigger update_user on users;

I get the following error back:

ERROR:  syntax error at or near ";"

How can I drop this trigger?


texts:
I trying to implement following scenario using postgres :

Server A :
This server is used for product A production stage

Server B :
This server is used for product B production stage. This B product is extension product of A which applicable for certain country.

I would like to replicate all changes data on A into B (I understand this can be achieved using master-slave replication), but in same time, there is feature so that People on server B can add new record, and this record should not write into A.

Is there any replication scenario using postgres which suit to above scenario ?

Best


texts: Should we manually failover again if the node which was originally a primary role and went down recovered?

texts:
We have an SQL Server 2016 standard edition with Basic Availability Groups(BAG) setup between dbserver1 and dbserver2.
This is our first time to implement the BAG and wanted to know what to do in a failover scenario like below:

In the beginning, dbserver1 is acting as a primary, dbserver2 is acting as a secondary
dbserver1 went down and an automatic failover takes place, and dbserver2 has become the primary
dbserver1 is recovered and came up, acting as the secondary since dbserver2 is now the primary
Should we manually trigger a failover to the dbserver1 to make it a Primary again?

The reason is that any database objects (SQL agent jobs, maintenance jobs and such) only exist in the dbserver1, and if we keep dbserver2 running as a primary node, then it can potentially miss the jobs that were supposed to run.
Should we always target to have a particular node to be Primary (in this case dbserver1), or each node should be exactly the same including all the objects outside of Availability Groups so that any node can take primary role at any time, and should be kept being primary node once a failover occurred? (possibly using DBAtools to keep objects in-sync)


texts: how to migrate certain filegroups data with partitions to another server periodically

texts:
I have 3 partition tables on multiple file groups on monthly basis., each month it generates about 1-2TB data on one partition, one filegroup. For more than 1 month old data, we set the filegroup to read-only.
Now the question comes, disk space is nearly full, I would like to move more than 1 year old data another server, and will repeat the same job each month, we will need to query the old data, just not that frequently.
I thought I can backup filegroup and restore filegroup, but apparently that won't work, as piecemeal restore is one time off thing, you cannot incrementally restore new filegroups.
The only solution I can think now is using partition-switch on the old data filegroup(sure need to change the filegroup to writeable), then BCP or transfer data through linked server, then drop table, and since filegroup cannot be deleted, I have to shrink the data file to smallest.
Do you have better solution? thanks!


texts:
I have a question about triggers performance.

CREATE TABLE [dbo].[_test](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Date] [DATETIME] NULL,
[DateYearID] [INT] NULL,
[DateQuarterID] [INT] NULL,
[Date1] [DATETIME] NULL,
[Date1YearID] [INT] NULL,
[Date1QuarterID] [INT] NULL)

Now I want to have trigger that must update DateYearID and DateQuarterID columns if I update Date column (or insert a new row) , and must update Date1YearID and Date1QuarterID columns if I update Date1 column (or insert a new row). What is better, have a single trigger like

IF UPDATE(DATE)
   UPDATE _test SET DateYearID = ... , DateQuarterID = ...
IF UPDATE (DATE1)
   UPDATE _test SET Date1YearID = ... , Date1QuarterID = ...

or have two different triggers, first of them updating DateYearID,DateQuarterID columns, and the second one updateing DateYear1ID,DateQuarter1ID columns.

I am using SQL Server 2014.

Thanks a lot for help.


texts: BULK INSERT getting "Cannot bulk load because ... Access is denied" on a file I have access to

texts:
I'm trying to run a BULK INSERT but I'm getting an access denied error.  So far I've tried the following:

I can open the file from my machine
I'm logged into the SQL Server (tried v2008 R2 & v2016) using windows auth
I have sysadmin permissions
I can type the file using xp_cmdshell (uses the SQL service account but it eliminates the idea that it's a firewall)
Path is a network URL not a local drive

The command I'm using is this:

BULK INSERT ken_temp
FROM '\\network_path\temp\kdf\text_file.txt'
WITH
   (
    FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'
   ,ROWS_PER_BATCH = 50000
   ,MAXERRORS = 10
   )

Any ideas?


texts: Can't create indexed view against table with masked columns

texts:
I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view.

create unique clustered index [IX_Name]
on dbo.vw_ViewName(SomeUniqueId)

I get this error:

  The index on view 'dbo.vw_ViewName' cannot be created
  because the view is referencing table 'dbo.TableName' with masked
  columns.

In another environment, where masking is not enabled, the index is created successfully.

I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index.

Here is some SQL to reproduce the issue:

drop view if exists dbo.vw_Aggregate
drop table if exists dbo.MainTable, dbo.SecondaryTable
go

create table dbo.MainTable
(
    MainTableId uniqueidentifier primary key,
    SomeExternalId uniqueidentifier,
    SecondaryTableId uniqueidentifier
)
go

create table dbo.SecondaryTable
(
    SecondaryTableId uniqueidentifier primary key,
    CreatedOn datetime,
    Amount decimal(19, 8),
    -- the below column produces error,
    -- if commented out - there is no error
    [Description] nvarchar(max) masked with (function = 'default()'),
    Dummy int
)
go

create view dbo.vw_Aggregate with schemabinding
as
    select AggregateId = m.MainTableId,
           m.SomeExternalId,
           s.CreatedOn,
           s.Amount
    from dbo.MainTable m
    inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId
go

create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)]
on dbo.vw_Aggregate(AggregateId)
go


texts: Plsql query to split data based on startdate and end date

texts:
I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly)
For example if the
Startdate is 2015/10/02 and the
Enddate is 2015/12/22
and my date range is Monthly (M)
then my required output is:
Output
-------------
Newstartdate newEnddate
2015/10/02.   2015/10/31
2015/11/01.   2015/11/30
2015/12/01.   2015/12/30

So I was looking for a generic PL/SQL query to split data what ever date range can be(Y/Q/M/W/D) based on the start and end dates and the range specification.


texts:
I have a partial index where a certain column is not null. This is a very small percentage of the table. Thanks to this index, SELECT * FROM table WHERE column IS NOT NULL is incredibly fast (5 milliseconds). But the table has hundreds of millions of rows.
If I want to create a second index on the same set of rows (where that same column is not null), how can I make Postgres use the first index that already exists to find those rows? Currently Postgres just scans the entire table to find them again, which takes many minutes. I can query all the rows in millisceonds, so why can't CREATE INDEX get them in the same way?


texts: Unexpected Seq Scan when doing query against boolean with value NULL

texts:
I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.

CREATE INDEX index_table_on_auto_renew ON table USING btree (auto_renew);

When I query the field for a boolean value, PG uses the index as expected.

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" = 'f'
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table  (cost=51.65..826.50 rows=28039 width=186)
   Filter: (NOT auto_renew)
   ->  Bitmap Index Scan on index_domains_on_auto_renew  (cost=0.00..44.64 rows=2185 width=0)
         Index Cond: (auto_renew = false)
(4 rows)

When the value is NULL, a sequential scan is used.

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" IS NULL
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on table  (cost=0.00..1094.01 rows=25854 width=186)
   Filter: (auto_renew IS NULL)
(2 rows)

I'm curious to know the reason behind this choice.


texts: Why can a log backup not be taken without first taking a full backup?

texts:
I've created a new database using the FULL recovery model. Now I am trying to take a log backup without first taking a full backup.

I'm aware that SQL Server won't allow us to do so, but I want to know why SQL Server is preventing it?

Below is the script I've used for testing purposes:

CREATE DATABASE TESTING
GO
BACKUP LOG TESTING TO DISK='G:\TESTING_LOG.TRN'


texts: Is it possible to create encrypted backups using encrypted by password certificate?

texts:
I have encrypted by password certificate, which I want to use for backup encryption. The issue is I am not able to use the certificate (in T-SQL statement or maintenance plan) because I am not able to open it  - there seems not to be OPEN CERTIFICATE command.

I want to have certificate encrypted by password as our goal is to have only one user who will be responsible for creating/inserting the certificates in the SQL Server Instances. No one else will be able to export the certificate and restore an encrypted backup.

Is it possible, or in order to have encrypted backups I always must have the certificate encrypted by the database master key?


texts: Running queries directly on DB2 databases

texts:
Just have a general query, if it is really fine or safe to run long select queries on RDBMS production Databases specifically DB2? If NO, what are the major risks and issues in executing select queries directly on the backend production DB2 database?

Regards
Muddu


texts: PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()?

texts:
i have a table with tsvector type column (col).

I fill some rows via script and use cast data::tsvector, but (!) some rows was filled with to_tsvector(data), so i have rows with different values like this:

'в' 'г' 'зея' 'иеговы' 'местная' 'организация' 'религиозная' 'свидетелей'
'г':7 'зе':8 'иегов':5 'местн':1 'организац':3 'религиозн':2 'свидетел':4

It was surprise for me :) So what the overhead for values not prepaired with to_tsvector(data) ? Will pg do to_tsvector on runtime or what?


texts: Percona pt-table-checksum output is in infinite loop "Waiting for the --replicate table to replicate"

texts:
We have following Multi master replication scenario. We are using Mariadb 10.1.34 and percona 3.0.11

Master:  Name: db-master1 (192.168.14.5)
Slave:   Name: db-slave2 (192.168.15.5) Replica Connection name:
master1 (192.168.14.5)

SHOW SLAVE 'master1' STATUS\G on slave server is working fine.

On execution of following pt-table-checksum command on DB Master (db-master1)

pt-table-checksum --replicate=percona.checksums --ignore-databases mysql,Quotient h=localhost,u=checksum_user,p=checksum_password --recursion-method=dsn=D=percona,t=dsns  --no-check-binlog-format
Checking if all tables can be checksummed ...
Starting checksum ...
Replica db-slave2 is stopped.  Waiting.
Replica db-slave2 is stopped.  Waiting.

On Master1-db, the percona.dsns table have entry for db-slave2

The Above Waiting information is continue in infinite loop.

Look forward any support


texts: Creating a column in dataset for later filter use (mysql)

texts:
I have a date column (format: 14.05.2018) and a count of sales. I would like to create an extra column that from the current date perspective flags fields that are +/-6 days from today. The following query does not work quite well, the column is empty
select `Date`, `Sales`,
(CASE
    when DAY(CURRENT_DATE())-6 <= DAY(`Date`)
            AND DAY(CURRENT_DATE())-6 >=  DAY(`Date`) then '1'
    when DAY(CURRENT_DATE())-6 > DAY(`Date`)
            AND DAY(CURRENT_DATE())-6 < DAY(`Date`) then '0'
 end as Flag)
from sales;


texts:
I've a table of students score

      ID_STUDENT | SCORE
     ------------------
           1     |  90
           2     |  80
           3     |  99
           4     |  80
           5     |  70
           6     |  78
           7     |  90
           8     |  50
           9     |  90

So lets say on first day I'll compute the rank and store it in one column say RANK -

      ID_STUDENT | SCORE  | RANK
     ----------------------------
           3     |  99    |  1
           1     |  90    |  2
           7     |  90    |  2
           9     |  90    |  2
           2     |  80    |  3
           4     |  80    |  3
           6     |  78    |  4
           5     |  70    |  5
           8     |  50    |  6

on second day I'll refresh the score and will recompute the rank. However, here is the thing, I need the records of prompt past rank as well, like this -

      ID_STUDENT | SCORE  | RANK | OLD_RANK
     ---------------------------------------
           2     |  99    |  1   |  3
           8     |  92    |  2   |  6
           1     |  90    |  3   |  2
           9     |  90    |  3   |  2
           3     |  80    |  4   |  1
           4     |  80    |  4   |  3
           6     |  78    |  5   |  4
           5     |  70    |  5   |  5
           7     |  40    |  6   |  2

So using this outcome, I would able find the rank trend,much the same as you can see the songs position going up/down over a week as shown here - https://www.billboard.com/charts/hot-100

How can I achieve this using straightforward DML queries?


texts:
I have an objective to record and store test data. A secondary objective is to track the progress of the test programme (it is a large programme). So I want to use MySQL (not Access or DOORS) to create a database that will store this data.

I have created an initial database design with the following extract

Attributes:

EvidenceRequirement: Id, Title, Description;
EvidenceSource: Id, Name;
TestType: EvidenceSource.Name, Title, Description;
TargetTestPoint: Id, ???

Now the question marks above highlight my problem. Each Test type gathers different data and so the content of the TargetTestPoints will also vary (e.g. some test points will require a mass (kg) attribute, some won't). So the above is not sufficient to store the data.

The only alternative design idea I have thought of, is to create a new table for each test type which stores this content. I haven't got an issue with having too many tables but to me it is not very elegant as every entry in these new tables will link to the same evidence requirement. This just seems wrong to me?

So the question is, has anyone got a more elegant solution?

NOTE: The design is an initial attempt so it has not been properly normalised and there may be other alterations. Also, this is an extract of a bigger database design.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 I thought I'd give more of a description to aid comprehension (as I may be being a bit vague):

Evidence Requirements: This will be the requirement that is driving the testing. Example: The aircraft will demonstrate it has a positive longitudinal static stability.
Evidence Source: This is the test method that will satisfy the requirement. So for my example it would have an entry of "Longitudinal Static stability testing"
Test Type: More information on the test method (such has what, why and how). The example here would include the detail for conducting LSS testing.
TargetTestPoint: These are the configuration details for the test point. So for our example, what is the airspeed, pressure altitude, a/c mass, a/c CG etc...

So if I wanted to add new evidence source for a different evidence requirement (say add manouvre stability or pressure error corrections (PEC)), the data for these in the target testPoints will be different (e.g. a/c mass and a/c CG are irrelevant to PEC)

Anyway I hope that helps.


texts: More Efficient 3-Period Rolling Average Calculation

texts:
I have a table of numeric answers to survey questions. The goal is to call a stored procedure, with a parameter for PeriodType ("Quarter", "Month", or "Year") that returns this output:

Year    Period    Score    WeightedAverage
2019    1         85.7     85.7
2019    2         87.6     85.9
2019    3         90.5     88.6
...    ...        ....     ....

Couple of notes:

WeightedAverage should be a 3-period rolling average, but NOT the average of the current and two previous averages. It should take into account all individual answers so that a period with 1000 answers has a larger effect than a period with 50 answers. Using windowed functions like LEAD/LAG or AVG OVER() do not seem to be the answer.
Setup is depicted below, and the #answers table has roughly 302,000 rows in it. The final query itself takes 35 seconds. Would like this optimized as best as possible.
The table scan to get #answers [c] creates an index spool (eager) and then a table spool (lazy) in the execution plan. Row estimate 34; execution estimate 2,705; actual rows 160,773,768; actual executions 33,037 (yikes). There has to be a faster way that my inferior brain can't solve.
Running SQL Server 2017 Enterprise, but compatibility level 100 (SQL 2008)

Code:

CREATE TABLE #answers (   ResponseID int,   QuestionID int,   Answer float,   CompletedDate datetime,   dateID int    )
-- code to insert 302,000 answers would go here
-- create a table of all date possibilities (there are no gaps), with an identity  for each

SELECT id = identity(int, 1, 1), [year] = DATEPART(year, CompletedDate), period = CASE @PeriodType WHEN 'Month' THEN DATEPART(month, CompletedDate) WHEN 'Quarter' THEN DATEPART(quarter, CompletedDate) ELSE NULL END
INTO #dates FROM #answers
GROUP BY DATEPART(year, CompletedDate), CASE @PeriodType WHEN 'Month' THEN DATEPART(month, CompletedDate) WHEN 'Quarter' THEN DATEPART(quarter, CompletedDate) ELSE NULL END
ORDER BY DATEPART(year, CompletedDate), CASE @PeriodType WHEN 'Month' THEN DATEPART(month, CompletedDate) WHEN 'Quarter' THEN DATEPART(quarter, CompletedDate) ELSE NULL END

-- update the answers table with the assigned dateID identity
UPDATE a SET dateID = b.id
FROM #answers a
INNER JOIN #dates b ON DATEPART(year, a.CompletedDate) = b.[year] AND ((CASE @PeriodType WHEN 'Month' THEN DATEPART(month, a.CompletedDate) WHEN 'Quarter' THEN DATEPART(quarter, a.CompletedDate) ELSE NULL END) = b.period OR (b.period IS NULL AND @PeriodType NOT IN ('Month', 'Quarter')))

-- join the answers to themselves based on (dateID - 2) for 3-period score
SELECT #dates.[year], #dates.[period], internal.score, internal.weightedaverage
FROM #dates
LEFT OUTER JOIN (
    SELECT b.[year], b.period, score = CAST(10 * AVG(a.Answer) AS decimal(10, 1)), weightedaverage = CAST(10 * AVG(c.Answer) AS decimal(10, 1))
    FROM #answers a
    INNER JOIN #dates b ON a.dateID = b.id
    INNER JOIN #answers c ON c.dateID > a.dateID - 2 AND c.dateID <= a.dateID
    GROUP BY b.[year], b.period
) internal ON #dates.[year] = internal.[year] AND #dates.[period] = internal.[period]
ORDER BY #dates.[year], #dates.[period]

Apologies for the formatting, I don't see how to indent and clean up how this t-sql is displayed. Thank you for your help.


texts: MySQL Partitioning on event tables with start and end datetime columns

texts:
I am curious as to what solutions are out there for partitioning tables that log events, that allow both efficient querying and the advantages of pruning when truncating data.

Suppose I have a simple table that records events from different locations:

tblEvents (
  event_id,
  location_id,
  start_datetime,
  end_datetime
)

Most queries on this table will take the form of:

SELECT    event_id
FROM      tblEvents
WHERE     location_id = @queried_location_id
AND       start_datetime < @queried_end_datetime
AND       end_datetime > @queried_start_datetime

To date, I have partitioned by the location_id, and simply just indexed the datetime columns. Performance-wise, this has sufficed, and I never planned to keep more than a couple of months worth of data in the database, so it seemed future-proof as well.

The problem arose when I actually went to purge data from this table (it is very large and also involved in replication). Using DELETE FROM tblEvents WHERE start_datetime < @some_date proved to be very slow, and created problems with requests being made from other clients (not surprisingly).

Partitioning by either of the datetime columns is no good. Although it solves my data purging problem, it makes the query above inefficient, as it still needs to look at multiple partitions depending on the dates being queried.

Am I missing a common solution here? Is there a more efficient way to purge my data? Or is there a smarter way to partition/index that I have missed?


texts:
My query and my data set (copied from sqlfiddle):

CREATE TABLE one (
    id int4 primary key,
    p_id int4,
    k_id int4
);
CREATE TABLE two(
   id int4 primary key,
   p_id int4,
   k_id int4,
   t_id int4,
   pos int4
);

INSERT INTO one(id, p_id, k_id) VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4);
INSERT INTO two(id, p_id, k_id, t_id, pos) VALUES
    (1, 1, 1, 1, 1), -- t_id = 1 and pos = 1
    (2, 1, 2, 1, 2),
    (3, 1, 3, 1, 1), -- t_id = 1 and pos = 1
    (4, 1, 4, 1, 3),
    (5, 1, 1, 2, 3), -- shares p_id and k_id with row 1
    (6, 1, 2, 2, 1),
    (7, 1, 3, 2, 5), -- shares p_id and k_id with row 3
    (8, 1, 4, 2, 6);

Table one is a join table, I've added it because in the future I might add more columns into it, and then it will be easier to modify this report.

What I want is to be able to filter my dataset like so:

Get all the rows where pos = 1 for t_id = 1

Expected output:

p_id, k_id, stats
1,     1,    [{p_id: 1, k_id: 1, t_id: 1, pos: 1}, {p_id: 1, k_id: 1, t_id: 2, pos: 3}
1,     3,    [{p_id: 1, k_id: 3, t_id: 1, pos: 1}, {p_id: 1, k_id: 3, t_id: 2, pos: 5}

As you can see I want to append to my results the data for others t_ids where p_id and k_id are the with the found results.

Update:

What will be the best way of removing duplicates?

Adding (9, 1, 1, 1, 20) to my dataset will result in having the same type twice in a row;

One solution to get only uniq types and the smallest pos values is:

WITH uniqt AS (
  SELECT   p_id, k_id, t_id, min(pos) as pos
  FROM     two
  GROUP BY 1, 2, 3

)

Because I am selecting from table one 50 rows (limit 50) and then join a table with SELECT like in @Erwin Brandstetter example, having this CTE will slow down my query?


texts:
I am working on an ERD which represents the relationships between entities in a Web development company behavioral model.
There I have entities like:

Customer
Service ----------- (like: web development, web design, web consultation, ...)
Order-item ------- (like: web development, web design, web consultation, ...)
Order
Invoice
Project
Project-phase
Task --------------- (like: web development task3, web design task7)
Developer
Delivery ----------- (like: the developed web app, web consultation session, ...)

I have this diagram so far, though I know it still has major problems, and it is just for a start point:

Main concept

Customer makes order-items on services, combined to form an order which relates to an invoice.
Each project has multiple project-phases with each project-phase related to an order.
Each project-phase has multiple tasks, each of which is assigned to a developer.
Each project-phase has a delivery, which will be delivered upon payment of the related orders invoice.

Questions

Are has and contains completely the same? If not, how to choose between them for relationships?! what is the measure?!
Are there any rational problems with the Main concept section?
Any helpful ideas or suggestions to improve this model?

Any help with this, would be really appreciated.

Update 1

Refined version of the diagram

Update 2

Considering an analogy of a Fast food restaurant would be helpful:

Customer ---------- A hungry person
Service ------------- Making a burger, Home delivery, Mobile delivery, ...
Order-item --------- A burger, ...
Order ---------------- A home-delivered burger
Invoice -------------- The printed invoice
Project -------------- Serving the customer for a complete year (like a contract)
Project-phase ----- Serving the customer for a single day
Task ----------------- Preparing the kitchen, process the meat, ...
Developer ---------- The cookie
Delivery ------------- A burger in wrapped in wax papaer and a drink with the tray

Update 3

(1st comment from  Joel Brown)

  Project seems to be unrelated to Customer in your diagram. Is this what you intended?

I think Order is and Project should not be related, as I think it is something off-the-counter and customer has nothing to do with it. she/he is only after the delivery and is concerned about time and money, nothing else. Please tell if I am wrong.

  Are all Orders part of a Project or can you have orders that aren't in a project at all?

All Orders are part of a project, except those new ones which are waiting in the process queue.

  Similarly, you relate order item directly to customer, but also to order. Are orders split between customers?

Please think of Order-Item as these:

web design
web design consultancy
domain name consultancy
...

So, customer choose some of these items, collected as an Order which has an invoice and could be paid. Order-Items are directly related to offered Services. I don't understand what do you mean by Are orders split between customers?.

  If not you don't need a ternary relationship between customer, order item and service. Rather, an order item is an intersection between order and service. Order items belong to customers by inheritance from order (or even project?)

This is somehow true: Order items belong to customers by inheritance from order, but I think it is more rational to say it like this:

  Order-Items, inherited from Service, belong to Order which itself belongs to Customer.


texts:
I am getting following error after modifying the innodb buffer pool size values.

InnoDB: mmap(137428992 bytes) failed; errno 12
InnoDB: Cannot allocate memory for the buffer pool

137428992 is innodb_buffer_pool_chunk_size.

I am having a machine with 256 GB RAM.

Following is what I added to my.cnf

innodb_buffer_pool_size=200G
innodb_buffer_pool_instances=40

All the other values are default. Resolution for similar questions are either increase RAM or decrease buffer pool size. If I monitor the memory usage when mysqld is starting, I see that it increases to 8-9 GB before the failure occurs.

Following is free -g output

              total        used        free      shared  buff/cache   available
Mem:            251           2         247           0           1         248
Swap:             3           0           3

What could be causing this issue?


texts: I am new in rds, but I am facing server performance issue. I got these two variable but I am confused what exactly is difference between      innodb_thread_concurrency and slave_parallel_workers.


texts: Look for key in jsonb array, LIKE with @>

texts:
How can I use @> to look into a jsonb array, to find data like '%Par%'  or '%ar%'?  The data in the table looks like

[
  {"city":"Paris","country":"FRA"},
  {"city":"Parana","country":"ARG"}
]

I prefer a solution with @> because I'm using sequelize, and the ORM have no full syntax conversion to/from jsonb.

I was trying something like that , but give error:

select * from "customer" where   "customer"."location" #>>'[{city}]'  LIKE '%ar%'

this give 0 results:

select * from "customer" where   "customer"."location" ->>'[city]'  LIKE '%ar%' ;


texts: How can I remove a bad execution plan from Azure SQL Database?

texts:
DBCC FREEPROCCACHE doesn't work in Azure SQL DB. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)? This is specifically for SQL created by Entity Framework, so these aren't self-managed stored procs - it's effectively dynamic SQL.

(Source was bad indexes -> bad stats, etc. That's all fixed, but a bad plan won't go away.)

UPDATE:
I selected @mrdenny's solution as he got there first. I am, however, successfully using @Aaron Bertrand's script to perform the work. Thanks to everybody for the help!!


texts: See if XML element exists at any level in document with a specific value

texts:
Is it possible to query XML to find if a particular element has a certain value? For example, if I wanted to see if the below XML has the value "Brandt" in <ContactFName>.

But note, the location of the element may change. In some cases it may be in /root/MCTLocations/MCTLocation, or it may jump to under the root, or appear somewhere else...

And, is it possible to parameterize the element name?

DECLARE @table TABLE (XmlCol XML)

INSERT INTO @table (XmlCol) VALUES ('
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
    <MCTLocation>
        <Address>1234 Main Street</Address>
        <ContactFName>Chris</ContactFName>
        <ContactLName>Brandt</ContactLName>
    </MCTLocation>
</MCTLocations>
</root>')

SELECT * FROM @table WHERE ??


texts: Explaining what the OR operator does in the following code

texts:
I ran into the following SQL code which (successfully) outputs a list of parents and the age of their youngest child:
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON (parents.id = children.fatherId OR parents.id = children.motherId)
GROUP BY parents.id

The code self joins a table named "people" on itself. I just wanted to ask how does the OR operator work here? I know OR as a logical operator but here it seems it does something else. It takes two arguments and just joins on both of them. What does it have to do with logical OR?


texts: Give permission to select from system tables

texts:
I have a database Microsoft SQL Server 2008 r2.
A user called marie is defined, who can access tables on the main database; this works well.
Now, I would like marie to be able to perform this query:

SELECT resource_type,spid,login_time,status,hostname,program_name,nt_domain,nt_username,loginame
  FROM sys.dm_tran_locks dl
  JOIN sys.sysprocesses sp on dl.request_session_id = sp.spid

The tables involved are master tables; how do I give permission to marie to read from them?
I already tried to execute the following as dbo:

GRANT ALL on sys.dm_tran_locks TO marie
GRANT ALL on sys.sysprocesses TO marie

Still, when marie tries to perform the query above, the error is:

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

What am I doing wrong?


texts: DB2 archive log extraordinary growth for any operation after runstat/reorg

texts:
My environment:
DB2 10.1.4 Enterprise, IBM Security Identity Manager 6.0 FP6, AIX 7.1
Each archive log have a size of 40MB

Yesterday, we do a maintainance on ISIM database by doing online backup. The backup size is around 7GB. After that, we prune the ISIM database by using
db2 prune history 20151001 and delete

Then we runstat and reorg all the table in ISIM database (No automatic maintainance is set on this database and never runstat/reorg for more than 6 months) using Data Studio 4.1.0.

After we reorg the database, we runstat again and notice an increase amount of space that archive logs take up (around 3 GB of archive log generated during runstat). So we thought something might hung inside db2 process so we restart db2 using db2stop / db2start

After restart DB2, we test the system and everything look fine, around 10GB available after we restart DB2 in the night. However, in the morning we found out that all spaces are used up by archive log.

We allocate 10GB to the disk in the morning and it exhausted very fast in a way that we never seen before.
Normally, when ISIM reconcile one system, we didn't even notice the change in archive log's size even it is reconcile every hour, however after last night maintanace, The same reconcile took up more than 50 GB of space even it is just one time reconcile and it slow down ISIM reconcile process as well, all of those space occupied by archive logs that seems to generate new file every second during reconcile.

Now, our HADR DB can't catch up with this amount of archive log file and all are disconnected, we decided to fix the problem on main DB first but using db2pd to check any active runstat and reorg didn't give any result. Restore the database now is not an option as it is a production database. We think that we might do offline runstat / reorg tonight but we don't sure if it can solve problem or not.

Anyone have any suggestion?


texts: For some reasons I cannot install MongoDb 3.0 version. What am I missing?


texts: How to prioritise delete and update based on timestamp during replication?

texts:
We have two MS SQL Server 2012 servers running merge replication.
We have used the SQL Server DATETIME (Later Wins) Conflict Resolver to resolve conflicts based on the latest updated time.

However, in case of row deletion, the delete operation always takes priority. Example:

On the publisher a row is deleted at 10:00 hrs.
On the subscriber the same row gets updated at 11:00 hrs.
My synchronisation cycle started at 11:30 hrs.

The default behaviour here was to delete the row. However, we wanted the row to be updated at the publisher from subscriber data, as per the last modified time. How to achieve that result in such scenarios?


texts: Which version of xtrabackup to use with MySQL 5.5?

texts:
I've just installed a copy of Percona xtrabackup onto my Debian (jessie) server, but when I run it it says that it is:

innobackupex version 2.3.5 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 45cda89)

But I am using MySQL 5.5. Is this the correct version of innobackupex to use with my databases, and if not which version should I use? The documentation isn't very clear.

I installed it using the following process:

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

sudo pico /etc/apt/sources.list

then add the following two lines
    deb http://repo.percona.com/apt jessie main
    deb-src http://repo.percona.com/apt jessie main

apt-get update

apt-get install percona-xtrabackup


texts: Trouble setting up Postgres for the first time

texts:
I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself.

Postgres is installed and running. But I cannot connect from a client.

pg_hba.conf looks like this (I added the last line):

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

## Added by me
host    all             all             10.0.1.201/32           trust

Now if I go to the client 10.0.1.201 and try to psql -h server.domain.com I get an error:

psql: could not connect to server: Connection refused
    Is the server running on host "server.domain.com" (10.0.1.38) and accepting
    TCP/IP connections on port 5432?

ssh authorized_keys are setup for this client <-> server connection. There is also Kerberos as an option. Seems I might have too many options here? Not quite sure how to troubleshoot this? I can verify Postgres IS running.

meltemi$ sudo lsof -i -P | grep -i ':5432'
meltemi$

leads me to believe maybe it's not listening?

ideas?


texts: Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2)

texts:
From PG DOC

  pg_column_size(any) : number of bytes used to store a particular value (possibly compressed)
  pg_column_size shows the space used to store any individual data value

Example:

select pg_column_size(5::smallint);    -- 2 bytes
select pg_column_size(5::int);         -- 4 bytes

With input of pg_column_size, it can be a column or row, so I created a test to check it.
Here's my test:

My table

CREATE TABLE index_test
(
  id integer NOT NULL,  -- 4  bytes
  a integer,            -- 4  bytes
  b integer,            -- 4  bytes
  CONSTRAINT index_test_id PRIMARY KEY (id)
)

1/ First query:   sum(pg_column_size(table.rows))

with abc as
(
 select id,a,b
 from index_test where b > 100
)
select pg_size_pretty(sum(pg_column_size(abc.*))) from abc  -- "348 kB", abc.* = record

And query's explanation:

"Aggregate  (cost=427.55..427.56 rows=1 width=24) (actual time=9.171..9.171 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.039..2.882 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=24) (actual time=0.047..7.151 rows=9902 loops=1)"
"Total runtime: 9.376 ms"

2/ Second query:  sum(pg_column_size(table.id)) + sum(pg_column_size(table.a)) + sum(pg_column_size(table.b))

with abc as
(
 select id, a, b
 from index_test where b > 100
)
select  pg_size_pretty((sum(pg_column_size(id)))  + (sum(pg_column_size(b))) + (sum(pg_column_size(a))))
from abc  -- "116 kB"

And query's explanation:

"Aggregate  (cost=526.57..526.59 rows=1 width=12) (actual time=10.959..10.959 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.035..2.780 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=12) (actual time=0.039..5.623 rows=9902 loops=1)"
"Total runtime: 11.173 ms"

3/ Results:

First query:  348 KB

Second query: 116 KB    ( pg_column_size(id) = 39 KB ...)

I thought both query had to return the same result, but size of first query = 3 * size of second query, It made me confused. In first explanation, the "width = 24 bytes / row " (instead of 12), I wondered why it is increase, I think it is the clue to the question . Until now, I can not find clear answers for my question, please help me out  .


texts: How to create a dummy Linked Server in SQL Server 2012

texts:
I have a database in two different production servers. I want to keep the schemas consistent between the two. One has jobs that call stored procedures that reference a Linked Server. The other does not (and should not) contain those jobs or a Linked Server of the same name as that in the first server, but I want both servers to have those same stored procedures. However, SQL Server refuses to create the procedures that reference the Linked Server on the server that has no Linked Server set up. I understand why that happens, but I need a workaround that does not involve changing the procedure code (such as to use Dynamic SQL to hide the reference from the compiler).

I tried creating a Linked Server of the expected name that points to the local server, but SQL Server is smart enough to see that the referenced tables do not exist on that Linked Server. If I create one to a non-existent server, SQL times out during the creation of the procedure saying that it couldn't connect to the remote server.

Is there any way to create a dummy Linked Server such that SQL Server will not try to validate the table names on the Linked Server?


texts: Fixed length text file insert into SQL table

texts:
I am not a DBA on this one and I will not have access to BCP or anything like that.  So I'm wondering if there is still a way to do it in SQL keeping it very basic.

I have 100's of text files that contain 1000's or lines of data.  Each line has a fixed length data format.

For example:

001     abc     test file ***
row 1     Test Data is here!       001
footerId 123 Footer for 001

Then I have a file format of

recordId starts at 1 length 8
recordName starts at 9 length 8
headerDesc starts at 17 length 10
rownumber starts starts at 27 length 3
rowNumber starts at 1 length 10
rowData starts at 11 length 25
rowId starts at 36 length 3
footerId starts at 1 length 12
footerref starts at 13 length 14

I hope that makes sense...  Obviously that is not real data.  Basically I need to load these files into a table (or many tables).  What would be the most basic way to do this in SQL?

I was playing around with the OPENROWSET command but that jammed everything into 1 row.  If there was a way to insert a temp table with each line of data I could maybe parse that out into more permanent tables.    But it would be great if there was a way to insert the data into an auto generated table.

Is there anyway to do this?


texts: Avoiding a divide by zero error in a computed column

texts:
How do you avoid divide by zero error in the below table?

CREATE TABLE [dbo].[TblDivision]
(
    [Numerator] int NOT NULL,
    [Denominator] int NOT NULL,
    [Result] AS (Numerator/ Denominator)
)
GO
Insert into (Numerator, Denominator) TblDivision values (3,0)
GO


texts: Is there a way to setup your Oracle db to always be brought up in an idle state so that you can then 'startup' when you want to use it. I'm running Oracle 11g R2 on Windows with lean resources.


texts: Mysql high safety implementation (replication or separate writes or something...)

texts:
I want to implement highly safe system storage with mysql.

My case is:

I need to store records.
For more safety i want to have N separate physical mysql nodes.
Then client save record and get OK response at this moment I want to be sure that data durable written in all N nodes.

Can mysql cover this?
I am not familiar with mysql replication can it cover this?

  Then client save key value and get OK response - at this moment I want to be sure that data durable written in all N nodes.

So I am afraid that then client get "ok" on saving row in mysql the data can be  written in one node (other replicas not sync) and if at this moment crash happens in only one node that holds client row
i can't restore it (replicas not in sync).

So i want be sure that all nodes written then client get OK.

Should i use mysql replication or I must implement this on applicaion level by do N separate INSERTS to  N servers or something other?


texts: Change postgresql data directory location

texts:
Currenly my postgresql service running with

/usr/local/psql/bin/postmaster -d /usr/local/pgsql/data

Due to insufficient disk space (data directory having 10GB of data), I planned to do the following steps:

Step1:

service postgresql stop

Step2:

mkdir -p /newdisk/newdata
chown postgres:postgres /newdisk/newdata

Step 3:

Change PGDATA path in /etc/rc.d/init.d/postgresql file

From

PGDATA=/usr/local/pgsql/data

To

PGDATA=/newdisk/newdata

Step 4:

initdb -D /newdisk/newdata
service postgresql start

If I do above steps, expecting 10GB data will be moved to /newdisk/newdata path and henceforth, new transactions will be stored in /newdisk/newdata path.

This approach is fine to proceed? or do I need to consider anything else?

(Using Postgresql 9.1)


texts: Change mysql storage engine from Innodb to MyIsam: Max locks exceeded error

texts: I have about 5 million records in a table which uses Innodb as storage engine. I have about 6000 users who use (do transactions) the same table. It sometimes becomes very slow or hangs. I decided to manually change to MyIsam. When I try to copy to another table and change the storage engine in phpmyadmin, it gives me Max Locks Exceeded Error. What should I do?


texts: Except from restart,when Innodb_buffer_pool_reads , Innodb_buffer_pool_read_requests parameters reset to zero?


texts: How to backup SQL Server 2014 database with SC Data Protection Manager using SQL script

texts:
I'm trying to set up the database maintenance solution for SQL Server 2014 using this SQL script and System Center Data Protection Manager 2012 r2 as a BackupSoftware (like SQLSAFE, SQLBACKUP and SQLSAFE)

Does anyone now which DPM backup procedures are needed to be called directly from SQL Server Management Studio to backup a database?
Is there any other way how to backup sql server 2014 databases using DPM? Cmdlets?

Thank you for any help!


texts:
I have a SQL Server 2014 DB where the log file filled up the disk. Since it was a test db, we decided to just delete the whole database. We took it offline, but when we try to drop it, we get a message that it cannot be dropped since it is in use.

How can it be in use when it is offline?
How can I get it dropped? (Preferable without taking it online.)


texts: How to transfer database from SQL Server to DB2?

texts: I have a database file on disk called project.mdf. I want to import this into IBM DB2. How do I import this database?


texts:
I have a database DBv1 on a SQL Server, and I want to migrate the data in it to DBv2 (on the same box, save server). DBv2 has very similar schema. The only difference in schema is that DBv2 has one less table, because it is de-normalized.

What would be the most practical way to migrate the data from DBv1 to DBv2 given that change in schema, and the need for some transformation?

Is there a tool that would do that for me? Or do I have to script that manually?

For clarification: this is a one-time load only since it is just a migration. By schema here I do not refer to the SQL object with the same name. Rather, I am referring to the structure of the tables. Due to de-normalization, one table is removed, and a new column is added to the parent table that would have the data in the deleted table.


texts: Amazon RDS Promote Db Read Replica to RDS Db Instance

texts:
When I promote a read replica to a db instance, does it replace the main db instance from which it was a read replica or does it create an entirely new Db Instance. I do not want to replace the Db Instance that the read replica is part of. I want to promote the read replica to a new Db Instance for testing.
thanks for replies and other suggestions


texts: Prevent UPDATE TRIGGER to fire when no changes made in SYBASE

texts:
In Sybase I have a main table test and an audit table test_a. The audit table is updated by triggers on insert, update and delete. Now the issue is, when I use update query with old data (no changes), the trigger still gets fired and logs in audit table, which I don't want (to prevent duplication when no changes).

test has

Id | NAME | DESC

test_a has

updated_by | date | Id | NAME | DESC

This is the update trigger I have:

create trigger test_utrig on test for update as
insert into audit..test_a select 'update',update_by(),getdate() inserted.* from inserted

I tried to use if (update(Id) or update(NAME) or update(DESC)), but still the update trigger get fired on no changes. Please help me how to stop trigger from firing when no changes are done.


texts: Cannot execute stored proc linked to C routine in DB2

texts:
DB2 version: 9.7.0.6
OS: RHEL 5

Here are the steps I took to create and bind a C routine to DB2:

1> Created myprog.sqC file
2> Executed db2 prep myprog.sql bindfile
3> Executed db2 bind myprog.bnd
4> Compiled the C code by executing

gcc -I /.../sqllib/include -c -fPIC myprog.C

5> Created a shared library i.e. myprog.dll by executing

 gcc -shared -o myprog.dll myprog.o -L /.../sqllib/lib64 -ldb2

6> Copied over the dll file created from Step 5 to /.../sqllib/function/. Set the permission 755 on the myprog.dll file

7> Created stored proc as follows:

create procedure myschema.test_func (IN INT_VAL1 INTEGER, OUT INT_VAL2 INTEGER) SPECIFIC test_func1 DYNAMIC RESULT SETS 0 NO SQL NOT DETERMINISTIC LANGUAGE C FENCED THREADSAFE PARAMETER STYLE SQL EXTERNAL NAME 'myprog!myfunc'

8> However, when I run the db2 command line : call myschema.test_func(10, ?), I get the error:

SQL0444N  Routine "myschema.test_func" (specific name "test_func") is
implemented with code in library or path ".../sqllib/function/myprog",
function "myfunc" which cannot be accessed.  Reason code: "4".
SQLSTATE=42724

I have been looking around for quite some time and frankly, I have given up.

Can anyone please help me out here to debug/understand the reason behind error in Step 8?


texts: How do you extend the tablespace when using innodb_file_per_table?

texts:
With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc.

With the option on, how do you control growth of the files? Do they autoextend? And can you set a maximum and then extend the tablespace for a given table onto another filesystem if necessary?


texts: How to convert unsigned int accidentally stored as signed int?

texts:
During a data conversion process, a field that was apparently an unsigned int was stored in a Sql Server int field(which is signed).

To remedy this apparently requires some combination of bitwise operators and type conversion that I'm struggling with.

For example the value 141 is stored as

  -29440 or 0x000000000000000000000000000000000000000000000000000000008D00

and the value 362 is stored as

  27136 or 0x000000000000000000000000000000000000000000000000000000006A00

Presumably I need to convert to binary first to thwart any implied casting.


texts: SQL Server error importing an Excel sheet

texts:
I am facing a problem while loading & importing an Excel sheet into a SQL Server database.

The error is

  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server
  "(null)" reported an error. The provider did not give any information
  about the error.

  Msg 7303, Level 16, State 1, Server PREM4, Line 3
  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLE .12.0" for linked server "(null)"

I could not find out what the issue is.


texts: What flavor of SQL is used inside Microsoft Access?

texts:
I don't have much Access experience, and I took on a new Access project. I can use SQL to query the Access databases.

What SQL specification/flavor is used inside Access? Is it the same as SQL Server?


texts:
I have a user who was added with windows authentication to a SQL Server 2008 R2 machine.

He had left the company a few months ago, at which point his AD user was disabled and moved to a different OU.
The users database logins and users were not affected.
He then returned to the company, his AD user was reactivated, and moved to the correct OU.

However all his database logins are currently giving login failures.
The issue is resolved if the following actions are taken:

Drop the existing login
Create the login again
Remap the login to the existing database users

This is an impractical method, as it means tracking down each login and recreating it.

How can I resolve this in a more practical way?
Failing a more practical solution, what is the cause for this issue?


texts: Oracle SQL Complications With Multiple Triggers

texts:
Hey all so I am creating a DB that is used going to be used for managing utilities of a system. My DB has 3 tables: Users, Utilites, and Files (leaving Files table out because question shouldn't affect it).

TABLES

CREATE TABLE USERS (
User_id Number(38,0) NOT NULL PRIMARY KEY,
User_name char(18) NULL ,
User_pass varchar(18) NULL ,
Storage_Size varchar(18) NULL ,
Memory_Usage Number(38,0) NULL
);

CREATE TABLE UTILITIES (
Utility_id Number(38,0) NOT NULL PRIMARY KEY,
Utility_Name varchar(18) NULL ,
Cost Number(38,0) NULL ,
Running char(4) NULL ,
User_id Number(38,0) NULL ,
);

TRIGGERS

//Trigger to SUM the cost of all UTILITIES:

CREATE OR REPLACE TRIGGER memory_after_insert
AFTER INSERT OR UPDATE OR DELETE
    ON UTILITIES
BEGIN
   UPDATE USERS
   SET MEMORY_USAGE = (SELECT SUM(COST) FROM UTILITIES WHERE USERS.USER_ID = UTILITIES.USER_ID);
END;

//Trigger to SUM the cost of all FILES

CREATE OR REPLACE TRIGGER storage_after_insert
AFTER INSERT OR UPDATE OR DELETE
    ON FILES
BEGIN
  UPDATE USERS
  SET STORAGE_USAGE = (SELECT SUM(FILE_SIZE) FROM FILES WHERE USERS.USER_ID = FILES.USER_ID);
END;

Now I would like to create a Trigger that on INSERT into USERS (User creation) will INSERT into UTILITIES all of the systems Utilities (I have X default utilities I would like every user to have)

Example Trigger I have come up with:

CREATE OR REPLACE TRIGGER users_after_insert
  AFTER INSERT ON USERS
  FOR EACH ROW
BEGIN
  INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  VALUES
  ('Javaw.exe', 'YES', :new.USER_ID, seq_utility_id.nextval, round(dbms_random.value(25000, 100000)));
  INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  VALUES
  ('Firefox.exe', 'YES', :new.USER_ID, seq_utility_id.nextval, round(dbms_random.value(60000, 200000)));
END;

When I have this trigger in my DB it causes my other triggers to mess up. I get errors: ORA-04091: table STUDENT052.USERS is mutating, trigger/function may not see it
ORA-06512: at "STUDENT052.MEMORY_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'STUDENT052.MEMORY_AFTER_INSERT'
ORA-06512: at "STUDENT052.USERS_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'STUDENT052.USERS_AFTER_INSERT'

Before adding this third trigger, the other 2 triggers were working perfectly. I assume the problem lies within this trigger. If anyone has any knowledge on how to fix this it would be greatly appreciated! Thanks!


texts:
I had a laptop die HDD went dead which a running sql database was opened (in program)
I had to have the hard drive recovered however all i got back was a bad database which has the torn page.
All pro software seams to be able to read my database and fix if i pay the 399.00 for the software however i was hoping i could use the built in DBCC However it seams everything i try wont work so i am hoping for some help.
Here is the error when I try to bring the database online

  Msg 945, Level 14, State 2, Line 1 Database 'CompassDefaultDB' cannot
  be opened due to inaccessible files or insufficient memory or disk
  space.  See the SQL Server errorlog for details. Msg 5069, Level 16,
  State 1, Line 1 ALTER DATABASE statement failed. Msg 824, Level 24,
  State 2, Line 1 SQL Server detected a logical consistency-based I/O
  error: incorrect checksum (expected: 0x20203e65; actual: 0x1f329010).
  It occurred during a read of page (1:9) in database ID 5 at offset
  0x00000000012000 in file 'C:\Program Files\Microsoft SQL
  Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

So Then I Try

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

That give me

  Msg 945, Level 14, State 2, Line 1 Database 'CompassDefaultDB' cannot
  be opened due to inaccessible files or insufficient memory or disk
  space.  See the SQL Server errorlog for details.

So Then I Try

ALTER DATABASE CompassDefaultDB SET EMERGENCY;

  Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical
  consistency-based I/O error: incorrect checksum (expected: 0x20203e65;
  actual: 0x1f329010). It occurred during a read of page (1:9) in
  database ID 5 at offset 0x00000000012000 in file 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

Then

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

  Msg 922, Level 14, State 1, Line 1 Database 'CompassDefaultDB' is
  being recovered. Waiting until recovery is finished.

It stays in this mode forever and does not seam to finish
this database is only 2MB with like 17 Rows of data i am after.
I can restart the sql server and repeat the same steps with the same results.
Is there something i am doing wrong?
any help is greatly appricated

I do have the origional corrupted database and have tried to restore and repeat but with the same results
I have also tried to bring up a database with the same name delete the mdf file and replace with me mdf file with the same basic results.
There is no backup :(
Thanks again for looking


texts: Coffee shop static tag/attribute querying (satisfying AND & OR condition groups) - Relational DB/DBMS or NoSQL?

texts:
Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has the attribute or it doesn't). So for example, you could have one cafe which has the following tags/attributes: (vegan-choices, high-price, open-late) and another cafe with (mid-price, open-late, serves-food) and a final with (low-price, serves-food).

How do I structure my data and queries to be able to e.g., do complex and grouped AND/OR statements such as (pseudo code) tags CONTAIN ('mid-price' OR 'high-price') AND 'serves-food'?

Can this be achieved using a relational database, or should I be looking at using NoSQL e.g., sub-documents in mongo? I want a solution which scales well, to handle potentially 10,000s cafes and even greater numbers of tags.

In MySQL I'd be tempted to have 3 tables – one for coffee_shops, one for tags and a 3rd 'mapping' table having a many-to-many relationship with the other 2 tables called coffee_shop_tags, however I'm not sure how to structure the queries to do this efficiently without doing a 'dumb' select against all referenced tags, then refining the list of coffee shops in application code.

What would be your recommended approach?


texts: When should we use table partitioning in Postgresql?

texts:
Context: we have two fairly large tables in our database, one holding 80 millions records and the other 160 millions.
We're seeing performances issues and are thinking about using table partitioning for these 2 tables.
My question is: is there a number of records that indicates that we should partition or not to keep good performances ?
I know there isn't a "one size fits all" answer, but there might be a general advice such as "passed X millions records, you should partition the table".
There is a lot of guidance regarding how to partition, but not "when".


texts: Error 8/40 PLS-00364: loop index variable 'I' use is invalid

texts:
When I execute this stored procedure:
create or replace procedure "delete-archive"
is
begin
for i in (select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
    where FILENAME like 'JDE%')
    loop
    UTL_FILE.FREMOVE ('DATA_PUMP_DIR', i.filename );
    end loop;
end;

I get this error:

8/40      PLS-00364: loop index variable 'I' use is invalid

The owner has execute permission. It was like that GRANT DBA TO pqp;. Do you think that the problem may be permissions?