texts: #1064 - You have an error in your SQL syntax; check the manual for the right syntax to use near ''&lt)' at line 1

texts:
DATABASE SERVER
Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.56-cll-lve - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
WEB SERVER
cpsrvd 11.66.0.18
Database client version: libmysql - 5.1.73
PHP extension: mysqli, curl, mbstring
PHP version: 5.6.30
phpMyAdmin
Version information: 4.7.3
I am using phpMyAdmin to update the column text which type is text with the following query:
UPDATE `oc_product_tab` SET `text` = '&lt;p&gt;&lt;u&gt;&lt;strong&gt;Ελλάδα:&lt;/strong&gt;&lt;/u&gt;&lt;br /&gt;\r\nΗ αποστολή - αφού έχει επιβεβαιωθεί η παραγγελία σας - πραγματοποιείτε &lt;strong&gt;σε όλη την Ελλάδα&lt;/strong&gt; με ταχυμεταφορά courier και η παράδοση γίνετε σε 1-3 εργάσιμες ημέρες στη διεύθυνση που θα δηλώσετε.&lt;br /&gt;\r\nΤο κόστος των μεταφορικών είναι &lt;strong&gt;3 ευρώ&lt;/strong&gt; για παραγγελίες κάτω των 15 ευρώ.&lt;br /&gt;\r\n&lt;strong&gt;Για παραγγελίες άνω των 15 ευρώ η αποστολή είναι δωρεάν Πανελλαδικά.&lt;/strong&gt;&lt;br /&gt;\r\nΣτις περιπτώσεις όπου η πληρωμή γίνεται με αντικαταβολή η &lt;strong&gt;χρέωση αντικαταβολής&lt;/strong&gt; είναι &lt;strong&gt;2€&lt;/strong&gt; επιπλέον.&lt;br /&gt;\r\n&lt;u&gt;&lt;strong&gt;Εξωτερικό:&lt;/strong&gt;&lt;/u&gt;&lt;br /&gt;\r\nΣε όλες τις χώρες της Ευρωπαϊκής Ένωσης η &lt;strong&gt;χρέωση&lt;/strong&gt; αποστολής είναι &lt;strong&gt;12€&lt;/strong&gt; για παραγγελία βάρους έως 3 κιλών ανεξαρτήτου της ποσότητας παραγγελίας και φτάνει στην πόρτα σας μεταξύ 5 έως 8 εργάσιμων ημερών.&lt;br /&gt;\r\nΓια παραγγελίες άνω των &lt;strong&gt;150€ εντός Ευρωπαϊκής Ένωσης&lt;/strong&gt; τα έξοδα αποστολής είναι &lt;strong&gt;ΔΩΡΕΑΝ!&lt;/strong&gt;&lt;br /&gt;\r\nΣτις υπόλοιπες χώρες η αποστολή γίνεται μέσω των δημόσιων ταχυδρομείων. Η χρέωση αποστολής καθώς και οι ημέρες παράδοσης εξαρτώνται από τη ζώνη αποστολής. Θα σας ενημερώσουμε μέσω e-mail ή τηλεφωνικά αφού ολοκληρώσετε τη διαδικασία παραγγελίας σας και πριν σας αποσταλούν τα εμπορεύματα.&lt;/p&gt;\r\n' WHERE `oc_product_tab`.`tab_id` = 2 AND `oc_product_tab`.`language_id` = 2;

and the error is
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''&lt)' at line 1

I have changed the name of the column from text to mytext (because text is a reserved word for MySQL) and the error shows up again.
If I use this query:
UPDATE `oc_product_tab` SET `text` = '' WHERE `language_id` = 2 AND `tab_id` = 2

It works.
What am i doing wrong?


texts:
I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres.  I am using postgres 8.4 on MAC OS.

copy tname from '/Users/Shared/folder/file.cxv' DELIMITERS ',' CSV;

  ERROR: could not open file "/Users/Shared/folder/file.csv" for
  reading: Permission denied.

I checked and have reading/writing privileges for this file.


texts: Disable SQL Server Login pop up for other users

texts:
Does anyone know how do I get rid of below popping up on other users computer who will only be using the spreadsheet as read-only file?

The data in the spreadsheet comes from a SQL query, and has OLE DB refresh enabled when opening the file. I will need to send this spreadsheet out to internal as well to external people but I don't want them to have this pop up appear on their screen.
The spreadsheet is collecting and updating data every time its opened and hence it is required to remain connected. What are my other options? There are more than 15 reports set up in the spreadsheet which will be sent out once every week internally as well externally.
I’m using the below VBA script to refresh the spreadsheet. Is there any code I could use in the script that will save the workbook and remove the SQL connection?
Dim oExcel
Set oExcel = CreateObject("Excel.Application")

oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False

Set oWorkbook = oExcel.Workbooks.Open("file path.xlsx")
oWorkbook.RefreshAll
oWorkbook.Save

oExcel.Quit
Set oWorkbook = Nothing
Set oExcel = Nothing


texts: Performance difference between SQL Server Linked Server and direct query

texts:
A Management Studio query pane connected directly to a remote sql server executes and displays a simple table query of (SELECT * FROM table) in just under 2 seconds.

The same Management Studio but now from a locally connected query pane, with the same query executed over a locally defined Linked Server (SQLNCLI10) now needs 7 seconds.

Comparing the client stats, while the number of server roundtrips is 1 in both cases, I do see that "client processing" in the first is just 1794ms, while over the linked server it is 5741ms. "Wait time on server replies" over the linked server is also higher 967ms vs 187ms.

This is SQL Server 2008 R2 64 at both ends and on the same domain.

Can anyone explain this?


texts: Convert seconds into HH:MM:SS in Informix

texts:
How is it possible to convert a float number of seconds to HH:MM:SS in Informix. I have a column that has a run duration of 1449.448520410. I want to convert this to a human-readable format. There are many ways of going about this, but I am struggling to find a method of doing so.
I have identified that running the below gives close to what I want, but excludes the hours:
select b.run_duration, floor(run_duration / 60) || ':' || lpad(mod(run_duration, 60), 2, '0') as run_duration_time from ph_task a, ph_run b where a.tk_id = b.run_task_id order by run_duration DESC

output:
24:09

What I would like to see is:
00:24:09

How can I customize my SQL to provide that?


texts: What is the correct method to pass range of dates to a SQL Server 2016 temporal table query where you need a set returned for each day?

texts:
I have a SQL Server 2016 temporal table query that I’m having a hard time figuring out an efficient way to write.

Let’s say I have a temporal table called [Users] that each day has the total body of users in a group. Each day users can have a status of ('A', 'B', 'C', 'Y', 'Z')

If you wanted to select the current version of the table I would simply write

SELECT [UserID]
, [Status]  --where the user status would be listed as 'A', 'B', 'C', 'Y', or 'Z'
, [InsertDate]      --record creation date which isn't related to the System-versioning fields
, [SysStartTime]    --<start_date_time> which could be omitted from the simple query
, [SysEndTime]      --<end_date_time> which could be omitted from the simple query
FROM [Users]

If I wanted to look up how the table looked on 6am of October 2nd, 2017 I know could write:

SELECT [UserID]
, [Status]
, [InsertDate]
, [SysStartTime]
, [SysEndTime]
FROM [Users]
FOR SYSTEM_TIME AS OF '2017-10-02 06:00:00'

Easy enough and I could count the number of people with various statuses using a simple SUM function.

Where my problem begins is when I want to analyse trends over a longer period. In theory I want a picture of how the table looked on each day of that extended timespan. Like if I was looking at 20 contiguous days of data with the [Users] table having on average 5000 rows per a day I would hope that my query would return about 100000 records.

My initial thought was a to pass in a subquery to the 'FOR...AS OF' statement but SQL Server does not seem to like that. I have tried a bunch of things many of which look something like this:

SELECT [UserID]
, [Status]
, [InsertDate]
, [SysStartTime]
, [SysEndTime]
FROM [Users]
FOR SYSTEM_TIME AS OF (SELECT Fulldate FROM SetOfDates_Table)

Am I missing something obvious about how to pass a set of date values to 'AS OF'? Should I instead be using the 'SELECT..FROM..FOR..AS OF' as a function, pass a parameter to it then UNION the resulting data sets together?

Just for reference, I'm in a data warehousing environment where I'd want to schedule the query to happen in advance rather than needing it to run on demand.


texts: Left join on one or the other column without table-scanning

texts:
I have this huge sql with a lot of left joins. Mostly on the tables primary key, so no problem. On one table though, my key could exist in either one column or the other.
Syntax-wise that is no problem:

LEFT JOIN table2 blk ON (t.rid=blk.transact OR t.rid=blk.transactchild)

I have created two indices on table2, one for each of the keys, but my Sybase ASE 15.7 decides to table scan in stead. So I tried creating a single index with both of the keys and forcing it in the join, but that didn't help on performance. Showplan indicates, that the index is being used, but a scan takes forever. Is there a better way to join on either one or the other column?


texts: SQL Server: Determine backup location from physical_device_name field

texts:
I am working on a SQL Server database and ran the following command to figure out where the database is backing up to:
SELECT TOP 1 M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = 'MyDB'

The physical_device_name says: HTSQLServerMyDB
I don't see any maintenance plans or jobs that I can figure out that do this backup. I am trying to figure out where it is doing this backup. If I go into Server Objects then Backup Devices it is empty.
How can I determine where are my backups?
I am running SQL Server 2016 (13.0.4001.0).


texts:
I ran into an issue on trying to optimise the following two query into 1 single query.

Result 1:

select *
from sip_trunks
where resporg_account_id_id=62
and identifier = (select action
                  from sip_trunk_numbers
                  where number='xxxxxxxxxx'
                  and resporg_id=80 limit 1)
limit 1

Result 2:

select *
from sip_trunks
where resporg_account_id_id=62
and sip_trunks.default=true
limit 1

Final Output = Result 1 + Result 2

Thus far I have managed to achieve this.

Note: I can't perform a UNION since I'm not sure whether RESULT 1 would yield any result or not (hence the flag matcher is used in below query. other than checking the rows returned) I know that Result 2 would always be there.

