texts: Postgres update query complaining about text value when only NULL values are passed

texts:
I am using Postgres 9.4.6.

Is there any reason why the below update query would execute successfully here:-

UPDATE "Predictions"
    SET "userid" = c."userid",
        "Fixture_No" = c."Fixture_No",
        "Home_Side_Score" = c."Home_Side_Score",
        "Away_Side_Score" = c."Away_Side_Score"
    FROM (VALUES(288, 1, NULL, NULL),(288, 34, 1, 1))
    AS c("userid", "Fixture_No", "Home_Side_Score", "Away_Side_Score")
    WHERE "Predictions"."Fixture_No" = c."Fixture_No" AND "Predictions"."userid" = c."userid"

But with these values, the same query would fail with "ERROR:  column "Home_Side_Score" is of type smallint but expression is of type text
LINE 4:    "Home_Side_Score" = c."Home_Side_Score",":

UPDATE "Predictions"
    SET "userid" = c."userid",
        "Fixture_No" = c."Fixture_No",
        "Home_Side_Score" = c."Home_Side_Score",
        "Away_Side_Score" = c."Away_Side_Score"
    FROM (VALUES(288, 1, NULL, NULL),(288, 34, NULL, NULL))
    AS c("userid", "Fixture_No", "Home_Side_Score", "Away_Side_Score")
    WHERE "Predictions"."Fixture_No" = c."Fixture_No" AND "Predictions"."userid" = c."userid"

This doesn't make any sense as I am not trying to pass postgres text instead of an integer?

My predictions table is as follows:

