texts: SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows

texts:
Brief on application:

This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database.

I have one application to create fingerprints of original songs and put these fingerprints in database. To store fingerprint in database I have table:

CREATE TABLE [dbo].[fp_core](
    [hashkey] [bigint] NOT NULL,
    [note_id] [int] NOT NULL,
    [timeoffset] [int] NOT NULL
) ON [PRIMARY]

The application processes song and takes 100 sample per second, so around 15000 samples for complete song. These sample values are stored in database, 1 row for each sample as {HASHKEY, NOTE_ID, TIMEOFFSET}. For fingerprint of complete song, I may have around 15000 rows in fp_core table. I am planning to put fingerprints of 50000 songs in database, so around 750 million rows will be in fp_core table.

I have other application to process recordings and detect songs played in it. Process is, create set of HASHKEY from recording audio, same as for creating fingerprint of original song. Recording audio will generate around 20000-30000 HASHKEYs. Then application retrieves rows from fp_core table for all matching HASHKEYs generated by recording audio.

To retrieve data from fp_core table by processing recording, I am doing is, filling these all HASHKEYs of recording in one more table, table is:

CREATE TABLE [dbo].[fp_core_keys](
    [hashkey] [bigint] NOT NULL
) ON [PRIMARY]

then I am joining these two tables to retrieve all matching rows, the query is:

select fp.hashkey, fp.note_id, fp.timeoffset
from dbo.fp_core fp
INNER JOIN dbo.fp_core_keys keys ON fp.hashkey = keys.hashkey

I have following indexes:

CREATE CLUSTERED INDEX [index_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_fp_core_keys] ON [dbo].[fp_core_keys]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Problem:

Retrieving data using above query is so slow, taking time around 40 seconds.

Right now, here is stats:

Query:

select count(hashkey) from fp_core
go
select count(distinct(hashkey)) from fp_core

Result:

57177764
13675633

Plan:

Can anybody help me?


texts:
I have installed Sybase(Developer's Edition for ASE 16.0) on a linux machine. I have executed the setup.bin file, and installed it using GUI. After this, I tried connecting to the localhost server, by running the dbisql executable in the DBISQL-16_0/bin folder of the base-installation-folder but couldn't connect to the 'localhost:5000' server, using the username/password of the server. I am not sure if the server is tarted. Do I need to run anything to start the server, or perform any other actions before trying to connect using this client?

Any help regarding this is highly appreciated.


texts:
So this is my set up. In our data warehouse we have mostly tables that are filled with data from a ETL-process. We do not take backup of these tables since we have an option to do a full reset using ETL. We also have a couple of tables with historical data. These are valuable and need to be backed up.

What I did was to split the historical tables into a separate filegroup and do a backup of this filegroup pretty often.

Then comes the trouble. I want to restore this. Since the historical filegroup are newer than the primary, I'm unable to restore it because I get a SLN error. I have simple recovery mode on the database.

In case of a data crash my thought was to restore the primary filegroup (consisting of empty tables) than restore the newest historical filegroup with the latest data, then start the reset action of my ETL.

Any ideas or solutions to how I can manage to to this?
I'm running MSSQL Server 2014


texts: Can jsonb do fast search by *value* or even a partial value? Other types?

texts:
I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice.

Example data:

id: 1, name: "Joe Doe", preferences: { color: "red" , toy: "car"}
id: 2, name: "Jane Doe", preferences: { color: "blue", food: "hamburguer" }

The problem is that I need to query it by the variable values, example:

All the people who has "hamburger" on preferences, and partial queries must be possible too, so searching for "burg" must bring me record 2.

Using json functions, I can search for values, as long as I known the key, right? Or there is a fast way to search for the values?

What I'm thinking to do is to create a tsvector column and dump the json to use fulltext search to do the partial queries.


texts: How to find out who started the 'SQL Server Agent' in Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64)


texts: Deny certain user from updating certain rows

texts:
Say I have a table like this:

canModify | name | age
------------------------
    1     | John | 24
    0     | Nick | 21

I want to grant update access to a specific user only where canModify=1

I know there is column level security, something like:

DENY UPDATE ON Table(column) TO user;

But I can't find anything like:

DENY UPDATE ON Table To user
WHERE Table.canModify = 1

Is there a trigger or something I can set up to do this?


texts: Case Behavior Changed Between 11.2.0.1 and 11.2.0.2

texts:
I have a case open with Oracle and they say they can't reproduce this.  Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query returns three rows unless the case statement is removed.  Can anyone confirm this behavior?

create table t1 as (select rownum+1 id1 from dual connect by rownum<=3);
create table t2 as (select rownum+1 id2 from dual connect by rownum<=2);

--Correct version that returns two rows.
SELECT t1.id1 id1, t2.id2 id2, t3.id3 id3
, DECODE(1,1,'a','b') "Case/Decode"
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id2
LEFT JOIN (SELECT 1 id3 FROM dual) t3 ON t3.id3 = t1.id1
WHERE (t2.id2 IS NOT NULL or t3.id3 IS NOT NULL);

--Incorrect version that returns three rows when it should return two.
SELECT t1.id1 id1, t2.id2 id2, t3.id3 id3
, case when 1=1 then 'a' else 'b' end "Case/Decode"
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id2
LEFT JOIN (SELECT 1 id3 FROM dual) t3 ON t3.id3 = t1.id1
WHERE (t2.id2 IS NOT NULL or t3.id3 IS NOT NULL);

The same issue seems to be effecting the following, which returns the wrong data even though the number of rows is the same.

create table t1 as (select rownum id1 from dual connect by rownum<=3);

--Correct version.
SELECT t1.id1, NVL(t2.id2,'NULL') id2, 'No' Case_Present, t2.Constant_Value
FROM t1
LEFT JOIN (select null id2, 'Should Be NULL' Constant_Value from dual) t2
ON t1.id1 = t2.id2;

--Incorrect version shows values for Constant_Value when it shouldn't.
SELECT t1.id1, NVL(t2.id2,'NULL') id2
, case
when 1=1 then ('Yes')
else ' '
end as Case_Present
, t2.Constant_Value
FROM t1
LEFT JOIN (select null id2, 'Should Be NULL' Constant_Value from dual) t2
ON t1.id1 = t2.id2;


texts: PostgreSQL: Function to reformat a string

texts:
Suppose I have a string such as:

abcdefghi

and I would like to reformat it as:

abc def ghi

(that is, I have added some formatting spaces to the string).

Is there a PostgreSQL function which will do that? Something like:

reformat(abcdefghi,'xxx xxx xxx')

I know there isn’t the above function, but there might be a built in function to do something like that.

If not, I am happy to write such a function, but I though I would check.


texts: Microsoft ODBC Driver 11 for SQL Server: Named Pipes Provider: Could not open a connection to SQL Server

texts:
I am aware this question has been asked before. I have been following the extensive troubleshooting steps at MSQL Tips as well as every answer I can find on Google. I am really stumped.

The error is as follows:

I cannot seem to get past this error. Every time I install/uninstall MS SQL Server I receive the same error. I have even performed a hard reset of the laptop removing even the BIOS information but I still get the same error. I have discovered that installs of MS SQL Server are concurrent so this perhaps explains it.

I even set up a virtual machine running Windows 7 to debug and got the exact same message. The application can be run on a different workstation and works perfectly. It seems the issue is something hard coded on to my computer.


texts: Maximum concurrent users on linux server

texts:
I have a dedicated server with these info:

128G RAM
6 cores/12 threads CPU, 3.4GHz
512 SSD storage
WHM/Cpanel latest
Apache/MySQL
Centos 7.x

I have a single website on this server which have heavy traffic at specific times of each day for less than one hour. At the peak traffic about 6000 users according to google analytics real time stats.

The average page load time by the server at about 500 users is about 0.15 seconds and the website code is very optimized for mysql.

The problem the server until 2000 concurrent users never have problem. After that number of users the Server load sometimes becomes 100% and above and mysql load becomes 600-900% and the website takes forever to load and displays server busy page and most of the time I have to restart mysql and apache servers.

These are my current settings in WHM for Apache:

Start Servers = 5
Minimum Spare Servers = 5
Maximum Spare Servers =10
Server Limit (Maximum: 20,000) = 5000
Max Request Workers = 5000
Max Connections Per Child = 10000
Max Keep-Alive Requests = unlimited
Timeout = 300

These are my settings for mysql in my.cnf which is default by WHM:

# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
log-error=/var/lib/mysql/hosting.hostrose.com.err
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=10000
max_connections=151

My question, what is the best values for these vars to serve that number of users without overloading or hanging the server specially for apache and mysql connections numbers.


texts: Completion of SQL Job triggers start of job on a different SQL Server

texts:
I want the completion of a SQL Server agent job to trigger the start of a job on another server.

More specifically, I want my SSRS server to email a subscription report whenever an FTP/Import job completes on a different production server.

if I do:
EXEC msdb.dbo.sp_start_job @job_name = 'Remote job name', @server_name = 'Remote server'
I receive:
The specified @job_name ('Remote Job Name') does not exist.
(maybe trying to run locally?)

if I do:
EXEC [Remote Server].msdb.dbo.sp_start_job @job_name = "Remote job name'
I receive:
Could not connect to server 'RemoteServer' because 'RemoteUser' is not defined as a remote login at the server. Verify that you have specified the correct login name.

This user is one we use to query across linked servers all the time, can query across, and has db_owner (just to test) rights on msdb.


texts: OpenProject pg_restore - input file does not appear to be a valid archive

texts:
I'm trying to restore my OpenProject's database created with the command
sudo openproject run backup

this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore with the command
pg_restore --host=localhost --dbname=openproject postgresql-dump-20190722131834.pgdump

but it returns me the error
pg_restore: [archiver] input file does not appear to be a valid archive

I've also tried to att the --format=c option to the pg_restore command, but the error changes to
pg_restore: [archiver] did not find magic string in file header

