texts: Is there a way to analyze a MySQL query and determine whether it's read-only?

texts: I have a main database and a replica that is automatically kept in sync with the main one. It would be nice, for each query I make, to perform all data- and database-altering queries on the main database, and all read-only queries on the replica. I'm envisioning some kind of query analyzer, and then based on its output, using the correct database, but maybe there's a different way to do that that I'm not aware of.


texts:
I want to open a 300 MB database downloaded to my computer. The database is called database-name.mysql.gz.

When I open the file called database-name.mysql.gz I get a file called database-name.mysql.gz.cpgz.

I have tried decompressing the GZ file using command line:

tar -xzf <your file>

not unzip unlike in the article since you're not using a .zip file which gave me an error: tar:

  Unrecognized archive format.

I have also tried

gunzip < database-name.mysql.gz | mysql -u USERNAME -p

but I don't really understand what USERNAME should be and the command returns

  gunzip: unknown compression format

How can I open the database and see its content?


texts: Restore SQL Server database using Windows Powershell 3.0

texts:
I'm trying to restore a SQL Server database with a PowerShell script, but I'm having problems.

Here is the error I'm getting:

  Exception calling "SqlRestore" with "1" argument(s): "Restore failed
  for Server 'WUSFK250042-OLU\SQLSERVER2008R2'. " At line:48 char:1
  + $smoRestore.SqlRestore($server)

Here is my code:

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$backupFile = "C:\SafewayRT\SafewayRTFUll.bak"

#we will query the database name from the backup header later
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "WUSFK250042-OLU\SQLSERVER2008R2"
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#restore settings
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestorePercentCompleteNotification = 10;
$smoRestore.Devices.Add($backupDevice)

#get database name from backup file
$smoRestoreDetails = $smoRestore.ReadFileList($server)

#display database name
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#give a new database name
$smoRestore.Database =$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#specify new data and log files (mdf and ldf)
$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#the logical file names should be the logical filename stored in the backup media
$smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"]
$smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "_Data.mdf"
$smoRestoreLog.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"] + "_Log"
$smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $smoRestore.Database + "_Log.ldf"
$smoRestore.RelocateFiles.Add($smoRestoreFile)
$smoRestore.RelocateFiles.Add($smoRestoreLog)

#restore database
$smoRestore.SqlRestore($server)


texts: Changing identity column from INT to BIGINT

texts:
I have a table with an identity column that is also a primary key. Currently, it has 50 million rows, with the highest value of the identity column sitting at 148,921,803. The table has a lot of DELETEs and INSERTS performed on it, hence the high value.

We want to change the data type from INT to BIGINT to prepare for the addition of more rows. Note that, there are no references to the PK column.

What is the best way to do this, with minimal downtime? I have two options.

Drop the PK and alter the column; or
The copy-drop-rename method, as described here:


texts: SQL Server 2012 Page Compression - Determine compression ratio?

texts: We're using SQL Server 2012 and have page compression turned on for a number of our larger tables. Is there a way we can determine the compression ratio we're getting? We'd like to be able to determine the benefits of compression.


texts: Postgres: Put 1 database in separate HD partition?

texts:
I have 2 HD partitions - one with SSD and another with regular hard drives.

I have multiple databases (as in logical database, not physical) with 1 data directory. I want the data for 1 of the databases to reside in the SSD partition because it's a database that will have LOT more reads/writes than the other databases. And since the SSD partition is limited in space, I can't store all the databases there, just that one.

Is there a way to configure this in Postgres - is it possible? Is there a tutorial online that I can read? I tried Googling but all the results talk about partitioning tables logically, not across different hard drive partitions.


texts: fill table A with random ID's from another table B

texts:
I have two tables and I want update the column GebruikerID from table Topic with the ID of the table Gebruiker with random values.

You must also know the follow things:

Topic.GebruikerID references to Gebruiker.ID.
Not every ID is used in both tables.
The random number that must be generated exist in the table Gebruiker (column ID) and must be updated into the table Topic (column GebruikerID).
I use SQL server in an express edition.

Here are some images.

The relation between the tables

The ID's of the Gebruiker-table


texts:
A friend of mine has run into this problem on SQL Server 2008, please suggest a solution:

He came to know that auto backup scheduled on server were not taking place since last couple of days, when he tried manual backup of our database it gave following error:

  System.Data.SqlClient.SQLError: Read "D:\DB\data.mdf" failed: 1 (Incorrect function.) (Microsft.SqlServer.Smo)

To safeguard our data he thought of rebooting the server in safe mode & copy the .mdf & .ldf files manually to a safer location but it gave an error after completing 80% of process (MS DoS invalid ...) which looked as if hard disk has some issue & it's not able to read the files from specific location.

He thought of copying the data table by table after creating a fresh database from DB script, during the process one of our developer by mistake dropped two important tables & he has not used BEGIN TRANSACTION option also

Now he is looking forward for an expert advise if we have some recovery tool to recover those tables & data in those tables.


texts:
How can I get nth bit value in oracle database?
I am currently using this query:

WITH a AS
     (SELECT 7 val
        FROM DUAL)
SELECT CASE BITAND (a.val, POWER (2, n-1))
          WHEN 0
             THEN 0
          ELSE 1
       END nth_bit
  FROM a


texts: In a trigger, does assigning a column to the "old" value incur a cost? (Oracle)

texts:
For the sake of readability, in one of my triggers I have :new.COLUMNNAME := old.COLUMNNAME; alongside the sets for the rest of the columns. Does this statement get removed before runtime? (That column is not assigned anywhere earlier in the trigger)

We're doing this because depending on the value of typecolumn, other columns will or will not have values, and those values are found on another table... It's not exactly good sql using all the features as they were intended to be used, or good db design, but this is an old codebase and we have to play the hand we're dealt.

I want to set all the columns, even if the value doesn't change, for the sake of clarity and readability so that people know that we didn't just forget a column. The lead dev is hesitant to do that because he's worried about the performance impact of extra lines in a trigger (which will get called many times), but if I can prove that the lines have no effect then he'll be placated.

Here's a little more (simplified) context to hopefully illustrate what I mean.

create or replace trigger SCHEMA.TRIGGERNAME
before insert on SCHEMA.TABLENAME1
for each row
declare mno1 number
begin
  select min(mno2), min(data22), min(data32)
    into mno1, data2, data3
    from (select mno as mno2, data2 as data22, data3 as data32
      from SCHEMA.TABLENAME2 t2
        inner join SCHEMA.TABLENAME3 t3
          on t2.somekey = t3.somekey);

  case :new.typecolumn
    when 1 then
      :new.mno := mno1;
      :new.data2 := :old.data2;
      :new.data3 := :old.data3;
    when 2 then
      :new.mno := :old.mno;
      :new.data2 := data2;
      :new.data3 := data3;
    end case;
end;


texts:
I have a Postgres loop as follows, which I am running from the command line via psql:
DO
$do$
BEGIN
   FOR i IN 1..20000
   -- update some rows
   LOOP
END LOOP;
END;
$do$;

Is there any way to access the value of the counter i to track the progress of the loop? I've looked in the pgAdmin dashboard with no success.


texts: Single Redshift table vs several mysql/ pgsql (on RDS) tables for small (200 tables * 20 columns * 1 million) monthly aggregation data

texts:
We're providing SaaS Data Analytics Dashboard for around 200 clients.
We're using Amazon Redshift for storing data in in around 5 tables.
We're getting around  30 million hits per day.

Currently our dashboard is based on raw tables; so its very slow.

We're planning to create aggregation tables on top of Redshift tables.
And we're confused between using Single Amazon Redshift table vs several small mysql/ postgresql on (Amazon RDS) tables for this small (200 tables * 20 columns * 1 million) monthly aggregation data.

Points we're considering while comparison.

Performance for readonly data.
Cost effectiveness.
Latency difference and its effects on performance.
Maintainability.


texts: How to format a phone number in a SELECT statement?

texts:
I have a database with table name student.

I would like to display the register_number and phone_number of the student.

The phone_number should be in the following format:

+91-123-456-7890

and if the phone_number is NULL, then it should display N/A.

The table looks like:

R_NO      |     STUDENT_NAME    |  PHONE_NUMBER
-------------------------------------------------------
  1       |      Rajesh         |  9632545123
  2       |      Sridevi        |  9512647359
  3       |      Shiva          |  9632155862
  4       |      HariHaran      |  8426911231
  5       |      Ravi           |  9111558899
  6       |      Pauline        |  NULL


texts: How can I instantly update a table in a database with the columns values from another database?

texts:
I have database_1 and database_2 both on the same server.

How can I instantly update the USER table in database_2 when data is inserted into the USER table in database_1?

Structure of both tables:

CREATE TABLE USER
(
    id int
    , email varchar(256)
    , `name` varchar(1000)
    , `address` varchar(1000)
);

Please help me with all steps to do this.


texts: Is it possible to run VACUUM FULL in Postgres asynchronously?

texts: I am working on some unused data purging scripts on Postgres 9.1.  VACUUM FULL is necessary to reclaim physical space after purging.  However, with my data volume (5M+ rows), it takes several minutes to execute it.  Since the vacuum needs to be incorporated in the script, I was wondering if it is possible to kick it off asynchronously (or schedule it) so that my script can just finish without waiting?


texts:
Considering my basic knowledge of PostgreSQL I am unable to find out why my second statement for where clause (record->'details'->>'amount')::numeric(19,2) = 2.99 is not working with current query. Idea is to send common filter from UI for header and details and when I am trying to filter to inner array details Json column its not giving me a exact result though it has some data within it

