texts: Performance problem with big query on first run

texts:
I'm having a problem with a query usually being so slow on first time run that it times out and then executing in under a second once it's loaded into cache.

The query looks something like this

exec sp_executesql N'SELECT
    [Project1].RecordID AS [RecordId],
    ...
    FROM ( SELECT
        [Extent1].RecordId] AS [RecordId],
        ...
        FROM Items AS [Extent1]
        LEFT OUTER JOIN ItemParameters as [Extent2]
        ON [Extent1].[RecordId] = [Extent2].[SampleId]
        WHERE ...
    ) as [Project1]
    ORDER BY [Project1].CreatedDate DESC, [Project1].RecordId ASC, ...

The query is generated by EntityFramework for me and only has this one join. The Items table is ~3.5 million rows and ~2.5 GB data and the ItemParameters is ~23.6 million rows and ~19.2 GB data. The query returns 11400 rows and, as far as I can tell, properly hits the indexes.

I've written a test program that does this in a loop, forever:

Restart the SQL Server service (MSSQLSERVER).
Sleep for 6 minutes. To allow it to start up.
Execute above query.
Write execution time to a file.

The results are disturbing. Sometimes it takes 4 minutes and sometimes it takes 4 seconds and I don't see why. It is running on a VM Ware virtual machine. On my developer laptop the same query (against a third of the data) is completed in 3 seconds.

I've run it with set statistics io on and set statistics time on and the output looks like this:

(11409 rows affected)
Table 'ItemParameters'. Scan count 3803, logical reads 19689, physical reads 2724, read-ahead reads 4261, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Items'. Scan count 109, logical reads 40423, physical reads 4, read-ahead reads 4089, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 546 ms,  elapsed time = 92636 ms.

 SQL Server Execution Times:
   CPU time = 702 ms,  elapsed time = 94012 ms.

Things I've done to speed up performance:

Run sp_blitzFirst from https://www.brentozar.com/blitz/. Gave nothing.
Set SQL Max memory to 90 % of my 32 GB as per https://www.brentozar.com/blitz/max-memory/.
Rebuilt all the indexes and column statistics.
Disabled the SQL Full Text service. We're not using it.
Restarted the server various times.

What can I do to make sure the query executes quickly enough every time? Any help is greatly appreciated.

EDIT

Execution plan for poor-performing query (132 seconds): https://www.brentozar.com/pastetheplan/?id=Syxbf-q5m
Ex
Execution plan against cache (1 second): https://www.brentozar.com/pastetheplan/?id=ByegNZcqX


texts: InnoDB page_cleaner error message: The settings might not be optimal

texts:
My server is in idle state but I often get this error:

Note: InnoDB: page_cleaner: 1000ms intended loop took 35165ms.
The settings might not be optimal.
flushed=0 and evicted=0, during the time

How can I fix this issue?


texts:
I have a table in Access like this:

ID |  Op_ID
1  |  1
1  |  2
2  |  3
3  |  4
3  |  5
3  |  6

Is there a SELECT query that I can run in Access that would give me something like this?:

ID |  Count(ID)
1  |  1
1  |  2
2  |  1
3  |  1
3  |  2
3  |  3

So basically I want a Count of ID that loops over ID every time ID changes.


texts: How do I select distinct on one column and return all other columns?

texts:
I am using vb.net/access/Microsoft.Jet.OLEDB.4.0/DbaseIV to do this. The syntax varies between vb.net and SQl Server so I am having trouble.

I need to select one of each version in the data (using the code field) while keeping all the columns in the final output. I am selecting distinct on a code field but I cannot figure how to return the rest of the columns.

The issue is with this line

Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

Here is all of my code:

**Dim ofd As New OpenFileDialog
    With ofd
        .Filter = "DBASE File (*.dbf)|*.dbf"
        .Multiselect = False
        .CheckFileExists = True
    End With

    If ofd.ShowDialog() = DialogResult.OK Then
        Dim fi As New IO.FileInfo(ofd.FileName)
        Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" _
                                            & fi.DirectoryName & "'")
        Dim TableName As String = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length)
        Dim cmd As New OleDb.OleDbCommand(TableName, cn)
        cmd.CommandType = CommandType.TableDirect

        cn.Open()
        Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
        dt.Load(rdr)

        SelectField.ShowDialog()

        Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)

        dBaseConnection.Open()

        Dim SQLCreateCommand As String

        Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

        Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(sql2, dBaseConnection)

        dBaseCommand.ExecuteNonQuery()
        dBaseConnection.Close()

        cn.Close()
        cn.Dispose()
    End If**

This is just an example of my output i need to select all columns using an asterisk or something....


texts: Storing hex values as strings or integers?

texts:
I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints.

I'm migrating the database across servers and I've noticed that re-populating the Fingerprint table takes hours. It seems that each fingerprint is given an integer id and is stored as a varchar, there's an index on the fingerprint column and hence the slow inserts.

My question is: is it worth my while to refactor this design and remove the Fingerprint table altogether? I would guess that indexing on numbers rather than strings would be faster, is this correct?

If the design looks like this: (hopefully this make sense)

Object(1,*)<----->(*,*)ObjectFingerprint(*,*)<------>(1,*)Fingerprint
 id                      object_id                          id
                         fingerprint_id                     fingerprint

Would it be better to use:

Object(1,*)<----->(*,*)Fingerprint
 id                     object_id
                        fingerprint

I'm using a MySQL server with INNODb tables and I have roughly 1.4 million objects.


texts: Recommended postgres parameters for index creation with 244gb RAM and 32 CPUs?

texts:
I have a 200 GB database with 26 tables that I need to create indexes on.

Currently index creation takes a long time on the biggest tables, as they have ~12gb and 400,000,000 rows in them (>4 hours).

I've set maintenance_work_mem to 100gb, and set max_parallel_workers to 30.

Are there any other parameters I should tune to improve the index creation speed?

This is on AWS Aurora, using postgres-10.6, in case that makes a difference.

There is no one else using the database and downtime/full locks are fine.


texts: No 'Copying to tmp table' state in profiling a mysql query

texts:
I have a table with 100k rows

CREATE TABLE `small_table` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `varc` varchar(255) DEFAULT NULL,
  `txt` text,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=103925 DEFAULT CHARSET=utf8

and I had executed the following query

select varc,count(*) from small_table group by varc;

and I had profiled the same

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000060 |
| checking permissions | 0.000010 |
| Opening tables       | 0.004685 |
| init                 | 0.000025 |
| System lock          | 0.000006 |
| optimizing           | 0.000002 |
| statistics           | 0.000010 |
| preparing            | 0.000006 |
| Creating tmp table   | 0.000020 |
| Sorting result       | 0.000003 |
| executing            | 0.000005 |
| Sending data         | 0.001720 |
| Creating sort index  | 0.000033 |
| end                  | 0.000002 |
| query end            | 0.000004 |
| removing tmp table   | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000004 |
| freeing items        | 0.000015 |
| cleaning up          | 0.000007 |
+----------------------+----------+

The profile has Creating tmp table state (by mysql docs it means a tmp table is created either in memory or in disk). My doubt is that the profile does not have Copying to tmp table state (during the state, The server will be copying to a temporary table in memory.) My assumption is that it has created a tmp table and not using it. Am I right?

Where is my Copying to tmp table state?


texts:
I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error:

C:: Permission denied

How do I solve this?


texts: "Credential retrieval failed" during recovery of an Oracle database

texts:
When starting up automatic instance INSTANCE:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/19/2015 15:40:19
RMAN-04006: error from auxiliary database:
    ORA-12638: Credential retrieval failed

I created an Oracle 12c database with 1 CDB and 2 PDB and told Oracle to create a user for me and also added this user in the registry to have access to the eventlog. I don't have any problem in restore but recovery is giving the above error. I am doing PIT restore and then recovery using these commands:

shutdown immediate;
startup;
RUN {
configure default device type to SBT_TAPE;
set until SCN = 2259403;
restore pluggable database PDB1;
recover pluggable database PDB1 auxiliary destination='C:\oracle\12.1.0\dbhome_1\temp';
[STDIN]: }
alter pluggable database PDB1 open resetlogs;
EXIT;


texts:
We have a table which has a column defined for set(to group rows) and a column defined for hierarchy(order position), how do we enforce hierarchy ordering position integrity within each set?

An example of what I mean.

                set      order
+----+-------+-------+----------+
| id | color | group | hierachy |
+----+-------+-------+----------+
| 1  | blue  |  bmw  |     2    |
| 2  | green |  bmw  |     4    |
| 3  | orange|  bmw  |     1    |
| 4  | red   |  bmw  |     3    |
| 5  | blue  | toyota|     2    |
| 6  | green | toyota|     1    |
| 7  | blue  | ford  |     1    |
+----+-------+-------+----------+

Currently, the hierarchy values of these above rows are all perfect, however we can ruin integrity in the rows below by doing the following.

+----+-------+-------+----------+
| id | color | group | hierachy |
+----+-------+-------+----------+
| 1  | blue  |  bmw  |     2    |
| 2  | green |  bmw  |     4    |
| 3  | orange|  bmw  |     1    |
| 4  | red   |  bmw  |     2    | BAD - duplicate position 2 in set, must be 3
| 5  | blue  | toyota|     8    | BAD - order position should be next number up in set, not 7
| 6  | green | toyota|     1    |
| 7  | blue  | ford  |     2    | BAD - as only one row in set, position should start at 1
+----+-------+-------+----------+

Here I have highlighted three problems which can occur, how can we enforce integrity here.

PS Is there a name for what I have described? I assume in maths there are some terms for this, I did some research around ordinal numbers but it doesn't fit exactly into this.

Describing this as just a set wouldn't allow for the issues I raised to be assumed problematic.

Thanks


texts: Trigger on 2 tables : issue with INSERT and UPDATE

texts:
This is related to my previous question:
Trigger to cascade updates to a second table.

In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields in metadonnees (formerly table2). Merged like this now:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
RETURNS trigger AS
$BODY$
begin