The script which OpenProject runs to create a PostgreSQL backup is:
dump_postgresql() {
  dst="${TARGET}/postgresql-dump-${timestamp}.pgdump"
  touch "$dst" && chmod 0640 "$dst"
  pg_dump -Fc $DATABASE_URL > $dst

  echo " done" >&2
  echo "$dst"
}

I've also noticed that pg_dump prints a lot of warnings like this:
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used

I'm trying to restore the database on a machine which runs Ubuntu 18.04 and PostgreSQL 10.
This is the result of the command head postgresql-dump-20190722131834.pgdump (I've put it in a screenshot because there are some invalid characters which I can't copy-paste):


texts: Get SSIS to check if excel file is updated?

texts:
So I have a SSIS package that reads from an excel file and populates a Sql server database. It assumes that the file will be updated monthly, and consequently, the data pull to update it is also done monthly.
However, as this excel file needs to be updated manually, there may be a case in which the user forgets to re-upload a new excel file. Naturally, I want to detect if that is the case. How would I be able to do this?
The best that I could think of was to use a script task to alter the excel file's sheet name to something like "already processed" after the data pull, and check for that condition at the beginning of the task. However, this seems like a work around solution that's overly complicated. Any ideas for better ways to do this?


texts: PostgresSQL date/time field out of range on seconds-since-epoch

texts:
I am importing a csv file into a PostgreSQL database. Most of the file imports without any trouble until I get this message

ERROR:  date/time field value out of range: "1421088300"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY accidents, line 6356158, column datetime: "1421088300"

But this seems to be a valid timestamp for 01/12/2015 @ 6:45pm (UTC) according to https://www.unixtimestamp.com/index.php

So why is it out of range?


texts:
I want to learn PostgreSQL and How to make the learning process efficient, but really don't know how long it would take even after googling my question.

I used MySQL before and knew relational database. However, I would like to use PostgreSQL the implement my web back end database management system. I want sufficient knowledge to do it, design not ugly structure, write not ugly code, like I used MySQL for another web app project before.

I am not asking the specific time, but at least I want a sketch in my mind. How much effort I need to invest? What kind of problem I would have? Any lessons you learned can be shared?

Thank you very much!


texts:
In postgres partition is done by child tables. My problem are,

when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables.
How does the query optimizer do the partition pruning ? My concern is unlike other databases we don't give partition column in postgres partitioning process. So how does the query optimizer knows what is the partition column ?


texts: Replication members count limitation in MongoDB

texts: Is it possible to configure only one secondary and one primary in MongoDB Replication?


texts: Sort name by Thai character is not working

texts:
I am trying to sort a table by name. The name are in Thai character;

>Select name from product_categ
                    name
--------------------------------------------
 ระงับกลิ่นกาย
 สกินแคร์ (ดูแลผิวหน้า)
 รองเท้า
 ผู้หญิง
 เมคอัพ
 น้ำหอม
 วิตามิน อาหารเสริม
 เส้นบุก, บุกก้อน

When I try to sort the table by name, it does not work.(it does sort but is sorting in some different way / probably using the equivalent of Thai Character ?)

>Select name from product_categ order by name;
                    name
--------------------------------------------
 ชา กาแฟ เครื่องดื่มชงสำเร็จ
 ร่ม กันฝน พวงกุญแจ
 หมวก
 หมวก
 เล็บ
 แป้ง

So i tried to google and find a solution where i came across using collation directly in the query.However the query is not working for me.

select name from product_categ order by name COLLATE 'th_TH';

I receive and error mentioning error at 'th_th';

Any suggestion to fix this will be a much appreciated.


texts: How to compare two SQL Servers instance level objects for a migration?

texts:
I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server).

The databases themselves will be transferred using a database backup and restore script.
I have tested this fine and it works without problems.

My worry is rather regarding all the other instance/system objects, like Logins, SQL Agent jobs.
These have been scripted out from the source server and reapplied on the new server, and I'm afraid I have forgot some.
I can obviously visually compare using SQL Management Studio, which is what I did, but it is time-consuming and error-prone.
So, before plugging the plug off the existing server in due time, I would prefer to have a more "scientific" way to confirm that everything is in there...

I've looked for software doing this kind of comparison and have found only schema or data compare tools.
I guess I could compare the system tables like sys.sql_logins and msdb.dbo.sysjobs, etc, but I don't know them all.

In short, is there a way to quickly compare the instance-level objects of two servers, and if possible, their contents as well rather than just by name?

Thank you.


texts: Database design for bidirectional relationship

texts:
I am modeling a bidirectional relationship between users with a relationship table. Each relationship deals with two and only two users. Each direction of the relationship allows for 3 different statuses (Pending, Blocked, Allowed).

To reduce memory overhead, I have decided on the following system to keep the entire relationship represented by a single row instead of one row per direction.

The smaller user_id is always stored as user_a and the larger user_id is stored as user_b so there is no risk of errors.

I am looking for feedback on whether I should represent both directions with a single column of (3x3 choices) or two horizontal columns of 3 choices each.

i.e.

... | status_a_b

A_Pending_B_Pending
A_Pending_B_Blocked
A_Pending_B_Allowed
A_Blocked_B_Pending
A_Blocked_B_Blocked
A_Blocked_B_Allowed
A_Allowed_B_Pending
A_Allowed_B_Blocked
A_Allowed_B_Allowed

vs

... | status_a | status_b

Pending | Pending
Blocked | Blocked
Allowed | Allowed

Thank you!


texts:
After installation, test,admin and local are the default database.
As per the MongoDB tutorial, when we insert data in collection a database will be listed in show dbs command.
For a specific purpose, I want to create a new database but I did not find any information about creating a new database. I executed a command like create db dbname or create database dbname. For both of the statement I got below error:
SyntaxError: Unexpected identifier

Can I know what are the steps to create a database?


texts: Execute ALTER SYSTEM command in oracle scheduler

texts:
I would like to set a job in the scheduler which will do a change of the resource manager plan.

    BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
       job_name             => 'scott.dayplan',
       job_type             => 'PLSQL_BLOCK',
       job_action           => 'BEGIN ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'dayplan'; END;'
       start_date           => '17-MAY-10 01.00.00 AM Europe/Warsaw',
       repeat_interval      => 'FREQ=DAILY; BYHOUR=8;',
       end_date             => '99-SEP-04 01.00.00 AM Europe/Warsaw',
       comments             => 'Resource manager daytime plan');
    END;
    /

But the job_action is given wrong. Any ideas how to execute alter system command in create job function?


texts: How to get sum of the count of rows till date from table in MySQL

texts:
I have a table as below:
TABLE A:
id|  on_date |off_date
1 |2020-09-10|
2 |2020-09-09|
3 |2020-09-08|
4 |2020-09-09|2020-09-10
5 |2020-09-08|

I want to get the count of ids which are in on mode till a particular date (2020-09-11)
Expected output:
count|date
2    |2020-09-08 (because id 3 and 5 has on_date of 2020-09-08)
4    |2020-09-09 (because id 2,3 and 5 has on_date till 09-09-2020)
4    |2020-09-10 (because id 4 not counted because off_date is present and id 1 is added)

I am not really sure how to get this output.


texts: DBEAVER: SQL error or missing database (near ",": syntax error)

texts:
I'm using DBeaver (because Base only works on OS X) to work with an SQLite database. When I attempt to execute the following query, I get the error message 'SQL error or missing database (near ",": syntax error)', even though I've specified the database and table:

INSERT INTO `contacts`.`contacts` (`fname`, `sname`, `dob`) VALUES ('MTN', 'Account', NULL), ('A', 'Person', -20456000), ('Local', 'Police', NULL), ...;

contacts is the database, containing a table named contacts with the following structure:

CREATE TABLE `contacts` (
    `cid` INTEGER NOT NULL AUTO_INCREMENT,
    `fname` TEXT NULL,
    `sname` TEXT NOT NULL,
    `dob` INTEGER NULL
);

I can use the + button to insert rows, but I'd prefer to perform bulk inserts.

I've already looked at the SQLite INSERT documentation and it looks exactly the same as MySQL's, so I don't see why it's having a hissy fit.


texts: How does this replication behaviour work in a RDBMS?

texts:
Consider a 3 node (N1, N2, N3) system
N1 is master (used for writes) and N2, N3 (used for reads) are slaves
Write request comes to update R to R1, N1 writes to disk and crashed without replication
Will system continue to work and Oracle select the another master automatically?
Write request comes to update R to R2 on N2
N1 is back again

Which value will be kept - R1 or R2?

I am using Oracle as RDBMS.


texts: SQL Server - is there a way to convert OR conditions to UNION to improve performance (as part of execution plan, under the hood)?

texts:
Let's assume we have following query (greatly simplified, of course):

SELECT * FROM MyTable
Where A or B

The query takes about 30 seconds to run

When I rewrite the query as

SELECT DISTINCT * FROM
(
    SELECT * FROM MyTable Where A
    UNION ALL
    SELECT * FROM MyTable Where B
) q

then I get results in about 4 seconds. I have seen the same pattern multiple times in the past - replacing OR with UNION improves performance a lot

Question: is there a way to make SQL Server to convert all those OR conditions to UNION internally, while preparing execution plan?

I was trying to find a query hint but no luck so far. We are using SQL Server 2016 SP2


texts:
I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on.
However, this question in particular is related to the query plan of this query,
because I see warnings in the query plan (in the distinct sort operators)
two types of warnings, one related to spillage to tempDB and the other warning related to conversion of data types and cardinality estimate.
the query and the query plan are further down, after the pictures.
Questions
As I am dealing with system objects, how can I find out which ones I need to update the statistics?
Or otherwise, how to get out of this warning on the query plan?
And regarding the data type conversion, is there anything I can do to avoid this, and the cardinality estimate issue?
Some trace flag maybe?
it is a database of 600GB, I would like to find all the dependencies on a specific table, first level alone shows me 325 objects, but it is not a query I would run everyday. I am interested in clearing those warnings, but it is not a question of life and death.
Information
1st picture of warning on spillage on tempdb:

2nd picture of warning on spillage on tempdb:

3rd warning - related to data type conversion and may affect cardinality estimation:

;WITH Radhe AS (

            SELECT DISTINCT
            s2.object_id,
            Name=SCHEMA_NAME(S2.schema_id) + '.' + S2.Name,
            ObjectType = S2.Type,
            DependsOn = s1.object_id,
            DependsOn_Name=SCHEMA_NAME(S1.schema_id) + '.' + S1.Name,
            0 as Level

            FROM sys.sysdepends DP

            INNER JOIN sys.objects S1
                    ON S1.object_id = DP.DepID

            INNER JOIN sys.objects S2
                    ON S2.object_id = DP.ID

            WHERE S1.object_id = OBJECT_ID('DBO.tblborder')

            UNION ALL

            SELECT
            s2.object_id,
            Name=SCHEMA_NAME(S2.schema_id) + '.' + S2.Name,
            ObjectType = S2.Type,
            DependsOn = s1.object_id,
            DependsOn_Name=s1.Name,
            Level + 1

            FROM sys.sysdepends DP

            INNER JOIN Radhe S1
                       ON S1.object_id = DP.ID

            INNER JOIN sys.objects S2
                       ON S2.object_id = DP.DepID

            WHERE Level < 100
              AND S1.object_id <> S2.object_id
              AND S2.object_id <> OBJECT_ID('DBO.tblborder')

)

SELECT DISTINCT *
FROM Radhe
ORDER BY LEVEL DESC, DependsOn_Name

here is the query plan for this query
After updating the stats this way (from How to update statistics for a database's system tables) :
DECLARE @TSql NVARCHAR(MAX) = ''
SELECT @TSql = @TSql +  'UPDATE STATISTICS sys.' + o.name + ' WITH FULLSCAN;' + CHAR(13) + CHAR(10)
FROM sys.objects o
WHERE o.type in ('S')
ORDER BY o.name

--Verify/test commands.
PRINT @TSql

The warnings related to tempdb spillage are still there, however, they have changed as per the picture below:

Nothing has been said or addressed to the following warning though:

Type conversion in expression
(CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0)) may affect
"CardinalityEstimate" in query plan choice, Type conversion in
expression (CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0))
may affect "CardinalityEstimate" in query plan choice

The related Q & A How to update statistics for a database's system tables is very good, but did not seem to have solved my problem here entirely, plus no address to the cardinality estimate warning.


texts: How to identify connections using implicit transactions?

texts:
I'm investigating a blocking issue that I believe is related to implicit transactions not being committed or rolled back.

How can I identify connections that have turned the implicit transactions setting on?


texts: Execution of sequential delete and uncommitted read

texts:
We are running the following queries using dynamic SQL, which is erroring out for few cases when we run concurrent instances. The error is:

  Could not continue scan with NOLOCK due to data movement...

This question says this error is thrown when a process reads data which is being deleted by another process. Our process is deleting and reading the same rows, however one after the other as per following query (the SELECT is after the DELETE):

DELETE FROM Table1
WHERE colum1 = somevalue1
AND column2 = somevalue2

SELECT COUNT(*)
FROM Table1 WITH (NOLOCK)
WHERE colum1 = somevalue1
AND column2 = somevalue2

I am trying to understand the execution of above query. As the SELECT is uncommitted, does it start executing before the DELETE is committed? Would this mean that removing the NOLOCK hint would stop the error?


texts: MySQL: Alter Table on very active table gives "Waiting for table metadata lock"

texts:
At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking.  Basically, whenever we run an ALTER TABLE statement on a fairly active table, the table becomes inaccessible to both reads and writes and threads are shown hanging, basically forever, with the "Waiting for table metadata lock" message.

I've done a bit of research and found good explanations of this in the MySQL manual and here and see that this is an expected and actually intended behavior.  What I haven't found anywhere online is a graceful way to mitigate the obvious problems this causes in production.  The only way we have been able to run alter table statements on our more active tables since upgrading is by disabling read and write permissions for any usernames our application runs under, killing all processes that are still running, and then running the ALTER TABLE statements.  This works, but results in many errors from our app: both from background processes and our web site.

In the past (ie before 5.5) schema changes were no problem.  I understand the reason for the new behavior, but it causes obvious problems in production and I figure folks in the community must have work arounds.

Any ideas would be appreciated.  Thanks!


texts: SQL Server Maintenance plan that goes to next step when the condition passes

texts:
I am trying to create a maintenance plan in SQL Server. What I want to do is I have an Execute T-SQL Task and inside the task I have condition below:

Use master
go
DECLARE @dbname varchar(1000)
set @dbname = 'myTEstDB'
If sys.fn_hadr_is_primary_replica ( @dbname ) = 1
 BEGIN
 END

If the If condition brings 1 I want the maintenance plan to go to next step.
Can we do this? Please let me know if there is a way to implement it like this.


texts: Group Data and show group as separte column


texts: In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

texts:
We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers.

Question:
Is there a time where it would be correct to use 4K rather than 32K? If so, why? (Does it perform better when it can be used?) Or, is it just a legacy appendage from some prehistoric time when 4K was just the page size?

Background:
I have always just created a 4K, 8K, 16K, & 32K tablespaces and associated bufferpools when I create DB2 db's.

My manager is challenging me on this. (Good for him - I should know this!) He thinks we should just create a 32K tablespace and be done with it.

I'm not able to find anything that tells me, for instance, that when the row size allows it, we should use 4K rather than 32K because XYZ. It tells me I CAN do this, but not that/when I should.


texts: Decompose Relation R(ABCDE) in 2nf and 3nf

texts:
Given relation R(ABCDE) with functional dependencies

A->C
B->D
AB->E
Given: AB is the primary key.

I know that this relation is in 1NF because of partial dependencies A->C and B->D

Now how I decompose it to get 2NF or better form.
My concepts are weak , so please help me.


texts:
I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW.

When the management data warehouse is created it creates a number of agent jobs one of which is mdw_purge_data_[DB NAME] that goes through and purges data that exceeds the retention period, as far as I am aware. This job is currently running and its duration has increased form a few hours to days. This was initially an issue in RTM but was supposedly fixed in Service Pack 1 (we are running SP2). I have tried following an amendment of the stored procedure it calls as per this link:

http://www.toddbaker.org/blog/2010/12/17/sql-2008-mdw-fixing-long-running-purges/

This does not seem to have helped. I then read another blog post suggesting creating two indexes on the sql_handles columns of two tables in the MDW, snapshots.query_stats and snapshots.notable_query_text. When I try and create these I end up with a timeout and the index fails to create. This suggests that there is far too much information in there.

Has anyone got any pointers about reigning in the mdw_purge_data_ job as I think it may well be impacting the performance of the server as a whole.


texts: What are the primary differences between NuoDB and MySQL?

texts:
Since NuoDB can be used as a storage engine for MySQL, what are the primary differences between using the NuoDB as a server and using MySQL as the server?

In addition, recently I've received a recommendation about this new sql database that looks intended to become a very good solution for more flexible scenarios, cloud, etc. The thing is I can't find any information about real tests or any comparison with other database systems. I'm not sure how it differs from the existing uses for MySQL and how it is different from MySQL when used as a standalone database server.


texts: Extract SYNONYM and SEQUENCE from Informix metadata

texts: We are migrating Informix database to oracle. Please help us to extract SYNONYM and SEQUENCE from Informix metadata using query.


texts: DBCC FREEPROCCACHE along with Re-Organise Index maintenance task

texts: While creating Index maintenance plan in SQL Server, they have also added execute T-SQL statement DBCC FREEPROCCACHE. Is this beneficial, or are there any side-effects?


texts: Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine

texts:
I have oracle server (standard edition) installed on a guest VM (Windows 8 Hyper V).  I have mapped a host entry to the VM as "brettvm".  I am able to log into the VM and connect to the default instance using sqlplus.  So far so good.

EDIT: Here's the listner.ora file on the server.  I'm starting to suspect the trouble may lie here:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = c:\oracle

The tnsnames.ora file on the server looks like this:

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

The Problem:
When I attempt to connect from the HOST machine, sqlplus prompts for a password and then hangs.  Here is my tnsnames.ora on the host machine:

orcl.brettvm =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Some things I have tried:

Verifed that "brettvm" is in my hosts (pingable and browseable)
Verified connectivity between the host and VM over port 80 (browser / telnet)
Verified connectivity between the host and vm over port 1521 (telnet).  Note that I opened this port up on the VM through windows firewall.
Verified the database is up and running via sqlplus on the host (see above).
Turned off windows firewall on the VM

I'm going to start digging through the installation guide...

Any other advice?  Thanks.


texts:
I have 1.8TB AX database.

File Size : 1851427.75 MB

Free Space : 502837.13 MB

If free spaces will be used in the future, I don't want to shrink. Because it's taking a long time. (and index fragmentation)

Daily observations result:

Day Free space 492.435,75 MB (26%)
Day Free space 485.750,25 MB (26%)
Day Free space 502.875,75 MB (27%)

Sometimes the free space is reduced, sometimes data file size grows.

Why there is growth when there is free space, how SQL decides?


texts: MYSQL ways to represent '1970-1-1 0:0:0'

texts:
Out of interest, are there any other ways of representing the timestamp '1970-1-1 0:0:0' other than from_unixtime(0)?

In PostgreSQL there is 'epoch'::timestamp. Would there be an equivalent in MySQL which is as concise?


texts: I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished successfully?


texts: mongodb inMemory engine available in the community edition?

texts:
I'm currently working with MongoDB 3.2.10 and I recently heard of the new "inMemory" engine. After some research I found that it was available in the Enterprise Edition since version 3.2.6, but I couldn't find any information on an eventual release in the community edition.

