texts: Fill in the gaps between rows using the previous row's values

texts:
I have a road_insp table:

create table road_insp
   (
    insp_id int,
    road_id int,
    insp_date date,
    condition number(38,2)
   ) ;

insert into road_insp (insp_id, road_id, insp_date, condition) values (1,100,to_date('01-APR-04','DD-MON-YY'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (2,100,to_date('01-APR-11','DD-MON-YY'),.7);
insert into road_insp (insp_id, road_id, insp_date, condition) values (3,100,to_date('01-MAR-12','DD-MON-YY'),.7);
insert into road_insp (insp_id, road_id, insp_date, condition) values (4,100,to_date('01-MAR-17','DD-MON-YY'),.6);
insert into road_insp (insp_id, road_id, insp_date, condition) values (5,100,to_date('01-MAR-18','DD-MON-YY'),.6);

insert into road_insp (insp_id, road_id, insp_date, condition) values (6,200,to_date('01-JUN-10','DD-MON-YY'),.4);
insert into road_insp (insp_id, road_id, insp_date, condition) values (7,200,to_date('01-JUN-12','DD-MON-YY'),.3);

commit;

select
    insp_id,
    road_id,
    extract(year from insp_date) as insp_year,
    condition as condition
from
    road_insp
order by
    road_id,
    insp_date
;
   INSP_ID    ROAD_ID  INSP_YEAR  CONDITION
---------- ---------- ---------- ----------
         1        100       2004         .9
         2        100       2011 <-gap   .7
         3        100       2012         .7
         4        100       2017 <-gap   .6
         5        100       2018         .6

         6        200       2010         .4
         7        200       2012 <-gap   .3

I can visualize road condition over time like this (road #100):

However, as noted in red, there are time gaps between the inspections.

Inspections for road #100 are missing for these years:

2005
2006
2007
2008
2009
2010
2013
2014
2015
2016

The gaps exist because not all of the roads in the City are inspected every year. While I wouldn't consider the gaps to be errors in the data, I am concerned that when I visualize the data, the gaps paint a misleading picture. I say this, because, the time intervals between inspections are certainly not constant,
 yet when I look at the graph, I'm lead to believe that the time intervals are constant (which is not true).

To compensate for this quirk, I would like to fill in the gaps between inspections using a select query in a view. Using such a view, the data could be visualized like this:

+---------+---------+------------+-----------+
| INSP_ID | ROAD_ID | INSP_DATE  | CONDITION |
+---------+---------+------------+-----------+
|       1 |     100 | 2004-04-01 |        .9 |
|         |     100 | 2005-01-01 |        .9 |
|         |     100 | 2006-01-01 |        .9 |
|         |     100 | 2007-01-01 |        .9 |
|         |     100 | 2008-01-01 |        .9 |
|         |     100 | 2009-01-01 |        .9 |
|         |     100 | 2010-01-01 |        .9 |
|       2 |     100 | 2011-04-01 |        .7 |
|       3 |     100 | 2012-03-01 |        .7 |
|         |     100 | 2013-01-01 |        .7 |
|         |     100 | 2014-01-01 |        .7 |
|         |     100 | 2015-01-01 |        .7 |
|         |     100 | 2016-01-01 |        .7 |
|       4 |     100 | 2017-03-01 |        .6 |
|       5 |     100 | 2018-03-01 |        .6 |
+---------+---------+------------+-----------+
|       6 |     200 | 2010-06-01 |        .4 |
|         |     200 | 2011-01-01 |        .4 |
|       7 |     200 | 2012-06-01 |       .35 |
+---------+---------+------------+-----------+

As you can see, the time intervals are now constant and more intuitive to interpret.

The filler records would be populated like this:

insp_id would be null
road_id would be the id of the last completed inspection
insp_date would be January 1st of the applicable year
condition would be the condition of the last completed inspection


texts: Why use an int as a lookup table's primary key?

texts:
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).

I understand that using a nvarchar(50) rather than an int would use way more space if it is linked to a table with many records.

On the other hand, using the lookup value directly would basically save us doing a join. I can imagine this would be a big saving if the join is always required (We're working on a web app so this counts quite a bit).

What are the advantages of using a int primary key (specifically for a lookup table) other than it being "the standard thing to do"?


texts:
I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete

DELETE TOP (n) syntax, then loop until now more needs to be deleted

it seems we controlled the log size problem. however,  the performance downgraded a lot. for example, if I delete the data from the first table A in one batch (delete from ...), it takes about 9 minutes. Now, if I delete in a batch of 2000, after the whole night, it does not finish yet. If I get the # of rows that will be deleted, then batch delete 10% of it in each loop (so, about 200,000 for each batch), it takes about 40 minutes.

Is this normal? one thing I want to point out is: there is no primary key in these 2 tables, although they have non-cluster index. it was designed by previous developer who never thought we would have so much data to process. Now, we have to live with this, change database structure is not an option. Could it because of this that makes the delete slow?

Any suggest of what can cause the slow?


texts: What would be the correct size of redo logs?

texts:
I installed and put in production a 11g database. I was adviced to size redo logs to 200MB each one. Now I see that my archived redo logs are each one 200 MB and all of them occupy a lot os disk space. Now I would like to change that size to 100MB or 50MB.

My question is: what would be the OK size for a redolog, depending on what?


texts: How to give read only access to a user to view the tables of different owner in oracle

texts: I have 2 users are present in oracle ex User1 and User2, Under user1 some tables are present ex t1,t2,t3 etc.. . what are the minimum permissions to view the tables of user1 by using user2 ( Just want to view the table description not the Data) .


texts: SQLVDI error when backing up to Azure Storage BLOB

texts:
I'm running a patched SQL 2014 trying to backup a database to one of our Azure Storage BLOBs, using:

BACKUP DATABASE [DB]
TO URL = N'https://storage.blob.core.windows.net/server-mssqlserver/DB.bak'
WITH CREDENTIAL = N'AzureCredential'
    ,NOFORMAT
    ,NOINIT
    ,NAME = N'DBA_DB-Full Database Backup'
    ,NOSKIP
    ,NOREWIND
    ,NOUNLOAD
    ,COMPRESSION
    ,STATS = 5
GO

but the query throws the following error:

  Msg 3292, Level 16, State 9, Line 1
  A failure occurred while attempting to execute Backup or Restore with a URL device specified. Consult the Windows Event Log for details.
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

Checking the server's Event Logs shows the actual error as:

  SQLVDI: Loc=IdentifySQLServer. Desc=MSSQLSERVER. ErrorCode=(5)Access is denied.
  . Process=4668. Thread=6596. Client. Instance=MSSQLSERVER. VD=.

I have made sure that the SQL Server Agent service's account has the Create global objects policy, and also made sure the SQL VSS Writer service is running under the Local System account. The error keeps happening!

Is there something I can do to fix it, or just log some more detailed error messages than the "SQLVDI: Loc=IdentifySQLServer" one above?


texts: Install Only Pgbench and Not PostgreSQL Server

texts:
I'm looking to benchmark some custom PostgreSQL statements using pgbench but my problem with this is that upon trying to install pgbench it tries to install PostgreSQL server. The pgbench utility package is documented here

The machine in question is a production box (Ubuntu) and I have refrained from installing any extra libraries that are not needed.

Is there a way to only install pgbench without completely installing the PostgreSQL package which includes PostgreSQL server?


texts: Sql Server 2008 Replication "Generate Scripts" option not available

texts:
All,

On Sql Server 2008, I have Transaction Replication going from Server1 to Server3 (with Server2 as distribution).

I have an option to "Generate Scripts" on Publication on Server1. But I do not have that option to "Generate Scripts" for Subscription on Server3.

But on a different set of servers (Server6, Server7, Server8) with replication I have options to "Generate Scripts" on both Publication and Subscription.

Both are Sql Server 2008, but the later has Sql Serve 2008 R2 on it.

Could that be the reason? How do I get around it?

Thanks,
_UB

Edit:Grammar, and more grammar.


texts:
I am a noob to database design so bear with me. I have two tables in Microsoft Access:

Table 1:

| ID | Field1 | Field2 |
|:---|--------|:-------|
| 1  |   A    |    1   |
| 2  |   A    |    2   |
| 3  |   B    |    5   |
| 4  |   C    |    8   |
| 5  |   D    |   20   |
| 6  |   E    |   32   |
| 7  |   F    |   22   |
| 8  |   F    |   7    |

Table 2:

| ID | Field1 | Field2 |
|:---|--------|:-------|
| 1  |   A    |  one   |
| 2  |   B    |  two   |
| 3  |   B    | eight  |
| 4  |   D    | seven  |
| 5  |   D    | twenty |
| 6  |   F    | thirty |

I am trying to establish a one to many relationship between the tables based on Field1. However Field1 is not unique in either table. This is conceptually possible, however, because each record in Field1 does have a corresponding record in Field2. I could create an intermediate table containing just the ID's as such

| ID |   ID1  |  ID2   |
|:---|--------|:-------|
| 1  |   1    |   1    |
| 2  |   2    |   1    |
| 3  |   3    |   2    |
| 4  |   3    |   3    |
| 5  |   5    |   4    |
| 6  |   5    |   5    |
| 7  |   5    |   5    |
| 7  |   7    |   6    |
| 7  |   8    |   6    |

However this would be too tedious since I have thousands of records (above tables are just a simplified example). Is there a way in Access to relate these tables directly without having to worry about the ID's? I know I used to be able to do this in ArcGIS using the "Relate Tables" feature so hopefully theres a way to do it in Access?


texts: Moving SQL Server Database Files to New Location - Why permission error?

texts:
I was trying to move a database (.mdf and .ldf files) to a different directory on the same server hosting the SQL server.  I followed these steps found in this Q&A:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDB SET OFFLINE
ALTER DATABASE MyDB MODIFY FILE (Name = MyDB, Filename = 'N:\DATA\MyDB.MDF')
ALTER DATABASE MyDB SET ONLINE
ALTER DATABASE MyDB SET MULTI_USER

When I got to Step4, I got "Access Denied".  Unfortunately, my maintenance windows were very short and no time to troubleshoot.  So I decided to drop the database and restored it using the WITH MOVE clause to place the DB files in the right directory.  No issue.

My question is why did I get "Access Denied"?  I didn't change MSSQL service account.  It's the same account that ran the RESTORE.


texts: MySQL return rows ONLY if ALL the rows in another table have a specified value

texts:
I have two tables
Table1 :
efd_details( id )

Table2 :
efd_detail_status( detail_id  , status )

Now the data as following :
select * from efd_details;

Output :
ID
1
2
3
4
5

select * from efd_detail_status ;

Output :
DETAIL_ID | STATUS
1         |   's'
2         |   's'
3         |   's'
4         |   null
5         |   'f'

What I need to do is get all the details (efd_details) ONLY if ALL the statuses in efd_detail_status = 's'. In the example above, I would retrieve ZERO records since two of the status are != 's'. If ALL the status = 's' then return ALL rows from cms_efd.
I have tried variations of EXISTS and the following:
select e.*
  from cms.cms_efd e
where exists(select 1
               from cms.cms_efd_status s
              where s.detail_record_id = e.detail_record_id
                and IFNULL(s.contrib_calc_status,'f') != 's'
             having count(e.detail_record_id)=count(s.detail_record_id))
    and e.earnings_file_id = 400676;

Appreciate the help.
-Branden


texts: SQL Server not checking for expired passwords

texts:
We are using SQL Server 2005 on Windows Server 2008 with Windows Authentication where access is granted to members of a specific domain group.  Clients are VB6/ADO applications that connect via an ODBC System DSN.

I noticed recently that users are being granted access to SQL Server despite their password having expired.  For example, if a user's password expires at midday on a given work-day they are rightly allowed to login to their machine first thing in the morning but if they attempt to login to SQL Server (by running the client application) in the afternoon I would expect SQL Server to reject the connection.  This is not what we're seeing in practise though, the connection is still being allowed.  Other services such as Exchange reject connections under the same circumstances.

Is SQL Server caching credentials?  Is there a way to force SQL Server to check for password expiration?  Why would SQL Server behave differently than, for example, Exchange?


texts: How to model complex systems like networks in Postgres

texts:
I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented perspective, I can code this in my sleep in C++. Switches, for example, would inherit from TwentyFourSwitch, and if they have POE, then from POESwitch, then in each class I would create 24 EthernetPort instances, and each would have a connect function making sure not to get an ethernet port to an SFP port, or a non POE port to a POE device and so on.

With RDBMS, we keep running into problems. First we organized everything to minimize data redundancy. Here is a sample of what we would do (to keep code short I used 2 and 4 port switches instead of 24 and 48).

CREATE TABLE MACAddress{
   id SERIAL INT PRIMARY KEY,
   mac1 INT,
   mac2 INT,
   mac3 INT,
   mac4 INT,
   mac5 INT,
   mac6 INT,
   UNIQUE(mac1,mac2,mac3,mac4,mac5,mac6)
);

CREATE TABLE IPAddress{
   id SERIAL INT PRIMARY KEY,
   ip1 INT,
   ip2 INT,
   ip3 INT,
   ip4 INT,
   UNIQUE (ip1,ip2,ip3,ip4)
);

CREATE TABLE NetworkDevice{
   id SERIAL INT PRIMARY KEY,
   ip INT REFERENCES (IPAddress),
   mac INT REFERENCES (MACAddress),
);

CREATE TABLE TwoPortSwitch{
   id SERIAL INT PRIMARY KEY,
   eth0 INT REFERENCES (NetworkDevice),
   eth1 INT REFERENCES (NetworkDevice),
   netId REFERENCES (NetworkDevice)
);

CREATE TABLE FourPortSwitch{
   id SERIAL INT PRIMARY KEY,
   eth0 INT REFERENCES (NetworkDevice),
   eth1 INT REFERENCES (NetworkDevice),
   eth0 INT REFERENCES (NetworkDevice),
   eth1 INT REFERENCES (NetworkDevice),
   netId REFERENCES (NetworkDevice)
);

CREATE TABLE Camera{
   id SERIAL INT PRIMARY KEY,
   eth0 INT REFERENCES (NetworkDevice),
   netId REFERENCES (NetworkDevice)
);

But the problem we ran into was that it took many joins and unions and it got very confusing very fast. For example, if you want to know what ip eth0 is connected to, you have to do joins/unions on TwoPortSwitch, FourPortSwitch, Camera, NetworkDevice and IPAddress. And mind you we have about 10 different cameras, 10 different switches, and 5 different computers.

We just got fed up and flattened everything, so now all switches are in one big switch table. It has 48 ports of 1 gig ethernet, 48 ports of 1 gig SFP and 48 ports of 10 gig SFP Plus. All in all, there are more than 1000 columns!

Is there a better way to approach this? I am seriously considering building a webserver and having the C++ server run at all times.


texts:
I'm relatively new to Database Design and I wanted to know the disadvantages to a particular decision I have made in the design of a database for a project. I don't think it will have an effect on the answer, but I am using SQL Server 2008 R2.

Project:

We have things called Bids which contain Materials. Materials can be grouped so I plan on having a MaterialGroup table, However, Materials don't have to be apart of a group (they can be unassigned if you will). So, instead of making a link-table or making the foreign key nullable I decided to make the first group in the database called unassigned and instead of allowing Materials not be grouped I plan to place them in this group if not given a group.

Question: Are there any other disadvantages to expecting the first row of the table MaterialGroup to be unassigned (other than the case of someone failing to insert it there)? From both a practical and theoretical standpoint should I switch to a link-table (or another alternative)?


texts: "Data truncated" vs "Incorrect string value"

texts:
I'm trying to figure out the difference between some warning messages related to data truncation.  Consider the following table:

CREATE TABLE `txttest` (
     `mycol` text NOT NULL )
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Pretty much expected behavior:

mysql > insert into txttest (mycol) values (repeat('a',65535));
Query OK, 1 row affected (0.17 sec)

mysql > insert into txttest (mycol) values (repeat('a',65536));
Query OK, 1 row affected, 1 warning (0.16 sec)

mysql > show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'mycol' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

When I'm using multi byte characters however, the expected warning state occurs but the message is different:

mysql > insert into txttest (mycol) values (repeat('é',65536/2-1));
Query OK, 1 row affected (0.17 sec)

mysql > insert into txttest (mycol) values (repeat('é',65536/2));
Query OK, 1 row affected, 1 warning (0.16 sec)

mysql > show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xC3\xA9' for column 'mycol' at row 1 |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Google attempts aren't very useful as I'm mostly running across pastes of this message for different contexts.

Basically what I'm wanting to know is: Is this just simply the wording/error code Mysql uses when truncating a sequence of multibyte characters, or is there something more telling I should be gleening from this message?

I was initially thinking it meant the byte sequence was getting split in such away it was resulting in malformed characters.  Attempts to try and make it do this didn't work (e.g. mysql seemed good about recognizing proper byte boundaries for a character encoding).

Edit:

After relooking over it does look to be the char splitting I initially dismissed.  I had an off by one brain fart looking at it initially.


texts: Performance issue | MySQL Consumes more than 80%+ memory and 2000%+ CPU

texts:
Everyday during the peak time my server getting slow or down. Our hosting provider insisting us to upgrade the server but I think some performance tuning issue is there.

Adding the  process information, server configuration and my.cnf parameters below.

Process Information

 PID  USER       PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 60848 mysql     20   0 34.8g  23g 6416 S 2196.2 82.1  16027:29 mysqld

Dedicated Server Configuration

Size:   'Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz', 2600MHz, 6 Core, Sockets: 2
Image:  CentOS 6 64-bit with cPanel Fully-managed
CPU:    Intel Dual Xeon E5-2620 v3
Speed:  2600MHz
RAM:    32067MB
CPUs:   2 Physical CPUs
Cores:  12
Total Cores RAID:   Level 10
Disks:  4
Size:   917GB
Type:   SSD

MySQL Configuration

[mysqld]
slow_query_log = 1
#long_query_time = 2
long_query_time = 2
slow_query_log_file = /var/lib/mysql/vps-slow.log
performance-schema=0
max_connections = 250
max_allowed_packet=128M
#skip-external-locking
key_buffer_size = 256M
open_files_limit = 50000
table_open_cache = 15000
sort_buffer_size = 6M
join_buffer_size = 6M
read_buffer_size = 1M
read_rnd_buffer_size = 15M
myisam_sort_buffer_size = 64M
query_cache_type=1
query_cache_limit=2M
query_cache_size=128M
max_heap_table_size = 96M
tmp_table_size = 96M
#low_priority_updates=1

thread_cache_size = 32
wait_timeout = 300
connect_timeout=15
interactive_timeout = 800

innodb_buffer_pool_size=4096M
innodb_buffer_pool_instances = 4

group_concat_max_len=1000000

default-storage-engine=MyISAM
innodb_file_per_table=1

[mysqldump]
quick

Please help me if there is anything wrong with the configuration ?

Or

Do I really need to upgrade the server ?

PS: Hosted  web application in the same server. Also WHM enabled for easy management.


texts: What kind of hardware/software config should I look at for a MySQL database with 10,000 plus tables

texts:
I am looking at rolling out a CMS system that will require the creation of around 10,000 tables within the primary MySQL database of the system.

The database will be the data store for several hundred small website front ends that might draw a modest load of around 150k unique viewers per month, but this might have to scale on short notice.

I'm looking for some advice around what kind of hardware should be used to be cost effective but also to have the ability to scale if the need arises.
I would also like some advice around the software configuration: i.e. should the MySQL setup be clustered or just straight forward MySQL with a high number of open files?

Any advice will be greatly appreciated!


texts: Change database used for SQL Server Integration Services logging

texts:
The SQL Server Best Practices Analyzer noted that my Integration Services' logging table is in a system database. It recommends that I move this to a non-system database. (See here)

How do I make this change? I wasn't able to find a setting for this. I'm using SQL Server 2012.


texts: Postgres Partitioning: Edge case issue on date column partitioning

texts:
I am using Postgres 9.5

I have tables with date column. All tables are partitioned based on the date column.

Table setup:

Example of current partitioned tables are like below

ORDER (id, description, orderstatus, submitteddate) --> submitteddate default value is current date

ORDER_LINE (lineid, orderid, description, submitteddate) --> orderid is FK from ORDER(id)
(multiple order lines inside each order)

PLAN (planid, orderlineid, description, submitteddate) --> orderlineid is FK from ORDER_LINE(lineid)
(Plan generated for each order line)

Tables are partitioned like below

CREATE TABLE ORDER_NOV2016 (CHECK ( submitteddate < '2016-12-01' )) INHERITS (ORDER);
CREATE TABLE ORDER_DEC2016 (CHECK ( submitteddate < '2017-01-01' )) INHERITS (ORDER);

CREATE TABLE ORDER_LINE_NOV2016 (CHECK ( submitteddate < '2016-12-01' )) INHERITS (ORDER_LINE);
CREATE TABLE ORDER_LINE_DEC2016 (CHECK ( submitteddate < '2017-01-01' )) INHERITS (ORDER_LINE);

Failing Edge Case

Since partition for inserting data is decided based on submitteddate which is a current date, there will be a situation where an order comes at 2016-11-30 at 11:59PM and data in ORDER table is inserted in NOV2016 partition but data in ORDER_LINE and PLAN table is inserted on DEC2016 partition as by the time inserts are done, date may change in the system.

When I try to drop Nov2016 partition from all tables (child first due to FK constraint), ORDER_LINE and PLAN table drop partition might go through but ORDER table partition drop will fail as orderid from Nov2016 partition would be pointing to the DEC2016 partition data in other 2 tables.

How do I make sure that the orders inserted on date change still goes to same partition across all tables?

Added info (based on @dezso reply)

Dezso's transaction suggestions makes sense. But to make question concise, I left some details. With those details, the suggested solution might differ a bit.

Application supports 2 databases, Oracle and Postgres. For Oracle, partitioning has been implemented using Reference partitioning with partitioned ORDER table and child tables are referenced partitioned based on foreign keys.

For postgres, since there is no reference partitioning option like Oracle, each table was supposed to be individually partitioned using submitteddate. The plan was not to add submitteddate to each table but to use inheritance like below

ORDER_PARTITION(submitteddate date not null default CURRENT_DATE);
ORDER (id, description, orderstatus) INHERITS (order_partition);
ORDER_LINE (lineid, orderid, description) INHERITS (order_partition);
PLAN (planid, orderlineid, description)INHERITS (order_partition);

This way application will not insert submitteddate from the code to support Oracle table structure, instead for postgres its a default value which will get added automatically when a row is inserted in each partitioned table.

Therefore calculating single date at the beginning of the transaction would be difficult as this will require changes on Oracle side as well to add date column to each table.

Is there any other option that is possible?


texts: Reverse auto_increment id existing column in phpMyAdmin

texts:
I have imported csv file through phpMyAdmin into one table. I have set auto-increment field, as shown below, but of course it ordered it ascending:

+----+------------+------+
| id | date       | name |
+----+------------+------+
| 1  | 2015-02-28 | Mike |
| 2  | 2015-02-26 | Sara |
| 3  | 2015-02-22 | Luke |
+----+------------+------+

So now, when I add new user, it gives him id=4. But what I want is to reverse the order of the above table, to reverse id, so when I add new user, because of the date it will be ordered according to the date.

I cannot change the csv file, I need to do something in PhpMyAdmin to change the ids (auto-increment) - to reverse them.
It should look like this:

+----+------------+------+
| id | date       | name |
+----+------------+------+
| 1  | 2015-02-22 | Luke |
| 2  | 2015-02-26 | Sara |
| 3  | 2015-02-28 | Mike |
+----+------------+------+

How to achieve this? As I want when adding new user to give him the id=4, but the date will be todays date and in the whole table it will be sorted by date normally.


texts: Do we need to shutdown DB/Stop DB service before Windows server patching

texts:
Before patching Windows server, do we need to shut down any DB or stop DB service.

If we need to shutdown a MongoDB on a node(Windows server) which is part of a replica set, what we need to before patching windows server..

Thanks


texts:
I have a problem with my database. I installed PostgreSQL 9.5 on my Ubuntu server. I changed the postgresql.conf file to allow binding the PostgreSQL server to the localhost. This allows me to run pgAdmin and connect to my database by forwarding also the port 5432, where I run my PostgreSQL.

The problem I am experiencing is that I only see the default table 'postgres', but not my newly created one 'games'. I created this table by running postgres=# create database games; with the postgres user connected to the server. I know the database was created, because if I run postgres=# \l+ while connected to the server, I get a list of databases, including my 'games'.

In the second picture is my screenshot of the pgAdmin 9.5 application with all the property values I use to connect to my server. So I manage to connect to the server, but I am unable to see the database 'games'.

I also went a step further and granted all permissions to the postgres user by executing postgres=# grant all privileges on database games to postgres;. You can see in my third screenshot, that the access privileges changed. But this change did not do help me to solve the problem. I am still not able to see 'games' in pgAdmin. I saw someone had a similar problem and run the right click on the server and clicked 'New database'.

This seemed to created a new database, because as you can see from the pgAdmin, the application was able to find the score table I create inside pgAdmin. The reason I believe this is the case is, because running the same SQL connected to the server games=# select * from score; results in ERROR: relation "score" does not exist LINE 1: select * from score;.

Any other ideas where I went wrong. Would appreciate any help I can get.

I can log in to my database. When I run psql games postgres, this will log me into my games database with the postgres user. And this is also how I logged in and executed the select statement:

games=# select * from score;

  ERROR:  relation "score" does not exist
  LINE 1: select * from score;

I went a step further I printed out the tables we have in 'games':

games=# \dt

  No relations found.


texts:
I got dedicated disk (RAID 10 - 4 SAS disks) for Postgres. I am bit worried as recently, I see under Windows Resource Manager disk queues like 3+ in the mornings (low traffic hours), like every 30-60 seconds. I checked what queries are running, started logging queries 1000ms+ but I cannot see what is causing this traffic. It looks like transaction log is saving most data, but I've read xlog to another disk will only give max 20% performance gain. I was thinking about some triggers added recently, but I am not sure how to check it.

Can you give me any tips how to find what is causing this traffic?


texts: Oracle sql combine several rows of the same table into one row

texts:
I have a table where for each given ID, three rows are generated as shown. The first row is the header row:

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 10    | 0  | 0  | 1  |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 200   | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1  | 0  | 0  |
+-------+------+-------+------+-------+----+----+----+

What im trying to achieve

+-------+------+-------+------+-------+----+-----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS  | SW |
+-------+------+-------+------+-------+----+-----+----+
| 12345 | MANX | Mango | 2667 | 10000 | 1  | 200 | 10 |
+-------+------+-------+------+-------+----+-----+----+

If it helps, if SU is 1, then CS and SW are always 0. If CS is 1 then SU and SW are always 0 and if SW  =0 then SU and CS are always 0.

So i would like to return:

The first 3 columns followed by
The value in the DEN and NUM columns where SU = 1 followed by the value in the SU column
The value in the NUM column on the row where CS = 1 followed by
The value in the NUM column where SW = 1 or 0 if SW = 0

I have tried googling a way forward and found self joins mentioned. i have tried this and  due to my limited understanding i just cant get it to work. I get back many more columns of data back !

I would greatly appreciate some help on this.

cheers

UPDATE

This is the SQL i tried to apply and the result was that each row was duplicated 3 times

select tab_su.id
,      tab_su.grp
,      tab_su."desc"
,      tab_su.den
,      tab_su.num
,      tab_cs.num as cs
,      tab_sw.num as sw
from   tab tab_su
,      tab tab_cs
,      tab tab_sw
where  tab_su.su = 1
and    tab_cs.cs = 1
and    tab_sw.sw = 1
and    tab_su.id = tab_cs.id
and    tab_su.id = tab_sw.id;

UPDATE AFTER RUNNING @ALEX07 CODE - RESULT WHEN ONLY ONE ROW EXISTS

BEFORE

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 8     | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+

AFTER

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | NULL | NULL  | 0  | 8  | 0  |
+-------+------+-------+------+-------+----+----+----+

WHAT I WAS EXPECTING - (so, no change expected)

+-------+------+-------+------+-------+----+----+----+
| ID    | GRP  | ITDESC| DEN  | NUM   | SU | CS | SW |
+-------+------+-------+------+-------+----+----+----+
| 12345 | MANX | Mango | 1    | 8     | 0  | 1  | 0  |
+-------+------+-------+------+-------+----+----+----+

PS: Alex07 code works well when there are 2 or 3 rows of data to be combined. How can the code be edited to ignore data that only spans one row?

ANSWER
I edited the second and third line of Alex07's code to be:

MAX(CASE WHEN SU = 1 OR CS = 1 THEN BUOMDEN END) AS BUOMDEN,
MAX(CASE WHEN SU = 1 OR CS = 1 THEN BUOMNUM END) AS BUOMNUM,


texts: Is it possible in Postgresql 12 to set up master/slave servers so that the replication has to be manually triggered? The database is large, so it’s not feasible to dump and import. The optimal would be if there was a way to do this without any external tools or modules.


texts: PostgreSQL service frozen and can't restart properly

texts:
I have Windows Server 2008 R2 and PostgreSQL 9.3.
Server is still for testing so I'm alone and only have a .net application doing consult with npgsql to the db, mostly doing some stored procedures and some update with NpgsqlDataAdapter.
In pgAdmin sometimes I'm doing a simple query and then take forever like PostgreSQL is frozen.
I go to service stop the postgresql service, but in the task manager still appear some "postgres.exe" process.
I can't restart PostgreSQL service until I kill those orphan process one by one by hand.
So what can cause the postgresql service frozen?
Why are those orphan process still living after I stop the service?

Log file
The stored procedure is moving data from a db to another using dblink. And fail doing the DROP TABLE before recreate the table. There is also 2 other thread working with the db and those are still running without trouble.
2014-07-08 11:42:10 VET LOG:  database system was interrupted; last known up at 2014-07-08 11:36:44 VET
2014-07-08 11:42:10 VET FATAL:  the database system is starting up
2014-07-08 11:42:10 VET LOG:  database system was not properly shut down; automatic recovery in progress
2014-07-08 11:42:10 VET FATAL:  the database system is starting up
2014-07-08 11:42:10 VET LOG:  redo starts at A9/47827B0
2014-07-08 11:42:14 VET FATAL:  the database system is starting up
2014-07-08 11:42:15 VET LOG:  record with zero length at A9/5683970
2014-07-08 11:42:15 VET LOG:  redo done at A9/5683930
2014-07-08 11:42:15 VET LOG:  last completed transaction was at log time 2014-07-08 11:39:23.99-04:30
2014-07-08 11:42:16 VET FATAL:  the database system is starting up
2014-07-08 11:42:17 VET LOG:  database system is ready to accept connections
2014-07-08 11:42:17 VET LOG:  autovacuum launcher started
2014-07-08 12:27:35 VET ERROR:  canceling statement due to user request
2014-07-08 12:27:35 VET CONTEXT:  SQL statement "DROP TABLE if exists app.vzla_traffic"     PL/pgSQL function app."LoadTraffic"() line 7 at SQL statement
2014-07-08 12:27:35 VET STATEMENT:  select app."LoadTraffic"()
2014-07-08 12:32:32 VET ERROR:  canceling statement due to user request
2014-07-08 12:32:32 VET CONTEXT:  SQL statement "DROP TABLE if exists app.vzla_traffic"     PL/pgSQL function app."LoadTraffic"() line 7 at SQL statement
2014-07-08 12:32:32 VET STATEMENT:  select app."LoadTraffic"()
2014-07-08 12:37:30 VET ERROR:  canceling statement due to user request
2014-07-08 12:37:30 VET CONTEXT:  SQL statement "DROP TABLE if exists app.vzla_traffic"     PL/pgSQL function app."LoadTraffic"() line 7 at SQL statement
2014-07-08 12:37:30 VET STATEMENT:  select app."LoadTraffic"()

I guess the canceling statement due to user request is the .net app canceling because timeout. Because running the stored procedure in pgAdmin didn't end neither for +100 seg. and timeout is at 70seg.


texts: Query: All employee with sum of salary > 500

texts:
In my MYSQL Database COMPANY, I have a Table: Worker :

mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SSN    | varchar(64) | NO   |     | NULL    |       |
| name   | varchar(64) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I created using following MySQL's command:

CREATE TABLE `Worker` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table having following data:

mysql> SELECT * FROM `Worker` ORDER BY salary ASC;
+-----+---------+--------+
| SSN | name    | salary |
+-----+---------+--------+
| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |
| 2   | Rahul   |    300 |
| 4   | Harsh   |    500 |
| 7   | THREE   |   1000 |
+-----+---------+--------+
7 rows in set (0.00 sec)

its insert query:

INSERT INTO `Worker`
    (SSN, name, salary)
  VALUES
    ("1", "Grijesh", 200),
    ('2','Rahul',300),
    ('3','Sumit',250),
    ('4','Harsh',500),
    ('5','ONE','100'),
    ('6','TWO',50),
    ('7','THREE',1000);

[QUESTION]

I wants to select first n workers (in asc list)those sum of salary is just more than 500 (> 500). for example:

following four workers are first low salary employees and having SUM of salary 600. (600 > 500)

| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |

I need something like SELECT *  FROMWorkerWHERE SUM(salary) < 501 ASC;  (its incorrect query)

An assumption can be made that all having different salary.

its something like cost cutting: remove employee those are relatively unimportant

A suggestion or link to read will be helpful


texts: using mysql calculate balance of each currency where credit and debit in same column?

texts:
With the following MySQL table containing debit or credit "actions" with associated amounts and currency, how is it possible to select all CLIENT_IDs with a non-zero "balance" of each currency? I have tried but something isn't working correctly.

CLIENT_ID    ACTION_TYPE    ACTION_AMOUNT    Currency
1            debit          1000                USD
1            credit         100                 USD
1            credit         500                 DR
2            debit          1000                EURO
2            credit         1200                DR
3            debit          1000                EURO
3            credit         1000                USD
4            debit          1000                USD

My MySQL query that doesn't work:

select CLIENT_ID,
       sum(if(ACTION_TYPE='credit',ACTION_AMOUNT,0)) as credit,
       sum(if(ACTION_TYPE='debit',ACTION_AMOUNT,0)) as debit,
       sum(if(currency='USD',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as USD_balance,
       sum(if(currency='EURO',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as EURO_balance,
       sum(if(currency='DR',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as DR_balance
    FROM my_table
    GROUP BY CLIENT_ID,currency

The result I am expecting is something like:

CLIENT_ID    USD_Balance    EURO_Balance    DR_Balance
1              -900             0              500
2                0            -1000            1200
3              1000           -1000             0
4             -1000              0              0

I have no idea what else to try. Any help would be great.


texts: How do I alter containment to none for partially contained db with default language?

texts:
Experimented with Contained DBs in an Always-On Availability Group and decided against them. I want to UN-contain the databases now but when I run:

alter database <myDB> set containment = NONE

I get the following error:

  SQL Server Database Error: The default language parameter can only be provided for users in a contained database.

I know how to change the default language to another language, I do not know how to change the default language to no default language. This is not a production system currently, but testing has happened so I want to be able to back the databases up and restore as non-contained DBs elsewhere (need to test my tlog shipping set up solution for cut-over during a pause in testing and want testers to come back to the same data). If there is a way to do this with restore commands that would work fine also...


texts:
I have been trying to install and configure a SQL Server Monitoring tool and running into issues. The vendor has indicated there is a problem with their tool collecting WMI information from one of our SQL Instances. They are trying to get a value from win32_perfrawdata_MSSQLSERVER_SQLServerGeneralStatistics. However, I have indicated to the vendor this particular server is running SQL Express.

Here is my question: Would counters that begin with "win32_perfrawdata_MSSQLSERVER" be installed with the SQL Express Edition?

If they are not installed I need some type of proof to bring back to the vendor.


texts: Counting number of ids present in one table which is not presented on another for a particular user

texts:
I have two tables I want to count the number of ids presented on first which is not in second with respect to user id how can I do the same?

For example.

Case 1

Table 1.

|id|data           |User_id
|4 |Some data      |12
|5 |Another data   |8
|6 |Some other data|6

Table2

|id|table1Id|userId|
|1 |4       |12    |
|2 |5       |14    |
|3 |12      |12    |

Case 2

Table 1.

|id|data           |userid
|4 |Some data      |12
|5 |Another data   |24
|6 |Some other data|32
|7 |Some more data |12

Table2

|id|table1Id|userId|
|1 |4       |12    |
|2 |5       |14    |
|3 |12      |12    |

What I am trying to do is this.

I want to know how many occurrences of id in table1 which is not in table2  for the user 12.

How can I do that?

Case 1 it should return 0

Case2 it should return 1


texts: How to verify SSL always being used on Postgresql 9.6

texts:
I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers.

Although I believe my postgresqls are configured to always use ssl I need a way to double-check this.

Is there some simple way to check that all client connections are indeed being forced to use ssl?


texts:
I have one single table.

Table A has two columns

id, sample_id

an id will be associated with multiple sample_id..However both values i.e combined are unique

example,

id, sample_id
1, 10
1, 11
1, 12
2, 9
2, 10
2, 11

Now i want to update all the records of "1" to "2" provided it doesn't have a record in "2"

How to do it? Expected Result

id, sample_id
1, 10
1, 11
2, 12
2, 9
2, 10
2, 11

Thanks


texts: How can I do the following task in Oracle

texts:
I wish to declare a variable, and create a temp table, and insert some data into the table, and show the data.  BTW, I don't what this done in a new stored procedure:

DECLARE
   I NUMBER := 1;

begin
  CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
end;
SELECT * FROM temp;
/

But it has a compiling error:

  ORA-06550: line 5, column 7: PLS-00103: Encountered the symbol
  "CREATE" when expecting one of the following: ( begin case declare
  exit for goto if loop mod null pragma raise return select update while
  with    << continue close current delete fetch lock insert open
  rollback savepoint set sql execute commit forall merge pipe purge

Thank you Marco. I change it to the following but still not working:

DECLARE
   I NUMBER := 1;
   global temporary table temp(CID NUMBER) on commit preserve rows;
begin
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
  SELECT * FROM temp;
end;
/

ORA-06550: line 3, column 25: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; not null range default character

The following code does not work either.  It looks I cannot place declare and create in the same code:

CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);
DECLARE
   I NUMBER := 1;
begin
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
  SELECT * FROM temp;
end;
/

The error message: ORA-00911: invalid character


texts:
My program does r/w operations on a MariaDB table. There are circumstances why it has to do it in concurrent processes. That's why it does it with multiple connections. After a write (UPDATE query) operation, the then triggered read operation receives legacy values.
Question is if there is any query or other feature which makes all connections refreshing their data. A reconnect of the reader is actually working, but honestly a bad workaround.

There is no cache activated. And even if, the description says that an UPDATE will clear the affected sections in the cache.
I'm pretty new to MariaDB, is there any part of the general concept that I miss here?


texts: Query parent table and get child tables columns

texts:
I have two tables, one inherits the other:

CREATE TABLE parent (
    col1 INTEGER
);

CREATE TABLE child (
    col2 INTEGER
) INHERITS( parent );

// Insert some data
INSERT INTO parent( col1 ) VALUES( 1 );
INSERT INTO child( col1, col2 ) VALUES( 2, 2 );

Is there any way to query the parent table, such that it gets all of the columns of the child tables?

This query returns just the parent columns:

SELECT * FROM parent;

| col1 |
| ---- |
| 1    |
| 2    |

My desired query would return all columns in parent and child:

SELECT ...  -- some query, with desired result:

| col1 | col2 |
| ---- | ---- |
| 1    | NULL |
| 2    | 2    |


texts:
We are performing a migration of 3rd party data and it is hanging.

When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, the SQL_Text from sp_whoisactive (or even activity monitor) is NULL for SPID 38. The wait_info is PREEMPTIVE_OS_GENERICOPS.

Is there anyway to tell what 38 is trying to do?

It's using a Windows-based tool the 3rd party has given us. I took a backup of the data, restored it on this new server (so the database and its contents are accessible and working). Then we run this tool to do the upgrade to their new version of the software and that is where we are getting the blocks.

sp_WhoIsActive @get_locks = 1 returns a really long XML (4k characters too long to post).


texts:
Hello i'm designing a database for a saas. Lots of queries will be per company. I have hesitate between 2 way.
First way: Duplicated ids
Each table have a FK for the company id and select will contains a simple where on this row.
Second way: Using inner join
Each query will have some inner join to get the company id of the user.

First way seem cleaner to me, as we don't repeat the relation.
Second way seems more performant as there is less join.

Note that there is some tables that will require 5 or six joins to get to the company.
I'm looking for the best option in term of security, clean design, and good performances.
EDIT:
Added some examples as I was not that clear.
No many 2 many relation table to simplify
Company <- Contract <- Folder <- Document <- Note
``

Each table point to its parent. If I want all company's documents, or all company's notes, I need to make multiple join if I do not duplicate de company id in all table I must make multiple joins to accomplish this select.


texts: Preparing a query to paste into excel to get growth by month


texts:
One of the best feature of FileStream is : The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

I have encountered an issue that If I create a stored procedure for return file from my FileStream table, Will The SQL Server buffer pool be used? Is it possible to take advantage of benefits of FileStream with T-SQL and SPs?

Thanks


texts: Does adding a surrogate key get rid of an identifying relationship between two entities?

texts:
In the following example, we have a Comments table with a composite primary key (PK)

CREATE TABLE Comments (
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  PRIMARY KEY (video_id, user_id, comment_dt),
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

There appears to be an identifying relationship, as the primary key of Comments includes its foreign keys.

However, watch what happens when you give this Comments table a surrogate primary key of its own:

CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
video_id INT NOT NULL,
user_id INT NOT NULL,
comment_dt DATETIME NOT NULL,
FOREIGN KEY (video_id) REFERENCES Videos(video_id),
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Does that get rid of the identifying relationship? Logically, it still seems to be there, so does this really come down to the physical model and whether or not you want to include a composite key? Are identifying relationships even necessary?


texts: How can I normalize this schema about students and courses further?

texts:
1NF:

I am working on 2NF:

student (student_id, name, start, program)
course (coure_code, course_name, faculty, credits, time)
student_course(student_id, course_code, grade)

I am trying to achieve 3NF and BCNF.

I have identified what I believe are transitive dependencies: { credit } → { time }, and maybe { program } → { start }.

Does this make any sense?


texts: Share mongo database between two OS on dual boot

texts:
Now I have two OS on my computer: Windows 10 and Linux (arch). Sometimes I use Linux but I also use windows because of gaming, and I want to create a small application that is going to work on both OS. I want to know if it is possible to share a Mongo database with all its collections and indexes between the two OS.

This is something I want on my computer for personal usage, it is just a small application, but it creates a lot of documents and some indexes. Also, I will be adding Indexes frequently.

I can not use any external server or machines to store the DB, because the application should work offline. I don't want to migrate the DB from one system to another every time I boot my computer using mongoexport because that takes time and recreates the indexes.


texts:
I look after some infrastructure that includes some MS SQL Servers (SQL Server 2017). I am by no means a SQL expert and have been asked the following:
"Create a user that can only access the schema for database X, Y and Z and not the data"
I am able to create a user that can only access X, Y and Z DB and not A, B, C etc. But I cannot work out have to restrict it to viewing the schema only. Can anyone provide any assistance?
Most of the DBs in question use the "dbo" schema only but some have multiple
Thanks


texts:
When designing a view (adding Order By clause) the wizard in SSMS automatically adds

  TOP (100) PERCENT

What do we need it for? Are there any performance consideratons regarding  TOP (100) PERCENT?

Thank you


texts: Extracting Extended Entity Relationship Diagram from a Relational Database

texts:
I am researching how to extract an Extended Entity Relationship (EER) diagram from a Relational Database.

I'm using Visual Studio 2010 and SQL Server 2008.

I've found tools for Entity Relationship Diagrams (ER) on the Internet, however I haven't find any for EER.

Do you know where I might find anything on this topic?


texts: Is it possible to PIVOT on a LIKE statement

texts:
Is it possible to group by elements (as in COLUMN LIKE='Value%') in a PIVOT table? I have a table [DBT].[Status] which contains various statuses (of databases, instances, etc.) and don't want to pivot/query all the PROD and TEST values as single values, but group them.

E.g. Instead of having columns for the statuses Prod, Prod ACC, Prod APP, .. etc. I would have only one column containing the values for Name LIKE 'Prod%' and Name LIKE 'Test%'.

What I have so far:

Table Definition

CREATE TABLE [DBT].[Status](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_Status] UNIQUE NONCLUSTERED
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Table Values

INSERT INTO [DBT].[Status]
(
    -- ID -- this column value is auto-generated
    Name
)
VALUES
('Test ACC'),
('Test APP'),
('Test DBA'),
('Prod ACC'),
('Prod APP'),
('Prod DBA'),
('Prod'),
('Test'),
('Migrated'),
('Offline'),
('Reserved')

The Pivoted Status Table

SELECT 'Database Status' AS [DB Status],
[1] AS [Test ACC], [2] AS [Test APP], [3] AS [Test DBA], [4] AS [Prod ACC], [5] AS [Prod APP], [6] AS [Prod DBA], [7] AS [Prod], [8] AS [Test], [9] AS [Migrated], [10] AS [Offline], [11] AS [Reserved]
FROM
(
    SELECT ID, Name  FROM [DBT].[Status]
) AS Source
PIVOT
(
    COUNT(Name) FOR ID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

Output So Far

DB Status       Test ACC    Test APP    Test DBA    Prod ACC    Prod APP    Prod DBA    Prod        Test        Migrated    Offline     Reserved
--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Database Status 1           1           1           1           1           1           1           1           1           1           1

db<>fiddle

The dbfiddle so far.

Question

Instead of having multiple rows for the various Test...  and Prod.... values, I would prefer to have them grouped, similar to the following:

DB Status       | Test | Prod | Migrated | Offline | Reserved
--------------- | ---- | ---- | -------- | ------- | --------
Database Status |    4 |    4 |        1 |       1 |        1

I don't have any clue how to go about solving my question. (To be honest I only just grasped PIVOT yesterday after extensive trial and errors).

This question is loosely related to the question How to create sums/counts of grouped items over multiple tables I have already asked. The tables [DBT].[Instance] and [DBT].[Database] contain a column with the [StatusID] which corresponds to the table we are looking at now.


texts: Oracle and stopkey of an index range scan?

texts:
I am struggling to resolve a performance issue with an Oracle query that is opting to perform a full table scan of a table with several million records.  The table is partitioned by month of a timestamp column and contains records that resemble work items.  Multiple concurrent processes run a query against it like this:
select t.col1, t.col2, t.some_nullable_column, t.item_number
from mytable t
where t.rowid in (
    select e.rowid
    from mytable e
    where decode(e.some_nullable_column,null,0,1) = 0
    order by (decode(e.some_nullable_column,null,0,1)), e.item_number
    fetch next 1000 rows only
)
for update of t.some_nullable_column skip locked

You can glean the details of the partitioned table.  The LOCAL function based index is as follows:
create index idx_mahindex on mytable (decode(some_nullable_column, null, 0, 1), item_number) LOCAL;

I am trying to get the oldest 1000 records that have a NULL value in SOME_NULLABLE_COLUMN so that the application process can lock each row and update them safely.
If I run an explain plan on the inner view query it seems to prefer an INDEX FAST FULL SCAN, and then a SORT operation.  I know that I only want the oldest 1000 records so my hope was that by specifying only columns on the index, and ordering by those columns on the index in the same order, that the analyzer would be smart enough to just start reading left most leaves of the index until I find 1000 records that satisfy my filter criteria (since the index is ordered by this function, then by item_number column).  Instead it is scanning the whole index wastefully.
I COULD live with that, but it gets obscenely worse when I run this inner query within the SELECT ... FOR UPDATE SKIP LOCKED query.  The explain plan here shows the preferred plan to be the inner view doing a FULL TABLE SCAN and rejecting the index outright.  If I hint the query to force the index then it will not do the FAST FULL SCAN and SORT, it will not do INDEX RANGE SCAN, and instead will scan the entire index for rowids, then do a batched lookup for the indexed columns, from the table.  The performance here is even worse still.
Is there something I am missing here that is important?  If the index is ordered, it should be easy to range scan the index for the columns in the index and get a list of rowids in sorted order.  Does it have to do something with the fact that this is a partitioned table?  Is there an aspect of SELECT ... FOR UPDATE SKIP LOCKED that makes the CBO think that a full table scan or full index scan is necessary?


texts: Simply inserting to temp table and failing

texts:
Can anyone tell me why this won't work for me?

CREATE TABLE #UndistributedCmds
(
pendingcmdcount int,
estimatedprocesstime INT
)
INSERT INTO #UndistributedCmds

EXEC sp_replmonitorsubscriptionpendingcmds ...

The error:

  Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 152An INSERT EXEC statement cannot be nested.

The error seems to imply that line 152 of the stored procedure definition is the issue, but I'm having trouble understanding this. Is this happening because the stored procedure itself has multiple stored procedures within it?


texts: Fulltext search in a CTE result - missing fulltext index

texts:
My question applies to MariaDB 10.2.7

The query

WITH prefiltered AS (
  SELECT id, fulltext_indexed_column
  FROM fulltext_table
  WHERE id < 30
)
SELECT *
FROM prefiltered
WHERE MATCH(fulltext_indexed_column) AGAINST ('Foo')

leads to

Error #1191 - Can't find FULLTEXT index matching the column list

I understand why, the CTE results are not indexed. I can rewrite the query to not use the CTE, or to use fulltext search inside the first CTE.

But is there a way to achieve what I'm trying to do in this? Tell the DB to index the CTE results?


texts:
I need to run a repairDatabase in mongo in order to reclaim some os disk space. Can anyone provide feedback on the following procedure: let me know if its correct, if I'm missing anything, if the steps are in order, etc.

The setup is a primary/secondary on separate servers. They are both about out of disk space. My plan is to repair the database on each one separately after the other completes. As I am out of disk space, I will be using an external block storage from my cloud provider to use as a repair path. Mongo is currently running via sudo service mongodb start. To complete this it seems that I'll run the following:

On the primary I step down using rs.stepDown()
I shutdown mongo using rs.shutdownServer() - Is this correct? For the next step it seems that I shouldn't run it if Mongo already has another instance running.
On the primary I run mongod --config /etc/mongodb.conf --repair --repairpath PATH_TO_BLOCK_STORAGE
When complete, do I need to copy the data in the repair path over to the original (/var/lib/mongodb)?
Once back up, I restart Mongo using sudo service mongodb start
I then wait for the oplogs to catch up and for the mongos to be in sync.
Once they primary and secondary are in sync I then go onto mongodb-perf-02 and run rs.stepDown() so that mongodb-perf-01 is the primary again.
Once complete I then repeat this on the secondary.


texts: Squashing aggregated rows with PostgreSQL

texts:
Given a table my_data like this:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | NULL
 1  | NULL | smith   | 32
 1  | NULL | NULL    | NULL
 1  | john | smith   | NULL
 1  | john | NULL    | 32

CREATE TABLE my_data(id,name,surname,age)
AS ( VALUES
  (1::int, 'john', 'smith' ,NULL::int),
  (1, NULL,   'smith' ,32),
  (1, NULL,   NULL    ,NULL),
  (1, 'john', 'smith' ,NULL),
  (1, 'john', NULL    ,32)
);

For the same id, the values (if present) in the respective columns are always the sames so how can I "squash" them to get:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | 32

My attempt

A cross join lateral for each column is my only idea so far, but I doubt is good:

select
distinct column1, c2.value, c3.value, c4.value
from my_data md
cross join lateral (select column2 from my_data where column1 = md.column1 and column2 is not null limit 1) as c2(value)
cross join lateral (select column3 from my_data where column1 = md.column1 and column3 is not null limit 1) as c3(value)
cross join lateral (select column4 from my_data where column1 = md.column1 and column4 is not null limit 1) as c4(value);


texts: PostgreSQL - PostGIS ST_SetValues - int array cast to double and back

texts:
I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][].

If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to integer?

Or is there some limited precission for integer part of the double. I want to store "packed" RGBA into a single 32bit integer there and "unpack" single color channels after output in my app.

As from documentation, double should be 64bit, so in my oppinion, this should be possible, but maybe I am missing something.


texts: How to organize database for translations, performance?

texts:
I am working on a new project for an international website. The task is to get this available in 16 languages.

On the database structure, I was wondering if there is any performance advantage or disadvantage between these two approaches. What would be the preferred choice and why?

Approach #1

Approach #2


texts: How to add DATETIME value for session user

texts:
I am maintaining two tables:

Employee table consists of username and password(for logging in)
log table consists of login and logout time for the session user

With the click of a button, I should be able to capture/insert the time and session user to log table.

Here is my code for logging time and session user:

 <?php

session_start();
     if($_SESSION['username']) {
        $user = $_SESSION['username'];
    $tin= $_POST["timein"];
            $conn = mysql_connect("localhost","root","");

            mysql_select_db("attendance", $conn);

            $query = "INSERT INTO log (user, timein) VALUES ($user , NOW())";

            mysql_query($query, $conn);

            echo "
you have logged your time!
";

            mysql_close($conn);
    }
?>


texts: I had PostgreSQL installation in an EC2 instance. I wanted to migrate to RDS and thus used the DMS service. However, I later came to realize that the data type of the columns have been changed. The changes in the datatype can be seen in the images attached.


texts:
I support an application that uses CodeSmith and NetTiers templates to generate C# code. CodeSmith inspects the database and uses the SET FMTONLY ON setting to determine the columns for which code should be generated.

Unfortunately in moving from SQL Server 2005 to 2012 there is a particular circumstance where this is now no longer working. There is a Stored Procedure that executes a Stored Procedure on a linked server and the code being generated for that Stored Procedure is incorrect.

I've managed to isolate the issue to the EXEC statement with SET FMTONLY ON. The following (example only) SELECT works on both the 2005 and 2012 instances:

SET FMTONLY ON
SELECT TOP(10) [MCMCU]
              ,[MCSTYL]
              ,[MCDC]
FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]
SET FMTONLY OFF

This returns the column headings only, as expected. The following works only on the 2005 instance:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE_CRP].[CRPDTA].[F0006]') AT [JDE]
SET FMTONLY OFF

When run on the 2012 instance, SSMS shows the message "Command(s) completed successfully" but doesn't display the column headings.

Is there anything I'm missing here? Perhaps a setting that I need to change? The linked server definitions are identical, including the identity used to connect. And yes I'm aware that FMTONLY is deprecated but I don't have any ability to change the way CodeSmith interrogates the database.


texts: Sql Server: how to validate schema against a textual representation (XML or the like) natively?

texts:
I need a simple "complete dump" of the schema for our repo, in human-readable format: Sprocs, Views, the like. We need to "fix" some old schema issues that cropped in over the time, and "clean up" the schema. In addition, constant merging between quite different branches should be as easy as possible, therefor a complete representation of the schema to begin with, and not migration scripts, as they are mergeunfriendly (Schema conflicts would have to be found be tests, unfortunately our test coverage is a joke to begin with).

Just as a reminder: any binary blop or .bak file are not the goal, as well as any GUI tools. This should run on a CI server. We need full automation for the schema vialidation from the commandline, and a humanreadable filfeformat for the schemareprenstation in the repository.

I know that Sql Server itself allows to export the schema of a table (maybe a whole DB) as XML, but I can't find the statement.. any help?

Or any (preferably c#/.NET related, preferably opensource) tools that can do such a thing?

Sql Server Version would be 2008 and up.

Edit: I'm speaking of a script, not some GUI functionality.. I've seen that done by a colleague, and can't ask him as he's on vacation for quite some time.. searched for a while, but didn't find the script

Edit2
I need a schema compare and diff like Tools like Redgate provide, just for the commandline, and because of our dev focused process we need a humanreadable format.. XML or whatever. One approach is showed by http://schemacrawler.sourceforge.net -> http://schemacrawler.sourceforge.net/snapshot-examples/snapshot.txt

I've written a C# tool to migrate a Schema from Sql Server to e.g. LocalDb via SMO , so I know the challenges, but I was hoping that this time I could save the time, because things like sp_help and XML schema support is built right into Sql server.
So the question is more like "is there a script which could export all at once". This is for built time, not production time, so perf doesn't matter at all.


texts: Is there any way to Restore a Database from newer version to an Older Version of SQL Server?

texts:
I am having a database backup file from SQL SERVER 2012 and i wanted to restore it on SQL SERVER 2008R2 .
If we can’t restore a SQL Server 2012 database to earlier version what is the next best option?
So Is their any way to restore by any other way ??


texts: online Oracle database migration to different Oracle database release

texts:
We currently have the following environment

Source data center:
1) OS: Oracle Enterprise Linux 5.7
2) DB: Oracle Database Enterprise Edition 10.2.0.1.0
3) Server Type: Physical Server
4) Data Size: 450 GB(used)/ 2 TB (allocated)

Target data center:
1) OS: RHEL 6.8
2) DB: Oracle Database Enterprise Edition 10.2.0.4.0
3) Server Type: Virtual on top of VMWare

WAN connection between Source and Target Data Center is 10 Gbps

At the moment there are no Tape library on Target Data Center

We want to copy the database to Target Data Center without stopping production application on Source Data Center (online copy).

Once database has been copied to target Data Center, we will do functional testing on target Data Center.

After the result from functional testing is confirmed okay then we will do cut over to Target Data Center with maximum 4 hours downtime allowed.

We have tried the following methods:
1) impdp - network link: failed, since the production need to be stopped
2) RMAN duplicate database: failed, since source db and target db is in different release