WITH t as (select row_to_json(rr)::jsonb as record
from (
       select (
                select row_to_json(row)
                from (
                       select
                             c.customer_id,
                             c.first_name,
                             c.last_name,
                             c.email,
                             count(*) total,
                             sum(p2.amount) total_amount
                       from payment p2
                       where p2.customer_id = c.customer_id
                     ) row
              ) as header,
              (select array_to_json(array_agg(row_to_json(row)))
                from (
                       select
                             amount,
                             payment_date
                       from payment p1
                       where p1.customer_id = c.customer_id
                     ) row
              ) as details
       from customer c
       join payment p on p.customer_id = c.customer_id
     ) rr)

    SELECT record FROM t
    where record->'header'->>'customer_id'= '2'
    and (record->'details'->>'amount')::numeric(19,2)= 2.99


texts: Composite multicolumn index for geopoint range and numeric range query

texts:
I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want's to see posts and other filters such date range and filter for a value of another column. This is going to be for an ad-hoc event discovery app.
I have read about PostGIS, its geometry,geography types and I know there is a native point datatype. Based on this answer I understood that it is better to order from equality to range columns, even though I feel like geo point column should be the first.
Suppose the following few rows of a simplified events table (disregard the validity position data):
id  event_title                  event_position   event_type  is_public  start_date
    (varchar)                    (point lat/lon)  (smallint)  (boolean)  (timestamptz)
--  ---------------------------  ---------------  ---------   ---------  ----
 1  "John's Party"               (122,35)         0           0          2020-07-05
 2  "Revolution then Starbucks"  (123,30)         1           1          2020-07-06
 3  "Study for math exam"        (120,36)         2           1          2020-07-07
 4  "Party after exam"           (120,36)         1           1          2020-07-08
 5  "Hiking next to the city"    (95,40)          3           1          2020-07-09
 6  "Football match"             (-42,31)         4           1          2020-07-10

Imagine the table contains several thousand records at least, obviously not only 6.
So in this table a user would be able to query public events close to (122,34) by 100km (suppose first three rows fall into this area) and of event types 0, 1 or 2 falling between dates 2020-07-05 and 2020-07-07. The user would get the rows with ID 2 and 3.
This is the query I want to optimize with an appropriate index. My question is, how is it possible to create such an index? I thought about GiST or GIN index but not sure how these could help. Thanks!


texts: Impact of "_oracle_script"=true in Oracle database 18c

texts:
I just installed Oracle XE 18c (I migrated from 11g XE) ... After running the installer, I saw new features; for example, Oracle now supports a multitenat model and a concept called pluggable databases ...

The first problem that I found was creating a new user ... it appears that in order to support the concept of pluggable databases, user names must comply with some naming rule prefixes (i.e. c##) ... Searching about the problem, I found this link  ... They suggest that to create a user using the old syntax (the one used on non-CDB), we should alter the session using:

alter session set "_ORACLE_SCRIPT"=true;

But, they warn that the user cannot be used in any pluggable database ...

So, this is my question: If a create the user after submitting the command, is the user created in the ROOT Container (CDB$ROOT)? Such user will be local to the Oracle instance and can be used to administrate all other pluggable databases, including the CDB (assuming that such user has all required permissions)?

Thanks!

Disclaimer: I'm not a DBA, I'm just a software developer with advance knowledge about databases ...


texts: Postgresql PG_Dump on Master vs Slave are different sizes

texts:
I have a backup running on both my master and slave servers using pg_dump, piped into "gzip -c" command. The result file sizes are quite different. My slave backup is 1/3 size of the master backup of the same database.

Replication seems to be running OK. I ran a query of table list with rowcounts and the two databases seem to be the same.

As far as I understand a pg_dump is a text file with copy commands and schema defines. They should be the same size. Am I correct?

PostgreSQL 9.5 on Ubuntu 16.04

The backup file is too big to eyeball


texts: InnoDB issue after power outage a few weeks ago - no backup

texts:
A few weeks ago we had a power outage on one of our web servers that hosts several websites, we didn't think it affected anything and everything came back up as normal as far as we were aware. I have been analysing the logs over the past few days to try and optimise as I noticed increased load recently - after exploring the mysql .err file I can see continual loading error messages such as the following -

  Cannot find or open table example_example/table1 from
  the internal data dictionary of InnoDB though the .frm file for the
  table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files
  of InnoDB tables, or you have moved .frm files to another database?
  or, the table contains indexes that this version of the engine
  doesn't support.
  See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
  how you can resolve the problem.

We have tried the suggested link on MySQL but doesn't seem to be working.

We do not have backup of the databases from a couple of weeks ago, so not quite sure how to diagnose or resolve this but have come to a brick wall.

Any advice would be greatly appreciated.


texts: How to avoid a race condition with cross db proc calls? and other concerns

texts:
I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a way dumbed down example, and the syntax may not be correct as I am rushing and way more interested in the answer to the underlying issue then the code to do it...

CREATE TABLE dbo.CodeNumbers(
CodeNumbersID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CodeValue VARCHAR(30) NOT NULL
IsUsed BIT NOT NULL DEFAULT(0)
);

dbo.CodeNumbers gets populated with a monthly CSV provided, the import method of your choice is already written to get them in there. We are NEVER given a duplicate code.

Lets assume for arguments sake we have 10,000,000 rows in the table. that all follow this format when imported:

1, 'ajdirjfisofklrlfo039402', 0 all the way till
10000000, 'fkeiir9489', 0

Now in dbBar I have 2 stored procedures the first should access the first un-used code in dbFoo, return it in an out variable and mark it as used. So I have something like:

CREATE PROCEDURE GetNextUseableCode
   @CodeOut VARCHAR(30) OUTPUT,
   @CID INT OUTPUT
AS
   SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
   FROM dbFoo.dbo.CodeNumbers
   WHERE IsUsed = 0

   UPDATE dbFoo.dbo.CodeNumbers
   SET IsUsed = 1
   WHERE CodeNumbersID = @CID

The code that calls the procedure from dbBar is accessed by 200k sessions a day at various times. When dbFoo.Codes has no more to return, its fine all is well and good, the application is simply told sorry no more check back tomorrow.

I have 3 main questions..

Is there anything special I would need to have in the code to avoid race conditions, and if so what would be best to handle for this without bringing the system to its knees.
Is their an efficient way to ensure the next code grabbed whenever the procedure is called, is the next one in chronological order within the ID column.
Is there any other concerns that I am not looking at right now that could issue big problems, and what would be an eloquent way to handle this situation?

I understand this is a long pretty open ended question, I have some coded up solutions, but I feel like there are much better ways to get the results I want.

Thanks in advance as always for all help.


texts: commitlog broken after standby setup with pg_basebackup

texts:
I'm trying to replicate a database with pg_basebackup in order to use it as standby server. But when I'm trying to start up PostgreSQL I get the following errors and the server shuts down again:

2016-08-09 15:53:38 CEST [10369]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:39 CEST [10372]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:39 CEST [10375]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:40 CEST [10378]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:40 CEST [10361]: [4-1] [: ] LOG:  redo starts at 11/188CBA38
2016-08-09 15:53:40 CEST [10361]: [5-1] [: ] FATAL:  could not access status of transaction 4809883
2016-08-09 15:53:40 CEST [10361]: [6-1] [: ] DETAIL:  Could not read from file "pg_clog/0004" at offset 147456: Success.
2016-08-09 15:53:40 CEST [10361]: [7-1] [: ] CONTEXT:  xlog redo Transaction/COMMIT: 2016-08-09 15:41:25.947996+02
2016-08-09 15:53:40 CEST [10359]: [3-1] [: ] LOG:  startup process (PID 10361) exited with exit code 1

I call pg_basebackup like this

sudo -u postgres pg_basebackup -h [server] -D [dbdir] -U replicator -v -P

My recovery.conf Looks like this

standby_mode = 'on'
primary_conninfo = 'host=[host] port=[port] user=replicator password=[password] sslmode=require'
trigger_file = '/tmp/postgresql.trigger'

When I'm trying to follow this solution I'm getting the error message

ERROR:  requested WAL segment 0000000100000010000000EF has already been removed

how can I avoid the broken clog file in the first place?

i'm using PostgreSQL 9.5.3


texts: SQLServer 2016 continually increasing stolen memory

texts:
We are experiencing a slow growth of Stolen Memory on our database servers over the space of several days. It appears to plateau around 130-140GB, at which point we start having larger problems such as out-of-memory errors, multi-second freezes & AG failovers. Problems start to manifest about a week after a reboot. I have started recording history of stolen memory, which is shown below:

Looking at sys.dm_os_memory_clerks, it appears as if the bulk of this is coming from non-page memory recorded against the buffer pool on NUMA node 0:

Tracking the total pages_kb for the buffer pool over time shows the decline in number of pages as virtual_memory_committed_kb grows. (On April 13th, the server was rebooted for windows updates. The buffer pool fills to 400GB in around an hour)

Has anyone seen this behaviour before?

We are running SQLServer 2016 CU12 13.0.5698.0 The server is a 64-core AWS EC2 i3.16xlarge instance. We have a number of other clusters of the same size that are all showing this issue. We also have a few clusters on 32-core i3.8xlarge instances that also show the growth in stolen memory, but they don't end up stalling / throwing out-of-memory errors. The only difference (other than scale) is that the 64-core servers have 2 NUMA nodes.

Update:
MS indicated that the bugfix in KB4536005 is not being back-ported to SQL2016.


texts: Many columns vs few tables - performance wise

texts:
Yes, I am aware that data normalization should be my priority (as it is).

I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth,  in total 65.
Now, I can divide that and have a Vehicle table, VehicleInterior, VehicleExterior, VehicleTechnical, VehicleExtra (all one-to-one with main Vehicle table).

Let's assume I'll have about 5 million rows (vehicles).
On SELECT with a WHERE clause: Will the performance be better searching through (both cases indexed at least on IDs):

Vehicle table with 65 columns or
Vehicle table with JOINS on four other tables (all with 5 million rows) to return all the data related to Vehicle?

(As per database engine, consider PostgreSQL and/or MySQL).
Truly appreciate any detailed insights you might have from your previous experience?
Updates will be rare if any, and selects will be mostly for all columns (vehicle details page) and main info (few columns) for search results list, and in fact maybe the best solution would be two tables: one with main info (few columns) and the other table with rest of the columns.


texts: Why doesn't INSERT AFTER Trigger add all results?

texts:
I'm working on the exercises for db-class.org where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger runs, we should have ('Friendly', A) in the Likes table for every other Highschooler A in the same grade as 'Friendly'.

This is being done in sqlite on the following dataset. Now my trigger works, but it only adds one person that 'Friendly' likes rather than all of them (those with the same grade).

Here's my trigger:

CREATE Trigger Q1
After Insert on Highschooler
For Each Row
When New.Name = 'Friendly'
Begin
    INSERT Into Likes Values (New.ID, (
    SELECT Highschooler.ID FROM Highschooler
    WHERE Highschooler.Grade = New.Grade));
End;

I suspect there's something wrong with my INSERT query, because in isolation the SELECT returns all the IDs. So how do I make sure it inserts all the IDs with matching grades?


texts: How to display query result in rockmongo by using execute method

texts:
I executed a command in mongo.exe

> db.tablebusiness.find({"_id": "the-simmons-paradise__41.85_-87.88"});

I got results:

Now I try similar command in rockmongo.
If I execute

db.tablebusiness.find(
    {"_id": "the-simmons-paradise__41.85_-87.88"}
    );

Result:

{
   "retval": null,
   "ok": 1
}

Basically it seems to tell me that the result is ok or something like that? I am not sure.

If I elaborate:

var cur = db.tablebusiness.find(
{"_id": "the-simmons-paradise__41.85_-87.88"}
);
cur.forEach(function(x){print(tojson(x))});

Result:

{
   "retval": null,
   "ok": 1
}

Same problem.

If I do:

function () {
   return db.tablebusiness.find({"_id": "the-simmons-paradise__41.85_-87.88"});
}

What does it mean by hello.tablebusiness -> undefined is beyond me. As you see from above, I successfully execute the query just fine in mongo.exe

Looks like rockmongo has very limited feature. I wonder how to actually see result. How to execute random mongodb command in rockmongo and observe the result.


texts:
I am new to MySQL Stored Procedure, in my first attempt I got the following issues,

Stored Procedure:

Delimiter $$

create procedure accountstatus

(

    IN  inmode              varchar(27),
    IN  AccountStatus_id    int,
    IN  AccountStatus       varchar(255),
    IN  CreatedOn           datetime,
    IN  CreatedBy           varchar(255),
    IN  UpdatedOn           datetime,
    IN  UpdatedBy           varchar(255),
    IN  is_active           bit)

    Begin

    if inmode   =   'insert'
    then
        insert into accountstatus
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
        values
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active);
    end if;

    /*update*/

    if inmode   =   'update'
    then
        update accountstatus acc
        set
        acc.AccountStatus_id    =   AccountStatus_id,
        acc.Account_Status      =   Account_Status,
        acc.CreatedOn           =   CreatedOn,
        acc.CreatedBy           =   CreatedBy,
        acc.UpdatedOn           =   UpdatedOn,
        acc.UpdatedBy           =   UpdatedBy,
        acc.is_active           =   is_active
        where
        acc.AccountStatus_id    =   AccountStatus_id;

    end if;

    /*delete*/

    if inmode   =   'delete'
    then
        update  accountstatus acc
        set
        acc.AccountStatus_id    =   AccountStatus_id,
        acc.is_active           =   0
        where
        acc.AccountStatus_id    =   AccountStatus_id;

    end if;

    /*select*/

    if inmode   =   'select'
    then
        select * from accountstatus acc
        where
        acc.AccountStatus_id    =   AccountStatus_id;
    end if;