So the question is: do they plan to release the inMemory engine for the Community Edition? If they do, do we know when?


texts:
I am not happy with the Wikipedia article about nested transactions.

I would not use the word "commit" for a nested transaction.

For me "commit" means "durability". A nested transaction does not provide durability.

Is it ok to use the word "commit" for nested transactions?
What word could be used, if it is not ok?


texts: How to run stored procedure in another database on the same server using a different login?

texts:
I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined.

I have a stored procedure in A, running as ua, that needs to run a stored procedure in B, running as ub (this is the only user that has access).

Until now, A and B were located on different servers, so I used linked server to switch login.
But now, when running the stored procedure in A, I get the following error: "Transaction context in use by another session".

I understood from googling for this error that this is by design, based on Microsoft's article - it mentions that loopback linked servers cannot be used in a distributed transaction.

Are there any other ways to make the stored procedure in A, running as ua, to run the stored procedure in B as ub, if both databases are located on the same server?

Thanks a lot!
Alex


texts: Why does importing a MySQL dump cause so many I/O?

texts:
I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many I/O?

I used the default options of MySQL Workbench to create the SQL-format dump files, namely:

It seems to match pretty well the advice of the section Bulk Data Loading for InnoDB Tables of the MySQL Reference Manual (except for autocommit=0, which is not present in my dumps). The resulting code headers in the dump look like:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `askbot_activityauditstatus`
--
LOCK TABLES `askbot_activityauditstatus` WRITE;
/*!40000 ALTER TABLE `askbot_activityauditstatus` DISABLE KEYS */;
INSERT INTO `askbot_activityauditstatus` VALUES
etc

I know I could try using other dump tools to speed up, but I'm curious. I'm especially impressed by the number of writes.

I use Windows 7 SP1 x64, MySQL 5.6 and MySQL Workbench 6.0.


texts: Does having a big table that I do not query cause the whole DB to be slower?

texts:
I have a log table that I use for logging files sent to clients.
I'm not querying that table, just use it for logging (INSERT) and when I need to check when something was sent to clients, usually once a week, maybe less.

Does having this table with BLOB field affects my DB performance? I'm putting PDF files in that table.


texts: Postgresql table function: get where condition

texts:
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.

Here, there is a very simple example:

create or replace function public.fn_world_cities()
returns table (
    city_id int,
    city_name varchar,
    country_id int
    )
as $$
begin

    ---- Extract temp table with all cities
    create temporary table tmp_all_cities on commit drop
    as
    begin $$
    select city_id, city_name, country_id
    from public.table_world_cities
    where row_status = 'A'; ---- filter active record

    ---- Return data
    return query
    select city_id, city_name, country_id
    from tmp_all_cities;

end; $$ language plpgsql;

The temporary table creation is time expensive.
When I use the function, always use some WHERE condition. Like:

select * from public.fn_world_cities()
where country_id = 10;

In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone.

Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation.

I wish I can have a function like this:

create or replace function public.fn_world_cities()
returns table (
    city_id int,
    city_name varchar,
    country_id int
    )
as $$
begin

    ---- Extract temp table with all cities
    create temporary table tmp_all_cities on commit drop
    as
    begin $$
    select city_id, city_name, country_id
    from public.table_world_cities
    where row_status = 'A' ---- filter active record
    and ***WHERE CONDITION APPLIED TO FUNCTION CALL***
    ;

    ---- Return data
    return query
    select city_id, city_name, country_id
    from tmp_all_cities;

end; $$ language plpgsql;


texts: Difference between Replication and Erasure coding techniques

texts:
I hope I am asking in the right community, If not, any suggestion will be appreciated.

I am doing a survey paper where I am doing a comparison between erasure coding and replication techniques. At this stage, I am comparing them regarding specific parameters as below.

The table that I am trying to construct is dealing with parameters that differentiate which technique is better in: storage efficiency, availability, durability, encoding time, latency of failure and cost of reconstruction.

Since replication is faster in terms of read performance when failure occurs, is it correct when I say that replication technique has Higher latency on failure? and the same for encoding time, is it correct to say replication has a high encoding time since it has better performance time in writing?
Does the reconstruction cost of failure in erasure coded system is higher than replication? does it involves more disk I/O ? will it be different if the failure is transient or permanent?
Will it be more informative if I compared all the above parameters according to transient and permanent failures?

is it correct if I compare them as below?

Erasure code: Higher ( Durability, Storage Efficiency, Availability ) and Lower (Encoding time, latency on failure, cost to reconstruct)

Replication : * Higher ( Encoding time, latency on failure, cost to reconstruct) and Lower ( Durability, Storage Efficiency, Availability)


texts: SQL Server 2017 Linux CU1 - MODIFY FILE encountered operating system error 31

texts:
I have a "VMWare RHEL 7.4" machine that I have installed "SQL Server 2017 Linux CU1" and I created a "Linux Linear Volume", see steps at the bottom, when I try to restore a database backup in the "Linear Volume" I got the error below.

/*
Msg 5149, Level 16, State 3, Line 6
MODIFY FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to expand the physical file '/sqldata/mssql_data/defense/defense_Data_01.MDF'.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
*/

I was able to restore the database backup on "/var/opt/mssql/data" without any problem, then I detached and moved the database to the "Linear Volume" and I was able to attach the database just fine, however any operation that needs to extend a datafile fails with the same error message.

I have setup "Linux Linear Volumes" the same way for Oracle and PostgreSQL databases and it has always worked fine with those.

Have you seen this problem? Is this a bug with "SQL Server 2017 Linux CU1" ?

Create Linux Linear Volume

vmware add /dev/sdc disk 100gb
Partition /dev/sdc and create /dev/sdc1 partition of 100gb
fdisk /dev/sdc
Create Volume Group
vgcreate vgsqldata /dev/sdc1
vgscan
vgdisplay vgsqldata
Logical Volume
lvcreate -l 25599 vgsqldata -n lvsqldata
lvdisplay -v /dev/vgsqldata/lvsqldata
Format the volume
mkfs.ext3 /dev/vgsqldata/lvsqldata
Mount
mkdir /sqldata
mount -t ext3 /dev/vgsqldata/lvsqldata /sqldata
df -kh
touch /sqldata/test.txt
ls -la /sqldata
rm -rf /sqldata/test.txt
Persist the mount
vi /etc/fstab
--mount logical volume
/dev/vgsqldata/lvsqldata  /sqldata             ext3    defaults        1 1
Change Owner
chown -R mssql:mssql /sqldata
ls -la /
"drwxr-xr-x.   5 mssql mssql  4096 Nov  7 10:43 sqldata"

The reason we use "Linux Linear Volumes" is because it is easier to add more space later, just need to add another vmware disk, partition the disk and add the new disk to the Linear Volume.

Increase Linux Linear Volume

vmware add /dev/sdd disk 100gb
Partition /dev/sdd and create /dev/sdd1 partition of 100gb
fdisk /dev/sdd
Extend Volume Group
vgextend vgsqldata /dev/sdd1
Extend Logical Volume
lvextend -l +100%FREE /vgsqldata/lvsqldata

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html-single/logical_volume_manager_administration/

I appreciate any feedback on this problem.

Thanks,

Marcelo Marques
Senior Technical Manager, OCP
www.esri.com


texts: Storing time with milliseconds in database

texts:
Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).

I was originally going to store it as TIME but then I realised MySQL doesn't support milliseconds in this type.

I will be storing the actual time taken from the users stopwatch, not the time when they ran and finished (this will be calculated before being inserted).

What would be the best field type to use? I was thinking either double or float, but I'm not sure that would work?


texts:
My mongod runs out of connections from time to time. But when I restart the instance, it becomes normal.

I check the log and found some connections were created and never be ended. This cause the connection depleted quickly.

My mongo cluster consists of  of 3 shards, each with 3 replicasets. So there are 9 mongod instance and 5 mongos instance. The problem occurs in one of the mongod instance.

I am running mongo version 2.4.

Here is part of the log:

Tue Sep  1 23:24:09.245 [initandlisten] connection accepted from 10.1.1.2:35569 #987213 (291 connections now open)
Tue Sep  1 23:24:09.687 [conn987214] end connection 10.1.1.1:36381 (339 connections now open)


texts: Query calculating AVG per minute GROUP BY timestamp


texts: Fail to copy all PostgreSQL databases between servers although there is no error message

texts:
I want to copy all databases from PostgreSQL 9.3 on Server-1 (Windows 7) to an empty PostgreSQL 12.0 on Server-2 (Windows Server 2012 R2). The steps are as follows:

On Server-1:

C:\Program Files\PostgreSQL\9.3\bin>pg_dumpall.exe -U postgres -W -f fulldump.sql

Copy fulldump.sql from Server-1 to Server-2.
On Server-2:

C:\Program Files\PostgreSQL\12\bin\psql.exe -f fulldump.sql -U postgres
Password for user postgres:

No error message appears, all seems to be fine. However, none of the databases appears in PostgreSQL 12.0 of the new Server-2. Unfortunately, there are to my knowledge no matching questions in this forum where I could draw some hints from. What is missing in my workflow?


texts: Log/Show number of (select) queries per table

texts:
I used the following view to summarize some metrics in my db. Is there any way to log the select queries per specific table instead?