Tried to install same Oracle release on target Data Center but the prerequisites from oracle said that we should downgrade the OS first to RHEL 5.7 (latest supported OS to run Oracle 11.2.0.1) and then upgrade to RHEL 6.8 after data migration.

After checking with Red Hat, it is also not possible to downgrade to RHEL 5.7 and upgrade later to RHEL 6.8 as Red Hat said that it is major upgrade and should be performed with fresh install then migrate the OS settings.

Need advise on what is the best possible solution for this situation? FYI, Oracle Golden Gate solutions is out of question since we currently do not have allocated budget to purchase additional tools.

Thank you and best regards,

Amir


texts: Cannot connect to SQL Server from Management Studio (both 2012)

texts:
I installed SQL Server 2012 Express. It seems to be running fine. My SQL Server Configuration Manager looks like this:

From what I understand it means that my instance is called MSSQLSERVER.

Now in Management Studio when I try to log in it can't find the instance. I've tried .\MSSQLSERVER but it doesn't work:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)

I don't understand what I'm doing wrong. Why can't I connect to the server?


texts: Will breaking down a bulky insert reduce the number of IOPS on MySQL?

texts: Our RDS instance is setup to handle 1000 Provisioned IOPS. This number is fine for most of the time. But at some point we need to run several insert operations (each inserting 2K records), our IOPS get very high and we start getting disk queue depth alerts. Does it make sense to break those inserts into smaller ones (less than a 1K records). Does it make sense to break those inserts into smaller ones or will it cause the IOPS to get even higher?


