texts: mysql server is not sending result set back after query execution to client and staying in sending data state

texts:
When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application.

When I execute the same query in SQLYog directly, it works fine and returns 1000 row(s)
with following details

Execution Time : 21.060 sec
Transfer Time  : 3 min 11 sec
Total Time     : 3 min 33 sec

I am not getting any kind of exception for debugging.

I am using the following

mysql server 5.5
JDK 7
mysql-connector-java-5.1.20
apache tomcat server 7.0.27


texts: Why are mysql password hashes internally saved with a star (asterisk)?

texts:
I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get

mysql> select * from mysql.user limit 1 \G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

The password is obviously hashed, but why does it begin with the star (asterisk)?


texts: Storing generated documents that can not be updated by changes in relationships

texts:
I have a system running with PostgreSQL and Yii Framework, which generates a few medical documents that, once ready, can't be changed. For example: If my full name comes out on this document today, and then I change this tomorrow, that document from yesterday can't get my "new name". It's like I need to have two versions: 1.0, with my old name, and the 1.1, with the new one. that's what I thought first:

Store the generated PDF. But I think that would occupy unnecessary space and prevent queries "in" document.
store in a plain text like a JSON in a text field

what's really clear to me is that it would not be possible to do this mechanism without avoiding the relationships between the tables. I also believe that storing the each field document in a table field to be a problem, because there are many multiple fields (documents can be up to 150 pages).

Hope I've made myself clear.


texts: Mongo slow startup after upgrade to 4.2.5

texts:
I have upgraded the MongoDB replica set, which consists of 3 members, from 4.0.11 to 4.2.5.

After upgrading, startup lasts about 5 minutes.

Before upgrading it was instant.

It is related to oplog size, because I tested with dropping oplog on new mongo 4.2 and startup was instant.

Max oplog size was 25GB, I decreased it to 5GB and the startup is still slow.

Mongo db is on AWS with EBS standard disks.

However mongo worked well until this upgrade.

Here is the part of the log output:

2020-04-07T16:36:19.391+0000 I  STORAGE  [initandlisten] Detected data files in /var/lib/mongodb created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2020-04-07T16:36:19.391+0000 I  STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=7279M,cache_overflow=(file_max=0M),session_max=33000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000,close_scan_interval=10,close_handle_minimum=250),statistics_log=(wait=0),verbose=[recovery_progress,checkpoint_progress],
2020-04-07T16:36:19.931+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277379:931910][29458:0x7f2d17521b40], txn-recover: Recovering log 2439 through 2440
2020-04-07T16:36:20.002+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:2298][29458:0x7f2d17521b40], txn-recover: Recovering log 2440 through 2440
2020-04-07T16:36:20.086+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:86290][29458:0x7f2d17521b40], txn-recover: Main recovery loop: starting at 2439/15997696 to 2440/256
2020-04-07T16:36:20.087+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:87145][29458:0x7f2d17521b40], txn-recover: Recovering log 2439 through 2440
2020-04-07T16:36:20.140+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:139996][29458:0x7f2d17521b40], txn-recover: Recovering log 2440 through 2440
2020-04-07T16:36:20.189+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:189056][29458:0x7f2d17521b40], txn-recover: Set global recovery timestamp: (1586277366, 1)
2020-04-07T16:36:20.197+0000 I  RECOVERY [initandlisten] WiredTiger recoveryTimestamp. Ts: Timestamp(1586277366, 1)
2020-04-07T16:36:20.205+0000 I  STORAGE  [initandlisten] Starting OplogTruncaterThread local.oplog.rs
2020-04-07T16:36:20.205+0000 I  STORAGE  [initandlisten] The size storer reports that the oplog contains 260431 records totaling to 5145508040 bytes
2020-04-07T16:36:20.205+0000 I  STORAGE  [initandlisten] Sampling the oplog to determine where to place markers for truncation
2020-04-07T16:36:20.207+0000 I  STORAGE  [initandlisten] Sampling from the oplog between Apr  6 14:15:08:222 and Apr  7 16:36:06:1 to determine where to place markers for truncation
2020-04-07T16:36:20.207+0000 I  STORAGE  [initandlisten] Taking 981 samples and assuming that each section of oplog contains approximately 2654 records totaling to 52436838 bytes
2020-04-07T16:38:48.156+0000 I  STORAGE  [initandlisten] Placing a marker at optime Apr  6 14:15:16:84
…..
2020-04-07T16:38:48.156+0000 I  STORAGE  [initandlisten] Placing a marker at optime Apr  7 16:29:31:13
2020-04-07T16:38:48.156+0000 I  STORAGE  [initandlisten] WiredTiger record store oplog processing took 147951ms
2020-04-07T16:38:48.160+0000 I  STORAGE  [initandlisten] Timestamp monitor starting

The problem is when WiredTiger starts to count samples from oplog. I decreased oplog size to 5GB, and still has a lag when starting, more than 2 minutes. It was above 5 minutes when max oplog size was 25GB.
Before upgrading to 4.2, oplog size was also 25GB, but startup was instant.
Do you have any idea what could cause slow startup? Thanks.


texts: Is there a way stop the "." (period) acting as a word breaker in FullText Indexes?

texts:
I know the period is a word breaker however I want to be able to search the fulltext catalog for a field containing the phrase "ASP.Net" or similar.
Currently my search returns 'ASP' and 'Net' without them occurring consecutively in the text. I want to know if there is a way to stop the period (.) acting as a word breaker for specific searches?

My research so far just suggests I use a LIKE statement however this is not feasible as there are millions of rows with large text fields to search as quickly as possible.


texts: What is the best alternative for MySQL Query Cache?

texts:
The Query Cache has been deprecated in MySQL 8. Is there any better alternative for Query Cache in MySQL?
Please help.


texts: cannot modify a column which maps to a non key-preserved table

texts:
Hello everyone am trying to insert data into base tables via a view that joins more than two tables. I have an instead of trigger that should insert the data into the tables, not sure where have gone wrong on the code. The following is the codes have used.

View

CREATE OR REPLACE FORCE VIEW "NEW_APPLICANT" ("FIRST_NAME", "LAST_NAME", "ADDRESS",
     "MATH_SCORE", "ENGLISH_SCORE", "SCIENCE_SCORE") AS
select APPLICANT.FIRST_NAME as FIRST_NAME,
       APPLICANT.LAST_NAME as LAST_NAME,
       APPLICANT.ADDRESS as ADDRESS,
       RESULT.MATH_SCORE as MATH_SCORE,
       RESULT.ENGLISH_SCORE as ENGLISH_SCORE,
       RESULT.SCIENCE_SCORE as SCIENCE_SCORE
from   RESULT RESULT, APPLICANT APPLICANT
where  APPLICANT.APPLICANT_ID=RESULT.APPLICANT_ID
/

Trigger

create or replace TRIGGER application_new_insert
   INSTEAD OF INSERT ON NEW_APPLICANT
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     INSERT INTO APPLICANT
       (FIRST_NAME, LAST_NAME, ADDRESS)
     VALUES (
     :new.FIRST_NAME,
     :new.LAST_NAME,
     :new.ADDRESS);
   INSERT INTO RESULT ( MATH_SCORE, ENGLISH_SCORE, SCIENCE_SCORE)
   VALUES (
     :new.MATH_SCORE,
     :new.ENGLISH_SCORE,
     :new.SCIENCE_SCORE);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END application_new_insert;

Table definitions. Table applicant:

CREATE TABLE "APPLICANT"
( "APPLICANT_ID" NUMBER NOT NULL ENABLE,
  "FIRST_NAME" VARCHAR2(45) NOT NULL ENABLE,
  "LAST_NAME" VARCHAR2(45) NOT NULL ENABLE,
  "ADDRESS" VARCHAR2(35),
  CONSTRAINT "PK_APPLICANT_ID"
    PRIMARY KEY ("APPLICANT_ID") ENABLE
) /

CREATE OR REPLACE TRIGGER "TR_APPLICANT"
before insert on APPLICANT
for each row
begin
  if :NEW.APPLICANT_ID is null then
    select APPLICANT_ID_SEQ.nextval
    into :NEW.APPLICANT_ID
    from dual;
  end if;
end; /

ALTER TRIGGER "TR_APPLICANT"
  ENABLE /

Table result:

CREATE TABLE "RESULT"
( "RESULT_ID" NUMBER NOT NULL ENABLE,
"APPLICANT_ID" NUMBER NOT NULL ENABLE,
"MATH_SCORE" NUMBER NOT NULL ENABLE,
"ENGLISH_SCORE" NUMBER NOT NULL ENABLE,
"SCIENCE_SCORE" NUMBER NOT NULL ENABLE,
CONSTRAINT "PK_RESULT_ID" PRIMARY KEY ("RESULT_ID") ENABLE
) /

ALTER TABLE "RESULT"
  ADD CONSTRAINT "FK_RESULT_APPLICANT_ID"
    FOREIGN KEY ("APPLICANT_ID")
    REFERENCES "APPLICANT" ("APPLICANT_ID")
  ENABLE /

CREATE OR REPLACE TRIGGER "TR_RESULT"
before insert on RESULT
for each row
begin
  if :NEW.RESULT_ID is null then
    select RESULT_ID_SEQ.nextval
    into :NEW.RESULT_ID
    from dual;
  end if;
end; /

ALTER TRIGGER "TR_RESULT"
  ENABLE /


texts: Error when restoring SQL Server 2008 R2 backup on 2012

texts:
I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the original DB from where the DB was backed up is no longer available. Any suggestions on how I can restore the DB to 2012 ?

  System.Data.SqlClient.SqlError: Read on *** failed: 13 (The data is invalid.) (Microsoft.SqlServer.SmoExtended)


texts: I need to merge two queries with condition

texts:
I have the following query:

SELECT
URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE",
URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION",
URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_COA as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.DIFFERENCE_MN as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.DIFFERENCE_ME as " MONEDA EXTRANJERA"
FROM
  TNLRUY.URYRR_FORWARDS_REC_H