select *,
        (case
           when identifier=(select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1)
             then 1
           when sip_trunks.default=true
             then 2
           else 3
         end) as matcher
from sip_trunks
where resporg_account_id_id=62
order by matcher asc
limit 2;

Now I'm stuck.

a: I can't group the result based on the matcher as Postgres complain the '*' is not used in the group.

But First I'm not sure how bad is the subquery is (select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1)

So does using a subquery as column would make it run for all the rows or it would run for rows for which the where conditions matched.

In other word does the subquery would run for all rows of sip_trunks or it would only run for rows for which the resporg_account_id_id=62 matches.


texts:
with a small table (about 2 M of rows, 5 classic columns, id, description, title, created_at, coordinates (point)) doing a WHERE created_at BETWEEN ... AND ... ORDER BY created_at LIMIT 50 is very very slow (about 15 seconds)

created_at is timestamp with index.

But when I use EXPLAIN, the column filtered shows 10, which is bad.
What could be the cause of this 10 ?

Query is simple, table is innodb, no partition

SELECT * FROM posts
WHERE created_at BETWEEN '2016-10-28 18:25:00'
                     AND '2016-11-02 18:25:00'
ORDER BY created_at DESC
LIMIT 50;

Explain plan

1   SIMPLE  posts   NULL    range   created_at  created_at  5   NULL    5317    10.00   Using index condition; Using where

Mysql 5.7 clustered version

(Added from comments)

