texts: Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

texts:
I  have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over from beginning from 1 although when I insert in the actual table it continues from the last value entered.
Is there any way so if I insert in actual or foreign table it goes on the sequence of primary key?

CREATE TABLE Actual_Table (
  id   serial   PRIMARY KEY
  name varchar
);

Then I insert some data in the actual table

INSERT INTO Actual_Table (name) VALUES
  ('AHMAD'),
  ('MAHMOOD');

Then I create the following foreign table in the second database

CREATE FOREIGN TABLE Foreign_Table (
  id serial,
  name varchar
) server some_server options(schema_name 'public', table_name 'Actual_Table')

And then when I user insert in foreign table using the following insert statement I get an error that is violating the primary key constraint

INSERT INTO Foreign_Table (name) VALUES ('John');

This is the error I get

ERROR:  duplicate key value violates unique constraint "to_copy_pkey"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  Remote SQL command: INSERT INTO public.to_copy(id, name) VALUES ($1, $2)


texts: adding new column slows down select statement

texts:
I have a table with a single column that is the primary key
Here is the create table statement:

CREATE TABLE `test` (
  `phone_number` varchar(18) collate utf8_unicode_ci NOT NULL,
  KEY `phone_number` USING BTREE (`phone_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The table is quite large but performing a select statement, SELECT phone_number FROM test returns a result in a reasonable time. Id like to add a date column so that I can manage the table size by regularly performing DELETEs on the table

Here is the create statement for the new table with the date column

CREATE TABLE `test` (
  `date` datetime default NULL,
  `phone_number` varchar(18)  collate utf8_unicode_ci NOT NULL,
  KEY `phone_number` USING BTREE (`phone_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

the problem now is that the select statement takes far to long to be practical. I know it must have something to do with indexes and keys but I am do not understand them well.

I would like to know why adding the date column causes the select to slow down so much, and if there is a way to use the date column along with indexes while keeping the performance of the original table.


texts: Keep stored procedures / UDF in separate database

texts: Is it good practice to keep all stored procedures in separate database which has no data and all tables with real data in separate database(s)? Main reason: to be able to upgrade stored procedures with simple restore which does not affect the production data. Second (optional) reason: 2+ databases can be processed by the same set of stored procedures.


texts: What is the most effective SELECTIVE XML index?

texts:
I am trying to determine the most effective selective XML index for an XML column within a table of 100 million rows. My question is similar to :

Sql Server 2012 Extended Events for Selective Xml Indexing not showing results

but the specific benefits of the selective index specified in the answer are not discussed in detail.

Below is a simplified version of the query:

;WITH CTE AS
( SELECT 1 AS ID,
              CONVERT(XML, '<Root>
                              <ParentTag ParentTagID="Sample Text">
                                <ChildTag1>5</ChildTag1>
                                <ChildTag1>6</ChildTag1>
                                <ChildTag1>7</ChildTag1>
                                <ChildTag2>8</ChildTag2>
                                <ChildTag2>9</ChildTag2>
                                <ChildTag2>10</ChildTag2>
                                <OtherTag>LargeIrrelevantData</OtherTag>
                              </ParentTag>
                            </Root>'
                      ) AS SampleXML
)
SELECT * INTO dbo.CTE FROM CTE

SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(MAX)') AS ParentTagID,
       RootParentTag1.ChildTag1.value('(text())[1]', 'NVARCHAR(MAX)') AS ChildTag1,
       NULL
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag1') as RootParentTag1(ChildTag1)
UNION
SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(MAX)') AS ParentTagID,
       NULL,
       RootParentTag2.ChildTag2.value('(text())[1]', 'NVARCHAR(MAX)') AS ChildTag2
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag2') as RootParentTag2(ChildTag2)

Is the most efficient index:

EXEC sp_db_selective_xml_index [DATABASE], TRUE
ALTER TABLE CTE ADD CONSTRAINT PK_CTE PRIMARY KEY CLUSTERED (ID)

CREATE SELECTIVE XML INDEX SIX_CTE ON CTE(SampleXML)
FOR
( ParentTag = '/Root/ParentTag' AS XQUERY 'node()',
  ParentTagChildTag1 = '/Root/ParentTag/ChildTag1' AS XQUERY 'node()',
  ParentTagChildTag2 = '/Root/ParentTag/ChildTag2' AS XQUERY 'node()',
  ParentTagID = '/Root/ParentTag/@ParentTagID' AS XQUERY 'xs:string' MAXLENGTH(255),
      ChildTag1 = '/Root/ParentTag/ChildTag1/text()' AS XQUERY 'xs:double',
      ChildTag2 = '/Root/ParentTag/ChildTag2/text()' AS XQUERY 'xs:double'
)

I do not want the LargeIrrelevantData, inside the <OtherTag>, to be indexed.

I'm using the following query to track indexed columns inside the XML

SELECT
a.name AS column_name,
c.name AS data_type
FROM sys.columns a
INNER JOIN sys.indexes b
ON a.object_id = b.object_id
INNER JOIN sys.types c
ON a.system_type_id = c.user_type_id
WHERE b.name = 'SIX_CTE'
AND b.type = 1
ORDER BY column_id

A few related questions:

How else can I determine the performance increase of my selective index besides reviewing query execution times?

Is it preferred to use XQUERY's xs:double or SQL's INT when specifying data types?

Thank you.


texts:
We currently have an oninsert() trigger set-up that everytime a row is insert into a table an update query fires off to update 3 fields.  The fields are a simple calculation such as

field1update = (amt/12)*14
field2update = (amt/12)*16
field3update = (amt/12)*18

The trigger is adequate, but it will sometimes lock the row so it can not be immediately accessed for a moment.  My question being if these fields were converted to a (please excuse my ignorance) calculated column or a computed column would we see performance improvement?

Usually this is a spreadsheet import and is roughly 20,000 - 25,000 rows being inserted at a time.


texts: How do I translate a Windows SID to an SQL Server server_user_sid?

texts:
There is this nice SQL Server function SUSER_SNAME which translates a server_user_sid to a user name. This is useful for translating well-known Windows SIDs to (potentially localized) user names.

Example:

SELECT SUSER_SNAME(0x01020000000000052000000021020000)

-- yields 'BUILTIN\USERS' (or, on a German system, 'VORDEFINIERT\Benutzer')

With some Googling and trial-and-error (= create the user manually and check sys.server_principals afterwards) I have determined the following equivalencies:

Built-in User/Group    Windows SID      SQL Server server_user_sid

BUILTIN\USERS          S-1-5-32-545     0x01020000000000052000000021020000
NT AUTHORITY\SYSTEM    S-1-5-18         0x010100000000000512000000

What's the algorithm to convert Windows SIDs to SQL Server server_user_sids?


texts: Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

texts:
I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc.

I'm using Mysql 5.5.x

Before doing the production system, I am doing a trial run on a copy of the DB on a test machine - just in case.

I backed up the tables with mysqldump using this information.

Some tables take more than the timeout (600sec) to convert, and so I get an error 2013:

ALTER TABLE voice.members ENGINE=InnoDB Error Code: 2013. Lost connection to MySQL server during query

When I check the table later, it's listed as InnoDB.

show table status in voice like "members";

Here are my questions:

Can I assume that the conversion went as intended?
Are there any obvious checks I should perform to ensure that integrity is not compromized?
Should I enforce that the DB is single user before conversion, or is my only worry that response time might be bad while conversion is ongoing + loss of work in case of a restore from the initial backup?


texts:
I have one query for the report.

Query

Use RIS

select CİHAZ,sum(sayi) as [Çekim Sayısı], count(*) as [Hasta Sayısı] from
(select CİHAZ,[KABUL NO],[TC NO], count(*) as Sayi from

(SELECT
a.GeneralReceptionNo as [KABUL NO], pa.TRIDNO as [TC NO],
m.EtiketAdi as [CİHAZ]
FROM   Appointments a
INNER JOIN Examinations e    ON a.ObjectID=e.AppointmentID   --aobject 1392334
INNER JOIN Patients pa       ON a.PatientID=pa.ObjectID
INNER JOIN Procedures pr     ON a.ProcedureID=pr.ObjectID
LEFT OUTER JOIN Machines m   ON a.MachineID=m.ObjectID
LEFT OUTER JOIN ExaminationReportAssistanceAssociation ra ON e.ObjectID =ra.ExaminationID
INNER JOIN Hospital..Receptions r       ON r.GeneralReceptionNo =a.GeneralReceptionNo
WHERE
CONVERT (DATE,e.CompletedDate)>='2018-12-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-12-30' AND e.IsCompleted =1) sorgu
GROUP BY CİHAZ,[KABUL NO],[TC NO] ) sorgu2
GROUP BY CİHAZ ORDER BY CİHAZ

My problem is ,when i wrote condition is CONVERT (DATE,e.CompletedDate)>='2018-12-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-11-30' , query not ended in 50 min but

if i wrote condition is CONVERT (DATE,e.CompletedDate)>='2018-11-01'
AND CONVERT (DATE,e.CompletedDate)<'2019-11-30'
 I can get the result in 12 sec.

First example time range wider than the second example. I can't find a solution. Thanks for the help.


texts: Duplication Oracle Database to remote host

texts:
I have two scenarios.

1 )  Let's say , I have the same file system and same SID on both nodes. I've created any directories necessary for start the duplicate database.
First I used command below.

CREATE PFILE = '/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora' FROM SPFILE;

I've moved  initDB11G.ora file from source server to auxiliary server.

On the auxiliary server :

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
$ mkdir -p /u01/app/oracle/archive_logs (Archived redo log files)

My initDB11G.ora from transcript:

DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata   /DB11G/control02.ctl'#Restore Controlfile
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u01/app/oracle/archive_logs'

Lastly :

DUPLICATE TARGET DATABASE TO DB11G
SPFILE
NOFILENAMECHECK;

A ) if I create a PFILE from SPFILE then do I have to use SPFILE parameter within DUPLICATE command?