create or replace view _dba_query_stats as
select
  SUBSTRING(VARIABLE_NAME, 5) as query_type,
  VARIABLE_VALUE as total_count,
  round(VARIABLE_VALUE / ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'), 2) as per_second,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60)))       as per_minute,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60)))    as per_hour,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60*24))) as per_day,
  FROM_UNIXTIME(round(UNIX_TIMESTAMP(sysdate()) - (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'))) report_period_start,
  sysdate() as report_period_end,
  TIME_FORMAT(SEC_TO_TIME((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status')),'%Hh %im') as report_period_duration
from
  information_schema.GLOBAL_STATUS
where
  VARIABLE_NAME in ('Com_select', 'Com_delete', 'Com_update', 'Com_insert');


texts:
I'm trying to find an answer to this one with not much luck.

I have an MS DB server with our main databases on it. Over time, someone has installed SharePoint and Microsoft Online (365) services which both created SQL Express instances.

My question is, SHOULD I move the databases off the SQL Express instances on to my main one and uninstall Express - and if so, why?


texts: Best backup strategy for MySQL (MariaDB) replication set

texts:
My main concern is getting a proper backup, even more so than a temporary outage.  The data is golden.  I've been doing reading through the documentation and a lot of the backup strategies seem to be.

Setup master-slave replica set.
When you want to back up, stop replication on the slave to basically "freeze" it.
Run your mysqldump or w\e you choose.
Restart replication and the slave will eventually catch up.

That's all well and good, but is there a theoretical off-chance that the slave could become corrupt (perhaps replication didn't happen right, or the slave disconnected from the replica set)?  If so, I will unknowingly be taking corrupted or stale backups.  What is the best strategy to avoid this?  The only thing I could think of is:

Stop the database service completely on master and slave.  Pull them out of the replica set.
Edit PHP config files to point to slave server.
Start the slave (essentially the new master) as to avoid downtime during backup.
Start the old master isolated and run your mysqldump
Stop both servers and somehow sync them back up to a consistent state (in case any writes happened on the old slave while the old master was backing up)
Fix your conf files, start the old master as the master and your old slave as the slave.

This seems very convoluted, is there a better solution?  I don't need multi-master.  The reads & writes will happen on one server.  The slave is just for failover purposes.


texts: understanding index fragmentation statistics

texts:
I'm using the query outlined in this stack exchange answer, reproducing it here for convenience...

  Here is a simple query to check fragmentation on your existing
  indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY
d.fragmentation DESC

DROP TABLE #indexDefragList

  This will return a list of all indexes in your current DB with their
  fragmentation in %.

The output from this query is confusing me.

I've limited the results to cover just a single index to simplify my question...

Some indexes are listed once, others appear repeated in varying numbers with different statistics.

What I'm struggling to understand is, why are some indexes returning multiple rows with different fragmentation and page_count statistics.

Is this a result of some implementation detail of MS-SQL indexes, if so how should I reconcile the different fragmentation numbers? Can you explain these results to me please?

Or, is this a problem with my database somehow reproducing multiple versions of indexes when all i need is one? Is this a problem i need to worry about??

Or, is it a problem with the query?

Thanks x


texts: Schema blocking in READ_COMMITTED_SNAPSHOT

texts:
I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues.

Background: Assume an online ordering system (ecommerce).  Product price changes are calculated minimum monthly, and this results in around 600,000 records that must be changed in the database.  When posting the price change updates to the database (SQL Server 2008R2 Web Edition) the site becomes unusable due to the significant levels of locking in the primary ProductDetails table when using READ_COMMITTED transaction isolation level.

To resolve this, READ_COMMITTED_SNAPSHOT is enabled, however other transactions are still being blocked during the price updates.  Investigation of sys.dm_tran_locks shows the blocked session is caused by a waiting Sch-S lock.  As I understand it, Sch-S locks (schema stability) are taken while a schema-dependent query is being compiled and executed (aren't they all schema-dependent?).  But sys.dm_tran_locks also shows a series of Sch-M locks (schema modification), which are not compatible with any outside operations per BOL.  I assume this is caused by the fact that the 3rd party tool used to replicate data changes drops foreign keys during the update process and recreates them after the update is completed.  And so, in spite of READ_COMMITTED_SNAPSHOT, other queries are still blocked, not by the update, but by the Sch-M locks cause by the changes to foreign key relationships.

This theory was confirmed by eliminating the setting that dropped/recreated the foreign keys.  Now the update process no longer takes Sch-M locks (sys.dm_tran_locks only shows X, IX, S locks), and other transactions are not blocked from using the version store to satisfy their queries.

However, when executed using this process, the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated.

Can anyone explain this behavior and offer suggestions on how concurrency could be maintained without exponentially increasing the maintenance time for price changes?


texts: What is this CASE statement doing exactly with the NVL function?

texts:
case when nvl(Q_IND, 0) = 1 then 1 else 0 end Q_IND

I'm having a hard time understanding what is happening in the above statement. Could someone explain?


texts: Query causing Database lock problem (php/Mysql)

texts:
What is the issue with my query? When I execute, it lock the database for 5 to 10 minutes. Please help me to optimize this query.

SELECT
    u.username,
    u.email,
    u.created_at,
    p.firstname,
    p.lastname,
    p.address,
    p.address_2,
    p.city,
    p.country,
    p.state,
    p.zip,
    p.phone,
    p.phone_2,
    u.last_ip,
    u.last_login_at,
    u.auto_login,
    u.registration_page,
    s.product_name
    FROM
    users AS u
        Left Join subscriptions AS s ON u.id = s.user_id
        Left Join profiles AS p ON u.id = p.user_id
    where u.registration_page='Chris_Cleanse' and
        u.id not in (select user_id from goal_sheets) and
        u.id not in(select user_id from sheet_user_popup_not_adam) and
        s.expired=TRUE ORDER BY u.id DESC;


texts: Reasons for a PL/SQL function to get slower with every execution

texts:
I created a PL/SQL function that translates sdo_geometry to ESRI sde.st_geometry:

create or replace
function tostgeom(sdogeom in sdo_geometry)
  return sde.ST_GEOMETRY
  is
  BEGIN
    return  SDE.ST_GEOMETRY_OPERATORS.st_geomfromwkb_f(sdo_geometry.get_wkb(sdogeom), 25832);
    EXCEPTION
         WHEN OTHERS THEN
            return null;
  END;

I need this function because some geometries are "broken" and can't be translated to st_geometry. (Testing the sdo_geometry with sdo_util functions reveals no problem)

Then I call this function in SQL:

create table test as
select id,tostgeom(GEOMETRIE) as geometrie from my_polygons
where rownum<8000;

The results are as expected, but the execution time gets slower (relatively) the more rows I include via "rownum".

For example:

1000 rows take 1.7 seconds
2000 rows take 3.6 seconds
4000 rows take 7.5 seconds
8000 rows take 17.5 seconds

As you can see, the execution "time per row" gets gradually slower for more rows.
If I use it on the full ~2 millon rows, it never finishes.

What could be the problem here?

I would expect the time per row to get better instead of worse for bigger datasets, beause of lesser overhead?!?

Thanks for any thoughts!

EDIT:

I made some more tests and it seems the problem is entirely within the "sdo_geometry.get_wkb" function.
When I use only this function to create a new table with a wkb-blob-column I get this results:

Objects | to_wkb (seconds) | objects/sec

10000 | 13 | 0,0013
20000 | 31 | 0,0016
30000 | 54 | 0,0018
60000 | 145 | 0,0024

The performance is obviously degrading the more rows are to be processed.

Is that a case for Oracel support?

Edit 2:

I looked at the v$mystat values for 10k, 60k and 120k rows.

Init: Initial Values for the statistics
diff: Difference after processiong of 10k rows
diff2: Difference after processiong of 60k rows
factor2: quotient diff2/diff
diff3: Difference after processiong of 120k rows
factor3: quotient diff3/diff

I only displayed the statistics where the factor ist greater than anticipated. (I assumed that the factor should not be greater than the increase in rows. 10k->60k Factor should be maximum 6)

Any Idea what those stats indicate?


texts:
I have few questions about using functions in a MySQL database for plugins and extensions of web apps like Magento or Wordpress.  Mostly this is for a few extensions I want to do for Magento.  The questions are:

is it possible under normal average hosts out there, that functions can be added.
(if 1) is it worth the effort to create function that would replace php in some manner (speed mostly is what I'm thinking)
(if 1) can functions be looped? like timed or call to it's self x time like sleep or something.

I was wanting to use the db for simple things like removing rows that were older then or something like that.  Examples are not as important as possibility here in my mind.  This is more of a set of questions that are due to not every really seeing web app use functions, and it just seems like an area of untapped potential for optimization when designing web apps.


texts: "A severe error occurred" - Login Failed (but only occasionally)

texts:
I'm using a C# application to execute a query against SQL Server. Here's the relavent portion of an exception I'm encountering once every twenty-four hours or so:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'S-1-9-3-3788657366-1166700905-846186909-3339902698'.
A severe error occurred on the current command.  The results, if any, should be discarded.

Here are the salient points:

I've been running this exact query for years, usually several
thousand times per day, with no problem.
The problem started immediately after updating to SQL Server 2014
from SQL Server 2008.
Unlike in SQL Server 2008, where I used a server-level user for
login, I'm now using a database-level login. That database user is
mapped to DBO. I'm not sure if this is even relevant but since the
error message says "login failed" I figured I'd mention it.

Again, the problem occurs only once in a thousand times -- maybe once every 24 hrs.

Is there a way I can reverse lookup the user in question given the user account ID? Recall that the error message provided me with this user ID: S-1-9-3-3788657366-1166700905-846186909-3339902698. Not that it should matter since my C# app only uses this single user account.

For reference, here is my connection string:

Server=inst2;Database=ema;User ID=emauser;Password=myPasswordGoesHere

********* UPDATE ********

I invested a few hours on this and received some great help from people on this site. There doesn't appear to be any workable solution right now.

This error message, specifically when the username is omitted and replaced with a bogus SID that doesn't correspond to any particular user on the database server or calling client machines, is just a bug in SQL Server that occurs when using contained users. The following links touch on the subject:

https://stackoverflow.com/questions/18629840/sql-login-failed-for-user-s-1-5-21-1482476501-1214440339-839522115-500-long-id (see SiHoran's answer)

and

https://connect.microsoft.com/SQLServer/feedback/details/772069/login-failure-with-connection-pooling-in-contained-db-after-connection-has-added-contained-user

It seems to rear its head when making a call from C# / .NET program.

As far as I can tell, the "contained user" functionality is just a bit flaky at the moment. Presumably switching back to an instance-level user (e.g. "sa") will fix the problem. Will try that next.


texts: Multiple Set Quoted_identifier within one script takes the last one?

texts:
I'm using SQL Server 2019 and just found a weird behaviour.
Research has not gotten me anywhere further.
Can someone please explain this behaviour?
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    -- SET QUOTED_IDENTIFIER OFF
    -- if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:
1- quoted_identifier is on
2- quoted_identifier is on

but the following code:
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    SET QUOTED_IDENTIFIER OFF
    if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:
1- quoted_identifier is off
2- quoted_identifier is off

Even though it isn't going into the catch!!!
I must be missing something.
I was even able to simplify the code to the most simple :
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
SET QUOTED_IDENTIFIER OFF
if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';

Results:
1- quoted_identifier is off
2- quoted_identifier is off

I have some code that uses FOR XML, which requires me to set quoted Identifier ON, but I need to put it back to OFF, no matter if the XML portion succeeds or fails.  How would you do that?
My tests show that if I put the SET QUOTED_IDENTIFIER to off in the CATCH, the insert fails to say that my quoted identifier is not set properly although it is set at the beginning of the TRY to ON.


texts: Dropping large clustered primary key fills up tempdb

texts:
Due to one of our tables growing outside of the range of an INT, something that wasn't predicted when the DB was produced, we're need to change the PK field to a BIGINT instead.

We are however running into problems regarding tempdb space usage at the very first step while handling that table: trying to drop the primary key constraint on it so we can alter it. None of our test environments have more space than 50 GB reserved for tempdb, while the table takes up around 120 GB of space right now. The table has but 5 columns and they contain only other INTs and a few short varchars.

Do any of you have any better, perhaps lighter suggestions where tempdb and logfile are concerned, to do the same changes? The table has only a few columns, all of which contain very little data. Sample below, as simple as it may be.

ALTER TABLE OURTABLE DROP CONSTRAINT OURTABLE_PK

ALTER TABLE OURTABLE ALTER COLUMN OURTABLE_ID BIGINT NOT NULL

ALTER TABLE OURTABLE ADD CONSTRAINT OURTABLE_PK PRIMARY KEY CLUSTERED (OURTABLE_ID)

Thanks!

I've been thinking about just creating a new table with the same definitions and a BIGINT as a bulk insert, then renaming it while dropping the old one. But I'm unsure whether that will prove to be any better a solution since the constraints would have to be put in afterwards anyway.


texts:
Consider the following:

db=> SELECT ga, count(ga) FROM gb GROUP BY ga ORDER BY ga ;

which gives the following output:

ga | count
---+------
?  |  200
A  |  100
B  |   50
   |    0
(4 rows)

Why is there an additional row with an empty ga and a count of zero?


texts: Prevent users creating constraints or indexes with no name

texts:
I have done some searching but haven't found a solution to my problem.

I started with some databases where users didn't care to name PK constraints, foreign keys or indexes, ending up with system generated names like PK__CarRenta__3213E83F2E5BD364. On mssqltips there's a great article by Aaron Bertrand about how to rename these objects using a stored procedure.

So that's great...but how do I avoid users or applications creating new objects without names which are then assigned a system generated name by SQL Server?

I have already read an article by the same great Aaron Bertrand on how to use policy based management to enforce naming conventions. However it seems that this is only good for notification after the horse has already bolted (or the object has been created).

Can you think of a way to automatically roll back statements that try to create an object without a name?


texts: How do you roll up header-level values on a fact table with line-item granularity?

texts:
From what I've read, the recommended granularity for a fact table based on an order header / line item structure is at the line item level. In the process, any additive values that apply only to the order header are repeated for each line item:

Designing Dimensional Models for Parent-Child Applications

Dimensional Modeling Basics

Below (from the second link) is an example of such a design:

In this example, the shipping amount, which applies to the order as a whole, repeats for each line item.

But if you were to roll all the shipping amounts under a specific customer, you would have double the actual value. How do you avoid this doubling of the value?

Is the shipping amount considered a non-additive fact for this
table?
Is some kind of special handling required?
Is there any reason not to simply have two (nearly-identical) fact
tables? One for orders and one for order line-items?


texts: SQL Server: Create New GUID Values for Existing Index

texts:
I have an existing table with about 6 million rows. The relevant columns are:

ID int not null PK
Key uniqueidentifier not null

Read/write on this table by Key lookup is probably something like 100 to 1.

The existing rows were all created using newid() so they are not in sequence. An index exists for Key.

CREATE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY]

Given that there are already millions of rows in the table, is there any benefit to changing the GUID generation to sequential using newsequentialid()?

Going forward it would generate better data, but since it's unknown where within the index the new sequential GUID series would land, would it just screw up the index faster? Would it be better to leave the GUID generation non-sequential so that the remaining page space in the index fills more evenly?

As far as the growth rate of the table, it represents about 3 years of data.

Year    NumRows
2012    3962660
2013    1661189
2014    711241

Where 2014 is halfway through the year as of this question. There were some code changes at some point that reduced the rate of inserts, so I believe 2013 would be a typical annual row count.


texts:
I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate:

CREATE TABLE `pupilgroupteacher` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupid` smallint(5) unsigned NOT NULL,
  `startdate` date NOT NULL,
  `teacherid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`groupid`,`startdate`),
  KEY `teacher` (`teacherid`),
  KEY `group` (`groupid`),
  CONSTRAINT `fk_pupilgroupteacher_2` FOREIGN KEY (`groupid`) REFERENCES `pupilgroup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_pupilgroupteacher_1` FOREIGN KEY (`teacherid`) REFERENCES `employee` (`personid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Having a teacher ID and a month, I need to find all groups for which this teacher worked (or is going to work) at least one day in the given month.

Note: For a given teacherid there may be more than one startdate (if, for example, a teacher worked for a group, was replaced with another teacher and then replaced back on a later startdate).


texts: Can I add a unique constraint that ignores existing violations?

texts:
I have a table which currently has duplicate values in a column.

I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.

Can I create a UNIQUE that doesn't check for existing compliance?

I have tried using NOCHECK but was unsuccessful.

In this case I have a table which ties licensing information to "CompanyName"

EDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. One approach is to have the INSERTs use a stored procedure which will fail for duplicates... If it was possible to have SQL check the uniqueness on its own, that would be preferable.

This data is queried by company name. For the few existing duplicates this will mean that multiple rows are returned and displayed... While this is wrong, it's acceptable in our use case. The goal is to prevent it in the future. It seems to me from the comments that I have to do this logic in the stored procedures.


texts: Should I connect to my replica through PgBouncer?

texts:
We're using a setup of one Postgresql master server and a several replicas to be able to withstand master failure. Connection to master server is made through PgBouncer.
Currently the idea is that replicas would serve reads when the master is down.
Is it a good idea to connect to replicas via PgBouncer during master failover/switchover? Should it be the same PgBouncer instance or a separate one (for fault tolerance)?
We build a web service and a set of background jobs, database transactions are typically short-lived.
If there's an article explaining this, I would appreciate if you share a link


texts: Why the Accounting DB Schemas do not allow to maintain a clean Products Table?

texts:
As far as I know, the database schema of the vast majority of Accounting Systems follows this architecture:

Considering all the FKs, I know that the product table is going to grow in time and, if I don't want to corrupt anything, it will not let me to delete any discontinued product. Same goes for most of the other tables. I understand the reason behind using all those FKs, but following this scenario, I am mortgaging myself using those FKs today for a great pain in the future. If we use this DB in a high volume transaction company for some years, I'm almost certain that we will encounter performance and maintenance issues.

I'm asking from an amateur point of view, does anybody know if there exists another DB schema for Accounting Systems that allow to clean those tables?


texts: Synchronous data transfer from SQL Server to Oracle

texts:
I have a requirement like:

  The table is present both in a SQL Server Database and an Oracle database with appropriate data types. Whenever a record is inserted into or updated in SQL server database table, then the same operation us to be performed on the Oracle database table.

Example: Table_SQL in SQL Server DB and Table_Oracle in Oracle DB. A record with id=1 is inserted into Table_SQL, now the same record will also be inserted into Table_Oracle automatically without running an external package or anything.

Is there anyway to do it?


texts: How do I change a processor count on a stopped SQL Server instance?

texts:
I have restored an image of a production SQL Server 2008 R2 to a Virtual Machine to test out some changes.

When I try to start SQL Server to recreate tempdb (in partition that was not restored), it throws the following error:

  Your SQL Server installation is either corrupt or has been tampered with (Invalid max number of sockets or cores). Please uninstall then re-run setup to correct this problem.

The command I was using to start SQL Server is the following one:

SQLServr.exe /f /t3608 -s instancename

It would seem like the simplest solution to this would be to have however many cores it's expecting. Since that's not really an option with my current environment, I'm hoping to edit whatever setting is determining how many processors it's expecting.

However, I'm not seeing any documentation on doing this. Can anyone point me in the right direction?

It seems like this would have to be supported for servers that go from two processors to one, or any other kind of processor change.

SQL Server provides a cpu_count column in the view sys.dm_os_sys_info. Perhaps if I could modify wherever it is stored to match the server's actual running CPU count, it might start. However I can't find much info on where those values are pulled from, either aside from the docs.microsoft.com page below:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql

I don't think the problem is related to tempdb. I'm just running it in single user to recreate tempdb, since tempdb was not on the partition that got restored.  I think the problem has to do with the fact that the source server had 8 virtual processors, and the destination has 2-4.

I tried updating the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\NUMBER_OF_PROCESSORS but it gives the same error. Seemed like Windows reset it back to the original value on reboot, so I tried it without rebooting afterwards. Still same error.

Storage is a vanilla dynamic .vhd file. Nothing set for persistence.

SQL Server cannot start due to the error. The intent was to use this installation to test out changes to the existing setup. Reinstalling SQL Server would create a fresh configuration and defeat the purpose of the VM.

I could see how a core licensing issue would come into play, but I figured that there would have to be some method of limping along in the case of a single processor failure on a two proc server.


texts:
I am using oracle 11g database in windows platform. Also, my db is in archivelog mode and I am taking daily RMAN backups. Now, I need to schedule my Rman backups on daily and weekly basis. I read about dbms_scheduler and using windows task scheduler to schedule this.

Now my question is: Which is the better method to schedule this?
Do I use dbms_scheduler or batch scripts or does we have method using OEM?


texts: Behaviour of RAISERROR within a TRY/CATCH within a loop

texts:
I have just encountered a section of SQL that is behaving not as I would expect (See below for a distilled version of the SQL in question that demonstrates the problem pattern).

(This is on SQL Server 2008 R2 SP2 64bit)

The 'do some work here' part raises an error and triggers the error handling in the CATCH block. The error number is 515 (attempt to insert a null into a non-nullable column), hence RAISERROR is reporting the error, but then the loop continues, attempts the work again, throws an error, etc. in an infinite loop.

I would expect RAISERROR to cause execution to exit the loop. What is going on here?

Thanks

WHILE (@Applied <> 1)
BEGIN

    BEGIN TRY

        -- === Do some work here ===

        -- Successfully applied
        SET @Applied = 1;

    END TRY
    BEGIN CATCH

        -- Save the error details
        SELECT
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        -- Test for a deadlock or uncommittable transaction
        IF (@ErrorNumber = 1205 OR @ErrorNumber = 3930)
            -- Sleep for 5 seconds
            WAITFOR DELAY '00:00:05';
        ELSE
            RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH
END


texts: What are the most effective schema migration strategies?

texts:
I recently came across a migration system that relies on incremental numbers, so the scripts are executed in ascending order. A folder containing a few scripts might look like:

Script1.sql
...
Script1009.sql
Script1010.sql
Script1011.sql

The process of adding new scripts means checking different branches in version control to find the latest number, incrementing it by 1 and praying you don't raise a file conflict because someone did the same thing before you could push to the repo.

The only other way I know about is a dependency method: each script specifies a script it's dependent on, so the scripts are linked together in a hierarchy... which is great until someone mistakenly creates a circular dependency.

So: what other methods are there, and why are they (in)effective?

It should also be noted this is a theory question, I'm not looking for software.


texts: ORA-02292: integrity constraint violated - child record found while creating procedures in ORACLE SQL Developer?

texts:
I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS.

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
(CUSTOMER_ID NUMBER) AS
TOT_CUSTOMERS NUMBER;
BEGIN
    DELETE FROM CUSTOMERS
    WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
    TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END;
/

I have to execute the procedure to delete customer with id 1.

EXECUTE DELETE_CUSTOMER(01);

When I do this, I get an error

Error starting at line : 120 in command -
BEGIN DELETE_CUSTOMER(01); END;
Error report -
ORA-02292: integrity constraint (TUG81959.ORDERS_FK_CUSTOMERS) violated - child record found
ORA-06512: at "TUG81959.DELETE_CUSTOMER", line 5
ORA-06512: at line 1
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

I know this is because there is a foreign key for CUSTOMER_ID on the table ORDERS, which means the customer cannot be deleted because he has placed an order.
How do I write the code so that I can first delete the corresponding ORDER_DETAILS and then delete the corresponding ORDERS so that I can finally be able to delete a record from CUSTOMERS?

I tried rewriting the code but I am just lost now:

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
(CUSTOMER_ID_IN NUMBER) AS
TOT_CUSTOMERS NUMBER;
CURSOR C1 IS
DELETE FROM ORDERS
WHERE ORDERS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C2 IS
DELETE FROM ORDER_DETAILS
WHERE ORDER_DETAILS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C3 IS
DELETE FROM CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
BEGIN
    OPEN C1;
    OPEN C2;
    OPEN C3;
    IF C1%FOUND AND C2%FOUND AND C3%FOUND
    THEN TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END IF;
    CLOSE C1;
    CLOSE C2;
    CLOSE C3;
END;
/

Here are the tables for reference:


texts: Estimating compression ratio for SecureFile LOBs?

texts:
We made a migration from Oracle 10 to 11.2. Our lob objects are currently stored in the original LOB storage architecture. But 11g introduced the SecureFile  storage architecture and we want to use its compression feature.

For non-LOB objects we can use something like dbms_compression.get_compression_ratio to get compression ratio and forecast estimated space usage.

What is analog for LOBs? Os it impossible to check the compression ratio before converting from basic LOB architecture to SecureFile?


texts: Leaving out foreign key when creating a postgresql table

texts:
I have been using mysql for the past number of years and i have always wondered? Lets say you have several tables and the tables are so interconnected, in an normal schema, you would have foreign keys all over.
What I do when i have to reference another table is to store the id of the table i am referencing and not indicate that its a foreign key.
Must you indicate in a table schema that a field is a foreign key so that you use JOIN keyword later on in my queries or can i still use joins if I understand which columns are foreign keys, they are just not indicated as such?


texts: Native replication from Percona XtraDB Cluster to a standard stand-alone slave

texts: I'm replicating from one node of a three-member Percona XtraDB cluster to a separate slave, using regular MySQL replication.  The node is set to write binlog in ROW mode, with log_slave_updates=true specifically for this.  Replication seems to work fine.  Still I wonder, given that XtraDB is an extension of InnoDB which the slave is using, is there a risk of some updates not replicating?  Is it worth it to replicate into an XtraDB slave, and how would that need to be configured?  I want the native async replication to the slave as it is an analytics DB and the goal is to offload the production Percona cluster from analytics queries, so it should not be just a regular cluster member.


texts: Unable to remove permission for mysql.user

texts:
I am trying to remove permission from mysql user to not be able to delete specific database. But if i run this command:

revoke all privileges on test.* from 'demo'@'localhost';

or this

revoke drop on test.* from 'demo'@'localhost';

I get this error

  ERROR 1141 (42000): There is no such grant defined for user 'demo' on host 'localhost'

test is the name of the databse.

The permissions for demo user are this:

mysql> show grants for demo@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for demo@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'demo'@'localhost' IDENTIFIED BY PASSWORD '*C142FB215B6E05B7C134B1A653AD4B455157FD79' |

Also, when i login as user demo i am able to delete the database test.

Why i am getting this error when i try to remove the permissions from user demo?


texts: Error 5123 when creating a database with my SQL Server on Linux

texts:
I followed the steps of this MS Guide and I managed to run a localdb and create a Testdb with this script:

CREATE DATABASE Test
ON
    (NAME='Test_DAT',
    FILENAME='/home/dlag/Documentos/Proyectos/BDtest/Test.mdf',
    SIZE=10,
    MAXSIZE=50,
    FILEGROWTH=5)
LOG ON
    (NAME='Test_Log',
    FILENAME='/home/dlag/Documentos/Proyectos/BDtest/Test.ldf',
    SIZE=5,
    MAXSIZE=25,
    FILEGROWTH=5);

GO
use Test;
GO

Time after, I try to create another db with the following script:

CREATE DATABASE BconDatos2
ON
    (NAME='BconDatos2_DAT',
    FILENAME='/home/dlag/Documentos/Proyectos/BDs/BiblioConDatos2/BconDatos2.mdf',
    SIZE=10,
    MAXSIZE=50,
    FILEGROWTH=5)
LOG ON
    (NAME='BconDatos2_Log',
    FILENAME='/home/dlag/Documentos/Proyectos/BDs/BiblioConDatos2/BconDatos2.ldf',
    SIZE=5,
    MAXSIZE=25,
    FILEGROWTH=5);

GO
use BconDatos2;

and I get this errors:

Msg 5123, Level 16, State 1, Line 4

AND SAYS(translated from Spanish):

  The instruction CREATE FILE found OS error: 2(The system cannot find the file specified.) when trying to open or create the physical file.
   '/home/dlag/Documentos/Proyectos/BDs/BiblioConDatos2/BconDatos2.mdf'.

Msg 1802, Level 16, State 4, Line 4

(translated from Spanish):

  CREATE DATABASE error. Can not create some of the files in the list. See related errors

Total execution time: 00:00:00.081

On both DB I'm in master and the path for mdf and ldf files are previously created.

It's strange because in the script of 'Test' DB I can make SELECTS for Tables, but when I run:

SELECT Name from sys.Databases

This database doesn't appear, but another empty DB created later appears.

Anyway on 'BconDatos2' DB script I can't create DataBases...

Problems with permissions on scripts?


texts: Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

texts:
Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions:

Attempting to view or run a stored procedure
Attempting to insert into a table after adding a trigger for ON INSERT actions
Executing SELECT LENGHT(description), id FROM table (with the misspelling... when LENGTH was spelled correctly, the error did not display)

The error in question:

  Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.

This has been reported as a bug (basically, lack of backwards compatibility), but there appears to be some confusion as to how to resolve the issue:

MySQL's documentation says the solution is to dump your stored procedures again after the upgrade to 5.1, then restore them again. The bug report I mentioned earlier shows that this has dubious results.
The bug report suggests running mysql_upgrade as a possible solution (albeit with some concerning warnings). However, it has been reported elsewhere that this solution doesn't always work.

I am in the unfortunate circumstance of not having a test box under which to test possible solutions as our development box is pending an OS upgrade to allow us to install the versions of MySQL we want to install. I am hesitant to try any solution on our production environment unless it has been verified as working by people who have encountered this issue.

What is the correct way to resolve this issue outside of waiting for an official patch or solution from MySQL?

While losing our stored procedures is not ideal, it is also a route we are willing to take to resolve the issue provided that there are no better alternatives and it will solve the issue cleanly.


texts: Simple yet multilingual flashcards - proper database design

texts:
I'm making a simple flashcards app for personal use.

I've thought about having such table:

CREATE TABLE flashcards (
    translation  VARCHAR(50),
    entry        VARCHAR(50),
    lang         VARCHAR(2),
    displayed    INT DEFAULT 0,
    known        INT DEFAULT 0,
    lastKnown    DATE NULL,

    PRIMARY KEY (translation)
)

Sample data would be:

'périlleux'     ,   'dangerous'  ,  'fr'  ,  ...
'hasardeux'     ,   'dangerous'  ,  'fr'  ,  ...
'mauvais'       ,   'dangerous'  ,  'fr'  ,  ...
'pericoloso'    ,   'dangerous'  ,  'it'  ,  ...
'malfido'       ,   'dangerous'  ,  'it'  ,  ...
'niebezpieczny' ,   'dangerous'  ,  'pl'  ,  ...
'gefährlich'    ,   'dangerous'  ,  'de'  ,  ...
'paisible'      ,   'calm'       ,  'fr'  ,  ...
'tranquille'    ,   'calm'       ,  'fr'  ,  ...

However, I have a feeling that it's not the best way to design this database as it is not aware of the relation between the translations of the same word.

The app will work like this (using speech recognition and speech synthesis):

get entry from database and say it
get all the translations of it for chosen language
wait for me saying the answer or some "I don't know" command
update "scores"
in case I didn't answer correctly say all of the translations (from 2.)

Alternatively (in other mode):

get entry from database and one of its translation in another language and say it (the translation)

I can make it work just fine with multiple query's to the db. But I'm writing here to ask for more of an elegant way to deal with the design. For which I'll be grateful.


texts: How to find if difference between all row data for a specific columns are same

texts:
I have a table with column with following data,

Column1
0.50
1.00
1.50
2.00
2.50
....
10000.00

Is there a way to validate the pattern that each data row has a difference of 0.50 ?

I tried googling but even struggling with keywords

I want to achieve is that, I want to make sure no value between 1 and 10000 is missing, e.g. I want to know if 100,00 is missing or 9999.50 is missing. I am not even sure if it's achievable?


texts: SQL Server: What is the difference when backing up via .bak, BACPAC or just copying .mdf files?

texts:
What is the difference between a .bak file as created when backing up a database, vs just copying the .mdf files and restoring as mentioned over here, vs creating a BACPAC file.

In this case, the logs themselves are not important, and the .bak file is far larger than the actual database. (a 20GB backup on a 2GB database).

For this backup strategy (copying database files themselves) I would detach the database on a daily basis, copy the mdf files across to an external media and then reattach the database. There would be 3 backups of each database (with older backups deleted as newer backups are created):

db_daily_backup
db_weekly_backup
db_monthly_backup

I realise that MS SQL provides it's own backup mechanism, that is almost certainly superior to what I've outlined. However the bloat caused on even differential backups seems too much for what we need and the amount of space we have available.


texts: How do I get the adjacent value of MAX/MIN aggregate function?

texts:
I have table applications and assets. Assets belong to applications. I want to get the highest last modified date and also the adjacent value (of who that value belongs to).
Application table
| application_id | name    |
| -------------- | --------|
| 101            | Weather |
| 102            | HR      |

Asset table
| asset_id | last_modified | last_modified_by | application_id |
| -------- | ------------- | -----------------| -------------- |
| 1        | 2013-01-20    | Toby             | 101            |
| 2        | 2014-03-25    | John             | 102            |
| 3        | 2015-05-30    | Mike             | 101            |
| 4        | 2016-06-25    | Toby             | 101            |
| 5        | 2017-07-26    | Mike             | 101            |
| 6        | 2018-08-27    | Lucky            | 102            |

I want
| application_id | name    | last_modified | last_modified_by |
| -------------- | --------| --------------| -----------------|
| 101            | Weather | 2017-07-26    | Mike             |
| 102            | HR      | 2018-08-27    | Lucky            |

Getting the last_modified is easy but I want to get the adjacent last_modified_by field as well.


texts:
My slave is not connecting to the master and is showing the error

  Got fatal error 1236 from master when reading data from binary log:
  'Slave can not handle replication events with the checksum that master
  is configured to log; the first event 'mysql-bin.000002' a'

I have tried set global binlog_checksum='NONE'; and it works fine in masters but when I try to change slaves's cnf file binlog_checksum=none it fails and MySQL won't start after restarting it.

What can I do to resolve this?


texts: Query to find plan details for a specific stored procedure, searching by name

texts:
I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored procedure.

So far I have:

select
    qs.sql_handle
    , qs.statement_start_offset
    , qs.statement_end_offset
    , qs.plan_handle
    , execution_count
    , st.text
    , substring(st.text, (qs.statement_start_offset/2)+1,
        ((case qs.statement_end_offset
            when -1
                then datalength(st.text)
            else
                qs.statement_end_offset
            end - qs.statement_start_offset) / 2 + 1)) as [Filtered text]
    , qp.query_plan
from sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
where st.text like '%myProcedure%'
order by qs.sql_handle
    , execution_count desc

But this query doesn't return any information. Ideally I would replace the st.text like condition with something like object_name(procedure_id) = 'myProcedure', but I can't find the right way to do so.

Is there a better way to do so?

I could replace the condition in the WHERE clause with a qs.sql_handle = 0x000004004040400..etc. but how can I find out the sql_handle for the procedure? (this information isnt' found in sys.objects or in sys.procedures).

Any information is greatly appreciated.

Update:

I've executed the procedure (exec myProcedure ...) in a test environment and now it is returned by the above query and so I can get the plan handle to use the where sql_handle = 0x0034300.., but I'd like to run the same query from above in production, without having to run the procedure in production (just to find out its plan handle).

This is the reason why I'm trying to write the query in this way, to get the information based on name, not sql_handle (just because in prod I don't know what is its sql_handle).


texts: SQL - How to return first encountered row that matches specific criteria

texts:
I have a table that has the following format:

id | price | category
---------------------
1   22    hardware
2   11    software
3   null  hardware
4   null  hardware
5   55    software
6   null  hardware

I want to fetch any row whatsoever that has price = null and category = hardware. I only want to know if a row with a null price exists. So whether the query fetches row ID 3 or 4 or 6 doesn't matter.

I'm currently using

Select Distinct id where price is null and category = hardware

but on a very large table with many null values the Distinct will require internal sorting by the database which will take extra time.

Other options that come to mind are

Select max(id) where price is null and category = hardware

But all those options require some internal sorting, grouping or indexing.

Is there a simpler SQL command to just fetches any row whatsoever that matches price=null and category = hardware without grouping, sorting, or indexing?


texts:
I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS).
I found information that output error log to syslog.

[mysqld_safe]
syslog

But I want to use syslog for logging general logs and slow query logs.
Please advise me how to write config file?

I could not find how to do that in the reference manual.

  http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html


texts: SQL Server 2005 Percent Log Used Alert fires too fast?

texts:
I have a SQL Server 2005 database thats roughly half a terabyte.  Third party software that I don't control writes to the database.

I have a Maintenance Plan which backs up the transaction log, and is called by an Agent Job which executes every 3 hours. The Agent job as a second step also compresses the finished trans log backup with gzip.  Sometimes there are sporadic large bursts of updates to the database, and the transaction log starts to fill prematurely.

I created an Alert on the database based on the SQLServer.databases object, Percent Log Used counter, that responds by executing my backup trans log Agent job when percent of log used goes above 75%.

When I enabled the alert and a burst of updates occurred, the Alert fired much more quickly than the job could backup and compress the trans log, about 1 alert every 3 seconds I think.  I think a bunch of job start/exec commands were queued up, and weird things began to happen with the job - it would sit there spinning and never finish.  Job Activity Monitor said the gzip of a trans log was happening, but NO files were being compressed..
I finally had to stop the job and restart Agent.

So it looks like the Alert fires too quickly for the job.  What can I do about this?  Can I somehow set some flag (maybe a file?) while the job is working, and the alert does not call the job when the flag is set?

Am I just missing something in how this should work?

Any help appreciated.
aj


texts: Entity set that has a relationship with itself

texts:
I have this ER model that I am having difficulty understanding, since there is an entity set that has a relationship with itself. I am trying to understand two questions:

  1) Can a child be born before the parent?  2) Can a person to be a child of themselves?

For 1) I don't think this is possible, because the parentID field would be null

For 2) Since this is a many to one relationship, I don't see why this can't be true, as long as the person isn't a child of someone else.


texts: How and what do you backup for a simple Oracle DB?

texts:
Background:

I am in the following situation: We have a JIRA instance running on a dedicated server machine (Windows Server). It uses Oracle XE to store all the data. The JIRA instance and a  Oracle XE install for the data are both running on this one server box. JIRA does support doing cyclic export to XML, and we export this "XML dump" to a network share twice a day. But due to performance implications, we would like to replace this no-brainer solution with a (hopefully) no-brainer database backup.

Question:

Now, this question is not about the technical details, I can look them up easily enough. This question is about cutting through the blabla and hopefully getting to the point :-)

So I have this very simple one-user database on a single server and single harddisk, and want to do a backup to a network share:

What do I need to back up so that the database is recoverable if the machine breaks.

Is an "export" enough, do I need some setting files, etc?
Essentially, what does constitute a "full" backup, so that the database + instance can be reconstructed by a fairly inexperienced Oracle user, even if the instance saw some undocumented tweaking.

How do I backup, as in:

Which tools should I use to do the backup.
Should I try to get a live/continuous backup, or just do periodic "exports".
Should the backup be "initiated" by the DB or by an external script?

So basically, I have a simple setup. The database was simple to install. How do I backup this so that it'll be simple to maintain and simple to restore?

Hope this is not too generic or noobish for this site.