WHERE
1=1
<#if BOOK_DATE??&& BOOK_DATE?trim !=''>
AND BOOK_DATE=to_date(:BOOK_DATE,'yyyy-mm-dd')
</#if>

and another query:

SELECT
URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE",
URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION",
URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.USD_BALANCE_COA as "MONEDA EXTRANJERA",
URYRR_FORWARDS_REC_H.DIFFERENCE_MN as "MONEDA LOCAL",
URYRR_FORWARDS_REC_H.DIFFERENCE_ME as " MONEDA EXTRANJERA"
FROM
  TNLRUY.URYRR_FORWARDS_REC_H

My requirment is that if I enter the BOOK_DATE then it should execute the first query, and If I leave BOOK_DATE blank then the second query will execute.


texts: addition in datetime column during insertion in mysql

texts:
i have a table with below 5 columns..timestamp is data type for timedate related column :

 Region     event_time     start_time     mid_time   end_time
 India   2013-11-03 13:00:00
 America
 Holland
Switzerland

I have source from where data for above column is coming at a particular interval..now while insertion i need to add time .

For ex. if in csv file i have event_time of 2013-11-03 13:00:00 , then for India time gets added by 5:30 ( due to GMT ) before insertion..and final inserted values should be 2013-11-03 18:30:00..same applies for rest of Regions and datetime columns..how should i do this...Also daylight saving will include for other Regions that do have this

I tried adddate , addtime functions..but this doesn't work...i tried updating after insertion , but this is not a solution i want.....any help ??


texts: Represent time interval in mysql Database


texts:
I have developed an async batch system using the Agent Jobs in SQL Server 2017. It is working really well. I have one issue with setting up the operations staff to run this. We get the following error
Only a sysadmin can specify '@output_file_name' parameter for a jobstep
The user has been set up on the msdb with the following Database role membership;
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
What it looks like happening is that the user can create (and delete) agents, but when we try and run the code then error above comes up which seems very odd.
For now I have temporarily granted sysadmin to get him working, but quite keen to revoke those right if we can resolve this.
Any help would be appreciated
Regards Ben


texts: Mongo DB Replica set Stuck at RECOVERING state

texts:
We have created a replica set and now the problem is 2 members of replica set [3 members set] are in recovering mode from 48 hours. Initially the size of recovering nodes was increasing and now even that has stopped. So in recovering nodes they are stuck after 90 GB of data with 60+ GB of local data.

How to come out of this mode ?


texts:
From what I've seen so far, when we define a table, normally we put the primary key as the first column. If there are miscellaneous columns that are common in all tables, they come at the bottom of the table definition. For example: Id (the pk) the first column and IsActive and timestamp at the bottom.

I am wondering if this practice has got more benefits other than only consistency across the database. Does the order of the columns affect the performance? How? I need to justify my opinion in the team that placing the primary keys and the common keys such as IsActive (Boolean column shows whether the record is active or not), randomly in the tables is not a good practice!

The developers create the entities in entity model in the Visual Studio 2010 and if they add a new attribute to an existing entity apparently VS does not put them in a right order. That's their excuse to overlook the order of the columns in a table. Does it affect the performance?

I appreciate your thoughts.


texts: FileStream files won't deleted after records deleteion

texts:
I am working in sql server 2016 with Filestream attribute column.
I have a column with filestream attribute activated on it. when I insert a reacord in TSQL , the associated file is created in Filestream container folder which is correct. but the problem is that when I delete a row , the assoicated file in filestream container remains and won't be deleted. as far as I know this shouldn't happen and the file is completely related to its record, so why doesn't it delete?

Thank you


texts:
Usually I use below query to get list of DBs which are with Percent growth or not having growth rate of 100 MB. I want to automate this process; How do I get alert when ever any developer change database growth rate value to any from our default configurations ?

SELECT DISTINCT
            db.name,
            growth,
            is_percent_growth
  FROM      sys.master_files mfile
            INNER JOIN sys.databases db ON mfile.database_id = db.database_id
  WHERE     ( growth <> 12800
              OR is_percent_growth = 1
            )
            AND db.state_desc = 'ONLINE'
            AND db.is_read_only = 0
            AND ( db.database_id > 4
                  OR db.name = 'Model'
                )
  ORDER BY  name


texts:
How can this be re-written? somehow not able to make it right. In case statement THEN and ELSE are the same
would like to change it to a single statement.
SELECT    t.status_id status_id, (
CASE WHEN t .status_id NOT IN (SELECT     status_id  FROM  firm_return   WHERE      enabled = 'D' and firm_id=t.firm_id)

THEN (cl.category_code + '-' + s.status_name)   ELSE cl.category_code + '-' + s.status_name END)  AS status_type
   FROM       bla bla


texts: Why do some applications use implicit transactions in SQL Server?

texts:
I have run into several applications that were written for a one dbms and then ported to SQL Server, and many use implicit transactions--which oftentimes really make it difficult to manage on the SQL side of things.

I came across Kendra Little's website and post here, and she seems to see the same thing:

https://sqlworkbooks.com/2018/02/when-should-i-use-explicit-transactions-for-single-statements/

  Implicit transactions
  Implicit transactions are a bit weird, and I typically only run into them when applications have been written for a different relational database and then ported to SQL Server.

My question is why? What benefit do implicit transactions provide to the application? It seems like extra work and makes things more difficult all around.


texts: AlwaysOn: Can reads on secondary block DDL on primary

texts:
We have a db that is replicated on a secondary in an AlwaysOn Availability Group. Doing some schema migrations proves very hard. We have read requests on the synchronous secondary replica basically 24/7. Schema migrations are only applied during deploy. On larger tables schema changes seem to cause a lot of blocking, which lead us to not applying some of our changes. Obviously this is not a desirable state, as the database schema diverges from the schema that we want (and use in our testing and development db).

We got the advice to disable ReadOnly connections to the secondary replicas for the schema migrations. However I am unsure if those can affect the schema changes at all. From what I have read, I would guess that it doesn't, because reads at the secondary should not block DDL queries on the primary. Could someone explain, what the actual consequences are, if any?


texts:
I have found out how to find all dependencies on a table.

However if I just wanted to know where this primary key is used, how would I do it?

I believe we use SQL Server 2005.


texts: Choosing A PostgreSQL Authentication Method For A Large Course

texts:
I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone.

I would like students to be able to do the following, and (of course) have their accounts reasonably secure from attack:

Use psql on the same machine where the database server runs.
Use pgAdmin on their local (probably Windows-based) machine.
Write JDBC applications that run from their local machine.
Write JDBC webapps that run through apache on the same machine where the database server runs.

There are many authentication methods available (see http://www.postgresql.org/docs/9.2/static/auth-methods.html), but none seem to meet all of my requirements well.

Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the peer mechanism.
Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option A is what I have always used myself, and it would be my preference.  But I believe that it would rule out accessing the database from any client other than psql running on the same machine.

Option B seems to be the most flexible.  But it also seems terribly ugly for students to need to set and maintain passwords in two disparate systems.

Option C would only allow connecting from clients on remote machines, which is not really acceptable.

I am fairly unfamiliar with GSSAPI / Kerberos, but it does not really sound like what I want either.  My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.  Is there some better option for my requirements than B above?


texts: Should I create a separate table for geo-location points?

texts:
I am developing the backend for a service that resembles Uber in some aspects (just a simplification to give you the general context). Users will book rides and as such users will define the pickup and drop-off locations. These locations will be addresses in a city (with the possibility of multiple cities in the future). The locations I will receive from the frontend/app will be in the form of latitude, longitude, and google-maps-verified address.

I am wondering whether I should store the locations as a separate table, or whether I should embed the locations in whatever table I need them. For example, the bookings table will have a pickup location and a dropoff location. Should these be just references to a locations table, or should I add more columns to store the location right there? I will need at least 3 columns per location (lat, long, address string), with the possibility to have more columns if we decide to break the street address into more components.

The way I see it the two approaches trade-off memory and time performance:

A separate table will save some space, as we avoid copying the same
information multiple times. But I am not sure how much space will be
saved as we do not have enough real data yet. My ballpark estimate is that a medium-sized city has tens of thousands unique addresses. So if we take 100 ride bookings there is little chance for overlap. If we have 100,000 ride bookings there is bound to be considerable overlap. However, space is not really a limiting factor in our design, even with millions of bookings. It does not really matter if we spend 100MB more in our database.
A separate table will make processing slower. First of all, each time we get a booking request we will have to search if the locations in the request already exist in our database. If not, we add them, otherwise we use the existing reference. With potentially tens of thousands addresses in our database this will take some time. Is this extra time significant compared to our other time consuming tasks? An example of another time-consuming task is a call to the google-maps api to find routes from one location to another and get a travel time estimate. I believe this operation will take longer that searching in our database. So it might make little sense optimising the database access when we have other bottlenecks. How about other operations? Say we want to find bookings within a bounding [lat, long] box. This can become more time-consuming with a separate table as we'll need a full table join. Or do a cumbersome two-step search where we find the locations IDs in our table that are within the bounding box and then search the bookings table with these IDs. If we have hundreds or thousands locations in a bounding box this might not be a good way to do it. But again, I am not sure about the utility of trying to optimise this operation. I am not sure how often will I need to search by location. It seems not that often, so I will not gain/lose much one way or the other.

It seems to me that there are no strong arguments based on the space-time tradeoff. Are there other aspects I should consider? For example: clarity of design, ease of development, flexibility and expandability. The separate table approach seems to offer more structure and the opportunity for more flexibility if the model needs to change. For example, it might be beneficial to break the street address in multiple components (street, city, state). We are less likely to do this if we are to use location columns in other tables (we need to use at least 3 columns already, and breaking the address in 3 components, brings this number to 5). About flexibility: in the future, we might have multiple-point rides. Having a location table will help with that.

I am leaning towards a separate table, but I do not see strong arguments for or against it. Are there other arguments that I am missing?

Not sure if the following details are relevant but I will be using postgresql as the RDBMS and sqlalchemy as an ORM to query the database.


texts: "Display width out of range for column" error when importing mysql 5.1 dump into 5.5

texts:
I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server.

I created a dump with

mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql

When I try to restore it on the new server, I get an error:

mysql -u root -p dbname < dbname.sql
ERROR 1439 (42000) at line nnnn: Display width out of range for column 'ID' (max = 255)

The col. is defined as ID bigint(1536).

Edit:  The table it is attempting to restore is actually a view.


texts: how much innodb_buffer_pool_size need to keep under my.ini file?

texts: I have Windows Werver 2012 R2 with installed physical memory 672GB. Can you please suggest innodb_sort_buffer_size?


texts: Count total number of nested one to many relationships

texts:
Tables: Users, Costs, Payments

Users can have many costs. Costs can have many payments

I want to get information about the user AND count of all payments that they made

This query returns result but takes very long time to run

SELECT u.id, u.name,
  (
    SELECT COUNT(p.id)
    FROM payments AS p
    WHERE p.cost_id IN (SELECT cost_id FROM costs WHERE user_id = u.id)
  ) AS payments_made
FROM users as u;

I tried outer joins but then it return duplicate if user has more than one cost

Inner join dropped all users that didn't have a cost


texts:
I'm trying to understand EXPLAIN on Redshift. I'm in a case were I have data like this

id | user_id | created_at
---|---------------------------------
1  | 1       | 2017-02-08 14:32:10.96
2  | 1       | 2017-02-07 14:32:10.96
3  | 2       | 2017-02-06 14:32:10.96
4  | 2       | 2017-02-05 14:32:10.96

I want :

id | user_id | created_at
---|---------------------------------
1  | 1       | 2017-02-08 14:32:10.96
3  | 2       | 2017-02-06 14:32:10.96

I have this two queries :

SELECT id,
       user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM my_table) x