IF (TG_OP = 'INSERT') THEN
    insert into activite.metadonnees (gid, date_publication, contributor, code_tranche)
    VALUES (new.gid, current_date, current_user, NEW.code_tranche)
    ;
    return new ;

elseif (TG_OP = 'DELETE') THEN
DELETE from activite.metadonnees
where gid = old.gid ;
return old ;

elsif (TG_OP = 'UPDATE') THEN
UPDATE activite.metadonnees
SET (contributor, code_tranche) = (current_user, NEW.code_tranche)
where metadonnees.gid = new.gid
;
return new ;
end if ;

end;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER trg_tbl_emprise_metadonnees
AFTER INSERT OR DELETE OR UPDATE
ON activite.emprise
FOR EACH ROW
EXECUTE PROCEDURE activite.tbl_emprise_metadonnees();

Table definitions:

CREATE TABLE activite.emprise
(gid integer NOT NULL DEFAULT PRIMARY KEY nextval('activite.emprise_gid_seq'::regclass),
surface numeric(9,2),
annee integer,
ro character varying(50),
numope character varying(12),
typope character varying(25),
geom geometry(MultiPolygon,2154),
typemp character varying(30),
nomope character varying(80),
numoa character varying(20),
numprescr character varying(25),
tranche integer DEFAULT 1,
code_tranche character varying(15),
producteur character varying(15),
notice_rapport character varying(50)

CREATE TABLE activite.metadonnees
(gid integer NOT NULL PRIMARY KEY,
date_publication date,
"date_création" date,
"généalogie" character varying(250),
"résolution_spatiale" character varying(5),
responsable character varying(10),
restrictions character varying(100),
source character varying(15),
creator character varying(50),
publisher character varying(80),
identifier character varying(50),
title character varying(80),
subject character varying,
code_tranche character varying(15),
contributor character varying,
dates character varying,
type_donnees character,
format character varying,
language character varying,
coverage character varying

If I use AFTER INSERT OR DELETE OR UPDATE trigger then when a record is inserted into emprise, the field contributor is overwritten by the current_user value for all records.

If the trigger is limited to AFTER INSERT OR DELETE then after insertion everything goes well: the field contributor is not replaced for all records (as desired), just the record concerned in emprise.

In both cases, the contributor and code_tranche are updated if, after insertion, code_tranche value is changed in table emprise. I will conclude that UPDATE is the cause of this dysfunction but I am unable to find why.


texts: Creating CDC system table on SQL Server 2008

texts:
I am facing problem with CDC tables in my database. I think few of these tables are damaged and are not capturing any audit information. I tried to disable CDC on these tables, but that did not work (I didn't even get an error). So I manually dropped a table.

Now I cannot see the table in system tables - but its still not properly deleted. So I cannot re-enable it.

I have got a fix here: http://support.microsoft.com/kb/966306

which is to manually create the CDC table of the same name in system tables - and retry disabling it.

Now the question is how do I manually create a system table.

Thanks


texts: SQL Data in form of Date+Time bulk insert into one column

texts:
Background of problem: To insert 2 seperated fields of data (from a big flat file document of data) with bulk insert. Columns in question that i want to get data from is in its current form in date time format (in flat file document).

I want to: insert these 2 fields of data into one column.

Idea of solution: Is it possible by xml format file point the fields of data into one column?

Example code (One line of data):

2016-01-01  00:00:00            GTM         clocktic

Into column:

   [TStamp] [datetime]     NOT NULL,

With Format file:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LEN GTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="TStamp" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="2" NAME="var2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="var3" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="var4" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="var5" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="var6" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="var7" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="var8" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="var9" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="var10" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="var11" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="12" NAME="var12" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="13" NAME="var13" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>


texts: Display a row for each month in a range defined by two columns


texts: Multiple ADD statements for an ALTER statement

texts:
I am trying to do something like this:
ALTER TABLE mbk_user
    ADD uuid varchar NOT NULL,
    ADD email varchar NOT NULL,
    ADD handle varchar NOT NULL;

but this doesnt seem to valid, I have to do this instead?
ALTER TABLE mbk_user
    ADD uuid varchar NOT NULL;
ALTER TABLE mbk_user
    ADD email varchar NOT NULL;
ALTER TABLE mbk_user
    ADD handle varchar NOT NULL;

is there a workaround?


texts: Can I select the rows that have the greatest Column A values compared to all other rows which have identical columns B in MySQL?

texts:
Perhaps not the clearest explanation.
I have this table:
+-----+------+
| ID  | Time |
+-----+------+
|  1  |  5   |
|  1  |  2   |
|  1  |  4   |
|  2  |  1   |
|  2  |  5   |
|  3  |  6   |
+-----+------+

I want the row with ID=1 that has the greatest Time compared to other rows where ID=1.  The same with WHERE ID=2, etc.  So essentially "get me all the rows that have greatest Time as compared to other rows that have the same ID as them".  So the results in this case would be:
+-----+------+
| ID  | Time |
+-----+------+
|  1  |  5   |
|  2  |  5   |
|  3  |  6   |
+-----+------+


texts: pg_restore slow after adding a check function

texts:
I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records.
The way I did this was to remove the foreign key on some tables in each schema and instead adding a check function which then checks if the reference
exists in the current schema or the public schema. All this went well, but now I have a problem with pg_restore,
before I could restore a database in 30 minutes now it takes a few days. Could it be the problem of the function checking row by row,
is there a way to speed up this process (I tried with the -j job option) ?

Here is the function and how it is applied.

CREATE FUNCTION check_features(integer, schemaname text)
RETURNS boolean
AS $$
DECLARE
key     ALIAS FOR  $1;
BEGIN
//check if the record exists in the feature table of the local schema
EXECUTE 'SELECT feature_id FROM ' || schemaname || '.feature WHERE feature_id=' || key';
IF FOUND THEN
RETURN true;
END IF;
//if it does not exist in the local schema check if it exists in the public schema
IF EXISTS (SELECT feature_id FROM public.feature WHERE feature_id=key) THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
$$
LANGUAGE plpgsql;

the way you use the function

ALTER TABLE particular_schema.feature_relationship
DROP CONSTRAINT feature_relationship_subject_id_fkey;

ALTER TABLE particular_schema.feature_relationship
ADD CONSTRAINT feature_relationship_subject_id_check CHECK (check_features(subject_id,'particular_schema'));

thanks for help

Why the foreign key didn't work

I will try to make this as simple as I can.

I have a table(and a copy of this table in each schema) containing for example gene ids, before each schema had separated data and there was no problem.

With development there was a need to have some of the gene ids which were common and needed to be linked to other schemas, so to be able to link them with other schemas I decided to
copy them to public schema, but the remaining ones which are schema specific stayed in their own particular schemas.

This gene id table is also linked to other tables like gene_relationship(gene_id1, gene_id2) etc. and of course these tables had FK constraint pointing to the gene ids table.
So as I had to separate these records into public and particular schema I decided to use the function shown here.

The function checks first within the particular schema to see if the record is there(returns true if found) and after it checks within the public(returns true if found false if not found), it is something like
the conditional foreign key.

Also this is a specific database which should not be changed much because it follows a certain design to be able to link other applications to it.


texts:
I have column with numeric values that I need to replace into text values.

I used the sql statement:

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(loanstatus,
  '1', 'Apple'), '2', 'Banana'), '3', 'Citrus'), '4', 'Deer'), '5',
  'Elephant'), '6', 'Fruits'), '7', 'Grey'), '8', 'Hope'), '9',
  'Island'), '10', 'June'), '11', 'Kate'), '12', 'Live'), '13', 'Moon')

I've tried using Double Quotations (") in place of Singles (').
I've also tried using String('1') and String("1").

Values 1 to 9 works fine.
The problem is if the insert value is "11" then the output is AppleApple
If "12" then "AppleBanana"
when it should come out as "Kate" for 11 and "Live" for 12.


texts: Adding array element to JSON column in PostgreSQL

texts:
We have a table (avl_vehiculo_data_estatica), which has a JSON column (data_vehdatesta) and, we need to update the contents of it by adding an element to actual JSON array that's stored in it:

Actual contents:

[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}]

Desired contents:

[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}, {"iddata":"6","valor":"ACTIVO"}]

So, what has to be done is to add a new JSON array element with the following values:

{"iddata":"6","valor":"ACTIVO"}

We've tried several PostgreSQL JSON related operators and functions, but haven't been able to find the correct mix to achieve such update, so any suggestions might be helpful.

DB Version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

PS: There's a similar question/answer but no exactly the same as our case.


texts: Shared 'directories' using closure tables

texts:
As part of an application I'm working on, users need to be able to store files in directories, and share certain directories for collaboration.

After doing some initial research, I decided to implement a closure table for storing the directory structure (files are stored separately).

Now, I'm looking to implement shared directories, akin to sharing in Dropbox, where each user has their own root-level directory which can't be shared and only children directories can be shared.

I'm wondering how I could go about implementing this?

I used the following repo as a reference for implementing the closure tree.


texts:
MESSAGE TABLE                                       USERS
msg_id | msg_to | msg_from | msg_body | msg_date            user_id  | user_name
  1       1          2         hi      22/1/14                 2          jeff
  2       2          1        hello    22/1/14                 3          john
  3       1          2        howdy    22/1/14                 4          james
  4       2          1       nothing   22/1/14
  5       3          1        yes      22/1/14
  6       1          3         maybe   22/1/14
  7       4          1         no      22/1/14
  8       1          4         cool    22/1/14
  9       4          1         hot      22/1/14

*note that user 1 does not have any data in the users table.

I am having an issue getting all the messages uniquely in a stream based on who it was sent from. That is I want the last message that is exchanged between each user who communicates with user 1. I am trying to populate a list that should should the last message exchanged, so the list would be similar to this

          MESSAGE LISTING
     name      |         last_message
     jeff                   nothing
      john                  maybe
     james                   hot

Last messages exchanged between user 1 and the other users at all times, user 1 does not have data saved in the users table but I have access to his user_id if needed in query.
I tried doing a JOIN with the users table ON msg_from = user_id  but this only returns the last message from one user, so the last message exchanged between user 1 and user 2 is howdy instead of nothing based on this sample. I am not quite sure how to tune this query to represent the correct dataset that is needed, any help would be appreciated thanks.