CREATE TABLE posts (
    id int(11) unsigned NOT NULL,
    title varchar(120) NOT NULL DEFAULT '',
    description text NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status tinyint(4) NOT NULL DEFAULT '1',
    context tinyint(4) NOT NULL DEFAULT '0',
    coordinates point DEFAULT NULL,
    PRIMARY KEY (id),
    KEY created_at (created_at),
    KEY status (status),
    KEY context (context)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts: SQL Server Management Studio - Processor counters for different sessions do not add up

texts:
In SQL Server Management Studio, I have two sessions. The workload for each session is:

Session 1: while loop to select top items from one table;

Session 2: while loop to update statistics of another table using full scan.

Then I used Get-Counter to get the processor counters of each session for a minute:

Get-Counter -Counter "\Process(sqlservr)\% Processor Time" -SampleInterval 1 -MaxSamples 60

When running session 1 itself, the average processor counter in a minute is about 34.6. When running session 2 itself, the average processor counter in a minute is 229.8. But when running session 1 and 2 together, the average processor counter is only 91.7. I tried it several times, sometimes session 1 and 2 add up together would be similar to session 2 itself. However, I was expecting something like the processor counter of session 1+2 would be similar to the one of session 1 + the one of session 2.

Session 1 only has logical reads (no physical reads) so I guess it's not a matter of IO block. The whole CPU usage of session 1+2 is only like 12% so it's not because CPU is fully utilized. Could someone explain why? What should I do to make the CPU usage of running session 1+2 together similar to the sum of running each itself? Thanks!


texts: row-based replication with slave triggers

texts: I'm replicating a prod MySQL database to an analytics one.  I need to drop several tables with sensitive data from the analytics and also redact several columns of several remaining tables, replacing NULLable fields with NULLs.  How can I set up triggers on the slave to achieve this?


texts: SQL Server - creating a historical data backup

texts:
I've run out of space on my SQL Server 2008 Express DB.

i'm creating a "duplicate" SQL Server 2008 Express DB so i can backup my data before i purge my DB so i can start over.  Is there a way in SQL Server to view the DDL text file, so i can easily recreate the empty table before i push data into it?

Also, is there as "easy" way to bulk push data from one table into another once i've created the backup structure?

at first i tried deleting some old rows, but of course that just increases the log file.


texts: Copy values to another column - ORA-01481: invalid number format model

texts:
I want to copy values of col1 NUMBER(13,4) column to another newly added col1_temp VARCHAR2(50). To perform this action I build the following update query:

update table1 set col1_temp = to_char(col1, '99.99%');

Here is the current view of both columns:

 col1  |  col1_temp
-------------------
 13.9  |  null
 65.7  |  null
 21.63 |  null
 50.5  |  null
 10.8  |  null
 30.1  |  null

But running of the query gives me back always this error:

SQL Error: ORA-01481: invalid number format model

What I'm doing wrong?


texts: Discover who changed SQL Server service state

texts:
There's any way to find out who stopped\restarted\started the following services ?

SQL Server Database Engine
SQL Server Agent
SQL Server Analysis Services

Thanks !


texts: CASE statement with IS NULL and NOT NULL

texts:
Is there any better way to write the lines below in SQL Server 2005?

CASE
WHEN (ID IS NULL)
   THEN 'YES'
WHEN (ID IS NOT NULL)
   THEN 'NO'
END AS ID_Value,


texts: Is there a sql database engine that support "specific value duplicate restriction"?

texts:
For example, in the brand column of a product database, the brand value "unknown" should only exist once while other brand values can exist many times.

Is there a sql database engine that supports it? or should I do it in application layer?


texts:
I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch.

Currently when I run the script, it gets the proper list of sql files recursively, but does not rollback the changes if there is an error. If there are 10 files and 3 complete but there is an error on the 4th, the first 3 files do not rollback.

Here is my script:

BEGIN TRANSACTION

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

DECLARE @folderPath VARCHAR(100) = 'folder\path'
DECLARE @cmd VARCHAR(8000) = 'dir /b /s "' + @folderPath + '\*.sql"'

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell @cmd
GO

DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT
        [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]

DECLARE @folderPath         VARCHAR(100) = 'folder\path'
DECLARE @vFileName          VARCHAR(200)
DECLARE @vSQLStmt           VARCHAR(4000)

SELECT * FROM ##SQLFiles

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ServerName -U UserName -P "password" -d dbname -i "' + @folderPath + @vFileName + '"'''
    PRINT @vSQLStmt
    EXECUTE (@vSQLStmt)

    FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

ROLLBACK TRANSACTION

Is there any way to get the changes made by the files to be rolled back?


texts: Having two entities that are the same except for 1 distinct attribute

texts:
In an example scenario for a database design assignment, a company provides hotel rooms (split into two entities - Hotel and Room) and apartments. So the database will contain the following entities:

Hotel (hotelNo(PK), name, address, telephone)

Room (roomNo(PK), hotelNo(PK), dailyRentalRate)

Apartment (apartmentNo(PK), address, numberOfRooms, dailyRentalRate)

In the specifications the company wants the database to contain an inspection entity which stores the following data:

Date of inspection,
The condition of the room/apartment,
The staff member who did the    inspection.
The hotel and room number    and apartment numbers depending    on
whether the inspection was on a    hotel room or apartment.

My question is would it be better to split this up into two    entities    as    shown below or to have one    entity (also shown    below) which    would have    a null value for either    the hotel    and room number    (when the    inspection is for an    apartment),    or the apartment    number (when the    inspection is for a    hotel    room).

Two separate entities:

HotelInspection (hotelInspectionNo, date, condition, staffNo(FK), roomNo(FK), hotelNo(FK))

ApartmentInspection (apartmentInspectionNo, date, Condition, staffNo(FK), apartmentNo(FK))

One entity:

Inspection (inspectionNo, date, condition, staffNo(FK), roomNo(FK), hotelNo(FK), apartmentNo(FK))


texts: How to delete a procedure which name is ambigous?

texts:
I am using informix...

I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a

DROP PROCEDURE myProc;

then i get a error message

ERROR: Routine (add_adr_trigger_row) ambiguous - more than one
routine resolves to given signature.
Error Code: -9700

How can i drop the procedures?


texts: How to design a database with product-dependent columns?

texts:
I am very new to database design. I have a task to create an inventory program with  asp.net core and entity framework core.

The inventory program will be used to manage various kind of products. Products have some common fields (aka columns) such as:

ProductId
Contidion (new, 2nd hand)
Price
etc

But each product also has some specific fields. For example, a mother board has

MemorySlots (either 2, 4, etc)
MemoryType (DDR2, DDR3, etc)
CpuType (Amd, Intel)
etc

Another product, for example, is a book. It has:

ISBN
Authors
Title
etc

Question

How can I manage the product-specific fields in a database?


texts: Can large data deletion affect Innodb buffer pool performance?

texts:
At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of 10 to 25 .CSV files per day with a global/average size of 400MB.
We noticed that reading performance on data even with full using of index became too slow. We have about 122GB of daya with 60GB of RAM and I configured 48GB for the Innodb buffer pool (please check details in images).
Is the deletion affect buffer pool performance ? What is the best/fast way to resolve that issue ?
Thank you for your help.

**

(UPDATED)

**
It's not really a fragmentation issue, I added a 3rd image for more information.
(updated)
It is not indexes (the use is about 90% - 100%) but the bad way the app is using the db model made is too bad.


texts: Adding Full Text Search via command line

texts:
I'm trying to add the full-text search to sql server via command line since we got a core-server.

I already searched the web but all I get is adding it with GUI or re-installing the complete SQL-instance.
Anybody got any idea how to do? (Hope it's on-topic, sorry otherwise: I'm not a DBA, where should I ask?)


texts: Mp3 file directly in a Database Without using BLOB

texts:
I need to store Mp3 files in a Database.

Problem is that the files are going to be accesed with a smartphone. As the Smartphone is going to need to decode the  Blob it would be very slow.

I tried already to do this with a MariaDB but discovered that the data was stored as a Blob...

Im asking for a way to store the files in a Databank without changing the format.
If there is one Please enlighten me!


texts:
So lets say I have a table, MyBigTable that has student scores of an examination. So lets say the structure looks something like

id
name
school
board
exam_partA_score
exam_partB_score

Then I need to run some script on it at certain levels. So lets say I have to run report at the school level so I will have

select
 school,
 board,
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board

Then I need to run another script at the board level so

select
 school = null,
 board,
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.board

Then at the end I need to combine them all so something like

select * into BigReport
from First_Select Statement UNION Second_select_statement

Problem:
I have about 6 fields in the group by clause. They change depending on the type of report I need (school level, board level, state level).

I also have about 200 cnt_* fields.

Right now I have those "select" statements three times at the different levels which makes my script around 900 lines.

I dont see why I have to compute counts three times when the logic they use is the same all three times. I just need to change the headers (school, board, id..) and the group by clause. Not only that, if a spec changes for a count, or another count field is needed then I am going to have to change it in three different places which is very error prone.

What is the best and most efficient way of doing this.


texts: New Sybase User can't SELECT Tables From Other User

texts:
I am accessing a Sybase database and have created a new user using:

CREATE USER "ThisNewUser" IDENTIFIED BY "ThisNewPassword";

I then granted privleges:

GRANT ALL TO "ThisNewUser"

I used the output from this:

select 'grant all on ' + name + ' to "ThisNewUser";' from sysobjects where type = 'U' or type = 'P'
go

Looking at the results from this query, my new user has all permissions:

SELECT DISTINCT SU.name,
                SP.selectauth,
                SP.insertauth,
                SP.deleteauth,
                SP.updateauth,
                SP.updatecols,
                SP.alterauth
FROM SYSTABLEPERM SP
JOIN SYSUSERS SU on SU.uid = SP.grantee
ORDER BY SU.Name

When I search for all tables in the current database, I can see the tables I want:

select table_name from systable order by table_name

When I try doing a basic select query SELECT * FROM Customers, I get the error Table Customer not found

My queries work just fine if I log in as the root user, but not with my new user account. What am I missing here?

FYI, The server is running dbeng16.exe, but when I installed the Sybase Central 16 GUI to connect, I got the message that the DB version was version 10 and needed to be upgraded. I have no trouble accessing the old DB using the dbisqlc app, which has let me run all the commands on it, and I exported the DB to a new upgraded DB where I also tested, but neither situations work.


texts: MYSQL Stored procedure to fetch entire row from a table

texts: I am a beginner to SQL and currently practicing the concept of the stored procedure. I have a table Fetch_Marks that contains 20 records with Student_No and Marks as columns. Now I would like to create a stored procedure for this in which the input parameter will be Student_No and when we enter the input (Let's say I gave the Student_No as 1), it should fetch the row of that particular student from the table Fetch_Marks. Is there any workaround for this? If yes, please help.


texts: How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

texts:
Here is an example of the json object

rawJSON = [
   {"a":0, "b":7},
   {"a":1, "b":8},
   {"a":2, "b":9}
]

And I have a table that essentially looks like this.

demo Table

id | ...(other columns)      | rawJSON
------------------------------------
0  | ...(other columns info) | [{"a":0, "b":7},{"a":1, "b":8}, {"a":2, "b":9}]
1  | ...(other columns info) | [{"a":0, "b":17},{"a":11, "b":5}, {"a":12, "b":5}]

What I want is to return a row which insideRawJSON has value from "a" of less than 2 AND the value from "b" of less than 8. THEY MUST BE FROM THE SAME JSON OBJECT.

Essentially the query would similarly look like this

SELECT *
FROM demo
WHERE FOR ANY JSON OBJECT in rawJSON column -> "a" < 2 AND -> "b" < 8

And therefore it will return

id | ...(other columns)      | rawJSON
------------------------------------
0  | ...(other columns info) | [{"a":0, "b":7},{"a":1, "b":8}, {"a":2, "b":9}]

I have searched from several posts here but was not able to figure it out.
Extract JSON array of numbers from JSON array of objects
How to turn json array into postgres array?

I was thinking of creating a plgpsql function but wasn't able to figure out .

Any advice I would greatly appreciate it!

Thank you!!

I would also like to avoid CROSS JOIN LATERAL because it will slow down the performance.


texts: Getting count of all children and if any child contains user id in nested hierarchy

texts:
I am trying to create a newsfeed for posts and comments. The user should see:
 1. A latest post in liked group or by a followed user
 2. A post with the latest comment (n-th level) in liked group or latest comment by a followed user
 3. If the comment is on a comment, then n-the level comment, n-1 level comment and the post on which the comment was made, where the comment is in a liked group or by a followed user.
 4. Each post should come only once, in the feed

I am following the nested set model in MySQL for storing comments. The schema for posts and comments is like:

**Posts**
id  |  post_text  | user_id  |  group_id   |  created_date

**Comments**
id  |  post_id |  comment_text  | lft  |  rgt   |  created_date

For every post created, I insert a row with lft and rgt 0,0. For any comment, the rows after that have adjusted lft and rgt values. For example, a post with a 2 comments, with the first comment having 2 child comments will be like

**Posts**
id  |  post_text  | user_id  |  group_id  |  created_date
 1        text        1            1

**Comments**
id  |  post_id |  comment_text  | lft  |  rgt  | created_date
 1        1         comment         0      0
 2        1         comment         1      6
 3        1         comment         2      3
 4        1         comment         4      5
 5        1         comment         7      8

To get the feed, my query looks like:

SELECT * FROM (
    SELECT p.id, p.post_text, c.text
    FROM posts p
    INNER JOIN comments c
    ON p.id = c.post_id
    ORDER BY c.created_date DESC) as sub
GROUP BY sub.id

This query takes forever on my table with about 70K rows. In the feed, I also need to show the number of child each feed item row has and if the logged in user has posted a comment on a post/comment or not, i.e. for an item at n-th level, if there exists another item at n+1 level with the logged in user's id.

What is the best way to achieve this? Should I run multiple queries?


texts: How to remove Parameters and Filters in a T-SQL?

texts:
I created a sql trace and my task is build a report listing the 10's most used commmands.
My problem is: I have the same procedure 100 times but my problem is it is using different filter and parameters. I need to remove them for using count functions (pe, 100 occurrences of this procedure is, true of fact, just one for my report).

There is any program or TSQL code for this removal?

Thanks!


texts: Why MS SQL bigint type is implicitly mapped to float64 python type, and what is the best way to handle it?

texts:
Python integer type has unlimited precision so it is more than capable to hold a bigint value of MS SQL (64 bit). Still it is implicitly mapped to float64 python type, when passed to an external script.
This can cause serious calculation errors for large integers.
So why is it mapped to float64?
My guess is:
R was added before Python via the Extensibility architecture and it has fixed precision integers (32 bit). So it can't hold bigints. So perhaps this is a compatibility issue.
What is the best practice to ensure precise calculations?
Simple but working idea: pass bigints as string then parse them as int.
I know it has a slim chance to cause problem in practice, but still good to know.
How can it be a problem:
I wrote a simple example to demonstrate how can it be a problem:
CREATE TABLE #test (
    big_integer BIGINT
);

INSERT INTO #test
    (big_integer)
VALUES
    (36028797018963968),
    (36028797018963968 + 1);

EXECUTE sp_execute_external_script
    @language = N'Python',
    @input_data_1 = N'SELECT big_integer FROM #test',
    @script = N'
print(InputDataSet.dtypes)
OutputDataSet = InputDataSet
'

Executing this code on SQL Server 2019 will give you the result of:
| | (No column name)  |
|---------------------|
|1| 36028797018963970 |
|2| 36028797018963970 |

and because of the print(InputDataSet.dtypes) statement we can see the following message:
...
STDOUT message(s) from external script:
big_integer    float64
dtype: object
...

So we got a floating point rounding error. The value of this error for big enough integers is greater than 1, which is the root of this problem.
It is out of the scope of this question to teach floating point arithmetics, but I link some good materials if you don't understand what did happen:
Simple example - Stack Overflow.
Floating Point Numbers - Computerphile
The IEEE 754 Format - Oxford
I also share a small ipython sample if you want to experiment with this (which isn't a substitute of learning the theory behind this):
In [16]: import numpy as np

In [17]: a = 2**55

In [18]: a
Out[18]: 36028797018963968

In [19]: float(a) == float(a + 1)
Out[19]: True

In [20]: float(a)
Out[20]: 3.602879701896397e+16

In [21]: float(a + 1)
Out[21]: 3.602879701896397e+16

In [22]: np.nextafter(float(a), np.inf)
Out[22]: 3.6028797018963976e+16

Note
To run my example T-SQL some conditions must be met:

Machine Learning Services must be installed
external scripts must be enabled
Privilege to execute external scripts must be granted
You must have SQL Server 2017 CTP 2.0 or later


texts: What exactly happens during Database Mirroring? What about during a fail over?

texts:
So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?

Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.


texts: Instead of trigger Sqlserver and Inserted table

texts:
I have this trigger:
create TRIGGER Emp_log on Emp
Instead of insert as
SELECT * FROM INSERTED

Then i make this insert:
insert into emp
values(99,'bbb','aaa','99','1900-09-09',99,99)

As a result no line was inserted in the emp table, as it should be.
But anyway i got the display of the inserted row with the command SELECT * FROM INSERTED.

The question is: The "INSERTED" table contains what is written in the insert statement, or contains rows actually inserted?

Because I expected no insert, and then no display from SELECT * FROM INSERTED.

Thank You


texts:
I want to map Linux's root user to Postgres user, like if I enter psql in root user it should go the PostgreSQL cli without asking any authentication.

I tried to add an entry in pg_ident.conf and make the necessary changes in pg_hba.conf, but it didn't work.

psql: FATAL:  role "root" does not exist

Postgresql Version 9.6

pg_ident.conf
# MAPNAME       SYSTEM-USERNAME PG-USERNAME
root                 root            postgres

pg_hba.conf
# TYPE  DATABASE USER   ADDRESS         METHOD
local   all     all                     peer map=root
host    all     all     127.0.0.1/32    ident map=root

Any suggestions to achieve this?

I need to map multiple Linux users. The above one is for who all are using root user.


texts: Am fetching data from database i need to pass variable in my query.but am not getting how to pass variable in query

texts:
import MySQLdb as mdb
con=mdb.connect(host="localhost",user="root", passwd="yogi@123", db="wisdomjo_db2011")
cur=con.cursor()
cur.execute("SELECT suggestion,related FROM `auto_related` WHERE suggestion!='' AND type='S' GROUP BY suggestion")
numrows = cur.rowcount
for x in xrange(0,numrows):
    row = cur.fetchone()
    cur.execute("SELECT suggestion,LOWER(REPLACE(suggestion,' ','')) as suggest,related FROM `auto_related` WHERE LOWER(REPLACE(suggestion,' ',''))=%s  AND type='S' ORDER BY Id LIMIT 0,6",(row[0]))
    data=cur.fetchall()
    print data


texts:
I have two tables.

Table 1:

A   B
------
1   'A'
2   'B'
3   'C'

Table 2:

   A   C
----------
   2   15
   3   20

I'm using the following query:

SELECT table1.A, table1.B, table2.C
FROM table1
LEFT JOIN table2
ON table1.A = table2.A

The problem arises in the output which is:

    A   B   C
---------------
    1   'A'  null
    2   'B'  15
    3   'C'  20

Is there a way to get the null as 0, as I have to do an order by on table2.C. Any help is appreciated.

My expected output is:

    A   B   C
---------------
    3  'C'  20
    2  'B'  15
    1  'A'  0

ps: I tried with IFNULL(table2.C, 0). Didn't work.


texts: Can moving tables to another MySQL database help performance?

texts:
I want to know if I using different databases can help performance.

I have one big table support_chat with more than 5 million rows. I need to scan this all the time (3s with ajax) to check new messages on support.

If I move this table to another database in the same computer, can it help anything? (let users, sells, comments table faster)

For example:

db1

users table
sells table
comments table
support_chat table // move this table to db2

db2

support_chat table

They want to keep old things due legal reasons, to have a user support history.


texts: SQL Server 2012 SP_HelpUser loginid dbroles

texts:
I need to display the list of all the roles a login is associated with. The logins are SQL Server logins. I tried all three:

Execute sp_helpuser 'example'

and

SELECT
MP.name, RP.name
FROM
sys.database_role_members RM
JOIN sys.database_principals RP ON RM.role_principal_id = RP.principal_id
JOIN sys.database_principals MP ON RM.member_principal_id = MP.principal_id
where MP.name = 'example'

and

select
convert(char(45),sp.name) as srvLogin,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
 sys.server_principals as sp join
 sys.database_principals as dbp on sp.sid=dbp.sid join
 sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
 sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
 sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
 sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id
 where sp.name='example'

All of these gave me 10 roles out of 23 total roles.

Is there any other query that will list all the roles. I am not sure if there are any permission that needs to be set.

I ran this query as sysadmin and also as a user associated with one of the roles. All the users are member of Public role.


texts: Query Output Window not honoring fixed length

texts:
Query below has fixed length values for data set being returned in a string, now the problem is that fixed lengths are not being honored in the query results. But the output text file produced by DB mail looks correct. See examples below:

     SELECT
          LEFT(CONVERT(char(9), machine) + SPACE(9),9)
          + LEFT(CONVERT(char(30), part_no) + SPACE(30), 30)
          + LEFT(CONVERT(char(7), ordered) + SPACE(7), 7)
          + LEFT(CONVERT(char(7), picked)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), allocated)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), in_stock)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), sh_bin_qty)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), short)  + SPACE(7), 7)
          + min_bin

Raw Output Results

Text File Results


texts: SQL Server Agent unable to view network drives

texts:
I am unable to get agent jobs to output to a network path. I have pushed the IT guy to set up a domain authenticated user that logs in when the agent starts. That login does have access to the domain and is able to see the network drives. If I set the location of the output file to be the local c: then this works without issue. However if I set the drive to be a network location I get the following message;
[SQLSTATE 01000] (Message 0)Unable to open Step output file. The step succeeded.

Any help would be very much appreciated


texts: Where is the data stored in MySQL databases?

texts:
I don't know much about RDBMS but I am interested, I am working on a project which requires a database, I am planning to use MySQL to maintain that database.
I have installed MySQL on macOS and I have created a database which has one table in it.
now I have following questions in my mind :

1) Does turning on MySQL server turn my local computer into a server and stores databases in my computer when I create them using MySQL ?
If so can I locate the database ?

2)  Can I make a offline database using MySQL which can be located through 'Finder' ?


texts: PL/pgSQL function + client-side lo_import

texts:
I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this:

create function add_file(flag integer, sth varchar) returns void as
begin
   if flag = 1 then
      insert into tab_one values (my_file_oid, sth);
   else
      insert into tab_two values (my_file_oid, sth);
   end if;
end;

I'm using the psql command:

\lo_import('path/to/file');

Both the function and the lo_import command are in the same .sql file.

I cant change the lo_import() to an INSERT statement, becouse I need client-site lo_import. There is variable LASTOID, but it is not avaible in the add_file function, and it wouldnt get updated updating on every call add_file().

So, how can I insert a large object into the database with, in our example, 'flag' and 'sth' by insert statement and everything in function with arguments?

The file to be loaded is on the client computer.


texts: What will be the order of statement execution in this use case?

texts:
Say I execute select * from users at t1 which gets finised at t3. At t2(in between t1 and t3) update comes for some of the records in users table say
update users set address='xyz' where name = 'abc'. Will select statement acquire shared lock on all rows in advance or it will acquire for each row
independently when it starts to read that row ?

My understanding is whosoever(among select or update) acquires the shared or exclusive lock earlier it will execute it other will wait ?

I am using Mysql innodb .


texts: Is it possible to use MAX with a condition?

texts:
I have a jobs table that has two relevant fields date_executed and user_id. I was wondering if there's a way I can avoid joins or inline sql and just calculate both the last executed time, and the last executed time for a specific user in one simple query. I want to bee able to use MAX on everything, and a MAX on a subset of the results.

Example mock-up query. Line 3 has made-up syntax that's hopefully self-explanatory for what I want to do:

SELECT
    MAX(date_executed) as last_executed,
    MAX(date_executed, user_id = 'current-user-id') as last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field


texts:
I realise this may be come a subjective mess, but I'm hoping for some objective information on the subject. I also don't want to go shopping, I just want to know 'what to shop for'.

I'm very new to database management. As in I started SQL 4 weeks ago, and using MS Access last week. I'm coming to the end of out perrenial database project using MS Access wherein I've hit up against a lot of the limiations to Access, and I've mentioned that we need something different.

Next time round I'd like full access to the 'database capabilities' such as Union Corresponding, Intersect etc. I'd also like to be able to cursor through the rows if need be and be able to run checks row by row.

It's likely we'll have a handful of tables, but a few hundred thousand entries in the biggest table. I also need to be able to report the data. I also need to be able to import from Agresso.

One did catch my eye, which was the JPQL, in fact I could imagine the example on wikipedia fitting my needs, and I am able to code in Java. But I don't know if it fits the rest of my needs or if the project was handed over to someone not proficient in Java if it would be easily transferred. A GUI is essential.

I'm a little lost having never seen or even heard of RDBMS until last week, and now I need to decide what would work best going forward.

What RDBMS fits my needs to closest? How do I know what to pick?


texts:
I'm importing data into a MySQL table using LOAD DATA LOCAL INFILE.
I.ve done this on another table without difficulty, and I copied the stmt and just changed the table name and the fields. Here's the statement:

LOAD DATA LOCAL INFILE '/John/league_seasons.csv'
INTO TABLE lwl_league_players
IGNORE 1 LINES
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(id, location_id, game_id, league_season_id, player_id);

Here's the error:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(id, location_id, game_id,' at line 4

The FIELDS TERMINATED BY and LINES TERMINATED BY lines are exactly the same as my previously working statement. I've checked the structure and it's fine.

I'd appreciate someone pointing out where I'm screwing up.  Thanks


texts:
Due to the high cost of SQL Server Enterprise Edition which includes the Transparent Data Encryption feature I am looking for an alternative product and have only found a couple of options:

DbDefence
NetLib Encryptionizer

Could anyone provide details of their experience with either of the above products (performance impact, ease of use, etc)?

Any other alternatives to SQL Server TDE?

Note:  We are currently using SQL Server 2008 R2 Standard Edition.


texts: variable table and column names in dynamic SQL

texts:
I'm setting up a stored procedure to handle the reseeding "bug" present in SQL Server 2012. Note that as a business we have opted not to change the trace flag setting, as only a couple databases on this instance have applications that rely on sequential identity values and we'd rather not make a global change for just those DBs. I've been informed that it is too time consuming to ask developers to change their code and use programmed id values rather than relying on system generated ones, but I digress.

What I'm attempting to do is get a list of all tables and their respective columns that use Identity and the maximum identity value currently in that column, then, running through a loop, use DBCC CHECKIDENT to re-seed the table with the next highest value. The procedure will be set to run at instance startup after a failover or power failure.

At the end of all this I'd expect that the max_identity_value column would be updated, but they all remain null. In fact the @sql variable is null at the point where it is being executed. Can anybody tell me what I'm doing wrong?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO

declare @update_tracker int = 1, -- identify which row is being updated in the loop
        @total_rows int = 1, -- total rows in identity columns table
        @max_identity_id bigint = 0,
        @table_name nvarchar(max), -- table that has a column with IsIdentity=1
        @column_name nvarchar(max) -- column that has IsIdentity=1

declare @sql nvarchar(500)

set @sql = 'select max(' + @column_name + ') from [' + @table_name + ']'

DECLARE @IdentityColumns table
    (
    column_identifier int identity, -- Oh the irony of using identity in a script that fixes a "bug" in the identity columns of other tables
    table_name varchar(max),
    column_name varchar(max),
    max_identity_value bigint,
    is_identity bit
    )

insert into @IdentityColumns
select I.table_name, I.column_name, NULL, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
from INFORMATION_SCHEMA.COLUMNS I
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

set @total_rows = (select max(column_identifier) from @IdentityColumns)
set @table_name = (select table_name from @IdentityColumns where column_identifier = @update_tracker)
set @column_name = (select column_name from @IdentityColumns where column_identifier = @update_tracker)

--select @sql

while @update_tracker <= @total_rows
begin

exec (@sql)

set @update_tracker = @update_tracker + 1
set @table_name = (select table_name from @IdentityColumns where column_identifier = @update_tracker)
set @column_name = (select column_name from @IdentityColumns where column_identifier = @update_tracker)

end

select * from @IdentityColumns


texts: Aggregation vs Cursor For Reshaping MongoDB Documents

texts:
I'm looking to reshape the Documents in one of my collections, and have found two ways to do it, but need guidance.  For simplicity, say I have a collection, "myColl", and I need to reshape Documents that look like this:

{
   x:"foo",
   y:"bar"
}

To:

{
   nest: {
       x: "foo",
       y: "bar"
   }
}

This can be accomplished by using the aggregation framework to reshape the documents, and then rewrite the entire collection. When run against a test collection of about 150K records, the following takes roughly 5 seconds:

db.myColl.aggregate([{$project: {_id:"$id", nest: {x:$x, y:$y}}}, {$out:"myColl"}]);

If I try to do this using a cursor, it takes about a 1.5 minutes:

db.myColl.find().snapshot().forEach(
        function(elem) {
            db.myColl.update(
                {_id: elem._id},
                {$set: {nest: {x: elem.x, y: elem.y}}}
            );
        }
);

I'm leaning towards the aggregation approach for performance reasons; however,  someone mentioned here that it is creating a "new" collection with somewhat of a negative connotation, but it's not entirely apparent as to why.  Are there causes of concern that I should be aware of other than the Type safety mentioned in that comment?

Also, if the cursor approach is better, then how might I speed up the execution?  Setting the "w" param of WriteConcern to 0 doesn't do anything in my test because everything is hosted on the same box so skipping the acknowledgement doesn't save me any time, and is orthogonal to the fact that aggregation is executing order of magnitudes faster.

Thanks for the input!


texts:
I have a query that looks like below:

UPDATE some_table
SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END,
    column_y = CASE WHEN y_specific_condition THEN new_value_for_y ELSE column_y END,
WHERE some_more_conditions

Problem with above is, each column (x, y) still gets updated with their own value if some_more_conditions return true irrespective of their specific conditions returning true. I tried removing ELSE from above, but no luck

I am combining as some_more_conditions are same for both cases, I think its better to perform all in 1 update (suggestions welcome)

Do you know if there is a way that can I can perform the above update in 1 query by skipping the individual columns where the specific conditions do not match (basically avoid overwriting of same values)


texts: AlwaysOn Primary Showing as Question Mark

texts:
If I look in the dashboard for my AlwaysOn implementation I can see my groups are synchronised and no data loss.  They also failover successfully but when looking in the Group folder list under replicas on the secondary, the primary node is showing as having a question mark next to it.

Has anyone seen that before?


texts: Why use explicit cursors instead of regular loops?

texts:
I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:

for i in (select * from STUDENTS) loop
      htp.prn(i.student_last_name || ', ' || i.student_first_name || ' ' || i.student_dob);
end loop;

But, cursors seem to be the 'right' way to do things. I can find lots of information on what cursors are and different ways to loop through them, but I can't find a solid reason why to use them over regular FOR loops.
Is it dependent on the needs of the procedure? Are there inherent advantages I should be aware of?


texts: Can you use BigQuery to run on top of Bigtable

texts: I need to run BigQuery on top of Bigtable live, not as an export. I have found the information stating it was in beta but only as an export function. I would like to run BigQuery against Bigtable data without exporting. Has anyone done this or is it possible? The importance is that running it realtime no data is lost by doing a point in time export.


texts: Role-Schema Permission issue (Management studio)

texts:
When designing a new database I organized the table under different schemas. I've also created a new role that should have select/insert/update/delete rights on one schema and only select on another one. So, using management studio, I went in the first schema properties window, under the permission section I added that role and granted it select/insert/update/delete rights.

Problem is, the next time I went in schema properties window, I did not see the role and the rights I had just assigned. Did it worked? How can I verify?


texts:
I have the following query, which fetches data from only one table.

It is a query which is supposed to return data for an autocomplete function.

Autocomplete data can be in text1 or text2.
The exact matches should be on top. int3 is an integer weight value, where the order of results are based on this. First two queries are here to identify exact matches.
The next two queries are here for the identify near matches. WHERE text1 > 'foo' AND text1 < 'fop' phrase is actually equal to WHERE text1 LIKE 'foo%'. I wrote it like this to benefit from index.

Hope this helps.

SELECT DISTINCT text1 as Key
    , 'text1' as Source
    , int1 as Count
    , 1000 as int3
FROM mytable
WHERE text1 = 'foo'
UNION
SELECT DISTINCT text2 as Key
    , 'text2' as Source
    , int2 as Count
    , 1000 as int3
FROM mytable
WHERE text2 = 'foo'
UNION
SELECT text1 as Key
    , 'text1' as Source
    , int1 as Count
    , MAX(int3) as int3
FROM mytable
WHERE text1 > 'foo'
    AND text1 < 'fop'
    AND Count < 4
GROUP BY Key
UNION
SELECT text2 as Key
    , 'text2' as Source
    , int2 as Count
    , MAX(int3) as int3
FROM mytable
WHERE text2 > 'foo'
    AND text2 < 'fop'
    AND Count < 4
GROUP BY Key
ORDER BY int3 DESC, Count, Key LIMIT 0, 15;

The table structure is:

CREATE TABLE mytable
(
    text1 TEXT
    , text2 TEXT
    , int1 NUMERIC
    , int2 NUMERIC
    , int3 NUMERIC
);

It works fine, but I need to fine tune the performance. I tried different indexing options and checked the performance results with the built-in timer.

The best indexing options I discovered are :

CREATE INDEX cmp1 ON mytable (text1 ASC, int1 ASC, int3 DESC);
CREATE INDEX cmp2 ON mytable (text2 ASC, int2 ASC, int3 DESC);

I will be glad if you could show me any better indexing option or a better performing SQL query.


texts: MySQL : How to fill in empty data with custom text

texts:
I have the following data :
SELECT
   city,
   region,
   postcode
FROM
   AREA
WHERE
   city IN ( 'Jakarta', 'Jabodetabek' )

with the result :

I want the result like this

I've tried several tutorials, including here
https://stackoverflow.com/questions/17832906/how-to-check-if-field-is-null-or-empty-in-mysql
but the result is the same as the first image


texts: SQL Server DB in suspect mode after installing SQL Server 2008 R2 SP3

texts:
One of our production DBs is stuck in Suspect mode since we installed SQL Server 2008 R2 SP3.

Earlier today we found that most text fields (nvarchar) had the following data appended to it

</title>
cialis online
</title><div class=ali7

Every time I tried running queries to remove that bit, the SQL Server Service and Agent Service keep constantly restarting.

I tried Installing SP2 & SP3 but still having the same issue.

After installing SP3 and rebooted, I was able to run a few queries then got an error message about Shared memory and now the DB I was using is in Suspect mode.

Now I can't bring the DB back online.

I've tried following this blog post Creating, detaching, re-attaching, and fixing a SUSPECT database.
But when I try to run this

ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

I get this error

Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 7929, Level 16, State 1, Line 1
Check statement aborted.  Database contains deferred transactions.

If I run this
    SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status';

It says the DB is online.

If I try to restore from the latest full backup, as soon as I select from Database or From Device the list of backup sets to restore is blank.

Then keep getting "You must select a restore source".

UPDATE

I was able to restore to a second db using T-SQL, but the main db is still in suspect mode and can't detach or take offline.

The other issue now is that it uses file groups to store old data related to two tables.

How can I remove the suspect db and make the new db point to the file groups? Or so I need to copy the file groups?


texts: MySQL8 Binary installation - getting error msg "-bash: bin/mysql: Permission denied" after upgrading to 8.0.22

texts:
We have a MySQL 8 instance with Binary Installation. After upgrading to 8.0.22 (from 8.0.19) can't seem to connect using following cmd in CLI
bin/mysql -uusername -p

error msg -
-bash: bin/mysql: Permission denied


texts: Purpose of Nonclustered indexes in SQL Server 2012

texts:
(New to SQL Server)
I am struggling to understand purpose of Nonclustered indexes in SQL Server. Read though MS documentation (both for v2012 and v2008 that has pretty pictures) and some online resources, but still have a lot of unanswered questions.

Does noncl. index maintains sorted B-tree ? As far as I understood, it does - http://technet.microsoft.com/en-us/library/aa933130%28v=sql.80%29.aspx, otherwise analogy of book index would not make any sense.
Why on earth you want noncl. index to be defined on table or view with a clustered index or on a heap ? Heap has no order, so defining noncl. index on heap makes some sense - you can quickly find exact matches. However, table with clustered index is already organized data, and defining noncl. index will only speed up exact searches.


texts:
Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a specific table in a specific database.
This job needs to be scheduled and ran on a weekly basis due to large data load and the recent performance issues we have been facing.
Unfortunately with the maintenance plan in 2014, I am unable to set the % limit of when the index should be rebuilt or reorganized. I have tried to test our Ola Hallengren script, but that works best with 2016 or 2017.
I have a script that detects the fragmentation level:
SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
 DDIPS.page_count
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
 INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
 INNER JOIN sys.schemas S on T.schema_id = S.schema_id
 INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
 AND DDIPS.index_id = I.index_id
 WHERE DDIPS.database_id = DB_ID()
 and I.name is not null
 AND DDIPS.avg_fragmentation_in_percent > 0
 ORDER BY DDIPS.avg_fragmentation_in_percent desc

Now all I need to do is to combine the rest of the requirement:
-if avg_fragmentation_in_percent > 5 AND < 30 , then ALTER INDEX REORGANIZE

-if avg_fragmentation_in_percent > 30, then ALTER INDEX REBUILD

Can someone please help me with the query? Thanks in advance.
**Edited/ Update:
When I execute this query:
SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
 INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
 INNER JOIN sys.schemas S on T.schema_id = S.schema_id
 INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
 AND DDIPS.index_id = I.index_id
 WHERE DDIPS.database_id = DB_ID()
 and I.name is not null
 and T.name= 'WSSSync_Partition25'
 ORDER BY DDIPS.avg_fragmentation_in_percent desc

This is my output (Take a note of the 99% fragementation) :

Then I run Ola's script:
EXECUTE dbo.IndexOptimize
@Databases = 'WSS_UsageApplication',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'WSS_UsageApplication.dbo.WSSSync_Partition25'

The script runs perfectly, however when I re run the first query to find the  fragmentation level. The fragmentation remains the same at 99.6%.


texts:
I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint:

How does the query processor execute COMPUTE BY and how can I obtain an accurate execution plan?

Here is some T-SQL to play with:

 create table employee(
    ID          int,
    name        nvarchar (10),
    salary      int,
    start_date  datetime,
    city        nvarchar (10),
    region      char (1))
GO
insert into employee (ID, name,    salary, start_date, city,       region)
              values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')

insert into employee (ID, name,    salary, start_date, city,       region)
              values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')

insert into employee (ID, name,    salary, start_date, city,       region)
              values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (6,  'James', 70060,  '09/06/99', 'Toronto',  'N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (7,  'Alison',90620,  '08/07/00', 'New York', 'W')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
GO

select * from employee
GO
/*
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
*/

-- COMPUTE BY
SELECT salary, Name
FROM Employee
ORDER BY ID, Name
COMPUTE SUM(Salary)
GO

/*
salary      Name
----------- ----------
      40420 Jason
      14420 Robert
      24020 Celia
      40620 Linda
      80026 David
      70060 James
      90620 Alison
      26020 Chris
      60020 Mary
sum
===========
     446226
*/

GO

drop table employee

(Source)

The results from this single SELECT are:


texts: Mysql Sharding. Application Layer vs Mysql Layer

texts:
I am going to create shards in mysql table and I am stuck at a dileman whether to go for application layer sharding or let MySQL handle it.
I mean, wether I should be figuring  out which shard to go and query at application layer or pass on the query to the MySQL driver and let it decide.

I read about some pros and cons of both here but I am unable to come up with a conclusion.

My personal opinion is to go for the MySQL driver handling it. Open for discussion on this.

Please share your views/experiences.


texts:
I am an amateur.

I am trying the following

select * from [dbo].[AccessFeeDetail]
where LastLoginDate >= '10/15/2012 00:00:00.000', jobname not like 'Uptest_%'

I am making a mistake on where clause. Can you correct me

"LastLoginDate" and "JobName" are Fields in the same table.

Thanks!


texts: When I connect to DB, always connects as tempdb

texts:
I am trying to run a sybase DB query from a jsp session.

I can connect to the DB fine, but when I run my query, by default it always runs against tempdb.

Is there a way I can make a connection to a different DB and not by default connect to tempdb?

The aim is to be able to run a query for any table chosen by the user from a drop down list. I have the dropdown list working and can successfully select any DB. I know this is working as I am storing the DB selected in a variable and can confirm the name selected is always the name I can see in the variable.


texts: SQL Server : How to make "Update Statistics" faster?

texts:
We have a table with a 1 column Primary Key on and
                      an UNIQUE Non-Clustered Index with 16 columns.

This table has more than 20695780 Rows and on every day new records inserted between 30000 and 35000.

After insertion process, we have many other processes those refer this table.

On every day, before proceeding further, after insert we do an Update Statistics with 30 Percent Sample on this table.

After this when we do rest processes then we are not facing problem and everything goes smoothly.

Now the problem is if we do Update Statistics then it is taking more than 15 Minutes.

We need a solution: -
    To make this "Update Statistics" faster OR
    Can we perform a parallel Update Stats on this table with other processes OR
    Instead of "Update Statistics" can we go for REBUILD INDEXES (need to know it will update all Statistics or not)? OR
    Any other solution by which we can save this 15 minutes.

Please advice.


texts: Hi is it possible to do a differential backup in ssms 2014, so that it gets all changes since a specified date?


texts: Why does requesting the "estimated execution plan" for sp_delete_job throw an error?

texts:
I'm attempting to look at the estimated execution plan for the following T-SQL statement:
EXEC msdb.dbo.sp_delete_job @job_id = '3A015189-F4EB-439B-9CA0-27AFB74719D8'
    , @originating_server = '(LOCAL)'
    , @delete_history = 0
    , @delete_unused_schedule = 0;

When I do that, SQL Server throws the following error:

Msg 213, Level 16, State 1, Procedure sp_delete_all_msx_jobs, Line 25
Column name or number of supplied values does not match table definition.

The definition of sp_delete_all_msx_jobs contains the following pertinent lines starting at line 25 (wrapped for readability):
INSERT INTO #temp_jobs_to_delete
SELECT sjv.job_id,
    CASE sjs.server_id WHEN 0 THEN 1 ELSE 0 END,
    sjv.owner_sid
FROM msdb.dbo.sysjobs_view sjv
    LEFT OUTER JOIN msdb.dbo.sysjobservers sjs
        ON (sjv.job_id = sjs.job_id)
WHERE (ISNULL(sjs.server_id, 0) = 0)
    AND (sjv.originating_server = @msx_server)

Line 14 of that stored proc shows the definition of #temp_jobs_to_delete is:
CREATE TABLE #temp_jobs_to_delete
(
    job_id UNIQUEIDENTIFIER NOT NULL
    , job_is_cached INT NOT NULL
    , owner_sid VARBINARY(85) NOT NULL
)

That looks valid to me.  Why is "Display Estimated Execution Plan" returning an error?
I can run this on SQL Server 2008R2, and 2012; both return the error.  I'm using SSMS 2012.


texts: mongodump sharded collection size is smaller after restoring

texts:
I need to change shard index from range to hashed in a sharded collection. So I need to mongodump and restore the collection. I have 3 shards with 3 replica sets.MongoDB version is:  3.2.11
I started mongodump from one of mongoses.

mongodump --collection Collname --db dbname

I have "count" : 12925651, "size" : 21233976913 in main collection.

But mongodump is done at %75 with

[##################......]  dbname.Collname  9700124/12925651  (75.0%)  done dumping dbname.Collname (9700124 documents)

I restore the dump to another db and collection size is smaller than the main collection.

It seems that mongodump doesn't take data from one of the shards. Shard distribution is :

Totals
 data : 19.77GiB docs : 12925651 chunks : 475
 Shard shard01 contains 24.98% data, 24.98% docs in cluster, avg obj size on shard : 1KiB
 Shard shard02 contains 50.03% data, 50.03% docs in cluster, avg obj size on shard : 1KiB
 Shard shard03 contains 24.98% data, 24.98% docs in cluster, avg obj size on shard : 1KiB

What is the problem? And what can i do for replacing the shard index?


texts: Increased performance from single statement, multiple row inserts?

texts:
I'm currently obsessing over bulk inserts via prepared statements with libpqxx.

I am far from a database expert and even further when it comes to Postgres, but just thinking superficially, it doesn't seem like it would make a difference.

In my MySQL days, it made a huge difference, but it strikes me that MySQL's clustered index was the bottleneck, that it would be better to prepare it for many rows at once rather than one at a time.

Since Postgres doesn't do that, happily I might add, is it correct that there would be a near negligible performance boost to bulk inserts?  Please explain why or why not.


texts: Parameter sniffing = On with Parameterization = forced. Which takes precedence?

texts:
These two settings seem to contradict each other. One forces plan parameterization so that only one plan gets created. The other allows for multiple plans

If you have Parameterization = forced, should parameter sniffing be false, or does one take precedence?

Additional details

Regarding the comments below, parameter sniffing is a database scoped option as of SS 2016. (was a surprise to me too).  See also: SQLShack: SQL Server 2016 Parameter Sniffing


texts: Relationship design for areas, managers and general manager

texts:
Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also an employee as the boss of all areas. My question is about how I should indicate which employee is the boss of all areas.
    | employee_id |    name    |

    |      1      |    Mark    |

    |      2      |    Carol   |

    |      3      |    Adam    |

    |      4      |    Andy    |

    | area_id |    area_name    | manager |

    |    1    |    Chemistry    |    2    |

    |    2    |    Electronics  |    4    |

    |    3    |    Medicine     |    3    |

    |    4    |      ALL        |    1    |

At the moment what has occurred to me is to create an occurrence called "ALL" within the relation "AREA" to indicate the head of all areas, however I am not sure if it is the best way or the correct way to do it . Any other idea?


texts: Performance considerations for using MySQL table as a queue?

texts:
I'm working on a system which reports certain events to an external service.
Whenever a relevant change is made in the database, we want to guarantee that at least one message is sent to the external service.

I'm thinking this could be implemented with a message_queue table as follows:

BEGIN a transaction
Make the relevant database change
Insert a message about the change into the message_queue table, including a UUID
COMMIT the transaction
Later (eg on a cron job), iterate through the message_queue table, attempting to send each message to the external service. Whenever we get a successful response, delete that row. When we don't, try again later. If the external service actually received that message the first time but the reply was lost, it can discard it based on the duplicate UUID.

Assuming we can send and delete message_queue rows faster than they are created, this table would tend to have very few rows at any moment.

Given this usage pattern of frequent inserts and deletes, I think we would not want to index the table.
Is that correct? What else could we do to minimize the impact on overall database performance?


texts: Transactional replication with updates on the subscriber?

texts:
Summary

We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.

Current solution

We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2.
Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k.
Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i,    x,y,z

db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload).
db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload).
db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).

We have setup a Transactional Replication (db1_to_db2) with the following (important) options
for the publication:
- @repl_freq = N'continuous'
- @immediate_sync = N'true'
- @replicate_ddl = 1

for the published articles:
- @schema_option = 0x000001410DC37EDF

As there is little maintenance window available for both databases, with the described setup we can:
1. replicate schema changes to the subscriber
2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription

This is working like a charm.
But now, a new requirement has come up.

The Problem

Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1.
Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.

Thoughts

We have tried every possible (simple and easily adopted) solution we could think of:

Setup merge replication instead of transactional.

It does not accept partial snapshots for newly added articles.
It adds an extra column which means major changes to the application app1.

FAIL
Transactional replication with updatable subscriber

It does not accept partial snapshots for newly added articles.
It adds an extra column which means major changes to the application app1.

FAIL
Update db1.b through a (ON INSERT) trigger on db2.b:

This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
There is no easy way to explude these Updates from being replicated back to db2.b through the replication.

FAIL
Setup a transactional replication from db2 to db1 with table b as the only article.

Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...

FAIL

This is as far as we have gone in search of a solution.

Please help

Please state any idea you might have, taking into account our specific needs.
Forgive my being too analytic but I wanted to give every piece of required information you might need.


texts: Is my ER Diagram correct? Why use relationships?

texts:
This is the first time I've made an Entity Relationship Diagram (ERD) for database that will be built on MySQL. The business domain of relevance is about equipment issues management.

Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and do the linking in my PHP/MySQL code.

Is there a reason why I should create these relationships?

I have read a lot of info on database design and relationships.

I want to be sure the ERD I've created is correct; did I make mistakes? Or are there better ways to do this?

The ERD under consideration

What my database should do:

I have a list of users (users) that can create tickets (issue_tickets).
They will give a description of the problem, maybe add some files (issue_files).
Then other users (issue_watchers) will be attached to solve the problem.
All the users (users) can comment on a ticket (issue_tickets).
A ticket (issue_tickets) only has one status (issue_status) at a time.
A ticket (issue_tickets) only has one category (issue_category).
A ticket (issue_tickets) can have many comments (issue_comments) and many watchers (issue_watchers)
A ticket (issue_tickets) only has one user (users) who created the ticket.
A ticket (issue_tickets) only applies to one equipment (equipment).
A user can create many tickets
An equipment can have many tickets


texts: cross self join to get more rows - better solution?

texts:
I have a table employees:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| persons_id     | int(10) unsigned |
| restaurants_id | int(10) unsigned |
+----------------+------------------+

and a table bills:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| customers_id   | int(10) unsigned |
| employees_id   | int(10) unsigned |
| restaurants_id | int(10) unsigned |
| date           | date             |
+----------------+------------------+

I want to insert 10,000 rows with an employees_id, restaurant_id and a date into the table bills by using cross joins. The table employees is filled with 15 rows (employees).

Currently I do following:

insert into bills (employees_id, restaurants_id, date) select e.id, e.restaurants_id, '2012-01-01' from employees e cross join employees e2 cross join employees e3 cross join employees e4 order by RAND() limit 10000;

With this query I get 50,625 rows (without to limit to 10,000) (15rows*15rows*15rows*15rows=50,625rows). Is there an more elegant solution to get 10,000 rows? So I don't have to write X times cross join employees eX.

I'm using MySQL. Thanks.


texts: How to unify aggregating function with Postgres per different group by

texts:
my_data table is like this (one row example):

 total_cost | total_exp | ind1 | ind2 | ind3 | flag
------------+-----------+------+------+------+------
 1000       | 2000      | A    | A1   | A2   | F

And I have a function like this:

CREATE OR REPLACE FUNCTION create_my_aggregation() RETURNS VOID AS $$
        DECLARE
            import_date date;
        BEGIN
            FOR import_date IN
            SELECT generate_series('2005-01-01'::date, '2015-04-01'::date, '1 month'::Interval)
            LOOP
                insert into my_timeseries

                       select
--                       ind1 as ind,
--                       ind2 as ind,
                       ind3 as ind,
                       import_date,

                       -- size

                       array_agg(total_cost) filter(where flag = 'F' and total_exp > 0) as f_cost_a,
                       array_agg(total_cost) filter(where flag = 'S' and total_exp > 50) as s_cost_a,
                       array_agg(total_cost) filter(where flag = 'D' and total_exp > 100) as d_cost_a,
                       array_agg(total_cost) filter(where flag = 'T' and total_exp > 20) as t_cost_a,

                       from my_data
                       where date_trunc('month', date_of_transfer) = date_trunc('month', import_date)
--                       group by ind1, import_date;
--                       group by ind2, import_date;
                       group by ind3, import_date;
            END LOOP;
        END
$$ LANGUAGE plpgsql;

The expected outcome is an array aggregation with the ind1,ind2,ind3 renamed to ind and the relative aggregations.

The problem with that functions is that I have to switch (and run 3 times) select ind1 as ind....group by ind1,import_date.

Is there a way to unify it in one go?


texts: Create Symmetric key error in column level encryption in SQL Server 2016

texts:
I'm trying to implement column level encryption for a sample database using the below listed scripts.

USE MyDB
--Step1
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '117r$$#1946kcj$n44nhdlj'

-- Step 2
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MyColumnLevelEncryptionCertificate;
GO

On executing the step 2 scripts, below error is being displayed.

Cannot find the certificate 'MyColumnLevelEncryptionCertificate', because it does not exist or you do not have permission.

I'm able to see the certificate under Security->Certificates section and the sql user being used is sysadmin.

After researching the online forums, I tried to execute the below scripts but still the same error is being displayed.

GRANT CONTROL ON CERTIFICATE::MyColumnLevelEncryptionCertificate TO [sql64]
GRANT VIEW DEFINITION ON CERTIFICATE::MyColumnLevelEncryptionCertificate TO [sql64]

Can anyone please suggest what I'm missing or what's wrong?


texts: i want to modify Redgate source control Git.xml file to include commit and push

texts:
Below is an excerpt for the git.xml file in redgates source control

<element>
<key type="string">Commit</key>
<value version="1" type="GenericHookCommand">
<CommandLine>git commit -F "($MessageFile)" -o "($ScriptsFolder)\"</CommandLine>
<Verify>exitCode == 0</Verify>
</value>
</element>

i wish to modify it to include the push so when you do a commit in management studio it will also the push to git.

i have tried adding another command like

<CommandLine>git commit -F "($MessageFile)" -o "($ScriptsFolder)\"</CommandLine>
<commandline>git push</commandline>

and also tried

<CommandLine>git commit -F "($MessageFile)" -o "($ScriptsFolder)\" && git push</CommandLine>

but neither work, any idea on how to get the commit and push working


texts:
I'd like to run a CHECK TABLE on all my tables from the MySQL command-line.

I can do it for a single table:

mysql> check table Restaurant;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| foodbox.Restaurant | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.02 sec)

Is there an equivalent command that would check all tables in the current database?


texts: How to change default security on SQL Server database so that everyone can use it?

texts:
I have a database hosted on my machine that I want co-workers to be able to access and edit through a program I created.

My current connection string is:

Server=MyMachine;Database=MyDatabase;Integrated Security=True;

And to let another user access it I have to:

Add a user under database security in SQL Server Management Studio
Add a user under server security in SSMS
Run the script:

USE Database
    GRANT SELECT, INSERT, UPDATE, DELETE TO [Username]
GO

How can I configure it so that everyone on my work network (or everyone whose windows username starts with "MyCompany\") can have these privileges by default? That way I don't have to manually add every new person.


texts: Log Shipping back up location to a network share instead of clustered file share

texts:
Below is my current cluster set up

Node 1 - Active
Node 2 - Passive

While doing Log SHipping can I save the back ups to a network share instead of a clustered file share?

The service account will have read/write permission on the network share.


texts: Is the syntax of Oracle 10g and MySQL the same?

texts: Is the syntax of Oracle10g and mysql are the same? For example the select query? Thanks, I'm using MySQL and want to try Oracle 10g for my java.


texts: Moving MongoDB shards from one server to another

texts:
I have a new server on which i have to copy the old version of the MongoDB already configured. On the old server(3.0.4), there are two shards: one shard has 350GB, and the other has 300GB of data.

I followed the following steps:

Installed MongoDB 3.0.15 on new server
Copied the data from two shards to the new server
then ran the following commands

mongod    --configsvr --dbpath C:\mongoConfigData/configdb1 --port 27019
mongod    --configsvr --dbpath C:\mongoConfigData/configdb2 --port 27020
mongod    --configsvr --dbpath C:\mongoConfigData/configdb3 --port 27021

mongos    --configdb 127.0.0.1:27019,127.0.0.1:27020,127.0.0.1:27021 --port 27017

mongod    --dbpath F:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --port 27010

mongod    --dbpath D:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --port 27011

Added the shard and the shard collection details

sh.addShard("localhost:27010")
sh.addShard("localhost:27011")
sh.shardCollection("log", {"id" : 1, "eventId": 1})

Shard distribution shows both the shards but the total docs and the collection size does not match the one in the initial server.
Further the content in the mongodb is not the complete data
Why is it so? What am i doing wrong?


texts: Can PostGIS use spatial indexes on foreign tables?

texts:
I'm attempting to perform a query on some foreign spatial tables which is taking several orders of magnitude longer than running on the normal spatial table because the index is not used.

Indexes seem to be working on non-spatial queries.

Is this something that should work or is this not supported?


texts: Mysql: Tell InnoDB to keep a specific table or database out of pool/cache?

texts:
I have some tables that are used primarily to keep historical data, or that are only used once a day.

I do not want any operations on these tables to disrupt the more important tables present in the InnoDB buffer pool that get queried multiple times per second.

Should I just let the engine just do its thing and trust that it decides which tables are most important?
Is there a way to "pin" certain tables to the InnoDB cache?
Am I wasting my time?


texts: Allow two users permission to execute single agent job

texts:
I am trying to allow two users to run a single agent job. With this in mind, I don't want to add the users to the SQLAgentOperatorRole role in msdb as that will give them excess priveleges.

I tried creating a stored procedure which will run as the owner (dbo):

Create the stored procedure (logged in with a sysadmin account)

CREATE LOGIN [UnPriveleged] WITH PASSWORD = 'MyPassword'
GO

USE AdventureWorks2017
GO

CREATE USER UnPriveleged FOR LOGIN [UnPriveleged]
GO

CREATE PROCEDURE spRunJOb
WITH EXECUTE AS OWNER
AS
       SELECT CURRENT_USER
       SELECT SYSTEM_USER
       EXEC msdb..sp_start_job @job_name = 'test'
GO

GRANT EXECUTE ON spRunJOb TO UnPriveleged

and then I run it as the Unpriveleged user in another session:

SELECT CURRENT_USER
EXECUTE [spRunJOb]

and I get an error

Msg 229, Level 14, State 5, Procedure msdb..sp_start_job, Line 1 [Batch Start Line 0]
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

I thought this would run as the login for the owner of the stored procedure (sa) as that is a sysadmin and should be able to do everything.

The output I get from the selects confirms the procedure is executing as the sa system user. The overall SELECT output is

Unpriveleged
dbo
sa

How can I achieve what I want here?


texts: Query to find if there are more than X occurences within any period of given length minutes

texts:
I have spent many hours thinking about a solution for my problem but I give up.
Let's imagine a table
user_id | occurred_at
   -- OK match example
   1    | 2020-01-01 08:00:00 <- First match of the set
   1    | 2020-01-01 08:08:00 <- Second match (8 minutes away from the previous so OK)
   1    | 2020-01-01 08:10:30 <- this already exceeds 10 minutes period so the set is excluded

   -- Not matched example
   1    | 2020-01-01 10:00:00 <- First match
   1    | 2020-01-01 10:05:00 <- Second match (5 minutes away from the previous so OK)
   1    | 2020-01-01 10:09:59 <- this fits into 10 minutes period so the set is matched (09:59 away altogether from 10:00:00)

   -- Another OK (4 matched)
   2    | 2020-01-01 14:23:00
   2    | 2020-01-01 14:24:00
   2    | 2020-01-01 14:26:00
   2    | 2020-01-01 14:27:00

   -- Not matched
   3    | 2020-01-01 11:00:00
   3    | 2020-01-01 11:01:00
   3    | 2020-01-01 15:26:00
   3    | 2020-01-01 18:00:00

   -- User mismatch so set is not matched neither
   3    | 2020-01-01 20:00:00
   1    | 2020-01-01 20:01:00
   2    | 2020-01-01 20:02:00

How one can query a table like this to find rows with at least N (=3 in this example) occurrences for the given user that occurred in a explicit minutes interval (=10 in this example)? I think a table example above explains it better.
http://sqlfiddle.com/#!17/54d43/1


texts: PostgreSQL security threats in external modules or PL languages

texts:
In reading the answer to question I came across a paragraph that indicated that some PostgreSQL PL languages and/or contrib modules can be used to compromise the security of the system.

Is this only an issue if they get postgres db user or superuser access?
Is this because they could in essence create any PL/Python (or other PL/*) function script to run against the system? Example?
Is there any way to mitigate this, other than not installing PL/*?
The paragraph mentions other contrib modules. What other contrib modules could be a postgres/system security risk?

Note that if PL/Python, PL/Perl, etc are installed, or some contrib modules are present, then it might be fairly easy to escalate access from the postgres database user (or other superuser) to the postgres shell user. From there you can reconfigure the database to enable logging, install extensions, inject C code, etc. There is no guarantee you would detect such an exploit, so they might be logging activity for some time.


texts: Execution plan with multiple missing indexes

texts: If you run a query with 'Include Actual execution plan' the plan will also suggest indexes which are missing. The index details are inside MissingIndexes tag in the XML. Is there a situation when the plan includes multiple index suggestions? I tried different sql queries but couldn't come up with any query which generates two or more missing indexes.


texts: Investigating an Oracle 12c outage after the fact, while limited to SQL*Plus

texts:
Warning up front: unfortunately I'm limited to using SQL*Plus on remote site, with no other tools available. This is Oracle 12c (12.2.0.1.0) Standard Edition running on Windows Server 2012R2.

Over the weekend there was an outage where Oracle stopped responding for approx 15 minutes. Looking at application logs and Oracle's alert log, it was clear that something tied up Oracle until it hit an ORA-04031 e.g.

ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool","unknown object","KGLH0^b3e62c9b","kkocsBeElem: kkoMergeBindEqvCtx")

My problem is that I don't know the cause of this issue. My question is, what tools are available to me at this stage 48 hours later, to figure out what happened? As mentioned I've been through the alert log, and the application logs.


texts: How can I get a list of dates between two given dates with mysql?

texts: I need to generate a list of dates with MySQL between two given dates. This list of dates must be filtered to the dates that correspond to Tuesday and Thursday. Is it possible?


texts: Should I use begin tran to do an alter table column that increases the column length?

texts:
I want to execute this statement in a production database to alter the length of a varchar column from 50 to 72:

ALTER TABLE Users ALTER COLUMN LoginID varchar(72) NOT NULL

The column is not null so I'm NOT altering column nullability from NULL to NOT NULL.

Database server is SQL Server 2008 R2; table has about 60,000 records.

Do I have to put the alter statment within begin tran commit tran?


texts:
I am new to databases, learning MySQL through a textbook.

My doubts are regarding grant statement.

I am learning on a localhost environment (both server and clieat are my own system).

FIRST DOUBT

If I create a new user account by

 GRANT ALL on mydb.* TO 'violetkiwi' IDENTIFIED BY '12345';

Command executed successfully.  When I tried to log into this account by

mysql -u violetkiwi -p

Access denied for user 'violetkiwi'@'localhost' (using password: YES)

If I specify @'localhost' in GRANT ALL issue is solved:

 GRANT ALL on mydb.* TO 'violetkiwi'@'localhost' IDENTIFIED BY '12345';

Is @'localhost' or @'IP ADDRESS' compulsory in GRANT ALL user account creation?

SECOND DOUBT is IPADDRESS specification in GRANT ALL for user account

Consider I have to connect to MySQL server over the internet.

How can I achieve connection to MySQL server through ADSL modem which doesnt have static IP to specify during user account creation with GRANT ALL?

THIRD DOUBT

Sometimes I can access MySQL without specifying -p:

mysql -u root;

mysql -u violetkiwi;

but I can't perform operations on DB. Why this access is provided without specifying password? Is it a bug in my system? Does it have a purpose?


texts: How can I ensure that only one copy of a procedure is running in Oracle?

texts:
We have the need to ensure that only one copy of a particular procedure is running in Oracle.  If it is already running and a user tries to open another, then it should error.

Whats the best method of doing this?


texts:
I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this took 30 hours.

Our business users noticed while rebuilding the new catalog as some searches didn't work anymore as expected, but some did.

I was wondering how SQL Server proceeds while rebuilding the fulltext catalog.

Does it delete all index and then start rebuilding it? Or is there some intelligent logic behind it, like delete just a part of it piece-by-piece?


texts: Changing the administrative user on mysql from `root` to something else

texts:
Just installed mysql on Ubuntu -

sudo apt-get install mysql-server
sudo apt-get install mysql-client

As part of the installation process, I provided a password for the root user.

I can now log in with

mysql -u root -p

But I'd love some other user besides root to own the mysql administration. I'm coming over from the world of Postgres and have never used MySQL before, so I'm wondering how I could change this user.

Thanks!

EDIT

This is based off a recommendation from the Wordpress installation guide:

  It is a safer practice to choose a so-called "mortal" account as your mysql admin, so that you are not entering the command "mysql" as the root user on your system. (Any time you can avoid doing work as root you decrease your chance of being exploited.)


texts:
I have a push subscriber in transactional replication (and the distributor is the publisher), in this subscriber I configured sql server port on 1433, and I have a web application which connect to this subscriber database, and I want to use any port but not 1433, Can I configure SQL server to listen to two different ports one will be used in replication from distributor IP (which stay 1433) , and the other for web request (from web server IP), and how to do that ??

In case of a single port, If the default port of sql server(1433) is changed this will affect on replication cause subscriber and publisher listening on the same port, is this right ??


texts: Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

texts:
I am getting

"ERROR: out of shared memory"

presumably because my plpgsql function drops/creates a lot of temporary tables.

Searching for a solution to the issue, I found this answer that says:

  So I guess one answer is that you will need enough locks to cope with
  all these tables being added/dropped throughout the transaction.
  Alternatively, you could try to reuse the temp tables between queries,
  simply truncate them to remove all the temp data

In order to avoid having too many looks, I am thinking about rewriting as many temporary table code using Common Table Expressions (CTEs).

My questions are:

Do the table expressions in CTEs use locks that count towards max_locks_per_transaction?

Will replacing temp tables with CTEs solve the max_locks_per_transaction problem?

Related:

https://stackoverflow.com/questions/16490664/

https://stackoverflow.com/questions/3132533


texts: Creating databases - no access to folder - MS SQL

texts:
I am merging a few instances together which are for separate customers. They need to be separated by account access so customer X can't view customer Y's databases.

I have created a login (we'll call it Bob). Bob has the server roles dbcreator and public. Under user mapping, I have given access to the required databases.

If I use these account details in SSMS, I can see all of the databases (as If I were SA). I can only open the database I have access to but I still don't want Bob to be able to see other customers' database names. Any ideas?

Also, when using a script in my application to create a new database, it complains that I do not have access to C:\Program Files\Microsoft SQL Server\MSSQL12.IDENTITYACCESS\MSSQL\DATA

My instance has access to this folder but I do not want to give Bob the sysadmin server role. Is there a way to give Bob access to that folder without giving the sysadmin server role?


texts: MongoDB does not allow to create unique partial index in field that contains null

texts:
I'm using MongoDB 3.6.9 (with Ubuntu) to save a set of documents that contain one field named rss_id and for some documents, this field has the value null. I want to create a unique index in that field and tried this solution. From the documentation it seems a good solution but for some reason, I can't make it work in my MongoDB.

When I excute the folowing command (I'm using the shell of Robo 3T):

db.getCollection('noticias').createIndex(
   { rss_id: 1},
   { unique:true, partialFilterExpression: {rss_id: {$exists:true }}}
)

I get the error:

{
    "ok" : 0.0,
    "errmsg" : "E11000 duplicate key error collection: newsWall.noticias index: rss_id_1 dup key: { : null }",
    "code" : 11000,
    "codeName" : "DuplicateKey" }


texts: Importing large .sql file stops before even midway and starts deleting

texts:
Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported

any ideas?

http://gyazo.com/15ea06892757f7cdc93a577869b3446b current progress of importing

E: server info:
MySQL 5.5.31+ubuntu0.12.04.2
PHP 5.4.17-1~precise+1
Ubuntu Server 12.04.2 x64 LTS, no desktop, was fully updated before 2013-07-23

E!!! NOW it happent at this point: http://gyazo.com/772c8ae9dcd8194d31f2efa8bcc563c2


texts: Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

texts:
I have set my back up preference to primary and the secondary is not readable. Then I did the following:

Set up the same maintenance plan on all replicas in the AG through the maintenance wizard.
Configure the Backup Preference plan is set to Primary on the availability group.
Enable the 'For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings' check box and run your maintenance job.
Now when I ran the Tlog backup on the secondary it generated a Tlog back up which was not supposed to ...

Is this normal?


texts:
I am constructing a database that needs to keep track of historical data. It is a Electronic medical system. All data needs to be updated in real time so the user can see what changes happened in the application.

Would CDC be the way to go or should I just have triggers on the database that pushes the information into a Audit tables