WHERE rownum = 1;

With the EXPLAIN  have :

XN Subquery Scan x  (cost=1000001263779.68..1000001513986.60 rows=50042 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=1000001263779.68..1000001388883.14 rows=10008277 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Sort  (cost=1000001263779.68..1000001288800.37 rows=10008277 width=16)
              Sort Key: user_id, created_at
              ->  XN Seq Scan on my_table  (cost=0.00..100082.77 rows=10008277 width=16)

Then the other query :

SELECT ac1.user_id, ac1.created_at FROM my_table ac1
JOIN
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM my_table
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE;

And the EXPLAIN :

XN Hash Join DS_DIST_NONE  (cost=150798.74..771939079.62 rows=7257 width=16)
  Hash Cond: (("outer".created_at = "inner".maxdate) AND ("outer".user_id = "inner".user_id))
  ->  XN Seq Scan on my_table ac1  (cost=0.00..100082.77 rows=10008277 width=16)
  ->  XN Hash  (cost=150606.01..150606.01 rows=38548 width=16)
        ->  XN Subquery Scan ac2  (cost=150124.15..150606.01 rows=38548 width=16)
              ->  XN HashAggregate  (cost=150124.15..150220.52 rows=38548 width=16)
                    ->  XN Seq Scan on my_table  (cost=0.00..100082.77 rows=10008277 width=16)

Result are a little bit slower with the first query but I'm lost when I try to understand EXPLAINs.
It seems that the cost is higher on the query that use ROW_NUMBER() but that's it same with rows.

But what can I extract from those EXPLAINs (I sadly cannot use ANALYZE on Redshift)?


texts: Does any kind of constraint in a database have a unique constraint name?

texts:
I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the constraints. If the programmer adds these constraints when the table is created (without specific add constraint syntax) then the Database Engine gives a name to these constraints that is comprised of random characters. The name that the database engine gives to these 'non-explicit' constraints depends on the implementation.

However my question is: Does ANY kind of constraint have a specific name in the database engine? Even the NOT NULL constraints ? Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?


texts: Using Workbench to export. Views no longer work

texts:
I'm using Workbench 5.2 from my production DB machine (Ubuntu Linux).  I've been trying to export.  First try.. I had the entire schema selected including all tables and views in the right hand window.

When I imported this data into my test machine all the views came in as tables.  Obviously not what I want because they don't return any data now.... and it's now a table.

But this is where I'm confused.  I can see how to export just the tables.  But how do I get the views exported and into the test machine?


texts: Why does mysql adds a default timestamp on insert/update even if I did not specify anything?

texts:
I have a table that is as follows:

CREATE TABLE SomeTable (
    id int unsigned NOT NULL AUTO_INCREMENT,
    status enum('broken','repaired') NOT NULL,
    from datetime NOT NULL,
    until datetime NOT NULL,
    brought_in timestamp,
    brought_by varchar(255) NOT NULL,
    repair_status enum('unpayed', 'payed') DEFAULT NULL,
    payed_when timestamp,
    delivered_when timestamp,
    primary key(id)
);

When I create the table if I do a SHOW CREATE TABLE then I see that the column brought_in is defined to have the current timestamp ON INSERT and ON UPDATE.
But I did not add such an instruction in my create table.
Why doesn't it let it be empty like the other columns?
Now on every insert or update of another column it adds the current timestamp. I don't want that.
It does not do that with the other columns. What am I doing wrong?


texts: pt-online-schema-change -- can't get it to stop quitting w/ "Threads_running=108 exceeds its critical threshold 100"

texts:
I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this:

pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX.XX.XX.XX" --max-lag 10
--execute --pass PASS
--alter "REORGANIZE PARTITION max INTO (
PARTITION p19 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ),
PARTITION p20 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ),
PARTITION max VALUES LESS THAN maxvalue
)"

No matter what settings I use, I get the Threads_running issue. I have also tried:

--chunk-size 100 (also tried 20)

--max-load="Threads_running=20"

Shouldn't it just wait until threads running gets lowered? Why does it keep quitting? I could raise the critical load to allow 200 threads, but I'm afraid of the server crashing.

Any advice?


texts: Generating an ER Diagram from SQL CREATE TABLE statements

texts:
As I can't find any ER diagram feature built in with Apex, I want to create one with the SQL statements I executed in order to set up the tables in my Apex Workbench.

I found schemaSpy, which is capable of generating diagrams from many database architectures, but it can't connect to Apex. (AFAIK)

Most of the other tools I found are doing the opposite of what I want - they let you design your scheme in a nice user interface and generate the according SQL statements afterwards.

As it was mentioned in the comments (and in other questions on stackexchange), it's possible to connect to Oracle Apex through ODBC, generating a diagram with some tools, i.e. Microsoft Visio.

Maybe I have to do it this way, but I was curious, if it's possible even without an existing database scheme; just SQL.

Is there a free (better: open source) tool which analyzes given CREATE TABLE-statements, including foreign keys, to an entity relationship diagram?


texts: Using COUNT() implicitly for making a view over game match data?

texts:
I've not used SQL in quite a while and I'm pretty rusty it seems. I've had to do a project recently and needed to use MySQL on Windows and when looking for solutions to this particular query, I get told that you can use COUNT(*) implicitly somehow, but I can't get it to work right.
What I need is something like:

PLAYER_ID
MATCHES_WON
MATCHES_LOST

0
4
1

1
2
5

2
2
2

I have made a simple SQL fiddle of my database here.
Could someone help me out with this? I'm willing to accept that I might have to make a more involved solution with some CURSOR and LOOP but thought I'd ask here. It's my first question on this community, so sorry if I didn't format this right or it wasn't an appropriate question.


texts: How to quote arrays in postgres copy statement to inser into JSON field

texts:
I have some data that looks like this:
id,bo,c,url
1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]"
2, "more text", 83, "[{'id': 'W3nAl', 'u': 'https://anotherwebsite.com'}]"
3, "even more text", 14, "[{'id': 'CyrMj', 'u': 'https://yetanotherwebsite.com'}]"

I'm trying to insert this data into a table that looks like this:
CREATE TABLE myTable(
id integer,
body varchar,
count, varchar,
url JSON
);

Using this command:
\copy mytable FROM 'myData.csv' WITH DELIMITER ',' csv header;