CREATE TABLE "Predictions"
(
  userid smallint,
  "Fixture_No" smallint,
  "Home_Side_Score" smallint,
  "Away_Side_Score" smallint,
  "Prediction_ID" bigserial NOT NULL,
  "Joker_Played" boolean,
  "Result" integer NOT NULL DEFAULT 4,
  CONSTRAINT "Predictions_pkey" PRIMARY KEY ("Prediction_ID"),
  CONSTRAINT "Result_Type" FOREIGN KEY ("Result")
      REFERENCES "ResultTypes" ("Result_ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Predictions"
  OWNER TO bjlizgykrnyffr;


texts: Is it better to store calculated values or recalculate them on request?

texts:
Possible Duplicate:
  Storing vs calculating aggregate values

At an old job, we had this database, it wasn't normalized rigth, some tables wheren't used anymore, it was patch after patch. In conclusion, it was a mess.

Once, while I was trying to fix it I saw that some fields where calculated on the fly and this made the db very slow performing simple select querys. Since then, I don't calculate any field anymore, I just store the data as is.

Is this recomendend? when is better to store data than calculate it?

Thanks guys.


texts:
I need to install two different versions of Mysql in one server OS RHEL6 and it will be separated by user called mysqlprod and mysqlpreprod.

I need to separate their binaries, softwares,data directory and mysqldaemons.

I assume tar ball installation is a best way to deviate the installations. But I don't find tar ball for Mysql 5.6.17 in http://dev.mysql.com/downloads downloads. Its just rpms and I cannot do "rpm --basedir=" because it says rpm cannot be relocatable.

Where can I find tar ball binaries that is provided in http://dev.mysql.com/downloads ? or anyother way to achieve my above setup in Mysql 5.6.17 latest GA? Please advise.


texts: Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

texts:
We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.

The problem generally manifests on an ORDER BY .. LIMIT .. clause (a common pattern in Django pagination) where the LIMIT is some relatively small subset of the results matched by the index. An extreme example is this:

SELECT * FROM mcqueen_base_imagemeta2
  WHERE image_id IN ( 123, ... )
  ORDER BY id DESC
  LIMIT 1;

where the items in that IN clause are ~20 and total rows matched by the index on image_id is 16.

The EXPLAIN shows that it misses the image_id index and instead does a PK scan of 5B rows:

Limit  (cost=0.58..4632.03 rows=1 width=28)
   ->  Index Scan Backward using mcqueen_base_imagemeta2_pkey on mcqueen_base_imagemeta2  (cost=0.58..364597074.75 rows=78722 width=28)
         Filter: (image_id = ANY ('{123, ...}'::bigint[]))

If the LIMIT is increased to 2, it works as expected:

Limit  (cost=7585.92..7585.93 rows=2 width=28)
   ->  Sort  (cost=7585.92..7782.73 rows=78722 width=28)
         Sort Key: id DESC
         ->  Index Scan using mcqueen_base_imagemeta2_image_id_616fe89c on mcqueen_base_imagemeta2  (cost=0.58..6798.70 rows=78722 width=28)
               Index Cond: (image_id = ANY ('{123, ...}'::bigint[]))

This also happens on queries where the index matches ~3000 rows and the limit is set to 100, so something that easily happens in real world REST API pagination.

The table definition is:

mcqueen=# \d mcqueen_base_imagemeta2
                                       Table "public.mcqueen_base_imagemeta2"
      Column       |           Type           |                              Modifiers
-------------------+--------------------------+----------------------------------------------------------------------
 id                | bigint                   | not null default nextval('mcqueen_base_imagemeta2_id_seq'::regclass)
 created_at        | timestamp with time zone | not null
 image_id          | bigint                   | not null
 key_id            | smallint                 | not null
 source_version_id | smallint                 | not null
Indexes:
    "mcqueen_base_imagemeta2_pkey" PRIMARY KEY, btree (id)
    "mcqueen_base_imagemeta2_image_id_616fe89c" btree (image_id)
    "mcqueen_base_imagemeta2_key_id_a4854581" btree (key_id)
    "mcqueen_base_imagemeta2_source_version_id_f9b0513e" btree (source_version_id)
Foreign-key constraints:
    "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b" FOREIGN KEY (image_id) REFERENCES mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED
    "mcqueen_base_imageme_key_id_a4854581_fk_mcqueen_b" FOREIGN KEY (key_id) REFERENCES mcqueen_base_metakey(id) DEFERRABLE INITIALLY DEFERRED
    "mcqueen_base_imageme_source_version_id_f9b0513e_fk_mcqueen_b" FOREIGN KEY (source_version_id) REFERENCES mcqueen_base_metasourceversion(id) DEFERRABLE INITIALLY DEFERRED

I'm a novice at best when it comes to tuning, but I figure that the defaults for statistics are not up to that table's size and so it naively thinks that a PK scan is faster than an index scan.


texts: Restrict Report Refresh Between Two Time Periods

texts:
Say a user has a report that refreshes every hour around the clock but as an Admin I do not want it to refresh between 2 and 4 AM.
Is there a way to programmatically restrict a report refresh for 2, 3, and 4 AM without user involvement?


texts: Optimising some SQL to get all rows corresponding to the minimum value of a column

texts:
I've worked out how to get some rows from a table where a column has the lowest possible value. Note this can return multiple rows, which is what I want. It works just fine.

SELECT col1, col2
FROM TableA
WHERE col4=0
    AND col5=1
    AND col3 IN
       (SELECT MIN(col3)
        FROM TableA
        WHERE col4=0 AND col5=1)

But is the nested select overkill, i.e. can this be optimised? Maybe there's a simple SQL command I'm missing...

I tried

SELECT col1, col2, MIN(col3)
FROM TableA
WHERE col4=0
    AND col5=1

but this will only ever return one row.


texts: postgreSQL - Checking if one date interval does not overlap a date interval in array of intervals for each row

texts:
PostgreSQL(9.6), I am trying to check whether a date interval

('2018-11-18 12:00','2018-11-20 12:00')

does not overlap any of the following date intervals in the array

{('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00')}

The elements in the array are composite:

CREATE TYPE reservation AS (
checkIn TIMESTAMP WITHOUT TIME ZONE,
checkOut TIMESTAMP WITHOUT TIME ZONE
)

Basically I am trying to check whether a "room" is available for a selected time interval by checking if this interval does not overlap with any existing reservation date intervals, example: ('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00').

Here is the rooms table:

CREATE TABLE rooms (
rId roomId PRIMARY KEY,
hRef hotelId NOT NULL,
rNo roomNo NOT NULL,
rType roomType,
numPeople INTEGER,
rBedOptions roomBed[],
reservations reservation[],
priceNight FLOAT,
FOREIGN KEY (hRef) REFERENCES hotels(hId) ON UPDATE CASCADE ON DELETE SET NULL
)

INSERT INTO rooms VALUES
('R001','H001','101','one-bedroom',1,
ARRAY[row('1 twin')::roomBed],
ARRAY[
      row('2018-11-21 12:00','2018-11-23 12:00')::reservation,
      row('2018-11-19 12:00','2018-11-20 12:00')::reservation],
450.5);

So far I have been successful in checking the first element of the array for each row by writing the following query:

SELECT * FROM rooms R
WHERE R.reservations[1] IS null
OR NOT (('2018-11-18 12:00','2018-11-20 12:00')
OVERLAPS (R.reservations[1].checkIn, R.reservations[1].checkOut))
ORDER BY rid;

The problem is that I don't know how to check all elements in the array if there is more than one. Any ideas or suggestions? Thanks


texts: Return true if any value in an specific key inside a jsonb array exists

texts:
The rows in my table look like

 facility | events
----------+---------------------------------------------------------------
  1       | [{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]

I want to get all the rows who have a severity = bad

I tried

select facility, events::jsonb @> '"severity"' as bad from tbl limit 10;

and I got (same data as before)

 facility | bad
----------+-------
  1       | f

Thanks in advance


texts:
I am trying to dump a database which has different character set at database level, table level and column level. The task at hand is to strip off the character set such that when the database is loaded all the character sets will be replaced with the default character set.
I do not want to edit the dump file. I am looking for an option to either strip off the character set while dumping or change the character set while loading.
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html


texts:
I have an old Oracle Forms solution that accesses a 9i database and I have been asked if it's possible to replicate to an Oracle 12c database server, with the intention of using Fusion until a new application is built.

Is it possible to replicate between these two versions? Or is there another way to achieve an active-active replica between the two servers? i.e. log shipping etc.


texts: To trigger or not to trigger- That is the question

texts: I have a mysql table that tracks page views. Each row includes a date stamp. I want to remove all rows that are over 30 days old on a regular basis. Could, or should, I trigger an action on each insertion of a new row that would delete the rows that are over 30 days old? Or would I be better off adding a separate delete action along with each new insertion?


texts: Optimize MySQL query and index for multiple column range query

texts:
I have a table like this:

CREATE TABLE IF NOT EXISTS `jobs` (
    `job_id` varchar(36) NOT NULL,
    `job_status` varchar(30) NOT NULL,
    `created_at` datetime NOT NULL,
    `lease_date` datetime,
    `priority` int NOT NULL,
    PRIMARY KEY(`job_id`),
    INDEX `job_status_priority_lease_date` (`job_status`, `priority`, `lease_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The full query I want to perform on the table would be the following:

select * from jobs
where job_status="IN_PROGRESS"
and lease_date<"10minutesago"
order by
priority
limit 100

I am implementing database as a job queue. 10 min is like the job time out, I only want to act on the jobs that are already timed out. Jobs has different priority so I was ordering by it. Also, I want to make my DB job queue behave like FIFO, that's why my index includes lease_date.

I definitely want my results to be ordered by Priority, e.g. two chunks of the results with Priority 0 and 1. I don't need my results to be strictly ordered by lease_date, as long as they are Ascending order in their own chunks that is good enough for me. e.g. result set:

priority lease_date
0        2017-08-19
0        2018-09-20
1        2016-08-20
1        2018-10-20

Is my index enough for that? How much can I optimize the query and build an index to boost the performance as much as possible?


texts: Unable to use a temporary table more than once

texts:
I am using SQL Server 2008 to build a stored procedure. In it, I create a temporary table (#results) using recursion for getting all the ancestors given a specific record.

My specific problem is, when I try to use this #results table twice, the second time it returns this message:

  Invalid object name '#results'.

Here is my stored procedure:

SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetSurveyByWorkflow]
 @WorkflowId INT  = NULL
AS
BEGIN
 DECLARE @SurveyService int = NULL;
 DECLARE @Theme int = NULL;
 DECLARE @TopAncestor int = NULL;
 DECLARE @Survey int = NULL;

 SET NOCOUNT ON
 SELECT @Theme = THEMEID FROM [dbCSSA].[dbo].[TYPESOFSERVICES] WHERE TYPEOFSERVICEID = @WorkflowId;
;WITH #results AS
(
    SELECT  THEMEID,
            THEMEIDPARENT
    FROM    [dbCSSA].[dbo].[THEMES]
    WHERE   THEMEID = (@Theme)
    UNION ALL
    SELECT  t.THEMEID, t.THEMEIDPARENT FROM [dbCSSA].[dbo].[THEMES] t
            INNER JOIN #results r ON r.THEMEIDPARENT = t.THEMEID
)
SELECT @SurveyService = IDSERVICESURVEYS FROM [dbCSSA].[dbo].[SURVEYSERVICES] WHERE IDTHEMESERVICE =
    (SELECT THEMEID FROM #results WHERE THEMEIDPARENT IS NULL);
--It fails here:
SELECT @TopAncestor = THEMEID FROM #results WHERE THEMEIDPARENT IS NULL;
SELECT @Survey = SURVEYID FROM [dbCSSA].[dbo].[SURVEYSERVICES] WHERE IDTHEMESERVICE = @TopAncestor;
SELECT * FROM FORMSURVEYS
    INNER JOIN FIELDSURVEYS ON FORMSURVEYS.IDFORM = FIELDSURVEYS.FORMID
    FULL JOIN OPTIONVALUESFIELDSURVEYS ON FIELDSURVEYS.IDFIELD = OPTIONVALUESFIELDSURVEYS.IDFIELD
    WHERE FORMSURVEYS.SURVEYID = @Survey
END

What am I doing wrong?


texts: SQL Server Max number of Times a table can be referenced as a foreign key?

texts:
Is there any maximum on the number of times a single table can be referenced in a SQL Server database via foreign key?

IE

Table A A_ID

Table B ~ B_ID, A_ID
Table C ~ C_ID, A_ID
Table D ~ D_ID, A_ID
...


texts:
I have a table with two columns that stores TIME type values. This is the CREATE statement:

CREATE TABLE `sdriving_turno` (
    `idturno` bigint(20) NOT NULL AUTO_INCREMENT,
    `idempresa` int(10) unsigned NOT NULL,
    `tipo` varchar(30) NOT NULL,
    `hora_inicio` time DEFAULT NULL,
    `hora_fin` time DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`idturno` , `idempresa`),
    KEY `fk_sdriving_turno_sdriving_empresa1_idx` (`idempresa`),
    CONSTRAINT `sdriving_turno_ibfk_1` FOREIGN KEY (`idempresa`)
        REFERENCES `sdriving_empresa` (`idempresa`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

I need to get the records in a range of TIME, lets say I need to get all the records BETWEEN 14:00 and 17:00 and for that I made this query (it has some JOINS but it's not relevant on this topic):

SELECT
    s.idregistros AS s__idregistros,
    s.maquinaemisorid AS s__maquinaemisorid,
    s.idoperador AS s__idoperador,
    s.idturno AS s__idturno,
    s.created_at AS s__created_at,
    s.updated_at AS s__updated_at,
    s2.idturno AS s2__idturno,
    s2.idempresa AS s2__idempresa,
    s2.tipo AS s2__tipo,
    s2.hora_inicio AS s2__hora_inicio,
    s2.hora_fin AS s2__hora_fin,
    s2.created_at AS s2__created_at,
    s2.updated_at AS s2__updated_at,
    s3.idmaquinaemisor AS s3__idmaquinaemisor,
    s3.idmaquina AS s3__idmaquina,
    s3.idemisor AS s3__idemisor,
    s3.created_at AS s3__created_at,
    s3.updated_at AS s3__updated_at,
    s4.idmaquina AS s4__idmaquina,
    s4.idempresa AS s4__idempresa,
    s4.patente AS s4__patente,
    s4.created_at AS s4__created_at,
    s4.updated_at AS s4__updated_at,
    s5.idoperador AS s5__idoperador,
    s5.idempresa AS s5__idempresa,
    s5.nombre AS s5__nombre,
    s5.rut AS s5__rut,
    s5.created_at AS s5__created_at,
    s5.updated_at AS s5__updated_at,
    s6.iddetalle_emisores AS s6__iddetalle_emisores,
    s6.idregistros AS s6__idregistros,
    s6.fecha_registro AS s6__fecha_registro,
    s6.dertalle_mensaje AS s6__dertalle_mensaje,
    s6.estado AS s6__estado,
    s6.revisado AS s6__revisado,
    s6.modo AS s6__modo,
    s6.remitente AS s6__remitente,
    s6.created_at AS s6__created_at,
    s6.updated_at AS s6__updated_at
FROM
    sdriving_registros_emisores s
        LEFT JOIN
    sdriving_turno s2 ON s.idturno = s2.idturno
        LEFT JOIN
    sdriving_maquina_emisor s3 ON s.maquinaemisorid = s3.idmaquinaemisor
        LEFT JOIN
    sdriving_maquina s4 ON s3.idmaquina = s4.idmaquina
        LEFT JOIN
    sdriving_operador s5 ON s.idoperador = s5.idoperador
        LEFT JOIN
    sdriving_detalle_emisores s6 ON s.idregistros = s6.idregistros
WHERE
    (s5.idempresa = '12'
        AND (s2.hora_inicio < '14:47'
        AND '14:47' > s2.hora_fin))

But when record has hora_inicio = 00:00:00 and hora_fin = 17:00:00 I get no results, why? What's wrong in my query?

Note 14:47 is the server data build through PHP if there is any way to get the records according to the current time in server just using SQL way let me know.


texts: Oracle: duplicate schema to another database

texts:
I want to copy an Oracle schema from a production database to a validation database.
Both databases are Oracle 11g R1 standard edition and need less interrupting time as I can (but I can do if it absolutely needed).

Both are installed on it's own proper server (Linux RedHat 5 Enterprise), instance names differ, tablespace and datafiles are already exist, both use archive logs.

What I want to do is select a whole schema on the production database and duplicate it to the validation database. The process must include all tables, indexes and all dependent schemas objects. There are some Lobs in some tables but no "exotical" objects (like types), no packages, no procedures, no functions, ...
All objects reside in two well identified tablespaces (one for tables and datas, one for indexes).

I can't duplicate all instance. So RMan duplicate command is not an option.

Currently a process exist based on exp/imp utility but it's so slow, need database unavailability (in order to preserve data integrity) and error prone (mainly because full human's hand process).
I've the feeling that use RMan Oracle utility would be fine but I need some some external advises or comments to define the right way to do it. Datapumps utilities could be a valuable second choice (I'm currently evaluating).

Thanks for your advises
Thomas


texts:
I have a database with the following tablespaces:

pgwb=# \db
                   List of tablespaces
    Name    | Owner |              Location
------------+-------+-------------------------------------
 bspace     | pgwb  | /data1/tablespaces/TS_B_SPACE/tests
 meta       | pgwb  | /data1/tablespaces/metadata/tests
 mspace     | pgwb  | /data1/tablespaces/TS_M_SPACE/tests
 nspace     | pgwb  | /data1/tablespaces/TS_N_SPACE/tests
 pg_default | pgwb  |
 pg_global  | pgwb  |
 uspace     | pgwb  | /data1/tablespaces/TS_U_SPACE/tests
(7 rows)

They are referenced in as symbolic links in:

pgwb@BLIXSQL0:/data1/pgwb/pg_tblspc> ls -l /data1/pgwb/pg_tblspc
total 0
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16536 -> /data1/tablespaces/TS_U_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16537 -> /data1/tablespaces/TS_B_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16538 -> /data1/tablespaces/TS_N_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16539 -> /data1/tablespaces/TS_M_SPACE/tests
lrwxrwxrwx 1 pgwb users 33 May  9 09:17 16540 -> /data1/tablespaces/metadata/tests

I have setup the database for PIT recovery.

To make a base backup I use pg_basbackup like:

pg_basebackup --xlog --format=t -D /data1/backup/`date +%Y%m%d`

In the backup folder I have the following files:

pgwb@BLIXSQL0:/data1/backup/20170510> ls -l
total 62772
-rw-r--r-- 1 pgwb users   289792 May 10 13:51 16536.tar
-rw-r--r-- 1 pgwb users  2821120 May 10 13:51 16537.tar
-rw-r--r-- 1 pgwb users  2821120 May 10 13:51 16538.tar
-rw-r--r-- 1 pgwb users   371712 May 10 13:51 16539.tar
-rw-r--r-- 1 pgwb users   716800 May 10 13:51 16540.tar
-rw-r--r-- 1 pgwb users 57170432 May 10 13:51 base.tar

Now a crash happens and I have to restore from these tar files, but I don’t know the symbolic link destination.

How can I solve this? Do I need a pg_dump from the tablespaces?


texts: Inserting into a table and getting the column names from a collection in PL/SQL

texts:
I want to copy data from one table to another and would like to have a rather dynamic insert statement.
I have the column names I want to insert into in a collection col_names and was wondering if it's possible to use that collection in the insert statement somehow. I believe I could do it with dynamic SQL and write a string in some for loop, but maybe there's a cleaner and more efficient way. I have something like the following pseudo code in mind:

declare
    type coll is table of varchar2(200);
    col_names coll;
begin
    -- getting the columns names into the col_names collection somehow
    -- col_names has entries like (id, fname, lname, dob, ...)
    -- could be something like
    select column_name bulk collect into col_names from all_tab_cols where table_name='SOME_OTHER_TABLE';
    insert into some_db col_names select col_names from some_other_table;
end;

I read about forall, but didn't find anything pointing to the right answer so far.


texts:
My coworker and I are tasked with redesigning our current Access databases in a MySQL environment. Between the two of us, this wont be a problem but we need to connect to other external databases that neither of us are sure how to do.

We have considered using views populated from the other databases, or have tables that copy the data on a timer but we feel that both of those will have negative performance implications.

I have looked but can not find anything that gives insight as to the best way to connect everything together.

One of the databases is managed by our phone system company, another is our in house data-warehouse. We also have the database managed by the workforce software company, and the final one will be ours that connects everything.

I'm hoping that someone here can point me in a direction as to how to connect everything together. Our DB will be on a server that we will have full access to if we need to write any additional software.


texts: Error 1067: The process terminated unexpectedly [InnoDB]

texts:
I am facing the following error when i try to restart the MySQL service after an unexpected shutdown of the database server last night.

Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.

When i check the .err log file under MySql data folder, the log details are as per below.

InnoDB: Log scan progressed past the checkpoint lsn 804 2135184621
150513 12:20:39  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 804 2136195241
150513 12:20:50  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 150513 12:20:50  InnoDB: Assertion failure in thread 2412 in file .\rem\rem0rec.c line 337
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http:// bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace.
150513 12:20:51 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=135266304
read_buffer_size=65536
max_used_connections=0
max_threads=400
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 262617 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
InnoDB: Thread 164 stopped in file .\os\os0sync.c line 391
0070A1A8    mysqld.exe!rec_get_offsets_func()[rem0rec.c:337]
0071B498    mysqld.exe!page_cur_parse_insert_rec()[page0cur.c:798]
0071512F    mysqld.exe!recv_parse_or_apply_log_rec_body()[log0recv.c:814]
00715CF1    mysqld.exe!recv_recover_page()[log0recv.c:1294]
006EBE0F    mysqld.exe!buf_page_io_complete()[buf0buf.c:2033]
006E4472    mysqld.exe!fil_aio_wait()[fil0fil.c:4273]
006BCDCD    mysqld.exe!io_handler_thread()[srv0start.c:437]
77E6482F    kernel32.dll!GetModuleHandleA()

Error log show in Event Viewer:

An unhandled win32 exception occurred in mysqld.exe [1200]. Just-In-Time debugging this exception failed with the following error: Debugger could not be started because no user is logged on.

MySQL version: 5.1
Table Type: InnoDB
ibdata1 Size: 28GB

There are no SQL dump backup files for the tables made and there is only the SQL physical data files. I am desperately needed to restore the data of these and bring the website back to online.

Please help.


texts: Same user cannot 'see' view on remote oracle database athough dblink is valid

texts:
I am running an impdp job that I have extracted the DDL from to determine why the job hangs.  It consistently stops on the first object created using a dblink. I am running the impdp job as system as recommended by Oracle.  The job must be run on two separate, different instances on the same database using a unique dump file for each instance. The impdp job only imports one schema from the dump file, the same schema name, in both instances. The two impdp jobs are interdependent on dblinks. At the point where the job is indefinitely waiting on either impdp instance job, it is unable to retrieve data from the specified dblink, as confirmed per DDL extract run on SQL Developer. The links have been sucessfully created in each database instance and SELECT * FROM DUAL@DB_LINK returns a value but SELECT * FROM 'view_name'@DB_LINK returns 'ORA-00942: table or view does not exist', although I have confirmed the view exists in the remote schema. I have researched creating and fetching data via dblinks and understand the dblink querying user must be granted access to the remote object(s). In this case the user is the same with identical passwords on both database instances. How can I grant permission on all schema objects to the user of same name in separate instance so that the DDL can complete?
UPDATE: I have granted access to all views in instance1 to the connect as user for the dblink utilized by the user instance2 and vice versa. I still cannot select views from either instance using the dblinks from respective remote users.This is the script:
BEGIN
   FOR objects IN
   (
         SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO <DB_LINK_NAME>' grantSQL
           FROM all_objects
          WHERE owner = 'OWNER_NAME'
            AND object_type = 'VIEW'
            AND object_type IN
                (
                  select view_name
                  from sys.dba_views
                  where owner = 'OWNER_NAME'
                )
       ORDER BY object_type, object_name
   ) LOOP
      BEGIN
         EXECUTE IMMEDIATE objects.grantSQL;
      EXCEPTION WHEN OTHERS THEN
         --Ignore ORA-04063: view "X.Y" has errors.
         --(You could potentially workaround this by creating an empty view,
         -- granting access to it, and then recreat the original view.)
         IF SQLCODE IN (-4063) THEN
            NULL;
         --Raise exception along with the statement that failed.
         ELSE
            raise_application_error(-20000, 'Problem with this statement: ' ||
               objects.grantSQL || CHR(10) || SQLERRM);
         END IF;
      END;
   END LOOP;
END;
/


texts: DBCC SHRINKDATABASE TRUNCATEONLY - 2008R2 vs 2012

texts:
Im a bit confused. Was the TRUNCATEONLY parameter changed in SQL 2012 or is the documentation wrong in SQL 2008 R2?

2008 R2

  Releases all free space at the end of the file to the operating system,
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent. target_percent is ignored
  if specified with TRUNCATEONLY.

  TRUNCATEONLY is applicable only to data files. The log files are not
  affected.

The last statement makes me thing this has no effect on log files at all?

2012

  Releases all free space at the end of the file to the operating system,
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent. target_percent is ignored
  if specified with TRUNCATEONLY.

  TRUNCATEONLY affects the log file. To truncate only the data file, use
  DBCC SHRINKFILE.

The last statement now tells me it only affects the log file?

So was the functionality changed, or is there an error in the documentation, or is my interpretation wrong?


texts: Update in SQL Server leads to very high value in a column

texts:
We encountered a very strange behaviour that happened only once in a few years of production of our application: An update happened via "normal" ADO.NET out of a WinForms app on a database table and a field of that table that should have remained 0 in this update contains now a value in the order of 3 billion. The field in the database is specified as decimal(18,2) and the SQLParameter used in .NET when calling the update procedure is also set to decimal with the right scale and precision.

(We have a simple update procedure that just takes all fields of that table as parameters and updates all values of the specified row.)

We have never seen this situation before and looking at the code there is no calculation around this field that could possibly end up with this high value.

I know this is a broad question, but where would you be looking for an error in this case? It happened only once and it is not reproducable. (We restored for example the databases and the application to the right point in time and replayed the user action leading to this strange behaviour. The field wasn't updated to such a crazy value in that scenario.)

We are using SP4.


texts: Need sample script for Cluster index scan

texts:
Do we have any example which is behaving as cluster index scan when ever queried with range of values. I am trying to get the conclusion on optimizer behavior as when ever the select count increases to more than 10% even the table have cluster index optimizer will take cluster index scan.

The related Query asked in below url as well.
Clarification on Clustered index scan and Clustered Index Seek


texts: Pass information to another table with the same inherit

texts:
Suppose the following scenario ... I have two tables (A, B) that inherit from a Z table, the SGBD is postgresql, these tables do not have an additional column, my question is how to pass a row from table A to table B that have the same inheritance.

The Table Z has a state column, with that column i decide which is the destiny of the data that has been inserted, this with a trigge, when execute a insert query the triger is activate and with condition is redirecte to child tables.

Now, if the state change, how can i pass to table A to table B.??

supose state is a varchar data type and the value is 'A' go to table A and if the value is 'B' go to table B, the constranint in the tables is something like that...
table A constraint.. CONSTRAINT only_A CHECK (state = 'A')
table B constraint.. CONSTRAINT only_A CHECK (state = 'B')

____________
|___Z_TABLE_|
| pk        |
| col1      |
| col2      |
| col3      |
| state     |
|___________|

_______________    ________________
|___A_TABLE____|   |____B_TABLE____|
| INHERITS (Z) |   | INHERITS (Z)  |
|______________|   |_______________|

i need to delete and reinsert in the other table or only with chage the value of state column ???

something more hard is... the table Z has a many relation with another tables...


texts: Do I need different linked server remote users?

texts:
I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup.

There may be multiple local "machines"/databases that all are linked to the same server.

I have a script (query) that creates the linked server and the remote user.

Do I need to create a new user for each machine or can all machines use the save remote user?

The script looks like this:

USE [master]
GO
/****** Object:  LinkedServer [FOODB001]    Script Date: 2015-04-16 21:36:49 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FOODB001',
                                   @srvproduct = N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'FOODB001',
                                     @useself = N'False',
                                     @locallogin = NULL,
                                     @rmtuser = N'MyUser',
                                     @rmtpassword = 'password'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'collation compatible',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'data access',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'dist',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'pub',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'rpc',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'rpc out',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'sub',
                                @optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'connect timeout',
                                @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'collation name',
                                @optvalue = NULL
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'lazy schema validation',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'query timeout',
                                @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'use remote collation',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'remote proc transaction promotion',
                                @optvalue = N'true'
GO


texts: Do the vacuum operation in postgresql and purging operation in mysql perform the same tasks?


texts:
I am planning on writing a webapp where a user can buy an object from a seller.
Also I would like to outsource the payment so that I don't have to deal with the payment process myself. I was wondering how the following scenario would work out in a NoSQL Database like MongoDB?

What happens exactly if a user buys an object, but at the same time
the seller deletes that object, because he does'nt want to sell it
anymore?
How do I deal with it in a NoSQL Database like MongoDB?
Or do I have to do it in the application level?

I saw an example that a big travel company uses MongoDB, so my guess is that scenarios like that should be possible to fix, I just don't know how. Should I stick with an SQL-Database like PostgreSQL?


texts: Necessity of having two instances (Reader and Writer) in Amazon Aurora

texts:
I'm managing an Amazon Aurora account. Somebody set it up months ago.
The service has TWO instances (same AZ):
- project
- project-eu-central-1b

Same class (db.t2.small), but different "Replication Role". First is Reader and the second is Writer. The service has only one cluster:project-cluster-1

I wouldn't like paying twice if I need only one instance but I'm not sure if Aurora actually needs two instances (W and R) to work or it was a bad initial setup.

Can I use one instance for reading/writing and "move permissions" on MySQL-side?
I'm also interested in "Reserved instances". In actual scenario I think I need to buy two reserved instances, right?

Thanks.


texts: Missing rows (gaps) on range read from the end of the table

texts:
On a data processing pipeline, getting gaps in returned data.

Process A - inserts sequentially into a table. Table has an auto-increment primary key id.

Process B - selects max(id) [into X] and then attempts "select * between id X-100 and X".

On occasion the number of rows returned is less than 100, but on rerun the query returns them all.

Tried adding "lock in share mode" to the select. Did not seem to help with getting all the data. [Expected it to have the reader wait until all current writes are finished, and then return all rows.]

Reads are done on a MySQL replica. Table is InnoDB.


texts: Test SQL Server connection with sqlcmd using machine name

texts:
How can I test a connection via machine name as the SQL logon name (ie mydomain\server01$ - as an IIS AppPool would connect) using sqlcmd?

I'm having trouble figuring out an application's SQL Server connection issue.


texts: Need advise on Indexaphobia: High value missing index with High Impact. When index is already there

texts:
I am trying to understand the true meaning of Missing index for this particular table when I already have index on it and how to address it.
I have a heavily used table and is of approximately 2.5GB. Since its heavily used, a bit hesitant to create index which are not very much required(debatable). This table was heap earlier, recently it is changed to table after changing primary key to clustered from non-clustered.
When I run sp_blitzindex with database name or with this table, it gives result as below:

Mostly it suggests to create index on APT_ID column and include suggests LOGID, RECEIVE_TIME and few other columns. If we look at table definition primary key is defined at LOGID and RECEIVE_TIME. And also we have a NC index on APT_ID column.
Table DDL is as below:
CREATE TABLE [dbo].[TXN_LOG](
    [LOGID] [int] IDENTITY(1,1) NOT NULL,
    [RECEIVE_TIME] [varchar](15) NOT NULL,
    [APT_ID] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    .
    .
    .
    [ERROR_CODE] [varchar](20) NULL,
    [MESSAGE_ID] [varchar](40) NULL,
    [END_POINT_ID] [varchar](50) NULL,
    [NODE_ID] [varchar](40) NULL,
    [TIMEOUT_NETWORK_ID] [int] NULL,
    [TXN_SUMMARY] [numeric](1, 0) NULL,
    [D_FLAG] [numeric](1, 0) NULL,
     CONSTRAINT [PKTXN_LOG] PRIMARY KEY CLUSTERED
    (
        [LOGID] ASC,
        [RECEIVE_TIME] ASC
    ))
GO

NC Index on this table is :
CREATE NONCLUSTERED INDEX [IDX_TXN_LOG_1] ON [dbo].[TXN_LOG]
(
    [APT_ID] ASC
)
INCLUDE([RECEIVE_TIME])
GO

Current usage of index doesn't seem to support NC index as write is higher than read count.

Sincere apologies for attaching images in the question. Appreciate if I can get some expert advice on this. Thanks in advance.

Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) - 12.0.6118.4
(X64)   Dec 12 2019 21:46:15   Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
 (Build 9600: ) (Hypervisor)


texts: Sql Server - Running a Job on Multiple servers

texts:
I am using SqlServer 2008.

I have a Sql Server Job that checks Disk Space and emails admins if the space is less than 15%

Instead of adding that Job to all servers is it Possible to run the Job on all servers ?

(I have those servers added as Linked Servers)


texts: Monitor SQL Server stored procedure latency

texts:
What is the best way to capture SQL Server stored procedure latency in SQL Server 2016?

Plan is to create a SSRS report and send it to management. We also enabled query store.

Thanks much


texts: Problem with EXEC MyLinkedServer...sp_executesql within transaction

texts:
I rather clueless about this so any help is appreciated.

We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET).

This stored procedure has got the follwing (here simplified) code in it:

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'DELETE FROM ... WHERE ...'
--And now the important part:
EXEC MyLinkedServer...sp_executesql @sql

So it calls sp_executesql on a linked server. Most of the time this query runs fine, however sometimes the EXEC-line fails. The error message is at the end of this post.

I am not quite sure what is happening here. I am not sure if it's EXEC not working within distributed transactions or if maybe EXEC is not running within the "normal" process (whatever that is, just guessing from the error message...).

The motivation behing the EXEC-construct in this case is that we need to delete some entries from a very big table on the linked server and this way it is much faster than just writing:

DELETE FROM MyLinkedServer.DBName.TableName WHERE ...

The error message:

Original: Die Verwendung des OLE DB-Anbieters "SQLNCLI10" mit SQL Server außerhalb des Prozesses with nicht unterstützt.

Google-Translation: The use of OLE DB provider "SQLNCLI10" with SQL Server out of process ist not supported.


texts: Linked server select Openquery causing datetime column to be an hour behind?

texts:
I have a SQL 2014 server which has a linked server to an Actian Ingres database.

When I run a simple linked server select openquery on any table with date/times, the results it pulls back is an hour out compared to when I query the ingres database itself..

When I run the same openquery on an old SQL 2008 R2 instance it pulls back the correct time (the same time as the ingres database)

Query format:

SELECT * FROM
OPENQUERY(linkedserver, 'select date/time column from tablename')

The server date/time zones are all the same, the odbc connection settings are all the same, the linked server settings and SQL instance settings are the same.

I have checked for any similar issues online but cannot find anything

Am I missing something here?


texts: Basic transactional DDL script in PostgreSQL

texts:
PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the whole thing back; otherwise commit. I'm having a hard time getting the syntax right, and I suspect my problems might stem from conflating SQL DDL with PL/pgSQL and/or not grokking the transaction semantics.

Long story short, what's the PL/pgSQL boilerplate for a script to do something like this?

My attempts have gone something like schema.sql here:

BEGIN;

CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL

EXCEPTION WHEN OTHERS THEN ROLLBACK; -- this, I gather, is PL/pgSQL

COMMIT;

\i schema.sql at the psql prompt produces a syntax error at or near EXCEPTION. OK, so EXCEPTION must be PL/pgSQL, and all this needs to go in a PL/pgSQL declaration. Let's try again:

Per the grammar at http://www.postgresql.org/docs/9.3/static/plpgsql-structure.html, this looks like:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

I don't need to name this, it's a one-off provisioning script, not declaring a function I'm going to use again. So skip the declarations and expand statements to what I had before:

BEGIN

    BEGIN;

    CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL

    EXCEPTION WHEN OTHERS THEN ROLLBACK;

    COMMIT;

END;

This blows up in even worse fashion, with syntax errors on BEGIN, EXCEPTION, and then it keeps running the rest of the script anyways, complaining about not being in a transaction.

What's the misunderstanding I have here?


texts:
I'm trying to get SQLPackage for schema compare working from CMD line.  Have to compare a master db to 300 live dbs.  All 2014 SQL and none registered as data-tier applications. PowerShell and 3rd party tools are not an option at this time.

I've followed the scripts in the first answer here:
https://stackoverflow.com/questions/20673516/command-line-api-for-schema-compare-in-ssdt-sql-server-database-project

I can successfully create .DACPAC files from my source and my targets.

If I add some objects (1 table, 3 views) to "Test_B" database, the compare script does not show those differences when I compare the master to either the database itself or to the .dacpac that was generated.  BUT, if I run a SSDT schema compare from VS, it does pick them up and will create a .sql script with the expected DROP statements.

So the question: What am I doing wrong at the cmd line that Visual Studio/SSDT is doing right? Missing a parameter?  Wrong order of things?

#Compare DACPACs

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.Test_B.dacpac /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql

#Compare master dacpac to database

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tsn:US1213113W1\SQL2014 /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql


texts: MS Access Residual Query Parameters on Copied Report

texts:
I've been working on an ms access database which was put together prior to my employment, I wanted to create a new report in order to display Quality Control information, in order to do this I copied the report which is used for producing quotes for customers (In order to retain the formatting).

I've almost completely redesigned the report and have just started writing some code to populate the report with data; I thought that I'd test the basics and added a SQL query as the report's record source.

Something like:

SELECT [Quality Report].JobNo, [Quality Report].CaseNo FROM [Quality Report]

At the moment there are no filters (WHERE clause) set.

For some reason, when loading the report outside of design view I'm receiving an InputBox() with the prompt "QuoteID", which indicates to me that there is some residual parameter from the report's previous usage; however I can't for the life of me figure out where it's coming from!

Any suggestions, ideas or answers will be greatly appreciated.


texts: Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?

texts:
Firstly 1 thread selecting a huge amount of data from a table.

Then, before the SELECT query is not yet finished, another thread deletes data from that table.

It causes SELECT query to failed with error errorCode=1205 sqlState=40001 and message that deadlock happened.

I thought that second transaction must wait until first is finished, or first transaction must return data without the changes that were made by the second transaction. How can I avoid this?

I can't change the order of transactions or control them in some way (there is many users of app, and they can do whatever they want in any order at any time). Why does it happen?

There is how queries looks like (i obfuscated them a little):

SELECT distinct P.pID, (LastNAME+' '+FirstName+' '+SurName) as NAME, 'person' as Type, CA.OrgName, PC.personemail as Email
FROM Persons P
INNER JOIN WORK PW ON (P.mainwork = PW.pwId)
INNER JOIN CONTACT PC ON (PC.pID = P.pID)
LEFT OUTER JOIN ORG CA ON (CA.orgId = PW.orgId)
WHERE (P.Status = ?)

and

DELETE FROM persons WHERE id = 1234


texts: How can I use date_trunc in postgresql to round weekend days to the following weekday?

texts:
SELECT DATE_TRUNC('day', trade_date + INTERVAL '1 DAY') from trade_table;

I have dates that are all on Monday through Friday, but when I date_trunc a date that is on Saturday (e.g. taking weekday plus 1), it is rounded to Saturday.  I want it to be rounded to the following Monday rather than Saturday.  How can I do that?


texts: How to resolve too many connections and fatal error in mysql running on vps

texts:
I am running an application PHPlist on my linode server, simultaneously running 12 PHP scripts, each of which opens a MySQL connection.  Now when I access PHPlist it often shows this error:

  Fatal Error: Sorry, the server is currently too busy, please try again
  later.

When I am trying to access phpMyAdmin, it shows me a #1040 error.
Output of my PHP scripts which run through cron jobs shows:

  PHP Warning:  mysqli_connect(): (HY000/1040): Too many connections

I am using the LAMP stack on the server with phpMyAdmin; the top output in terminal shows mysqld using 100-130% CPU. When I am trying to troubleshoot this problem I got some clues:

Increase max_connection variable : I am using 200  (100 by default)
Open table cache: 512 (400 by default)

There are lots of variables to set but I can't determine what specific ones, I am getting some reference from:
too many connections
and http://dev.mysql.com/doc/refman/5.5/en/table-cache.html

But according to my usage how to increase memory and what is the maximum memory difficult for me.

On my server I am using around 12 PHP scripts, PHPlist application for sending emails, and a major database for user registrations.

Kindly help me to resolve this problem.


texts: Splitting table with many field for convenience example

texts:
Is this good idea to split table with many field for on two parts for convenience?
Real example:
class UserData(Base):
    __tablename__ = "users_data"

        id = Column(Integer, primary_key=True, index=True)
        age = Column(Integer, index=True)
        first_name = Column(String, index=True)
        last_name = Column(String, index=True)
        country = Column(String, index=True)
        city = Column(String, index=True)
        comment = Column(String, index=True)
        users_meta = relationship("UserMeta", uselist=False, back_populates="users_data")

    class UserMeta(Base):
        __tablename__ = "users_meta"
        id = Column(Integer, primary_key=True)
        email = Column(String, unique=True, index=True)
        hashed_password = Column(String)
        created = Column(DateTime(timezone=True), server_default=func.now())
        last_visited = Column(DateTime(timezone=True))
        is_active = Column(Boolean, default=True)
        user_data_id = Column(Integer, ForeignKey('users_data.id'))
        users_data = relationship("UserData", back_populates="users_meta")

So in one table only id and data about user, the second table is a meta info.
Currently I'm don't know how many times and which fields I will use more than other.
A one more bonus question it is, is I need to create third table with correlation between email and id or I can do it without it?
P.S. I hope the data is pretty understandable to no translate in standard SQL format


texts: OFFSET ... FETCH overlapping results on second page

texts:
When using OFFSET ... FETCH, I'm having I'm getting...interesting results.

Here are two sqlfiddles to illustrate my problem.

http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8

The first fiddle is the first page, while the second is the...second page.

Here are the unpaginated results.

First thing, the paginated results are not in the same order as the unpaginated results. I'm cool with that, I guess, as I'm not showing the unpaginated results anyway.

However, event though I'm specified an offset of 0, and for some reason it decided to put them in the reverse order they were entered, it skips the first 3.

On the second page, I see see results that appear on the first page.

The fact that they all have the same value, although in this example is contrived, is a reality in the specific query I'm dealing with in real life. It's a table, and the user selected to sort by date.


texts: How to count multiple occurences on the same date

texts:
my question is related to the table down below,  where I have to count multiple occurences of "Hours" if they happen on the same "Date" by the same person.

CREATE TABLE [dbo].[Time](
    [GUIDTime] [uniqueidentifier] NOT NULL,
    [GUIDEmployee] [uniqueidentifier] NULL,
    [Date] [datetime] NOT NULL,
    [Hours] [decimal](18, 2) NOT NULL,
    [Info] [varchar](256) NULL,
    [TaskDescription] [varchar](256) NULL,

 GUIDEmployee   Date            Hours
    1       2012-03-02          2.00
    1       2012-03-02          4.50
    1       2012-03-19          1.50
    1       2012-03-19          1.50
    1       2012-03-12          2.00
    1       2012-03-16          4.50

The problem is that in order to make an overtime calculation work, I need to count the hours worked. Normally people only enter it by the end of the day (7h,8h,9h) and thats it, but there are cases like above, where employees enter their hours seperately

2012-03-02   2.00
2012-03-02   4.50

and that makes the calculation invalid.

The outcome should look like

 GUIDEmployee   Date             Hours
    1        2012-03-02          6.50
    1        2012-03-19          3.00
    1        2012-03-12          2.00
    1        2012-03-16          4.50

Favorably I'd like to have another column in the Time table with the calculation of the "whole hours worked per day". I tried Count distinct and group by and tried searching for other ways to solve this issue through calculations in a seperate view but sadly to no avail.


texts: Left joining table with existing data negative match

texts:
I have a table (TYPE_GOODS_ASSOC) set up like this:

ID| TYPE_ID| GOOD_ID
1 |   1    |   2

Another table (X) is like this:

ID| GOOD_NAME          | GOOD_ID
1 |   'Something'      |   1
1 |   'Something else' |   2

I have both GOOD_ID and TYPE_ID, and am trying to left join X to TYPE_GOODS_ASSOC to find the records with certain TYPE_ID that are not present in the table. Here is a part of the query:

SELECT * FROM table X
LEFT JOIN TYPE_GOODS_ASSOC on TYPE_GOODS_ASSOC.GOOD_ID = X.GOOD_ID
WHERE TYPE_GOODS_ASSOC.GOOD_ID is NULL AND TYPE_ID = 2

I don't get anything back. And maybe I m not writing the query right. Since I have existing entries with TYPE_1, I m looking for the GOOD_ID(s) where TYPE_ID is 2 and they don't have entry in the TYPE_GOODS_ASSOC table. What am I doing wrong here?

Desired result is :

1 |   'Something'      |   1 - which is ID 1 from Table X


texts: Best Practice for Oracle SGA and PGA Memory Components Size

texts:
I have a Server that has 128GB RAM. I have read here and there that the size of SGA + PGA can bee 80% of the RAM.
I wonder if there is any best practice for this configuration that could allow Oracle Database to utilize hardware even better?
There is also this consumption that this server will be used only to serve the Oracle database.


texts:
In SQLite, are there any differences in performance/features/reliability/... between:

1) an in-memory database:  rc = sqlite3_open("file::memory:", &db);

2) a database on a RAM-disk?

The database must be accessible from multiple processes.


texts: How to create two additional columns in a results set to act as axis for a heatmap

texts:
I have a set of results from a WSUS report that consists of computername and the status level of patching on that computer.

I have been asked to display this as a heatmap e.g each computer is a single cell with a color assigned depending on the patch status (e.g. failed, waiting for reboot, downloaded etc) and to do this I need some columns to use as axis. Heatmaps needs 3 columns (x,y and heat) I only have the heat at the moment.

I can do this with cursors iterating over each row incrementing the counters for two columns and inserting the row plus these two values into a temp table and then return that but it is not efficient.

Is there a better way of doing this?

Ultimately I am after

Computer Name | Heat | X | Y
Comp1         | 4    | 1 | 1
Comp2         | 4    | 1 | 2
Comp3         | 4    | 2 | 1
Comp4         | 4    | 2 | 2

Where X & Y are the generated columns (i.e. not in the data returned from the query)

I am using SQL Server Express 2014 for dev but the production target will probably be 2012 or 2014 full version.


texts:
I installed pgbouncer and configured it and it starts only via using this command

pgbouncer /etc/pgbouncer/pgbouncer.ini

however using service pgbouncer start it dose not start. I then set up supervior with this settings to mannage it which works fine.

[program:pgbouncer]
command=pgbouncer /etc/pgbouncer/pgbouncer.ini
stdout_logfile=/var/log/supervisor/pg_bouncer.log
stderr_logfile=/var/log/supervisor/pg_bouncer_error.log
autostart=true
stopsignal=QUIT
redirect_stderr=true
directory=/etc/pgbouncer/
user=postgres
group=postgres
preload_app = True

when i reboot the system pgbouncer dose not work,looking into the pgbouncer log here is what the error says

WARNING Cannot listen on ::/6432: bind(): Address already in use
WARNING Cannot listen on unix:/var/run/postgresql/.s.PGSQL.6432: bind(): No such file or directory
FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file

and looking into supervisor log this is what it says

LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 130
LOG listening on 0.0.0.0:6432
WARNING Cannot listen on ::/6432: bind(): Address already in use
WARNING Cannot listen on unix:/var/run/postgresql/.s.PGSQL.6432: bind(): No such file or directory
FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file or directory

can any one help out with this please


texts: Is it possible to delete cache in Mongo?

texts:
My case is that I run several queries in Mongo and measure the response time. I know that Mongo uses Memory Mapped files and cache some data in RAM.

If I create an index and run a query the execution time is 2,07 seconds. When I run the qeury again the execution time is 0,017 seconds.

This means that the file is mapped into memory by the OS on the first access and the OS will keep it in cache until it decides to page it out.

Because I want to make some benchmarks I want somehow to remove the mapped results from memory.

free -g:

               total       used       free     shared    buffers     cached

Mem:            29          29          0          0          0         29
-/+ buffers/cache:          0         29
Swap:            0          0          0

I try db.collection.getPlanCache().clear() but it doesnt work.

Then I try:

sudo service mongod stop

I reboot my machine

sync; echo 3 > /proc/sys/vm/drop_caches
sudo service mongod start

free -g:

               total       used       free     shared    buffers     cached

Mem:            29          0          0          0          0         0
-/+ buffers/cache:          0         29
Swap:            0          0          0

In above it seems that cache is free now. But when I execute again the query the execution time is 0,017 seconds. It seems that mongo has manage to cache or preserve the data somewhere. The desired execution time would be 2,07 seconds as I clear the cache.

Can anyone tell me how I can actually delete the cached results in Mongo?


texts: How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?

texts:
How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly.

Consider this code:

DECLARE @UpdateTable table (UpdateTableID int IDENTITY, UpdateTableValue int);
DECLARE @InsertTable table (InsertTableID int IDENTITY, UpdateTableValue1 int, UpdateTableValue2 int);
DECLARE @TestValue int = 5;
INSERT INTO @UpdateTable (UpdateTableValue) VALUES (1),(2),(3);
SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        (
                UPDATE      @UpdateTable
                SET         UpdateTableValue = UpdateTableValue + @TestValue
                OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
                WHERE       UpdateTableID = 2
            ) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

The last-inserted row has an identity value of 1, yet the @@IDENTITY and SCOPE_IDENTITY() functions are returning their original values from the original INSERT prior to the last statement executed.

@@VERSION:

  Microsoft SQL Azure (RTM) - 12.0.2000.8   May  2 2019 20:11:13
    Copyright (C) 2019 Microsoft Corporation


texts: How much memory will a MEMORY table take up?

texts: Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY table? 11 gig, or more?


texts: Sql, get data/rows for more selected ids in one condition, from file

texts:
I am using PostgreSQL 11.5 on Ubuntu

Question

How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids.

Example

|product_id |product_name                                                                                |product_url                                                                                |filename                                                                                |
|-----------|----------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------|
|134810     |Vliesová tapeta bílostříbrný květ                                                                   |bydleni-tapety-vliesove-tapety-vliesova-tapeta-bilostribrny-kvet                                    |331322.jpg                                                                                |
|134827     |Vliesová tapeta Grandeco Exposed PE-11-01-1                                                         |bydleni-tapety-vliesove-tapety-vliesova-tapeta-grandeco-exposed-pe-11-01-1                          |331325.jpg                                                                                |
|134827     |Vliesová tapeta Grandeco Exposed PE-11-01-1                                                         |bydleni-tapety-vliesove-tapety-vliesova-tapeta-grandeco-exposed-pe-11-01-1                          |331325i.jpg                                                                                |
|134807     |Vliesová tapeta béžová se stříbrnými pírky                                                          |bydleni-tapety-vliesove-tapety-vliesova-tapeta-bezova-se-stribrnymi-pirky                           |336628.jpg                                                                                |
|134948     |Prskavky 90 cm 3 ks                                                                                |zahrada-vanoce-vanocni-dekorace-prskavky-90-cm-3-ks                                                 |sp16-2.jpg                                                                                |
|134943     |Krmítková směs 400 g                                                                                |zahrada-zimni-sortiment-krmitka-pro-ptactvo-krmivo-pro-ptactvo-krmitkova-smes-400-g                 |427298-krm-tov-sm-s-400g.jpg                                                                        |
|135074     |Sluban B0519 Fontánový kruháč se skútrem                                                            |zahrada-pro-deti-stavebnice-sluban-b0519-fontanovy-kruhac-se-skutrem                                |m38-b0519-1.jpg                                                                                |
|145556     |Skříň LOGICO HIGH 65 x 45 x 182 cm                                                                  |bydleni-ulozne-boxy-a-kosiky-ulozne-skrine-skrin-logico-high-65x45x182-cm                           |343453-9634000-logico-high-cabinet-0270-preview.jpg                                                 |
|145556     |Skříň LOGICO HIGH 65 x 45 x 182 cm                                                                  |bydleni-ulozne-boxy-a-kosiky-ulozne-skrine-skrin-logico-high-65x45x182-cm                           |343453-9634000-logico-high-front-preview.jpg                                                        |
|135088     |Krbová kamna Verso                                                                                |stavba-kamna-a-krby-krbova-kamna-a-biokrby-krbova-kamna-krbova-kamna-verso                          |verso-1.orez2.jpg

... and many more.

I need get data only for some ids/rows. Is there a way to insert file into where condition? Or must I use where ... and where ... and where condition for every id I need?


texts: Sql Server Ranking Value Not Changing Please Help

texts:
How to convert this (MySQL Query) to SQL Server?

Or how to get a ranking value not changing while I'm using where in query:

SELECT id,
       name,
       score,
       FIND_IN_SET( score, (SELECT GROUP_CONCAT( DISTINCT score
                            ORDER BY score DESC ) FROM scores)
                  ) AS rank
FROM scores WHERE name = A;


texts: DBCC CHECKDB consistency-based I/O error

texts:
Running:

DBCC CHECKDB(DatabaseName) with NO_INFOMSGS

gives me the following error:

  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical
  consistency-based I/O error: incorrect pageid     (expected 1:7753115;
  actual 0:0). It occurred during a read of page (1:7753115) in database
  ID 11 at offset 0x00000ec9b36000 in file 'K:\UAT
  Databases\dbname.MDF'.  Additional messages in the SQL Server error
  log or system event log may provide more detail. This is a severe
  error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.'

Also i found a entry in dbo.suspect_pages

Please advise.


texts: How does this syntax work? {fn CurDate()} or {fn Now()} etc

texts:
Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand.  It appears to be some type of function call.

A sample:

SELECT o.name, o.type_desc, o.create_date
FROM sys.objects o
WHERE o.create_date < {fn Now()} -1;

This will display all rows from sys.objects that have a create_date prior to 24 hours ago.

If I display the execution plan for this query, I see that {fn Now()} is replaced with getdate() by the Database Engine:

SELECT [o].[name],[o].[type_desc],[o].[create_date]
FROM [sys].[objects] [o]
WHERE [o].[create_date]<(getdate()-@1)

Clearly, using {fn Now()} is far more obtuse than GetDate().  I for one will avoid this syntax like the plague since it is undocumented.


texts: Insert data into table where 4 fields are the same and the fifth changes

texts:
I have a table with this structure:

username varchar(35)
startdate datetime
enddate datetime
saledate datetime
packageid int

for each username there are 5 packages, so one username has 5 records in the table, and the only field that changes is packageid. Now I have been asked to insert for all the users (around 4200) another package in the table, so the statement should be like this:

insert into subscription(username,name,startdate,enddate,saledate,pkgid)
values (@username,@name,@startdate,@enddate,@saledate,41)

  41 is the value of the package field

I have included this statement in a procedure, but my problem is in how to access the username field (it logically should access a distinct(username), loop this field until there are no more users left,and in each cycle execute the procedure). The id column of the table is of no help, because the packages at the beginning were not added in group (so I cannot set the id=id+5). Any idea?


texts: Tickets created and resolved (or merged) per year

texts:
In a third-party application (RT4) running on MySQL 5.5 I have a series of helpdesk tickets represented as records in a table. Each record has a date/time created, a date/time resolved, and a merge flag (NULL or 1).

id int(11) AI PK, Created datetime, Resolved datetime, IsMerged smallint(6)

When the ticket is created the created field is populated and the resolved field and merge flag are NULL. When the ticket is closed the resolved field is populated. If the ticket is a duplicate the merge flag is set to 1, and the resolved date is set to 1970-01-01 00:00:00.

I've looked at a number of the offered related answers and of those mysql: counting number of tickets which are open per day basis seemed the most promising. I feel it's almost what I need but I can't extrapolate from the answers there to my use case.

I can list the number of tickets created per year easily enough -

select year(Created) as Created_Yr, count(*) as N_Tickets from rt4.Tickets
group by year(Created)
order by 1

A similar query can list the set of non-merged tickets per year -

select year(Resolved) as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where Resolved is not null
group by year(Resolved)
order by 1

However, what I would like is to list the number of tickets resolved or merged, per year.

I can do this year by year like this -

select 2016 as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where ( Resolved >= '2016-01-01' and Resolved < '2017-01-01' )
or ( Created >= '2016-01-01' and Created < '2017-01-01' and IsMerged = 1 )

We have data from years 2012 to 2017 and I'm hoping to avoid having to run a query per year and manually collate the results.

Is there a query that will list the Resolved year (or the Created year if the ticket was merged), for all years present in the database?


texts: GETDATE() uniqueness when used on INSERT

texts:
Recently I was reading this blog post:
http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx

which contains this section:

  Picking a Clustered Index

  There are a couple of strategies for picking
  your clustered indexes; one of the easiest and best is to add another
  column of data type datetime and use that column for your clustered
  index. Here is what you need to do:

  Add the column as data type datetime
  I usually call it Date
  Set the Default Value to GetDate().
  Make it non-null.
  Create your clustered index on it before you insert data into you.

My question is would this ever create two identical values for the Date? Does this answer change if parallelism is in use? (Assume value never specified, always comes from GetDate())

I believe I'm correct in assuming it wouldn't matter due to the behind-the-scenes uniqueifier being added, right? But I'm interested anyway.

I'm asking from a SQL2008R2 perspective but would be interested if the answer differs for any version of SQL Server from 7.0 up.


texts: SQL Server Point Constructor or version of ST_Point(x,y)?

texts:
PostGIS provides two point-constructors

ST_MakePoint(x,y,[z,[m]])
ST_Point(x,y)or the compliant version that does not support 3DZ or 4D

MySQL supports Point(x,y)

In SQL Server, Is there anyway to construct a point from two numeric inputs or reals without going from text first?

I do not see a constructor that takes numbers
in the docs on "Create, Construct, and Query geometry Instances".


texts: what is the penalty for NOT setting Disconnect users in the database when restoring backups?

texts:
setting up Log shipping between 2 sql server 2016 servers
I want my databases read-only standby on the secondary server.

there is a tick box saying Disconnect users in the database when restoring backups.

what if I don't tick this box?
The restore of the log would not come trough because it could not get exclusive lock on the database?

But then, what if someone is logged in for hours? Hours without restoring the logs?


texts:
How can I have a MySQL database number column that only allows one 1, but infinite 0's? Some type of constraint or something.

Elaboration

To clarify what I mean and why I want this:

Imagine you have a MySQL table (let's say "accounts"). An account can be "assigned" to multiple people, but only one person can "own" it. This is in a way similar to bank accounts or Netflex.

So, the schema might look like

Accounts
[id] [name]

Account_membership
[account_id] [user_id] [is_owner]

Here's the rub: You can only have one owner. But, You can have infinite non_owners who are members. How can I ensure that this is the case?

A unique constraint won't work for this, because (1, 1, 1), (1, 2, 0), (1, 3, 0) are valid rows.

So, is there some way I can accomplish this with mysql?


texts: I want to regularly run DBCC CHECKDB on my production database. My database is real-time and DBCC CHECKDB is very expensive query, that`s why I want to write a maintenance plan to execute the query on nights when the activity is low. I want to set the query results to be sent as mail in order to check it when I am able. My question is, how can I set the query results to be sent to my e-mail when it is executed ?


texts: SQL Server Patch Level Is Inaccurate, Causing Upgrade Issues

texts:
I have a box that lists SQL Server 2014 SP2 (12.2.5000) in both add/remove programs and the registry

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
  Server\120\SQLServer2014\CurrentVersion\PatchLevel

However, if I run SELECT @@VERSION it returns 12.0.2000. If I look at the sqlsrvr.exe it also lists 12.0.2000.

Doing some testing I can get a machine in this state (not sure if this is how it happened in production).

Windows 7 SP1 image with SQL Server 2014 RTM (12.0.2000)
Execute SQL Server 2014 SP2 installer as an upgrade (non-automated)
Once on the accept license screen, cancel the install
Reboot the machine

The big problem is that if I execute the SQL Server 2014 SP2 upgrade package against this machine nothing is upgraded. What are my options? Do I just need to tear out SQL Server and install clean? I would love to avoid that if possible as this will have to happen at 200+ locations

EDIT:
Okay, so this is where I say I'm not a DBA and I figured out what was happening. Apparently the "Upgrade" option does not upgrade the instance. Still doing some reading on that, but I can "add features to an existing instance" and get things upgraded. Still digging through, I will post an answer once I have everything squared away. Thank you for all the suggestions thus far.

EDIT 2:
At current this is the installer that was used.
https://www.microsoft.com/en-us/download/details.aspx?id=53167
When running through this upgrade the "Upgrade from SQL Server 2005, 2008, etc. option is chosen. I believe this is the issue at it appears to just updgrade Shared Features? I could use this installer and go through the add features to an existing installation and select the appropriate instances.

I believe this is the installer we should be using as it is a patch to SP2.
https://www.microsoft.com/en-us/download/details.aspx?id=53168
Select all instances and go. Seems easier.

Did I break this down about right?


texts: PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

texts:
In PostgreSQL 9.2.3 I am trying to create this simplified table:

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (user_id WITH =, startend WITH &&)
);

But I get this error:

ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define
       a default operator class for the data type.

The PostgreSQL docs use this example which does not work for me:

CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

Same error message.

And this one, which does not work for me either:

CREATE TABLE zoo (
    cage   INTEGER,
    animal TEXT,
    EXCLUDE USING gist (cage WITH =, animal WITH <>)
);

Same error message.

I am able to create this without any problem:

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (startend WITH &&)
);

and this:

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING btree (user_id WITH =)
);

I've spent quite a bit of time searching for hints about figuring out how to make this work, or figuring out why it won't work. Any ideas?


texts: Using totals on aggregates to improve performance

texts:
I have two tables: details and totals of these details.

Details (Slow solution):

select
    OrderId             =   r.OrderId
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
from dbo.Order r
group by r.OrderId

Totals (Fast solution):

select
    t.OrderId
  , t.TotalQty
  , t.TotalGrossConsid
  , t.IsValid
from dbo.OrderTotal t

Sometimes totals become invalid (some job has to recalculate changed totals but it delays).
As you understand the second query is faster and the number of valid totals is more then invalid ones.
So I am looking for a combined query that returns valid totals from the second table (totals) and returns dynamically recalculated totals by using the first slow query. So my goal will be reached: all totals are valid and time to response is faster then full recalculation.

Here is my attempt (Hybrid solution):

with fast_static(OrderId, TotalQty, TotalGrossConsid, IsValid)
as
(
    select
        t.OrderId
      , t.TotalQty
      , t.TotalGrossConsid
      , t.IsValid
    from dbo.OrderTotal t
)

, slow_dynamic(OrderId, TotalQty, TotalGrossConsid)
(
   select
        OrderId             =   r.OrderId
      , TotalQty            =   SUM(r.Quantity)
      , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
    from dbo.Order r
)
select
    OrderId, TotalQty, TotalGrossConsid
from fast_static
where IsValid = 1
union all
select
  OrderId, TotalQty, TotalGrossConsid
from slow_dynamic s
    --inner join fast_static ff
    --on ff.OrderId = s.OrderId
where   --ff.Valid = 0 -- too slow!!!
    s.OrderId in (select OrderId from fast_static f where f.Valid = 0)

I have compared the Fast solution and Hybrid one, I got 32% to 68% (relative query costs). If you can see commented variant it equals to  1% to 99% (too bad). Is it possible to improve this query?

ADDED

@gbn:

Valid  =  case when i.OrderId is null then 1 else 0 end
...
dbo.OrderTotal t  left join dbo.InvalidOrders i

Yes, I have a job to recalculate totals and this process is not synchronized to query requests. InvalidOrders tables is a little one that store records to know that totals is invalid (to be recalculated)

SOLUTION

Indexed views are the best choice. Be aware on SQL Server edition (noexpand hint for non-enterprise editions) and be ready to recreate some database objects (SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON) to start using indexed views on client side.


texts:
I have a wide table that looks like this:

Table Windspeed
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  13.09   13.95   10.16                   11.71
09-Apr-19 00:05:00  12.17   11.98   12.54                   10.66

I need to generate a new table in the same format, but replace the column values from a lookup table that looks like this:

Table Powercurve
WindSpeedBin    Power_WdSpd Turb_Num
...
9.5             1216.9      1
10              1362.9      1
10.5            1476.3      1
11              1597        1
11.5            1701.5      1
12              1798.3      1
12.5            1828.5      1
13              1840        1
13.5            1846.1      1
...
9.5             1211.9      2
10              1342.9      2
10.5            1436.3      2
11              1517        2
11.5            1691.5      2
12              1713.3      2
12.5            1801.5      2
13              1833.1      2
13.5            1846.1      2
...

And finally, a third table that looks like this:

Table NameLookup
TName   Tag     TNum
A-004   A04     1
A-005a  A05     2
A-006a  A06     3
A-008a  A08     4
A-009   A09     5
...
E-009   E09     162

I need to generate a table that takes the values from table 1 (Windspeed), looks up the values in table 2 (Powercurve) (round to the nearest .5 to match WindSpeedBin), using the column names to lookup the Turb_Num value. Any ideas how to go about this? the end result would look something like this:

Table Expected_Production
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  1840    1846.1  1528.5                  1780.6
09-Apr-19 00:05:00  1828.5  1713.3  1843.9                  1614.5
...

This is for Microsoft SQL Server 2017


texts:
We want to set up a second instance/server of SQL Server such that both instances will be kept in sync. The reason is we need to separate some users and processes out to a separate server for business reasons.

More information (requested in comments):

Neither instance would be read-only
It does not need to be synced in realtime, but that would be nice
We have full control of the table schemas

We've done a lot of research, but most potential options are part of disaster recovery features, so we're not sure if those are right for this.

I'm not sure how to whittle down this list without actually trying the different options, but that would be very time consuming. If there's an option I'm missing, that would be very helpful.

Always On Availability Groups
Merge Replication
Database Mirroring
Synchronize Data
Windows Server Failover Clustering with SQL Server (Is this what is normally meant by clustering?)


texts:
Is this normal? To be exact it uses 1,068,000 KB. It occasionally shoots up to 1,400,000 if query takes too long.

My total system memory is 3GB and I am running WinXP. Is there a specific amount of RAM recommended for SQL Servers?

The reason I am asking this question, could our database have problem, if it uses this much RAM? We dont have a DBA here, just a programmer. Also

Does running multiple instances of SQL Servers affect performance?
Does running multiple databases hit peformance? We use only 1 but there are 5 databases running (old ones)

I am using SQL Server 2005 Express. It is not a huge database, just about 40,000 records


texts: MySQL Workbench Ground To A Virtual Halt

texts:
I have been using MySQL Workbench for a while for both backups and to develop a database and associated queries. Over that time I have noticed that it slows noticeably if the SQL Editor has to many tabs (20 or over) and have tried to keep to a maximum of a dozen. However I came in this morning to find on start up there were approximately 150 tabs! 30% are the same query! In the midst of this are a few complex queries that I need to copy and save which means I do not want to reinstall at the moment. The problem is that it is taking up to ten minutes to access and close some of the tabs so it could potentially take days to sort out. Is there a back door into these?

Also what I cannot understand is why the tabs self replicated themselves?

As I have worked through Workbench has sped up and is now averaging under five minutes per tab! Only data entry can be more boring!


texts: How to configure encrypted SQL Server connections to prevent MITM attacks?

texts:
It has come to my surprise that SQL Server by default does nothing to prevent man-in-the-middle attacks when configuring your SQL Server to enforce connection encryption.

Forcing encryption on your connections is dead-simple. You go to Sql Server Configuration Manager, expand SQL Server Network Configuration, Protocols for [your instance], right-click TCP/IP, change Force Encryption to 'Yes', restart SQL Server and you're done.

Per Microsoft TechNet (https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx), this will generate a Self-Signed Certificate. Fine, no problem, But I expect my clients to complain that the certificate is not trusted.  However, they do not. They happily connect to it. I then realize that 'Trust server certificate' is checked by default in SQL Management Studio. Fine, but the most shocking realization is: even if you uncheck "Trust server certificate", SSMS does not complain, warn, or reject the connection.

So by default SQL Server is completely susceptible to MITM attacks because clients do nothing to verify the certificate from SQL Server. The certificate shouldn't have to be signed by a CA: the certificate should have to be configured as a trusted certificate on the client. If that's not true, what is the "Trust server certificate" checkbox for?


texts: How can I prevent gaps in a MySQL table AUTO_INCREMENT field?

texts:
For example, I have a table for user registration:

CREATE TABLE IF NOT EXISTS test_user (
   user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
   user_name varchar(50) NOT NULL,
   PRIMARY KEY (user_id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I want the user_id field ID numbers to be continuous, without gaps.

Failed transactions will occupy some ID numbers, is there any way around this?


texts: Socket recv() timeout on secondary nodes

texts:
We started using MongoDB pretty recently. We are on version 2.6.3. We have a replica set of 4 nodes (1 Primary + 3 Secondary)

We are currently seeing some latency in replication. The Secondary nodes seem to be falling behind primary by a few minutes (2 mins to 20 minutes).

Looking at the log file of Secondary nodes, I do see socket timeouts.

  2014-10-01T08:42:03.485-0400 [rsBackgroundSync] Socket recv() timeout
  157.206.233.10:27017 2014-10-01T08:42:03.485-0400 [rsBackgroundSync] SocketException: remote: 157.206.233.10:27017 error: 9001 socket
  exception [RECV_TIMEOUT] server [157.206.233.10:27017]
  2014-10-01T08:42:03.485-0400 [rsBackgroundSync] DBClientCursor::init
  call() failed 2014-10-01T08:42:03.485-0400 [rsBackgroundSync] replSet
  sync source problem: 10276 DBClientBase::findN: transport error:
  XSNW10K832W:27017 ns: local.oplog.rs query: {}
  2014-10-01T08:42:03.485-0400 [rsBackgroundSync] replSet syncing to:
  XSNW10K832W:27017 2014-10-01T08:42:23.110-0400 [rsBackgroundSync]
  replset setting syncSourceFeedback to XSNW10K832W:27017

Here is what I see in the log file of Primary.

  2014-10-01T08:42:23.260-0400 [conn492807] getmore local.oplog.rs
  cursorid:95505391617 ntoreturn:0 keyUpdates:0 numYields:1
  locks(micros) r:8846 nreturned:1 reslen:1629780 108434ms
  2014-10-01T08:42:23.260-0400 [conn492807] end connection
  157.206.235.17:61991 (551 connections now open) 2014-10-01T08:42:23.276-0400 [conn496810] getmore local.oplog.rs
  cursorid:96066510233 ntoreturn:0 keyUpdates:0 numYields:2
  locks(micros) r:4403 nreturned:1 reslen:1629780 108438ms
  2014-10-01T08:42:23.276-0400 [conn496810] end connection
  157.206.235.16:58038 (550 connections now open) 2014-10-01T08:42:23.276-0400 [conn496812] getmore local.oplog.rs
  cursorid:95180580732 ntoreturn:0 keyUpdates:0 numYields:1
  locks(micros) r:2960 nreturned:1 reslen:1629780 108440ms
  2014-10-01T08:42:23.276-0400 [conn496812] end connection
  157.206.235.15:60989 (549 connections now open) 2014-10-01T08:42:23.291-0400 [conn496843] end connection
  157.206.235.17:55712 (548 connections now open)

Any insight on what is causing the socket timeouts and how to identify/fix the problem?


texts: Streaming replication version between different minor versions

texts:
I want to know few details about streaming replication.

I want to setup streaming replication between version 9.4.4 and 9.4.20 ?

I have the master server in centos6.6 with postgres version 9.4.4 which is a physical machine and we have created a new virtual machine centos7.5 and postgres version is 9.4.20. I want to know if this will cause any problem due to version mismatch.

Also, How can I download specific minor version 9.4.4 of postgres ? I checked some official documentation and by yum I am able to see only 9.4 which is actually 9.4.20 after I installed.


texts: Granting Execute Permissions on Stored Procedures, Functions and Views

texts:
I am using a user which has read permission only. It basically has permission to

Connect
Execute

However, it does not have execute permissions on Functions and Stored Procedures. In part of my reports, I am calling my own functions. I would like to give my readonlyuser permission to execute SP, Functions and Views.

I came across  this code which shows how to give execute permission on Stored Procedures and Functions for a particular user.

- Grant Execute on all functions for testuser
declare @username varchar(255)
set @username = 'testuser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='PROCEDURE'

and for functions

declare @username varchar(255)
set @username = 'testuser'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE='FUNCTION'

Now, here it looks like this permission applied to existing SP and functions only. If you add a new function, you have to run this code again to grant permission.

Questions

If my above interpretation is not wrong, is there a way to give execute permission to a read only user so that you have permission on all functions and stored procedures, even if you create new functions?
Can I apply the same permissions on Views?
In this thread, it is said you cannot give execute permission on function which returns a table. Is that true?

I know there are a couple of questions but all of them are related. Better to be in one place.


texts: Update value by column/field in INSERT SQL

texts:
I want to update the column value in multi-rows insert SQL.
For example:

INSERT INTO `product_price` (`product_id`, `store_id`,`price`)
VALUES (20001, 1, `price`+10),
(4442, 1, `price`-5),
(2442, 1, `price`*1.1),
(3002, 1, 399),
ON DUPLICATE KEY UPDATE `price` = VALUES(`price`);

Where you can see, I want to update the price by some constant value or by some self updates (+, -, *, /).

But it doesn't work in case of INSERT (works fine in UPDATE statement).

How to make it workable in INSERT statement.
FYI, I am using MySQL 5.7.


texts:
I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just created as a subquery in the JOIN, for instance:

SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
FROM oldcontracts
JOIN (SELECT DISTINCT name, identifier FROM jobs)
ON oldcontracts.job = jobs.name
;

The thing here is, how would we go about setting an ON condition that would work? I say this because the syntax is of the following form:

table1.column <condition> table2.column

Where apparently, SQL 2005 is very strict about where we get those columns from. Meaning I get an error on jobs.name saying it is not a valid identifier. I assume SQL throws this error because jobs was not the table that is being joined in the operation.

I know that the way around this is to simply set jobs as the table2 to use in the JOIN, and adjust the SELECT as need be, then everything would work, but I'm curious to know if there's a way to address a column of a subquery, given that that specific subquery is the one being used as table2 in the JOIN operation?


texts: I run UPDATE INDEX STATISTICS on some HUGE table. And it runs already about 1 hour. Is there some procedures/ways to find the percentage of executing this command? sp_who, sp_sysmon don't help. Thanks.


texts:
Can i call a mysql stored function from another mysql stored function or stored procedure, passing a string to the first function which is the name of the second function to call. I.e choosing the the second function via a value in field.

I suspect I can't and I will have to use a case statement, but I have to ask :)

Something like this?

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                            data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

RETURN ($function_name)(to_process);
END


texts: How to restrict table access to a application

texts:
I would like to know how I would go about restricting access to a table so that only a certain application can access the data within.

Where I work, we have a database application that rather stupidly stores user logon details (including passwords) in plaintext. There is a vulnerability in the way the software works that allows any user to run a SELECT query on the users table, and return all this information. This has been reported to the application developers, and should hopefully be fixed in a future version.

For now however, would I be able to change security settings so that only this application can access that table, and users of the application would be denied access? Users should still be allowed to run UPDATE queries to change their passwords, but SELECT is a big no.


texts: A Script to insert dummy Data in all tables of Database

texts: I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ability to run queries and Test Apps.


texts: Execution time of triggers fired when a procedure is executed in postgresql

texts:
I have a stored procedure which may delete and/or insert tuples into a database table. This table has an AFTER INSERT OR DELETE OR UPDATE trigger. Basically I want to retrieve how much time was spent in this trigger as a result of changes that were caused by the stored procedure.

I have tried to use EXPLAIN ANALYZE for this:

EXPLAIN (FORMAT JSON, ANALYZE, TIMING) SELECT f(x);

However the result that I get contains no information about triggers:

[
   {
      'Plan':{
         'Node Type':'Result',
         'Parallel Aware':False,
         'Startup Cost':0.0,
         'Total Cost':0.26,
         'Plan Rows':1,
         'Plan Width':4,
         'Actual Startup Time':37.086,
         'Actual Total Time':37.087,
         'Actual Rows':1,
         'Actual Loops':1
      },
      'Planning Time':0.041,
      'Triggers':[

      ],
      'Execution Time':37.092
   }
]

The database system is posgresql, accessed in python via psycopg2. Is there any way to get the timings from the database system directly?


texts: Adding time slots through trigger SQL Server

texts:
I am quite new to SQL. I am now working on something to schedule appointments.
I found an existing answer about time slots, but did not quite understand.

I need to automatically add time slots to the timeslots table according to the availability table. if availability from start at 10:00 and end at 16:00, and duration is 60 minutes, I need to insert 6 time slot rows.

Would you please be kind enough to let me know what I should do?
Do I need to do it through a trigger? Or do I need to redesign my schema?

My trigger is like this, apparently not really working:

    CREATE TRIGGER trigger_addSlots ON availability
    FOR INSERT AS
    BEGIN
        declare @AvailabilityId int
        declare @SlotStart DateTime
        declare @SlotEnd DateTime
        declare @NumberOfSlots int
        declare @Duration int
        declare @SlotDoctorId int
        declare @i int

        Select @AvailabilityId = Id from inserted
        Select @SlotDoctorId =DoctorId from inserted
        Select @SlotStart =AvailableFrom from inserted
        Select @SlotEnd = AvailableTo from inserted
        Select @Duration = AppointmentDuration from inserted

        set @NumberOfSlots = convert (int, (@SlotEnd - @SlotStart))/@Duration

        set @i = 0;
        While @i<@NumberOfSlots
          Begin

            insert into timeslots(AvailabilityId, SlotStart, SlotEnd,
            SlotDoctorId,IsAvailable) values (@AvailabilityId, @SlotStart,
            @SlotStart+@Duration,
            @SlotDoctorId,1)
            Set @SlotStart = @SlotEnd
            Set @SlotEnd = @SlotEnd + @Duration
            set @i=@i+1
          End

    END

schema


texts:
Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP.

Are there any good and free tools for managing a PostgreSQL database?

I have tried with pgAdmin III but it was a far from intuitive application to use, compared to phpmyadmin that I have used before. What are PostgreSQL DBAs usually using? do they use graphical tools like pgAdmin III or is it mostly command line tools?


texts: How to create a table with data from other tables in Postgres?

texts:
How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6.

This is a spatial data with the Postgis extension enabled so they have geometry that I need to put in the first and second columns. The output table that I'm looking for is something like:

X coordinate | Y coordinate | field 3 | field 8 | field 20


texts:
I have a user table and I need to create a VIEW so a user can only see their own data/row.

How could I achieve this? I have been searching online but I can't find anything useful

User with user_id, name, address