end

When I execute these statements I got the following output:

STATEMENT

call accountstatus ('insert', 1, 'accepted', current_date(),'rathish', current_date(), 'raj', 1);

OUTPUT

call accountstatus ('insert', 8, 'accepted', current_date(),'rathish', current_date(), 'raj', 1)
1 row(s) affected

Similarly I inserted some values to this table, but when I tried to execute the following update statement, values in the Account_Status became null as shown below,

STATEMENT

call accountstatus ('update', 8, 'accepted', current_date(),'raj', current_date(), 'raj', 1);

OUTPUT

call accountstatus ('update', 8, 'accepted', current_date(),'raj', current_date(), 'raj', 1)
1 row(s) affected

But, when I execute the following query statement I got the unexpected output as follows,

What is error in this procedure?
Is there any other ways to implement this procedure?
How it will effect the performance of the database?

Thanks in advance.


texts: How concat two sql query in a single result

texts:
I have two sql query

First:

SELECT ticketing_ticket.email,
       CONCAT(
              TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
              MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
              MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
             ) AS 'temps de traitement '
FROM ticketing_ticket
WHERE DATE(date_close) = CURDATE();

Output:

   |  email       |       Time
   |test@test.com | 15 jours 23 heures 7 miinutes
   |test@test.com | 15 jours 22 heures 54 miinutes

Second:

SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := id_parent FROM ticketing_category WHERE id_category = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 38, @l := 0) vars,
        ticketing_category m
    WHERE @r <> 0) T1
JOIN ticketing_category T2
ON T1._id = T2.id_category
ORDER BY T1.lvl DESC

Output:

 |          Domaines           |
 |Elections;Aides et démarches |

How Can i combine my sql query  in a single result like this

|  email       |       Time                        | Domaines
|test@test.com |    15 jours 23 heures 7 miinutes  | Elections;Aides et démarches
|test@test.com |    15 jours 22 heures 54 miinutes | Elections;Aides et démarches


texts: Truncating and inserting in a safe transaction on MySQL

texts:
I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table.

I wanted to make sure that if any statement in the transaction failed, the data will be untouched.

I can't have an empty or corrupted table if something fails.

This is how I planned to do it.

BEGIN TRANSACTION
TRUNCATE TABLE table_name
INSERT INTO table_name
COMMIT

I then learned that TRUNCATE TABLE table_name causes an implicit COMMIT, which makes the whole transcation pointless.

I then replaced the TRUNCATE TABLE table_name with a DELETE FROM table_name approach, which wasn't ideal - but that too causes an implicit COMMIT!

DROP TABLE table_name and CREATE TABLE table_name also causes an implicit COMMIT!

What method / logic is best to perform what I am trying to do?

Using MySQL 5.6.17 64-bit server running on Windows Server 2008 R2


texts:
I need to check datecom is less than datelivr

create table Commande
(
Numcom int identity primary key,
Datecom date,
Datelivr date,
Codecli int foreign key references clients (Codecli)
)

alter table Commande
    add constraint date_check check(datediff(day,Datecom,Datelivr) > 0)

but I get the error message:

  The ALTER TABLE statement conflicted with the CHECK constraint
  "date_check". The conflict occurred in database "tp4", table
  "dbo.Commande".

How can this happen?


texts: How do I prevent updates on an entire table (but allow inserts) in Mysql 5.5?

texts:
I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them.  How can I do this?

This question handles making individual rows immutable, but I'm hoping there is a better way to make all rows in a table immutable (ideally returning a suitable error message if any change is attempted).


texts: Is there a way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix?

texts:
This is pretty trivial, but if someone has the answer handy, I'd appreciate it.

I use the msdb..sp_send_dbmail stored procedure as a lightweight way to distribute simple reports.  This often entails producing a CSV.  However, the stored procedure includes the Unicode little-endian prefix FF FE.  This confuses Excel, causing it to not automatically parse the CSV into columns.

There are a number of workarounds: use a real reporting platform (we have Crystal, but it's never worked reliably); send the file to a little app that converts the file to ANSI and sends it on; use tab delimiters (my favorite); or educate the recipients about how to work with CSVs (just kidding, we all know that's not possible).

However, I'd love to just flip a switch somewhere and tell SQL Server to stop creating the attachments as Unicode, as I have no such need. I haven't found such a setting yet; is this possible?

Thanks!

EDIT

Based on Rohan's answer, this script in a SQL Agent job step almost works:

$reportQuery = "EXEC Routine..TopTenReviewsReport @DoNotLog = 1"
$filePath = "\\SQLBI001\RawData\TopTenReviewsReport.csv"
$mailQuery = "
  EXEC msdb..sp_send_dbmail
  @profile_name = 'X',
  @recipients = 'X',
  @subject = 'X',
  @file_attachments = '" + $filePath + "'"

Invoke-Sqlcmd -query $reportQuery | export-csv $filePath
$result = Invoke-Sqlcmd -query $mailQuery

The file now has a "#TYPE System.Data.DataRow" prefix, instead of 0xFF FE, but it's now ANSI, so that's something.


texts: What's the complexity of renaming a column in MySql?

texts:
We want to rename a column in our MySql (version 5.5) database with something like this:

ALTER TABLE t1 CHANGE a b INTEGER;

Is this an O(N) (N=number of rows in the table) or an O(1) operation? I can't seem to find an answer in the manual nor on Google. The table has millions of records, and it wouldn't be feasible directly if it's O(N).


texts:
I used SELECT FILEPROPERTY('file_name', 'SpaceUsed'), and it returns NULL, what does it mean?

I am correctly using it in the correct database.

I cannot assume that NULL means empty since:

I tried to create a new database (test2), then query SELECT FILEPROPERTY('test2', 'SpaceUsed'). It already has a value.


texts: Stuck Oldest unsent transaction in db mirroring

texts:
I am seeing some very strange behavior in db mirroring

For one of the databases, during the weekend when rebuild index job ran on the principal, it generated around 60 GB log.

Now its been 2 days and mirroring is not in sync. ON mirror it always showing synchronizing  and seems to be DC in between.

When i monitor further i see oldest unsent transaction is always stuck at 2:10:07:20 and increasing with time while unrestored log starts with 60 GB goes to 0 and then starts showing again with 60 GB still pending.