However, I'm having trouble with the single quotes in the url field of the csv. I'm getting errors like this:
ERROR:  invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: [{'...

Can someone show me the correct way to structure my \COPY command so as to be able to load the url field in the csv as a JSON?
If necessary I can do some pre-processing of the file at the command line.


texts: Is there a way to store a PostgreSQL database directly on a block device (not filesystem)?

texts:
I am reading about where PostgreSQL stores its data it needs to offer me its service (here: http://www.postgresql.org/docs/8.4/static/storage-file-layout.html )

As it is writen there PostgreSQL's data and storing seems to work on top of the respective filesystem in which I store the PGDATA folder. In this folder again the data is stored in a set of individual files (i.e. also files per database).

Question:
While I can get along with such an "in-filesystem-setup",

I wonder if it is possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage?

I would think that this would offer some advantages:

the blocksize configuration and optimization to fit database to filesystem needs no longer be undertagen
overhead in the filesystem can be avoided
backup can be simpler

If not for PostgreSQL are there database applications known that do not rely on keeping its data as files filesystem, but use block devices directly?


texts: Numbering a rows positional index relative to its siblings within a parent context


texts: Changing the port of the postgresql server for security reasons

texts:
For security reasons, it is common to change the port of Microsoft Sql Server.
The subject is discussed here in Is changing the SQL Server port really that much safer?

my question is: does this apply also to PostgreSQL ? Or is it unrelated ?


texts: Normalization of library book table with multiple authors

texts:
I have a database structure with two tables:

The first one is called Author, has two columns (AuthID, AuthName) and its Primary Key is (PK_AuthID).
The second one is called Book and has three columns (BookID, BookTitle, AuthID). Its Primary Key is (PK_BookID), and has (FK_AuthID) defined as a Foreign Key that referes to Author table.

The question is: If one Book has more than one Author, what is the best design pattern to follow in order to maintain the second Author, and at the same time keep the Book table without NULL values?


texts: what are the checklist when looking at MySQL database for first time?

texts: What should I put on my checklist when I look at MySQL database for the first time? I would appreciate your help. Thanks.


texts: sqlcmd is running but database shown no running query

texts:
We are facing this weird issue on our Azure setup. The database is SQLServer and client is CentOS.
We fire queries using sqlcmd. Sometimes it happens that you see the sqlcmd running, but there is no runnable/sleeping query running in db ( seen using sp_who2 ). This happens only for DML queries, for the queries of the sort insert into < sometable > select < someslectquery >.
And this happens like 25% of time. This occurs even when we run some procedure. The procedure stops at some insert .. select query. But sqlcmd still show s it running.
We have another setup with AWS with same database and same Centos, but we haven't seen this thing happening there.
On same azure database, when we run the queries using windows machine using SSMS then the query completed on back-end. We tested this multiple times.
Has anyone face any such thing? Where to look for the issue?


texts: Possible reasons for slow BLOB updates in Oracle?

texts:
We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. I've run some tests on our database and there is some lag, but more like 0.8 seconds. The user has an Oracle 9i R2 database. I've run tests on 11g, 10g and 9i and there doesn't seem to be any performance difference between them for me, so I'm guessing it's not because they're running an older database.

Here is the query that's being used:

UPDATE RESULTS_DATA
     SET IMAGE_BLOB=:myBlob
WHERE MEASUREMENT_INDEX=:mIndex
  AND BATCH_ID=:batchId
  AND SYSTEM_ID=:sysId

There are indexes on SYSTEM_ID and BATCH_ID but not MEASUREMENT_INDEX. The problem doesn't appear to get worse when you have large amounts of data in the table.

Can anyone think of a reason that BLOB transfers would be so slow? Is there any way of optimising the table structure to make writing to BLOBs more efficient? Could it just be a case of a slow network? If so, how could I diagnose this?

Thanks!

Here's the table structure:

CREATE TABLE RESULTS_DATA (
SYSTEM_ID       VARCHAR2(15),
BATCH_ID        VARCHAR2(15),
USER_ID         VARCHAR2(40),
MEASUREMENT_INDEX   NUMBER,
IMAGE_BLOB      BLOB,
CONSTRAINT DATA_PKEY PRIMARY KEY (MEASUREMENT_INDEX,SYSTEM_ID,BATCH_ID))
TABLESPACE MEASUREMENT_DATA
LOB (IMAGE_BLOB) STORE AS (TABLESPACE IMAGE_DATA);

CREATE INDEX RESULTS_INDEX ON RESULTS_DATA(SYSTEM_ID,BATCH_ID) TABLESPACE MEASUREMENT_DATA_INDEX;

Here's the output of EXPLAIN PLAN from Oracle SQL Developer:


texts: how to select the not null value of a column when it has both null and not null value in teradata?

texts:
consider a table named "TABLE-A" with below data.
ID   hcc    D_CD
1    null   1
1    5      1
2    null   2
2    10     2
2    20     3
3    null   3

I need to select ID,hcc fields from TABLE-A in the combination of ID,D_CD.
when the combination has single hcc value (even it is null) it should be fetched.
But, when it has both null and not null values, it should fetch the not null value only.
So the output must be as below.
OUTPUT:
ID   hcc
1     5
2     10
2     20
3     null

can anyone please help me on this? thanks in advance.


texts:
The documentation claims that snapshots taken by ops manager are delta (differential) between successive snapshots.

How do you then restore?
I mean if you are able to delete some snapshots according to
retention, how do you connect the dots in between to restore to a
point in time.
What do they mean by base snapshot? Is that whole backup?
How can a given snapshot become full and ready for restore if any
snapshot before it is deleted?

I understand one can replay oplog to go forward to a point in time from a given snapshot. This question is if any snapshot before the snapshot where restore begins gets deleted how can you restore?

From MongoDB docs:

"...The snapshot only stores the differences between successive snapshots. Snapshots use only a fraction of the disk space required for full snapshots."

Any help is much appreciated.


texts:
I haven't spent much time with SQL Server, and I can't seem to figure something out.

I have 3 tables; user, usergroup, group.

usergroup is a table that contains both a group id and a user id, where the group table just contains it's id and a name column.  I am attempting to add a new row in the usergroup table for lets say if the user is already paired with name = x in the group table I want to also pair him with y in the group table.

I'm sorry if that doesn't make too much sense I am not really sure how to explain it but the query I've come up with so far is:

 Insert into usergroup (user_id, group_id, default_group)
 Select user_group.user_id from [user_group]
      join [group] g
      on user_group.group_id = g.id
      where g.name = 'someName';

This should return all the user ids that I want to add a specific group_id/default value to, but I am unsure how to insert them into the table I tried to do something like VALUES ( (Select statement above), "group_id_I_want_to_specify, 'false'));


texts: Create connections in sqldeveloper. 12505 error

texts:
I have oracle 12c installed in my ubuntu 15.10, I can run sqlplus with

sqlplus / as sysdba

and run it in a CLI. But I want to be able to use it with sqldeveloper.

cat $ORACLE_HOME/network/admin/tnsnames.ora

shows

# tnsnames.ora Network Configuration File: /opt/Oracle/Database/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRAKASHDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 9250))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PrakashDB)
    )
  )

and

 cat $ORACLE_HOME/network/admin/listeners.ora

has

# listener.ora Network Configuration File: /opt/Oracle/Database/network/admin/listener.ora
# Generated by Oracle configuration tools.

PRAKASH =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 9250))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC9250))
    )
  )

But when I try to create connection I get the following error.

How can I fix this?


texts: Call Back In Union All Within the With-As-Table Itself

texts:
ALTER PROCEDURE [dbo].[A]
AS
BEGIN
WITH SplitSting1
AS(
Select id, NameString, emp
  ,LEFT(NameString,CHARINDEX(',',NameString)-1) As Head
  ,RIGHT(NameString,LEN(NameString)-CHARINDEX(',',NameString))  AS Remainder
From [dbo].[Table_1]
WHERE NameString IS NOT NULL AND CHARINDEX(',',NameString)>0
Union ALL
Select id, NameString, emp
    ,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
    ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
From SplitSting1
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
)
Select * from SplitSting1 order by id option (maxrecursion 0);
END

The first image is the table I query.
The second image is the output I run my stored procedure.

Can anyone assist to explain how the code is running to generate the output seem like split the NameString/Remainder Column into Head Column. How is the algorithm work?


texts: SQL Server 2005 Easy way to add all users to a database role

texts:
I have three application databases: A, B and C. Users of A and B are all users in C; the users from A are in the public role with only SELECT permission, while the users from B are in a role with more permissions. Due to some recent changes, all of the users on C need to be in the other role and not just public. Since this will go out as a database patch and be executed automatically on 150+ databases, I would prefer it to be a simple, blanket script...i.e., a single statement that says "put all users in this role". I know that I can use the script below to create exec statements and loop through the results and execute them, but I'm hoping for something a bit more streamlined. I've searched around all over the place and can't find anything, but there's always hope for some undocumented system stored procedure that someone knows about.

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id
WHERE roles.name = 'TheNewRole'


texts: Nearest neighbours (KNN) from a selected point

texts:
I have a small point dataset, I perform the following I get the 10 KNN from the coordinate. How do I first select a point from the dataset and with that select the 10 nearest neighbours (not including the original point - if possible)?

SELECT spatial_id, geom
FROM rhp_tvp.spatial
ORDER BY geom <-> 'SRID=23033;POINT(280100 4683200)'::geometry
LIMIT 10


texts: How to compare MySQL privileges granted to privileges used?

texts:
I have inherited a MySQL server that has a user with far too many privileges granted but I believe not all of these are required.

Is there any way to compare the MySQL privileges granted to the MySQL privileges actually used?


texts: Amazon Redshift "cache lookup failed for aggregate" error when using median aggregate

texts:
I recently ran into this error message and am posting an answer here so that the solution is easily searchable in the future.

Problem: The following query returns an error message when run on Amazon Redshift. What does it mean and does Amazon Redshift support the 'median' aggregate?

dev=# select median(5);
ERROR:  cache lookup failed for aggregate 2762


texts: How can I speed up "show columns" on MySQL?

texts:
My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower.

The database contains about 250 databases, with 100 to 200 tables per database (about 20k tables in total).

How can I find out why these operations are so slow?
Is there perhaps some setting I can change to make it run faster, or to cache it SQL-side?