texts: Heavy performance drop on Progress query, looks like based on string character inside table

texts:
I'm launching following query on a Rel 11.6 Progress database:
FIND FIRST Table1 WHERE Table1.Field1 = 1
                    AND Table1.Field2 >= 2
                    AND TRUE
                    AND
  CAN-FIND(Table2 WHERE Table2.Field1 = Table1.Field3    /* join */
                    AND Table2.Field2 = 0
                    AND Table2.Field3 = "WHATEVER").

On both tables, the necessary indexes are created and rebuilt.
The problem is the following: when the criterion Table2.Field3 = "WHATEVER" is present (although that field is present in an index), the query takes entire minutes to run, otherwise the query is done very rapidly (in a matter of seconds).
What can explain such behaviour in a Progress 11.6 database and are there any profiling possibilities for monitoring what's happening? (I'm working with the appBuilder/procedure editor "IDE")
Thanks in advance


texts: How to install SQL Server 2017 Express in "Quiet Simple" mode without an extraction folder?

texts:
I need to install SQL Server 2017 Express with as little user interaction as possible. I am using the SQLEXPR_x64_ENU.exe setup file that I found a Microsoft download for. The installer I am currently using is the 2017 CORE one linked to from here (I have no idea how people find official Microsoft downloads for anything!)