Update@

I see couple of times every 5 min there is a Disconnected state logged in Mirroring_state column for all the databases now on the instance

Can someone please guide what can be an issue here and what to check?


texts: Microsoft Access database to FileMaker Pro

texts: I have all my customer details in Microsoft Access. I want to transfer all this data to FileMaker Pro. I hope there is a way to do this. If so please help me.


texts: I m a new in SQL. I have one question where if we update, i mean add or delete data at source database, is that the data will be update in database snapshot also. Please enlighten me.


texts: What is the significance of the principal_id in sys.schemas?

texts:
What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id?

1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas;
2> GO
                     schema_id   principal_id
-------------------- ----------- ------------
dbo                            1            1
guest                          2            2
INFORMATION_SCHEMA             3            3
sys                            4            4
db_owner                   16384        16384
db_accessadmin             16385        16385
db_securityadmin           16386        16386
db_ddladmin                16387        16387
db_backupoperator          16389        16389
db_datareader              16390        16390
db_datawriter              16391        16391
db_denydatareader          16392        16392
db_denydatawriter          16393        16393

(13 rows affected)

Internally, I see that the schema_id comes from sys.sysclsobjs while the principal_id comes from sys.syssingleobjrefs's r.indepid field.


texts:
I cannot seem to permanatly set innodb_lock_wait_timeout in the sql database I need to change this to 600.

Its for yetiforce crm system. I am running Ubuntu 16.06, with lamp.

Everything else is set bar this but I cannot see it anywhere in my my.cnf file or php.ini.

Even tried global set in the db itself but it wont change.

Any help would be great.


texts: Database insanely (and weirdly inconsistently) slow on 8.0

texts:
I recently tried moving a large DB to 8.0, and am seeing insanely bad speeds (100x slower in some cases). I've spent the last 2 days googling this and feel like I've tried everything and have not had any luck.
Some info: I spun up a new VM for the new instance. It has more ram, same SSD drives and should be generally faster than the old one, but I still have that running so I can compare the two.
I pulled one of the queries I found acting slow, and did some testing:
Table accounting has ~100k rows. Here's where things get a little odd. I have a production and dev database on the new server and I am getting drastically different speeds. I have copied this particular table recently, so I know the table, structure and keys are identical in all 3 places.  (Note cash and status are both ENUM)
SELECT * FROM `accounting` WHERE type='cash' AND status='pending'
Response on 5.7: (Query took 0.0427 seconds.)
Response on 8.0 dev db: (Query took 0.7911 seconds.)
Response on 8.0 prod db: (Query took 4.1671 seconds.)

The first digit on those response times are consistent every time. I've speed tested the underlying hard drive and it is testing faster than on the old server.
I've tried every config option that came up while googling and none of them help significantly.  Here is what I have.  I've tried larger and smaller versions of most of these options.  This server has nothing else happening on it, and this site isn't using it in production right now so I can experiment and restart as much as I need.
join_buffer_size=512K
sort_buffer_size = 4M
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances=5
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 16
innodb_write_io_threads = 64
innodb_log_file_size = 3G
innodb_log_buffer_size = 64M
innodb_lru_scan_depth=100
#optimizer_switch='derived_merge=off' #this significantly hurts performance
join_buffer_size=8M
tmp_table_size = 256M
max_heap_table_size=256M
table_open_cache=4000
performance_schema=OFF #roughly 25% speed increase
skip-log-bin
sync_binlog=0
innodb_flush_log_at_trx_commit=0

I'm really at a loss here. Any help would be appreciated.
Edit:  I tried adding a type+status index on prod-8.0 db, and the speeds were blazing.  When I removed the index, I am now getting speeds consistent with dev-8.0 db.  Not sure what this tells us.
Edit 2: Table I'm testing with is MyISAM.
Update (and solution):
I ended up installing mariadb, and the queries are blazingly fast.  I guess it's time to hop off the MySQL train.


texts:
I'm running into a performance problem with a query that I can't seem to get my head around.

I pulled the query out of a cursor definition.

This query takes seconds to execute

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

The actual execution plan looks like this.

Noticing the server wide setting was set to MaxDOP 1 I tried playing around with maxdop settings.

Adding OPTION (MAXDOP 0) to the query, or changing the server settings results in much better performance and this query plan.

However, the application in question (Dynamics AX) doesn't execute queries like this, it uses cursors.

The actual code captured is this.

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

resulting in this execution plan (and unfortunately the same multiple-second execution times).

I've tried several things such as dropping cached plans, adding options in the query inside the cursor definition, ... But none of them seem to get me a parallel plan.

I've also searched google for quite a bit looking for parallelism limitations of cursors but can't seem to find any limitations.

Am I missing something obvious here?

The actual SQL build is SQL Server 2008 (SP1) - 10.0.2573.0 (X64) which i realise is unsupported, but I cannot upgrade this instance as I see fit. I would need to transfer the database to another server and that would mean pulling a fairly large uncompressed backup over a slow WAN.

Trace flag 4199 doesn't make a difference, and neither does OPTION (RECOMPILE).

The cursor properties are:

API | Fast_Forward | Read Only | Global (0)


texts: Compare the database compatibility_level to the server version?

texts:
I have a mixed environment of servers from 2000 to 2012, and I'd like to find any databases that have a compatibility level set less than the version of the server it is running on.

Ideally, I'd like to do something like:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <> SERVERPROPERTY('productcompatibilitylevel')

but that unfortunately doesn't exist. Instead I'm having to do a big CASE statement based on SERVERPROPERTY('productversion'). Any better ways to do this?


texts: Problem with shortest distance calculation in PostGIS

texts:
While working with PostGIS pgrouting, for calculating the distance between two roads (lines) I'm using the shortest_path function.

The logic is based on start_point(Start_id) and end_point(end_id) but in my data the line string contains many internal points like (linestring(1 1,2 2,3 3,4 4,5 5) for example.)

So it is taking the start point as (1 1) and the endpoint as (5 5). If another line starts with (5 5) it shows as route like linestring(5 5,6 6).

But a line which has a crossing point inside the linestring like (2 2,3 3,4 4) is not considered connected.

For example, using this roads table:

id         name                way
1          A                   linestring(1 1,2 2,3 3,4 4,5 5)
2          B                   linestring(5 5,6 6)
3          c                   linestring(2 1,2 2,2 3)

If I apply the shortest_path function from point (1 1) to (6 6), it does determine a path. But for (1 1) to (2 3) it doesn't find anything. It does not find the (1 1,2 2,2 3) route.

What do I need to do so that the function considers each segments internal points rather than only the start and end points of the lines?


texts: MySQL cluster DataMemory and DiskPageBufferMemory parameters

texts:
I want to setup a MySQL cluster.

The amount of data is more than double the amount of RAM.
I have 3 Data Nodes each with 8GB RAM.

QUESTIONS

What is a good rule for the DataMemory and DiskPageBufferMemory parameters?
Are 3GB DiskPageBufferMemory and 2GB DataMemory good values?


texts:
I have a table named e_emp3 in Oracle with these fields among others:

FNAME VARCHAR2(20 BYTE)
SAL   NUMBER(8,2)
DEPTNO    NUMBER(4,0)

I need to alter e_emp3 and set unused column(sal) with online keyword to stay dml operations for users on this table. I wrote this code:

alter table e_emp3 set unused(sal) online;

but I got the following error message

Please help me what is problem? When I run this code without online keyword it succeeds. When I add online keyword it fails.


texts: How to reclaim disk space on a MongoDB Atlas replica set?

texts:
I migrated a replica set from mLab to MongoDB Atlas.

mLab offered an automated process to reclaim disk space after dropping large amounts of documents from a collection. Compacting (or resyncing) basically happened with the click of a button with a rolling node replacement process that does not require downtime.

I didn't find such a process in the MongoDB Atlas UI.

In the mongoDB docs the steps to "automatically sync a member" require deleting data manually from the shut-down secondary, and possibly other steps that seem quite delicate.

What is the easiest way to reclaim disk space on MongoDB Atlas for a replica set with 1 primary and 2 secondaries?


texts: CREATE TABLE in Postgres locks pg_class forever

texts:
I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class and the indexes on pg_class are locked, lock type is relation and mode is RowExclusiveLock.
I can add a column to a table but cannot create an index on a table.

If I create a table in another database, there are no locks on pg_class.

Every hint can help.

Here there is description of locks:

relname                             locktype    mode
pg_class_tblspc_relfilenode_index   relation    RowExclusiveLock
pg_class_relname_nsp_index          relation    RowExclusiveLock
pg_class_oid_index                  relation    RowExclusiveLock
pg_class                            relation    RowExclusiveLock


texts: SQL To Fetch Partition Boundaries and Filegroups

texts:
For a given partition scheme name, I need to retrieve the set of

Boundary Value
FileGroup Name

This must work without the existence of any tables participating in the scheme.

i.e. joins via sys.indexes will not work.

I think the answer lies around sys.destination_data_spaces, but have not yet figured the joins to sys.partition_range_values.


texts:
I went through at least 5 question on SE, but I must be doing something very bad out of frustration already.

I have a stored procedure that returns an XML like this:

<pmsg:Messages>
    <pmsg:Message Info="blah" />
</pmsg:Messages>

I need to get the value of Info.

DECLARE @xmlMessage XML;

EXEC procReturnsXml @xmlMessage OUTPUT;

 -- returns 0 rows
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)')
FROM   @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg);

 -- returns NULL
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)');