(The application does about 14 such queries per page load - I'm well aware that this legacy code needs to be cleaned up, but looking for possible options while I work on the long-term fix.)


texts: Cannot Create or Drop User in MySQL Workbench

texts:
OK. I have set up a new MySQL Server in AWS Ubuntu EC2 and migrate databases from the RDS. After importing the tables, stored procedures and functions into the new server, I encountered the attached error.

This happens whenever I want to create or drop users, or if I modify the stored procedure and apply changes. For stored procedures or functions, if I continue to apply changes second time, it would work.

I tried to delete those users in my Workbench but the pop-up error prevents me from doing that.

I think these users were created when I import those stored procedures with those definers.

Anyone could help?


texts: Why shouldn't user-defined database roles be members of fixed roles?

texts:
I'm redesigning security setup in our database. I was in process of defining certain user-defined roles and - as I find it handy - on the other monitor I was browsing documentation to make sure I don't miss anything.

And in this resource from the Microsoft Docs I noticed the following:

  Do not add user-defined database roles as members of fixed roles. This could enable unintended privilege escalation.

Now - either I'm tired or there is no good explanation for this tip.

In fact I find it common to define specific role and ensure they have proper permission through fixed role membership.

Simplified examples:

"power user" = db_datareader + db_datawriter
"developer" = db_datareader + db_datawriter + ddladmin
"function designer" = db_datareader + GRANT CREATE FUNCTION on database:xyz

etc.

It's just handy.

I'm putting this security setup on hold - hopefully someone can give me some good pointers here.


texts: JDBC can't use the current primary database by setting multiple hosts

texts:
node1: 192.168.0.1 as primary
node1: 192.168.0.2 as standby

The JDBC string is setting as this:

<url>jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb</url>

Using repmgr doing the repliation and automatic failover.

First, use node1 as primary:

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 7        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | node1    | default  | 100      | 7        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

If the node1 down, it can be change to node2:

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | primary | - failed  |          | default  | 100      | ?        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 8        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

The application can run well at this timing.

But if recovery node1 manually:

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | standby |   running | node2    | default  | 100      | 8        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 8        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

It seems the application will try to connect node1 again, then as it is read-only mode, can't insert new data. So should change the JDBC connect order as:

<url>jdbc:postgresql://192.168.0.2:5432,192.168.0.1:5432/mydb</url>

Let node2 at first place. Restart the application. It works.

Even tried adding params to the connect string base on the original way(node1, node2 order):

<url>jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/mydb?targetServerType=master&loginTimeout=10&connectTimeout=10&tcpKeepAlive=true</url>

The application looks lost data and will create data again.

So is the chaning order method the only way in this case? Can't it choose current primary database correctly?


texts:
On my dev box I sometimes need to run very IO intensive queries such as index builds and CHECKDB. This can put so much load on the disk that it hardly can process anything else. This causes enormous lagging in other programs. For that reason I sometimes need to suspend sqlservr.exe using Process Explorer.

If I do that for longer periods of time I sometimes get IO timeouts such as

  Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000000160673C40, type 4, Task 0x000000013BBD5C28 : 22, waittime 300 seconds, flags 0x1a, owning task 0x000000010F91F088. Continuing to wait.

  SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [....ndf] in database id 5.  The OS file handle is 0x0000000000000FDC.  The offset of the latest long I/O is: 0x00000e07c60000

  A time-out occurred while waiting for buffer latch -- type 2, bp 0000000200E9FD80, page 1:30481, stat 0x2040d, database id: 20, allocation unit Id: 72057594068533248, task 0x00000001CCE93088 : 13, waittime 300 seconds, flags 0x1a, owning task 0x00000001CC0FBC28. Not continuing to wait.

This sometimes causes the query to abort. How can I disable IO timeouts on my dev box?

Also, in case the query does not abort I sometimes get dump files. How can I disable those?


texts: How to change the default initial and next for new partitions in oracle

texts:
This is kind of a follow up on this question :
How to change initial extent of an existing partition
I have a table that was created with an initial extent of 8M for new partitions and this takes too much space. Using the answer above I am able to resize already created partitions to something that is OK, but newly created partitions still get an initial extent of 8M. Is it possible to change this without re-creating the table ?
Edit : my table looks like this :
    CREATE TABLE MY_EVENTS (
      EVENT_ID NUMBER            NOT NULL,
      EVENT_DATE     DATE              NOT NULL,
      DESCRIPTION    VARCHAR2(2000 BYTE),
      CONSTRAINT TAUDIT_TRAIL_STA_PK PRIMARY KEY (EVENT_ID) USING INDEX TABLESPACE USR_D1 STORAGE (INITIAL 64 K
                                                                                NEXT 1 M
                                                                                MAXEXTENTS UNLIMITED)
    )
    TABLESPACE USR_D1
    PARTITION BY RANGE (EVENT_DATE)
    INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
    (
    PARTITION P_FIRST VALUES LESS THAN (DATE'2013-06-01'),
    PARTITION VALUES LESS THAN (DATE'2013-10-23')
    );

Edit 2
This is what I have been trying :
ALTER TABLE MY_EVENTS
     MODIFY DEFAULT ATTRIBUTES FOR PARTITION P_FIRST INITIAL 65536;

but I keep getting this error :
ORA-14121: MODIFY DEFAULT ATTRIBUTES may not be combined with other operations

Also, I am using Oracle 12.1
Thanks !


texts:
First i connect to oracle remotely, then open pl/sqldeveloper create one table. Then i am trying to import csv file to PL/sql developer. Importing process starts normally, but every day midnight we face

  ORA-03135 connection lost contact.

The file is almost 3 GB containing 20 million records.

the importing process stop after 10 million records.


texts: MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS?

texts:
I'm trying to run some load tests on my MySQL server and check a bunch of relevant GLOBAL STATUS variables after each run.  Is there an easy way to flush GLOBAL STATUS variables like the regular FLUSH STATUS command?

Restarting the server seems to be one way of accomplishing the same thing but I'd prefer something faster.


texts: ODBC - how to set up ODBC driver for 2 languages (for different code page)

texts:
How to set up an ODBC driver to operate the Polish code page (1250) and Lithuania (1257) at the same time? Although the code page Lithuanian is wider than the Polish code page, the driver incorrectly passes data to the database.

Database: MS SQL 2005 Standard
OS: Windows


texts: MySQL 5.6 on OS X 10.8.2 disconnects on user creation?

texts:
Via Homebrew I just upgraded from MySQL 5.5.29 to 5.6.10 and all my queries and existing users are working fine, but whenever I try to modify permissions or create users now MySQL disconnects me. Are there any my.cnf settings I need to change for 5.6, or system table updates I need to do etc?

mysql> create user 'something'@'localhost' identified by 'password';
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> create user 'something'@'localhost' identified by 'password';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> SHOW VARIABLES LIKE "%version%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.2.10              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.10              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | osx10.8             |
+-------------------------+---------------------+
7 rows in set (0.00 sec)


texts: Is this product table schema good or worth editing?

texts:
I haven’t created such structures before and decided to ask the experts. Is this a good structure?
I am concerned about the following questions:

I created the "product_size" table separately, because some products do not have dimensions.
The "product_prefix" table is a boolean value for displaying icons on the product card.
Table "product_gallery", is it correct or worth transferring these values to the table "products"?
Table "product_characteristics", I haven’t come up with anything else how to store an array with json data of the form
[{"key": "value"}]


texts:
Need: To store / accesses dbTable data from a separate drive not the default

I was using MySQL 5.7 so far & as specified in the solution - Link1 I added the my.ini file with the info as stated & it worked fine.

I now uninstalled MySQL 5.7, installed MySQL 8, created a my.ini file & specified the info as stated in this solution Link2. But when I check it still points to the default C:\ drive & hence unable to access data from the existing database stored in E:\ drive

SHOW GLOBAL VARIABLES LIKE 'datadir';

shows

C:\ProgramData\MySQL\MySQL Server 8.0\Data\

The new my.ini file which I have created as below in

C:\Program Files\MySQL\MySQL Server 8.0

[mysqld]
datadir = E:/MySqlData
secure-file-priv="E:/MySqlData/Uploads"

Hope the issue faced is clear, await inputs.

Edit:

I found this input - Link Could anyone let me know if this could be a reason ? Also any known hex editors for Windows 10 ?

Update: Even after removing the hex characters, the server still does not start if I point the parameter datadir in my.ini file to my database folder in another drive


texts:
Can anyone explain why SQL Agent didn't run any jobs between 04:30 and 05:24...

The previous execution at 04:30am completed in 8 seconds so it wasn't a long running job. The SQL Server was still up and accepting connection in the time between has confirmed by the SQL Log.
Checked the SQL, agent and Windows log and can't see anything.
All scheduled jobs during this time didn't run, including a replication snapshot which was scheduled for 5am but then ran at 05:24am, almost like it SQL Agent was playing catch up for the missed time!
Does this mean SQL Server can now time travel?
UPDATE #1
SQL Agent was also up during this time as well based on the following query.
    SELECT [SQL Server Start Time] = CONVERT(VARCHAR(23), a.sql_start, 121),
       [SQL Agent Start Time] = CONVERT(VARCHAR(23), a.agent_start, 121),
       [SQL Uptime] = CONVERT(VARCHAR(15),
                      RIGHT(10000000+Datediff(dd, 0, Getdate()-a.sql_start), 4)
                      + ' '
                      + CONVERT(VARCHAR(20), Getdate()-a.sql_start, 108)),
       [Agent Uptime] = CONVERT(VARCHAR(15),
RIGHT(10000000+Datediff(dd, 0, Getdate()-a.agent_start), 4)
+ ' '
+ CONVERT(VARCHAR(20), Getdate()-a.agent_start, 108))
FROM   (SELECT SQL_Start = Min(aa.login_time),
               Agent_Start = NULLIF(Min(CASE
                                          WHEN aa.program_name LIKE 'SQLAgent %'
                                        THEN
                                          aa.login_time
                                          ELSE '99990101'
                                        END), CONVERT(DATETIME, '99990101'))
        FROM   master.dbo.sysprocesses aa
        WHERE  aa.login_time > '20000101') a


texts: Set simple recovery mode and shrink log files for all user created databases

texts:
I hope you can point me in the right direction.
I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit.

I want the script to:

To select all databases, except the system DBs.
To set recovery to simple.
To shrink the log files for every db(.ldf), except the system db

The script:

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
    begin
    select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='USE @dbname checkpoint'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='DBCC SHRINKFILE @dbname.ldf'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)

    fetch next from c1 into @dbname
    end
close c1
deallocate c1


texts: how to Protect certificate in sql server

texts: I am using symmetric key encryption in SQL server but the problem is that if anyone has permissions to explore the database they can easily view certificates and symmetric keys and can therefore access encrypted data using these keys. How can this be prevented?


texts: How can I make this query more efficient?


texts:
I have a procedure TempSalesUpdateID which gets all ProductName's column in TempSales table and set it's corresponding ProductID.

ALTER PROCEDURE [dbo].[TempSalesUpdateID]
AS
UPDATE T
SET        T.ProductID = P.ProductID
FROM       TempSales T
INNER JOIN Products P
ON         P.PN=T.ProductName

When I execute it through SQL Server Management Studio it runs perfectly: when it finds a corresponding ID, it fills ProductID column, otherwise leaves it NULL.