Currently I can do exactly what I want with 2012 by using the following parameters:

/FEATURES="SQL, Tools" /QS /IACCEPTSQLSERVERLICENSETERMS /ADDCURRENTUSERASSQLADMIN=1 /ACTION="Install" /ERRORREPORTING=0 /INSTANCENAME="MyDB"

In 2012 this works great, however I am trying to now do the same in 2017. The problem I have is that it creates an extraction folder called "SQLEXPR_x64_ENU" in the same directory as the install. This is not desirable.

Question: Is there anyway to prevent this extraction folder from being used, so that it functions that same as the 2012 setup?

Alternatively, one of the following solutions would be acceptable:

Allow the user to select the extraction location - like it does with default install (non-quiet) - but still ensure /QS mode for the rest of the setup
Have the extraction folder automatically deleted after setup (it wouldn't be so bad if it cleaned up it's mess after it was finished)


texts:
I have a table that has approximately 450k records
here is the structure of it

CREATE TABLE [dbo].[Reviews](
[ReviewID] [int] IDENTITY(1,1) NOT NULL,
[AuthorName] [nvarchar](50) NULL,
[AuthorURL] [nvarchar](500) NULL,
[Rating] [int] NULL,
[Comment] [nvarchar](max) NULL,
[UTCCreatedDate] [date] NULL,
[IsImported] [bit] NULL,
[ListingID] [int] NULL,
CONSTRAINT [PK_Reviews] PRIMARY KEY CLUSTERED
(
[ReviewID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Reviews]  WITH NOCHECK ADD  CONSTRAINT [FK_Reviews_Listings] FOREIGN KEY([ListingID])
REFERENCES [dbo].[Listings] ([ListingID])
GO

ALTER TABLE [dbo].[Reviews] CHECK CONSTRAINT [FK_Reviews_Listings]
GO

Now when I look at the index sizes of all my tables (via posted tsql) here http://basitaalishan.com/2012/07/06/find-the-size-of-index-in-sql-server/

The review table has a massive 446264K (approximately 450MB) index on just the primary key.

What are my options on reducing/eliminating this as I am not sure how useful this is as this is just a related table (child)


texts: Moving database from one server to another

texts:
I have a virtual machine running SQL Server 2012 in Azure and have also generated a script of my database to be run on the SQLSERVER2012 VM on Azure, however I am getting an error

  "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PXDEV.mdf" failed with the operating system error 3 (The system cannot find the path specified.).

I have checked that path and the file is actually there on the source server.


texts: Why is Update Query Slow with correct Indexes?

texts:
I have a query that updates a temp table. There are 2 Filtered indexes (by date) used in this query.
However, the update take 8-10 mins to run. I am struggling to find out why it is so slow. There is no blocking or locking when the query is being run.

I have also rebuilt the indexes.

Anything else I should check? Thanks.

DECLARE @PDate DateTime = '2014/12/16'

CREATE TABLE #UserData
(
    UniqueID int,
    Broker nvarchar(80),
    Account nvarchar(50),
    Currency nvarchar(5),
    InvestmentID nvarchar(50),
    Description nvarchar(300),
    Quantity numeric(38,2),
    TFactor numeric(38,2),
    Price numeric(38,7),
    CurrentPrice numeric(38,7),
    PFactor numeric(38,2),
    Cost numeric(38,2),
    TradeAction nchar(10),
    TradeID nvarchar(50),
    Commission numeric(38,2)
)

UPDATE #UserData
    SET CurrentPrice = OtherUser.Price
FROM #UserData BD
INNER JOIN UserDetailsTbl OtherUser WITH(INDEX (IX_UserDetailsTbl_FX1))
       ON OtherUser.InvestmentID = BD.InvestmentID
       AND OtherUser.PDate = @PDate
       AND OtherUser.Factor = BD.TFactor
INNER JOIN UserTransactionsTbl Trans WITH(INDEX ([IX_UserTransactionsTbl_FX1]))
       ON Trans.Account = OtherUser.Account
       AND Trans.Broker = OtherUser.Broker
       AND Trans.TDate = OtherUser.PDate
       AND Trans.TFactor = BD.TFactor
WHERE BD.CurrentPrice is null
OPTION(RECOMPILE);

INDEXES:

CREATE NONCLUSTERED INDEX [IX_UserDetailsTbl_FX1] ON [dbo].[UserDetailsTbl]
(
[InvestmentID] ASC,
[PDate] ASC,
[Account] ASC,
[Broker] ASC,
[Factor] ASC
)
INCLUDE ( [Price])
WHERE ([PDate]>='2014-12-03' AND [PDate]<='2014-12-27')

CREATE NONCLUSTERED INDEX [IX_UserTransactionsTbl_FX1] ON [dbo].[UserTransactionsTbl]
(
[Broker] ASC,
[Account] ASC,
[InvestmentID] ASC,
[TDate] ASC,
[TFactor] ASC
)
INCLUDE ( [UniqueCounterPartyID],
[Currency],
[Description],
[Quantity],
[AdjTFactor],
[AdjTradePrice],
[Price],
[PricingFactor],
[Cost],
[TradeAction],
[TradeID],
[Commission])
WHERE ([TDate]>='2014-12-03' AND [TDate]<='2014-12-27')


texts: Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

texts:
The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market:

WITH markets AS (
    SELECT unnest('{1,2,4}'::int[]) AS market_id
)
SELECT m.market_id, count(td.id) AS agg
FROM markets m
LEFT JOIN market_trades td
ON td.market_id = m.market_id
GROUP  BY m.market_id
ORDER  BY m.market_id;

But it takes about 10 full seconds to run! I added an index on market_trades.market_id, but it did not speed up the query at all.

The market_trades table has about 30 million rows.

Here is the ouput of EXPLAIN ANALYZE on the above query (on depesz):

                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1142468.03..1142468.28 rows=100 width=8) (actual time=62052.515..62052.516 rows=3 loops=1)
   Sort Key: m.market_id
   Sort Method: quicksort  Memory: 25kB
   CTE markets
     ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.006..0.010 rows=3 loops=1)
   ->  HashAggregate  (cost=1142463.20..1142464.20 rows=100 width=8) (actual time=62052.502..62052.504 rows=3 loops=1)
         ->  Hash Right Join  (cost=3.25..992529.47 rows=29986746 width=8) (actual time=1.398..51289.914 rows=14297964 loops=1)
               Hash Cond: (td.market_id = m.market_id)
               ->  Seq Scan on market_trades td  (cost=0.00..580208.46 rows=29986746 width=8) (actual time=0.007..21659.649 rows=29985911 loops=1)
               ->  Hash  (cost=2.00..2.00 rows=100 width=4) (actual time=0.023..0.023 rows=3 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  CTE Scan on markets m  (cost=0.00..2.00 rows=100 width=4) (actual time=0.010..0.017 rows=3 loops=1)

EDIT: Clearly the index on market_trades.market_id is not being used. Why not?


texts:
We're using Postgres 9.2 on Windows to store low-frequency timeseries data:  we're inserting around 2000 rows per second every second 24 hours, 7 days a week with no downtime.  There is a DELETE that runs on the table every 10 minutes or so to keep the length of the table to a fixed number of days.  This ends up being a fairly stable 900 million rows.  (For those interested, SELECT, INSERT, DELETE are all performant).

As such the DELETE, whilst deleting rows is not freeing up disc space.  For that we need VACUUM to run.

I've queries the pg_stat_user_tables and VACUUM appears not to have ever run.

What I understand from various docs (http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html):

we appear to have auto-vacuum on, and it is running on other tables.
auto-vacuum doesn't run FULL, and shouldn't require an exclusive lock on the table.

Does anyone have any thoughts why auto-vacuum is not running?  Is this purely because the table is continuously busy?

And it is worthwhile running VACUUM after every DELETE in this case (which runs every 10 minutes)?

Edit:

Query using the SQL from the SO link below:

-[ RECORD 2 ]---+---------------------------
schemaname      | stats
relname         | statistic_values_by_sec
last_vacuum     |
last_autovacuum |
n_tup           |    932,315,264
dead_tup        |    940,727,818
av_threshold    |    186,463,103
expect_av       | *

and raw output:

-[ RECORD 3 ]-----+---------------------------
relid             | 501908
schemaname        | stats
relname           | statistic_values_by_sec
seq_scan          | 12
seq_tup_read      | 4526762064
idx_scan          | 29643
idx_tup_fetch     | 2544206912
n_tup_ins         | 1573896877
n_tup_upd         | 0
n_tup_del         | 941176496
n_tup_hot_upd     | 0
n_live_tup        | 688858417
n_dead_tup        | 940727818
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  | 2014-08-09 01:36:21.703+01
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 69


texts:
How can I disable the "Tasks ->" button in SQL Server Management Studio?

I need to prevent users scripting objects.


texts: Indexes: Normal tables vs memory optimized

texts:
If two tables have the same schema but one is "normal" and the other is memory optimized, if I research on how indexes should be for the "normal", can I do a direct extrapolation for the memory optimized table?

Couldn't find anything conclusive so I really appreciate links, topics and so on.


texts: How export a database based in a primary key of a table?

texts:
So, I'm trying to export all the DB structure of my MySQL db.

Attached is my DB structure, I have more then this, but I want these tables only.

Basically Tools relate to Factsheets and Factsheets to Content and Images, then to everything else.

I need to export based on TOOL ID. Which is only defined on the Factsheets table as a FK and PK only on the Tools Table.

Any ideas?

I can do command line, mysql workbence or PHP-my admin.

UPDATE #1:

Ok to clear some stuff up, lets forget about glossary. A tool is a collection of factsheets, factsheets are collections of factsheetcontent (1 factsheet to many contents) and each piece of factsheetcontent is related to one factsheettitle.

Basically, I'm needing to query based on the TOOLID, ex the Citrus Pests tool with ID of 2, to get all the factsheets which then get all the content under them.

Relations:

Tool to Factsheet: 1 to Many

Factsheet to FactsheetContent: 1 to Many

FactsheetContent to FactsheetTitle: 1 to Many

Factsheet to FactsheetImages: 1 to many

FactsheetImages to image: 1 to 1

Starting from the tool I need to get factsheets, then all things under them. I need the Database structure. Otherwise I'd do a Select with a bunch of joins and export to a CSV

This is what I need, we can drop the glossary as thats not needed. This is what I'm after, using the command line mysqldump works great for the 2 table that has the toolid, after that it quickly fails and blows apart--starts to throw lots of errors.

Any ideas?

Error I get using RolandoMySQLDBA suggestion:

./getDbtablesBasedOnToolID

: command not foundnToolID: line 6:

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)
: command not foundnToolID: line 22:
: command not foundnToolID: line 32:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 39:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 46:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 53:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 60:
'@'localhost' (using password: NO) when trying to connect


