texts: Can a clustered index with low cardinality cause deadlocks?

texts:
I'm looking at a deadlock, which happens when two processes execute an UPDATE statement on the same table. Both transactions are implicit, meaning that they span only the current statement only, which in turn means that locks I'm seeing are not a carry over from a prior statement.

There are two locks and two processes involved in the deadlock. One of the two locks is a page lock in the table (One process has update lock the other one wants Intent Exclusive lock) and the second one is the key lock on the table clustered index (one process has Exclusive lock and the other wants the update lock).

Now, the fields being updated in the both UPDATE statements are not a part of the clustered index.

Still, I'm wondering can the fact that this clustered index has tonnes of duplicate values contribute to the deadlock?

I'm sorry for not posting the deadlock trace, the update statements, the definition of the table and of the index, there is too much to obfuscate.

My question is not how to solve my deadlock, my question is about effect of low cardinality clustered index on probability of deadlocks in principle.


texts:
In postgresql, I'm using the following configuration to maintain my weekly logs:

log_truncate_on_rotation = on
log_rotation_age = 1d
log_filename = 'postgresql-%a.log'
log_rotation_size = 0  #just rotate daily

This creates a log file with a name like 'postgresql-Mon.log' and when rotation occurs, it overrides the old file with the same name. This way I'm able to maintain a weekly log.

I now want to move this configuration to keeping the log for just one day, instead of 7 days. How do you suggest I tweak the config given above to achieve this? Do I only need to change the log_filename to postgresql.log? Please advise. Thanks in advance.


texts: MYSQL workbench & other IDEs not working on fresh OS after reinstall?

texts:
I've recently had to format my computer and re-install my Win7 OS. Then I reinstalled the MySql workbench I was using last time. However, each time I open it and create a schema, the entire workbench crashes. I've tried rebooting, reinstalling, "check online for help" option, and even tried HeidiSQL which also starts but fails to connect to server. I've attached screenshots.

I think I might have to assume that this is a system fault and my system, since it is new, has some dependencies or some configuration that is missing. But both installations were fine so I'm not sure what those dependencies and configurations might be. Can someone offer some guidance on what can be done here? Thanks, greatly appreciated!!

I'm using 6.3.7 build 1199 CE (64 bit) Community.


texts: Remove characters from PostgreSQL database causing encoding errors

texts:
I have a PostgreSQL 10 database that uses WIN1252 encoding.

One of my columns has values that cause conversion errors when running a select from pgAdmin 4:

SELECT myfield FROM mydb.myschema."MYTABLE"

Returns:

ERROR:  character with byte sequence 0x9d in encoding "WIN1252" has no
equivalent in encoding "UTF8"
SQL state: 22P05

Setting the client encoding to WIN1252 in pgAdmin 4 causes it to drop the connection to the database (I remember reading somewhere that pgAdmin 4 has issues with setting the client encoding, but track down the link).

Using psql and setting the client encoding to WIN1252, selecting one of the offending features:

SELECT myfield
FROM mydb.myschema."MYTABLE"
WHERE oid = 12345

Returns:

â€œA sample comment

Looking at the data that was originally imported into the DB, it appears that “curly quotes” were incorrectly imported.

Is there a way to remove these characters from the column - either replacing them with regular quotation marks, or simply deleting them?


texts: SQL query to aggregate data from three tables

texts:
I have the following three tables:

The "users" table:

user_id
first_name
last_name
username
password

The "users_addresses" table:

address_id
user_id
street
country_id
email

The "countries" table

country_id
name

A user can have multiple addresses, but each user has at least one address.

What I would like to do is to write a single query to get all the users with their latest address (the address with the higher address_id for that user_id).

I tried this query, but apparently, it doesn't return the latest address if there's more than one address for a user:

SELECT u.first_name
     , u.last_name
     , u.username
     , u.password
     , a.user_id
     , a.street
     , a.email
     , c.name
  FROM users u
  LEFT
  JOIN user_addresses ua
    ON ua.user_id = u.user_id
  LEFT
  JOIN countries c
    ON ua.country_id = c.country_id
 GROUP
    BY a.user_id
 ORDER
    BY u.last_name ASC

Any help is appreciated.

Thank you!


texts: Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

texts:
I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that one has a server, with this kind of memory? I should mention that the queries towards the table are mostly ad-hoc queries.

One issue I believe I have found that would arise is that queries towards the MEMORY table could cause temporary tables to get created. Temporary tables have a hard limit of utilizing up to 4 GB of RAM before they get converted to a MyISAM table. This would of course kill performance completely. The memory limit of temporary tables could be worked around by setting the tempdir to a ramdisk.

What other kinds of problems do you foresee?


texts: Linked server error not caught by TRY-CATCH

texts:
I am setting up a job to loop through a list of linked servers and execute a specific query against each one. I am trying to execute the query inside a TRY-CATCH block so if there's a problem with one particular server I can log it but then carry on with the other servers.

The query I'm executing inside the loop looks something like this:

BEGIN TRY
    SELECT *
    FROM OPENQUERY([server1], 'SELECT 1 AS c;');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;

PRINT 'We got past the Catch block!';

If there is a problem connecting to the server the code just fails immediately and doesn't transfer to the CATCH block.  If the server connects but there is an error in the actual query, e.g. divide by zero, then this is caught as expected by the CATCH block.

For example, I created a linked server to a name that I know doesn't exist.  When executing the above I just get:

OLE DB provider "SQLNCLI" for linked server "nonserver" returned message
    "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "nonserver" returned message
    "An error has occurred while establishing a connection to the server.
    When connecting to SQL Server 2005, this failure may be caused by the
    fact that under the default settings SQL Server does not allow remote
    connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

I've read BOL on TRY-CATCH and know that it won't catch level 20+ errors that break the connection but this doesn't seem to be the case (this is only level 16).

Does anyone know why these errors aren't caught correctly?


texts:
I have two tables, test1 and test2, and these tables have around 600M(70GB) and 10k(2MB) records, respectively. when I run a query with left join on site_id, it is taking too long, even though site_id has an index in both tables.table1.rank also indexed.

Added tables structure here db<>fiddle

EXPLAIN Query:

explain SELECT count(*) FROM test1 t1
   left join test2 t2
   on t1.site_id =t2.site_id where rank=1;

Execution plan:

"  ->  Gather  (cost=273235.26..273235.77 rows=5 width=8)"
"        Workers Planned: 5"
"        ->  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8)"
"              ->  Parallel Seq Scan on test t1  (cost=0.00..267015.64 rows=2087846 width=0)"
"                    Filter: (rank = 1)"

Execution plan with EXPLAIN (ANALYZE, BUFFERS):

"Finalize Aggregate  (cost=273235.78..273235.79 rows=1 width=8) (actual time=625.486..625.486 rows=1 loops=1)"
"  Buffers: shared hit=233204"
"  ->  Gather  (cost=273235.26..273235.77 rows=5 width=8) (actual time=624.638..700.069 rows=6 loops=1)"
"        Workers Planned: 5"
"        Workers Launched: 5"
"        Buffers: shared hit=233204"
"        ->  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8) (actual time=436.761..436.762 rows=1 loops=6)"
"              Buffers: shared hit=233204"
"              ->  Parallel Seq Scan on test1 t1  (cost=0.00..267015.64 rows=2087846 width=0) (actual time=0.014..336.926 rows=1739383 loops=6)"
"                    Filter: (rank = 1)"
"                    Rows Removed by Filter: 514726"
"                    Buffers: shared hit=233204"
"Planning time: 0.120 ms"
"Execution time: 700.117 ms"

Execution plan with a partition on test1, EXPLAIN (ANALYZE, BUFFERS):

"Finalize Aggregate  (cost=233119.25..233119.26 rows=1 width=8) (actual time=999.316..999.316 rows=1 loops=1)"
"  Buffers: shared hit=179937"
"  ->  Gather  (cost=233118.73..233119.24 rows=5 width=8) (actual time=998.124..1054.495 rows=6 loops=1)"
"        Workers Planned: 5"
"        Workers Launched: 5"
"        Buffers: shared hit=179937"
"        ->  Partial Aggregate  (cost=232118.73..232118.74 rows=1 width=8) (actual time=796.767..796.768 rows=1 loops=6)"
"              Buffers: shared hit=179937"
"              ->  Result  (cost=0.00..226900.55 rows=2087269 width=0) (actual time=0.014..649.061 rows=1739383 loops=6)"
"                    Buffers: shared hit=179937"
"                    ->  Append  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.014..515.933 rows=1739383 loops=6)"
"                          Buffers: shared hit=179937"
"                          ->  Parallel Seq Scan on test1_p1 t1  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.012..415.959 rows=1739383 loops=6)"
"                                Filter: (rank = 1)"
"                                Buffers: shared hit=179937"
"Planning time: 0.254 ms"
"Execution time: 1054.547 ms"


texts: Where is MSOLEDBSQL provider driver? "The 'MSOLEDBSQL' provider is not registered on the local machine."

texts:
This has been going on. At first MS said they will not support TLS1.2 for OLEDB then they said, they will. You can read about it here, where they said, the support comes in March 2018

Now it looks like package has arrived. I installed it. Then I ran soft without change to connection string using old style Provider=sqloledb. That was expected to fail and it did. I changed to new style Provider=MSOLEDBSQL (see first link). And now I get

  "The 'MSOLEDBSQL' provider is not registered on the local machine."

Ok. I didn't install provider after all. But I can't find it either. I thought, may be this is part of Native Client update. But that came out Jan 2018. I installed it, it didn't break anything, it didn't fix anything. Then I came across this provider that was just released. Installed it. This seem to be new ODBC version.

Bottom line, where do I get MSOLEDBSQL? Thank you


texts:
Without considering the indexes,
Is there a way to re-write this query and get the same result quicker?

select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*)
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
having COUNT(*)>1

When I re-write it as the the followig, it even gets worse.