When I run it from my C# Windows Form Application, it puts almost random numbers instead of leaving NULL (the ones which doesn't have a corresponding ID).

I say almost because: 13098 rows have value 1 instead of NULL, 14742 rows have value 2 instead of NULL, 13601 value 3, etc.

Could you please help me? I'm really lost here


texts: How to log DML statements executed by a pl/pgsql function?

texts:
I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands.

When I set log_statement = 'mod', I can see nothing on the log upon execution of the function with SELECT fnct_clear_temp_fields();.
When I set log_statement = 'all', and execute the function I can see SELECT fnct_clear_temp_fields(); in the log, but not the underlying UPDATE commands.

Is there a way to have the UPDATE commands appear in the log as well ?

For info, here is the function:

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$
DECLARE
    --Put into a cursor a view dynamically listing all user-defined fields beginning with 'temp_'
    dataset_1 CURSOR FOR
        SELECT
            column_name,
            table_name
        FROM information_schema.tables
        NATURAL JOIN information_schema.columns
        WHERE
            table_schema='public'
            AND table_type='BASE TABLE'
            AND column_name ~ '^temp_'
        ORDER BY table_name,column_name;

    --Record variable to go through each line of the view above
    dataset_1_row RECORD;

BEGIN
    OPEN dataset_1; --Open the cursor
    FETCH dataset_1 INTO dataset_1_row; --first row of the view

    WHILE FOUND LOOP
        RAISE NOTICE 'Table: %, Column: %',  dataset_1_row.table_name,dataset_1_row.column_name;

        --Set to NULL the contents of the current 'temp_' column
        EXECUTE 'UPDATE '||dataset_1_row.table_name||' SET '||dataset_1_row.column_name||'=NULL WHERE '||dataset_1_row.column_name||' IS NOT NULL';

        FETCH dataset_1 INTO dataset_1_row; --next row please.
    END LOOP; --while end

    CLOSE dataset_1;

    RETURN;
END;
$$ LANGUAGE plpgsql;


texts:
I've encrypted texts stored in sql server 2012 which I want to decrypt using sql. AES 256 bit algorithm was used to encrypt the data and I've the key used to encrypt. This encryption was done in a third party application and not in SQL Server.
Is there as way I can decrypt it?


texts: Duplicate entry error for unique key that doesn't exist

texts:
I have a model that had the following fields:

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
| CCC      | ***      |
+----------+----------+

But then I deleted the last row (CCC) and tried to insert a new one with the same username:

INSERT INTO users (username, password) SELECT 'CCC', '***'

But I got this error:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'CCC' for key 'username_UNIQUE'

And if I query for all the existent rows, I get only two rows:

SELECT * FROM users

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
+----------+----------+

I suspect this has to do with the data not syncing to disk, since in the first days, when I first created the model using MySQL Workbench, it wouldn't appear on Adminer only until after a reboot, but I have no idea as to how to force a sync on the data to disk, or even if it's the issue.

I'm using MariaDB 5.5.39 on Fedora 20 64 bit on linux 3.17.4-200 as the DBMS and InnoDB as the DB storage engine.

Update:

It looks like this happens with all and every username I try, so 'askaksjk' will also trigger the same error, but if I insert a new row without using a SELECT statement, it adds the record correctly:

INSERT INTO users (username, password) VALUES ('CCC', '***')

Does it has anything to do with the fact that the SELECT already returns a record with the same username as the one I'm trying to add?


texts:
I want to keep track of GTIDs and the corresponding mysqlbin log somewhere. I see this mysql.gtid_executed table, however on my mariadb DB, there are no rows in this table. Any idea how this gets populated ? These are the GTID related parameters. Any help is appreciated.
MariaDB [(none)]> show variables like '%gtid%';
+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| gtid_binlog_pos         | 0-10001-4866 |
| gtid_binlog_state       | 0-10001-4866 |
| gtid_cleanup_batch_size | 64           |
| gtid_current_pos        | 0-10001-4866 |
| gtid_domain_id          | 0            |
| gtid_ignore_duplicates  | OFF          |
| gtid_pos_auto_engines   |              |
| gtid_seq_no             | 0            |
| gtid_slave_pos          | 0-10001-4866 |
| gtid_strict_mode        | ON           |
| last_gtid               |              |
| wsrep_gtid_domain_id    | 0            |
| wsrep_gtid_mode         | OFF          |
| wsrep_gtid_seq_no       | 0            |
+-------------------------+--------------+


texts: SQL Server LDF is is keep growing on secondary Always on

texts:
I have configured ALways-On (Async mode) on SQL server 2017, recently LDF file has been growing on secondary (80 GB), but on the primary database it is less than 50 MB, also I have set up full back and transaction log backup on primary (every 30 minutes)

I also have shrunk LDF on primary but LDF on secondary is still growing. to me, size of LDF on primary and secondary should be equal; any thought?


texts: Single query for retrieving a category path


texts: Reclaiming unused space from datafile in oracle

texts:
Tablespace                        Used MB    Free MB   Total MB
------------------------------ ---------- ---------- ----------

 DATA_S1                               686        295        981

We have a table space named DATA_S1 which contains one data file. As we can see that around 300M space of the data file is unused. If I try to shrink the datafile by 200M by:

alter tablespace TABLESPACE_NAME datafile 'DATAFILE_NAME' resize 800M;

it gives:

ORA-03297: file contains used data beyond requested RESIZE value.

Is there anyway I can reclaim the space from datafile


texts: whether to merge partitioned table with only partially overlapping columns

texts:
I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL)  in general.

I have data with a few "cross-sectional" tables (e.g. the parents of each individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately).

I wonder if it is a good idea to merge/union the annual tables into long panels.

The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary tables or views.

However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. I would also spend time on how to merge tables when there is a (rare) type change over the years (usually strings vs. TINYINTs). The merged tables could be too big anyway: think of tables ~70 GB in some cases, while my server has 128 GB RAM. Many use cases would focus on a subset of years anyway.

Actually some links I found were about how partitioning would improve performance (at least if I can learn to code using partitioning):

http://msdn.microsoft.com/en-us/library/ms190787.aspx

How Does Table Partitioning Help?

Improve performance by partitioning

Do you have any thoughts about this? Maybe more concrete questions would be:

How cumbersome would it be (for a novice!) to code joining data from
the annual tables separately, for multiple series of tables? (E.g.
UNION ALL some columns of tax returns for 1997-2008, then join all residential
data for these 12 years year-by-year, then join employment records
year-by-year etc.)
How much slower would this run than simply selecting years from the longitudinal tables?
Shall I be worried
        about the longitudinal tables having the size on the same order of
        magnitude as my physical memory, or MonetDB handles it equally
        efficiently (it would need to access many of the small annual tables
        anyway, that also takes memory). Can at least the missing values for entire years be stored efficiently?


texts:
I have a table that is created as below.

CREATE TABLE [dbo].[Table1](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Title] [VARCHAR](255) NULL,
    ...

Lets say that the latest ID is 100 ( I have 100 records)

Is there a way to ALTER this table's IDENTITY column and change the auto increment value to now be 500?

So at the end i would have something like this
ID 98, 99, 100, 500, 501, ....

I can not seem to find a solution for this in T-SQL


texts: Use sqlldr control file to automatically create several tables in Oracle

texts:
I have a client who has a requirement that I load data from csv files into an Oracle database. Part of the requirement is that I use sqlldr and provide a control file to facilitate loading this data. However there are a lot of text files with a lot of fields, and it will be a lot of labor to create the tables in the database and then create the control file for each table.

I know there are tools which will read a bunch of text files and generate the SQL that is required to create all the tables and load them with data. But that does not involve sqlldr with control file, which is the requirement. Is there a way to do this easily?


texts:
I am converting an Oracle 11G DB to PostgreSQL v11 DB.

I am super new to the whole database thing and I have encountered a query using index type of ctxsys.context to create an index.

I have no idea what ctxsys index type is doing as I tried reading oracle documents related to it but got confused.

I wish to know what exactly this indextype does and

Is there any equivalent solution in PostgreSQL ?

Below is the query for all of your references,

CREATE INDEX IDX1 ON TBL1(BLOB Column) indextype is ctxsys.context;
CREATE INDEX IDX2 ON TBL2(BLOB Column) indextype is ctxsys.context;

I am told you can't index bytea columns (which is the equivalent to blob) in Postgres. Can I move only the lob column to a user defined tablespace ?


texts: SSRS, Limit Fixed number of Columns in Matrix within a Tablix - issue in sorting matrix data

texts:
I am converting a crystal report to ssrs report, i am trying to implement "Format With Multiple Columns" feature of crystal report to ssrs.

I have a Main tablix that has two Two Groups

1st Group - PolicyType
2nd Group - WeekNumber

Under Week Number group, i have inserted a rectangle and a matrix within it. The matrix will show the policy number generated in every week. The matrix should only contain 6 columns and the grouping for matrix is as below

Row Grouping : Ceiling(RowNumber("WeekNumber") / 6)
Column Grouping : Ceiling(RowNumber("WeekNumber") MOD 6)

I need a output like below

InsuranceType1
Week 1
Policy Number
1001 1002 1003 1004 1005 1006
1007 1008 1009 1010
Week 2
Policy Number
2001 2002 2003 2004 2005 2006
2007 2008 2009 2010

 I am able to get a similar output but the sorting within the matrix is not working, ie., i get the following result
InsuranceType1
Week 1
Policy Number
1002 1003 1001 1004 1007 1006
1005 1008 1009 1010
Week 2
Policy Number
2008 2009 2003 2004 2005 2006
2007 2001 2002 2010

 i have not used any sorting on the Matrix, nor in the group of matrix the only sorting is on the main tablix groups PolicyType and WeekNumber and both are in ascending order.

What i have tried is:

Sort Matrix using PolicyNumber, but that will only output first row and first column (very strange!!!).
Add an additional sort of PolicyNumber in Group WeekNumber (did not work either)

 i have no option of modifying the stored procedure to return the row number back to the report as the client is strict on SP modification as they want to get this report backward compatible.