texts: Debug PostgreSQL lock count with check_postgres.pl

texts:
We use check_postgres.pl to monitor our database.

We use this to check the count of the locks:

https://bucardo.org/check_postgres/check_postgres.pl.html#locks

We often see more than 150 locks.

The question was: What is going on? We patched the script to output this sql statement, if the lock count was exceeded:

select * from pg_stat_activity order by datname

Unfortunately the result is not what I was expecting. Although there are more than 150 locks, pg_stat_activity shows only few (less then 10) queries.

This has happened about twice a day during the last days, and every time only few lines where returned by pg_stat_activity.

What is going on here?

How to debug the current DB state if there are too many locks?


texts: Can duplicate pg_hba entries result in a connection error?

texts:
I have received the following error when attempting to connect to an Arch Linux server:

Sequel::DatabaseConnectionErrorreplication#monitor
PG::Error: could not connect to server: Connection refused Is the server running on host "45.56.86.24" and accepting TCP/IP connections on port 5432?

but I have an entry in my pg_hba.conf file on that server:

hostssl replication replication 45.56.86.24/32 md5

Actually... there are two lines like this. Is presence of the second line be expected to result in this error?


texts: Two corrupted indexes found by DBCC CheckTable

texts:
I'm wondering if this problem is solvable by dropping/recreating the two indexes.  Both are rarely used, nonclustered, non PK, non FK indexes.