B)  Another question :
If I store backup files and autobackup are in a different location on the source server ( no default location flashback database ) then how to use DUPLICATE command?

2 ) Let's say , I have the same file system and different SID on both nodes. I've created any directories necessary for start the duplicate database.
First I used command below.

CREATE PFILE = '/u01/app/oracle/product/11.2.0/db_1/dbs/initCLONE.ora' FROM SPFILE;

I've moved  initCLONE.ora file from source server to auxiliary server.

On the auxiliary server :

$ mkdir -p /u01/app/oracle/oradata/CLONE
$ mkdir -p /u01/app/oracle/fast_recovery_area/CLONE
$ mkdir -p /u01/app/oracle/admin/CLONE/adump
$ mkdir -p /u01/app/oracle/archive_logs (Archived redo log files)

My initCLONE.ora from transcript:

DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/CLONE/adump'
*.control_files='/u01/app/oracle/oradata/CLONE/control01.ctl','/u01/app/oracle/oradata   /DB11G/control02.ctl'#Restore Controlfile
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u01/app/oracle/archive_logs'

Lastly :

DUPLICATE TARGET DATABASE TO CLONE
SPFILE
NOFILENAMECHECK;

is there any missing/incorrect within two different scenarios?If so , please clarify

Kind Regards,


texts: How do we restore database configured with Always Encrypted between environments and enable web app to encrypt/decrypt data?

texts:
I have a SQL Server 2016 database configured with AE in Production environment. Column Master key Certificate is available(under Windows Certificate Store -> Local Machine location) in a different server other than database server and the web server hosting ASP.NET application also has the Certificate to encrypt/decrypt data.

For troubleshooting or operational support purpose, we usually backup and restore this Production database to our staging/UAT environment on need basis.

After restoring the database, web app is NOT working as expected since it's missing the Certificate used to configure AE in Production environment.

As per the security policy, client is NOT willing to bring the AE Certificate from Windows Certificate Store to the target environment.

In this regard, what are the best practices to be followed to make the web app working when we restore database from one environment to another? Or is there any other approaches to make the web app working without bringing the Certificate?


texts: How can I get a list of tables contained in a database diagram?

texts:
I need to get a list of the tables contained in a diagram in SQL Server. How can I do it using an SQL script?

I don't need a list of tables in the database, but the list of tables contained in a diagram of the database in SQL Server 2008r2/2012.


texts:
So.. I've got:

3 DB  Queries returning 1 row each ....

SELECT value FROM ja_settings WHERE varname='clickatel_api_id'
SELECT value FROM ja_settings WHERE varname='clickatel_user'
SELECT value FROM ja_settings WHERE varname='clickatel_pass'

Could be re-written as:

SELECT value, 'clickatel_api_id' AS name FROM ja_settings WHERE varname='clickatel_api_id'
UNION ALL
SELECT value, 'clickatel_user' AS name FROM ja_settings WHERE varname='clickatel_user'
UNION ALL
SELECT value, 'clickatel_pass' AS name FROM ja_settings WHERE varname='clickatel_pass' ;