-- returns NULL
WITH XMLNAMESPACES('pmsg' AS pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') AS INFO
FROM   @xmlMessage.nodes('/*') t(x);

Generally, I don't understand what these mean at all.
Can you provide an example exactly on this case, please?


texts: general memory requirements for sql server 2008 r2

texts:
I'm not experienced with DBA work, but I'm trying to make a case for requesting additional resources for our sql server and was hoping I could get some smart folks to provide a rough rough estimate of what we should be running.  I'm suspecting that the allocation of resources IT has given to our production sql server is low.

Hardware & Software:

Database: sql server 2008 r2 enterprise database

Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on VMware.

Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz  2.26 GHz (2 processors)

Installed memory: 4GB

Hard drive for Database files: 300GB

Hard drive for backups: 150GB

Hard drive for logs: 100GB

Application:

We have 3 main databases that add up to about 170GB in data, a Reporting Services database (SSRS) on the same server that houses maybe 10 different reports (comprising average of 700k records each) that get generated daily.  Our user base is about 20 simultaneous users, maybe 5 of those could be considered "resource intensive" with generating data-crunching large reports.  Majority of users interact with database through asp.net website and Report server website.  Additionally, our developers use SSIS in BIDS extensively by remoting directly onto the server (2 remote connections max).  Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Current Problems:

We have chronic sever server timeouts and the response time to the website is pretty bad.  I suspect the amount of memory we have (4GB) is probably a large bottleneck.  Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.  While we aren't sql pros or (as I'm sure you can tell by our setup) db admin pros, I want to make sure I'm not expending all my time trying to squeeze out little potential performance if the hardware is the bottleneck.

Thanks all for the tl;dr avoidance!


texts: How to tell if `CLUSTER table USING index` finished?

texts:
I want to use an AWS scheduled Lambda function to update my PostgreSQL table indexes every month. I am not on psql bash and I am not leaving Lambda function up until CLUSTER finishes.

How can I tell if the CLUSTER finished?


texts: Can I enforce cascade delete in a delete from statement?

texts:
I am using constraints in a database meaning that records of a certain table cannot be removed lightly. That is normally the desired case.

However, is there a way to write a DELETE FROM query so that for every constraint that would normally block the removal of record/s, cascade delete will be used?


texts: Identifying blocking queries on MySQL 5.5

texts:
For an application using MySQL 5.5.60 under Debian, lately we experience transactions with database writes, which do not succeed within the 50s time limit of the innodb_lock_wait_timeout setting.

I need to identify the transactions / statements which hold those locks, thus the cause, to start resolving the issue.

My hope was to get this information from the MySQL side, but so far it seems embarrassingly difficult to identify the blockers.
Best information was in the TRANSACTIONS section from the report given by a show engine innodb status statement, e.g.:

---TRANSACTION 6FEE8DD5, ACTIVE 13021 sec
1314 lock struct(s), heap size 129464, 1599 row lock(s), undo log entries 1212
MySQL thread id 16165411, OS thread handle 0x7f1491f53700, query id 1056480972 host.domain.tld 217.69.64.139 username
Trx read view will not see trx with id >= 6FEE8DD6, sees < 6FEE0FE9

Though I do not see any SQL statement here.

For unknown reason such a transaction does not show up in the MySQL slow query log (or processlist, innotop, mytop etc.). And if it would show up there, it will not list the MySQL thread id or the query id.

The thread id seems to be listed in the MySQL global log, which I dared not to activate during business hours, due to the mass of information it has to store.

Is there a better approach?

My only hint for the invisibility so far is that multiple statements might be displayed differently.

  Note transaction can be active even if the connection is in “Sleep” stage – if it is multiple-statement transaction.

(Source)

Can this be the case? And how would one identify /debug such a source for locking?


texts: Why doesn't this query use an index spool?

texts:
I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap:

CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;

INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

And force a nested loop join with MAXDOP 1:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

This is a rather unfriendly action to take towards SQL Server. Nested loop joins often aren't a good choice when both tables don't have any relevant indexes. Here's the plan:

The query takes 13 seconds on my machine with 100000000 rows fetched from the table spool. However, I don't see why the query has to be slow. The query optimizer has the ability to create indexes on the fly through index spools. This query seems like it would be a perfect candidate for an index spool.

The following query returns the same results as the first one, has an index spool, and finishes in less than a second:

SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);

This query also has an index spool and finishes in less than a second:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);

Why doesn't the original query have an index spool? Is there any set of documented or undocumented hints or trace flags that will give it an index spool? I did find this related question, but it doesn't fully answer my question and I can't get the mysterious trace flag to work for this query.


texts:
I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk insert (using copy) time was cut from 22 minutes to 2.3 minutes. The deletion time was also improved. I plan to make this bulk update every two hours or every hour.

Although the performance now (after SSD) is compatible with a more frequent update I have read some horror stories about SSD death due to limited NAND endurance combined with write amplification. As SSDs are expensive I would like to push its death as far into the future as possible. Hence my question: What really happens to the disk file in a delete and subsequent vacuum? I guess there are two disk writes, one to mark the row as deleted and the other when vacuuming to mark it as available to overwrite. If instead of deleting and vacuuming I partition the table creating and dropping tables at each bulk insert/delete would I be minimizing the SSD wearing?


texts: Can I restore a database to a point of time without backup?

texts: I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?


texts: SmallDateTime to DateTime Conversion Equality

texts:
If I were to convert a table's smalldatetime field to a datetime field instead, what are possible side effects (with regards to the database only)? Are there equality issues that might come up in stored procedures? Could joins, with or without casting result in errors or different results?

It seems to me that converting smalldatetime to datetime is mostly harmless but I wanted to get an understanding of possible pitfalls.


texts: How do I rule out SQL Server as the reason for a slow website

texts:
A website we host has recently been experiencing slow load times. It's been ruled out that the problem lies with the web server and it maybe database related.

What steps or procedures do I take to confirm or rule out that SQL Server is the reason for the slow load times? Also, without a baseline, what may be behaviours/characteristics of SQL Server to look out for that could indicate that there is a problem?


texts: SQL server unable to create user with dash in domain name

texts:
I am in the process of creating a proxy user in SQL server. I run the below SQL but it failed with the error because my domain name has dashes in it.

CREATE LOGIN [ABC-SEN-DEA\proxyssisuser] WITH PASSWORD='Password'

  Msg 15006, Level 16, State 1, Line 1
  'ABC-SEN-DEA\proxyssisuser' is not a valid name because it contains invalid characters.

Please help how to rectify this error.


texts: Is the SQL Server Express memory and CPU limit per instance?

texts:
If I have 8GB of RAM in a server and I run 4 instances of SQL Server Express, will the total memory limit used by SQL Server be 1GB or 4GB?

Would it be advisable to run multiple instances like this to enable each database to make better use of resources (assuming that the server has plenty of resources)?


texts: MySQL Incremental backup on Master/Slave

texts:
I've a mission critical MySQL Master/Slave config, which I'm backing up with innobackupex. I want to speed it up a little as the backup is about 60GB, so I'm considering something like a full backup on Sunday's and incremental backups from Monday to Saturday, or something like that.

My questions, as I've master and slave:

Is a good idea to backup from slave?
Am I going to be able to spin up a new slave from this backups? (if not, I'll backup from master)
Do you recommend any other incremental backup strategy?

Thanks.


texts: MySQL connection count much larger than query count

texts:
I'm maintaining a relatively simple LAMP server. It does how ever have a some what high load.
What I'm noticing is that in a one hour time span there are roughly 50k connections made to the mysql server, yet there are only 17k-18k actual queries made. There are only 12 aborted clients.
I've enabled the general log, but I couldn't see any empty connections.
I don't maintain the PHP scripts that connect to the database myself,
but I do have access to them. They always execute at least one query.
The bulk of the connections are made by a php script that does:

Connect to mysql
Execute one very fast query
Close connection

So the connection hardly ever show up in the processlist.
There are no connections left open.
netstat only shows the 3 connections made from my own computer.
The processlist shows the same thing.
Does anyone have suggestions on how I could trace the 34k connections made, that apparently don't do anything.
Here is a screenshot from the phpMyAdmin Status tab:


texts: sql server 2016 sp1 to sp2 with latest cumulative

texts: I inherited an SQL Server 2016 SP1. I am not a DBA and work at a small company where we do not have a test environment. I know that I need to install the SP2 first.  Do I then install the last cumulative?  Are there any tools that would help me check what issues may come up after this update.


texts: Get All data from one table according to another, using Temporal Tables

texts:
I have two simple tables:
CREATE TABLE [dbo].[StatusForStudents](
    [ID] [int] NOT NULL,
    [PrimaryStatusID] [int] NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE [dbo].[Students](
    [ID] [int]  NOT NULL,
    [IsFoster] [bit] NOT NULL,
    PRIMARY KEY (ID)
);

With Temporal Tables feature working. Each have a ValidFrom and ValidTo fields.
I've added some dummy data:
insert into Students values (1,1)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (1,1)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (2,2)
WAITFOR DELAY '00:00:01';
insert into Students values (2,2)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (3,3)

Which got me to:
statusforstudents
ValidFrom               ID  PrimaryStatusID
2020-12-14 13:18:44.25  1   1
2020-12-14 13:18:45.27  2   2
2020-12-14 13:18:47.30  3   3

students
ValidFrom               ID  IsFoster
2020-12-14 13:18:43.22  1   True
2020-12-14 13:18:46.28  2   True

I now want for each record of statusforstudents, to match the relevant record of students.
That is - for each record in statusforstudents, find the record of students that was valid at the statusforstudents's time.
Or, in my example (ss for statusforstudents, s for students) the expected result would be:
ss.ValidFrom            ss.ID   ss.PrimaryStatusID     s.ValidFrom              s.ID  s.IsFoster
2020-12-14 13:18:44.25  1       1                      2020-12-14 13:18:43.22   1     True
2020-12-14 13:18:45.27  2       2                      2020-12-14 13:18:43.22   1     True
2020-12-14 13:18:47.30  3       3                      2020-12-14 13:18:46.28   2     True

I couldn't come up with anything better than just joining them, but that didn't give me anything useful..
Here's a working example


texts: Materialized View: What is the Update Trigger property?

texts:
I've created a materialized view in an Oracle 18c database.
When I look at the Info tab of the materialized view in Toad, I see that there is a property called Update Trigger.

What does the Update Trigger property represent?


texts:
In a scenario where Oracle Golden Gate is used to replicate a primary site with an Oracle RAC database to a secondary site (and active/active back) we suspect unexpected changes from the unused secondary site.

The issue is a bit hard to debug as we do not have direct DBA access. I wonder is there an easy way with unprivileged SQL access on the primary side to see if any changes are received from the other database?

Can I see counters or timestamps of OGG activity which helps me to track down DML made?

As I understand it I could see changes from the OGG user when setting up triggers or auditing - however both is not available in this situation.


texts:
My machine has 2GB RAM, I have some cron job run every minute insert into some data.

My mysql always overhead. I think it is very frequently, if some cron job mysql insert run, 5-10 minutes, the db will become overhead. 12 hours later, the overhead data will increase into 1-2MB.

how to reduce mysql overhead? this is my my.cnf

key_buffer_size = 256M
key_buffer = 128M
back_log = 3000
max_allowed_packet = 4M
table_cache = 2048
table_open_cache = 512
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
thread_cache = 512
query_cache_size = 384M
set-variable =wait_timeout=60
thread_concurrency = 4
log-slow-queries = slow.log
long_query_time = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 384M
ft_min_word_len=3
# log-bin=mysql-bin

here is some print in phpmyadmin.

Table    Action  Records  Type    Collation    Size       Overhead
table_nam        101,399  MyISAM  utf8_unicode_ci  112.7 MiB  1.2 MiB


texts:
I have a SQL code like shown below:

declare @str nvarchar(max),@i int
set @i=0
set @str='abc '
declare @tbl Table(a  nvarchar(max))
insert @tbl select @str
while (select a from @tbl)<>''
begin
    set @i=@i+1
    set @str = substring(@str,2,len(@str))
    update  @tbl set a=@str
select * from @tbl
end

Here @str has value 'abc ' (there is a space at the end). When above query is executed it will stop when only a space is present in 'a'.
Also output of this query is:

bc
c
<here blank>

For the above query if I give input @str as 'abcd'
then output will be

bcd
cd
d
<here blank>

So in the first case that is @str='abc ' I want to get output like

bc
c
<here blank>
<here blank>

Now the code is checking for space and because of that I am having problem. But I want it to consider the space at the end also.

Same is the problem in SQL for len() also. Both len('a') and len('a ') will return 1.
So if anyone please help on my query so that it will give my desired output.


texts: Referring to function parameters inside a Postgres function

texts:
I have a function like this in Postgres 9.5:

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
        BEGIN
                RETURN QUERY
                with t as (
                    select p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
                      FROM crosstab (
                        $$
                          select postcode::text, indicator::int, value::float
                          from my_data
                          where (indicator = p2 or indicator = p3) and date = p1
                          order by 1,2
                        $$)
                      AS ct (
                        "postcode" text,
                        "price" float,
                        "rent" float)
                )
                select * from t where result is not null;
        END
$func$ LANGUAGE plpgsql;

When I load it with PSQL I get back the error:

ERROR:  column "p2" does not exist
LINE 4:                           where (indicator = p2 ...
                                                     ^
QUERY:
                          select postcode::text, indicator::int, value::float
                          from my_data
                          where (indicator = p2 or indicator = p3) and date = p1
                          order by 1,2

I tried to prefix the p2 etc with my_func. but it doesn't work.
How can I refer to a function parameter in that context?


texts: How do you setup a new oracle instance from the commandline after having done a database software only install?

texts:
I'm trying to familiarise myself with the process for setting up new instances/databases from the commandline with the aim of building a tool for deploying databases.

The process we are thinking of using is

(optionally) create a new instance to deploy to
import some data using impdp
run upgrade scripts on the database to get it to the desired version

The bit I'm currently focusing on (+ have just cracked) is the instance creation, although I'd be interested to hear comments on other parts of it + any information on alternative approaches or things that are a good idea!  Im very new to databases aswell as oracle and am having to figure things out mostly from scratch.

The key requirement for all this is that is should be done in a way that can be scripted so we can automate the process.

I'm working on windows with 12c standard edition one and installed just the database software.


texts: Connection timeout via AlwaysOn Listener IP on local computer

texts:
I have AlwaysOn availability group and it has one Listener IP.
I can connect this instance using Listener name but, cannot connect using Listener IP from my local computer. It returns following error

  "Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=18013; handshake=14215;"

When I try to connect this instance from another remote server, both of them are works!

Is there any problem on my local computer?


texts: How to insert an IP-address into an inet column in PostgreSQL?

texts: I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are there any help-functions for this so I can test it from psql in the command prompt?


texts: How to not "duplicate" a table if it has a unique constraint over many columns?

texts:
I have a very large table (35GB) that is unique over a combination of four of its columns.

The table isn't very wide and the four columns it is unique over are the columns that are larger (in bytes).  The end result is that the index to keep the table unique is 21GB.  This isn't a result of the index bloating in size over time but is the size of the the index immediately after it is created.

I don't need to optimize for insert speed at all, as inserts will only happen in batches once per month.  There won't be any updates to any rows once they are inserted.

I'm running PostgreSQL 9.5.0.

Is there a way to not duplicate such a large portion of the database just to enforce a unique constraint?  Possibly using something like a clustered index?

Full table description:

CREATE TABLE medi_cal_base_eligibility (
    client_index_number text NOT NULL,
    medi_cal_date date NOT NULL,
    eligibility_date date NOT NULL,
    aidcode text,
    responsible_county text,
    status text,
    cardinal smallint NOT NULL,
    id SERIAL PRIMARY KEY
);

Indexes:

"medi_cal_base_eligibility_pkey" PRIMARY KEY, btree
    (id)
"medi_cal_base_eligibility_uq_dates_cin_cardinal" UNIQUE CONSTRAINT, btree
    (eligibility_date, client_index_number, medi_cal_date, cardinal)


texts: postgres fdw create foreign table without specifiy columns

texts:
need to create couple of foreign tables with a lot of columns from oracle.
I dont want to specify all cloumns by myself.
Is there a way to do something like this:

create foreign table ALFSTK **(*)** SERVER server1 options (schema 'test', table 'ALFSTK');

Or is this not possible with fdw?

Thanks for your advices.


texts: Search database name in information_schema database


texts: SQL Server and SQL anywhere compatbility

texts:
I wish to install a SQL server DB on a VM already running a SQL anywhere DB.

May I encounter a compatibility issue ?


texts:
I'm a second year software engineering major looking to create an iOS app that connects to a MySQL database (database not yet created) over the summer. I have a strong understanding of programming and object-oriented design, but have never taken a course in relational databases and don't know much about them at all. I could whip up an OO class diagram for this project in a matter of minutes but I can't seem to map that design to a relational database. This stuff is trickier than I thought.

Background Info About App: Well it's not incredibly complicated. The idea is to make an app that allows skateboarders to upload skateparks, write reviews on them, and discover new parks based on location, user reviews, photos, and other park characteristics. Users sign up for accounts within the app and get a username and password. They can search for nearby parks and filter them. They can bookmark their favorite parks. They can flag both parks and park photos if deemed inappropriate or inaccurate. Park reviews can be voted on as helpful or unhelpful. It's basically just a directory of skateparks w/ reviews and photos consolidated into an iPhone app.

Below is an image of my ERD at this moment in time. I'd appreciate it if you could pinpoint any flaws in the design (sorry for being so vague).

Specific areas I'm struggling with:

Do my primary and foreign keys make sense? Should I have an auto-increment ID for each table or is that not necessary?
Users need to filter parks by: distance, average rating, popularity (number of ratings and photos), free/paid admission, and types of riders allowed in the park (skateboarder, biker, inline skater, or any combo). Does the way my DB is currently layed out support efficient filtering of parks by these attributes?

Notes:

Just pretend that I have a fully functional user database and user_id works to identify unique users. I'm more focused on the park design than the user management design right now.
Ignore the relationships between entities. I don't really understand crow's notation at the moment..

Thanks so much!


texts:
I am running this syntax below on roughly 30,000 records.  The syntax is run 4 - 5 times a day and takes anywhere between 5 and 10 minutes to execute.  Below is sample DDL with a subset of data (not the 30,000 records) and I can upload my XML if someone will let me know how so you can see query execution.

If any one sees ways of optimization to reduce that lengthy time to return the data I will greatly appreciate it.

Declare @NumOfLeads Table
(
    clientname varchar(500)
    ,numoflicensesrequested int
    ,calldate date
)
Declare @PurchasedLicenses Table
(
    clientname varchar(500)
    ,numoflicensespurchased int
    ,purchasedate date
)
Insert Into @NumOfLeads (clientname, numoflicensesrequested, calldate) Values
('Client A', 10, '2017-01-01'), ('Client B', 5, '2017-01-02')
,('Client C', 7, '2017-01-01'), ('Client D', 12, '2017-01-03')
Insert Into @PurchasedLicenses (clientname, numoflicensespurchased, purchasedate) Values
('Client A', 10, '2017-01-10'), ('Client C', 5, '2017-01-15'), ('Client E', 10, '2017-01-15')
,('Client F', 11, '2017-01-15'), ('Client G', 5, '2017-01-22')

Select clientname, numoflicensesrequested
INTO ProdData
FROM @NumOfLeads
WHERE calldate BETWEEN '2017-01-01' AND '2017-01-31'

ALTER TABLE ProdData
Add numlicensespurchased int NOT NULL DEFAULT(0)

MERGE INTO ProdData pd
USING (
    SELECT clientname, numoflicensespurchased as CNT
    FROM @PurchasedLicenses
    WHERE purchasedate BETWEEN '2017-01-01' AND '2017-01-31'
) pl
    ON pd.clientname = pl.clientname
WHEN MATCHED THEN
    UPDATE SET pd.numlicensespurchased = pl.CNT
WHEN NOT MATCHED BY TARGET THEN
  INSERT (clientname, numlicensespurchased, numoflicensesrequested)
  VALUES (pl.clientname, pl.CNT, '0');

Select * FROM ProdData order by clientname asc

EDIT
SQL XML Plan is here
https://www.brentozar.com/pastetheplan/?id=SJ16cKXkZ


texts: Will an AD Account inherit all the User Mappings to databases that it's parent AD Group is mapped to?

texts:
If a Windows AD Group and an AD Account that belongs to that AD Group are both setup as Logins on a SQL server, will the AD Account Login inherit all the User Mappings that it's parent AD Group Login are currently mapped to?

Or would I need to re-apply the same User Mappings to the AD Account Login that are already setup on the parent AD Group Login?


texts: MySQL, error creating system tablespace ibdata1

texts:
I have a MySQL database (version 5.6.21-log) which I wanted to reset.

I have erased the database folder (where I had my tables), and also the ibdata1 (the system tablespace).

The problem is that when I restart the server I get this error:

[ERROR] InnoDB: space header page consists of zero bytes in data file
/myqdata/myserver01/data/ibdata1

It looks like MySQL is not able to create the system tablespace correctly. I am not finding many references to this issue, any idea on how to


texts: Query optimization ndbcluster + order by

texts:
I'm not really all too familiar with the ndbcluster storage engine of MySQL, but I've recently written an application that does some very simple queries. However, a co-worker of mine (who is now on vacation, or I'd ask him about it, of course) left a TODO in the comments next to what I thought to be a pretty harmless query.

The comment read "TODO: Avoid sorting on PK when querying on MySQL cluster"

Granted, there are some things I'm aware of when working on a cluster (like using less JOIN's) but I gathered the index (and therefore the PK) lookups were effectively O(1) operations, so a simple ORDER BY someID DESC wouldn't hurt performance.
I've been looking around on google, but I can't quite find a definitive answer as to what this ORDER BY means on a cluster. I can make a few educated guesses, but still...

What's more, I can't really think what query I can use instead. Basically, I have a generic method that enables me to fetch the last N rows based on 1 or 2 values. The simplified version of the query looks something like this:

SELECT *
    FROM db.tbl
WHERE user_Id = ?
  AND req_action = ?
ORDER BY tbl_Id DESC
LIMIT 0,?; -- 0,1 is default, though

Most of the time, the limit, though is 0,1, so I came up with this alternative approach that enables me to not use the ORDER BY clause, but it reduces the flexibility of my method, rather significantly:

SELECT *
    FROM db.tbl
WHERE
    tbl_Id = (
        SELECT MAX(tbl_ID)
            FROM db.tbl
        WHERE user_Id = ?
          AND req_action = ?);

But in that case, I'm always going to end up with either an empty resultset, or a single row.

My questions:

Is it true that ORDER BY on the primary key is not advisable on a cluster
if so, what alternative constructs should I be looking at to maintain the flexibility of my initial query, while sticking to good practices for cluster queries?

Things to keep in mind:

The dataset I'm querying is certain to have hundreds of thousands, if not millions of rows.
The table will be used heavily, both read and write operations, 30 RW/s is not exceptional
I can write stored procedures, but we only use them as a last resort.
An EXPLAIN EXTENDED of both queries would have me believe that the first (my initial query) is still preferable, but I ran it on a vagrant box, and just noticed the test tables use InnoDB storage engine, so I take it I can't trust those results :)


texts: Update column by comparing date and string values together

texts:
I have problem in updating table.

I need to update table by comparing date and name with another table.
I have two tables:

 table 1 : dim_sesid

    name(varchar)                       role
    20111012133513aaa123
    20110908072611aaa121
    20111002210235bbb853
    20120113113353bbbl971

The role column is updated later.

and the other table is

 employeerole
    username(varchar)       role(varchar)           thedate(varchar)
    aaa                     technician              2011-10-12 14:35:13
    aaa                     technician              2011-09-08 07:26:11
    aaa                     technician              2011-09-08 07:26:11
    bbb                     day guard               2011-10-02 20:02:35
    bbb                     day guard               2012-01-13 10:33:53
    bbb                     night guard             2012-01-13 21:30:00

I want to update role in the dim_sesid table depending upon username and thedate. The same user can have different role in different date.  There is no common column in two tables and the only column to perform join is "name" in dim_sesid. There are some duplicates values in employeerole.

I write the following query to update the dim_sesid table.

UPDATE dim_sesid ds
       SET role =
             (SELECT min (er.role)
                FROM EMPLOYEEROLE er
               WHERE SUBSTR(
                       ds.name
                      ,1
                      ,length(to_char(thedate::timestamp, 'yyyymmdd') || er.username)) =
                       to_char(thedate::timestamp, 'yyyymmdd') || er.username );

But the problem is, no rows are updated.

If I check with  to_char(thedate::timestamp, 'yyyymmddhh24miss') condition, then it checks both date and time, but i want to compare only with date and username.

I am wondering how to check only 'name' and 'date' columns in two tables.


texts: Query results that not are in previous query result

texts:
I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want

For example i want the id_product of the products in which attribute = 'Product Type' and value != 'Motherboard' AND value != 'Intel Motherboard'

I have the following table:
Atr_basic

column         | type                 | attributes
---------------+----------------------+-----------------------------------------------------------
id             | int                  | (identity) primary key
attribute      | varchar              | not null
value          | varchar              | not null
category       | int                  | not null
id_product     | int                  | not null

The query:

SELECT id_product
FROM Atr_basic
WHERE attribute = 'Product Type'
      AND value != 'Motherboard' AND value != 'Intel Motherboard' AND category = 140

The problem with this query it's only give me the id_product from the products they have Attribute = 'Product Type' but some product doesn't have Attribute = 'Product Type'

I managed to do this query but it takes 120seconds to complete:

SELECT id_product
FROM Atr_basic as ab
WHERE id_product
    NOT IN (SELECT a.id_product
                   FROM Atr_basic as a
                   WHERE a.attribute = 'Product Type'
                   AND a.value != 'Motherboard'
                   AND a.value != 'Intel Motherboard'
                   AND Category = 140)


texts:
When we back up a database or log, what steps determine the name of the media set? Is it WITH MEDIANAME clause when we format the media? Is this information kept inside the header?
Or is this for naming a particular media (such as a particular tape)? Even though I don't see the point of naming the individual tape.

If I add a subsequent backup sets to the same media set, is it possible to change the media set name in anyway?

Thanks


texts: Does any DBMS have a collation that is both case-sensitive and accent-insensitive?

texts:
Note this question is vendor/version agnostic

It seems to me, as a speaker (typist, writer) of English, reasonable to expect words to be properly cased but not necessarily have the correct accents going in the right direction:

  as I mused in a tete-a-tete with Chloe the maitre d'hotel at the Champs-Elysees restaurant, while waiting for the garcon to fetch my sauted jalapeno pate...

You get the idea with that.

So today I thought I wanted a search condition to use a case-sensitive but accent-insensitive collation but couldn't find one. Is there a good reason for this or is  mine merely a rare use case?

Here's an example of some documentation I was looking at (though thinking vendor/version agnostic):

SQL Server Collation Name (SQL Server 2008 R2)


texts:
I have read this question:
What is the best way to get a random ordering?
and can do a select top 4 from tabel order by NEWID() but I need it per category.
So I need 1 row from category A, 1 row from category B, 1 row from category C and 1 row from parentcategory D.

So I tried to union them and I tried to add a wrapping select query which told me that I could not order the subquery... So I am stuck.

Here is the SQL I have so far:

SELECT top 4 Artikel.ArtikelID -- etc.
FROM Artikel
INNER JOIN Categorie ON Artikel.CategorieID = Categorie.CategorieID
INNER JOIN CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID
INNER JOIN CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
INNER JOIN CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
INNER JOIN Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
INNER JOIN Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID
WHERE (ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
and taal.taalcode = 'EN'
order by NEWID()

current result could contain 4 rows all of the same category. That is not what I want. It's a bit different than the possible duplicate because it is not grouped on 1 column, but two different once.


texts: PostgreSQL requirements for multi workstations

texts:
Can PostgreSQL run on network environment?  Does it required a dedicate machine to host the PostgreSQL engine, like MS SQL?

What am I trying to do is having multiple workstations can access to the network database at the same time, without connect to dedicated host machine.  The MS SQL Express doesn't allow me to put the database to network drive.


texts:
My company is moving our infrastructure to new data center and I am trying to figure out the best way to keep the database on the new server in sync with the current production database until the new environments are ready to go live.  Not being a full-time DBA, I did some research and from what I have read, it looks like a transnational replication setup would best suite our needs.

Some details: the production DB is around 90 GBs in size and using Robocopy it took about 9 hours to move a copy of it to one of the new servers.  The current production database will need to stay online and accessible throughout the entire migration process.  It's in simple recovery, so database mirroring is unavailable.

Is a transactional replication the best method to keep the databases in sync?

My plan:

(Done) Transfer the current database and log to the new server and attach it to the new instance of SQL Server
Setup a distributor on our development database machine and publish to it from the production database
Create a subscriber on the new database machine which will accept updates that are pushed out from the distributor, once each night

There are two things on my mind.  Transactional replication requires that each published table have a primary key and a lot of the tables in the production database do not have primary keys defined.  I don't think this will be too big of an issue since my main concern is just getting the databases in sync.  We'll test the different applications that the use the database at a later data, but I'd like to make sure it's not a serious issue.  Secondly, do I need to also move any associated system DBs from the original instance, such as master?  We're moving to an Active Directory setup in the new environment, so I don't care about the users and such, but I am unsure about the necessity of the system DBs.

And just in general, am I grasping these concepts correctly?


texts:
QUESTION: I have QUOTATION table and SOA table
quotation - table is for pending and not approve quotation.
SOA- table for approve quotation.

i will just transfer the data from quotation table to soa once it is approve, the difference is in soa it has soa_account_number.

the question is, is it advisable to do this process or i just use 1 table.

QUESTION: I have two table with same fields

motor_car_quotation
motor_car_soa

the motor_car_quotation table is the storage of motor car that is not yet approve.
the motor_car_soa is the table for approve quotation, i will just transfer all the fields from the quotation to motor_car_soa table once it is approve.

the question is, is it advisable to do this process or i just use 1 table.


texts: Is there a function like min_active_rowversion for Identity?

texts:
I'm storing a queue of events in Sql Server with concurrent writers and I'm assigning a sequence number using an IDENTITY column.

I'm willing to accept the gaps that might occur, but I would like readers not to read past the minimum identity active in any uncomitted transaction... like what I can do with min_active_rowversion for ROWVERSION columns.

Is this possible?

EDIT: The actual problem

What I'm trying to do is finding a solution for the problem described here: https://dba.stackexchange.com/a/135116/176861

This is solved for ROWVERSION with min_active_rowversion like this:

TX A begins and inserts a row with rowversion = 1
SELECT * FROM events WHERE rowversion < min_active_rowversion() => no result
TX B begins and inserts a row with rowversion = 2
SELECT * FROM events WHERE rowversion < min_active_rowversion() => no result
TX B completes
SELECT * FROM events WHERE rowversion < min_active_rowversion() => no result
TX A completes
SELECT * FROM events WHERE rowversion < min_active_rowversion() => both rows as result

Like this, I won't "read past" the minimum active rowversion and won't miss updates. But rowversion is not immutable like a proper identity is, and I would like to do the same but using an IDENTITY column.

I would like to make a SELECT statement that does not read a row with an id greater than the lowest identity in any active transaction.

EDIT 2: The schema

A simplified version of my table looks like this:

CREATE TABLE events
(
    sequence int NOT NULL IDENTITY(0,1),
    name nvarchar (850),
    data varbinary(max)
);

EDIT 3: Proposed solution

I got an idea for a - probably obvious - solution: Have both IDENTITY and ROWVERSION.

If a row have both, I can use an immutable Identity value as the starting point of where I want to read from (which row did I read and successfully process last) and min_active_rowversion for my cutoff, so I don't read to far ahead.

I'll try it out an report back here.


texts: Excessive amount of data received from server

texts:
We are having a problem with slow queries on a certain table. We tried some things with indexes but that didn't make any difference. Then we discovered that the amount of data being transferred was excessively high.

The table definition is as follows:

/****** Object:  Table [dbo].[survey_tbMultiLanguageText]    Script Date: 23-6-2015 17:45:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[survey_tbMultiLanguageText](
    [fMultiLanguageTextId] [int] IDENTITY(1,1) NOT NULL,
    [fProjectID] [int] NOT NULL,
    [fElementTypeID] [int] NOT NULL,
    [fElementCode] [varchar](50) NOT NULL,
    [fPropertyID] [int] NOT NULL,
    [fLanguageID] [int] NOT NULL,
    [fText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_survey_tbMultiLanguageText] PRIMARY KEY CLUSTERED
(
    [fMultiLanguageTextId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage] FOREIGN KEY([fLanguageID])
REFERENCES [dbo].[survey_tbLanguage] ([fLanguageID])
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage]
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject] FOREIGN KEY([fProjectID])
REFERENCES [dbo].[survey_tbProject] ([fProjectID])
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject]
GO

If I query this table like this:

select top 100 *
from survey_tbMultiLanguageText

This results in bytes received of 1.284202E+07 (12.25 MB)

Within these 100 records the max length of fText is 380, and if I convert to nvarchar(380) the number of bytes received is much lower (79339).

The database server I'm testing this on is (select @@VERSION):

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64)     Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation     Standard Edition (64-bit) on
  Windows NT 6.3  (Build 9600: ) (Hypervisor)

Oddly enough, when querying for a specific project/language, it gives different results on different servers when querying the same data (normal on staging vs excessively high on live). Yet the top 100 query also gives the same problem on staging.


texts: I have a managed MySQL instance on GCP. Where would I find a query log? Is this something I need to enable first somewhere? I have read their documentation ( https://cloud.google.com/sql/docs/mysql/how-to ) and didn't find it helpful on this topic.


texts: Trying (and failing) to create a DVD cataloging database table design

texts:
I've been trying (and failing) to create a DVD cataloging database. My initial design has a whole bunch of many to many relationships. Consequently, my attempts to normalize it has produced a whole bunch of Composite Entities.

It's a huge mess and I think I've almost certainly done something wrong somewhere.

Right now, it looks like this:

movies(id, tmdb_id, imdb_id, title, summary, release_date, runtime, poster_path, rating)
series(id, tmdb_id, imdb_id, title, summary, start_date, end_date, poster_path, rating, status)
seasons(id, tmdb_id, imdb_id, title, summary, season_number, poster_path, FK series_id)
episodes(id, tmdb_id, imdb_id, title, summary, airdate, still_path, episode_number, FK season_id)

genres(id, genre_name)
people(id, tmdb_id, imdb_id, name)
jobs(id, job_name)

movie_genres(FK movie_id, FK genre_id)
series_genres(FK series_id, FK genre_id)
movie_crew(id, FK movie_id, FK person_id, FK job_id, character_name)
season_crew(id FK season_id, FK person_id, FK job_id, character_name)
episode_crew(id, FK episode_id, FK person_id, FK job_id, character_name)

discs(id, type, features, status)
case(id, name, status, location FK disc_id)

disc_movie(FK disc_id, FK movie_id)
disc_episode(FK disc_id, FK episode_id)

The problem is, I'm not sure how to fix it.

I know the first four entities have a lot if common attributes. I don't know if I can rip them out and create a new entity and then have the four inherit from the common entity (????) or...?

Assuming I'm on the right track with that, I don't really know how to model it either.


texts: Local Database for Importing/manipulating ~200gb of data

texts:
So I've recently come into a huge data set that i'm looking to work with - About 100gb in size across 80 flat files all of which I need to store in a relational way in order to interrogate, work with and actually perform data transformations and statistical analysis on.

I'm by no means a database administrator - I'm more in the statistical analysis space, but have learnt SQL and regularly work with local instances of SQL Server 2012 Express and R Studio. I use SQL Server to perform the manipulations, filtering and data clean up and R Studio to perform the statistical analysis; however historically I've only worked with data up to 10gbs.

During the SQL Express data import process I see that the maximum write speed is about 4mb/s (with my SSD I presumed it would be higher) and it has the storage limitation of ~10gb. Given I'm set up with an SSD, i7 processor and 16gb of ram (which i can upgrade to 64gb of ram if needed), I expected this to be faster. I'm thinking SQL Express is no longer be the ideal platform, but i'm not sure where to next. I'm open to paying for the versions of SQL Server under $1000, but I just don't know if that's what I need.

My question is, given the scale of this data and that I really need to be able to filter, combine and transform huge scales of it, what would be the ideal database I should be using? My preference is that there are suggestions for a windows platform, but I'm open to Linux databases too.


texts:
I have a SQL Server database (2008R2) running on a Windows 2003 box.  I'm connecting to it using another Win2003 box.

When I select a datetime value from the database, I'm getting it back in this format:

  11/5/2014 12:07:46 PM

However, when I'm using a different system (Win2008 connecting to a 2008R2 database), I'm getting it back in a more expected format:

  2014-11-02 10:53:00

In both of these cases, I'm pulling the values through a Microsoft OLE DB driver directly from the database tables. The SELECT statements are not converting these values in any way.  This is being pulled from some C++ code and logged to a file.  When I connect using SSMS, the values are always in a more expected format (no AM/PM).

My Question:
Why am I getting two separate formats from the same version of SQL Server?  Is this an OLE DB Driver setting somewhere?


texts: Does --single-transaction and transaction_isolation level set to repeatable-read does same thing

texts:
In my my.cnf file Transaction isolation set to repeatable read. I am trying
--single-transaction  for dump for consistent backup.As per documents --single-transaction sends signal to server as to set  transaction level to repeatable read and then backup gets completed. My question is does I need to use --single-transaction in dump or not? please advise pros and cons  of this.


texts: 64 bit Postgres 9.1 Limitions on Windows 2008 R2?

texts:
When Deploying Postgres on Windows Server 2008 R2:

Are there any limitations on windows that don't exist on Linux?
Does the postgres scale in the same way on windows as it does on Linux?
Is performance on windows reasonably close to performance on Linux say within 10%?


texts: Select into specific array positions with array_agg()?

texts:
Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.)

For example, I would like to select an item and its stock information from the following tables:

Table item:

CREATE TABLE item (
  id integer NOT NULL
);

INSERT INTO item VALUES
 (1), (2), (3), (4);

Table item_stock (containing shop-specific information like stock and prices):

CREATE TABLE item_stock (
    item_id integer NOT NULL,
    shop_id integer NOT NULL,
    stock integer,
    cost numeric(19,3),
);

INSERT INTO items_stock VALUES
  (1, 1, 2, 10),
  (1, 2, 0, 9),
  (2, 2, 0, 9),
  (3, 1, 3, 22);

SQLFiddle

Looking for a query to produce the following results, where the array in the column stock contains stock info for specific shops. In the example, array position 1 is stock for shop_id=1 and array position 2 is stock for shop_id=2. 0 instead of NULL where no data is found:

id | stock
---+-------
1  | {2, 0}
2  | {0, 0}
3  | {3, 0}
4  | {0, 0}


texts: Using T-SQL to dynamically create Extended Properties

texts:
I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values.

It would be very laborious to add these two properties to each table and column. Is there a way to use T-SQL to be able to generate these via script?