Here is the dbcc output:

  Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

  Msg 8955, Level 16, State 1, Line 1

  Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

  Msg 8951, Level 16, State 1, Line 1

  Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_ITEMS_ADVFLAG_PARETN_PRICE_STOCK' (ID 70). Possible missing or invalid keys for the index row matching:

  Msg 8955, Level 16, State 1, Line 1

  Data row (1:10834009:15) identified by (Id = 8885719) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 9.30 and NSFPQtyInStock = 0 and Id = 8885719'.


texts:
I'm very new to SQL, so really need some tips.

I need some help building a query that'll get me all users that have logged into to our platform over the past 12 months.


texts: I need to load a database backup from some months ago.There are 29 tables backed up as individual.sql files. Is the order I load them important? For instance the ClientSupplementary table has a FK into the Client Table so that if the Client is deleted the Supplementary data is as well. So does the Client have to be loaded before their Supplementary information?


texts: Can I rely on this SQL shortcut to save a query?

texts:
Consider a table with a status field that holds one of two values:

| status | set('active','default') | YES  |  | active |

I need to know how many records are in the table, and additionally how many have the status set to active. I notice that SUM returns 1 for each record set to active and 2 for each record set to default:

mysql> select count(*), sum(status) from users;
+----------+-------------+
| count(*) | sum(status) |
+----------+-------------+
|        3 |           4 |
+----------+-------------+
1 row in set (0.00 sec)