1 db query that returns 3 rows ...  (there's even better ways than a union... I know).
This would eliminate 2 db server roundtrips on ALL pages.

How could I write this Query?
Thank you!


texts: I really can't get rid of these conditional statements in mysqldump?

texts:
My mysqldump DDL file is filled with tons of this:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`...

I've searched to no avail for an option to silence this.

My understanding is that this kind of output is necessary so that the script can successfully recreate the schema in what may be another version of mysql server. For my scenario with a closed system of installations, I know that the version will be the same as the source of the dump.

Again, I need to be able to make edits (manual or scripted) in the dump, and these outputs make the task quite a bit more daunting.

Elsewhere at SO, I saw a couple of questions on this, but folks have concentrated on preaching about why the tokens should be left alone. Kinder people recommended parsing the output on the command line to filter them out. Note that when I dump with phpMyAdmin, these tokens are not there.

Yak!

So, there really isn't a neater option to skip this kind of output? I work on Windows and with PowerShell.


texts: Query is executing without starting the PostgreSQL 9.6 Windows Service

texts:
My postgres service has not started but I am able to execute any queries on the database. Will it happen like this?
Im using win10 OS, Postgres 9.6

When Iam trying to start the service, I'm getting an error message as the below.


texts: Unable to search data fast in postgres table

texts:
I am using Postgres v 9.5.

I have a table with a column of type JSON. Trying to search data in that JSON column by using the following query:

select * from mytable where  data->'application_form_attributes'->>'5705' = 'ABCD';

To speedup the query I have tried to create an index :

CREATE INDEX myindex1 ON mytable USING btree (base_service_id,(data :: jsonb->'application_form_attributes'));

But I am getting the error:-

ERROR:  index row size 2744 exceeds maximum 2712 for index "myindex1"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
SQL state: 54000

mytable structure is:

  department_id integer,
  org_name character varying(250),
  base_service_id integer,
  service_id integer,
  service_name character varying(250),
  application_id integer NOT NULL,
  appl_ref_no character varying(50),
  submission_date timestamp without time zone,
  submission_mode_id integer,
  submission_mode character varying(150),
  submission_location character varying(250),
  data json,
  execution_data json,
  pending_task_json json,
  max_current_process_id integer,
  appl_status character(1),
  due_date timestamp without time zone,
  payment_mode_name character varying(200),
  version_no integer,
  appl_amount numeric(18,2),
  coverage_location_id integer,
  location_id integer,
  location_type_id integer,
  payment_mode_id integer,
  applied_by character varying,
  kiosk_registration_no character varying(50),
  last_task_at character varying(100),
  current_task_pending character varying(500),

  CONSTRAINT pk_table1_json PRIMARY KEY (application_id)

Need solution so that the search could be made fast.

Update :

My index that created successfully:

CREATE INDEX concurrently appl_processingjson_data_idx2 ON application_processing_json (CAST(md5(initiated_data :: text) AS uuid));

EXPLAIN output:

select * from application_processing_json where base_service_id = 721 and initiated_data->'application_form_attributes'->>'5734' = '9898989822';

"Seq Scan on application_processing_json (cost=0.00..109162.32 rows=2342 width=1998)"
" Filter: ((base_service_id = 721) AND (((initiated_data -> 'application_form_attributes'::text) ->> '5734'::text) = '9898989822'::text))"

Now problem is that this index is not being used in searching. Rather a sequential scan is being used as described in EXPLAIN.


texts: SSIS Package - "Subquery returned more than 1 value" - Inserting one record into a table

texts:
SSIS on SQL Server 2008 R2

I have inherited an SSIS package that is failing when importing a spreadsheet. The part of the failure is in a data flow that consists of :-
  * an Excel Source Data Flow Component
  * a Derived Column Data Flow Component
  * an OLE DB Destination Data Flow Component

I have added data viewers on the two flows, and these show only 1 row on each.

The table that I'm importing into is empty.

I'm not sure what else to look at. Any assistance is appreciated. Thank you.


texts: Database for personal income/expense, property and small business management

texts:
I am currently working on a personal finance management project. In my project a person want to manage his personal income, expense. Beside this, he has land business. He buy and sell lands. and some micro business.

main tables are

--Accounts (Bank, Bank2, Cash in Hand)
--Parties (who paid or whome to pay)
--Catagory (Salary, Entertainment)
--Payment Method (Cash,)
--Assets (Property)
--Loan (Client may take loan or give someone loan)
--Business (some other business like, accessories, sharemarket)

So finally i made this Schema for this, but i'm not sure this will work or not. Any suggestion is appreciated.


texts:
I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem is all the tables have foreign keys so I want to know the order in which I have to execute the tables.

Is there any way to know it?


texts: How do I use the `COPY` command with JDBC

texts:
So I've been working with postgre databases and I noticed that it throws an error sometimes when I try to read a csv file and copy to a table remotely. It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

Error occurs in this line:

st.execute("COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ',';");

Here's where I connect to database:

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");

I read online that maybe STDIN can possibly help me but I haven't been able to get it to work and most examples online are using pqsl. Is there a way I can get tthis to work or do I need to go back to buffered readers?

UPDATE:

So I've tried using the CopyManager API but it has an error when trying to make a connection using DBUtil as it is not recognized even though I downloaded the jar file from this website: http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

Code:

    Connection connection = DBUtil.getConnection("POSTGRES");
    String sql = "COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ','";
    CopyManager copyManager = new CopyManager((BaseConnection)connection);
    copyManager.copyIn(sql);

I get the error in the first line with DBUtil...is importing the jar file the only way to make jdbc recognize this variable?

UPDATE2:

Thanks again guys I ended up getting the copyin to work by following the given link and a few adjustments.

public static void readInputData(String inputDataFile, Statement st) throws FileNotFoundException, IOException, SQLException {
    Connection connection = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");
    String sql = "COPY INPUTDATA FROM stdin CSV HEADER DELIMITER ','";
    BaseConnection pgcon = (BaseConnection)connection;
    CopyManager mgr = new CopyManager(pgcon);
    try {
        Reader in = new BufferedReader(new FileReader(new File(inputDataFile)));
        long rowsaffected  = mgr.copyIn(sql, in);
    } catch (SQLException ex) {
        System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
        System.exit(0);
    }
}


texts: Import csv file into sqlite3 table - ERROR "Insert failed: datatype mismatch"

texts:
I am using sqlite3 and I am writing my scripts in two files. The first one is called "Books.sql"

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    books_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    author TEXT,
);

The second one is called "Import_books.sql "

.mode csv Books
.import C:/Users/Jim/books_data.csv Books

My goal is to import in "Import_books.sql", the csv file "books_data.csv" that contains a list of books into the table Books that I created in "Books.sql"

I made sure the csv matches exactly the structure of the table but I get this error
INSERT failed: datatype mismatch. How can I solve this?


texts: SQL date and time query not importing all

texts:
I have the below table and when i run this query:

SELECT *
FROM table1
WHERE Date <= "11/15/2018 12:00:00 AM"

it only retrieves the dates 15,14,13,12,11,10 and 1 of Nov 2018 .
How can i solve this? Thanks!

I tried changing the format of the date in the WHERE part of the query to "11/15/18 12:00:00 AM", it doesn't work.


texts: Easy way to update Excel data to SQL Server

texts:
I have a database with some tables "Products, Cores, Clients, ProdCliPrice, etc"

I am developing a software and one of it's functions is to update (or insert) the data in an Excel file to my database.

The Excel file contain a ProductID, a Core Name and a Product Price.

I wrote a code that stores all Core Names and Ids to a array, and then, line by line insert (or update if already exists) the product to the database. Since I have more than 100k rows, it is taking too long.

Could you help me with a better way to insert or update these data?


texts: SQL Server Availability Group configuration steps

texts:
I was preparing for a testing and got into question:
In which order should you perform the actions (5 total actions) to create availability group ?

I arranged actions as below:

  Launch the Failover Cluster Manager and configure AO-AG1 and AO-AG2 as servers in the cluster. Name the cluster WINCL1
  Enable SQL Server 2016 Always On Availability Group feature
  Add and configure the replica and create availability group listener named AGL1 on port 1433
  Create the Always On Availability Group and select the user databases for the availability group
  Select the Full data synchronization method and specify te network path: \DATA-CI1\SQL

Learning material says that correct order is below:

  Launch the Failover Cluster Manager and configure AO-AG1 and AO-AG2 as servers in the cluster. Name the cluster WINCL1
  Add and configure the replica and create availability group listener named AGL1 on port 1433
  Enable SQL Server 2016 Always On Availability Group feature
  Create the Always On Availability Group and select the user databases for the availability group
  Select the Full data synchronization method and specify te network path: \DATA-CI1\SQL

As you can see Learning material states that you "2. Add and configure replica..." before you "3. Enable SQL Server ... AG feature"

This seems wrong to me - as I remember you should "2. Enable SQL Server ... AG feature" before actually creating availability group

Who is right - me or learning material ?
Or both are wrong and steps should be rearranged differently ?


texts: Database collation different from tempdb collation

texts:
I just received the source code and database of an application from my client (it was developed by another company, from a different country) and the application is throwing some exceptions related to the objects collation:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal
  to operation.

I saw that in my case this is caused when a Stored Procedure creates a #temp table and this #temp table is used in a comparison with tables from the application (the application database uses SQL_Latin1_General_CP1_CI_AS and tempdb uses Latin1_General_CI_AS).

I'm adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement, however this database has many Stored Procedures that may use #temp tables.

How can I fix this faster without breaking the other databases/applications?


texts:
This statement:

INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN  deleteme  ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;

...will fail with a primary key violation in the concurrent scenario (i.e. when inserting the same key that is absent in deleteme from several threads at the same time) with the default transaction isolation level of READ COMMITTED:

  Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.

What is the best way to prevent that?

The table looks something like this:

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));

Update

From comments:

  Why do you have multiple sessions, which can obviously pull the same id from somewhere, using the same permanent table without having some kind of session key to tell them apart? Even without a duplicate error, how are you going to know which rows belong to which session?

This is a stored procedure that is called by an external service that populates data in this table. The service generates the record id and it provides no guarantee that it won't send the same data twice. Or send the same data at the same time.

The database code is supposed to discard all the records with the same id, if one already exists. The service never sends two records with the same id in the same batch.


texts: What are the benefits of packages over standalone procedures and functions?

texts: Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.


texts: Is there another source for untrusted foreign keys beside FK disable and BULK inserts?

texts:
This question and its answer explain how bulk inserts will render FK as untrusted, as bulk inserts are not completely checked (only PK and UNIQUEs) . I also know that temporarily disable of a FK may lead to untrusted status.

My current project uses a rather small database (< 3GB) and typical table count is less than 200K rows. However, integrity is very important, so I rely heavily on FK constraints (integrity is much more important than speed).

Question: is there any other mechanism (besides disable and BULK INSERT) that can produce untrusted foreign keys?


texts: SQL Server Mirroring (with Witness) and Client Timeouts

texts:
Can you explain what happens in this situation.

Imagine I have a Sql Server Mirroring set-up with 2 databases and 1 witness.

Database clients have in their ConnectionStrings the Principal= and Failover=.

Now imagine if the clients can't connect to Principal for some reason (e.g. Network error) but can for the Failover.

Will they automatically try the failover? What happens from the SQL Server perspective. What happens from the clients perspective?

Thanks.


texts: Could you monitor all changes made within a postgresql database by somehow subscribing to the replication log?

texts: I've been reading about postgresql replication and was wondering if it was somehow possible to listen to the replication binary log and push that to some tool like sumologic or splunk so that any changes made could be monitored without having to build update triggers on every table. Is this possible?


texts:
Disclaimer: I am a beginner in the field of database.

I am trying to design a database in PostgresQL which will record the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM approach.

Here is the problem with the design.

This is the simplified description of my schema.

I have a Clients table, an ItemDetails table and a RateChart table, among others.

The Clients table records details for each client, including their address.

The ItemDetails table records details for each item we sell to our clients, including a short description for the items. We do update the description from time to time.

The RateChart table has the different rates for items for each client.

Now, I also have to save the details of each and every sale. So, I have an Invoice table, and InvoiceItems table.

The Invoice table saves the invoice number (primary key), the date, the total amount of the order and the client name(foreign key, refers to client name in client table).

The InvoiceItems table, on the other hand, records the items sold, the quantity of each item sold and the rate at which it is sold.

Now, here's the problem.

The items sold in InvoiceItems list is a reference to the ItemDetails table where details of all the items are saved. Now, the details of items are changed from time to time. For example, the description of items are changed. So, whenever I change those item details, those changes will cascade down to this InvoiceItems table, and will make the older invoice records erroneous, because that was not the description when the product was sold. Same problem lies with the Client field in sale table. Whenever I change an attribute any record in the client table, the changes will cascade down to the sale table.

Here is the schema for illustration.

Now, the solution I have thought of is highly inelegant. I have considered saving the client details for every sale in the Invoice table, and the item details in InvoiceItems. Of course, that would be a great deal of duplicate data.

Another solution might be to keep the defunct details of items and clients, which would mean polluting my ItemsDetails and Client table with records no longer valid. Of course, I can add a boolean IsValid column, but then again, the solution does not seem to be succinct.

Any help would be heartily appreciated.


texts:
I'm currently investigating the possibility of using mongodb. This would contain the a collection of 100million+ documents. I need a process that will be constantly running deleting documents older than some expiry data. I will have new documents constantly going into this. I will also have big updates occuring that may need to update X million document to change the expiry date because a tenant has changed their retention policy. At the same time a front end will be reading this data to show a report which can be filtered.

Reading the mongodb documentation it looks like when I do a write I'll be locking out the reads until the writes have completed. Is there a better way to prevent the expiry of old documents and updating of expiry dates blocking the front ended display this data in a report?


texts:
We have a third party database that has a table with over 300 columns, and in SQL Server 2012 we are getting an error that we are exceeding allowed maximum 8060 bytes. I was wondering if upgrading to SQL Server 2016 will help.

Does SQL Server 2016 also have a limitation of 8060 bytes? Is it possible to have more columns in SQL Server 2016 without encountering the error below?

  Error: The table "X" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.


texts: Oracle newly created DB is filled with datatables

texts:
I've just created my first database with oracle database configuration assistant.

Basically nothing special, database was created successfully. What I don't understand is why are there so many tables already in the database? How to create empty database in oracle?


texts: Can I use the attach/detach database option to shut down Azure sql database until i need it

texts: I need to use Azure SQL Database once a week for a BI solution.  O load the data in on Monday, perform ETL, and then load the data into a PowerBI model.  After that I don't need the database anymore until next week.  In order to save costs can I just detach the database and attach next week?


texts: How to restore a Galera Cluster from backup

texts: I have a 5 node cluster, with 3 running as VMs on different hosts, and 2 nodes as physical machines. We had a power failure which took down the cluster, and I was not able to get any node to restart (using galera_new_cluster command). All nodes are current, as the power failure occurred during a weekend when everything was idle. I made a database backup, and my plan is to restore the database after getting all nodes running with no data. For the VMs, this is easy, as I can rewind the snapshot to just prior to starting with no data. For the physical servers, I'm not sure what is the best approach. Uninstall MariaDB, delete the data, re-install?


texts: Oracle Database Upgrade Assistant "Failed in bringing up..." Error

texts:
So I was upgrading 3 databases from 10.2.0.5 to 11.2.0.4. Two of them went just fine. Long story short for the last one, the upgrade was interrupted near the end (let me know if more details are necessary). Tried a couple of things, then just decided to restore because I have never manually finished upgrading a database before. It would not have been time efficient.

Restored, ran through the steps again to prepare for an upgrade, run the upgrade assistant script for the 3rd database. The first screen correctly states that the Oracle Home = 10.2.0.5, but after hitting next was told that the database needed to be mounted before the upgrade began. Database was in startup mode. So I clicked next again to see if I would get a different message. The next message I got read,

  "The Upgrade Assistant filed in bringing up the database [name]. Oracle >Home /oracle/product/11.2.0.4/db_1 obtaining from the >file /var/opt/oracle/oratab was used to connect to the database. Either the >database is not running from  Oracle Home /oracle/product/11.2.0.4/db_1 or the >correct Initialization Parameter file (pfile) was not found."

It then prompted me to give the path for the init file. But before that, I checked the oratab; it correctly stated 10.2.0.5 for that database. Went ahead with the giving the path to the init file, returned the same error without the option of choosing the init file. Tried bringing over a good copy of the pfile, changed the parameters I needed to change, deleted the spfile and created a new one from the pfile, then tried again with the same results.

So I am guessing that someone along the line during that interrupted upgrade, some reference that I don't know about was changed that is telling the Upgrade Assistant that the original database Oracle Home is in 11.2.0.4 instead of what the oratab and listener actually say, 10.2.0.5. Any insight to what I need to change here would be greatly appreciated as I do not want to do another restore without a plan of what I would do differently than the last time I did so.


texts: SQL Server CTE Bottom to Top Recursive with Where clause

texts:
I have an Employee Table with an EmployeeId, ManagerId and a Name field.

The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).

I found this link which helped to get the base of the code but I do not manage to make it work for my case

DECLARE @EmployeeTable table ([EmployeeId] int, [name] varchar(10), [managerId] int)
INSERT @EmployeeTable VALUES (1,'Jerome', NULL )  -- tree is as follows:
INSERT @EmployeeTable VALUES (2,'Joe'   ,1)     --                      1-Jerome
INSERT @EmployeeTable VALUES (3,'Paul'  ,2)     --                     /        \
INSERT @EmployeeTable VALUES (4,'Jack'  ,3)     --              2-Joe               9-Bill
INSERT @EmployeeTable VALUES (5,'Daniel',3)     --            /       \                  \
INSERT @EmployeeTable VALUES (6,'David' ,2)     --     3-Paul          6-David       10-Sam
INSERT @EmployeeTable VALUES (7,'Ian'   ,6)     --    /      \            /    \
INSERT @EmployeeTable VALUES (8,'Helen' ,6)     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @EmployeeTable VALUES (9,'Bill ' ,1)     --
INSERT @EmployeeTable VALUES (10,'Sam'  ,9)     --

DECLARE @employeeId int = 3

;WITH StaffTree AS
(
    SELECT
        c.[EmployeeId], c.[name], c.managerId, 0 AS [Level]
        FROM @EmployeeTable c
        LEFT OUTER JOIN @EmployeeTable  cc ON c.managerId=cc.EmployeeId
        WHERE c.EmployeeId=@employeeId OR (@employeeId IS NULL AND     c.managerId IS NULL)
    UNION ALL
        SELECT
            s.[EmployeeId], s.[name], s.managerId, t.[Level]+1
        FROM StaffTree t
            INNER JOIN @EmployeeTable s ON t.[EmployeeId]=s.managerId
        WHERE s.managerId=@employeeId OR @employeeId IS NULL OR t.Level>1
)
SELECT * FROM StaffTree

In case you select the employee 3 hierarchy, the result should be:

EmployeeId | Name    | ManagerId
1          | Jerome  | NULL
2          | Joe     | 1
3          | Paul    | 2


texts: How do I migrate mysql .frm and .ibd files to sqlite?

texts:
Mysql seems to not work anymore on my machine. I have spend way too much time trying to fix it but without success.
But I found out that sqlite does all the stuff I need so why not just change to it. But since mysql does nothing I also can not use mysqldump or anything, and all the tutorials I find on the internet are using either this or something else that needs mysql running.
But I have access to the database files, specifically the .frm and .ibd files. How can I transfer them to a fresh sqlite install? If it matters I am onManjaro Linux.


texts: Querying LDAP multi-value attributes using Openquery

texts:
I'm using SQL Server 2012 Express to query an LDAP linked server like this:

SELECT  *
FROM OPENQUERY
(MYLINKEDSERVER,
'SELECT  *
FROM ''LDAP://myserver:389/o=root''
        where telephonenumber = ''9*''
        AND categoryid = ''3''   ')

I manage to get the query working when I'm just using SELECT *, but I'm having problems with selection the categoryid field, as it's a multi-valued parameter.

I get the error:

  Cannot get the data of the row from the OLE DB provider "ADsDSOObject"
  for linked server "MyLinkedServer". Could not convert the data value
  due to reasons other than sign mismatch or overflow

As I have seen in other ADSI tutorials, it's not possible to query multi-valued parameters as they don't fit in a relational database, but my problem is that this attribute is not really multi-valued (only by definition), as it always stores a single value.

Is there any way to get the first-value of a multi-valued attribute?

Thanks in advance


texts: Performance Metrics After Enabling Auto-Stats

texts:
Short Version
After enabling AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS_ASYNC on a database, what performance metric will best display the changes?
Long Version
We just realized that the database for a legacy system has never had the AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS_ASYNC turned on (AUTO_UPDATE_STATISTICS was turned on).
As you would expect, after turning those on, our applications have performed much better. Response times are significantly better and the users are not waiting for the screen to refresh or pages to load.
The database is well indexed & we had already manually created stats where we thought they were needed. Immediately after enabling I checked sys.stats (joined on sys.stats_columns & sys.objects) and it only needed to create stats on three objects. (I have these logged in case quantifying these changes lies with them.)
I capture a lot of performance data on this server and I have looked at that data from prior to the change and after, but I have yet to pinpoint where the actual gains are.
I have other similar systems where auto-Stats are still turned off for that database. Those systems would also benefit from enabling them, but I am waiting in case there is some metric that I do not yet have, I can add it before making the changes.
My real question
Aside from the user’s experience, how do I quantify the increases made by changing the Auto-Stats settings on a system?
SQL Server 2008R2 on on Windows Server 2008.


texts: How would SQL Server combine Read-only and Write-only permissions?

texts:
Lets say I have two active directory groups:

Database_RO
Database_WO

Database_RO is granted DataReader rights.
Database_WO is granted DataWriter rights.

If a user is a member of both AD groups, is the user granted both DataReader and DataWriter rights?

I believe SQL will combine granted rights rather than going with the most restrictive, unless there is a explicit deny.   Correct?

Current SQL Server is 2012.   Will soon be migrating to 2016.

This question adds a corner case to the question it duplicates (two mutually exclusive AD groups rather than one group simply possessing additional rights), but the answer to that question answers that arrangement as well.


texts: Convert to 2 decimals and keep leading zero

texts:
The following piece of code is resulting in a value with 4 decimals.
COALESCE (SUM( CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' and PLL_REVENUETYPE = 'OTHER' then CAST(ROUND(PLL_AMOUNT,2) as decimal (9,2))
WHEN PLL_REVENUETYPE    = 'TAX' THEN CAST(ROUND(PLL_TAXAMOUNT,2) as decimal (9,2))
WHEN PLL_ISINCLUSIVETAX = '-'   THEN CAST(ROUND(PLL_NETAMOUNT,2) as decimal (9,2))
ELSE CAST(ROUND(PLL_NETAMOUNT,2)as decimal (9,2)) END ), 0) * -1.00 AS AMOUNT

I found many suggestions on the web, but I haven't been able to round the amount with 2 decimals.
Also, when I do a SQMCMD to export the query to a CSV, it will drop the leading zero if the amount is 0.00
Any help would be appreciated.


texts:
Say I have a table like this:

create table SomeTable
(
    id int identity(1, 1) not null primary key clustered,
    SomeString1 varchar(50) not null,
    SomeString2 varchar(50) not null
)
go

create nonclustered index IX_SomeString1
on SomeTable(SomeString1)
go

If I was to do this:

insert into SomeTable(SomeString1, SomeString2)
values('foo', 'bar')
go

And view the actual execution plan, I only see a Clustered Index Insert.  Why am I not seeing a Nonclustered Index Insert in the execution plan?


texts: How can I keep both MySQL databases consistent in master-master replication after recovering from failed event?

texts:
I was given a task to fix the sync issue between 2 database servers where I had two MySQL database servers set up with master-master replication. They were working normally but one of the servers was stuck due to errors in executing the events received from the master. I ran the following command on the slave and found the error in the output.

show slave status

I got Slave_IO_Running = Yes, Slave_SQL_Running = Noand Last_Error was showing Duplicate error message.

While searching for the fix I got a solution where we can skip the failed event one by one until the slave can successfully execute the events received from the server.

The solution I applied:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

My question is that when I am skipping the events in this way I believe it might lead to the inconsistency in the databases so what steps I should take so both databases would be having consistent state without the need of taking the full backup of one database and then restoring it at the other database.

As I am new to this concept, it would be great if suggestions can be grasped easily. :)


texts:
I am working on a project that requires a column that is currently in plain text to be encrypted. As part of the project i still need to be able to run a SP from SSMS and the plain text data. I was going to go with the standard symmetric key encryption that is encrypted with an asymmetric key.

From the aspect of protecting the data from unwanted access, I'm not sure what the point of encrypting the data is if there is going to be an SP that is going to have all of the code to decrypt the column.

Is there a strategy for encrypting a single column, and protecting the data to ensure that if the DB is stolen then no one can access the encrypted data, even if they have access to the SP?

I know you can encrypt the SP's, but there are a lot of free tools to decrypt the SP's so I don't feel like that is a good strategy other than adding an extra step for someone to get through.


texts: Fetching content binary from database or fetching content by its link from storage service

texts:
For an app (web + phone) there are two options:

Image binaries in database. Server replies to app HTTP request with images as base64
Images in storage service like Amazon S3 or Azure Blob Storage or a self-hosted one. Image links in database. Server handles app HTTP requests by sending back only the links to images. The app fetches the images from storage by their link

Which option above is the standard practice? Which one has less trouble down the road?


texts:
select id
from master_tabe
where id = ALL (select id from user_table where name = 'Nikhil')

select id
from master_tabe
where id = ANY (select id from user_table where name = 'Nikhil')

Above mentioned queries are returning the same resultset. According to my knowledge there should be different resultset as the user_table has only one record related to name Nikhil. So according to this Any clause must return 1 row whereas All must return 0 records. Whereas when I add one more record in user_table than All clause statement is returning 0 rows and Any clause is returning only 1 row.

Can someone look into this?


texts: When one of masters is unreachable all other replica sets work too slow in mongodb 2.6.11 and web application throws error 71

texts:
When one of masters is unreachable all other replica sets work too slow in mongodb 2.6.11, also I can not list the databases on mongos servers, and that is because of one replica set. How to solve this problem.

Update:

I have found this https://jira.mongodb.org/browse/SERVER-7246, do they say "If you have no redundant network connection to remote site, then go to hell"?


texts: MongoDB multi-datacenter deployment and shard keys

texts:
I am trying to check my understanding after reading docs and some older questions, so my question is quite loaded:

In a sharded cluster, each shard should (and as of 3.6 must?) reside on its own replica set. 2) Each replica set has its own master.

So suppose I wanted to deploy a Mongo cluster in 2 availability zones. So I set up a 6-node cluster with 2 shards on 2 separate replica sets, such that master for each set is in different datacenter. Slave nodes are distributed such that at least one slave from each replica set is in each datacenter, so:

If I use "majority" read concern, would my mongod instances within same replica set have to communicate between my datacenters to verify that data is available on at least 2 instances? It sounds a little silly. Or would replication take longer, as each instance needs to confirm to other instances that its write is complete?
If I use "available" read concern, can I guarantee that my application reads from closest instance? Can I make my application (say Mongo CLI) prefer a certain slave?
If I wanted to guarantee each write happens to the closest master, I could use region as shard key.

Now suppose I wanted to do same exercise with 4 shards, 2 on each replica set.

Is it possible to have more than one shard in one replica set? Ie each node would duplicate 2 sets of data?
If so, and I wanted to still make sure each write happens to the closest master, I would use a compound shard key, with region and some other attribute. Can I somehow configure ranges of my shard key hash results to each master? Ie I'd like to achieve something like:

Shard 1 and 2 reside in US-West datacenter and shard 3 and 4 reside in US-East datacenter
Then for my compound shard key of (region, foo) I would want to guarantee that:
| region = US-West | region = US-East |
| foo < 0 | foo >=0| foo < 0 | foo >=0|
| shard 1 | shard 2| shard 3 | shard 4|


texts: How to set a specific directory location for individual MySQL database?

texts:
Is it possible to set each database in MySQL to use a separate datadir?

I'm running a userdir development (sandbox) server and would like to put the MySQL data files for the database(s) for that user in their /home/<user>/mysql directory.

Linux Ubuntu Server 10.4
MySQL Server version: 5.1.41
Storage Engine type: InnoDB

How would you do this?


texts: PostgreSQL not using the index on queries that return large number of records

texts:
I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used.

Index not used:

explain (buffers, analyze) select * from my_table where column_a in (1);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..1306095.85 rows=7772384 width=648) (actual time=0.009..6231.312 rows=7720995 loops=1)
   Filter: (column_a = 1)
   Rows Removed by Filter: 7398657
   Buffers: shared hit=463612 read=653528
 Planning time: 0.840 ms
 Execution time: 7717.923 ms
(6 rows)

Index Used:

explain (buffers, analyze) select * from my_table where column_a in (8);
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_table  (cost=1378.58..233236.51 rows=73567 width=648) (actual time=14.258..67.795 rows=74756 loops=1)
   Recheck Cond: (column_a = 8)
   Heap Blocks: exact=36425
   Buffers: shared hit=36632
   ->  Bitmap Index Scan on my_table_column_a_idx  (cost=0.00..1360.19 rows=73567 width=0) (actual time=8.595..8.596 rows=74756 loops=1)
         Index Cond: (column_a = 8)
         Buffers: shared hit=207
 Planning time: 0.855 ms
 Execution time: 82.253 ms
(9 rows)

I have the table vacuumed:

select relallvisible, relpages, relallvisible/relpages as ratio from pg_class where relname='my_table';
 relallvisible | relpages | ratio
---------------+----------+-------
       1117140 |  1117140 |     1
(1 row)


texts: In MySQL, does the order of the columns in a WHERE clause affect query performance?

texts:
I am having performance issues on certain database queries that have large possible result sets.

The query in question, I have three ANDs in the WHERE clause

Does the order of the clauses matter?

As in, if I put the ASI_EVENT_TIME clause first (since that would remove the most of the results out of any of the clauses.

Will that improve the run time on the query?

QUERY:

SELECT DISTINCT  activity_seismo_info.*
FROM `activity_seismo_info`
WHERE
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    )

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

EXPLAIN of query:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Using:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propel 1.3

Symfony 1.2.5


texts: SQL 2008 Server agent cant see perfmon counters

texts:
I am trying to create a new SQL server agent alert to capture "SQLServer:General Statistics|Processes Blocked||>|5' but it looks like alert creation GUI is not able to see any perfmon counters as in the following picture:

The drop downs for Object, counter and instance are empty.
I can't even create the alert using TSQL as follows:

I can see these counters collecting data in perfmon.

Any ideas?
Thanks in advance...


texts: How to know SQL Server installation language while it is down?

texts:
SQL Server is down after a security update.

I need to install a cumulative service pack which is language discriminated.

I'm downloading Spanish and English versions, I'm sure it will be either one, but it could be better to determine which it is while the server is down.


texts: How to find the current Log Sequence Number (LSN) of a database that is in Restoring state?

texts:
I removed one of my databases from the availability group on my secondary replica in my AlwaysOn group for an experiment. I want to add it back, but I cannot do so because the databases needs to be rolled forward to match what is on the primary (there was a transaction log backup taken on the primary, I guess that clears the log on the primary and that makes it impossible to join the database back to the availability group on the secondary without restoring from the transaction log).

The database is now in the "Restoring" state. I have the transaction backup files on the Primary replica and I can copy over the files needed to perform a transaction backup restore on the secondary to roll it forward. I tried a couple of files and I get the error below

I understand that this means my log back up is older than the current state of the database. The current state of the database seems to have the last LSN to be the second number in the error message.
Question: How can I query the database to find out the second number LSN 956952100000057? Is this error message the only place where I can see it and Follow up: Is trial and error of the only way of figuring out which log backup file the correct one?


texts: What can I do about high CXPACKET waits?

texts:
We are experiencing some slowness on our MS SQL Server 2016 database, I have been using Brent Ozar's first aid kit to do some initial troubleshooting.

I am seeing a high amounts of CXPACKET wait types, out of a 17.5 hour data sample we saw 99 hours of wait across our 10 CPU's, that 55.5%!

I was hoping someone here could confirm that we should be concerned about this number and resolve it asap. We have MAXDOP setting of 4 which is accurate to MS recommendations but our CTP is set to 5 which I believe needs to be changed to 50.

Just looking for clarification before I take this information to my boss, yes, I am new to database administration and yes I am looking at other wait types but this seems to be the most significant so far.

Cheers,
Josh


texts:
In DB2, I need to do a SELECT FROM UPDATE, to put an update + select in a single transaction.
But I need to make sure to update only one record per transaction.

Familiar with the LIMIT clause from MySQL's UPDATE option

  places a limit on the number of rows that can be updated

I looked for something similar in DB2's UPDATE reference but without success.

How can something similar be achieved in DB2?


texts:
I have managed to setup master and slave replication.

It is working fine. What are the possibilities that it might go down?

Is there any alerting tool to monitor that?

Another thing: Can I run a separate db in my replication db which I just run for testing purposes?


texts: Using Galera Cluster(Percona XtraDB Cluster, MariaDB Cluster) for setting up multi master

texts: Before i gotta use one I want to know if it is working in windows OS cause I saw a lot using it in linux OS. Does using galera (Percona XtraDB Cluster, MariaDB Cluster) cluster works in windows?


texts:
I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works):

SELECT oks_c.contract_number,
       oks_l.line_id,
       oks_l.tank_numbers, column_value AS TANK_NUMBER
  FROM oks_contract_header oks_c
 INNER JOIN oks_contract_lines oks_l
    ON oks_l.contract_number = oks_c.contract_number AND
       oks_l.item_name LIKE '%.55201'
    , table(str2tbl(oks_l.tank_numbers,';')) acbb
ORDER BY oks_c.contract_number,
         oks_l.line_id,
         TANK_NUMBER

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return
str2tblType
PIPELINED
as
    l_str      long default p_str || p_delim;
    l_n        number;
begin
    loop
        l_n := instr( l_str, p_delim );
        exit when (nvl(l_n,0) = 0);
        pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
        l_str := substr( l_str, l_n+1 );
    end loop;
    return;
end;

But I'm trying to understand why it works.  Particularly how the pipelined function results are not cross-joined?

I'm familiar with outer apply and table-valued functions in SQL Server, but this seems to be quite different - or is this really an OUTER APPLY?


texts: Data in tables after DROP DATABASE and recreate

texts:
Just came into the 'feature' I had no idea will ever happen. We have two servers for development - S1 and S2. We most often use S2 and S1 went out of date. Let's assume that S1's database DB1 stays at C:\mssql\data\db1.mdf

What I did:

Drop database DB1 on S1
Scripted DB1 on S2 without data as CREATE DATABASE... and create tables, indexes, etc...
Run script on S1, to create new DB1 as on S2.

although I didn't delete db1.mdf file, after dropping and recreating I expected my tables to have no data. What was my surprise after finding some tables filled!

What's the explanation for that?

Cheers.


texts: Finding "one of" entries to a composite key/index

texts:
Problem:
Assuming I have a table like this:
CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `a_id` bigint unsigned NOT NULL,
  `b_id` bigint unsigned NOT NULL,
  `c_id` bigint unsigned NOT NULL,
  `d_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_entry` (`a_id`,`b_id`,`c_id`,`d_id`),
  KEY `test_b_id` (`b_id`),
  KEY `test_c_id` (`c_id`),
  KEY `test_d_id` (`d_id`)
)

In this test table, every id can appear multiple times, but all combinations must be unique.
Now I want to write an application to compare them. I don't want to compare apples with peaches, so I have to find entries where all other keys are the same and only one of them is different.
How can I archive this and what indexes would be useful here? Let's say comparisons on column a_id would be significantly more common than on all other columns.
There is no need to find this for all 4 columns in one query. I would like to have a template query to find comparable entries for one of the columns.

Example:
Assume there are 10 different ids for each column and every combination of them is present in the database. (10^4 = 10000 entries).
Query 1:
Now I want to compare a_id = 1 with a_id = 2. The query result has to look something like this:
Test: [a_id, b_id, c_id, d_id]
1, 1, 7, 1
2, 1, 7, 1
1, 5, 5, 3
2, 5, 5, 3
1, 1, 7, 4
2, 1, 7, 4
⋮

Notice a_id is in (1, 2) but for each entry, there is a matching one, where only a_id is different.
Query 2:
Also, I would like to provide a way to query which comparisons could be made on a specific column. The result should look like Query 1 but for arbitrary ids. (id_a in (m, n))
In my case the database does only contain a subset of all possible combinations.

One idea:
From my understanding, unique composite keys are like an additional unique column where the value is a chain of the original columns in the specified order.
fname, lname, composite_key(lname, fname)
---------------------------
John, Miller, Miller-John
Peter, Jones, Jones-Peter
…

If it is possible to query on this composite_key index tree or something like that, my problem would be easy to solve.
Create a composite index for each column so every column is the last one once. E.g.
KEY `key_a` (`b_id`, `c_id`, `d_id`, `a_id`),
KEY `key_b` (`a_id`, `c_id`, `d_id`, `b_id`),
KEY `key_c` (`a_id`, `b_id`, `d_id`, `c_id`),
KEY `key_d` (`a_id`, `b_id`, `c_id`, `d_id`),

For a column a_id comparison just find all paths on the tree with two values on the last branch. This would be similar for the other columns.


texts:
I have a SQL Server Agent job that backs up a database on a remote server to a network drive. Linked Server, I believe is set up correctly but I get the following error:

  Msg 3013, Sev 16, State 1, Line 35
  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

  Msg 3201, Sev 16, State 1, Line 35
  Cannot open backup device. Operating system error 1326
  (failed to retrieve text for this error. Reason: 1815). [SQLSTATE 42000]

I have used SQL Server Configuration Manager to change the Log On to "This Account", selected the domain account and supplied the right password.

Then, on the Linked Server Properties, I mapped the Local Login to the domain account and ticked "Impersonate" The security on the network drive folder is set to "everyone" and full permission is given. And yet still getting this error and cannot backup the database as a result.

What can I do?


texts: Any way to represent simple AND/OR business logic in a database?

texts:
I'm designing an application to provide students access to modules once they have completed certain online courses.

I have tables for: Students, Modules, Courses, and StudentCourseResults. Course requirements and modules will change over time and I believe it would be easier to track their relationships in the database than the application. Unfortunately this is not a simple 1-1 mapping. For example, for ModuleA you may need to complete Course 1 OR 2, but for ModuleB you may need to complete Course (1 OR 2) AND 3. Requirements may also be somewhat different depending on the type of student.

Has anyone tackled this kind of requirement before? I can't think of a solution other than creating a ModuleCourseRequirements table and putting the logic in a field (e.g. (1|2)&3) then parsing this logic in the application and performing subsequent queries to check the requirements are met.

Let me know what you think, thanks!


texts:
I have a table and I need to update some names but I was wondering about the

following queries :

Will both do the same?

Query1

Update mytable
Set Name = Replace(Name,'Jeff','Joe')

Query2

Update mytable
Set Name = 'Joe'
where Name = 'Jeff'


texts: Is this a good strategy for importing a large amount of data and decomposing as an ETL?

texts:
I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of other tables, including inserts that just rely on the values I just inserted into the first tables.

I can do my inserts as an A, B, C process, where I insert into the first table, then insert into some table S where exists in A + T (being some other table that has preloaded "configuration data"), then inserting into Z where exists in B + U, etc.

Should I be trying to batch those inserts with a cursor (I know, stone the traitor) or should I just run the raw insert into scripts and let the server die a thousand heat deaths? Basically I'm worried about starving the server or causing it to collapse from too many index or something else with inserts.

Should I stage out the inserts as:

Insert into one set of tables
Insert into secondary tables based on the first tables
Insert into tertiary tables, etc.

OR should I insert into all the tales where the data is needed but do it via cursor, in a "for loop" style pattern of 100k rows at a time.

FWIW, this is a behavior I saw from the DBAs at my last job, so I figure that's "what I'm supposed to do" (the batch process via cursors) but maybe I don't understand enough about what they were doing (they were also live-loading into systems that already had data, and were loading new data afterwards).

Also bear in mind that I'm normally a C# dev, but I've got the most TSQL experience here and I'm trying to make the best process I can for raw-loading this data as opposed to our "current" method that is mostly webservice fetches and NHibernate save-commits.

Things I think are important to the question:

There will be no other load on this server when I do this, I will have complete and total control of the box, and I'm the only user interested here (this is the initial data load before anyone else can do anything with the box)
There are indexes on all the tables, where appropriate, and there are FKs and other predicates
The entire db system is in use for slews of other clients right now, so I know the indexes are needed for operation, but if I should do something to disable those and re-enable them to make the inserts etc faster, I'm looking for those sorts of optimization hacks.
I'm waiting on sample data but I expect the maximum records in a given table to be about a million rows, fairly wide, and that I'm going to insert into those other tables at most a million rows for the ancillary tables that can come secondary or tertiary.
The hardware is "average".


texts: Any benefit to having multiple filtered indexes?

texts:
I have a SQL Server database and stumbled across a table with a uniqueidentifier primary key named ActivityId and columns StateCode (int) and ActivityTypeCode (int) along with many others. The tables has 13M rows and there are 94 unique values for ActivityTypeCode with row counts ranging from 1 to 4M. OK, here’s the weird part: I have 97 different filtered indexes on (StateCode, ActivityId) where the only difference is the where clause on (ActivityTypeCode=(_some_value_)).

To my eyes, all 97 indexes could be dropped and replaced with one index on just StateCode (since ActivityID is the primary key and is already included as a hidden column). My question is: Is there any reason why I would want to keep multiple filtered indexes? Could they reduce deadlocks somehow? I’ve done some testing and I don’t see any reason to have so many indexes. Thoughts?

Ken

[Edit]
Based on the excellent answer below, I've done some testing and it does not appear that the filtered indexes prevent blocking on even simple queries.  My indexes are of the form:

CREATE INDEX IX_StateCode_ActivityID_ActivityTypeCode_Filtered_0 ON dbo.Activities(StateCode,ActivityId) WHERE ActivityTypeCode = 0;

If I go into one database connection and issue the following:

Begin transaction;
Update Activities set StateCode=888 where ActivityTypeCode=1

Then go into a second connection and issue this:

Update Activities set StateCode=999 where ActivityTypeCode=3

The second connection hangs.  It seems these different indexes do not prevent blocking so I don't see any reason to keep them.  Given that there are indexes for ActivityTypeCode values that do not exist, I suspect they were all generated by some sort of development tool.  FYI:  This is from a third party vendor.

Thanks everyone for your help.

[Edit #2] Upon detail examiniation, not all indexes are the same.  Below are the combinations.  Keep in mind ActivityID is the primary key.

StateCode, ActivityID
StateCode, CreatedOn, ActivityID
StateCode, CreatedOn, StatusCode, ActivityID
StateCode, StatusCode, CreatedOn, Activity ID

I don't think any of this will prevent me from replacing them all with a single index - it will just be a bit larger than originally anticipated.


texts: Should I be concered by large SERIAL values?

texts:
I have a Django application that uses PostgreSQL to analyze data from tweets. The data set increases by thousands of records with each request. I am using the database primarily as a cache, so I had planned to delete all records every 24 hours to permit new requests without needlessly increasing the size of the database.

Django uses the SERIAL type for storing the ids; a new item is given the next highest value of the last item that was created, rather than the first available number. I don't use the ids for anything outside of the ORM. My concern is that I will eventually run out of key space on my 32 bit VM. What does PostgreSQL when the next value is too large for SERIAL? Does it give error? Does it roll back to one?


texts: Change a field in Database on a Particular Date

texts:
There is a field in my database named GRADES, what I want to accomplish is that on a particular date I want to update all the grades of a specific value to a new value. For example, let's say 5 weeks from today's date I want to update Grades from I to F.

Thanks in Advance

Note: I cannot do anything to the database like create a new table or a new field  it should be in the same table.


texts: Should you run DBCC CHECKCONSTRAINTS regularly?

texts:
In my database, no one is adding, removing, or disabling constraints outside of patches and releases. All constraints are enabled and trusted. There is a regular check for untrusted or disabled constraints in case one were to be accidentally introduced. DBCC CHECKDB is also run on a regular basis to check for corruption.

Is there a benefit to running DBCC CHECKCONSTRAINTS just as regularly as DBCC CHECKDB? Or am I causing unnecessary work? When should DBCC CHECKCONSTRAINTS usually be run?


texts: View dropped, now shows as table "in use", can't drop

texts:
I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid.

I tried dropping the view so I could recreate it, then MySQL said it was a table (not a view) and marked it "in use".

Now I can't drop nor repair that table because it says it is not a table.

I've googled for hours trying find a way to fix this :(

I do have root access on the MySQL server running Ubuntu. I stopped MySQL and tried myisamchk, but no luck.

I can see a .frm file for the view in the db data folder.

Do I need to delete that file to make the corrupt item go away,
or do I somehow delete the table from the db schema?

I have a db backup from last night, but would rather fix this 1 issue than restore from backup and lose today's changes.


texts:
I want to change the PORT to 255:

can i change per db or just for instance?
is there any issue can come up?
have to restart the service?


texts: Connection Reset error - SQL Server - MS JDBC Driver

texts:
We have a situation where this error only happens when connecting to our on-premise database, with a cloud runtime engine. This runtime is doing many database lookups and also excecuting insert and update commands, the runtime should be closing each connection automatically after the db command is successful.

"Failed to write JDBC target. Database Error: Connection reset"

One key factor for this matter is that we are able to run the "same" runtime  locallly and not have the Connection Reset errors. Also, this errror is intermittent. On some of our less complex runtimes, the cloud runtime is able to connect and execture db commands against the on-premise database without having connection resets crash the process. Based on this, I don't think it's a connectivity, firewall, or security issue.

From the research I've done, JBDC will not directly manage the connection pooling or call the sp_connection_reset stored procedure, rather the 3rd party Java EE in the runtime engine will be managing the connection pool.

I'm not necessarily looking for an answer here, as I realize I'm not a DBA and you will probably need more details to really dive in and solve this issue.

Rather, I'm trying to establish a baseline path and understanding for troubleshooting this issue.

My assumption is the connection pool is eventually reaching it's maximum number of connections allowed, and then the pooling mechanism will re-use previous connections in the pool that are not being used. It sounds like when that happens, the connection needs to be reset for integrity and security reasons.

If and when that happens, is that why our complex process is dying with this error in the middle of the process?

If so, I plan to focus in on what is different between the local and the cloud runtime engine configuration in regards to the maximum connections allowed and if and when it's re-using and resetting connections.

From what I read, it's not recommended to override this and set the connection reset flag to false, so it seems the answer will be to reduce the amount of db calls the runtime is making and/or increase the maximum connections allowed, so we don't get to the point where a connection needs to be re-used and therefore reset.

We do use these two params on our JBDC Driver configuration:

encrypt=true;trustServerCertificate=true

Is my fundamental direction here correct?  If not, please steer me in the right direction.

Thanks for your expertise,
Jon


texts: Loading the databases from previous installation mysql

texts:
My Windows OS crashed today. Now I have recovered all the files in Program files/MySQL folder.(I don't have the exported .sql file)

With the new installation of MySQL 5.6(same as before), can I get those databases back(Or how to load the data to new installation of MySQL)? Please suggest.


texts: Is it possible to do index optimization with Ola's scripts and have it filter out a specific schema?


texts:
How can I output text until it is ascii in SQL?
Here is what I am trying to do:
DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE CHAR(ASCII(SUBSTRING(@input, @index, 1))) LIKE '[ -~]'
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

But in the end I am getting an empty string. Why? What am I missing here?
I am expecting the value of the @output to be 'text' in the end of the script.
UPDATE
If I update the script to the
DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE CHAR(ASCII(SUBSTRING(@input, @index, 1))) LIKE '[a-z]'
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

It will work as expected. But here I just shrinked the set from all printable ascii characters to only small letters. Why does the shrinked set [a-z] include the text characters and the extended set [ -~] does not?


texts: Sample.exe is NOT created under the \bin\x64\Release folder

texts:
I am following https://github.com/griddb/c_client to install griddb
under Quick start (Windows) subheading it is written.
A sample.exe is created under the <C_CLIENT_SRC_PATH>\bin\x64\Release folder
However, Sample.exe is NOT created under the <C_CLIENT_SRC_PATH>\bin\x64\Release folder
Please help.


texts: How to extract a whole database schema using only ODBC access?

texts:
I need to migrate an old proprietary database (custom database based on a Btrieve engine) to a newer system . All I have to access this database is an ODBC driver that works pretty well. When I connect to the database, I only see views but still the database documentation provides the table names and I am able to access them even though the driver does not show them.

Unfortunately the database documentation is quite poor and does not allow me to generate the full schema. Some information like the column precision and scale is missing.

So is there a tool that would allow me to read the full database schema and dump it to a file ?

I really just need a description of the the tables like table name, column name, datatype, precision, scale and optionally comments. I don't even need relationship and keys. For the rest, I have enough skill to process those information and generate a script for my destination database.

Regards.


texts: How can wait times be higher than clock time?

texts:
When I'm tracking waits with sp_BlitzFirst, I get this detail:

<?ClickToSeeDetails --
For 20 seconds over the last 5 seconds, SQL Server was waiting on this
particular bottleneck.

 -- ?>

Should that read "for 20 times over the last 5 seconds?"  Finding was CLR_SEMAPHORE.


texts:
I am trying to install mongodb on Ubuntu EC2 instance. However, I am confused about what user the DB would run as:

If I follow: http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/

Then it says : "mongodb"

If I follow : http://docs.mongodb.org/ecosystem/tutorial/install-mongodb-on-amazon-ec2/

It says : "mongod"

I think it leads me to inconsistent state. There was a process running is ps output for mongodb but sudo service mongodb status or stop says: Unknown Instance.

What should be the user of mongodb?


texts: How to set a search_path default on a psql cmd execution?

texts:
I'm looking for an option to create an alias command for a psql console connection which allowed setting the search_path, but I am not finding any option on psql util, nor the option to execute a command without exiting. any idea?

I would like to avoid preconditions as setting environment options and have a oneliner if possible


texts:
I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS.

Every night we do a backup of all the databases with this script:

rm -rf /var/backup/mysql/ *
for i in $(echo 'SHOW DATABASES;' | mysql -u root -p'password' |grep -v '^Database$'); do
mysqldump \
-u root -p'password' \
-Q -c -C --add-drop-table --add-locks --quick --lock-tables \
$i > /var/backup/mysql/$i.sql;
tar -cpzf /var/backup/mysql/$i.sql.tar.gz /var/backup/mysql/$i.sql;
rm -rf /var/backup/mysql/$i.sql
done;

Every time the backup is executed a particular MySQL table is broken (the MYD and MYI files are deleted, while the FRM remains).

We have many databases and tables, only this one is broken.

Any idea?


texts: How to implement complex search functionality?

texts:
Below is my table structure:

CREATE TABLE `post` (
  `postId` bigint(20) NOT NULL AUTO_INCREMENT,
  `userId` mediumint(8) unsigned NOT NULL,
  `areaId` smallint(5) unsigned DEFAULT NULL,
  `postTitle` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `postPrice` int(10) unsigned NOT NULL,
  `availableFrom` date DEFAULT NULL,
  `postCreateDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `postCreateIp` varbinary(16) DEFAULT NULL,
  `postUpdateDate` timestamp NULL DEFAULT NULL,
  `postUpdateIp` varbinary(16) DEFAULT NULL,
  `fullAddress` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `floor` tinyint(3) unsigned DEFAULT NULL,
  `room` tinyint(3) unsigned DEFAULT NULL,
  `bath` tinyint(3) unsigned DEFAULT NULL,
  `size` smallint(5) unsigned DEFAULT NULL,
  `postContactNo` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `des` varchar(511) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `imageUrl` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `postType` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0>for_rent\n1>need_rent',
  PRIMARY KEY (`postId`),
  KEY `post_userId_postCreateDate_index` (`userId`,`postCreateDate`),
  KEY `post_areaId_postType_postId_index` (`areaId`,`postType`,`postId` DESC),
  CONSTRAINT `post_join_area_areaId_fk` FOREIGN KEY (`areaId`) REFERENCES `join_area` (`areaId`),
  CONSTRAINT `post_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=20004 DEFAULT CHARSET=utf8

  I want to add search functionality for this table. There will be below conditions:

  I may search by text which will match with rows postTitle and
  des.
  I may also filter those result by many conditions for rows
      areaId, postType postPrice, availableFrom, floor, room, bath, size.

Below is an example:

select *
from post
WHERE areaId = 29
  AND postType in (0, 1)
  AND (postPrice > 0 AND postPrice < 10000000)
  AND availableFrom = '2019-06-26'
  AND (floor >= 0 and floor <= 6)
  AND (room >= 0 and room <= 20)
  AND (bath >= 0 and bath <= 10)
  AND (size >= 0 and size <= 20000);

Now I'm not sure how can I implement that efficiently. I can think below ways:

First I will query for data which match against postTitle and
des rows, this query will use MySQL full text index which I will
create on postTitle and des rows. Then i will apply filter on
those result for areaId, postType postPrice, availableFrom, floor,
room, bath, size rows but this will not cover any index because
this will use temp table.
Second, I may write query by combining all conditions like below:

select *
from post
WHERE (postTitle like '%post%' or des like '%test%')
  AND areaId = 29
  AND postType in (0, 1)
  AND (postPrice > 0 AND postPrice < 10000000)
  AND availableFrom = '2019-06-26'
  AND (floor >= 0 and floor <= 6)
  AND (room >= 0 and room <= 20)
  AND (bath >= 0 and bath <= 10)
  AND (size >= 0 and size <= 20000);

  But I'm not sure how to make this query efficient. How to create index for this query. Please suggeset me how can I design this search functionality or what do I need to do to make this query efficient?


texts: How do I correctly use %p and %f in postgresql.conf to make the Write Ahead Log?

texts:
I am attempting to modify postgresql.conf (Postgre SQL 10.0 under Ubuntu 18.04 VPS) to setup the Write Ahead Log (WAL).

AIUI, the commands should be:

wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /var/lib/postgresql/10/main/pg_wal/%f'
archive_timeout = 600           # force a logfile segment switch after this
                                # number of seconds; 0 disables

AIUI, %p references the database file (to be archived) and %f references the filename that will contain the copy of said database file.

In this case: I am saying to copy every 5 minutes the database file to the /pg_wal directory.

However, this is clearly not correct as the error log files shows:

cp: 'pg_wal/000000010000000000000002' and '/var/lib/postgresql/10/main/pg_wal/000000010000000000000002' are the same file
2020-02-16 21:01:05.857 UTC [20707] LOG:  archive command failed with exit code 1
2020-02-16 21:01:05.857 UTC [20707] DETAIL:  The failed archive command was: cp pg_wal/000000010000000000000002 /var/lib/postgresql/10/main/pg_wal/000000010000000000000002

So I am not sure how %p and/or %f are supposed to be used. If someone could present the syntax, I would be most grateful.


texts:
I need to check email addresses against an internal known bad domain database.  My internal database has domains in the form of myfreepaysite.com just no @ sign.  I have been using this syntax to check, but it locks up my SSMS for roughly 90 seconds then unfreezes.  Is there a better way to do such?

Create Table #Testing
(
  emaildomain varchar(Max)
)
INSERT INTO #Testing VALUES ('myfreepaysite.com')

Create Table #DataToCheck
(
  fullemail varchar(max)
)
Insert Into #DataToCheck VALUES('rr1234@myfreepaysite.com')

Select Top 1
fullemail
,SUBSTRING(fullemail, CHARINDEX('@', fullemail) + 1, LEN(fullemail))
FROM #DataToCheck
WHERE SUBSTRING(fullemail, CHARINDEX('@', fullemail) + 1, LEN(fullemail)) IN (Select emaildomain FROM #Testing)


texts: transaction chaining, isolation levels and atomicity

texts:
I'd like to ensure that I understand correctly how the concepts of transaction chaining, transaction isolation level and atomicity relate to each other.

Here's my understanding:

transaction chaining (chained versus unchained transactions) is orthogonal to the four transaction isolation levels defined by the SQL standard. I.e. all eight (4×2) combinations are possible.
transaction chaining is simply the default setting for each database connection. It is not a constraint and it can easily be overridden. E.g. if transactions are chained by default then a number of statements will only be committed when a COMMIT is encountered. If that is not the desired behavior, simply intersperse more COMMIT statements. If, on the other hand, transactions are unchained by default then each statement is committed automatically. If that is not the desired behavior simply wrap a block of statements inside BEGIN / COMMIT.
unchained transactions is the same as auto-commit.
atomicity of individual statements is always maintained regardless of transaction chaining or isolation level.
atomicity of groups of statements depends on the transaction chaining mode and the BEGIN / COMMIT statements used. It is not affected by the transaction isolation level.


texts: Best index strategies for read-only table?

texts:
I have a table in SQL Server which has following characteristics:

will contain about 1.2M records.
first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each
the data will not be updated or deleted, just be queried
the number of concurrent query is high, each query return only one record
there's nothing to do with the ID (because of the records are not updated or deleted)
the key of queries is two field, PROVINCE_ID and CANDIDATE_NUMBER (unique across the table)

I'm thinking about creating clustered index on the ID column, and non-clustered index in (PROVINCE_ID,CANDIDATE_NUMBER). will it the best choice? Can you give me another suggestion? please explain why should I do that?

Thank you so much!


texts: What would I use a MATCH SIMPLE foreign key for?

texts:
What I understand:

In addition to valid references to the referenced table, a column with a foreign key is also allowed to hold a NULL value. The SQL standard defines several match modes for foreign keys, like MATCH SIMPLE and MATCH FULL. For multi-column foreign keys, MATCH SIMPLE allows a wrong (= not referencing) value in any of the columns as long as at least one column of the foreign key holds a NULL value.

Multi-column foreign keys are rare (because multi-column primary keys are), but I can imagine valid use cases. For example a translation of a blog post might have a primary key (post_id, language_id). Now something that references such a translation (for example which translation a user is currently editing) would have a multi-column foreign key.

What I don't understand:

Why would I ever use MATCH SIMPLE for my foreign key?

In above example it doesn't make sense to have a "currently editing" entry that only references by post_id and not by language_id.

What would be an example where it does make sense?


texts:
I need to make a decision about deployment and security of some SSRS reports and would like some advice on what the most appropriate method is.

We have 40+ sites that have access to information for only their site.  Each site has access to a number of reports, say Report A, Report B, etc.  For each site, the report name is Report A - Site 1, Report B - Site 1, etc.  For each report (Report A), there is a Master report (Report A - Master).  The Master report is the report itself - dataset and formatting - and includes a hidden parameter for site name.  Each sites' report links to this Master report and passes in the site name.  Thus if the report changes, the change is made in one place.

Please note that limiting the reports to site-specific information on the database is not possible.  Our data source uses a service account and all users access to the information is handled in the Web Portal.

Now it is time to grant users access to these reports.  Currently it is set up that all of the reports and the master reports are in a single folder.  Access is controlled on each report individually and users must have access to the Master report as well as their site reports.

I would strongly prefer to set up a folder for each site and control access that way.  Obviously that would make it easier as site-specific reports are added, and it's superusers who will be controlling access.  My concern is this will make deployment of reports incredibly difficult.  Because of the linked mechanism, all 40+ versions (plus Master) are in the same BIDS project - and it seems like a nightmare to deploy to 40 different locations for every change.

I would like to know if anyone else has had a similar challenge and found a good way to solve it.  I have played around with linked reports, but then the site report can't "find" the Master report, even if a linked copy is placed in the same folder.

EDIT: To clarify, and use the exact terminology, the Master report is a sub-report of each site report.


texts: Transfer rows into a new table, forcing to generate unique primary keys till its insertable

texts:
Lets say we have the following scenario...
+-----------------+-------------+---------------+
| Table           | PK          | Additional    |
+-----------------+-------------+---------------+
| playerinventory | id (int)    | amount, level |
+-----------------+-------------+---------------+
| newinventory    | id (BIGINT) | amount, level |
+-----------------+-------------+---------------+

We are in the middle of a architectural change of our game-database.
"amount, level" should get extracted from "playerinventory" and get inserted into "newinventory".
Limitations

"newinventory" has no auto-increment pk, therefore we need to
generate a unique long for each... we also can not add
"auto-increment" to the table
"newinventory" could possible
already contain those identities, so we need to check if the
generated id already exists and if so repeat the process for the
affected set of extracted columns till it was inserted
successfully.
We CANT simply insert each row from
"playerinventory" into "newinventory" with its current id... that's
simply not possible (if I would explain why this post would be much,
much longer), that's why we NEED to generate a new unique
ID

In nearly any programming language this process would look like this
for row in playerInventory
   id = generate new ID
   while(id in newInventory) id = generate new ID
   insert into newInventory( id, row.amount, row.level )

How can we achieve this in MySQL/SQL ?
The highest ID in "newinventory" is already 9223372036854775806 and therefore we cant increase anymore... we need to generate and test till it fits.


texts: What replica in Availability Group should System Databases be backed up

texts: Just wondering what is the best practice for backing up system databases in Availability Group i.e should it be backed up in Primary, secondary or in all replicas please. Thanks in advance


texts:
I am editing the configuration of running mongodb in an 'amazon ubuntu instance' (with more than 30 GB data), and I attached some volumes for data, log, and journal

I followed mongodb-ec2:

formatted the volumes
mounted /data , /log and  /journal
stop mongodb
edit /etc/mongodb.conf

mongodb.conf:

  dbpath=/data

copy all files from old dir to new mount volumes.
start mongodb
For testing I run a python script,

from pymongo import MongoClient
db = MongoClient().my_testdb

I got the error message,

  ymongo.errors.ConnectionFailure: could not connect to localhost:27017:
  [Errno 111] Connection refused.

So remove the lock from the /data/mongod.lock.

and run sudo mongod --repair
The following error I got.
..........

  ERROR: dbpath (/data/db/) does not exist

........

My question is, even though I configured dbpath=/data, why does it try to look for the path /data/db? How can I resolve it?


texts: Can and should relational database contain tables that don't have primary keys?

texts:
So this site says: "All tables in a relational database should have a primary key". Here it also says that every table should have a primary key.

But if I look here and here they say that tables that contain only foreign keys are okay.

So who is right? And which would be the correct answer under a university setting?


texts: I need to unzip some files. I can do it with xp_cmdshell using 7z (since it is free) but I need this procedure to be able to run on multiple machined and I cannot guarantee that all of them will have 7z. Can I include the installation of 7z into the SQL Server?


texts: Mysql master-master: single table out of sync

texts:
I have a master-master MySQL 5.7, everything works fine except for one error which is rising from time to time.

If I run a show slave status\G on server2 I have an error on a table:

...
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table my_database_name.my_table_name; Can't find record in 'my_table_name', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000120, end_log_pos 83145706
...

I can temporarily skip the error by running:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

or by setting

slave-skip-errors = 1032
skip-slave-start

in server2's my.cnf and then restarting MySQL.

However, I would like to fix it permanently so I checked in server1's binary logs and I found this:

### UPDATE `my_database_name`.`my_table_name`
### WHERE
###   @1=140
###   @2='2014:02:02'
###   @3=2878
###   @4=3253
###   @5=''
###   @6=0.00
###   @7=0
###   @8=35.75
###   @9=0.00
###   @10=0
###   @11=0
### SET
###   @1=140
###   @2='2014:02:02'
###   @3=2878
###   @4=3254
###   @5=''
###   @6=0.00
###   @7=0
###   @8=35.75
###   @9=0.00
###   @10=0
###   @11=0
# at 83145706

I see it tries to update the table by setting the value 3254 in the 4th column where the 4th column itself has value 3253. Anyway, if I look at the table on  the servers, it has a different value on both:

server1:

mysql> select * from my_table_name where my_table_name.id = 140;
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| id  | col2       | col3     | col4     | col5    | col6      | col7    | col8    | col9      | col10               | col11               |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| 140 | 2014-02-02 |     2878 |     3254 |         |      0.00 |       0 |   35.75 |      0.00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+

server2:

 mysql> select * from my_table_name where my_table_name.id = 140;
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| id  | col2       | col3     | col4     | col5    | col6      | col7    | col8    | col9      | col10               | col11               |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+
| 140 | 2014-02-02 |     2878 |     3257 |         |      0.00 |       0 |   35.75 |      0.00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+-----+------------+----------+----------+---------+-----------+---------+---------+-----------+---------------------+---------------------+

Considering it's master-master, how can I align the tables? I think I can't simply set the value 3253 hoping it's going to be updated by the binary log, can I?

If possible, I want to avoid re-syncing the whole database because it's really huge.

Thank you!


texts: Restoring from pg_dump with foreign key constraints

texts:
In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored.  An example:

ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL:  Key (channel_id)=(1) is not present in table "channels".

Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.

caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...

version: PostgreSQL 9.4.4.

How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?


texts: Do the showplan (any or all) events add more overhead than a tuning trace should have

texts: How long (order of magnitude) do the various showplan events take to record? Also is the time these events require counted as part of the "duration" column of the corresponding SQL query?