;WITH R1 AS (

select ROW_NUMBER() OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo ORDER BY fo.OrderNo, fo.OrderItemSeqNo) AS N
       ,fo.OrderNo
       , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N = 2

OR THIS:

;WITH R1 AS (

select COUNT_BIG(*) OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo) AS N
       ,fo.OrderNo
       , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N > 1

It is a fact table in a datawarehouse.
the indexes are on the picture below.

An Indexed view in this situation would use much less space than an index.
I have this situation in a similar indexed view -- you can see the code below:

CREATE view [dbo].[VProcessStatus]
WITH SCHEMABINDING
AS
select processstatus,
      StatusCount = COUNT_BIG(*)
      FROM DBO.tbltaxtransaction
      GROUP BY processstatus
GO

GRANT SELECT ON [dbo].[VProcessStatus] TO [mydomain\itdevelopment] AS [dbo]
GO

CREATE UNIQUE CLUSTERED INDEX [IDX_VProcessStatus] ON [dbo].[VProcessStatus]
(
    [processstatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [pscheme_ProcessStatus]([processstatus])
GO

when I look for the space used by this view and the underlying table:

sp_spaceused 'tbltaxtransaction'
go
sp_spaceused 'VProcessStatus'
go

I get the following result:

The only extra is that this indexed view is partitioned according to the PARTITION schema and PARTITION functions below:

CREATE PARTITION SCHEME [pscheme_ProcessStatus] AS PARTITION [pfunc_ProcessStatus] TO ([FG01], [FG02], [FG03], [FG04], [FG05])
GO

CREATE PARTITION FUNCTION [pfunc_ProcessStatus](int) AS RANGE LEFT FOR VALUES (1, 2, 3, 9)
GO


texts: I have a production database. I am using FULL recovery model. I am taking regular FULL backup every week. I am planning to take LOG backup every day. Now, if my database crashes or failed to load due to a reason then what can I do? Restore the database backup and restore all the log backups or only the last log backup. Should I automate the FULL backup and LOG backup using Jobs? Any help will be appreciated


texts:
I'm trying to create a multitenant database, but I get ORA-00205

I made a directory for datafiles:

[oracle@localhost oradata]$ mkdir -p TESTCDB/data/TESTCDB

I made an initTESTCDB.ora file:

db_name='TESTCDB'
enable_pluggable_database=true
audit_trail='db'
control_files='/u01/app/oracle/oradata/TESTCDB/data/TESTCDB/control01.ctl'
db_block_size=8192
db_domain=''
memory_target=629145600
memory_max_target=629145600
open_cursors=300
processes=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'

I set environment variables

[petar.testDB ~] vi .profile_TESTCDB
#!/bin/sh
umask 022
ORACLE_SID=TESTCDB; export ORACLE_SID
ORACLE_HOME='/u01/app/oracle/product/12.2/db_1'; export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

Now, I try to startup database in mount mode and I get this error:

SYS> startup mount
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size          8791960 bytes
Variable Size         234883176 bytes
Database Buffers       50331648 bytes
Redo Buffers            3788800 bytes
ORA-00205: error in identifying control file, check alert log for more info

Thanks in advance.
Petar


texts: Can't modify FormatString on cube ( SSAS )

texts:
I'm trying to create a cube, to know a little more about SSAS and as a DBA, be prepared for anything ( almost ).

When I'm changing SalesAmmount's FormatString to Currency, it doesn't change the values to currency. It keeps the big number values.

What am I missing? I tried to find some "currency language" or another option with no success.

( The Canada is a little motivation with my objective to move to Canada :) )


texts:
I tried to drop one of the databases on the server. That locked the entire server up so I stopped the mysql process via the console.

I restarted the server, started mysql and now it seems hanged on:

debian-sys-maint | localhost           |                 | Query   | 192  | Opening tables | select count(*) into @discard from information_schema.COLUMNS |

Not necessarily hanged,  but this takes a long time to move to a different query.

The other databases seem locked but I see no locks in "show open tables".

Any idea what's going on and what I could try?

Thanks.

Edit: it seems like the locks are moving from database to database and table to table. I guess it's doing some kind of checks?

Edit2: I think I understand what's happening, the answer to why it's doing this is: https://stackoverflow.com/questions/12567122/linux-mint-trigger-slowly-query-on-mysql-on-system-booting


texts: show detail processlist like at mariadb on maxscale

texts:
on mariadb when i need check what are query is running or sleep on mariadb instance.
i can see with SHOW PROCESSLIST or select * information_schema.processlist.
But after mariadb join and client connect to maxscale i can't see processlist like SHOW PROCESSLIST or select * information_schema.processlist
any advice ?
maxscale configuration
[maxscale]
threads=auto
max_auth_errors_until_block=0
admin_host=192.168.101.107
admin_port=8989
admin_enabled=1

Edit.
I already update paramater at maxscale configuration
[maxscale]
threads=auto
max_auth_errors_until_block=0
admin_host=192.168.101.107
admin_port=8989
admin_enabled=1
retain_last_statements=20
dump_last_statements=on_error

query already show from command
maxctrl show sessions

but there is query from last execution on each session. and i can't see the different like sleep or still running when i run command SHOW PROCESSLIST on mariadb.


texts:
I am trying to create a report that calculates how many contracts have been funded each month for the year 2014. The problem is I am trying to limit the dates, meaning I want the user to be able to see of how many funded contracts there are of each month but not passed the present month. So we are in June right now, I do not want the user to be able to access the month of June or July because the month hasn't been over yet, where as the passed months can be seen but only up until the first day of 2014. So, I do NOT want the current month. But only up to the last day of the previous month of every month. I believe my Where Clause is where the flaw is because I'm still new at the function of GetDate and Datediff.

 ALTER PROC spGetAdminTotalYTD
    (
      @Begin_Date DATETIME ,
      @End_Date DATETIME ,
      @program INT = NULL
    )
 AS
    BEGIN
        DECLARE @year INT
        SET @year = 2014

        SELECT  d.name ,
                a.dealer_code ,
                b.last_name ,
                b.city ,
                b.state ,
                b.phone ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 1 THEN 1 ELSE NULL END) January ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 2 THEN 1 ELSE NULL END) Feburary ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 3 THEN 1 ELSE NULL END) March ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 4 THEN 1 ELSE NULL END) April ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 5 THEN 1 ELSE NULL END) May ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 6 THEN 1 ELSE NULL END) June ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 7 THEN 1 ELSE NULL END) July ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 8 THEN 1 ELSE NULL END) August ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 9 THEN 1 ELSE NULL END) September ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 10 THEN 1 ELSE NULL END) October ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 11 THEN 1 ELSE NULL END) November ,
                COUNT(CASE WHEN MONTH(c.Funded_date) = 12 THEN 1 ELSE NULL END) December ,
                COUNT(1) AS YTD
        FROM    tdealer a
                JOIN tContact b ON a.contact_id = b.contact_id
                JOIN tContract c ON a.dealer_id = c.dealer_id
                JOIN tCompany d ON c.company_id = d.company_id
        WHERE   YEAR(c.Funded_date) = @Year
                AND c.Funded_date >= DATEADD(MONTH,
                                             DATEDIFF(MONTH, 0, GETDATE()) - 4,
                                             0)
                AND c.Funded_date < DATEADD(MONTH,
                                            DATEDIFF(MONTH, -1, GETDATE()) - 1,
                                            0)
                AND c.program_id = @program
                AND c.funded_date BETWEEN @Begin_Date AND @End_Date
        GROUP BY d.name ,
                a.dealer_code ,
                b.last_name ,
                b.city ,
                b.state ,
                b.phone
    END


texts:
Suppose the following schema