mysql> select status from users;
+---------+
| status  |
+---------+
| default |
| active  |
| active  |
+---------+
3 rows in set (0.00 sec)

Can I rely on this behaviour? Is this a bad practice?


texts: Equivalent in SQL*Plus of sqlite3's "line mode"

texts:
One of the output display modes supported by the default sqlite3 text client is the so-called "line mode".  In this mode, the output of SELECT queries is displayed "one value per line".  Here's an example:

sqlite> .mode line
sqlite> SELECT * FROM invoices LIMIT 2;
        InvoiceId = 1
       CustomerId = 2
      InvoiceDate = 2009-01-01 00:00:00
   BillingAddress = Theodor-Heuss-Straße 34
      BillingCity = Stuttgart
     BillingState =
   BillingCountry = Germany
BillingPostalCode = 70174
            Total = 1.98

        InvoiceId = 2
       CustomerId = 4
      InvoiceDate = 2009-01-02 00:00:00
   BillingAddress = Ullevålsveien 14
      BillingCity = Oslo
     BillingState =
   BillingCountry = Norway
BillingPostalCode = 0171
            Total = 3.96

How can I achieve the same thing with SQL*Plus?


texts: Choose security group for restore-db-instance-to-point-in-time

texts:
When using the AWS CLI restore-db-instance-to-point-in-time command, I can't figure out how to set the security group. The doc on this page says:

  The target database is created from the source database with the same configuration as the original database except that the DB instance is created with the default DB security group.