Please help!!


texts:
I am trying to implement the DENSE_RANK function of SQL Server using only basic SQL functions. I managed to implement the RANK function but I don't know how extend it to DENSE_RANK. More specifically, I don't know how to arbitrarily distinsguish between rows of the same rank.

Here is my implementation of RANK.

SELECT A.partition_id,
    A.order_column_id,
    COUNT(B.order_column_id) AS rank
FROM MyTable AS A
INNER JOIN MyTable AS B
    ON A.partition_id = B.partition_b AND A.order_column_id <= B.order_column_id
GROUP BY A.partition_id, A.order_column_id

How can I write an implementation of DENSE_RANK in a similar way?


texts:
Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica?

In one case we've observed usage of 300+ threads with 3 availability groups and 10 databases total. SQL Server 2014 SP1.

Our leads are backup on secondary replica, high activity on primary replica, reports on secondary replica.

The AGs are in a datacenter on VMware. 16 schedulers total, usual worker threads are under 200 range. max_dop on server is 2.

3 AG, 10 DB, 4 replica each - primary, 2 readonly, 1 not readable.
1 secondary is synch, 2 async
16 vcores on 32 cores physical on large multi host cluster.
No overprovision.
Other smaller VMs 4-8 cores are colocated, but they don't press on CPU

We observed a spike in worker threads resulting in denial of service. Attribution of worker threads to AG is our assumption, as only those worker threads can cross the limit.

Below links from the SQL Server Premier Field Engineer Blog read in context don't give a complete answer to me:

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
Are my actual worker threads exceeding the sp_configure ‘max worker threads’ value?


texts: Remove db_denydatawriter, db_denydatareader role from admin user

texts:
I have an AWS SQL Server instance, and I have accidentally given my user (and the only user I have access to) db_denydatawriter and db_denydatareader role on our primary database, which stops me from making any other changes to the db permissions to remove them.

Remembering this an AWS db instance, and the user I have locked out is the only user I have access to.

Surely it can't be this simple to ruin a database?

jcdbadmin is the user in question


texts: Limit Access To mysql information_schema

texts:
In case of mysql attacks, many times user extracts information from information_schema.

can i do something in mysql that can restricts user to xtracts information from information_schema while all privileges on rest database is available.


texts: How do I authenticate a Database User by a LDAP Server and then to login with the SQL Developer, without using the OID/Apex?

texts:
I want to create a global User, identified by a LDAP_DN.

My Problem is: I can automatically create users, but when I try to log on to the database, I get the error message that the login data are wrong.

  "CREATE USER 'TEST_ID' IDENTIFIED GLOBALLY AS 'LDAP_DN' DEFAULT
  TABLESPACE Testuser TEMPORARY TABLESPACE TEMP QUOTA 15M ON Testuser';"
  it is my Term to create the User on the DB.

Admin_ACL&Assign_ACL files are created and the User got the "Connect" and the "Create Session" permissions.

I used the DBMS_LDAP Package to connect to the LDAP Server("init") and then i tried to "bind" with an Account. That was successful.

I 've googled a lot, but i cant find a way to authenticate my Database_User without using an OID / Apex.

I'm using an Oracle 12c Database on a virtual machine  and the LDAP Server is a OpenDJ http://opendj.forgerock.org/ ,LDAP V3.

I dont understand why I can use DBMS_LDAP Package without any Problems, but the IDENTIFIED GLOBALLY Term  doesnt work. I already tried to activate the Oci/Thick Driver on the Sql Deveolper, but i only got problems.

Do you have any idea how I can solve the Problem without using the OID?

Maybe someone can explain how the "Identified Globally" Term and the password encryption from Orace 12c works exactly?


texts: Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly

texts:
Lately we run into performance inssues and I started tracking some recommended sql, memory, disk and cpu counters. I informed myself about the recommended values and numbers to look at.
One of the counters is :

pagefile.sys % Usage

We had really small values like 0,5 in the beginning of collecting metrics. When it came to performance problems we reached numbers like 83,69

Does that mean 83,69% was swapped from memory to disk from the available page file? I am kind of new to this and would love to understand what's going on in order to prevent.


texts:
Joins are very complicated. I've been trying to fully understand them for years. The only way to do this is practice. I thought this was a good website to practice but most of the questions have many answers that return the same results, since the data is limited. Like this question's answer uses a full outer join. But I get the same result with this query

select c.cust_name, c.city, o.ord_no, o.ord_date, o.purch_amt
from orders as o
join customer as c on o.customer_id=c.customer_id

So there's no way I can tell that either both answers are correct, or my answer would produce bad results with different data.

I'm looking for SQL join practice problems where I'll know if my answers are wrong. SQL version doesn't matter to me.


texts: SQL Replication groups in Windows failover clusters RRS feed

texts:
I have a scenario where there is a Cluster of 2 Windows servers that have SQL Standard. Between those servers is replication, and we need to use replication groups for each database, what is the best scenario and is it better to use DFS replication or SQL replication?

Thank you in advance


texts: DB2 LUW: how to influence query planner's choice of join

texts:
Suppose I have two tables, A and B.  They "share" the same primary key, which is indexed in both tables.  Table B also has a timestamp column which is separately indexed.

I do a subselect on B using the timestamp column (I give it a 24-hour interval for the timestamp to belong to), and inner join the results against A using the primary keys.

A and B are huge (O(10^9) rows).  When I give the subselect a recent 24-hour period, it seems that the statistics are poor and the planner underestimates the number of rows coming out of the subselect (say, anywhere between O(1) and O(10^5) rows, while there are actually 6M such rows for the queries I run).  It picks a nested loop join, with the subselect output as the "outer" table and A as the "inner" table, accessed via its index.  While NL joins have a bad rep, it only takes a few minutes to complete.

When I tell it to look for older timestamps ("old" 24-hour periods), it does understand that there are about 6M rows coming out of the subselect.  It picks a hash join, with A as the outer/probe table, and the subselect output as the inner/build table.  It does a table scan on A as part of the plan.  It doesn't complete even after 48 hours.

Is there a way to force or hint to the planner that it should use the nested loop join?

While I'd be interested in hearing about DB tuning as a solution, this is a box I have no authorization to tune.

...more information:  the join switches from nested loop to hash when the number of rows returned by the subselect increases beyond about 1.012*10^6.


texts:
I have a really big query, that queries data from various tables. I have tried various approaches to optimize the query.

Made possible LEFT JOIN as LEFT LATERAL JOIN, so that the lateral join queries are computed after fetching results from the main query
Removed GROUP BY since the aggregation took more time.

But even the final query that I had come up with is slow when I try to order it by an indexed column. Please suggest to me how I should get started on optimization.
Here is the query and the explain analyze detail
https://explain.depesz.com/s/kTBh
Here is the EXPLAIN (ANALYZE, BUFFERS) with indexscan ON:
https://explain.depesz.com/s/SyRu
Here is the EXPLAIN (ANALYZE, BUFFERS) with indexscan OFF:
https://explain.depesz.com/s/TPDv
Indexes on the tables:
CREATE INDEX idx_screens_name ON public.screens USING btree (name);
CREATE INDEX idx_screens_number ON public.screens USING btree (((data ->> 'number'::text)));
CREATE INDEX idx_screens_tags ON public.screens USING gin (tags);
CREATE INDEX idx_screens_theatre_id ON public.screens USING btree (theatre_id);