CREATE TABLE `log_apache` (
  `date` datetime NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `input` bigint(20) unsigned NOT NULL,
  `output` bigint(20) unsigned NOT NULL,
  `server` char(30) NOT NULL,
  `vhostid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`server`,`vhostid`),
  UNIQUE KEY `date` (`date`,`vhostid`,`input`,`output`)
);

INSERT INTO log_apache VALUES ('2014-04-01', 12, 19031, 1358358, 'server 1', 11);
INSERT INTO log_apache VALUES ('2014-04-01', 1, 41051, 151977, 'server 1', 15);
INSERT INTO log_apache VALUES ('2014-04-01', 22, 4051, 11115, 'server 1', 5);
INSERT INTO log_apache VALUES ('2014-04-01', 21, 5893, 81906, 'server 2', 5);
INSERT INTO log_apache VALUES ('2014-04-01', 13, 4051, 11115, 'server 5', 11);
INSERT INTO log_apache VALUES ('2014-04-02', 9, 8195, 331551, 'server 3', 115);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 2', 12);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 3', 2);
INSERT INTO log_apache VALUES ('2014-04-03', 12, 19031, 1358358, 'server 4', 11);
INSERT INTO log_apache VALUES ('2014-04-01', 12, 19031, 1358358, 'server 4', 51);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 1', 11);

I need to form a view of the table that looks more like this

-----------------------------------------------------------------------------------
|  Server  | 2014-04-01                                | 2014-04-02               |
----------------------------------------------------------------------------------|
| Server 1 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 2 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 3 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 4 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 5 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
-----------------------------------------------------------------------------------

But not just with one day but for every day.

Ideally I'd have a where condition for the view so I could isolate vhostid and date range. I believe this requires a pivot table but I have very little experience with them and am not sure how to begin constructing such a query. If you could help with this and provide and explanation as to how and why the query should be that way that would be fantastic.

PS I came up with this query but clearly the result set it wrong.

SELECT t0.server, t1.`date_in`, t1.`date_out`, t2.`date_in`, t2.`date_out`
  FROM (select distinct(server) as server from log_apache order by server) as t0
  LEFT JOIN (SELECT server as Server,
                    sum(`count`*input) as date_in,
                    sum(`count`*output) as date_out
               FROM log_apache
              WHERE vhostid = 11
                AND `date` = '2014-04-01'
               GROUP BY server) as t1
    ON t0.server = t1.server
  LEFT JOIN (SELECT server as server,
                    sum(`count`*input) as date_in,
                    sum(`count`*output) as date_out
               FROM log_apache
              WHERE vhostid = 11
                AND `date` = '2014-04-02'
              GROUP BY server) as t2
    ON t0.server = t2.server


texts: How to show how many times the server was restarted on SQL Server?

texts:
Is there any way to show how many times the server was restarted this month?

I'm using SQL Server 2008.

Thanks


texts: Postgres 13 - how to downgrade password requirements?

texts:
I have a MS .net application that needs to access a postgres database that's been recently upgraded to v13.
I'm using the Npgsql library (very old version) and get the error Only AuthenticationClearTextPassword and AuthenticationMD5Password supported for now. Received: 10
The suggestion from this post is to comment-out the line password_encryption in the file postgresql.conf and then re-set the users passwords ALTER USER ???? PASSWORD '1234'. I try this and get the same error, I also tried ALTER USER ???? UNENCRYPTED PASSWORD '1234' but this option isn't available anymore.
The basic problem seems to be that postgres 13 wants a level of security that the Npgsql library can't cope with. I can't upgrade Npgsql (not my choice!), so unless I can downgrade the password requirements I'm going to have to regress to an earlier version of postgres
I've tried setting password_encryption = scram-sha-256 also password_encryption = md5 and I've tried commenting-out the whole line. Each time I make the change I run SELECT pg_reload_conf() (as per the comments in the file) and then reset the passwords before trying to log on again. Same error every time.

ok, just found a cure that will fix the immediate problem. I've edited the pg_hba.conf file (thanks @jjanes) and changed the METHOD from scram-sha-256 to md5. This seems to work.


texts: SSMS: Restart the values on a IDENTITY field

texts:
I am starting to use SSMS to manage a database and I am having a problem I would appreciate some help with.

I have a table that was created with

CREATE TABLE [dbo].[Machine](
        [Machine_id] [int] IDENTITY(1,1) NOT NULL,
        [Machine_alias] [nvarchar](50) NULL,
        [Machine_ip] [nvarchar](50) NULL,
        [Machine_stockWarning] [int] NOT NULL,
    .....
CONSTRAINT [PK_machine] PRIMARY KEY CLUSTERED
(
    [Machine_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then I tried to add a row manually and the Machine_id became 1, but there was something wrong with this line so I deleted it.
Then I tried to add another row, but this time Machine_id is 2. How can I reinitiate the count so that it starts on 1 again?


texts:
I've been thinking on how to store data efficiently in database tables, I'll give the examples in visual look. See pictures below.

I've come to think to store data in database using compressed values row with the thinking of to save a lot of rows to remove duplicate page_id's

The other one is the usual that I've seen, I separate row_id's per page_id's.

The one I'm concerned is, which is the best way or am I doing it wrong on my first way storing data.


texts: MySQL select...into variable yields null, even though data is there

texts:
My stored procedure performs SELECT ... INTO var yielding NULL, but if I repeat the same SELECT myself, I get a value.

Here's the relevant part of the schema

CREATE TABLE UrlAuthority
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,name            VARCHAR(255) NOT NULL COMMENT 'includes TLD suffix'
    ,UNIQUE(name)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE UrlFqdn
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,authority       BIGINT NOT NULL COMMENT 'references example.com'
    ,name            VARCHAR(255) NOT NULL COMMENT 'host.example.com'
    ,FOREIGN KEY (authority) REFERENCES UrlAuthority (id)
    ,UNIQUE (authority, name)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE Url
(
     id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,fqdn            BIGINT NOT NULL
    ,path            VARCHAR(255) NOT NULL
    ,FOREIGN KEY (fqdn) REFERENCES UrlFqdn (id)
    ,UNIQUE (fqdn, path)
)
ENGINE = INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

And here's the procedure

CREATE PROCEDURE UrlToId (url TEXT)
BEGIN
    DECLARE host      TEXT;
    DECLARE authority TEXT;
    DECLARE tld       TEXT;
    DECLARE pathtext  TEXT;

    DECLARE authority_tld TEXT;
    DECLARE fqdn          TEXT;
    DECLARE authority_id  BIGINT;
    DECLARE fqdn_id       BIGINT;
    DECLARE url_id        BIGINT;
    DECLARE err           TEXT;

    CALL UnpackURL (url, host, authority, tld, pathtext);

    SELECT JoinHostNames(authority, tld)      INTO authority_tld;
    SELECT JoinHostNames(host, authority_tld) INTO fqdn;

    INSERT IGNORE INTO UrlAuthority (name) VALUES (authority_tld);

    SELECT id
    INTO authority_id
    FROM UrlAuthority
    WHERE name=authority_tld;

    -- LOG ('UrlAuthority', authority_tld, authority_id)

    INSERT IGNORE INTO UrlFqdn (authority, name)
    VALUES (authority_id, fqdn);

    SELECT id
    INTO fqdn_id
    FROM UrlFqdn
    WHERE authority=authority_id AND name=fqdn;

    -- LOG ('UrlFqdn', fqdn, fqdn_id)

    INSERT IGNORE INTO Url (fqdn, path) VALUES (fqdn_id, pathtext);

    -- LOG ('Url', fqdn_id, pathtext)

    SELECT id FROM Url WHERE fqdn=fqdn_id AND path=pathtext;
END
|

The LOG lines are a bit of metaprogramming, they insert strings into a debug table.

If I run

call UrlToId('http://mail4.z.uk/foo/bar');

I get these debug messages

UrlAuthority authority_tld=z.uk authority_id=1
UrlFqdn fqdn=mail4.z.uk fqdn_id=NULL
Url fqdn_id=NULL pathtext=/foo/bar

Clearly the problem is that fqdn_id=NULL however, if I manually repeat the query which does SELECT id INFO fqdn_id and substitute the values of the variables indicted in the trace:

    SELECT id
    -- INTO fqdn_id
    FROM UrlFqdn
    WHERE authority=1 /* authority_id */ AND name='mail4.z.uk' /* fqdn */;

This selects 1 as expected.

The select gets the data, so why is the variable NULL afterwards?


texts: Questions on updating statistics and index maintenance jobs

texts:
Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions:
For statistics and index optimization I want to use Ola Hallengreens scripts.

If I update statistics only or rebuild/reorganize indexes: Is there anyting dangerous by cancelling the job right in the middle of it if I see logfile becomes too big or the job takes too long and slows down daily work?

If not there is nothing I need to worry about and I can run it maybe on any evening or on a saturday and watch how it works and cancel it with no problem if some of the mentioned problems occur. Is this true?

If I use Olas script configured like this (no offline index rebuild):

FragmentationLevel1 = 50%
FragmentationLevel2 = 80%
FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE’
FragmentationHigh = ‘INDEX_REBUILD_ONLINE’
@UpdateStatistics nvarchar(max) = 'ALL',
@OnlyModifiedStatistics nvarchar(max) = 'Y',

Can I use this safely during worktime when people are working on the database?

Is there any dangerous by doing such maintenance jobs (statistics update/index rebuild/index reorganize) which could damage the database if I use Ola with those parameters? The only thing I've seen where growing a large log file. Or can I install this with no worries and if anything occurs just cancel it (see question 1)?


texts: how can i write a trigger that compare between two tables in phpmyadmin mysql

texts:
I have two tables that has rows that have same values.

I want to write a trigger that will check for mypin in debtors tables and if mypin exists in debtors it fills the debtor row in the users table to have a value of 1 and if mypin does not exist in debtors table it fill the debtor row in users table to be 0.

debtors table
`debtor`, `myPIN`,

Users table
 `debtor`,`myPIN`

A db<>fiddle can be found here


texts:
Has anyone found a great way to convert individual rows to date ranges for a specific value. In this case, I'm looking to get a date range for each code in my given table.

Date        Code
1/1/2017    F
1/2/2017    F
1/3/2017    F
1/4/2017    R
1/5/2017    P
1/6/2017    P
1/7/2017    P
1/8/2017    P

My result should be similar to the following.

From        To          Code
1/1/2017    1/3/2017    F
1/4/2017    1/4/2017    R
1/5/2017    1/8/2017    P


texts: How to export all the security-related information from a SQL Server database

texts:
As you guys probably know, SQL Server does not provide an out-of-the-box solution to export all the security related statements that are declared for each element in the database (both at the database level and also the object level)

I'm talking about the ability to come up with this information

All the users
All the user-defined roles
All permissions at database level (e.g. GRANT CREATE FUNCTION)
All permissions at the object level (e.g. GRANT SELECT ON OBJECT::xxx)

You would think that SQL Server MUST have something like this but neither the SQL Server Export Wizard or the various scripts that are generated as a result of right clicking the objects do capture this information.

I have seen online many different "possible solutions" using non-curated scripts that people graciously post, but since I have to be 100% sure that all security information is captured I can't fully rely on those scripts.

I have the option of using those as a starting point to write something myself but I hate having to re-invent the wheel for a requirement that you would think many people may have.

Isn't there a tool provided by someone (either as part of the SQL Server product or an 3rd party tool) that could reliably provide you with this information?

Or at least, is there a community supported script that most of people would agree "will do the job"?

Thanks!


texts:
All,

My database has several years worth of temporal data that I am currently partitioning my the month. The problem is that some months there are hundreds of millions of records while other months only have a handful. What I would like to do is partition based on the number of records in the partition table so that each partition is equal. My question is, how would one go about doing this using a dynamic trigger function or maybe even a rule of some sort?

Thanks,
Adam


texts:
Trying to run an update, but I want to step through it as it's quite a bit of data and don't want to blast it all at once.

In oracle it's a bit easier to select the rows I want because you can include it in the WHERE clause, but T-SQL you include it in the select. I'm wanting to write it something along the lines of this:

Update Instances
set thing = 'new thing'
Where rownum<500

Basically want to do 500 at a time, is this syntax possible in TSQL?


texts:
i can get right result using the where join , with next sql:

WITH fktable1 AS (
SELECT 2 AS id
)
,fktable2 AS (
SELECT 3 AS id
)
SELECT main.*
FROM
-- main data
    (
    VALUES
    (1, NULL, NULL),
    (2, 2, NULL),
    (3, NULL, 3),
    (4, 2, 3),
    (5, 4, NULL), -- wrong, not exist fkcol1 in the fktable1
    (6, NULL, 5) -- wrong, not exist fkcol2 in the fktable2
    ) AS main (col1, fkcol1, fkcol2)
    , fktable1, fktable2
WHERE
    (
        fktable1.id = main.fkcol1
        OR main.fkcol1 is NULL
    )
    AND (
        fktable2.id = main.fkcol2
        OR main.fkcol2 is NULL
    );

it give me the output:

 col1 | fkcol1 | fkcol2
------+--------+--------
    1 |        |
    2 |      2 |
    3 |        |      3
    4 |      2 |      3
(4 rows)

is there some way to get the same by table join statement?
i tried to find solution and read many exists question, but unsuccess.


texts:
A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday.  One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes.  Looking at dba_hist_sqlstat , its running the same SQL as before, but with a different plan.  The stats seem to have negatively affected it.  Because its a large query that touches a few dozen tables, they already decided that restoring the stats on the affected tables isn't an option.

I've got the old (better) and new (terrible) plans by executing:

select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('sqlid'));

But they're so different that its not a minor manual tweak to get them back.

Is there a way to force it back to the old plan, given that the database still has much of that information available to it?
(And we don't have the Tuning Pack.)


texts: With the recent release of MySQL 8, I can't seem to find any information about why version numbers 6 and 7 were skipped.  Anyone know?


texts: service broker queue index growing beyond control

texts:
We have a queue that was set up about ten years ago in 2008. We have been running it on an Azure VM Standard edition sql 2016 for about 3 years no with no problems.
In the last 3 months, with no changes to the code or platform, we are seeing MASSIVE issues. The service broker index has become massive. There are about 100 million pages and counting, even though there are only 400 rows of data. As a result the database has ballooned out from 50GB to about 400gb, which is causing massive issues downstream.
I am convinced we are not tidying up messages or there are poisoned messages in the queue somewhere, but nothing i have found online seems to help me figure out were to look first.
We have been through the code and recommendations and everything seems to be in order.
the query used to find out the number of pages and rows.
SELECT TOP 5
    SCHEMA_NAME(itab.schema_id) AS 'schema_name',
    itab.name AS 'internal_table_name',
    idx.name AS 'heap_or_index_name',
    p.*,
    au.*
FROM
    sys.internal_tables AS itab
JOIN sys.indexes AS idx
    --     JOIN to the heap or the clustered index
        ON itab.object_id = idx.object_id
           AND idx.index_id IN ( 0, 1 )
JOIN sys.partitions AS p
    ON p.object_id = idx.object_id
       AND p.index_id = idx.index_id
JOIN sys.allocation_units AS au
    --     IN_ROW_DATA (type 1) and ROW_OVERFLOW_DATA (type 3) => JOIN to partition's Hobt
--     else LOB_DATA (type 2) => JOIN to the partition ID itself.
        ON au.container_id = CASE au.type
                               WHEN 2 THEN p.partition_id
                               ELSE p.hobt_id
                             END
ORDER BY
    au.total_pages DESC

This is what we are seeing, no rows, massive number of pages.

It feels like its no longer removing pages, or there are a large number of messages in the table that dont show up anywhere. being able to see what is in the table when it claims to be empty would be useful.
This time 6 months ago when the rows were low, so were the pages used and total pages. In fact its clean everywhere but production.
UPDATE:
5 days after reducing traffic to it and a random reboot on a firewall server

without doing any changes its back to normal. last night 14 million pages, this morning 132. What is going on?


texts: Cumulative Sum over 12 months by Postgreql 10


texts: NT SERVICE\MSSQLSERVER cannot be found to add as user for file permissions

texts: I need to give the NT SERVICE\MSSQLSERVER account permissions on a folder to be able to move and rename some files. I'm getting an access denied error. From the SQL Configuration tool, the services is set up like the below image. What else has to be done to make this appear as a user account?


texts:
this query is working but not giving me the output I want.

I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But here I have always 'OK' as output in my updated table. Normally, I should have 'FAILED' for the row [AFUP] and 'OK' for the row [BICHATP].

Can you run it on your machine?

drop table [MyTable]
drop table [BICHATP]
drop table [AFUP]

CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');

DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] =
                    case when left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'' then ''FAILED''
                         else ''OK''
                    end
               from DB_SANOFI.dbo.' + @Tabs;

    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

Tables

    AFUP           BICHATP
------------     ------------
M006 | 1010      M000 | 1101
M054 | 1000      M090 | 1001
M015 | 2324      M012 | 1023

Expected result:

Tabs    | Check column
-----------------------
   UFAP | FAIL
BICHATP | OK


texts: Postgresql Union: Do not repeat based on 1 column only

texts:
UNION filters out duplicate entries while UNION ALL keeps duplicate. Similar to that scenario, Consider this anological case, I want to set union to check only single column duplicacy.
SELECT id, 1 AS category FROM users UNION SELECT id, 2 FROM users_2;

Here I want to reject all UNION entries from id, 2 and check duplicacy based only on id.
OUTCOME:
id   |   category
------------------
100  |    1
101  |    1
...  |    ...
100  |    2   # Skip this as 100 is already present
201  |    2
....

EXPECTED:
id   | category
----------------
100  |   1
101  |   1
...  |  ...
201  |   2


texts: Using a filtered index when setting a variable

texts:
I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This means you can't use a variable in the where clause because sometimes it might be able to use the filtered index and sometimes not.
One way around this is to use OPTION(RECOMPILE), so that the times it can use it, it'll get the filtered index.
Doing some testing, I found that this query can use a filtered index (note, I'm forcing the index just to prove a point):
SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

However, if I want to assign the result to a variable, I'm bang out of luck:
SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

results in:

Msg 8622, Level 16, State 1, Line 15 Query processor could not produce
a query plan because of the hints defined in this query. Resubmit the
query without specifying any hints and without using SET FORCEPLAN.

The query can clearly use the filtered index as it runs find when I don't want to save the output to a variable.
I have ways of rewriting this query to hard-code @variable to remove the problem, but can someone explain why the first query can use the filtered index, but the 2nd query can't?


texts: MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online

texts:
I can take databases offline (via GUI) but can't bring them back online. The server details are as follow:

RDBMS: MS SQL 2016 Express
Host: Amazon AWS/RDS Free Tier

Details/History of the Problem
A few months ago, I created a db instance on Amazon AWS and at the time of creation, the 'master/admin' account was setup via the AWS/RDS web page. With this 'admin' account, I have created several databases on that instance without any problems.

Over the past few months,  I have used this 'admin' account to change several databases to contained databases. I do this so that I can setup contained users. I have also  done this several times on this server instance with the same admin account with no problems.

Last night, I had just created a new database via this admin account. I then tried to set this new database as a contained database and the process failed. The dialog box error message stated among other things "please try again later".

After the 3rd failed attempt, I decided to take the database offline (via the GUI in SSMS). I did this in a bid to force close any possible open processes or connections that might be on this new database. That worked. However, I have not been able to bring it back online. I have tried via the GUI and also via a query and it keeps failing.

I have then checked the server roles assigned to this 'admin' account. It is not part of sysadmin role. As I understand, the 'sysadmin' role can do absolutely anything on the db instance. I reckon my admin account is not of this sysadmin role because it is meant for the in-house DBAs at Amazon AWS. I have tried to add it as sysadmin but it fails.

To ensure that my 'admin' account is the problem, I have taken another database offline (it's empty). It went offline but it is also failing to come back online.

What could be the problem? Please help. Note that my skill level is very very low and I'm learning as I go along.

The server logs don't show anything useful. I have attached screenshots.

Error bringing database online in SSMS

Error looking at SQL Server error log from SSMS

Permissions for login

Error adding login to sysadmin role

1:


texts: column precision exceeded. ORA-01438 is so unmeaningful

texts:
I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not.

Let's suppose you have a Java batched prepared statement writing data to database. If one of the columns has a value larger than specified precision it throws ORA-01438 exception. Is there any way to find out which column is that?


texts: Select rows with same id but null and some other value in another column for that id

texts:
I want to get only rows having a value NULL and some other value than NULL for a particular username column.

If both rows have null for that particular username or both have some values other than null then it should not appear in output.  If there are more than two rows for same username with null and some other value then they should appear.

Below is example sample and output. How it can be done using sql query?

+----------+-------+
| username | col2  |
+----------+-------+
| a        | abc   |
| a        | ef    |
| b        | null  |
| b        | null  |
| c        | der   |
| c        | null  |
+----------+-------+

output

+----------+------+
| username | col2 |
+----------+------+
| c        | der  |
| c        | null |
+----------+------+


texts:
How do I merge two rows returned by a Group By statement:

    Select  SiteId,
            SiteName,
            EventCount = Count(*)
        From #MyLogs

        GROUP BY EventCode, SiteId, SiteName

returns this:

SiteId   SiteName       EventCount
 1      Abbottabad         53
 1      Abbottabad         52

How do I merge these results into one Row

The result I need is:

    SiteId   SiteName  |  OpenEvent            |        CloseEvent
                       | Count(Event = 43)     |     Count(Event = 44)
---------------------------------------------------------------------
      1     Abbottabad |  53                   |          52

I have posted the sample data and query here: Link


texts: Oracle Network Encryption from SQL Developer

texts:
I'm configuring Oracle Network Encryption on my Amazon RDS Oracle SE2 12.1 instance per these instructions. I've got the RDS instance configured with the appropriate option group, enabling network encryption on the server using the NATIVE_NETWORK_ENCRYPTION option. How do I configure my SQL Developer client to require encrypted connections to the server?

The Oracle and Amazon docs mention updating the connection properties and/or sqlnet.ora, but I'm not sure where to do that for SQL Developer. Is it possible to configure the various SQLNET.*_CLIENT encryption settings directly in the connection string (jdbc url)? If not, can these properties be specified directly in the SQL Developer Connection properties somewhere, or is some external configuration needed?


texts:
I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN ... COMMIT statements.
This sounds silly, but getting rid of this requires a lot of changes and I cannot afford to do so. My assumption is this basically means a small overhead for each group of SELECTs (roundtrips between application and SQL Server for BEGIN TRAN and COMMIT).
I am wondering if there is more to this (extra locking?).
Question: Is there any side effect if select statements are wrapped in BEGIN TRAN ... COMMIT?


texts: Any workarounds for lack of primary key on partitioned tables?

texts:
I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not supported. Is there any workaround or suggestions for alternative ways of doing this anyone can suggest? Or should we use table inheritance instead.

Thanks.


texts: How to ensure a trigger is fired after variable number of inserts?

texts:
I have a 1:N relationship between two tables - let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to "foo" and then to "bar". I need to check whether percentages saved in "bar" sum up to 100% ("check_percentage" trigger) after execution of all the "check_bar" triggers (they check whether "bar" meets some criteria and discards it, including the percentage, if not) - there is one per "bar" table.

First I though of "check_percentage" as a trigger on "foo", but it is not a constraint trigger, so DEFERRABLE INITIALLY DEFERRED shouldn't work according to the manual, and I didn't find any other way how to postpone its execution. The only solution I already found was to trigger "check_percentage" on "bar" instead, after "check_bar". If I check the percentage after each insert and "check_bar" execution, I can easily find cases where there are more than 100%, but for less than 100% (this is more likely to cause the problem), I would need to ensure that this instance of "bar" is not the last. I can theoretically get this information, but it would require a lot of extra logic. Triggering "check_percentage" in the end of the transaction would be ideal, but I don't know how to achieve it.


texts: I'm using the SQL server export wizard to export data to text flat file or Access file. The wizard gets stuck at 92960/92945 rows of about 153000 thousands. Nothing happens. Only after hours it continues. This happens all the time. Any idea? I happens always around the same amount of rows, not depending of the total amount of rows. Thanks


texts: How to select mutiple values with like clause

texts:
SELECT *
from table
WHERE column LIKE "AAA%"
   OR column LIKE "BBB%"
   OR column LIKE "CCC%"

I have more than 10 values to select, so it is not appropriate to write like the above example. Is there any way to select possible values in one sentence? I am using MySQL.


texts: PostgreSQL: Restoring 1 file from a directory format dump

texts:
I've got a large'ish (> 700GB) PostgreSQL database.  I'm working on importing it into AWS RDS.  I realize there are modules that will cause problems, we're dealing with that separately, so for right now, I am just working on migrating the data to RDS.

I'm migrating to and from PostgreSQL 9.4.5 using pg_dump and pg_restore 9.4.5.

Several hours into the transfer (pg_dump to pg_restore with a directory format), I checked the log and realized there is one thing (possibly a table?) owned by the Postgres user I was unaware of.

I found the line in the toc.dat file that made the call and unpacked the .dat.gz file to find it only contains "\." (sans the quotes) in it.

So, my question is, short of stopping the transfer and starting over, is there a way to simply transfer that specific command and file once the import is completed?

Here are the relevant details.

The pg_dump command:

pg_dump -h localhost -p 5432 -d DBNAME -U USERNAME -F d -f /mnt/backup -j 7

The pg_restore command:

pg_restore --dbname=DBNAME --no-tablespaces --host=HOSTNAME --port=5432 --username=USERNAME --verbose -F d -j 6 /mnt/backup

The section from the toc.dat file.  I realize the directory format outputs in binary format, but I am hoping that the fact that this is identifiable means there is a way to go back and fix that one error.

^@^F^@^@^@public^A^A^@^@^@^@^H^@^@^@postgres^@^E^@^@^@false^@^C^@^@^@173^A^A^@^@^@^@    ^@^@^@10393.dat^@W<^@^@^@^A^@^@^@^@^A^@^@^@0^@^E^@^@^@26106^@^N^@^@^@search_name_66^@
^@^@^@TABLE DATA^@^C^@^@^@^@^@^@^@^@^@^@^@^@^@^@]^@^@^@COPY search_name_66 (place_id, search_rank, address_rank, name_vector, centroid) FROM stdin;

The actual error reported by pg_restore:

pg_restore: [archiver (db)] Error from TOC entry 10393; 0 16787 TABLE DATA spatial_ref_sys postgres


texts: Replicate to a secondary database that also allows edits

texts:
I have a client with a somewhat odd requirement. They want to be able to take a snapshot of their sales database that their accountants can then use once it's disconnected from the live database. That makes sense. But the accountants also want to be able to make edits to historical data in the secondary database, and have those edits retained the next time they take a snapshot.

I'm at a loss for how to do this. I could enable change tracking, then go through the change-tracking tables and reapply their changes after recreating the secondary database, but that sounds like it would quickly get messy.

Could I possibly use log shipping for this? They tell me that the data they would be editing in the secondary, historical database is unlikely to be touched in the primary database. But if there have been changes to the secondary database, will I still be able to restore transaction logs?

I'm really pretty clueless on how to proceed... Any advice would be appreciated!


texts: Unit of COST in an Oracle execution plan

texts:
Is there a unit for cost in an Oracle execution plan?

I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? What does this number stand for?

Is there any way I can deduce how many seconds or ms this operation is going to take to complete?


texts:
My backed up archivelog file format is 'al_%U'. During recovery it shows the following error:

RMAN-06054: media recovery requesting unknown log: thread 1 seq 17 lowscn 671075

How to determine which archivelog is necessary? In RMAN format I can't indicate thread and seq  yes(for simply finding archivelog during this error)?

I should have that archivelog... If I really have that archive log how to indicate it to my database?

Thank You a lot...

I wil be very glad if you provide me with some book that clearly explains backup and recovery strategy and examples(In Oracle)...

GREAT THANKS!!...

Partial Answer of my question:

If I type

RMAN> list backup;

It shows me the thread, Seq and Low SCN of the logfiles in the backup set :D great!!

But there is one missing archivelog backup, what to do? How to register?(I am not using Catalog!)


texts: Behavior of Oracle During Very Large Transactions

texts:
I understand that if a transaction is large enough to overflow all of the redo logs on an Oracle database, and the database is in ARCHIVELOG mode, the transaction will wait until the archive log file is written before it continues.

How does Oracle behave in situations where a transaction overflows the redo logs and the database is in NOARCHIVELOG mode?


texts:
Is there a way to monitor or track when a Database is being exported using the Export Data Tier Application?

The reason is our users require DBO permissions when working with a Database but we just want to monitor if they are not doing a complete Export of the Database.


texts:
Given two tables - users and games - and a pivot table - userGames, I'm trying to efficiently seed the userGames table For testing, so that each user has close to a set percentage of games.

The query I've come up with so far relies on cross joining users with games, adding a RAND() value for each row, and then selecting the rows where the random column is below a given threshold:

SET @percent := 30;

INSERT IGNORE INTO `userGames`(`id_user`,`id_game`)
SELECT `user`, `game`
FROM
(
    SELECT
        `u`.`id` AS `user`,
        `g`.`id` AS `game`,
        RAND() AS `rand`
    FROM `users` AS `u`
    CROSS JOIN `games` AS `g`
    HAVING `rand` < (@percent/100)
) AS `NEW`

The problem with this is that for a very high number of users (~500k) and games (~100) this can take a long time, I'm assuming mostly due to having to calculate RAND() for each row.

Is there a better way of randomly seeding a pivot table like this with a given percentage?

Also just out of curiosity, can this be done without the subquery, without also having to insert the rand field into userGames?


texts: (macOS/Docker) "Cannot bulk load. The file path does not exist or you don't have file access rights."

texts:
I am running SQL Server from a Docker container on macOS. All queries except for the loading of the csv file is getting executed. The column names etc are correct. Any ideas?

error:

  Cannot bulk load. The file "/Users/me/snp500-company-by-sector.csv" does not exist or you don't have file access rights.

bulk insert CompanyBySector
from '/Users/me/snp500-company-by-sector.csv'
with
(
    firstrow = 2,
    fieldterminator = ';',
    rowterminator = '\n',
    tablock
)
go


texts: Fast hamming distance queries in postgres

texts:
I have a large database (16M rows) containing perceptual hashes of images.

I'd like to be able to search for rows by hamming distance in a reasonable timeframe.

Currently, as far as I properly understand the issue, I think the best option here would be a custom SP-GiST implementation that implements a BK-Tree, but that seems like a lot of work, and I'm still fuzzy on the practical details of properly implementing a custom index. Calculating the hamming distance is tractable enough, and I do know C, though.

Basically, what is the appropriate approach here? I  need to be able to query for matches within a certain edit-distance of a hash. As I understand it, Levenshtein distance with strings of equal length is functionally hamming distance, so there is at least some existing support for what I want, though no clear way to create an index from it (remember, the value I'm querying for changes. I cannot pre-compute the distance from a fixed value, since that would only be useful for that one value).

The hashes are currently stored as a 64-char string containing the binary ASCII encoding of the hash (e.g. "10010101..."), but I can convert them to int64 easily enough. The real issue is I need to be able to query relatively fast.

It seems like it could be possible to achieve something along the lines of what I want with the pg_trgm, but I'm a bit unclear on how the trigram matching mechamism works (in particular, what does the similarity metric it returns actually represent? It looks kind of like edit-distance).

Insert performance is not critical (it's very computationally expensive to calculate the hashes for each row), so I primarily care about searching.


texts:
I have the following question regarding the allocation of disks or LUNs to ASM in Oracle, specifically, I have an Oracle 18c DB in Cluster and I have created several diskgroups, now, I need to increase the size of the data diskgroup to which I only present a LUN of say 300 GB, it turns out that I have realized that it is not enough because the data volume will increase considerably, and I need at least 2 tb at the moment, I would like to know that it is recommended in these cases, to present a LUN of 2 TB, several of 300 GB ...???
My storage is completely integrated by NVMe SSD disks.
Thank you very much


texts: Will changing a MySQL user's password break existing connections?

texts:
If I update a user's password or privileges in MySQL, how does this affect existing connections?  In particular, if I change the replication user password, will it break replication for connected slaves?

Edit: Assume I am using FLUSH PRIVILEGES after updating the information.


texts: How to Choose an SQL Database for Simple Data Preparation & Analytics

texts:
In the interests of not making this a shopping list: please advise on "how" to choose a the right tool, as opposed to which is the best. E.g:

Which questions should I be asking myself / client?
What features should I be looking for?

Draft use case:

easy setup / installation + easy to use
size: 5-10gb database with 10-30 tables of varying sizes
users: handful of beginner-intermediate SQL analysts
load: 10-20 short-ish SQL scripts per day
usually one user per table at any one time
aspiration that SQL scripts should not take long to run
'fast' and 'flexible' more important than 'safe'
abiliy to import/export from/to CSV
low cost(!)

Essentially, we are a small team of (somewhat) geographically distributed data analysts looking for platform to do data preparation - we are not app developers. Data is exported for analysis in MS Excel.

Options considered so far:

MySQL with phpMyAdmin on WAMP on my 3-yr old laptop, which was OK
for one user but slow for some queries on larger datasets
Google Cloud SQL - I have set up Tier D0 instance and connected
phpMyAdmin from Compute Engine but found it slower than my desktop
so far. Wasn't so easy to set up.
Visual Foxpro - in the past with similar issues to (1) but more flexible; expensive.
MS Access - some use but risk of 2gb size constraint
Valentina Studio with SQLite - still testing; documentation confusing

What suggestions does the community have? Particularly intrested in comments on:

Desktop vs. Server vs. Web/Cloud (cloud seems like overkill, but option to access database anywhere is attractive)
SQL vs. other approach / language (SQL is easy to learn)

What other questions should I be asking but am not?

Thanks in advance for your thoughts.


texts: Permission denied to create "pg_catalog.tablename" during pg_dump targetting another machine

texts:
I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard:

pg_dump -t table dbname | psql -h hostname -d dbname

I am using the Postgres user, which I realize is probably not ideal, but as these are both dev data processing boxes that only I use and it is relevant to the error which follows. Initially, I got the error

  ERROR:  unrecognized configuration parameter "row_security"

which is expected as this feature is new in 9.5, and, yes, I realize that it is not advisable to pg_dump between different Postgres versions, but, sadly unavoidable, given that I have hit a very obscure bug, and need to move a large amount of static data once.

So, using this hideous hack to remove the row_security error, and turning on stop on first error:

pg_dump -t tablemame dbname |sed 's/SET row_security = off;//'|
psql -v ON_ERROR_STOP=1 -h hostname -d dbname

I now get:

  ERROR:  permission denied to create "pg_catalog.tablename"
  DETAIL:  System catalog modifications are currently disallowed

While it may be undesirable to use the Postgres user/role, my understanding is that there should be no permission issues of this nature. I am open to any suggestions bearing in mind that this is a one off operation and that these are data processing dev boxes not connected to any live services, so expediency preferred over ideal corporate data access policies. Having said that, it would be good to understand how to do this properly and avoid this in future.


texts: Cost threshold for parallelism and wait stats

texts:
We have an Stored Proc on our prod server (SQL Server 2014) that takes 3hr 40m (on a Sunday: low server activity) to run (collection job costs on 220 jobs with each requiring 6 SP calls, so 13200 SP calls and then some aggregating)

On my laptop with cost threshold set to default of 5 it runs in 30 mins. If I set cost threshold to 50 on my laptop it goes to 1hr 10mins (1/3 of time it takes in prod server)

If i set cost threshold on prod server to 25 it drops to 1hr 30mins, but wait stat go through the roof. See attached: green is threshold of 50, yellow is threshold of 25

Top waits are CXPACKET and SOS_Scheduler_Yield

I don't have a comparable dev or staging server (CPU's, RAM and Disk), so I play too much with a bunch of settings while i replay a workload

Tow questions

Am I better off with a cost threshold of 25 and the high wait stats and 60% performanc gain. Or will the wait stats cause a lot of issues when the load increases
Any thoughts on where the bottleneck is and how to solve. my uneducated guess would be CPU (from the blue line in the charts, it's not too high (under 70%) but is it that it can't serve enough threads in parallel?


texts: SQL Server 2008 - get current LOCK_ESCALATION on particular table

texts: Is there a way to find the current setting for LOCK_ESCALATION on particular table? Can it be found through system views?


texts: how to nest query with avg and sum group by outer in MySQL 5.7

texts:
I have a dataset like this:
tenant_code app_id reg_count
  1          1       0
  1          2       0
  2          1       1
  2          2       1
  3          1       5
  3          2       5

and now I want to group by tenant_code in the outer query and sum reg_count,but the reg_count should avg app_id in the inner query first and next sum reg_count in outer, what should I do to make it work? This is the query now I am using but did not work:
select
    sum(
        select avg(b.reg_count)
        from h_tenant_overview b
        where b.tenant_code = a.tenant_code
        group by b.tenant_code,b.app_id
    ) as reg_count
from h_tenant_overview a
group by tenant_code

the result should look like this if success:
tenant_code  reg_count
  1                 0
  2                 1
  3                 5


texts: MySQL - is there a way to implement a CHECK constraint after creating the table?

texts:
I created a table and after entering all the data, I found that I had missed a CHECK constraint in the CREATE TABLE statement.

create table jims_faculty
( fac_id int primary key,
fac_name varchar(15) not null,
fac_address varchar(40),
fac_contact_no int,
fac_qualification varchar(30) default 'post graduate',
fac_designation varchar(30) default 'assistant professor',
Fac_dept varchar(15)  check(fac_dept in ('it' , 'mgmt')) ,
fac_experience int ,
fac_date_of_joining date not null,
fac_aadhar int unique);
fac_aadhar int unique);

The fac_experience field should not be less than 2 years is the condition that should be in the check constraint.

Should I recreate the table?

Or is there another possibility ?


texts: I have a simple SSIS package with a ForEach Loop Container with 1 Data Flow task that triggers when a file is present. If that task succeeds, I want to move the input file to a "success" folder. If it fails, I want to move the file to an "error" folder. I am not seeing an error (red arrow) from the data flow task. How do I access the error condition for the data flow task?


texts: Fastest mechanism to move data between tables in SQL server

texts:
We are building a data warehouse. For some of our target tables, the only way to manage the process correctly is to re-transform all the data every time something changes.

Currently we do this using a 'table swap', ie:

Create new data in temp schema 'B'
Open transaction
Move A.Table to schema C
Move B.Table to schema A
Move C.Table to schema B
Commit

In theory this is all instant, because SQL never needs to physically copy any data. However due to the schema locks that get acquired, we are getting terrible concurrency.

Is there any other way of bulk moving data between tables (move, not copy), or do we have to fall back to doing an INSERT INTO (which might be bulk logged, but will still cause all the data to be written to disk again, which is slow)

The tables in question are several GB, hence the performance issues

SQL 2012


texts: I can't seem to find the same level of granularity for SQL Server restores that we can for backups?  I see the restore start date/time in msdb.dbo.restorehistory but not start and stop date and times.  Does it exist?


texts:
I have a table like this that keeps a total score of a history exam for students.

+----+---------------+-------+---------+-
| id | name          | history |  rank  |
+----+---------------+-------+---------+-
|  1 | yngiid        |    97   |        |
|  2 | viyrp         |   217   |        |
|  3 | pae           |   599   |        |
|  4 | spohl         |   284   |        |
|  5 | shl           |   295   |        |
|  6 | okeer         |    73   |        |
|  7 | jmaany        |   657   |        |
|  8 | hxt           |    80   |        |
|  9 | yanier        |   599   |        |
+----+---------------+-------+----------+-

The history field as you can see contains the total score the student . Now, I would like to rank the students by their scores as in, the higher the points the lower the rank.

Expected output.

+----+---------------+-------+----------+-
| id | name          | history |  rank  |
+----+---------------+-------+----------+-
|  7 | jmaany        |   657   |   1    |
|  3 | pae           |   599   |   2    |
|  9 | yanier        |   599   |   2    |
|  5 | shl           |   295   |   3    |
|  4 | spohl         |   284   |   4    |
|  2 | viyrp         |   217   |   5    |
|  1 | yngiid        |    97   |   6    |
|  8 | hxt           |    80   |   7    |
|  6 | okeer         |    73   |   8    |
+----+---------------+---------+--------+-

I have looked at answers in stackoverflow  but the answers have some issues like:

In the below example, methods will increment the rank when users have the same score, as in the example of pae and yanier

|  7 | jmaany        |   657   |   1    |
|  3 | pae           |   599   |   2    |
|  9 | yanier        |   599   |   3    | # shold be 2 bc pae&yanier have equal points
|  5 | shl           |   295   |   4    |

Other solution will fix the above problem but skip the the next rank number if scores are the same example:

|  7 | jmaany        |   657   |   1    |
|  3 | pae           |   599   |   2    |
|  9 | yanier        |   599   |   2    |
|  5 | shl           |   295   |   4    | # should be 3 b/c .. logic

These are just some of the problems, so I am wandering if there is a solution to output the expected result.

Here is a sample table dump if it helps.

create table `test` (
    `id` int (11),
    `name` varchar (765),
    `history` int (11),
    `rank` int (11)
);
insert into `test` (`id`, `name`, `history`, `rank`) values('1','yngiid','97',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('2','viyrp','217',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('3','pae','599',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('4','spohl','284',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('5','shl','295',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('6','okeer','73',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('7','jmaany','657',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('8','hxt','80',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('9','yanier','599',NULL);


texts: Dropped connections, corrupted schema ... is MySQL sick?

texts:
Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are:

Not being able to forward engineer said schema mysql -u root -p dbeq < dbeq.sql due to loosing connection with the server. 11kb file, 13 tables, each table has at most 13 columns, and the sql file is the output of MySQLWorkbench, so pretty much gauranteed to be syntax error-free. It does SET foreign_key_checks=0 at the start, but I've checked the file table by table; it creates them all in the correct order so that no mismatched foreign key  dependencies occur.
Frequent **ERROR 2013 (HY000) at line 47: Lost connection to MySQL server during query type messages, even when pasting CREATE TABLE statements at the mysql shell (which are 50ms queries at maximum). Max packet is 16Mb and write timeout is one minute, so those can't be the constraints.
The database becomes corrupted by the last table each time (can't create table because it exists, can't drop it because it doesn't).
MySQL workbench can't forward engineer directly to the database either; the connection to the MySQL server gets dropped in under a second.

Do I have something wrong with MySQL? I'm going to try creating the schema on a different machine at home (Unfortunately SQLFiddle is down so I haven't been able to test the schema there).

Output of mysqlcheck -u root -p dbeq (noting that it took 3 goes to create this many tables)

dbeq.AB_test_questions                             OK
dbeq.AB_test_responses_v0                          OK
dbeq.ABs                                           OK
dbeq.business_units                                OK
dbeq.customers                                     OK
dbeq.normalization_survey_customers
Error    : Table 'dbeq.normalization_survey_customers' doesn't exist
status   : Operation failed
dbeq.normalization_survey_questions                OK
dbeq.product_test_questions                        OK
dbeq.product_test_responses_v0                     OK
dbeq.products                                      OK
dbeq.project_managers                              OK
dbeq.survey_hashtable                              OK
dbeq.surveys                                       OK

Output of mysqldump -d dbeq

-- MySQL dump 10.13  Distrib 5.5.41, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: dbeq
-- ------------------------------------------------------
-- Server version   5.5.41-0ubuntu0.14.04.1

/*!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 */;
mysqldump: Got error: 1146: Table 'dbeq.normalization_survey_customers' doesn't exist when using LOCK TABLES


texts: Postgresql- how to rotate table partitions, trunc oldest every day?

texts:
I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition.

I know how to create table partitions on postgres, but I can't understand how to arrange daily rotation of several partitions.

I'm on postgresql 9.6, Amazon RDS instance which means no pg_cron to be able to run daily truncating.

I could write a python script and schedule it in cron on a side micro instance, but I'd preper to use internal postgres tools only.


texts:
I'm involve in a development of a tiny Social Network where users must be able to establish relations between them and also have permissions over contents. For a example: I add one user as my friend but this user doesn allow me to see all his/him contents so I only have access to those contents that user allow me (permissions). So I have a problem and need help designing the DER of this part. I think in have this tables:

- users (id, name)
- relations_type (id, name, active)
- users_relations (id, id_user_1, id_user_2)
- users_permissions (id, id_relation, id_module, id_user, view, edit, delete)

This cause the following:

Two rows for every relationship: User 1 > User 2 and User 2 > User 1 because when I search (SELECT) I need to know which are User 1 friends and also which are User2 friends. If I leave only one way relationship then I need a UNION and this migth slow  my DB

Is that correct? How yours handle this when CRUD on this? I'm using MySQL by the way and MyISAM tables.

Cheers and thanks in advance


texts: Tool for quickly backup and restore a table

texts:
Is there a tool for quickly backup and restore a table's data, preferably to a csv file?

It would be best if I could do it using batch. Run a .cmd file and data is backed up into a file with datetime name. And anytime needed I can just run another .cmd file to restore a file into the table.


texts: Restore data from an old rman backup - ORA-01152

texts:
I have some trouble restoring some data from an old backup.

Original machine was lost and my mission is to restore data. This is not a critical machine, but some data is still useful.

Oracle on original server: 11.2.0.2
Oracle on destination server: 11.2.0.4

Server is redhat- 5.6 original. 6.x destination.

On the new machine, with oracle installed I did:

export ORACLE_SID=oldsid
rman TARGET /

And in rman i set the sid as the old databsae:

SET DBID 3348161342;

Then restored controlfile and spfile from the backup. Important note: these files are more recent than the backup.

RESTORE CONTROLFILE from '/mount/restorepoint/3348161342_41qtltn4_1_1_20160111.bck
restore spfile from '/mount/restorepoint/sp_OLDDB-3348161342_42qtltn6_1_1_20160111.bck';

Database in mount with:

ALTER DATABASE MOUNT;

And specified where the backup files are:

catalog start with '/mount/restorepoint/rmanfiles';

Because on the original db there is also TDE.

Created a directory to put wallet
Created sqlnet.ora files with indications on the wallet location

Altered system with:

alter system set encryption wallet open identified by "thetdepassword";

And then again in rman:

alter database mount;
RESTORE DATABSE;

So far, everything fine. But when I trai to OPEN database:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Fine, my bad. Then I use: alter database open RESETLOGS; and I have the error:

ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/ltm/data/oracle/dati/olddb/users01.dbf'

What am I missing?
In the original backup script options must be:

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE

How can I read again that data?

I come from sqlserver and postgresql world, so probably I miss something...


texts: Error when modifying Notify Operator task in maintenance plan

texts:
I'm unable to modify a Notify Operator task in my new maintenance plan.
SQL Server Management Studio displays this error after double clicking on the task:

  Property IsMailHost is not available for database '[msdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access right.

The other tasks in the maintenance plan are working successfully.
I'm using SQL Server 2016 Standard (13.0.1601.5) and SQL Sever Management Studio 16.5.
The database mail is also configured with a public profile.


texts: Why is it slow: "SELECT * ... ORDER BY id LIMIT 50000, 2"

texts:
car_trims is an InnoDB table, has ~40 fields, average row length is 230 bytes, no TEXT or BLOB type columns

car_trims.id is PK

Query 1: .0007 seconds

SELECT * FROM car_trims ORDER BY id LIMIT 2

Query 2: .023 seconds

SELECT id FROM car_trims ORDER BY id LIMIT 50000, 2

Query 3: .09 seconds

SELECT * FROM car_trims ORDER BY id LIMIT 50000, 2

First off, I don't understand why query 2 is so slow, but it's marginally acceptable. What I really don't get is why query 3 is taking nearly 100ms to read the row data using the primary key.

My understanding is that the database should be retrieving the PK from memory to locate the row on disk and then read it out. Query 3 shouldn't be taking much longer than query 1.

EXPLAIN

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
 1       SIMPLE     car_trims     NULL  index   NULL            PRIMARY       4     NULL    50002   100.00      NULL

my.cnf

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#
# * Query Cache Configuration
#
query_cache_type=0
#query_cache_limit  = 1M
#query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

# Custom Stuff
performance-schema=0
event_scheduler=ON
slow-query-log=1
long-query-time=1
max_user_connections=1000
max_connections=1100
table_open_cache=8192
key_buffer_size=64M #myisam table index buffer
max_connect_errors=20
max_allowed_packet=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
myisam_sort_buffer_size=64M
max_heap_table_size=256M
tmp_table_size=256M
thread_cache_size=100

concurrent_insert=2
innodb_buffer_pool_size=1024M
innodb_buffer_pool_instances=8
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=32M            #see http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
innodb_old_blocks_time=1000
innodb_stats_on_metadata=off
innodb_log_buffer_size=16M
innodb_file_per_table=1
open_files_limit=10000


texts: RMAN Restore Validate time equals to actual restore time?

texts: I'd like to know if the time to restore the database is equal the time using restore validate database. If not, How can I know how much time the restore will spend?


texts:
I have restored a dump into MySQL.  I also have a binary log, which I want to partially restore, let's say from position 10 - 2500.

How can I do that?


texts: Insert new row into table after performing checks in other tables

texts:
I want to insert a new row in a table only if a value of the tuple I want to insert exists in another table. I want to perform that check for more than one value.

Let me show you an example to better understand what I want to do:

I have a table called personal_transaction. I want to perform the following insert:

INSERT INTO personal_transaction
(id, type, date, fund, paid, caid)
VALUES (3, credit, `2016-12-28`, 30, 2, 4)

Before inserting the tuple into the table, I want to check if 4 (=caid) is a valid key in table called commercial_account and also check if credit is greater than or equal to 30 (=fund) in the table called personal_account where id = 2 (=paid)

How can I do this?

personal_transaction has 2 columns paid and caid which are foreign keys to personal_account (column aid) and commercial_account (column aid).

For better understanding these are the checks I want to do before inserting:

SELECT * FROM personal_account
WHERE aid = 2 AND credit >= 30;

SELECT * FROM commercial_account
WHERE aid = 4;

If those selects both produce a row each as a result then the following query:

INSERT INTO personal_transaction
(type, date, fund, paid, caid)
VALUES
(credit, `2016-12-29`, 30, 2, 4)

is clear to be done, otherwise no insert occurs.


texts: Question about the workflow/design process/tools used by professional database designers

texts:
I hope this in the right place, as it's really more a question about database design software than the philosophy and practise of database administration. I'm a masters student doing an "introduction to databases" subject; the course leans more towards giving us a grounding in theory, less towards discussing how things are "really done". Working in Oracle SQL developer, we started off learning how to create databases by typing out schema files. Subsequently, we were introduced to the modelling tools in SQL developer, and shown how a logical model can be transformed ultimately into a set of schema files, or even just converted straight into a data dictionary.

While we were working in the 'raw' schema files, the general impression given was that this was how databases were designed by professionals, and that GUI tools were more suited to 'DIY' databases (e.g filemaker). Having seen how powerful (and time-saving) the GUI tools in SQL developer are, I'm not sure I believe this 100%.

The question I'm asking is: If a professional is building a new database from scratch, do they generally design and model, and then manually construct the schema files to implement? Or do designers/teams use tools like SQL developer from the beginning of the project to the end, using the software to design and implement the database?


texts: What is the proper event in Extended Events to track stored procedure executions?

texts:
My goal is to track executions of a single stored procedure using Extended Events

In the event selection list, I can find following:

sql_statement_completed
sql_batch_completed
sp_statement_completed
rpc_completed

Which one should I chose ?
I only need a distinct execution count


texts: How to get psql to prompt for a password with the superuser postgres?

texts:
I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:

psql -U myusername

it asks for a password just fine and logs in.
However if I do:

psql -U postgres

it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:

psql -U postgres -W

then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.

I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.

The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser?  It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.


texts: Identifying elements in SSIS package built in MS Visual Studio 2008

texts:
I recognize that these are variables, but I am not sure how to add these elements to the package (or how they are added to begin with), what effect that have or even how to manipulate them. No right click, no left click. I can hover the mouse over the element and the tooltip displays the variable name.
I can add variables via right click > Edit > Parameter mapping...however that does not display the little icon on the various control flow elements.

I am trying to rebuild this SSIS package. I have very little experience with MS Visual Studio/SSIS/SQL. Thanks for any input.


texts:
My Google Searching skills were not enough to find a solution to this. If possible, how can I promote a partition on a table to a table on its own?
Let's assume I have part0, part1, and part2. Is there any ALTER TABLE that I can give that converts part0 to a new table?
My thought process was, since the data is already existing in a separate file on disk, the table creation process may be faster than creating a new table using a SELECT query.


texts:
Getting error while service restart.

  [ERROR] InnoDB: A parallel doublewrite file /test/mysql/xb_doublewrite found on startup.


texts: Oracle: Recover source code/table structures from "utterly wiped out" development database

texts: Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain (and even then, just as hot backup of disk taken while database was open; not as database/RMAN backup), is there any way to open a "skeletal" version of the database such that source code and table schemas can be retrieved?  No concerns whatsoever for data recovery/integrity/etc; just want to retrieve whatever data dictionary contents that are possible (given these scraps of the original).


texts:
Some developers use SSMS 2016 with db_owner permissions to database(s) on a shared SQL Server, but error logs show an attempted login to the first user database on the instance, which they don't have access to. The log error is the typical 18456, but the user didn't try to connect to that database (I watched). Also, SSMS clocked for about 5-10 seconds when trying to expand the databases folder. After some time all the databases on the server were present. This hasn't been an issue using SSMS 2014. As a work around we granted 'connect to all databases', for the user which did stop the errors in the log and removed the delay when expanding the database folders, but doesn't seem like the long term solution.

Does this sound like a SSMS 2016 bug or user permissions? Any thoughts?

Thanks in advance,

TJ

Default db is set to the user database with db_owner permissions. This has been tested on different servers with the same login error result, and login was to the first user database. This is a AD user.
Error: 18456, Severity: 14, State: 38


texts: mongo DB The disk space completelly filled for replicate instance

texts:
I am currently facing a scenario that one of the replicate server disk got completely filled up, while our primary server still has additional space left.

My confusion is that the cmd like db.compact and db.repairDatabase() has to be run on primary server or I need to run it on replicate server instance?

Along as I have no disk space left, would it be appropriate to  remove it, as a replicate using db.config , delete the data files and add it again.

Need your help?


texts: Pause and resume execution of stored procedure

texts:
There are two processes(p1, p2) that may run simultaneously. p2 is a scheduled execution of SPROC while p1 consists of a group of stored procedures that is triggered on the request.
Execution of p1 while p2 is in progress can create issues. Only one should run at a time.
There are three ways to solve this problem

When p1 starts check whether p2 is in progress and wait until it completes. p2 can run for more than a day and it might not be a preferable solution
Kill p2, complete p1 and then restart p2. Killing and restarting p2 isn't safe due to nature of the sproc
pause p2 and resume p2 when p1 completes

Questions
1. How can I search and stop store procedure being executed?
2. Is there any way I can pause and resume a stored procedure that is being executed?


texts: SQL Database mirroring \ replication capabilities?

texts:
I've looked through the similar questions, but they are a little further down the line, more into details already.

While I'm still researching the concept of mirroring and what can and can not be achieved.

So I wanted to ask an opinion\suggestion\direction:

We have a web app which is backed by an SQL DB (2014)
We also have remote (far..) office which uses that web app
The remote office uses our local HQ web server so the network delay is getting on their nerves

Ideally we would like to replicated the whole app environment (web server + db) to their region and have a "live sync" between these two environments.

Within the app itself - there is no provisioned functionality for this.

So I am looking into default SQL toolset or other 3-rd party solutions maybe which could help us set a mirror of the main DB in the remote region with live synchronization (if possible). We can survive a data replication delay (user in region 1 creates something while it gets to the region 2 within next 5 minutes).

It's fairly common situation and I want to understand if this can be achieved by SQL tools themselves or with some other 3rd party solution which will manage the replication\syncing?


texts: Restore a SQL Server database to an offline copy

texts:
I have SQL Server Management Studio. I need to access a backup file (*.bak).

How do I create a new database engine that is OFFLINE (running from my PC not the server) and then restore the database to that offline database engine?


texts:
I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014.  However, when I run msdb.dbo.sp_syscollector_update_collection_item on SQL 2012, I get this error:

  Msg 14696, Level 16, State 1, Procedure
  sp_syscollector_update_collection_item, Line 70  Cannot update or
  delete a system collection set, or add new collection items to it.

Looking at the code of msdb.dbo.sp_syscollector_update_collection_item, I see differences between the SQL versions:

SQL 2008 R2/SQL 2014

IF (@is_system = 1 AND (@new_name IS NOT NULL))
BEGIN
    -- cannot update, delete, or add new collection items to a system collection set
    RAISERROR(14696, -1, -1);
    RETURN (1)
END

SQL 2012

IF (@is_system = 1 AND (@new_name IS NOT NULL OR @parameters IS NOT NULL))
BEGIN
    -- cannot update, delete, or add new collection items to a system collection set
    RAISERROR(14696, -1, -1);
    RETURN (1)
END

I'm passing in a non-NULL value for @parameters, so I fully understand why I'm getting the error on SQL 2012.

Questions:

Why is the code different for SQL 2012?
Is it safe to manually change the code for msdb.dbo.sp_syscollector_update_collection_item?

Update: (version numbers for further clarification)

Ver     SP  Build Number
--------------------------
2008 R2 SP3 10.50.6000.34
2012    SP2 11.0.5058.0
2014    SP1 12.0.4100.1

Update2:
I've updated SQL 2012 to SP3 (11.0.6020.0)
The issue persists.


texts: How to restore mysqldump of one database in different databaseName with content of data in the same host machine

texts:
Let's start from the beginning there is a MySQL database with name DB1. As I want to restore the mysqldump backup of DB1 database in DB2. The DB2 database is another database in same Host Server machine.

As I have been restored the DB1 mysqldump backup file to DB2 database through (MySQL Workbench as well as SQLyog Ultimate Tool). The restoration went through successfully restored. But there is no Any tables,Views,Stored Procs,Functions,Triggers and event tables or data present/appears in DB2 database. I have also gone through Restore mysql database with different name but somewhere bed luck.

My Environment is as follows:

Operating system version  :   Microsoft Windows Server 2012 R2 Stanadard
Handware information      :   VMware, Inc. VMware Virtual Platform
Processors                :   Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60 GHz
Intalled memory (RAM)     :   24 GB
Installed MySQL Server version : Server version 5.7.24 community edition
The DEFAULT CHARACTER SET of database : utf8
The Backup of DB1 database is 26 GB.

  Note: As I want to all content of database of DB1 in DB2 database.

My question is that how to get DB1 database all (tables,Views,Stored Procs,Functions,Triggers and event tables) data in DB2 in the same host machine.


texts:
I have two tables: customer and boxes in one-to-many relationship.
The structure of the tables is in addition :

Customer table :   id#| name | date_purchased | address |
                    1    John     2012-9-10       N/A

Boxes table:   id#| type | quantity |
                1    big      4

The id of the boxes table is foreign key to the customer table.

What if John has purchased more than 4 big boxes ? For example: he has ordered 2small boxes as well. How to add this to the database design schema?


texts: Using NULL values for foreign key with normalization

texts:
I am creating a table as follows:

Building (PK: buildingID)
Area (PK: areaID, FK: buildingID)
Room (PK: roomID, FK: buildingID, FK: areaID)

All rooms are in a building. Some rooms can be in an area of a building. Rooms that are not in an area of a building have the FK: areaID set to NULL.

For example, areas of a building include: blue zone, green zone, red zone. Rooms can be in one of those areas, but may not be in an area and just part of the building itself.

Is this properly normalized? I feel that when the FK: areaID is set to a value then it is not normalized as the buildingID can be determined from the areaID.

If it is not normalized, how do I go about normalizing it so that the FK: areaID can still use NULL values?


texts:
As part of a MERGE query that I want to run I'd like to assert at runtime that a certain condition holds. When a MERGE-match is found I'd like to update a certain column and have the following logic executed:

If the target column is NULL, write the source value
If the target is NOT NULL, assert that target and source are identical

I expect that the two values are always identical in case 2 but I might have made a mistake (have a bug). When that happens I'd like to crash the statement and let my app report the error. This is a very rare error condition, not something that can happen as part of usual processing.

So I was thinking that I could abuse a divide-by-zero exception to trigger a crash:

MERGE
...
WHEN MATCHED BY TARGET THEN UPDATE SET
  TargetCol = CASE
    WHEN TargetCol IS NULL THEN SourceCol
    WHEN TargetCol = SourceCol THEN SourceCol
    ELSE 0/0 END --crash!

Will this work reliably? Is there a reason this should not be done?