texts: xmlforest() omits any fields that are null entirely, instead of creating an "empty" element

texts:
I'm know I can use an empty string, but I don't want to lose the null functionality in the database. In some cases null is appropriate and I can't use empty strings.

Other than a empty string, how can I cause xmlforest() to create the empty elements?

tmpint = 0;

    xmlout = '<?xml version="1.0" encoding="UTF-8"?>';
    xmlout = xmlout || '<ListOfFoo FooId="' || input_foo_id  ||'">' || chr(13) || chr(10);

    FOR tmptxt IN
        select xmlforest("foo-orange","foo-apple","foo-banana") from "list-of-foo-meta" where "foo-id" = input_foo_id
    LOOP
        xmlout = xmlout || '<Foo FooNum="' || tmpint || '">' || tmptxt || '</Foo>' || chr(13) || chr(10);
    tmpint = tmpint + 1;
    END LOOP;
    xmlout = xmlout || '</ListOfFoo>' || chr(13) || chr(10);

    return xmlout;

P.S - I am just beginning to experiment with the XML functions available in PostgreSQL. This procedure creates valid XML I can use straight from the query (nice), and there might be better ways. Any suggestions or insight would be appreciated.


texts: SQL Server license from AWS Marketplace with ? cores

texts:
We are planning to buy a SQL Server 2012 Enterprise edition license from AWS Marketplace for our company. We have exhausted our previous licenses and we need to add a new region with SQL Server in cloud. This SQL instance will be in peer-to-peer replication with SQL instances in other regions.
I also found Microsoft certified vendors who sell these licenses. These vendors clearly specify the number of cores for which SQL edition is licensed. In AWS Marketplace though, I don't find the number of cores specified. Does that mean if I use a R5.2X EC2 instance to host this SQL edition, it will be licensed (and billed) for 4 physical cores and if I use it in a R5.4x EC2 instance, it will be licensed(and billed) for 8 physical cores?
If anyone has experience buying SQL Server in AWS Marketplace, please advise.
Thanks in advance!


texts: How do I set the external editor for PostgreSQL's psql client?

texts:
What do I put in ~/.psqlrc file to set the default editor?

The editor I want to use (Textmate) is at /Users/username/bin/mate.

I found the place in the docs where they talk about it, and tried several syntax variations, but can't seem to get it to work.

For example, this didn't work...

\set PSQL_EDITOR "/Users/username/bin/mate"

In psql when I enter \e I get...

sh: /usr/local/bin/mate: No such file or directory
sh: line 0: exec: /usr/local/bin/mate: cannot execute: No such file or directory


texts: I cannot access local database on terminal (MAC)

texts:
I have access to my databases on phpmyadmin and mysql Workbench on my local computer but I have a problem when I try to access the server using Terminal.

Originally, I had uploaded the databases on phpmyadmin. When I utilized Workbench after running new server instance localhost, I had automatic access to my databases. However, when I run mysql command in terminal and show databases, only the default information-schema and test database show up.

Furthermore, when I try to execute the .sql files to import the database on terminal, I get the following error:

  ERROR 1046 (3D000) at line 11: No database selected.

After reading through the documentation, I thought the issue was that I didn't start off the script with USE Database db_name. I then implemented that in the file and then I got this message in terminal:

  ERROR 1044 (42000) at line 7: Access denied for user ''@'localhost' to database 'database'.

If I have access to my databases on phpmyadmin and on Workbench, shouldn't it be the same in terminal?

Also when I first run in terminal mysql -u admin -p < text-file, it asks me for my password. If I simply press enter, then I receive the aforementioned ERROR 1046 or ERROR 1044.

If I put in my password, I then receive the following error:

  ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES).

Can someone please help?


texts: Text string stored in SQLite Integer column?

texts:
I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line:

sqlite> .schema mytable
CREATE TABLE mytable (
    id              integer primary key,   /* 0 */
    mycol           integer not null,      /* 1 */
);

sqlite> SELECT mycol FROM mytable;
here is some text
here is more text
[...]
it's text all the way down

I'm confused. What gives?


texts: does sequence of columns in where condition matters the sequence of composite key? in Oracle Database

texts:
here, In the code where exists clause contains all primary key columns in where, I want to know that the sequence of this columns in it, matters in query performance.

  update temp_fd_dtl_svj a
  set a.LAST_INT_PAID_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                              where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                                and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('I')
                            )
  , a.LAST_PROV_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                              where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                                and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('P')
                            )
   , a.LAST_TDS_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                              where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                                and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('T')
                            )
  where exists (select *
                from temp_fd_int_svj c
               where a.acct_cd = c.acct_CD
                 and a.comp_cd = c.comp_cd
                 and a.BRANCH_CD = c.BRANCH_CD
                 AND a.ACCT_TYPE = c.ACCT_TYPE
                 and a.fd_no = c.FD_NO
               )
  ;


texts: Is it possible to combine 7 sources without a UNION?

texts:
I have a source table that looks essentially like this:

EmployeeCode
WeekStartDate
HoursWorkedDay1
HoursWorkedDay2
HoursWorkedDay3
HoursWorkedDay4
HoursWorkedDay5
HoursWorkedDay6
HoursWorkedDay7

The actual table has something like 500 numbered columns (didn't really count them - there are various and numerous fields numbered 1-7, and then another handful numbered 1-25, times 7) per weekday (no, that's not my design), and there are currently something like 38,600 rows (growing every week).

I have an SSIS package that's trying to normalize this data... that currently looks like this:

Each "source" is selecting one set of numbered columns from the same source table, and the UNION ALL component combines the 7 sources into one, resulting in some 258,900 rows.

The rest of the workflow adds some calculated columns, looks up surrogate keys (e.g. EmployeeCode is used to lookup an EmployeeId, and then the date is computed and used for looking up a TimeId), and then the "modified" rows get updated and the "new" ones get inserted into a normalized table; unchanged rows end up nowhere.

Is there any better way (e.g. a bit less heavy on memory pressure) to normalize the source data?


texts: Temporary tables created on disk: is huge

texts:
I have an issue with a huge number of Temp tables writing on disk. I have tried many things to avoid this but no luck and I really am giving up. I though I might need some expertise to help solve this issue.

My machine is dedicated to four wordpress sites. The server has 8 cores and 64GB RAM.

Every time I run mysqltunner I get this message

  Temporary tables created on disk: 84% (3K on disk / 3K total)

and the number keeps increasing.

From PHPmyadmin I notice this message:

  Issue:
  Too many sorts are causing temporary tables.
  Temporary tables average: 1.24 per minute, this value should be less than 1 per hour.

Here is my configuration

    [mysqld]
    log-error=/var/lib/mysql/server.ict-hardware.com.err
    default-storage-engine=InnoDB
    performance-schema=ON
    skip-name-resolve=1

    # MyISAM #
   key-buffer-size                = 110M
   myisam-recover                 = FORCE,BACKUP
   join_buffer_size               = 4M
   sort_buffer_size               = 4M

   # SAFETY #
   max-allowed-packet             = 16M
   max-connect-errors             = 1000000
   # CACHES AND LIMITS #
   tmp-table-size                 = 512M
   max-heap-table-size            = 512M
   query-cache-type               = 0
   query-cache-size               = 0
   max-connections                = 500
   thread-cache-size              = 100
   open-files-limit               = 65535
   table-definition-cache         = 8096
   table-open-cache               = 8096

   # INNODB #
   innodb-flush-method            = O_DIRECT
   innodb-log-files-in-group      = 2
   innodb-log-file-size           = 3075M
   innodb-flush-log-at-trx-commit = 1
   innodb-file-per-table          = 1
   innodb-buffer-pool-size        = 30G
   innodb_buffer_pool_instances   = 30

   # LOGGING #
   log-error                      = /var/lib/mysql/./mysql-error.log
   log-queries-not-using-indexes  = 1
   slow-query-log                 = 1
   slow-query-log-file            = /var/lib/mysql/./mysql-slow.log
   long_query_time                = 10

   max_allowed_packet=268435456
   open_files_limit=2048

After Several Investigation i found I have two main query that take more than 1 sec

SELECT DISTINCT(wp_posts.post_parent) as ID
FROM wp_posts
INNER JOIN wp_postmeta AS pf1
ON (wp_posts.ID = pf1.post_id)
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE wp_posts.post_type = "product_variation"
AND pf1.meta_key IN ("attribute_pa_women-clothes")
AND pf1.meta_value IN ("s","")
AND ( wp_posts.ID IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN ( 401 ) ) )
AND ( wp_posts.ID IN (
SELECT post_id
FROM wp_postmeta
WHERE meta_key LIKE "attribute_pa_%"
GROUP BY post_id
HAVING COUNT( DISTINCT meta_key ) = 1 ) )
GROUP BY pf1.post_id
HAVING COUNT(DISTINCT pf1.meta_key) = 1
LIMIT 29999

and

SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
FROM wp_posts
WHERE post_type = 'attachment'
ORDER BY post_date DESC

Any help please :)

Thanks


texts: Report Builder cannot publish to report server

texts:
This is currently in a cloud lab environment. We're not allowed to join any AD domains.

We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2.

Our laptop is Win 7 64bit running Report Builder 3.0. We created an OBDC connection to the MySQL database and are able to build reports in Report Builder. However, if we add the connection to the report server (http:// IP_ADDRESS/reportserver) it gives us the following error when trying to publish:

  For more information about this error navigate to the report server on
  the local server machine, or enable remote errors.

  Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)

  An error has occurred during report processing. (rsProcessingAborted)

I am able to open SQL Management Studio locally on my laptop and connect to the remote SQL server without issue.


texts:
I have created a SQL Server Job on SQL 2016. This is a job that runs a full backup from our backup utility DDBoost. When I execute the job, the backup completes in 5 mins for all databases. However, the job keeps running forever. Any ideas?