CREATE INDEX idx_theatre_companies ON public.theatres USING gin (company_ids);
CREATE INDEX idx_theatres_chain_name ON public.theatres USING btree ((((data -> 'chain'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_city_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'city'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_country_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'country'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_name ON public.theatres USING btree (name);
CREATE INDEX idx_theatres_province_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'province'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_qwc_is_active ON public.theatres USING btree (qwc_is_active);
CREATE INDEX idx_theatres_tags ON public.theatres USING gin (tags);

CREATE UNIQUE INDEX company_screen_mappings_key ON public.company_screen_mappings USING btree (company_id, screen_id) WHERE (is_deleted = false);

CREATE UNIQUE INDEX idx_scheduling_times_company_id_screen_id_content_type_id ON public.scheduling_times USING btree (company_id, screen_id, content_type_id);
CREATE INDEX idx_screen_right_mappings_date_range ON public.screen_right_mappings USING gist (daterange(valid_from, valid_till));
CREATE INDEX idx_screen_right_mappings_time_range ON public.screen_right_mappings USING gist (public.tmrange(start_time, end_time));


texts: Database Design for updatable sequential records

texts:
I am working on creating a data model for storing data related to production tracking.  I work for an engineering firm that models and analyzes data for our clients.  There are several steps in the process and the process is constantly updating.

I am trying to model the processes and include parent processes and sequential order of processes.

For example:

Process Table
---------------------
ProcessID - uniqueidentifier
ProcessName - varchar
ProcessDescription - varchar
...

ProcessOrder Table
---------------------
ProcessID - uniqueidentifier FK - Process
ParentProcessID - uniqueidentifier FK - Process
ProcessOrder - int
...

The ProcessOrder column in the ProcessOrder Table would simply store a number representing which sequential step in the parent process it represents.

For example, a modeling procedure has the following steps: create new empty model, name model, enter model parameters.  The Process Table would look like:

ProcessID | ProcessName | ProcessDescription
-------------------------------------------------
UUID1     | Modeling    | Create Model of Data
UUID2     | New Model   | create new empty model
UUID3     | Name Model  | name model
UUID4     | Parameters  | enter model parameters

The ProcessOrder Table would look like:

ProcessID | ParentProcessID | ProcessOrder
--------------------------------------------------
UUID2     | UUID1           | 1
UUID3     | UUID1           | 2
UUID4     | UUID1           | 3

The issue with this design is that when the workflow gets updated, the process order will change and I will need to update the ProcessOrder record for the process that changed and for all subsequent records with the same ParentProcessID.

Is there a better way to store this kind of data and maintain normalization?


texts: What are the advantages and inconveniences of generalization or specialization of SQL tables?

texts:
This is a "theoretical" question linked to quality of table's definition.

I work on tires and, currently, I have a big table that, for each tire's label, contains a row with label's values. For more information go to this site:
DUNLOP Tire Label Explained.

The used tire's labels are Rolling Resistance ('RR'), Wet Grip ('WG') and Noise ('NOISE').

Some columns in this big table are common to 'RR', 'WG' and 'NOISE' labels, but some other columns are specific to only one label. For example

CTC     LABEL GRADE G-SYMBOL G-VALUE AVERAGE
------  ----- ----- -------- ------- -------
345674  RR    B                      11,2
459864  WG    A                       2,1
893023  NOISE       ))       71      71,2

... where:

CTC column is a term used to group all tires having same characteristics and belonging to same tire's line's name. Example: 'Goodyear EAGLE 155/60R20 95H Extra Load'.
LABEL contains the shortname of the label.
CTC+LABEL represents the table's primary key.
GRADE contains the grade (level) of the label. For 'RR' and 'WG' the grade can vary from 'A' to 'G' where 'A' is the best grade. For 'NOISE', the grade is defined by a symbol and not a letter.
G-SYMBOL has been added to represent the grade of 'NOISE'. Acceptable values are ')' or '))' or ')))', where ')' is the best value for 'NOISE'.
G-VALUE has been added to represent the grade of 'NOISE'.
AVERAGE is the average of all measures taken on some tires that are used to fix the grade of all tires belonging to same tire's line's name.

As you can see, GRADE is only used by 'RR' and 'WG' and G-SYMBOL/G-VALUE are only used by 'NOISE'. AVERAGE column are used by all labels but magnitude of value for each label are distinct. For 'RR', values are between '6' and '15' kg/T, and for 'NOISE' values are between '60' and '80' dB.

Question(s):

How many tables must be defined to manage all these fields?

Solution A: 1 big table?

BIG-TABLE: CTC / LABEL / GRADE / G-SYMBOL / G-VALUE / AVERAGE

Solution B: 3 tables (one in label)?

RR:    CTC / AVERAGE / GRADE
WG:    CTC / AVERAGE / GRADE
NOISE: CTC / AVERAGE / G-SYMBOL / G-VALUE

Solution C: 4 tables (one with specific field in label + 1 for common fields)?

RR:     CTC / GRADE
WG:     CTC / GRADE
NOISE:  CTC / G-SYMBOL / G-VALUE
COMMON: CTC / LABEL / AVERAGE

Solution X: another solution?

What are the advantages and inconveniences of each solution?

Some other considerations:

It is very uncommon to mix ALL labels.
In the future, some new labels (as 'SNOW') will be added.


texts: Unable to replicate Postgres tables using logical replication in RDS

texts:
Tables in the destination database are empty even after subscription is successful.
I am extremely new to logical replication within Postgres. I am trying to replicate three tables from the source database to the destination database.
These two databases are running on difference RDS instances
I followed the steps laid out on this link
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html
I have enabled replication across both the database instances by changing the parameter rds.logical_replication to 1 (enabled). The parameter groups are in sync after the restart.
On the source database, I created a publication
create publication master_data_publication for table tbl_1, tbl_2, tbl_3;
On the destination I created a table with the same name and same columns and then created a subscription
create subscription master_data_sub CONNECTION 'host=sourcedbhost.some-code.some-region.rds.amazonaws.com port=5432 dbname=sourcedb user=sourceuser password=userpassword' publication master_data_publication;
if i run
select * from pg_catalog.pg_stat_subscription;, it shows the following.
With this, we would assume that the replication should be working.
But the tables are shown to be completely empty.
On Source
select count(*) from tbl_1;
 count
-------
     332

On Destination
select count(*) from tbl_1;
 count
-------
     0

I am now stuck on how to proceed from here.


texts: How SQL Server handles concurrent requests?

texts:
Suppose a SQL Server table has two MailID columns, with max value = 5.

If two different users at the same time execute insert queries that try to insert Max(MailId)+1 into the MailID column, is it possible that both new rows may get the value 6?

I want to learn how SQL Server internally handles more than one request at the same time.


texts:
Given the following schema and dummy data:

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  memo VARCHAR(255) NOT NULL,
  amount INT NOT NULL,
  tx_date DATE NOT NULL
);

INSERT INTO transactions (memo, amount, tx_date) VALUES
  ('Initial balance', 10000, '2015-08-17'),
  (     'Eating out', -1000, '2015-08-19'),
  (            'Gas', -7000, '2015-08-19'),
  (          'Wages', 75000, '2015-08-31');

I'm trying to generate a report of the running balance ordered by the transaction date such as:

 id |      memo       | amount |  tx_date   | running_balance
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000

Ideally I'd like to sum each amount to the running_balance of the following row, but it seems that the lead() window function can't reference the very same column you are trying to create:

SELECT *, amount + lead(running_balance, 1, 0) OVER (ORDER BY tx_date DESC) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

ERROR:  column "running_balance" does not exist
LINE 1: SELECT *, amount + lead(running_balance, 1, 0) OVER (ORDER B...

Another option would be to make partial column sums from the current row to the last one, but I am also unable to come up with the correct syntax:

SELECT *, SUM(amount) OVER (ORDER BY tx_date DESC ROWS UNBOUNDED FOLLOWING) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

ERROR:  frame start cannot be UNBOUNDED FOLLOWING
LINE 1: ...T *, SUM(amount) OVER (ORDER BY tx_date DESC ROWS UNBOUNDED ...

Can any of these queries/approaches be made to work? Or is there any other way to achieve the desired result?


texts: Multiple volumes vs single volumes for SQL Server CHECKDB performance

texts:
We have a ~10TB database with dozens of data files on SQL Server 2017 Enterprise CU21. We're seeing slower CHECKDB performance when all of the files are on a single volume as opposed to multiple volumes. (In both cases, the same high-end (Pure) storage is involved, same VM hosts, same storage network - just different numbers of volumes.)
Is there a documented advantage to spreading data files across multiple volumes, either for DBCC CHECKDB specifically, or for SQL Server generally?
The trace flag 2549 described in KB 2634571 tells CHECKDB to treat each database file as if it's on a unique drive. But is there any other official documentation outlining how SQL Server (or CHECKDB specifically) treats IO differently when there are multiple volumes involved?


texts: Convert an int column to bigint with the 4 bytes of padding available

texts:
If there's 4 bytes of padding that would fit the bigint perfectly, is it possible to efficiently convert it on-the-spot by taking advantage of that gap?

CREATE TABLE a (
    b SERIAL PRIMARY KEY,
    c TIMESTAMP WITH TIME ZONE NOT NULL,
    d TIMESTAMP WITH TIME ZONE NOT NULL,
);

I understand that indexes and foreign keys would still be affected, but just wondering about the table itself.


texts:
I'm trying to perform a query in a transactional database that was used for more than 3 years. But it always says timeout expired after running for about 30 seconds when I try to run this query:

SELECT COUNT(*)
FROM tblBPLedgerBill LEFT JOIN
    tblAssessment ON
    tblBPLedgerBill.strAssessmentID = tblAssessment.ObjID
WHERE tblAssessment.intType = 0 AND curSurcharge != 0

Is it possible to adjust how many seconds the query should take before it terminates it.


texts: Specifying the failover partner when using Availability Groups

texts:
We have a situation where we are using replicated databases in an environment that's making use of Availability Groups.  As we wanted no manual intervention when failing over, we decided to set up subscriptions to write replicated data to both of our replica servers.  As these databases will only ever be read from, this architecture should be ok.

To further configure this and after reading this article by Brent Ozar, we set the connection strings in our apps that used said databases with a failover partner such that if we lost our primary replica then our app would still work.

After a recent test, failover and fail back, we have now started receiving some intermittent errors from our clients along the lines of:

System.InvalidOperationException:
Server xxx, database xxx is not configured for database mirroring.

At present I am confused as to why this error is appearing. There has been some suggestions of adding a connection timeout to the connection string, however that doesn't make sense to me as if the primary timed out then reading from the secondary should work, and in our case is valid as it's an exact copy.

If anyone has any ideas I would be grateful.

EDIT:  Further to this I have now removed the failover partner from the connection string and left it pointing at the DB on the replica, however we get errors of:

System.Data.SqlClient.SqlException: 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: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)

Its like the client has cached the secondary server somewhere?

Edit: in response to Brent's answer

Hi Brent thanks for that, however I wasn't clear in my initial description.  The replicated databases are not part of an availability group but instead I have a subscription on each node because, as I understand it, replicated DBs are not well supported in AGs.

I was therefore trying to come up with a way to avoid manual intervention for these DBs should a failure of the replica occur and the other databases which are part of an AG failover.

I was thinking that while Node A was up all connections would go to that server, but if Node A failed then connections would go to Node B when using Failover Partner.  However this does not seem to be the case and actually according to this article:

http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

If you specify a failover partner but the failover partner server is not configured for database mirroring and the primary server (specified with the Server keyword) is not available, then the connection will fail.

This Failover Partner configuration seems not to be supported for how I am attempting to use it, but leaves me with a situation where manual intervention is required for any failover :(  unless I am missing something?


texts: T-SQL command to log in as a different user?

texts: Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?


texts:
I have a fixed width text file that I am bringing in through SSIS. As part of the input into SQL Server I am grabbing a string of characters and trying to CAST them as numeric in a derived column.
The data looks like this:

02      PR000000000000017943                    0287801709

I need to grab 17943 and convert it to 179.43

The expressions I have tried are:

(DT_NUMERIC,18,2)(SUBSTRING(EntireRow,10,18)
(DT_NUMERIC,18,2)TRIM(SUBSTRING(EntireRow,10,18))

I keep getting the following error:

  The ProcessInput method on component "Derived Column" (2) failed with
  error code 0xC0209029 while processing input "Derived Column Input" .

I have looked but have not been able to find a solution to this.