Is it possible to override this parameter to use the same security group as the original instance?


texts: migrating data from mysql to postgres pgloade IRONCLAD::SIMPLE-OCTET-VECTOR

texts:
I am migrating data from MySQL to Postgres using pgloader.
My script works fine on my local machine. But as I am running pgloader to prod database it's giving me following error

The value of SEQUENCE is "?;JAExN[^F#yZ:O_o3Mj...", which is not of type IRONCLAD::SIMPLE-OCTET-VECTOR.

I have tried the following way but
https://github.com/dimitri/pgloader/issues/782.

I am not sure what went wrong.
Any help is appreciated


texts:
If I had a table structure like this :

id | product name | sales dollars | projection dollars | year

And had data like this :

1 | tv | 334600 | 452000 | 2015
2 | pc | 929492 | 779564 | 2015
3 | tv | 222232 | 432134 | 2014

How can I get results into single row like this :

product name | current year sales dollars | previous year sales dollars

And resulting in this :

tv | 334600 | 222232
pc | 929492 | 0


texts: Is it possible to set a timeout for optimization in PostgreSQL?

texts: In PostgreSQL is there a timeout when the optimizer stops the optimization process and returns the least expensive plan it has found so far? Is it possible to change its value?


texts: How do you add data to a table dynamically from a trigger in T-SQL

texts:
I have a Uni assignment due, part of it requires this:

"Create a database trigger called “TRG_PREVENT_VIEW_DROP” that will prevent the dropping of any view

The trigger must log the action in a separate table called event logs and include the following data; the user, date/time, attempt type and view name. The attempt_type and view name data must not be hard coded. "

It's got me stumped, any help will be greatly appreciated, cheers


texts: sp_executesql processes uniqueidentifier-param with EntityCommandExecutionException but still acts like expected

texts:
I use a Azure DB(MS-Sql) and administrate the DB with Management Studio for SQL Server 2016. My Application is a simple C#/WPF-Application.

So here we go, this is my SProc:

    ALTER PROCEDURE [dbo].[StartChangeTrackingProcedure]
(
@UID uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON
declare @sql nvarchar(max);

SET @sql = N'INSERT INTO TBL_ChangeTrackerPerLoggedUser
             VALUES (0,0,0,0,0, @UID)';
             PRINT @sql;
EXECUTE sp_executesql @sql, '@UID uniqueidentifier', @UID = @UID
END

The TBL_ChangeTrackerPerLoggedUser's name is a bit confusing as it has nothing to do with the ordinary ChangeTracking-Mechanism, it is just a simple table where you can look up wether there were made any changes to DB or not (because Azure has no Query Notifications, right?). It looks like this:

CREATE TABLE [dbo].[TBL_ChangeTrackerPerLoggedUser](
[ChangedTemplates] [bit] NOT NULL,
[ChangedInstances] [bit] NOT NULL,
[ChangedWF] [bit] NOT NULL,
[ChangedUser] [bit] NOT NULL,
[ChangedDoc] [bit] NOT NULL,
[UserDependency] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_TBL_ChangeTrackerPerLoggedUser] PRIMARY KEY CLUSTERED
(
[UserDependency] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Next you'll see the call from Entitiy Framework, where LoggedUser.Instance.UsrID is a GUID:

 using (NCDBEntities DBContext = new NCDBEntities())
 {
     var uid = new SqlParameter("@UID", LoggedUser.Instance.UsrID);
     var res = DBContext.Database
               .SqlQuery<TBL_ChangeTrackerPerLoggedUser>
               ("StartChangeTrackingProcedure @UID", uid).ToList();
 }

Now, when i process this code I get an EntityCommandExecutionException telling me:

  Der Datenleser ist mit dem angegebenen Wert für 'NCDBModel.TBL_ChangeTrackerPerLoggedUser' nicht kompatibel. Ein Element vom Typ ('ChangedTemplates') weist keine entsprechende Spalte im gleichnamigen Datenleser auf.

Translated this means something like:

  Datareader is not compatible with the given value for 'NCDBModel.TBL_ChangeTrackerPerLoggedUser'. One element of type ('ChangedTemplates') has no actual column in equal named Datareader.

But when i take a look at DB at this point, SProc has been executed successfully:

So...is there any advice or explanation you can provide to understand this kind of magic? Should i simply catch this Exception and act like it never happened? Sideeffects etc.? Thank you!


texts:
We been having a lot of sql server performance problems lately. I've recently been digging into performance analysis using the variety of DMVs. I've definitely found some interesting things. Unfortunately, i'm a developer and can't spend a whole lot of time on this issue and we don't have any in house knowledge in this area. SQL performance monitoring almost seems like an art in itself. So i've been researching some tools that will lead us down the right path and take a lot of guess work out the equation. The competing tools i've found so far are:

Ignite - Confio

SQL Monitor - Redgate

Foglight - Quest

Diagnostic Manager - Idera

Performance Advisor - SQL Sentry
I'm leaning towards SQL Monitor because we have a variety of red gate tools and have been extremely happy with them and it comes at a very attractive price compared to the competition. I also really like the web interface compared to a stand alone app. I'm sure by the price there are features in the other tools that red gate doesn't have. There is only so much you can get from marketing info & videos on their website. Can anyone give some advice to which of the products (if not all) give alerts that are more geared towards the type of shop that doesn't have anyone who is SQL Server performance anaylsis pro?

Edit: Which of the tools require a separate machine? It appears from confio web site that ignite does.


texts:
Our Active directory will change the accounts name for example John Doe Jdoe@abcd.com to John.Doe@abcd.com.
My question is do I need to change all the SQL Logins individually or the person can login to SQL server management studio with their old names. What also happens if the login is an owner of a database or job.


texts: Azure SQL database DTU compute utilization maxing out

texts:
I have an Azure SQL database that has gone from a low DUT baseline between 0-3% (avg) to now maxing out at 100% utilization.

This is for a LOB application and we need to understand what is causing the utilization to be maxing out like this. The server is on standard with 200 DTUs and not in an elastic pool.
I've looked at Query performance insight in the Azure portal and while there are some long running queries, the top offender represents only 0.3% CPU and data IO of 0.23% over the past week.

Can anyone offer a good strategy for diagnosing the issue here?


texts: Powershell script executed by SQL Server doesn't take effect

texts:
I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (c:\temp\sqltotext.ps1).
echo "haha" | Out-File "c:\temp\haha.txt"

Then I execute the script through xp_cmdshell like this.
DECLARE @result INT;
EXEC @result = sys.xp_cmdshell N'powershell.exe -Command "Start-Process powershell.exe -Verb RunAs ''-NoProfile -ExecutionPolicy Bypass -File c:\temp\sqltotext.ps1''"';
IF (@result = 0) PRINT 'Success';
ELSE PRINT 'Failure';

It runs successfully and I can find the file c:\temp\haha.txt with the content haha.
However, when I change the content of c:\temp\sqltotext.ps1 to:
echo Process CurrentUser LocalMachine | %{Set-ExecutionPolicy bypass -Force -Scope $_}

and execute the same aforementioned TSQL command, the TSQL reports success but I didn't get the expected result (the execution policy in all scopes were NOT changed).
When I execute the PowerShell script manually (in a PowerShell console and type c:\temp>.\sqltotext.ps1), it works as expected (the execution policy in all scopes were changed). Why this happens?
I did some investigation though. EXEC xp_cmdshell 'whoami.exe' reports I'm running with nt service\mssqlserver. I also manually added nt service\mssqlserver into the administrators local user group. By using process explorer, I can confirm the PowerShell session indeed was started and all related processes have admin permission (Integrity = High).

The cmd.exe command line looks like this:

When I execute this command manually, it can change the execution policy without any issue (I changed all execution policy back to the original values after this).

The PowerShell process command line looks like this:

When I execute the PowerShell process command line manually, I can change the execution policies too. I don't have any idea why when I run the command through xp_cmdshell, it doesn't change anything.
BTW, I know there are multiple ways to write the command. I'm just talking about the technical skills here, so please don't suggest me changing command syntax etc.


texts: How to disable SSMS table designer timeout?

texts:
How do I disable the Table Designer timeout in SQL Server Management Studio?

Background

This KB Article seems to indicate that if you stop overriding the Designer timeout:

  To resolve this behavior, use one of the following methods:

  Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio.

it is supposed to revert to the Execution timeout:

But rather than timing out never it times out after 30 seconds:

I've also tried setting the table designer to the maximum value of 65,535. Unfortunately that just causes the timeout to happen after 18 hours, rather than 30 seconds.

How do I disable the table designer timeout in SQL Server Management Studio?

SQL Server Management Studio: 9.00.1399.00

Pre-emptive snarky comment: what kind of table change are you making that needs more than 18 hours to complete? I'm changing a column in a 130 GB table from text to nvarchar(max).