USE [master]
GO
DECLARE @returnCode int
EXEC   @returnCode = dbo.emc_run_backup
' -c <FullyQualifiedName> -k -S 4 -l full -y +90d
-a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE"
-a "NSR_DFA_SI_DD_HOST=adc-dd-02" -a "NSR_DFA_SI_DD_USER=ddboost"
-a "NSR_DFA_SI_DEVICE_PATH=/sql-prod"
-a "NSR_DFA_SI_DD_LOCKBOX_PATH=D:\Program Files\EMC DD Boost Modules\DDBMA\config"
-a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE"
-a "SKIP_BACKUP_PROMOTION=FALSE" "MSSQL$PRSQL01:"'

IF @returnCode <> 0
BEGIN
    RAISERROR ('Fail!', 16, 1)
END


texts:
I got issues with some geo IP query. So basically here is create code of table:

CREATE TABLE `geo_ip_city` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `begin` BIGINT(20) NOT NULL DEFAULT '0',
    `end` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(2) NOT NULL DEFAULT '',
    `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `begin` (`begin`),
    INDEX `end` (`end`),
    INDEX `code` (`code`)
)

And query:

SELECT * FROM `geo_ip_city` USE INDEX ( `end` ) WHERE `begin` <= 2523596988 AND `end` >= 2523596988 LIMIT 1

Table size is about 4.5m records.

This query runs when all other criteria doesn't work, but still on peak times quite often.

In worst case scenario (where nothing found) it executes for 1.9444 seconds.

Here is explain:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  geo_ip_city range   end end 8   NULL    2297506 Using index condition; Using where

So when i have alot of requests for this feature, my servers going crazy.

Question:

Can I do something with this table or query to increase performance (maybe partitioning or complex keys)? Or should I watch in some other way?


texts:
We have a machine that crashed completely.
The ASM disks are fine. Is it possible to mount the old ASM disks to a new machine and then restore the datafiles/redo files/ control files/everything to the new database?

Please note I don't want to restore from backup. I need to restore everything on ASM disks to new hardware with fresh OS and fresh database installation.
Is that possible? if yes, how?

We are running Oracle 11gR1 database on Redhat Linux.


texts: How do I locate my Postgres data directory while the server is down?

texts:
I'm on Mac 10.13.6.  I restarted my machine and Postgres, which normally starts upon restart, wasn't running.  Sadly, I cannot remember how or where I installed it.  I'm able to locate instances of "pg_ctl"

sudo find / -name "pg_ctl"

returns

/usr/local/bin/pg_ctl
/usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl
/usr/local/Cellar/postgresql/9.5.0/bin/pg_ctl
/usr/local/Cellar/postgresql/9.6.15/bin/pg_ctl
/usr/local/Cellar/postgresql@9.6/9.6.15/bin/pg_ctl
/usr/local/Cellar/libpq/11.5/bin/pg_ctl
/usr/local/Cellar/postgresql@9.5/9.5.19/bin/pg_ctl

However, I need to tell it a data directory in order for it to start, and I can't figure out how to know the data directory while the server is down.  What kind of files should I look for to figure out where the data directory is?


texts: PostgreSQL 11 error: column p.proisagg does not exist

texts:
Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error:

ERROR:  column p.proisagg does not exist
LINE 18:    WHERE NOT p.proisagg
                      ^
HINT:  Perhaps you meant to reference the column "p.prolang".
Dans l'instruction :

            SELECT
                p.oid AS prooid,
                p.proname,
                p.proretset,
                pg_catalog.format_type(p.prorettype, NULL) AS proresult,
                pg_catalog.oidvectortypes(p.proargtypes) AS proarguments,
                pl.lanname AS prolanguage,
                pg_catalog.obj_description(p.oid, 'pg_proc') AS procomment,
                p.proname || ' (' || pg_catalog.oidvectortypes(p.proargtypes) || ')' AS proproto,
                CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) AS proreturns,
                u.usename AS proowner
            FROM pg_catalog.pg_proc p
                INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                INNER JOIN pg_catalog.pg_language pl ON pl.oid = p.prolang
                LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
            WHERE NOT p.proisagg
                AND n.nspname = 'public'
            ORDER BY p.proname, proresult

Fatal error: Call to a member function recordCount() on integer in /mnt/webdata/websites/applications/pga/classes/Misc.php on line 1949

I get the same error using the \df meta-command in psql (version 10.1 as it turns out):

ts_d=> \df
ERROR:  column p.proisagg does not exist
LIGNE 6 :   WHEN p.proisagg THEN 'agg'
                 ^
ASTUCE : Perhaps you meant to reference the column "p.prolang".
ts_d=>

The error in those queries seems to be due to a reference to proisagg, a column originally from the pg_proc table in the pg_catalog schema, but which no longer exists in PostgreSQL 11.
See: https://www.postgresql.org/docs/11/release-11.html

Any way to resolve this?


texts: Update MySQL slave when changing the hostname/IP of master

texts:
I have one master and one slave replicating great, but the master is about to have both it's hostname and IP changed. In master.info on the slave server I see the current hostname for the master, if I shutdown the slave server, change the hostname in that file and then restart MySQL will that be all I need? No usernames/passwords etc are changing.

If I used CHANGE MASTER queries instead it looks like it will require re-setting the binlog position etc?


texts: mysql unable to start with semi sync parameter

texts:
Im using mysql 5.7 and it has 2 slaves with GTID. I tried to enable semi-sync and I have done with set global variable in mysql prompt. It was running fine. Then I did a restart, after that mysql unable to start and it says unknown variable 'rpl-semi-sync-master-enabled=1'

mysql log

2018-11-03T08:28:35.453089Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(2), pos(, 4)
2018-11-03T08:28:35.453119Z 5 [Note] Start asynchronous binlog_dump to slave (server_id: 2), pos(, 4)
2018-11-03T08:28:35.793103Z 6 [Note] Start binlog_dump to master_thread_id(6) slave_server(3), pos(, 4)
2018-11-03T08:28:35.793141Z 6 [Note] Start semi-sync binlog_dump to slave (server_id: 3), pos(, 4)
2018-11-03T08:29:25.656749Z 0 [Note] Giving 5 client threads a chance to die gracefully
2018-11-03T08:29:25.656793Z 0 [Note] Shutting down slave threads
2018-11-03T08:29:33.658167Z 5 [Note] Stop asynchronous binlog_dump to slave (server_id: 2)
2018-11-03T08:29:33.658360Z 6 [Note] Stop semi-sync binlog_dump to slave (server_id: 3)
2018-11-03T08:29:35.658275Z 0 [Note] Forcefully disconnecting 0 remaining clients
2018-11-03T08:29:35.658368Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2018-11-03T08:29:35.658532Z 0 [Note] Binlog end
2018-11-03T08:29:35.662977Z 0 [Note] Shutting down plugin 'rpl_semi_sync_slave'
2018-11-03T08:29:35.663025Z 0 [Note] Shutting down plugin 'rpl_semi_sync_master'
2018-11-03T08:29:35.663059Z 0 [Note] unregister_replicator OK
2018-11-03T08:29:35.663062Z 0 [Note] Shutting down plugin 'auth_socket'

syslog:

Nov  3 04:11:17   mysql-systemd-start[9868]: ERROR: Unable to start MySQL server:
Nov  3 04:11:17 mysql-systemd-start[9868]: 2018-11-03T04:11:17.961175Z 0 [ERROR] unknown variable 'rpl-semi-sync-master-enabled=1'
Nov  3 04:11:17   mysql-systemd-start[9868]: 2018-11-03T04:11:17.964899Z 0 [ERROR] Aborting

Update:

I used the lines then it worked. But what is the use of these parameters?

loose-rpl_semi_sync_master_enabled = 1
loose-rpl_semi_sync_slave_enabled = 1


texts: How to display values excluding maximum value based on column that is duplicated

texts:
I am facing a challenging issue where I need to print all fields from a specfic column based on a duplicate column that excludes the maximum value

My query plan:

select event_no,
       bs_id
from   bs_bank_mast
where  event_no in (select   event_no
                    from     bs_bank_mast
                    group by event_no
                    having   count(*) > 1)
order by event_no ASC

My output:

event_no       bs_id

1692163        40672
1692163        41974
1692163        40672
1692163        41974
1723264        67460
1723264        67499
1723264        68357
1723265        67929
1723265        67383
1723266        67735
1723266        67423
1723266        67969
1723266        68164
1723266        67501

event_no       bs_id

1692163        40672
1723264        67460
1723264        67499
1723265        67929
1723266        67735
1723266        67423
1723266        67969
1723266        68164

So basically everything but the last max value from bs_id is printed

The query I tried:

select event_no,
       bs_id
from   bs_bank_mast
where  event_no in (select   event_no
                    from     bs_bank_mast
                    group by event_no
                    having   count(*) > 1)
and    event_no < max(bs_id)

The error I get is:

  Bad use of aggregate in this context

Please will you provide the assistance for what I need.


texts: Creating a Windows database backup service

texts:
I am creating a Windows service in C# that will run on our system to run a backup between 3am and 5am on our databases.

I have started by creating a method that will basically run the backup when SQL statements are passed into a backup method as below.

 private void BackupData()
        {
            try
            {
 RunBackupSQL("INSERT IGNORE INTO new table_backup SELECT * FROM current table",
"Backup current table data");

However this is doing it 1 table at a time. I need to do this for 5 databases with over 56 different tables.

I was wondering if there is a way in which I can create a SQL command to take all tables in each database and do the backup so there would only be 5 SQL statements instead of 56?

The company I work for wants it done in this way based on the fact it was so difficult with their current backup system as it encrypted everything and it was difficult to extract the data again. I need to create a backup onto a different database completely so in effect snapshots of the current.

It needs to run once a day between 3am and 5am so in effect any changes made outside of these time frames will be picked up when the timer does start and it will overwrite the other database. I have 5 databases within the server I need to back up, so really there would be 5 databases I need to copy to. So if anything does go wrong with the current databases we have an exact replica that we can bring back across. It will create new dbs and then every time the current ones are changed it would then run the timer which will send across the changes to the new dbs and constantly do that.

It is MySQL. FYI I'm an apprentice at the moment, so it could be they want me to do it as a project form in C# using Visual Studio to get me into creating Windows services.


texts: SSRS Azure SQL Database Active Directory - Universal with MFA

texts:
Does anyone know when support for Active Directory - Universal with MFA will be included in Reporting Services?

It's available in SSMS.

Visual Studio 2017 Data Source Properties

Reporting Server Data Source Properties


texts: Oracle 12c: sql query return less result when it's used in a subquery

texts:
I recently encountered a weird situation where i get different result from the same query by only changing its scope.

What i mean by changing the scope , is putting the query inside a subquery:

I have this query:

select * from my_function

It returns the correct result, but when it's used as a subquery it gives less result :

select * from (select * from my_function)

Please note that there is no filters nor limits on rows.

my_function is a function that return a dataset like this :

return anydataset pipelined using myType;

I'm wondering if there is some (hidden) parameter in oracle 12c that might limits the result of a query when it's used in a view or a sub-query , especially if this query is using a function as source.

Thanks for your help :)


texts: How force specific table to not logging (for all DML)?

texts:
We using Oracle 9i .
I'm still learn about admin Oracle (I'm DBA but other vendor).

We implement manually at our databases a trigger over LOGON and LOGOFF to save some information about our users sessions.

We execute an insert at LOGON and an update at LOGOFF over our table.
This table was created as NOLOGGING.

Our problem is the amount of archive generated by day, growth a lot.
After check with log miner I detect is the updates over this table the reason.

I research little and found the information the NOLOGGING work only for very specific situations and all UPDATEs still logging.

I don't need keep safe this table or backup it. We extract the information all days... if I loose some days I can live with it. But I can't keep this overhead .

Is there no way to force a table to not logging for all DMLs in Oracle?
Any alternative solution? (where I keep the triggers and my own table)

Regards
Cesar


texts: Formatting Timestamp in Default Constraint

texts:
Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x?

Example Code:

create_date TIMESTAMP WITH TIME ZONE
    DEFAULT to_char(current_timestamp, 'DD-MON-YYYY HH24:MI:SS')
    NOT NULL

When running this, I get the following error:

  ERROR:  column "create_date" is of type timestamp with time zone but default expression is of type text
  HINT:  You will need to rewrite or cast the expression.
  SQL state: 42804

However, according to the Postgres Docs this type of formatting should be possible.

I've also tried casting this with the to_timestamp() method, however, I get the following error:

  ERROR:  function to_timestamp(timestamp with time zone, unknown) does not exist
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  SQL state: 42883


texts: Can I setup database triggers to run on a defined secondary set of servers only?

texts:
This might sound a bit off, but here's what I have been thinking for a while now:
Use Case
You want to build an activity log for each user action on your application using database (postgreSQL) triggers on every relevant table, outputting to an activity_log table.
The triggers should do the trick, but how do we eliminate the burden of every user action triggering an action on the production servers, delaying the whole application?
Purposed Architecture
What I have in mind is a complex structure where one or more secondary postgres nodes would take the entire activity_log trigger activity.
The triggers would be disabled on all primary nodes (the ones the application reads and writes to) and enabled on some/all secondary nodes (let's call them "workers").
Data would be written to a primary server (no trigger runs) and replication would forward it to all other nodes. When a "worker" node get's the data, the triggers process it and update the activity_log.
activity_log table should be replicated across ALL servers which means that a "worker" node should be able to read, write and send selected data upstream.
Is there anything even close to this?
Is this even possible to achieve without having to rewrite a replication model from scratch?


texts: How do I connect to a database which is a linked server via ODBC?

texts:
(All the searches for this sort of thing tell me about where the linked server is connecting to the original database via ODBC, but I'm connecting to the intermediate server via ODBC.)

I have a Python script which I want to connect to an Oracle database. In between the two I have a server running Server 2003 and SQLServer 2008. I have created a linked database on this intermediate server which successfully connects to the Oracle server. (Using Management Studio I can run a query on the remote Oracle server successfully.)

When I set up the machine that the Python script is running on, (happens to be another Server 2003, but I'll be running it on XP and Win7 machines too), I try to create an OBDC data source to the intermediate server. When the list of databases on the server comes up it doesn't list the linked server databases, only the normal databases on that intermediate server.

Is it possible to get ODBC to look at a linked server database? If so, how?


texts: Galera cluster: change the size of InnoDB Redo log files online?

texts:
We have a 3 node Galera cluster. I need to change the mysqld innodb_log_file_size parameter (see Changing the Number or Size of InnoDB Redo Log Files).

I do this by stopping all 3 nodes and disabling Puppet. Can I do this change online with Galera? How?

> select VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)


texts: Dropped database still shows but causes errors PostgreSQL

texts:
I had dropped a database a couple weeks ago but it still shows with \l.  I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was still showing up with \l.  Now when I try that command again I get:
template1=# \l

template1=# drop database mixmobi_master;
ERROR:  database "mixmobi_master" does not exist

template1=# \l
                                 List of databases
      Name           | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------
biterator_development   | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
mixmobi_master          | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
plantjotter_development | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres                | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0               | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/atmos         +
                        |       |          |             |             | atmos=CTc/atmos
template1               | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/atmos         +
                        |       |          |             |             | atmos=CTc/atmos
(6 rows)

Every couple of minutes or so it will also give me this error message:
FATAL:  database "mixmobi_master" does not exist
DETAIL:  It seems to have just been dropped or renamed.

Anyone have suggestions?  I'm running Lion and had used Atmos' Cinderella environment.

Edit

I used @Erwin's suggestion and got this:
template1=# SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

datid |    datname     | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts |          stats_reset
-------+----------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+-------------------------------
16387 | mixmobi_master |           0 |           9 |          2118 |       262 |     1826 |         2181 |        1157 |            0 |           0 |           0 |         0 | 2012-01-24 11:47:34.692079-06
(1 row)

template1=# SELECT pg_terminate_backend(procpid)
template1-# FROM   pg_stat_activity
template1-# WHERE  datname = 'mixmobi_master';
pg_terminate_backend
----------------------
(0 rows)

Does anyone else have any suggestions?  I'm still getting these errors and nothing has worked so far!  Thanks in advance for your time!


texts:
Both MyRocks (MySql) and Cassandra uses LSM architecture to store their data. So I have populated around 5 million rows in MySql with MyRocks as storage engine and also in Cassandra.
In Cassandra it takes only 1.7 GB of disk space while in MySql with MyRocks as storage engine, takes 19 GB.

Am I missing something? Both uses same LSM mechanism. But why they differ in data size? Can someone please explain?

And an update to the above question. I guess it has something to do with the text column.

Tried with another Table Structure : (bigint,bigint,varchar,text).
No Of rows Populated : 300 000
Now in MyRocks the data size is 185MB
In Cassandra - 13 MB.

But if I remove the text column then,

MyRocks - 21.6 MB
Cassandra - 11 MB

Any idea about this behaviour?


texts: Will creating partitions reduce locking and how do we implement this in sql-server?

texts:
A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.

This application generates quite a lot of data that is used for reporting. Before each run the old data for that item is deleted, the calculations are performed and then the new data inserted. In busy periods the users queue up hundreds of these generation tasks and we run upto 30 of them concurrently. Each run might create 60K rows.

The dba has suggested we could change the application to use 30 partitions (eg. one per thread) to reduce locking between threads during insert and delete. They suggested that in standard sql we could do something like

INSERT INTO schema.table.partition (...) VALUES (...)

I do not see this syntax in the msdn docs and this will mean changing this application which is a pain but is it even possible to do this? As I understand we would instead partition based on columns of the tables using partition functions?

I've read the create partition function docs but am not completely sure how to create a function to meet our needs. To make matters worse I don't yet have enterprise edition to try this out on so my apologies for incorrect syntax.

I am thinking that for example if we have an items table and an itemdata table with data for that item we might partition itemdata table by splitting the data based on a function like itemid mod 30. This would put item 1 in partition 1, item 2 in partition 2, etc. I'm not sure if we could do this in the partition function, in the scheme, table declaration or would we need to create a calculated column and use a values clause? Also not sure if we are going to see any performance improvement?

This is how I think we could implement this:

CREATE PARTITION FUNCTION SplittingItemIds_PFunc(decimal(18,0)) AS
RANGE LEFT FOR VALUES
(0,1,2,3, ... ,29)

CREATE PARTITION SCHEME SplittingItemIds_Scheme
AS PARTITION SplittingItemIds_PFunc
ALL TO ([PRIMARY]);

CREATE TABLE ItemData
(
    Id decimal(18,0),
    ItemId decimal(18,0),
    ...
)
ON PartitionSplittingItemIds_Scheme(ItemId % 30)

CREATE INDEX ItemData_ItemId_Idx ON ItemData(ItemId);

Is this kindof right?
From what I've read the index will be automatically partitioned - is that correct?


texts: Writing MongoDB logs to a remote logging server

texts:
I am trying to forward MongoDB logs from several remote servers to a central logging server. To do this, I changed my mongod.conf files to use syslog like so:

systemLog:
  destination: syslog
  syslogFacility: local3

I then configured rsyslog to forward messages like so:

if($syslogFacility-text == "local3") then{
  if($syslogTag contains "mongod") then{
    action(type="omfwd" target="MY.SYSLOG.SERVER.IP" port="PORT" protocol="udp")
    stop
  }
}

My logging server is configured to write any messages sent at that port to my central mongod.log file.

My question is; is there a better way to do this? For example, could I specify my logging server's IP/PORT in mongod.conf so I don't have to rely on each Mongo server's local rsyslog?


texts: Benchmarking procedures or Upgrade tests to be done after upgrading to SQL Server 2016 from SQL Server 2012

texts:
I need suggestions on how to benchmark performance or create baselines to test the upgrade from SQL Server 2012 to SQL Server 2016 (both standard edition), also if any post upgrade tasks that needs to be checked to ensure smooth performance after upgradation.

P.S : I have recently created a test snapshot of my 2012 SQL Server database(also have separate snapshot of 2012 SQL Server as well) and have upgraded it to 2016 SQL Server (both standard) in AWS RDS. I want to test the performance and functionality of both the instances to check if the upgrade went smooth and  I can proceed it for our main production Database.

Please help me on all the checks to be performed.

I have done replaying a trace file from the current server on the new server.
Below are the results:


texts: Log Shipping restore job failed, DBNAME is not in NORECOVERY/STANDBY mode

texts:
My log shipping job stopped suddenly. I'm getting this error:

2020-04-17 14:46:36.42  ----- START OF TRANSACTION LOG RESTORE   -----
2020-04-17 14:46:36.53  Starting transaction log restore. Secondary ID: '02d5e9cd-bd01-42d6-9a8b-9c79c2b13d01'
2020-04-17 14:46:36.55  Retrieving restore settings. Secondary ID: '02d5e9cd-bd01-42d6-9a8b-9c79c2b13d01'
2020-04-17 14:46:36.56  Retrieved common restore settings. Primary Server: 'SERVERNAME', Primary Database: 'DBNAME', Backup Destination Directory: 'D:\LOGShipping\LSSecondary', File Retention Period: 14400 minute(s)
2020-04-17 14:46:36.56  Retrieved database restore settings. Secondary Database: 'DBNAME', Restore Delay: 0, Restore All: True, Restore Mode: Standby, Disconnect Users: True, Last Restored File: D:\LOGShipping\LSSecondary\DBNAME_20200413181001.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
2020-04-17 14:46:36.64  *** Error: The restore operation cannot proceed because the secondary database 'DBNAME' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) ***
2020-04-17 14:46:36.65  Deleting old log backup files. Primary Database: 'DBNAME'
2020-04-17 14:46:36.69  The restore operation completed with errors. Secondary ID: '02d5e9cd-bd01-42d6-9a8b-9c79c2b13d01'
2020-04-17 14:46:36.76  ----- END OF TRANSACTION LOG RESTORE     -----

Exit Status: 1 (Error)

How can I solve this problem?


texts: To select from all tables inside the schema in PostgreSQL

texts:
Is it possible to select from all tables inside the schema?
I got all the table names from

select table_name from information_schema.tables

but I am unable to use it to do my query.


texts: Truncate temporary table commits transaction?

texts:
Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction?

Does this mean that the only way to have a fresh temporary table without committing the current transaction is to run (after above DDL statement):

DELETE FROM temp-table-name;

This TRUNCATE auto-commit behavior caused a rather nasty bug in my app -- the rollback didn't go all the way up because of the "TRUNCATE barrier" ;)


texts:
I have a column that has strings of the following type:
Miller 10
Allen 20
King 10
....
Jones 100

I try to sort the column based on the numerical part of the data.
I tried the following:

SELECT * FROM the_table ORDER BY CAST(RIGHT(data,2) AS UNSIGNED);

But this does not work. It places Jones 100 as the first entry etc.

What am I doing wrong?


texts: Incomplete installation with unsupported locale

texts:
I was installing postgresql on Ubuntu 12.04 (Digital Ocean droplet). The locale variables in my environment gets transported to the DO box.

When postgres is installed via apt-get install postgresql, it appears to succeed. However no default cluster is created, and postgresql is not added as a service. This happens silently. I realised what was going on when attempting to run pg_createcluster manually failed.

Some questions arising from this:

how can I be alerted that the install hooks aren't being run?
after fixing the locale, I removed (and purged) the postgresql package; however the default cluster still wasn't created. What was preventing this? How can I ensure that install hooks are being run?


texts: Is order by necessary after a group by clause?

texts:
Suppose I have a simple query

SELECT col1, col2, COUNT(col3)
FROM tbl_name
WHERE condition
GROUP BY col1, col2
ORDER BY col1 ;

My understanding is group by clause seems to already have a sorting functionality. DB engine would sort col1 then col2 if adjacent rows of col1 happen to have same values. As least that is my experience with sql server.

If that is the case, order by clause here isn't really necessary?


texts: Does MySQL or MariaDB support a LineString constructor that takes points?

texts:
It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2)

> SELECT ST_MakeLine(Point(0,0), Point(0,1));
ERROR 1305 (42000): FUNCTION ST_MakeLine does not exist

> SELECT ST_LineString(Point(0,0), Point(0,1));
ERROR 1305 (42000): FUNCTION ST_LineString does not exist

Is the only way to construct a LINESTRING to use ST_GeomFromText or ST_LineFromText, or ST_LineStringFromText?


texts:
I want to keep track of different activities/actions made by members of a website. For example "post created", "successful login", "file uploaded" etc.

Is it OK to just have a table named for example "actions" and save timestamps , user ID etc and the primary key for the actions such as post_id (table Posts), file_id (table Files)

Is this a valid way of doing it, or is there a better way?


texts:
We are in the process of migrating all our data from Oracle DB (12c Enterprise Edition Release 12.1.0.2.0) to Postgres (9.4.9). We are half way through the migration process and it is going to take a while to migrate all the tables. A SQL I have requires JOINs and sub-queries on tables across these two different DBs.

The obvious solution is to write a script that runs queries separately on these databases and combines the results in code.

Is there an easier alternative to this?


texts: mysql function for 2 rows (same name) with group by

texts:
I have my table:

id   name   salarry   month   year
1    mike   1000      1       2017
2    john   1200      1       2017
3    mike   1100      2       2017
4    mike   900       1       2017

my results now is:

id   name   sallary   month   year
2    john   1200      1       2017
1    mike   1100      1       2017

I want to show the sallary of mike separatelly by month:

id   name   sallary   month   year
1    mike   1000      1       2017
2    mike   1100      2       2017
3    john   1200      1       2017

the mysql code:

SELECT  `id` ,  `name` , MAX( sallary ) ,  `month` ,  `year`
FROM  `test`
GROUP BY  `name`
LIMIT 0 , 30


texts: replication filtering replicate-rewrite and do-table Mysql 5.6 (innodb engine )

texts:
I have a master/slave replication setup based on statement based replication.
On master database named DB1, I have 5 tables and I want to replicate only two table on slave's database named DB2.

Also, I want to change the database name on my slave end.

So my configuration file on Slave end look like this and replication filtering is this:

   replicate-rewrite-db="DB1->DB2"
   replicate-do-table=DB2.table4
   replicate-do-table=DB2.table5

Also I have tried below filtering as well:

   replicate-rewrite-db="DB1->DB2"
   replicate-do-table=DB1.table4
   replicate-do-table=DB1.table5

But in both case records not getting replicated , also no error is reporting.

As per MySQL documentation , replicate-rewrite-db doesn't work with replicate-do-table.

Any method to resolve this? Or is this not possible?

I'm using MySQL 5.6 community edition on Linux server.

I haven't made any changes to master end as it is on customer side.


texts:
I have a UPDATE statement that is using a UPDLOCK inside a DTCTransaction to avoid deadlocks.

Now I was wondering. is it locking on a Row-level or on a Table-level?


texts: Linked Server Issues SQL Server to Oracle

texts:
I am trying to set up a linked server from SQL Server Production to one of our Oracle instances but experiencing this error :
The test connection in ODBC succeeds, but it fails on SQL Server side. I have checked the following things:

OLEDB provider is installed
OLEDB bit matches the bit of SQL
OLEDB is registered
Linked server properties is the last option to be able to login in using username and password
Oracle provider has Allow In process checked
Oracle ports are opened
The right version client is installed

Are there anything else I should check further to resolve this error? Please provide me suggestions if you have experienced this before and what has worked for you. TIA


texts: How does the transaction log work in SQL Server when i run a DML

texts:
I would like to ask you something about a query execution in SQL. in general, I know that when I run a SELECT query I mean a view, this is loaded in cache memory then is parsed, compiled and then the query optimizer applies improvements based on execution plans so for that we have also the support coming from buffer pool (storage engine and relation engine). So, all that happens in memory and finally the result set is sent to SQL client.

Otherwise, we have the log file in a DB and here is my doubt because I don’t know if the log file in SQL is also used in queries ‘SELECT’ executions. Or it’s just used in transactions like an update, delete or insert so I really would like to know the role of transactional log in SQL when I perform a query (view) and also his role in the other DML transactions.


texts: Execute Remote Powershell script from SQL Agent

texts:
A test SQL Server job hangs when it executes a Powershell script.  It does work using my login and a Powershell window, but not using the Agent account and a job.  The SQL Agent user has read/write/execute permissions to the directory with the powershell script.

Example:

Run as: SQL Server Agent Service Account

powershell.exe -file \\Server02\test2\Test.ps1

The agent account has been given access also.

This test file currently does something very basic: displays a string.

When it runs, it does not show an error message, because it just does not respond. Do you have any idea about what is happening, or which security settings are missing?


texts: High CPU for SQL server running on VmWare

texts:
I am trying to understand the below behavior for one of our SQL server running on VM.
SQL Server version 2017 has 64vCores and Host has 96 cores with base speed 2.1 GHZ
Based on the article from https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware i collected the counters and can see below"-
Processor% Processor Time in Task Manager or perfmon: Toggles around 80 in peak business hours
% Processor Time for VM ware is always pegging around 100%
Effective VM speed MhZ averages around 140,305 so does not look to be limited anywhere. ( Minimum 8219 and maximum 157,612)
Host processor speed is 2091
Is there a way to find what does that 100% coming from? Or is that OK to be 100 % for that counter where SQL seems to be between 75-80%?
Additional info:-
Avg Batch re/sec on high time = 30K
Blocking/deadlocks= none
Queries are generally fast like 100K executions/min and avg CPU they spend is few ms.
Mostly all the above queries are single threaded due to various reasons.
CTOP= 200 and MAXDOP =8
Thanks


texts:
I have a query that pulls in a delivered count of a campaign. Each run is a new row in the runs table. To retrieve a campaign I need to join the related table in.

SELECT c.Company, r.RunId, r.DeliveredCount FROM Run r
INNER JOIN Campaign c ON r.CampaignId = c.Id
WHERE r.RunId = 3001626 AND c.Company LIKE '%74108%'

Why does it return more than one row, even when I specify the Run Id I would like to pull?


texts:
I have an application for doctors . pateints should can set appoint time .
I designed this :

an example :

date : 2016/22/8 has some times like : 09:00 , 09:45 , 14:30 users can selec on of them

is this design good ?


texts: How do I solve the following error on attaching ldf-less database in SQL Server?

texts:
Before you write about the dangers of not having backups, etc., please withhold judgement... perhaps I just inherited the problem?

I have an .mdf which which I am trying to attach to SQL Server 8.
I've tried a number of methods.

For example:

CREATE DATABASE [foo] ON ( FILENAME = N'D:\Microsoft SQL Server\YourDataPath\Data\foo.mdf' )
FOR ATTACH_REBUILD_LOG
GO

Gives me the following error:
"Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'ATTACH REBUILD LOG'"

If I run the query with only ATTACH, it errors out because of the missing log file.

Furthermore, I have tried to attach it through the Management Console.

I'm reasonably competent (usually) and as I wrote, I inherited this problem.

The database was completely hosed, I ran the emergency procedures, the CBCC CHECKDB commands, etc, and it appeared to be working (zero errors), however only in Read Only.  I thought it might have been a problem with permission on the log file, and I read that you could detach it and reattach to start a new log file, and when I detached it - I found that I can't reattach it.

My head hurts.  Any help will result in serious karma points. Halp!??!

Update, I did a pretty hack-y thing: I renamed the mdf, created a database with the same name, deleted the new mdf and ldf and then started it back up.  It was found in 'suspect' mode.

So I did this:

USE [master]
GO

EXECUTE sp_configure 'allow updates',1
RECONFIGURE with OVERRIDE
GO

UPDATE sysdatabases
SET status = 32768
WHERE name = 'foo'
GO

Then this:

USE [master]
DBCC CHECKDB('foo')
GO

Finally this:

USE [master]
GO

ALTER DATABASE [dbname] SET MULTI_USER
GO

UPDATE sysdatabases
SET status = 0
WHERE name = 'foo'
GO

EXECUTE sp_configure 'allow updates',0
RECONFIGURE with OVERRIDE
GO

It appears to be back online, I can run SELECT statements and the database appears to be reconnected, however there is no ldf file and if I try to do an INSERT or an UPDATE the server bonks.

I tried to create a table and it says:

'STUFF' table
- Unable to create table.
Invalid transaction state: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not run BEGIN TRANSACTION in database 'foo' because the database is in bypass recovery mode.

I ran:

ALTER DATABASE foo SET MULTI_USER WITH ROLLBACK IMMEDIATE

Still no dice.


texts:
There has been a security audit for many of our sql servers and per script there are many findings.

One of them says "SA Account has not been disabled"

I know it can be disabled, but can it be renamed.

Please confirm what should i go with "Remove, rename or disable the SA account" and whats he best practise?


texts: Galera throws “WSREP has not yet prepared …” error but nodes are working

texts:
Yesterday we moved from 2 node Galera to 4 node Galera with a brand new infrastructure. But after we move the database, Galera started throwing "WSREP has not yet prepared node for application use" error. However all 4 nodes are operational.

New symptom: This error comes from only one table :/

Setup;

Galera is working with 4 nodes
All 4 nodes are in the same local network
There is no network connectivity issue
There is no disk issue (or IO)

We tried these but;

Increasing CPU did not help
Increasing RAM did not help
Changing config for auto_increment_increment to 4 did not help
Changing config query_cache_size=0 did not help
Database fix didnt work

Symthoms;

New disks are all SSD, so I dont think there is a slow IO issue
Constantly monitoring them but WSREP is always in sync
Not always throwing errors
Cannot insert to a specific table and it gives errors with that table most of the time


texts: RBS and Fail Over Cluster with SQL Server 2012

texts:
I am trying to install one SQL Server 2012 cluster with two nodes. I will also be enabling Remote Blob Storage (RBS) on server so I was wondering if I need to take something in account while installing cluster?
Or I can install cluster like we normally do and then later on enable RBS like we normally do?  I mean does RBS require some special configuration/setting in SQL Fail Over Cluster?


texts:
SELECT ROUND(4.524965325714286, 2);

I'd have thought that should be 4.53, because

6 rounds 8 to 9
9 rounds 2 to 3
3 leaves 4 as 4
..
so. it ends up lastly doing 4.525 rounds to 4.53

It looks like it's doing

I want 4.524965325714286 as 2 decimal places
Q: What's the third decimal? A: 4
4 doesn't round up
Return 4.52

Is that a correct representation of what happens?

I'm using MySQL version 5.7.20


texts: Will dropping text column on SQL Server 2014 lock the table?

texts:
I have been tasked to drop a text column from a production SQL Server 2014 table.

The table contains 61,683,821 records.

Question: will dropping the column lock the table (or cause similar disruptions)?

Bonus question: one procedure references the column (the column does not allow nulls - so, on insert we are using an empty string). Can I wrap the ALTER TABLE and ALTER PROCEDURE in a single transaction without issues? (that is, alter the table and remove the reference from the procedure).

Thanks...  I'm the developer, not the dba... why they want me to do this, I don't know :(

Update  I can answer the first question.  The DROP was instant and caused no problems.  I chose not to use a transaction.


texts: SQL Server - backup checksum default vs VSS

texts: A (hopefully) quick question - does enabling backup checksum default option affect VSS backups in any way?


texts: Troubleshooting TempDB for Performance Bottlenecks

texts:
While trying to investigate some performance issues with our SQL Server database, I've noticed our wait statistics seem high for ASYNC_IO_COMPLETION and PAGEIOLATCH_*. In Resource Monitor I noticed that tempDB is frequently (at least 1 time per hour) Reading/Writing large amounts of data, Total (B/Sec) range from 5GB - 25GB.

The tempDB is on a separate Virtual Disk than the Database and Log Files. TempDB size is 4 GB. Our main DB and Log file is 40GB in size each. I've tried researching perfmon counters for this but haven't been able to find anything I can make sense of in order to figure out why I'm getting certain numbers.

Is it unusual that the total data for tempDB is so high through resource monitor? Our main Database is only growing about 50MB per day. What should I be looking for to figure out what is causing such high data on tempDB and if this is causing an I/O Bottleneck? If the best method is by using specific perfmon counters, which counters should I be looking at and what values are good or bad?


texts:
I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple database structure consisting of two tables.

Table users:

PK_User, uniqueidentifier
ID, bigint
Username, nvarchar(128)
CreationTimestamp, datetime

Table data:

PK_Data, uniqueidentifier
FK_User, uniqueidentifier
FK_Reporter, uniqueidentifier
CreationTimestamp, datetime

I am currently working with the following SQL Statement:

SELECT u.Username, COUNT(d.FK_User) AS 'Count', CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime) AS 'Date'
FROM data d INNER JOIN users u ON u.PK_User = d.FK_User
GROUP BY CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime), u.Username
ORDER BY CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime)

which delivers something like this:

User1   5   %Date1%
User2   3   %Date1%
User1   7   %Date2%
User2   1   %Date2%

So I am able to get the sum for each user for each specific day. I would like to sum this sum up, so that User1 gets for the second day 5+7=12 and User2 3+1=4.

How can I achieve this?


texts: Connect to SQL Server 2008 R2 from Windows 98

texts:
Is it possible to connect to SQL Server 2008 R2 from a Windows 98 machine?

If it is possible, what would I use?

I have an old testing machine that currently writes to an access database.  This testing machine is probably finally going to be replaced in a year or 2.

I want to migrate the Access database to SQL Server, but as part of that effort I'd need to be able to have the Windows 98 machine write to the SQL Server.

It takes about 6 months or so to build a replacement piece of test equipment, and lately we've hit some pretty serious growth in our business, so even with all the test equipment we have, we still run behind. So it will be 2 more pieces before we can replace it.


texts: Shutdown mode fast for stopping PostgresSQL standby server

texts:
Is it correct to assume that shutdown mode smart, i.e.

pg_ctlcluster 9.6 main stop

won't work for PostgreSQL standby servers and that I will always have to use shutdown mode fast (or immediate) instead:

pg_ctlcluster 9.6 main stop -m fast

I am assuming so because of this paragrah in pg_ctl(1):

  Three different shutdown methods can be selected with the -m option.
  “Smart” mode waits
         for all active clients to disconnect and any online backup to finish. If the server is in
         hot standby, recovery and streaming replication will be terminated once all clients have
         disconnected.  “Fast” mode (the default) does not wait for clients to disconnect and will
         terminate an online backup in progress. All active transactions are rolled back and clients
         are forcibly disconnected, then the server is shut down.  “Immediate” mode will abort all
         server processes immediately, without a clean shutdown. This will lead to a crash-recovery
         run on the next restart.

I am hesitating a bit, because I have been used so far in using fast (or immediate) only in cases of relative emergencies.


texts: How to declare reserved words as variables in Oracle PL/SQL?

texts:
What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration?  I realize bind and substitution may be acceptable alternatives for an actual query, but what about Oracle PL/SQL?  For example I would like to declare a numeric variable as "num", structured below within the Oracle PL query:

SET SERVEROUTPUT ON
declare
  num number;
begin
   num := 10;
   DBMS_OUTPUT.put_line('The value of num ' || num);
end;


texts: XML Serialization in SQL Server 2005 CLR

texts:
I know this question has been asked before, but none of the answers I've tried seem to work for me.

I'm trying to execute a CLR Stored Procedure in Sql Server 2005. The CLR generates a file, sends it to a webservice, reads the XML response (using .net's XML serialization classes) and logs the result.

When I try to execute it, I get this error:

  System.InvalidOperationException: Cannot load dynamically generated
  serialization assembly. In some hosting environments assembly load
  functionality is restricted, consider using pre-generated serializer.

Supposedly, this can be fixed by using the XML Serializer Generator tool (Sgen.exe) to create the XML serialization assembly for the original assembly manually and then loading the assemblies into the SQL Server database. (See here, here and here)

I've generated the XML Serialization assembly, loaded into the database, and decorated the serialization classes using the XmlSerializerAssembly attribute. Still get the same error.

I've tried using different versions of the sgen.exe tool and toyed with different permission sets for the assemblies, to no avail.

What am I missing? Is there some configuration in the server that can prevent this from working?


texts: Locations, single primary plus multi-secondary

texts:
I'm looking for suggestions/best practices for implementation of a location management DB.

Locations have a primary access point and potentially infinite secondary access points. They must have one and only one primary access point. These access points may be referenced differently by other locations. (ie a secondary access point for one location may be the primary of another).
In the interests of DRY, I would like to have one table of "Locations" and another table of "AccessPoints". I'm struggling with intermediate tables/rules to enforce this behaviour.


texts:
In T-SQL I can accumulate the results of two unioned queries using the following syntax:

SELECT Q.a, SUM(Q.b)
FROM (
SELECT f1 as a, f2 as b FROM TBL1
UNION ALL
SELECT f1 as a, f2 as b FROM TBL2
)
GROUP BY Q.a

What is the equivalent in Oracle?


texts: System.OutOfMemoryException SQL Server 2012 and 2008R2

texts: I have a 64-bit and 64gb memory for both SQL Server 2012 and 2008R2, when I generate a report w/c has 900K rows, it finished the query smoothly and no problem, but when I'm going to copy the result with header. The error System.OutOfMemoryException is prompting. What do you think causes this problem?


texts:
Suppose I have a query like:

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

I have an index on id and an index on column but often I add a composite index with both that can improve efficiency of queries like this.  My question is concerning the order of the columns in the index.  By trial and error I've found that sometimes the DBMS prefers the joined index first and sometimes it prefers the WHERE index first.

In the above query is there a hard, fast rule I can abide by to know which key order will work best?

Typically I just add both indexes, run EXPLAIN on the query and check to see which is preferred, then remove the other one.  But this process feels like it could be improved by a better understanding of the logic involved in determining index order.


texts: In Oracle what is the difference between STDEV and STDEV_SAMP functions?

texts:
We have a query that should return NULL when doing standard deviations in groups that have only 1 row. The normal STDDEV function returns zero. However I found another function called STDDEV_SAMP which returns NULL when there's only one row. It seems to return the same result as normal STDDEV in all other respects, and the Oracle documentation (see: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm) says:

  STDDEV...differs from STDDEV_SAMP in that STDDEV returns zero when it
  has only 1 row of input data, whereas STDDEV_SAMP returns null.

But my question is, is this the ONLY difference?


texts: Error after SQL-Server Update: The prepared statement with handle xyz was not found

texts:
In the last weeks we updated two of our customers. Both got a new SQL-Server and both have now this error: "The prepared statement with handle xyz was not found."

The error occures several times throughout the day but not on all mid tier servers. If the error arises the transaction stops. In some cases the mid tier server shows this error for some minutes (not to all users) and works correct some time later. In most cases we have to restart the mid tier service.

This is what changed:

both have now a VMWare VM (prior it was a Hardware Server)
Windows Server changed from Win 2008 / Win 2012 to Win 2016
SQL Server version changed from 2012 to 2017 (customer a) / 2014 to 2016 (customer b)

The error is logged in the Windows Eventlog on mid tier servers that interact with the databases and the users get a appropiate message box.
We don't see this at the SQL server. Maybe we don't look at the right log...

Do you have any hint or suggestion or advice what we can do, where we should look, what to change to get rid of this error?

Our customers use MS Dynamics NAV 2013R2.

Thanks in advance
Heinrich


texts: Postgres: What could cause the error "cannot call populate_composite on a scalar"?

texts:
I have a column of type JSONB, where each row contains a JSON with an array of objects, eg:

[
    {
        "grade": "4.44/5",
        "endYear": 2011,
        "startYear": 2006,
        "userId": "defg"
    },
    {
        "grade": "9.133/10",
        "endYear": 2010,
        "startYear": 2006,
        "userId": "abcd"
    }
]

I'm attempting to expand those JSONB collections into rows, like:

| grade    | startYear | endYear | userId |
-------------------------------------------
| 4.44/5   |    2006   | 2011    | defg   |
| 9.133/10 |    2006   | 2010    | abcd   |
-------------------------------------------

..using the following query:

WITH arr AS (SELECT jsonb_array_elements(jsonbrecords) AS jsons
             FROM "table-with-jsonb"),
    lines AS (
      SELECT x.*
      FROM arr, jsonb_to_record(jsons) AS x(
                "field1" VARCHAR
                 )
  ) SELECT *
    FROM lines

I'm using Datagrip, which paginates results, and the query finishes fine for the first 500 rows.

However, when I try to load the last page of results, I get this error:

[22023] ERROR: cannot call populate_composite on a scalar

Googling this error shows almost no useful results (a first for me) except for the Postgres source code.

I don't speak C, but since the jsonb_array_elements CTE evaluates fine on its own, I am assuming the problem is that some row(s) have a scalar value in jsonbrecords column instead of a proper JSONB.

To ensure I had only JSONB values, I stripped the rows that didn't contain '{' with this query:

UPDATE  "table-with-jsonb" SET jsonbrecords = NULL
WHERE jsonbrecords :: TEXT !~ '{'

This deleted about a dozen rows, but the error remains.

I've also tried to find the problem by inspecting the raw JSONB rows in the table, but have found nothing.

Am I correct in thinking the error means a row has a non-JSONB value in it? If so, how can I fix? If not, how can I debug?


texts: How to find a specific column name in table design view?

texts: I have a table in my database in SQL server 2014 with a lot of columns, and when I want to find a column for modify properties, it is difficult to find that column, how can I find that column (ex: column name = "Birthdate"), has SSMS have any tools (such as find tool) for finding that column.


texts: Granting [NT AUTHORITY\SYSTEM] sysadmin on SQL server - Compliance/Best Practice

texts:
New to security in general.  We have a third party service which runs on SQL server host and under Local System.  Since it is running under Local System, they have always given sysadmin privilege to [NT AUTHORITY\SYSTEM] on older server.

When a new server was getting build we request if you could use a service account to run the service so that permission can be granted to that specific account instead of a build in account and for whatever reason the application team does not seem to have figured out a way to make it work.  Now they are asking if we could go back to giving [NT AUTHORITY\SYSTEM] permission again.

So my options/questions

Grant sysadmin to [NT AUTHORITY\SYSTEM].  Probably the worst option, but I am more interested in knowing what the industry standard is about this.  Does any compliance like SOX or PCI ban this?
Grant more granular permission to [NT AUTHORITY\SYSTEM].  This is what I will try to do first.

So I would like know about what others do in terms of [NT AUTHORITY\SYSTEM] in SQL Server and what SOX, PCI etc compliance best practices are.

Thanks


texts: How do we free up disk space from allocated namespace? / ORA-03297

texts:
We are running out of disk space.

The server has a 420 GB HDD, and runs a Microsoft SQL Server and an Oracle Server(10g).

The Oracle server uses up to 315 GB space.  We have just loaded a 100GB Microsoft SQL Server database.  Now we have approximately 5GB free and need to make a backup of the SQL Server database.

First we tried to move the backup to an other drive but this didn't work.

Then we tried to resize the allocated space of our Oracle Server, which consists of 6 files of 20GB each with about 4GB used in each file.  When we tried to resize this file to 10GB, we got the Oracle Error ORA-03297 but it shows 4GB used.  After several tries we could shorten some files to 16/17/18GB, but we are still in need of more space.

Is there a Solution how we can resize the Oracle files to 10GB each?

We've tried Coalesce and deallocate unused space, however that did not work either.


texts:
I am developing a forum like app using a MySQL database. I have posts, comments and replies just any other forum.

At the moment I have a table for posts, a table for comments/replies and a table for user/object relationships.

Lately, I have been studying WordPress CMS database structure and I've noticed that all the above are under a single table and there is a field 'post_type' that determines the object's type (eg post, comment or reply).

I have come to discover that this is very flexible for this site's purposes but is it wise in terms of speed?

Additionally, I was thinking of using an additional database such as ElasticSearch just for quering and MySQL only for storing.

What are your thoughts on this approach? Advantages/Disadvantages?

Thank you in advance :)


texts:
How do i get this column to execute, it has this exception 'Invalid name [SIS ID]? Meaning there is a space in between and want to find a way to allow it not to happened.
insert into dbo.eNtsaRegPeopleLists(LoginID,Name, [SIS ID],Section,Role,LastActivity,TotalActivity)

values(1, 'gcobani', 'Ggcobs', 'Your course guide to template', 'Developer', '2020-09-22', 05.23);

// Error
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SIS ID'.


texts:
Is there a way to log SQL warnings on a MariaDB server (10.2)?

For example, I would like to be able to record (in a log file) this type of warning (strict mode is disabled in this example):

MariaDB [mysql]> select now() = 1;
+-----------+
| now() = 1 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

MariaDB [mysql]> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect datetime value: '1' |
+---------+------+-------------------------------+
1 row in set (0.00 sec)


texts: If A and B are candidate keys, then AB is candidate key or not ?

texts: If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?


texts: Why does Concatenation operator estimate fewer rows than its inputs?

texts:
In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs.

However, an estimate of~238 million rows is produced, leading to a sub-optimal Sort / Stream Aggregate strategy that spills hundreds of GB of data to tempdb. A logically consistent estimate in this case would have produced a Hash Aggregate, removed the spill, and dramatically improved query performance.

Is this a bug in SQL Server 2014? Are there any valid circumstances in which an estimate lower than the inputs could be reasonable? What workarounds might be available?

Here is the full query plan (anonymized). I do not have sysadmin access to this server in order to provide outputs from QUERYTRACEON 2363 or similar trace flags, but may be able to get these outputs from an admin if they would be helpful.

The database is in compatibility level 120, and is therefore using the new SQL Server 2014 Cardinality Estimator.

Stats are updated manually every time data is loaded. Given the volume of data, we are currently using the default sampling rate. It's possible that a higher sampling rate (or FULLSCAN) could have an impact.