texts: How to avoid implicit conversion when using XQuery value() method in TSQL?

texts:
I have the following xml that I am parsing using TSQL - XQuery value method.
<Root>
  <FooAudit>
    <BarIntField>..</BarIntField>
    <BarDateTimeField>..</BarDateTimeField>
    <BarVarcharField>...</BarVarcharField>
    <BarFloatField>...</BarFloatField>
  </FooAudit>
</Root>

However, every time I invoke .value() it results in an implicit conversion.  How can I get rid of this implicit conversion since this logic will be called thousands of times per minute?
SET NOCOUNT ON

DECLARE @BarIntField INT;
DECLARE @BarDateTimeField DATETIME;
DECLARE @BarVarcharField VARCHAR(25);
DECLARE @BarFloatField FLOAT;

--Test with non null values
SET @BarIntField = 123;
SET @BarDateTimeField = GETUTCDATE();
SET @BarVarcharField = 'My Text';
SET @BarFloatField = 2.3;

--Test with null values
/*
SET @BarIntField = NULL;
SET @BarDateTimeField = NULL;
SET @BarVarcharField = NULL;
SET @BarFloatField = NULL;
*/

DECLARE @payload XML;

SET @payload =
(
    SELECT @BarIntField AS BarIntField,
           @BarDateTimeField AS BarDateTimeField,
           @BarVarcharField AS BarVarcharField,
           @BarFloatField AS BarFloatField
    FOR XML PATH('FooAudit'), ROOT('Root'), TYPE
);

--Implicit conversion here
DECLARE @parsedIntValue AS INT = @payload.value('(/Root/FooAudit/BarIntField/text())[1]', 'int');
PRINT @parsedIntValue;

--Implicit conversion here
DECLARE @parsedDateTimeValue AS DATETIME = @payload.value('(/Root/FooAudit/BarDateTimeField/text())[1]', 'DATETIME');
PRINT @parsedDateTimeValue;

--Implicit conversion here
DECLARE @parsedVarcharValue AS VARCHAR(25) = @payload.value('(/Root/FooAudit/BarVarcharField/text())[1]', 'VARCHAR(25)');
PRINT @parsedVarcharValue;

--Implicit conversion here
DECLARE @parsedFloatValue AS FLOAT = @payload.value('(/Root/FooAudit/BarFloatField/text())[1]', 'FLOAT');
PRINT @parsedFloatValue;

Here is the plan.


texts:
I had to modify a report that was assembled entirely within table (with 1 column and 10 rows). After my modifications, the report now spans multiple pages and I need it to break naturally at one point (i.e. beginning of Row 8).

Is there a way to add a page break in the middle of a  table?

Note, I've tried adding a "Rectangle" and applying the page break with no luck (in the desired Row and then tried in the Row above).

Additional Info:
   - using Visual Studio 2010
   - DBMS = SQL Server 2012 R2

Any assistance would be greatly appreciated.


texts: Transactional Replication breaks when schema is changed in Publisher

texts:
We have set up Transactional replication where two Publishers feed into one Subscriber. Now if try to change the schema of any one Publisher or both publishers, replication breaks.
We have tried to pause the replication and change the schema in publisher as well as subscriber, but as soon as we re-start the replication it breaks.
The only option that we found is, we have to drop the replication, sync the schema and the start the snapshot replication.
Is there a better way of doing this?


texts: How to investegate MySQL crash on windows?

texts:
I have MySQL 5.6.19 running on Windows Server 2008 R2.

I have 3 servers A, B, C
Server A is a stand alone server. This server had data that can't be placed on SERVER B (ie. users information.)

Server B is another stand alone server which has data that can't be saved on server A. Server B is a master of server C.
Server C is another stand alone reporting server which is a SLAVE of server B.

After using adding FEDERATED table to server B. I re-sync server C "the the slave" to Server B "it's master."

But now server C will not start unless I add --skip-slave-start option.

In addition, whenever I issue START SLAVE; command on server C, this server stops working immediately (crashed.)

Something is going on with the replication. I must say that the replication was working before I added the FEDERATED table with no issues. (IMPORTANT NOTE: I did not issue any create table engine=FEDERATED while the replication is running. I first created all the FEDERATED tables on the master (SERVER B) and then I created a server backup and restored it into the slave (SERVER C).

In an effort to figure out what is causing this crash. I have added core-file to the file my.ini to get MySQL to generate core.pid file, but nothing was created.

I tried to add core-file-size=1GB but this caused the not to start at all due to unknown variable! (perhaps it is not recognized by Windows.)

What can I do to investigate this silent crash on Windows 2008 R2?

Thank you


texts: How can I compare row values as column name to the another table and Get data (Sql Server-2017)?

texts:
CREATE TABLE Table1(
ID int NOT NULL,
PTA_1 int NOT NULL,
PTA_2 int NOT NULL,
PTA_3 int NOT NULL,
PTA_4 int NOT NULL,
PTA_5 int NOT NULL,
DTA_1 int NOT NULL,
DTA_2 int NOT NULL,
DTA_3 int NOT NULL,
DTA_4 int NOT NULL,
DTA_5 int NOT NULL
)

INSERT INTO Table1 VALUES(1,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(2,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(3,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(4,1,0,1,0,0,0,1,0,0,0)

CREATE TABLE Table2(
ID int  NOT NULL,
PartNum1 varchar(10),
PartNum2 varchar(10),
PartNum3 varchar(10),
PartNum4 varchar(10),
PartNum5 varchar(10),
DataNum1 varchar(10),
DataNum2 varchar(10),
DataNum3 varchar(10),
DataNum4 varchar(10),
DataNum5 varchar(10)
)

INSERT INTO Table2 VALUES(1,'PTA_1','PTA_2','PTA_3','','','DTA_1','DTA_2','DTA_3','DTA_4','')
INSERT INTO Table2 VALUES(2,'PTA_1','','','','','DTA_1','DTA_2','','','')
INSERT INTO Table2 VALUES(3,'PTA_1','PTA_2','','','','DTA_1','','','','')
INSERT INTO Table2 VALUES(4,'PTA_1','','','','','','','','','')

I have 2 tables as "Table1" and "Table2".
"Table1" is transaction table it contains zero's or 1's value and "Table2" contains "Table1" column names.
Compare "Table2" column name and get data as per relevant ID's.
Result should be like this.

Please provide the query


texts:
I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB inside few days! The objects are removed after few mins, so it's not case the case of amount of data really held, but simply vacuum does not do its job. After full vacuum table size drops to approx. 150 MB. In the logs I see that vacuuming process is running, but as table grows in size, it takes over 5 mins and does not help a lot.

I wonder how can I tune the vacuum to make its job better for this table?


texts: Delete using id's not found in loop - MySQL

texts:
I have a stored procedure where I loop through an array passed as an argument, update the values provided, then delete the missing values using an id passed in the given array.

    /* edit the purchase items */
        WHILE items_index<items_length DO
            UPDATE PurchaseOrderItems SET quantity=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].quantity')) ,products_productid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].productid')) WHERE itemid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid'));
            IF items_index+1=items_length THEN
                /* condition will only be true in last iteration*/
                SET item_separator='';
            END IF;
            SET save_list = CONCAT(save_list,JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid')),item_separator);
            SET items_index=items_index+1;
        END WHILE;
        /* delete all other items that were not in the list */
        DELETE FROM PurchaseOrderItems WHERE purchaseorders_purchaseorderid=p_purchaseorderid AND itemid not in (save_list);

The delete command however deletes all the records except for the first item in the save_list variable. I have confirmed that the variable save_list has the id's to be skipped so where are things going wrong?


texts: Calculating Median Value within a Group By

texts:
I have a Stored Procedure which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is calculate the Median value... I suspect I would need a subquery but can't figure out how to accomplish this.

SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time]
     ,AVG(CASE WHEN Duration is null OR Duration = 0
              THEN null ELSE Duration
          END) AS [Mean Response Time]
FROM [Application].[Exchange] WITH (NOLOCK)
WHERE [Started] >= '24 Oct 2012' AND [Started] < '25 Oct 2012'
GROUP BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)
ORDER BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)

Can anyone suggest a way I might accomplish this?


texts: Why is this query failing with invoker rights?

texts:
I connect to oracle as sysdba and run this

but why is it failing when called with invoker rights? That is the p_user call.

Here is the code:

alter session set current_schema=x;
CREATE or replace package p_definer as
    procedure d(name_ varchar2);
end;
/
CREATE or replace package p_user authid current_user as
    procedure d(name_ varchar2);
end;
/
CREATE or replace package body p_definer as
    procedure d(name_ varchar2) as
    begin
        execute immediate 'drop procedure '||name_;
    end;
end;
/
CREATE or replace package body p_user as
    procedure d(name_ varchar2) as
    begin
        execute immediate 'drop procedure '||name_;
    end;
end;
/
create or replace procedure f_foo as
BEGIN
    dbms_output.put_line('hello world');
END;
/

And here are the calls

SQL> call p_user.d('f_foo');
call p_user.d('f_foo')
     *
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "G_GS.P_USER", line 2

Why doesn't that work?

SQL>
SQL> call p_definer.d('f_foo');

Call completed.


texts: Cannot open database requested by the login. The login failed

texts:
I have a SharePoint environment, but my problem is related to DB as I couldn't solve it in SharePoint ways.

I have a database that suddenly stopped working. It's appearing in SQL management studio, but without the + sign. If I right click on it to see the tasks, they're disabled.

I have full control over the server as am the one who installed SQL and Windows. Any idea how to solve this.


texts: Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie -- show me why I am wrong)

texts:
I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of those in the tutorial. I then ran the following code from the tutorial:

df = pd.read_sql_query('''
  SELECT
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
  FROM neighborhoods AS neighborhoods
  JOIN stations AS subways
  ON ST_Contains(neighborhoods.geom, subways.geom);''', engine)

This worked and returned the expected output. But when I went back through this query I noticed that the table "subways" was referenced (maybe this is where I am confused) but I don't have a table named "subways" because I had titled this table "stations". Indeed, here is the list of available tables in the db:

pd.read_sql_query('''SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public';''',engine)

       table_name
   0   geography_columns
   1    geometry_columns
   2     spatial_ref_sys
   3       neighborhoods
   4      raster_columns
   5    raster_overviews
   6             streets
   7               solar
   8            stations
   9              census
   10           vehicles

So, why is this query working?

I think I am asking for rows from a table named subways with the subway.name in the SELECT portion of the query, isn't it true?


texts: Deleting Entries from Table not reducing my DB file

texts:
Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file.

When i Entered 512 Record in my users table its size get increased to 200kb from 96 kb but when I deleted all rows using Command

DELETE FROM USERS;

then my .ibd file didn't reduce not even by a single byte.

BUT When i delete rows using command

TRUNCATE TABLE USERS

Then my .ibd file got reduced to it's initial size which was on creation that is 96 kb.

Now i Want to know that is here any way to reduce .ibd files when i use DELETE FROM USERS WHERE ...  command


texts: Full Text Index Data file much smaller after recreation

texts:
In order to speed up a field's datatype change, I:

completely dropped the Full Text Catalog and a few NC indexes.
changed the datatype (from VARCHAR to NVARCHAR).
recreated the NC Indexes.
recreated the FTC.

Before this change the NDF was 100gb and 6gb free space. After the change the NDF was still 100gb from with 99gb free space (and I looked at the FTI status and it was idle and fully populated).

Why would recreating the FTI make the overall size so much smaller? There were a ton of tables in the FTC that I didn't add back in there, but those tables didn't have any columns checked. The FTI only indexes the tables were there are actual columns checked, right?


texts: Cassandra query response time degradation

texts: I am experimenting with Cassandra on 4 sites with 2 nodes on each one. Have small network latency between them. I always query the data in DC where I inserted it. I see very strange behavior: when I increase the nodes number the query response time slowly increasing, but insert time is improving. I am expecting that query time should improve as well. Any suggestion regarding configurations check?


texts: SQL Server CLE Encryption Implementation

texts:
We got instruction from HQ to implement encryption on our database. The reason be, should a client gets hacked, the hackers cannot access personal info such as names, phone numbers, addresses, etc.
I have done a fair bit of research and it seems that Cell Level Encryption could be the way to got but I have some questions regarding the implementation (I am a developer, not really DBA material...).
Am I correct in saying the following:

All data types, for the columns we want to encrypt, must be changed to varbinary
We have to change all our SELECT statements to include DecryptByKey for encrypted columns
We have to change all INSERT & UPDATE statements to include EncryptByKey for encrypted columns

Since 99% of all selects, inserts and updates in our system happen in stored procedures, how would I implement the opening and closing of the SYMMETRIC KEY? Surely, any hacker worth something, can look at the SP code to see which key was used, or just execute the SP the get the data?
Also, is there a way to determine if a column contains encrypted data? We have generic insert/update functions and these would need to know when to use EncryptByKey.
Any guidance will be greatly appreciated!


texts: Best practices for relating NoSQL data to RDMBS

texts:
I am looking to use both a NoSQL solution (Redis, Mongo, etc) with a mySQL/Laravel application. My reasons for this is that I need a schemaless key value storage to go with my relational data. I could easily make a key value storage within mySQL, but I think that this is a case where NoSQL could shine and would rather not take the easy way out. Other than coming here for help, that is.

What I am a little foggy is how to tie these two together. An example scenario is I have 2 forms that generate leads. One form has email, best time to call, and date of birth. The other has email and are you wearing pants? Is it as simple as storing data in my NoSQL table as follows:

{ 'lead_id':3829, 'email': '3829@test.com' }
{ 'lead_id':3829, 'best_time': 'evening' }
{ 'lead_id':3829, 'dob': '1980-07-01' }
{ 'lead_id':3830, 'email': '3830@test.com' }
{ 'lead_id':3829, 'pants': 'off' }

Then my mySQL Lead table could look like:

id   | form_id | timestamp
3829 | 1       | 2014-09-18 11:02:32
3830 | 2       | 2014-09-18 09:12:10

Since this will be my first NoSQL excursion, I am also not certain as to the functionality available to my NoSQL data. Would it still be possible to retrieve data based on my lead_id and sort on a particular key? For example, filter form #2 results by email containing 'foo' sorted by the date they were submitted?


texts: Describe the process in steps, please. What tools do you use for it?


texts: Data-Driven Subscription - how to iterate through all parameter values?

texts: I am trying to build a data-driven subscription in SSRS (In SharePoint integrated mode, but I believe that's irrelevant). The rsds file I am running against has a certain parameter that is a hard-coded list of about 20 values. In the subscription, I need to run the report and generate a separate Excel file for each of the 20 values. Where do I assign those those values from within the subscription definition? I can specify a query but don't see where to point to the parameter list that is hard-coded inside the report file. Can't I just point to the list and have this run 20 different files automatically or do I need a separate subscription for each value?


texts:
when I tried to connect to my oracle instance with sql developer, I see this error in logs
TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
  TNS-00534: Failed to grant connection ownership to child
   64-bit Windows Error: 10022: Unknown error

Can someone give me a poit how to solve this error. Note I have 64b client installed.


texts: Can I anchor my data type to system type?

texts:
The following code:

create or replace type sqlids_t is table of sys.v_$sql.sql_id%type;

...gives me...

Error(2,31): PLS-00329: schema-level type has illegal reference to SYS.V_$SQL

I get the same error creating a %object or %row type.

Is there any way I can use a sys type in my type definition? I know I can use the underlying VARCHAR2(13), but I'd like to avoid it if possible.


texts:
I am beginning to develop a Data Warehouse. The data does not already exist in a relational database. Instead it will be sourced from various 3rd party vendors.

The data requirements have been drawn up into an Excel spreadsheet containing 15 sheets with column totals ranging from 5 to 60 on each sheet.
Do I create a Fact table of each sheet heading and within each sheet create related dimensions.
In the sheet with 60 columns, none look like they can be grouped into separate dimensions. So do I create one very large dimension?

Also, all dimension columns will have default values, which can be overridden for a specific related fund. How best to do this? Type 2 SCD and add the fund identifier to each dimension?

Thanks!


texts: How to know which process is causing high memory usage on DB?

texts:
I've got one Onesight alert mail stating below:

Short Description: Onesight Alert: ZEUS DB Monitor (zeus04)

Description:
Solaris 5.8: vmstat memory(free) - Free Memory for zeus04 2065112   8:06:02 AM IST    2 min
  Free Memory below 4GB: Value was 3,965,488 , threshold is 4194304.0 - 12:36:02 AM IST

Upon issuing OS level commands below I got the following understanding:

prstat

This command returned me the top process ID(3578) which is using more CPU. then,

prstat -Lm -p 3578

this command returned me the following output:

PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWPID
3578 oracle 0.7 2.3 1.6 0.0 0.0 90 4.4 1.5 483 235 1K 0 java/838694
3578 oracle 0.3 0.0 0.2 0.0 0.0 95 1.5 0.1 52 8 41 0 java/839336

I thought some Java process would be running so asked the Apps Support team and the said:

we believe this process is related to the Oracle OEM agent Java process

Now how do i drill down the issue and resolve because free memory is going below 2GB almost everyday.

DB environment details:
OS:Solaris 5.8(Unix)
DB version: 10.2.0.5
Memory size:64 GB

Regards,

Ritu


texts: Propper way to monitor SQL Server mirror status from service or job?

texts: We have a production DB which is in a mirror configuration. It is in synchronous high safety with automatic failover configuration. Right now we monitor it using windows service which pings it. Is there a better way to monitor mirror state using job or script?


texts: Innobackupex: How to extract individual files from xbstream compressed backups

texts:
We are scripting a mix of full and incremental backup setup for our prodcut backend with sunday triggering full backup and incremental backup on all the remaining days.

In this process, we should be able to read the LSN number of a file from the full backup file.

We have setup the script to compress the backup into xbstream format so it would result one single file compressing all the data.

Now the question is how to extract or read the LSN or one single info file from the complete xbstream file.

Though we can use log file for this, we wanted a more dependable solution.

Thank you.


texts: Disable alphabetical execution order of triggers

texts:
I would like to disable alphabetical order of triggers firing. Is it possible?

The reason I need this is to show in my studies why it is important to take care of triggers' execution order. However, I cannot find any way to disable this order and allow triggers race.


texts:
I'm going though some past papers for my database exam and it asks:

  Specify all minimal keys for R

R(A,B,C,D,E)
A → B
CD → E
E → A
B → D

I am unsure what is meant by minimal key, I've tried googling it but it only comes up with minimal superkeys. Does it just mean shortest candidate keys: CD, CA, CE, CB?

I'm getting confused as in each question he uses a different name for it and as far as I understand a minimal superkey is a candidate key?


texts: Query plan shows high negative value and the server slows down

texts:
My SQL Server 2016 had been presenting problems certain days, all process since the morning begins to run slowly than normally. Sometimes updating statistics works, sometimes not and had to reboot service. In query plan, when things go wrong with the "main" query, it shows a high negative value, after things start to work well, the query plan show normal values.

There is something else thing I could do and  somebody knows why sometimes update stats doesn't work?

Thanks in advance


texts:
In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it.

Some info:

My database is in Full Recovery Model.
Backups for both database and log file taken every day.
My database is mirrored to a mirror SQL Server.
My database has active connections.
I have read so many articles and opinions but still I cannot decide what I should do in order to truncate log file without consequences.

Should I turn to simple recovery model and truncate?

Should I stop mirroring, do what I should do to principal database and then enable mirroring?

Should I create a new log file to another disk?

I have read so many things but I am really confused. Could someone give me a hint how should I cope with this problem?


texts: Error Code : 1248 Every derived table must have its own alias in MySQL

texts:
i have problem here,
when im trying to select with query below, i got error message

  Error Code : 1248 Every derived table must have its own alias

SELECT B.BRANCH_CODE, B.BRANCH_NAME, C.COMPANY_CODE, C.NAME, TSK.DATE_CREATE,
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Payment'
    ELSE 'Payroll'
  END AS TRX_METHOD_E,
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Pembayaran Tagihan'
    ELSE 'Pembayaran Gaji'
  END AS TRX_METHOD_I,
  TB.TOTAL_RECORD,
  TB.ACCOUNT_NO,
  TSK.TRX_TOTAL,
  TC.TOTAL_CHARGE,
  DATE(TSK.DATE_TRX) AS DATE_TRX,
  TB.REF_ID,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'All Success'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'All Failed'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Partial Success (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Pending Execution'
  END AS TRX_STATUS_E,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'Berhasil Semua'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'Gagal Semua'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Berhasil Sebagian (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Tunggu Eksekusi'
  END AS TRX_STATUS_I
FROM CB_TASK_BULKS TB
  JOIN CB_TASKS TSK ON TSK.REF_ID = TB.REF_ID
  JOIN CB_COMPANIES C ON C.COMPANY_ID = TSK.COMPANY_ID
  JOIN CB_BRANCHES B ON B.BRANCH_CODE = C.BRANCH_CODE,
(
  SELECT REF_ID, SUM(CHARGE) AS TOTAL_CHARGE
  FROM
  (
    SELECT XTB.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS XT
    JOIN CB_TASK_BULK_DETAILS XTBD ON XTBD.BULK_DETAIL_ID = XT.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB ON XTB.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB2.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS_DOM XTD
    JOIN CB_TASK_BULK_DETAILS XTBD2 ON XTBD2.BULK_DETAIL_ID = XTD.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB2 ON XTB2.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB3.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_PAYMENTS XP
    JOIN CB_TASK_BULK_DETAILS XTBD3 ON XTBD3.BULK_DETAIL_ID = XP.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB3 ON XTB3.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID
  )
  GROUP BY REF_ID
) TC
WHERE TC.REF_ID = TSK.REF_ID
AND (TSK.TRX_COUNT_SUCCESS > 0 OR TSK.TRX_COUNT_FAIL > 0);

can somebody help me to find my problem here,
i think i've given all tables with his own "name" like TB for CB_BULKS_DATA (example)


texts: Stored procedures over SQL in the code, good idea?

texts:
Possible Duplicate:
  What are the arguments against or for putting application logic in the database layer?

Assuming I got over the problem of maintainability and debugging.
My assumption is it is better to have all my queries as stored procedures.
Am I missing any caveat?


texts:
This is a follow up query of this
I am using : mysql  Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT,
    title CHAR(20),
    date DATETIME,
    description VARCHAR(1000),
    CONSTRAINT Work_PK PRIMARY KEY (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Work_Citation(id INT NOT NULL AUTO_INCREMENT,
                   work_1 INT NOT NULL,
                   work_2 INT NOT NULL,
                   CONSTRAINT Work_Citation_PK_1 PRIMARY KEY (id),
                   CONSTRAINT Work_Citation_PK_2 UNIQUE (work_1,work_2),
                   CONSTRAINT Work_Citation_FK_1 FOREIGN KEY (work_1) REFERENCES Work(id),
                   CONSTRAINT Work_Citation_FK_2 FOREIGN KEY (work_2) REFERENCES Work(id)) ENGINE=InnoDB DEFAULT  CHARSET=utf8;

-- How to prevent circular citation?
-- Example 1: In the Work_Citation table, if one piece of Work say work_1 = 1 cites another piece of Work say work_2 = 2, then work_1 = 2 cannot cite work_2 = 1, ie the converse is not possible.
-- Example 2 : (work_1 = 1, work_2 = 2), (work_1 = 2, work_2 = 3), (work_1 = 3, work_2 = 1)?


texts:
I am trying create a table using a cross join or Cartesian Product subquery that creates a table with a large number of rows, like at least 148 million rows.  I get this

  ERROR 1114 (HY000): The table is full.

I plan on filtering and querying the table later on, but how can I create this table?


texts: Why would MSFT would deprecate mirroring from 2012.. I understand alwaysON will replace it but then SE users are left with nothing..no alwaysON or dbm from future releases.. Just analyzing from end users POV.


texts:
We are designing a new multi tenant .NET Core 2.1 application progra, where we are using one database per tenant.

We can give each tenant a different subdomain so that we can base the tenant lookup on the subdomain, but that wouldn't let one user be able to exist in several tenants.

How would you solve it so that a user can belong to several different tenants?

I've thought about creating three tables, namely, Users, Tenants and UsersTenants in a separate database, so that we can lookup all the tenants one can access from there, and then have a default one it chooses automatically. And then in turn let the user pick from a dropdown list in the application to change the tenant (and always send a x-tenantId in a header with every request).

A very big problem with this is that the userlist recide outside of the tenants database. So when for instance a user wants to save CreatedBy or ModifiedBy and any other action based on users, it would have to store an ID located in a different database, that's not good at all (securitywise a nightmare also).

I've thought about solutions to this but haven't came up with any good ones, maybe a sync so that the users that are created in the main catalog is also added to the specific user databases, but what happens if a customer admin wants to create a new user, we would have to create it in two different places.

Any thoughts on how this can be solved?


texts: How do I convert unicode data from UTF-16LE to UTF-8 when migrating from SQL Server to MySQL?

texts:
Objective: Ensure existing Unicode (UTF-16LE) data is displayed the when moved to MySQL (UTF-8).

Background:
I'm migrating 1TB from SQL Server to MySQL. The current Collation in SS is "Latin1_General_CPI_CI_AS". Given MySQL supports Latin1, I am assuming there shouldn't be a problem with the collation.

My main concern is the Character Set conversion. In SS, all numerical data-types are set to Character Set = NULL. The remaining are set to "UNICODE". Since SS Unicode is UTF-16LE and it needs to be UTF-8 in MySQL, I fear I'm stuck verifying each character in all non-numerical columns. That would be very long and intensive given ~4k tables.

Potential solutions:
1. Using SqlDataReader, validate each record value, change if necessary, and then save updated results to an export file.
2. BCP data out of SS and save to .txt file. Reopen each .txt file and re-save with the UTF-8 encoding.

Problems encountered:
When converting the character set from UTF-16LE to UTF-8 using a sqldatareader, it works great except when the source value to be converted is already in UTF-8. Ideally, the solution would only run if the data needed to be converted.

Does anyone know of an easy way to ensure data is correctly set and verified from database to another? Am I over thinking this? Is this even an issue?

Thanks in advance!


texts: is there any need to index b again as it is already a under primary

texts:
Is there any need to index b again as it is already a  under primary-
if primary(a,b)  and in query we have where b=' ';

Is there any need to index(b) again individually because while indexing it will take left column first then thinks of the next.

Thanks in advance!, and please help me out of this.


texts: Return results that contain or do not contain punctuation

texts:
Is there a way within MySQL to return results using a LIKE statement that contain or do not contain punctuation characters such as apostrophes and hyphens?

For example if I have one of the following queries:

SELECT * FROM some_table WHERE some_value LIKE "%this-thing%";

or

SELECT * FROM some_table WHERE some_value LIKE "%person's thing%"

Is there a way in which I could obtain the same results as the queries below but with a single LIKE clause?

SELECT * FROM some_table WHERE (some_value LIKE "%this-thing%") OR (some_value LIKE "%this thing%");

or

SELECT * FROM some_table WHERE (some_value LIKE "%person's thing%") OR (some_value LIKE "%persons thing%");

Basically a function that when searching would return a result if either version of the text exists ("person's thing" or "persons thing"), something like:

EDIT

Is there a function within mysql which would return only alpha numeric characters within a column? For example if the column value where "something's & other-stuff" then it would be returned as "somethingsotherstuff"? So a query would look something like:

SELECT * FROM table WHERE magic_function(column_name) LIKE '%words%'

EDIT x2

For the time being I have resolved this by chaining together REPLACE() and replacing all special characters in the column value with "" before doing the comparision. Value being compared has also been stripped of all special characters.


texts:
So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS.  I've just taken over this application, and I'm concerned we're not getting good backups, since snapshots aren't reliable.

I've had an idea. I propose to

periodically create a read replica
elevate it to be a separate instance
and do a mysqldump from that replica.

Does this make sense?


texts: How to trace SQL queries which are crashing SQL Server

texts:
We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here).

Our application runs Hibernate for DB access, and since we upgraded recently from v3.1 to 3.6, we have been experiencing SQL Server crashing regularly (every 24-48 hours, but sometimes more frequently).

The specific problem in question seems to be memory related.  Just before the server crashes (and is then auto restarted by the failover cluster manager it seems), we get a load of these errors:

Error: 701, Severity: 17, State: 130.
There is insufficient system memory in resource pool 'internal' to run this query.

also occasional (but regular) messages of

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

I'm also getting some app level errors such as

java.sql.SQLException: A time out occurred while waiting to optimize the query. Rerun the query.

and then the exciting and possibly instructive error:

The query processor ran out of internal resources and could not produce a query plan.
This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.
Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

The load on the server hasn't changed so there's no reason it should now be running out of memory when it previously wasn't indicating a problem with queries being sent to it.

Now to the question - how do I trace the queries that are causing this error (and thus presumably all of the problems)?  It seems like since our Hibernate upgrade, it's been firing some huge queries at SQL Server, and that's broken it.  As it happens, I have some ideas as to what they might be, but it would be good to be able to trace them.

I can of course run SQL Server profiler, but once this is done (and produced an enormous amount of data - it's a busy OLTP database), how do I filter to find the problematic queries?

Thanks!


texts: PostgreSQL PostGIS geopoint within angle range

texts:
I'm new to PostGIS and I see there are so many options, it's a bit overwhelming.

I want to simply find all geo points (stored in ISO 6709 format) within a certain angle of a given geopoint.

For example, given a geopoint "+25 -90", find all geopoints within 5degrees angle of that geo point, that is:

+25+/-5 degrees -> 20 to 30 degrees latitude
-90+/-5 degrees -> -85 to -95 degrees longitude

What's the easiest way to achieve this query?

Thanks


texts:
I've been looking at some SQL Profiler traces recently, and I see something I don't understand.  I'm running simple INSERT commands:

INSERT INTO Foo (ForeignID, FileExtension, MimeType) VALUES (1, 'FOO', 'Application/Foo')

SQL Profiler consistently shows 0 Writes and 2 Reads.  How can there not be any writes on an INSERT?  Or am I misunderstanding what profiler is saying?

For completeness, here's the table definition I'm using:

CREATE TABLE [Foo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ForeignID] [int] NOT NULL,
[FileExtension] [nvarchar](32) NOT NULL,
[MimeType] [nvarchar](96) NOT NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


texts: ALTER TABLE query to convert table to utf8mb4_bin takes many hours - how to optimise this?

texts:
I have a table which has approx 15 million rows - I won't add the schema here but it has 96 columns (far too many in my opinion).

The current table collation is latin1_swedish_ci but for full emoji support I need to change this using the following command:
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Running this query when I view the processlist does the copying to a temp table - due to the size of the table takes ages I don't know exactly how long but it must be between 6-9 hours. I have to do this the same query on another table which is even bigger than this one so I'm worried how long this will take so I need to find a better approach for this?

When I run show index from posts; I get 37 rows!?? Which explains why this is very slow.

What would be the best way to make this a much quicker process?


texts:
I have a SQL Server database with the PAGE_VERIFY option set to NONE. When I set this to CHECKSUM, only pages changed from that point onwards get their checksum calculated and stored on the page.

I'm looking for the best way to put checksums on at least all table and index data.

My initial idea would be to drop and recreate all clustered indexes. For tables that have no clustered index I would create one and then drop it again.

That should result in at least one write operation on each data and index page, but it's a bit heavy-handed. Anyone has a better idea?


texts: Can I create a Azure SQL Server (server) with a different collation?

texts:
I want for my application the same collation at all database levels (server, database, columns): Latin1_General_CI_AS
This is recommanded by Dan Guzman to have constistent collations and get the collation configuration right from the start.

The default collation for a Azure SQL Server (server) is SQL_Latin1_General_CP1_CI_AS. I would like to change this to Latin1_General_CI_AS. Or create a new Azure Sql Server (server) with the collation Latin1_General_CI_AS. For on premise SQL Servers it is possible.

In the SQL Azure Collation Documentation from 2010 is stated that  ...the server and database collations cannot be configured in SQL Azure... Besides this, I didn't found something useful.

I would be totally happy if I could create a new Azure SQL Server (server) with a collation Latin1_General_CI_AS.

Is it somehow possible or exists a workaround?
I really appreciate any help.


texts: Help combining two MySQL queries to improve performance

texts:
I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query:

SELECT d.field_id_105 AS code,
       title
FROM exp_channel_titles AS t
INNER JOIN exp_channel_data AS d ON (d.entry_id = t.entry_id)
WHERE t.channel_id = 18
AND t.status = 'open'
AND t.author_id = 123
ORDER BY t.entry_id ASC

Then, using a PHP foreach loop, I'm looping through each of those codes and performing a second query on each one, which is obviously incredibly inefficient.

The second query varies slightly depending on whether the code comprises one or two letters. If it comprises 2 letters, I simply want to find all records where a specific field starts with those two letters. If it comprises one letter, I want to find all records where a specific field starts with that letter followed by any number from 1 to 9 (e.g. G1 or W8).

Here are the two different versions of the second query:

If the code comprises two letters

SELECT t.title AS title,
       t.entry_date AS entry_date,
       t.entry_id AS entry_id,
       i.title AS installer,
       i.entry_id AS installer_id,
       d.entry_id
FROM exp_channel_data AS d
INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48)
INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open')
INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open')
WHERE LEFT(field_id_56, 2)  = 'AB'
AND d.channel_id = 12

If the code comprises one letter:

SELECT t.title AS title,
       t.entry_date AS entry_date,
       t.entry_id AS entry_id,
       i.title AS installer,
       i.entry_id AS installer_id,
       d.entry_id
FROM exp_channel_data AS d
INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48)
INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open')
INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open')
WHERE LEFT(field_id_56, 2) IN ('G1','G2','G3','G4','G5','G6','G7','G8','G9')
AND d.channel_id = 12

I've 'hard-coded' the values in the WHERE clauses for demo purposes, but they would actually be populated dynamically by PHP.

So essentially what I'd like to do is combine all of these queries into one query. I'm not sure if that's even possible. I got as far as using the first query as a subquery inside the LEFT() function, but I'm getting a little out of my depth at that point and can't work out how to handle appending the numbers to the single-letter codes. Maybe some REGEXP, but I'm not sure.

Any help appreciated, thanks.


texts: MDW performance_counter_instances table running out of identity values

texts:
My MDW database's performance_counter_instances table has run out of identity values.

Has anyone ever encountered this?

Is it safe to change the data type of the identity field performance_counter_id from INT to BIGINT without breaking anything?

Bonus Question - What is the impact of this field running out of identity values?

I can't find anything useful about this issue online.

I have converted the data type to BIGINT using this script and now all of the collection_set_2_upload jobs are failing with the following error message:

  LKU - Lookup into performance_counter_instances to obtain performance_counter_id for all counter paths that get inserted.Outputs[Lookup Match Output].Columns[performance_counter_id] and reference column named "performance_counter_id" have incompatible data types.

Please let me know if this is something that can be fixed or if I will have to just eventually recreate the MDW database again.  This happens about once every 6 months in my environment.  For now I've reseeded the identity to -2147483647 to buy some time.

DBCC CHECKIDENT ('snapshots.performance_counter_instances', RESEED, -2147483647)


texts: Do I need to add more memory to my SQL Server 2008 R2 instance?

texts:
I've been asked if one of our SQL Server instances has sufficient memory. This is a SQL Server 2008 R2  Standard Edition running on a x64 machine with 40 CPUs and 64 GB of RAM.

The total size of all the databases running under this instance is slightly more than 300 GB.

The instance has been configured with 4096 MB for the Minimum Server Memory and 24000 MB for the Maximum Server Memory.

So far, I've managed to gather the below information:

Physical memory in use:

SELECT physical_memory_in_use_kb,
   process_physical_memory_low,
   process_virtual_memory_low
FROM sys.dm_os_process_memory

physical_memory_in_use_kb process_physical_memory_low process_virtual_memory_low
------------------------- --------------------------- --------------------------
25657180                  0                           0

Performance Counters:

SELECT counter_name , cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)',
'Target Server Memory (KB)', 'Granted Workspace Memory (KB)',
'Maximum Workspace Memory (KB)',
'Memory Grants Outstanding', 'Memory Grants Pending')

 counter_name                                cntr_value
-----------------------------------     --------------------
  Granted Workspace Memory (KB)                1024
  Maximum Workspace Memory (KB)                17714520
  Memory Grants Outstanding                    1
  Memory Grants Pending                        0
  Target Server Memory (KB)                    24576000
  Total Server Memory (KB)                     24576000

Buffer Pool:

select count(*) AS Buffered_Page_Count
 ,count_big(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

Buffered_Page_Count Buffer_Pool_MB
------------------- --------------------
2499693             19528

Buffer Pool Size Graph:

BPHR:

PLE:

Plan Cache Size:

Page Reads Per Second

Page Writes Per Second

Total IO Wait Time:

My questions are:

What are the other things that I should be looking at?

If the above is sufficient, do I have to recommend adding more memory (maybe make it 50 GB) because PLE shows a degradation over time (while Total IO Wait Time shows going up)?

Target Server Memory is equal to the maximum memory set. Is it an indication that the instance can utilise more memory if it is configured with?

So, how do I know if my SQL Server needs more memory?
or maybe it doesn't


texts: SQL Server : If a query is cancelled, where to get data on it?

texts:
Suppose am tracking a query using sys.dm_exec_query_stats, if the query ends fine, then we can find the relevant statistics in sys.dm_exec_query_stats. But if a query is cancelled, the statistics of that query (i.e. how long was it executing before being cancelled by the user, its I/O usage, and so on) is not there in sys.dm_exec_query_stats.

Is there any other place to get this info?


texts: See if an JSON array in MySQL contains an object whose key holds a specific date

texts:
i'm trying to find out if there is a row which contains a specific date inside a JSON array
Let's say my data looks like this:
Table applications:
id | application_id | data

# Rows
1 | 1 | [{"data" : ["some", "data#1"], "date": "2016-04-21"}, {"data" : ["other", "data#1"], "date" : "2016-04-22"}]
2 | 2 | [{"data" : ["some", "data#2"], "date": "2016-04-21"}, {"data" : ["other", "data#2"], "date" : "2016-04-26"}]
3 | 1 | [{"data" : ["some", "data#3"], "date": "2016-04-22"}, {"data" : ["other", "data#3"], "date" : "2016-04-26"}]
4 | 3 | [{"data" : ["some", "data#4"], "date": "2016-04-26"}]

How can I find all the applications whose data contains the date '2016-04-26'?
So basically I can do this:
select id, json_extract(`data`, "$[*].date") from applications

Which returns:
1 | ["2016-04-21", "2016-04-22"]
2 | ["2016-04-21", "2016-04-26"]
3 | ["2016-04-22", "2016-04-26"]
4 | ["2016-04-26"]

But if try to use json_extract in the WHERE clause I can only use it if I explicitly tell the array's key in the json_extract's path argument, like so:
select * from applications where json_extract(`data`, "$[0].date") = "2016-04-26"

which correctly returns the row with id 4.
But if I try to use a wildcard in the path then it no longer works:
select * from applications where json_extract(`data`, "$[*].date") = "2016-04-26"

this should return the rows 2, 3, 4.
I tried many other options/variations but I can't seem to find a way to structure the query correctly.
Is something like this even possible with the current implementation of MySQL JSON?


texts: How to track down app name for query in query plan cache?

texts:
I found a query in the plan cache that needs parameterizing.  Not sure which app created this query.  Is there a good way in SQL to track down the app that put a given query in cache?
sql2014


texts: SQL Server : Is it necessary to disable SA user for security issues?

texts:
We have a website that will published in national grade with millions of users with Microsoft Sql Server database.

I need to know is it necessary to disable SA login for security issues like DOS attacks ?

Thank you and sorry if question have problems <3.


texts: Why is it faster to do four simple selects instead of one select with left joins?

texts:
I need to find a single value from a table A containing three foreign keys to two other tables B and C.

For the sake of experiment, I tested two ways to query the value:

Multiple queries:

declare @start int = (select top 1 [Id] from [B] where [Day] = '2015-01-01')
declare @end int = (select top 1 [Id] from [B] where [Day] = '2017-06-14')
declare @category int = (select top 1 [Id] from [C] where [Title] = 'Hello, World!')

select top 1 [Name]
from [A]
where [StartId] = @start
    and [EndId] = @end
    and [CategoryId] = @category
    and [Day] = '2016-05-27'

Single query:

select top 1 [Name]
from [A]
left join [B] as [BStart] on [BStart].[Id] = [A].[StartId]
left join [B] as [BEnd] on [BEnd].[Id] = [A].[EndId]
left join [C] on [C].[Id] = [A].[CategoryId]
where [BStart].[Day] = '2015-01-01'
    and [BEnd].[Day] = '2017-06-14'
    and [C].[Title] = 'Hello, World!'
    and [A].[Day] = '2016-05-27'

I was surprised that the execution plan indicates that the single query is more expensive than multiple queries. When doing all five selects together, the one with left joins indicates 53%. The other four queries indicate 12% each.

Those are the execution plans:

declare @start int = (select top 1 [Id] from [B] where [Day] = '2015-01-01')`

declare @end int = (select top 1 [Id] from [B] where [Day] = '2017-06-14')

(Same as below)

declare @category int = (select top 1 [Id] from [C] where [Title] = 'Hello, World!')

select top 1 [Name]
from [A]
where [StartId] = @start
    and [EndId] = @end
    and [CategoryId] = @category
    and [Day] = '2016-05-27'

select top 1 [Name]
from [A]
left join [B] as [BStart] on [BStart].[Id] = [A].[StartId]
left join [B] as [BEnd] on [BEnd].[Id] = [A].[EndId]
left join [C] on [C].[Id] = [A].[CategoryId]
where [BStart].[Day] = '2015-01-01'
    and [BEnd].[Day] = '2017-06-14'
    and [C].[Title] = 'Hello, World!'
    and [A].[Day] = '2016-05-27'

Why is the single query with left joins slower than the first approach?


texts: MS Access displays alle columns in SQL Server tables as "deleted"

texts:
I have created an ODBC connection to a SQL Server 2005.

When I link to the tables from MS Access, some tables have only "deleted" data.

This means the rows and columns are displayed, but all data seems "deleted" ("Geloscht" in the image)

I find it remarkable that some of the tables of the same database work fine.


texts:
I want to connect to mysql over socket.

This is what I'm trying to do:

and it's not working.

This is how it works in mysql workbench:


texts: Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

texts:
We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table.

The table in question is an InnoDB table and the innodb_file_per_table option was (and still is) enabled during table creation.

As the manual notes:

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

We cannot afford the table being locked, since new records are inserted continuously in its production environment.

Hopefully there exists some workaround for the obvious problem; a reliable way to OPTIMIZE the table while dealing with the locking problem, that have been used in a production environment.

Maybe creating a temporary table and renaming it, but without the possibility of losing new records while the copying is being performed?


texts: Postgres lag() function has huge impact on query


texts:
In course of a change I migrated a database from Oracle 12.1 to MSSQL 2016.
Since the two databases handle date differently, I now need to move the fields from char(26) to datetime2.
Output format is e.g. 2011-05-30-13.06.56.824000 What I need is Type 21: 2016-04-01 10:20:56.8950000.
I already tried using

SELECT CONVERT(
           DATETIME2
          ,LEFT (EXP_DATE ,10) + "" +
           REPLACE (SUBSTRING(EXP_DATE ,12 ,8) ,"." ,":") + RIGHT(EXP_DATE ,7)
          ,21
       ) AS DateFromString
FROM   My-TABLE;

but this does not work (wrong column name):

  Message 207, level 16, status 1, line 1 Invalid column name " ".
  Message 207, level 16, status 1, line 1 Invalid column name ".".
  Message 207, level 16, status 1, line 1 Invalid column name ":".

Can someone help me?


texts: MongoDB 3.4 - Add nodes to Replica Set when only one is available

texts:
I originally had a Replica Set comprised of three nodes on different servers. As part of a contingency plan, I tested how long it would take to hot-swap from using a replica set to only using one node, in case one of our data servers went offline.

I followed the steps listed in the Mongo docs here: https://docs.mongodb.com/manual/tutorial/remove-replica-set-member/

I manually removed the others nodes from the cfg.members array and performed a rs.conf() to get the other node working properly. This seemed like the fastest way to restore our application, with writes being possible again after ~7 minutes of downtime.

Now, I'm at the point where I have one node in the replica set, with unsynced writes, and I need to add the other two nodes back. How do I go about adding these two nodes, while maintaining the writes committed during the hot-swap? I can't just execute rs.conf() since I am unable to hold elections in my replica set. I apologize if this question is a duplicate, but I was unable to find any satisfactory answers.


texts: Cardinality showing the nature of relationships among tables

texts:
Lets say there's a table named Department and a table named Instructor. We've conditions that says :

A Department can have many instructors.
An instructor can be associated to only single department.

Now the point is, if seen from the Department's angle, the relationship is of type "one to many"(coz one department can accommodate many instructors ) but if seen from instructor's angle, then the relationship is of type "one to one"(coz, one instructor can join only one department).

If we implement "one to many"( from department's angle) by making a foreign key in instructor table that would refer to department table, e.g. :

Instructors table :

create table instructors(inst_id number primary key not null, inst_name varchar2(30) not null,
dept_id number constraint dept_id_fk references department(dept_id) constraint not null);

Department Table :

create table department(dept_id number primary key not null, dept_name varchar2(15) not null);

Does these statements sufficiently satisfies both "one to one"( from instructor's table angle ) and "one to many"(from department's angle) ?

or

Do we need to change or SQL code to introduce "one to one" aspect too ?


texts: What Type of Join to Use in this Search?

texts:
I have the following query that is bound to a datagrid:

select
  distinct  a.Person_ID,
            a.Person_Last_Name,
            a.Person_DOB,
            CASE
              WHEN a.Person_Last_Name IS NULL
                THEN ''
              ELSE CONVERT(varchar, a.Person_Last_Name)
            END
            + ', ' +
            CASE
              WHEN a.Person_First_Name IS NULL
                THEN ''
              ELSE CONVERT(varchar, a.Person_First_Name)
            END
            + ' ' +
            coalesce(a.Person_Suffix,'')
            + ' ' +
            coalesce(a.Person_Middle_Name,'')
            +
            CASE
              WHEN a.Person_Archived='T'
                THEN ' (Archived)'
              ELSE ''
            END
            +
            CASE
              WHEN a.Person_Deceased='T'
                THEN ' (DECEASED)'
              ELSE ''
            END AS PersonName
FROM PERSON a
  LEFT OUTER JOIN COMPANY c
    ON a.Person_Company_ID = c.Company_ID

I need to also pull up an image for each person when one is available (but display 'None' when no image is available). There are potentially multiple images for each Person - I only want to select the first one. However, there are also possibly no images on file, in which case a default value should be populated. Here is the current SQL statement to fetch images for Persons:

SELECT
  PImage_Path
FROM PIMAGE
  WHERE PImage_Subject_ID=@Subject_ID

When no images are available, however, I would like it to return 'None' as the value of PImage_Path and not restrict the original select statement from finding Persons with no image. What type of join should I use? How would this query look?


texts: How to write search query to retrevie the rows with biggest DateTime <= 2010102248 in SQL

texts:
I am creating a table and now trying to create my first Search Query.

I have created the table successfully like this:
 CREATE TABLE testTable
 (
   DateTime INT    NOT NULL, /*yyyyddhhmm, 2010102345*/
   FeatureNbr      SMALLINT  NOT NULL,
   Val FLOAT(53)   NOT NULL
 );

I have successfully inserted values to the table like this:
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102245, 0, 12.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102245, 1, 13.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102245, 2, 14.56789);

 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102246, 0, 15.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102246, 1, 16.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102246, 2, 17.56789);

 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102247, 0, 18.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102247, 1, 19.56789);

Now I have a little problem and questions. I am trying to create a search query to retreive ALL FeatureNbrm,Val,DateTime where the biggest datetime is found <= 2010102248 for each of the three features. The lines that should be retrevied would then be the below 3 lines, - for ALL the three features which are: 0,1,2 (We don't know how many features it is. We just want ALL of them/rows with the biggest datetime)

2010102246, 2, 17.56789
2010102247, 0, 18.56789
2010102247, 1, 19.56789

But I only retrieve those lines in this format for the features: 0 and 1
2, 2010102246, 17.56789 <-- is missing
0, 2010102247, 18.56789
1, 2010102247, 19.56789

The code I use is the below but I don't understand how I should write this correctly?
SELECT FeatureNbr, DateTime, Val
FROM
  testTable
WHERE
     testTable.DateTime=
      (
        SELECT
          MAX(DateTime)
        FROM
          testTable
        WHERE
          DateTime <= 2010102248
      )


texts: MySQL security group view permission query

texts:
I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups.

record

+----+-------------------+
| id |    record_data    |
+----+-------------------+
|  1 | Some sample data  |
|  2 | More data         |
|  3 | Some more data    |
+----+-------------------+

groups

+----+-------------+
| id | description |
+----+-------------+
|  1 | Group A     |
|  2 | Group B     |
|  3 | Group C     |
|  4 | Group D     |
+----+-------------+

user

+----+------+
| id | name |
+----+------+
|  1 | Levi |
|  2 | Bill |
|  3 | Sam  |
|  4 | Jen  |
|  5 | Amy  |
+----+------+

I have created lookup tables to match the record to the groups that have permission to view them. I have also created a lookup table that matched the users with the groups they are members of.

record_group

+----+-----------+----------+
| id | record_id | group_id |
+----+-----------+----------+
|  1 |         1 |        2 |
|  2 |         1 |        4 |
|  3 |         2 |        1 |
|  4 |         3 |        2 |
|  5 |         3 |        3 |
|  6 |         3 |        4 |
+----+-----------+----------+

So in the table above, record one is viewable by users in group 2 and 4. Record 2 is viewable by users in group 1, and record 3 is viewable by users in group 2,3 and 4.

user_group

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       1 |        2 |
|  3 |       1 |        4 |
|  4 |       2 |        1 |
|  5 |       2 |        4 |
+----+---------+----------+

Above, User 1 is in group 1,2 and 4. User 2 is in group 1 and 4.

I am trying to create a query that will show all the records that a particular user has the correct group permissions to view. Example. I want to list all the records that user ID 2 can view and ignore the others.

The query for user 2 should return record 2,3 and 6

I need to do this in a single query as it will be plugged into an existing framework.

My understanding is that I can do this with the MySQL IN function but I cant seem to get it to work with two sets, example (Select id from member_groups) IN ()

So my Question, how do I build a MySQL query that selects all the records user one has permissions to view?

I'm sorry I cant provide more information or example query, I'm not sure how to start.

SQL Fiddle http://sqlfiddle.com/#!9/e40ee6/1


texts: How can I avoid breaking Postgres sync replication when building a large index?

texts:
We have pairs of Postgres 9.2 servers using synchronous replication. Sometimes rebuilding an index on a large table will break replication due to WAL segments not being kept around long enough. We are a bit constrained for space so don't want to allocate a bunch more for WAL segments.

Is there any way to build the index "incrementally" instead of in one big transaction? Any other ways we could handle this?


texts: MariaDB slave replication server from many sources?

texts:
can I have one server, which is slave for like 500 masters? All the masters have different content, but they inserting is like once per minute, so I think 8 core slave should be sufficient for it.
And also can I have one master database on this slave server? I don't have to, but it would reduce our servers costs.
Thanks for answering!


texts: Fast way to drop schema with large amount of objects

texts:
for the purpose of testing I have to drop a schema with a large amount of objects often. It consists of > 2000 tables, a large amount of foreign key and check constraints, triggers views and so on. All tables are within the same tablespace, but the tablespace is used by other schemas too.

I did try admin_drop_schema procedure at first, but it takes a lot more time than parsing all schema catalogs, generate all drop statements and execute it as one sql script. The script runs under 45 minutes and the procedure more than a hour. Further more the procedure locks to many things.

What is the fastest way to drop such a schema?


texts:
I'm looking for a good explanation of terminology for No-SQL.
I have some colleagues that won't get the point, and for the sake of advancing i need to have definitions of the names.. Though this seems real hard to find.

let's see a Database is a database, talking about No-SQL or SQL it keeps the same name. People do understand that.. But if you start looking on the internet.. there are people that say that you have: "a database with documents", "a database with rows and each row is a document" and "a document has rows",  It's real confusing if you ask me..

So could anybody give a good explanation of the terminology that exists in No-SQL, to improve communication about the subject. And also how this compares to the traditional SQL terminology?(like a row is a document? a table.. etc..)

EDIT
Can anybody confirm that this is the relation between the terms:
A Database has multiple documents.
In this case each documents will be the same as a row and the specification of an Table like in SQL does not exist. you could see all the tables with the same type as a table, if i'm right....?

EDIT 2

I'm specifically looking for couchDB.


texts: SQL server windows authentication through network

texts:
I am using sql server 2008 r2.There are about 50 pc in my office connected in network by using LAN.In a sql server 2008 r2 express(installed in windows 8) i have created all users windows account with same name as their name in their PC and then created windows authentication user in sql server. Now all the users are able to connect to sql server using windows authentication through SSMS.

But now i am trying implement same for the another SQL server 2008 r2 express which is installed in windows xp sp3. So i created one windows authentication user of network PC. But it is not working when i try to connect to sql server using windows authentication from network pc  message comes like "Login failed for user 'PC91\Guest'. " It is not recognizing the windows account of my pc. why it happens? Please tell me a solution.

UPDATE:

SQL server and browser is running and sql server is already configured to use remote connections.Also i can ping the PC.

My doubt is is it allowed to connect sql server in another pc by creating same account as in sql server installed PC?

Let me explain what i did below:

In sql server installed pc(windows XP) i created windows login 'test'.Then i created sql windows authentication login using 'test' windows account.

In another PC i created windows user account 'test'(same as in pc having sql server,with same password) so that i have same user account in both PC. Now from this PC ,i connect to sql server of another PC by using windows authentication('test'). But it worked properly for sql server which is installed in windows 8 and windows 7.But for the sql server installed in XP it didn't work(I tried in two instance in two different windows XP machine). So i doubt if this feature is allowed in windows XP or i am going wrong in anyway(may be any settings missing)?


texts:
I have this query that I can't seem to speed up at all. My running theory is that I need to fix some indexes or something.

I am running the database through the Azure and that does auto indexes, so I haven't had a whole lot of experience with indexes.

When I try to take stuff away, to see if it goes any faster, I get a little bit of speed. But when I take anything important that would probably speed everything up, I need to take out part of the WHERE statement and then I end up with a crap ton more rows and the speed is way worse.

So, here is my Paste The Plan

If anyone has any suggestions I would love to hear them.

Thanks in advance.


texts: Mysql: [match() against()] full text search order full field value match first

texts:
I am doing a full-text search against a WordPress database, I am using match() against() to test the search term against multiple fields from multiple tables, all is working great, but I want to improve the order so when a field value is exactly the same as the search term it comes first before any other matches.
This is the current query that searches the database for the term traduction:
    SELECT  `ID`
        FROM  `wp_posts`
        JOIN
        (
            SELECT  `post_id`, `meta_key`, `meta_value`
                FROM  `wp_postmeta`
                WHERE  `meta_key` IN ('definition', 'note', 'definition_en',
                            'observation', 'الملاحظة', 'التحديد')
        ) AS pm  ON `wp_posts`.`ID` = pm.`post_id`
        JOIN
        (
            SELECT  `wp_terms`.`term_id`, `name`, `object_id`
                FROM  `wp_term_relationships`
                JOIN  `wp_term_taxonomy`  ON `wp_term_relationships`.`term_taxonomy_id` = `wp_term_taxonomy`.`term_taxonomy_id`
                JOIN  `wp_terms`  ON `wp_term_taxonomy`.`term_id` = `wp_terms`.`term_id`
                WHERE  `taxonomy` IN ('terme', 'terme_arabic', 'terme_english',
                            'sous-domaine')
        ) AS td  ON `wp_posts`.`ID` = td.`object_id`
        WHERE  (MATCH (`post_title`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
            OR  MATCH (`meta_value`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
            OR  MATCH       (`name`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
               )
        ORDER BY  MATCH (`post_title`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE) DESC

In the results, the post with the title traduction is ordered after other posts with titles like traduction didactique, aide à la traduction,...
How can I modify the query so exact field value matches rank higher than other matches?


texts: Is it possible to have a valid backupset with null position?

texts:
In database restore process I need to rely on position column exists in backupset record to restore the latest backupset.

Restore the latest backup on the database

I noticed sometimes position is null. What does cause this? In msdn it's said that Position can be null, but no more explanation on under what condition it can happen.
Does it mean the backup file is corrupted?
Any idea?

Thanks.


texts: Indexes for SQL query with WHERE condition and GROUP BY

texts:
I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow.

My query:

SELECT group_id
FROM counter
WHERE ts between timestamp '2014-03-02 00:00:00.0' and timestamp '2014-03-05 12:00:00.0'
GROUP BY group_id

The table currently has 32.000.000 rows. The execution time of the query increases a lot when I increase the time-frame.

The table in question looks like this:

CREATE TABLE counter (
    id bigserial PRIMARY KEY
  , ts timestamp NOT NULL
  , group_id bigint NOT NULL
);

I currently have the following indexes, but the performance is still slow:

CREATE INDEX ts_index
  ON counter
  USING btree
  (ts);

CREATE INDEX group_id_index
  ON counter
  USING btree
  (group_id);

CREATE INDEX comp_1_index
  ON counter
  USING btree
  (ts, group_id);

CREATE INDEX comp_2_index
  ON counter
  USING btree
  (group_id, ts);

Running EXPLAIN on the query gives the following result:

"QUERY PLAN"
"HashAggregate  (cost=467958.16..467958.17 rows=1 width=4)"
"  ->  Index Scan using ts_index on counter  (cost=0.56..467470.93 rows=194892 width=4)"
"        Index Cond: ((ts >= '2014-02-26 00:00:00'::timestamp without time zone) AND (ts <= '2014-02-27 23:59:00'::timestamp without time zone))"

SQL Fiddle with example data: http://sqlfiddle.com/#!15/7492b/1

The Question

Can the performance of this query be improved by adding better indexes, or must I increase the processing power?

Edit 1

PostgreSQL version 9.3.2 is used.

Edit 2

I tried @Erwin 's proposal with EXISTS:

SELECT group_id
FROM   groups g
WHERE  EXISTS (
   SELECT 1
   FROM   counter c
   WHERE  c.group_id = g.group_id
   AND    ts BETWEEN timestamp '2014-03-02 00:00:00'
                 AND timestamp '2014-03-05 12:00:00'
   );

But unfortunetly this didn't seem to increase the performance. The Query Plan:

"QUERY PLAN"
"Nested Loop Semi Join  (cost=1607.18..371680.60 rows=113 width=4)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Bitmap Heap Scan on counter c  (cost=1607.18..158895.53 rows=60641 width=4)"
"        Recheck Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        ->  Bitmap Index Scan on comp_2_index  (cost=0.00..1592.02 rows=60641 width=0)"
"              Index Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Edit 3

The query plan for the LATERAL query from ypercube:

"QUERY PLAN"
"Nested Loop  (cost=8.98..1200.42 rows=133 width=20)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Result  (cost=8.98..8.99 rows=1 width=0)"
"        One-Time Filter: ($1 IS NOT NULL)"
"        InitPlan 1 (returns $1)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan using comp_2_index on counter c  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        InitPlan 2 (returns $2)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan Backward using comp_2_index on counter c_1  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"


texts: Postgresql server is running, but service appears stopped

texts:
I am running Postgresql 9.4 as a service on Windows Server 2008 R2. It usually works as a charm, but now there is a situation when the server is apparently running (I can connect to it, Task Manager shows the running processes), but the Windows Service appears stopped.

I tried using pg_ctl, but I get:

$>pg_ctl restart -D c:\psqldata94
pg_ctl: old server process (PID: 1436) seems to be gone
starting server anyway
server starting
2016-02-23 09:59:00 CET  LOG:  could not bind IPv6 socket: No error
2016-02-23 09:59:00 CET  HINT:  Is another postmaster already running on port  5432? If not, wait a few seconds and retry.
2016-02-23 09:59:00 CET  LOG:  could not bind IPv4 socket: No error
2016-02-23 09:59:00 CET  HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2016-02-23 09:59:00 CET  WARNING:  could not create listen socket for "*"
2016-02-23 09:59:00 CET  FATAL:  could not create any TCP/IP sockets

$>pg_ctl stop
pg_ctl: PID file "c:/psqldata94/postmaster.pid" does not exist
Is server running?

I tried looking for help online, but the only thing I could find, was this ominous post at psql-admin mailing list.

It's exactly as the author of the post describes: the server is running, but it does not respond to commands. I suppose I can restart the underlying Windows server, but obviously this could have grave consequences if I just kill the running processes.

Does anybody know how to gracefully stop the server under this circumstances?


texts: PostgreSQL copy text type column to text[] array type column

texts:
I have two columns, material_size (text type) and material_size_temp (text[] array type).

When I ran UPDATE parts SET material_size_temp[0] = material_size; I would get values like [0:0]={".021 x 2.450"} instead of {".021 x 2.450"}.

How do I clone the text type column to text[] array type column?


texts: SELECT with comparison operator, based on an order, skipping row(s)

texts:
I have a resultset from a SELECT query, based on a certain ORDER BY condition, and I’m trying to check the integrity of the order in which the resultset is rendered. First, I’m specifying a LIMIT, on the basis of which, the resultset size is limited. Then, I’m selecting a random PK value from the resultset, get a particular row and use the comparison operator on that row value(s), for the next SELECT. Something like this(Let’s suppose a is the random row from above):

SELECT * FROM tbl where tbl_columnX > a.fieldX and tbl_columnY > a.fieldY
ORDER BY certain fields

Basically, what I’m trying to do here, is to ensure that for a resultset (R) of certain order, when choosing a random PK value, and issuing a SELECT statement with comparison operator(s) on that PK based row value(s), the resulting set(S) contains the rows that are in R, after/before the PK, depending whether the order is ascending or descending.

It is basically working, except for a few cases where the next row(s) after the PK based row, have the same value(s) as the PK based row, in a few column(s). For example, these are the last few rows, from the resultset R:

ID(varchar)     VALUE(varchar)  DESC1(text)     DESC2(text)     NAME(text)
-------------   -------------   -------------   -------------   -------------
62E212AF            C09A        F/T/K              AH            AN
CAFFA217            C09AA       F/T/K 2            Ah            An
D90222CC            C09BA       F/T/K 2d           Ahod          Anod

Now, if I try to get a new resultset(S) after the ID 62E212AF, it skips the ID CAFFA217, and the first ID in the resultset is D90222CC. The similar pattern is repeated in a few other places. This is how I’m doing the order by, in this particular case:

order by
  NAME asc,
  VALUE asc,
  DESC1 asc,
  DESC2 asc,
  ID asc

I’d really appreciate some help with this.


texts: Which is faster to update thousands of table rows?

texts:
With stored procedures in PostgreSQL v10.1.2, which is the fastest way or which is better: to check if rows exists and then update or try to update directly with the possibility that no rows matching the condition are found? I need to check a lot of tables with the same condition, tables are not 'denormalized', I made a couple of tests and sometimes option 1 is faster in other cases option 2 ...

Option 1:

IF EXISTS ( SELECT  1
FROM   public.table1
WHERE  column1 = 'oldvalue' )
THEN
   UPDATE public.table1
   SET   column1 = 'newvalue' , date_update= ....
   WHERE  column1 = 'oldvalue';
END IF ;

Option 2:

UPDATE public.table1
SET   column1 = 'newvalue' , date_update= ...
WHERE  column1 = 'oldvalue';

Option 3:

perform FROM   public.table1 WHERE  column1 = 'oldvalue' ;
if found then
   UPDATE public.table1 SET column1='newvalue', date_update = ... WHERE column1 = 'oldvalue';
end if ;

I made:

BEGIN;
EXPLAIN ANALYZE VERBOSE select a_test_w_perform();
ROLLBACK;

for every option and the time average vary, results in time are something like:

Option 1

Query returned successfully in 311 msec. / 423 msec./ 242 msec./ 367

Option 2

Query returned successfully in 308 msec. / 307 msec./311 msec./ 200

*Option 3

Query returned successfully in 204 msec./ 279 msec./451 msec. / 230


texts: psql does not require a password on macOS with Postgres.app

texts:
When opening the psql console, there is no password prompt. This behaviour is the same for all postgres users. I can basically do everything without a password. This seems rather unsafe.

How do I enable password checking? I'm running macOS Mojave and use Postgres.app with postgresql version 10.

This is the command I use:

psql -Umyuser # this user has a password set


texts: Subselect vs join: Are both SQL queries equal?

texts:
I have this db:

DROP TABLE auth_user_groups;
DROP TABLE auth_user;
DROP TABLE auth_group;

CREATE TABLE auth_user (
    id integer PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

SQL statement using join:

select username from auth_user, auth_user_groups, auth_group where
  auth_group.name='group1' and
  auth_user_groups.group_id=auth_group.id and
  auth_user.id=auth_user_groups.user_id;

SQL statement using subselect:

select username from auth_user where id in (
  select user_id from auth_user_groups where group_id in (
    select id from auth_group where name ='group1'));

Is the result of both SQL statements equal for all possible data sets?

I use PostgreSQL, but AFAIK this should not matter for this question.


texts: Is it generally faster to select into a temp table than selecting into an actual table?

texts:
I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity?
I thought I recall it saying something special about tempdb and storing the data in memory?


texts: Merge replication - corrupt row continually trying to INSERT

texts:
We have "push" SQL replication sent up, with one publisher and three subscribers. Two of the three are working fine, but one is continually causing trouble. I went through the pain and heartache of reinitialising the subscription at the weekend, and just a few days later it's stuck again.

This time, the error I'm seeing in replmerg.log (after turning up the verbosity) is:

  The merge process could not replicate one or more INSERT statements to
  the 'Publisher'. A stored procedure failed to execute. Troubleshoot by
  using SQL Profiler.

I've tracked this down to a single table in the database: for some reason, an obviously corrupt row is continually trying to be INSERTed into the table, with a rowguid of 00000000-0000-0000-0000-000000000000. DBCC CHECKDB shows no sign of corruption.

Here's the row:

If I try to get rid of the bad row on the Publisher:

delete from mytable
where rowguid like '00000000-0000-0000-0000-000000000000'

I get the error:

  Msg 2601, Level 14, State 1, Procedure
  MSmerge_del_590C419D78ED4A36A40292C23CF5F67B, Line 68 Cannot insert
  duplicate key row in object 'dbo.MSmerge_tombstone' with unique index
  'uc1MSmerge_tombstone'. The duplicate key value is (3095029,
  00000000-0000-0000-0000-000000000000). The statement has been
  terminated.

If I manually remove the row from MSmerge_tombstone, it comes back after the next replication cycle (which is continuous, so typically 60 seconds later).

I really don't want to have to reinitialise this subscription again. Is there any way of purging this set of corrupt transactions out of the "queue", or otherwise returning the replication back to normal?


texts: Update a PostgreSQL row with last user and timestamp

texts: I would like to track who is the last user who updated the rows of a PostgreSQL table (v9) and when. This webpage explains how to do it with a trigger regarding the timestamp (I did not try it yet), I was wondering if something similar could be done to keep the user name.


texts:
Brent Ozar had an interesing newsletter on 2015-06-22; How do you manage dbas measuring backups, where he thinks that a good DBA should check when

the last backup was done
it was last restored
the last CheckDB was done

It turned out to be a good exercise for me. Not only did I find two databases that were not backed up, I also discovered that we do have databases where we have never checked that we can restore the backup.

So;
I have provided one solution to the task in the answer section. We have the convention that database names are unique and my script lists production server and test server(s), so I can compare them.
Do you have a solution that is better?

Best regards,

Henrik


texts: Setting connection collation in master for slave replication

texts:
I am trying to replicate a MySQL 8 server (master) to MySQL 5.7 (slave) instance. I know it is not officially supported, but the thing is we need to use a service that has only supports MySQL 5.7.

I know there are a few collations that are not available in MySQL5.7 which are present in MySQL8. For that, i converted all tables from utf8mb4_0900_ai_ci to utf8mb4_general_ci as our data values are supported by both, so I was able to do this without any consequences.

I have also set the following settings in my master's configuration:

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci; SET NAMES utf8mb4;'

which yields me the following: (I am not connected as superuser)

mysql80> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

Now I have narrowed it down to this that my collation_connection is the culprit because utf8mb4_0900_ai_ci is not part of MySQL57. Due to which I get the following error on slave:

Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file' on query. Default database: 'database'. Query: 'BEGIN'

Is there any way to force my master instance to use utf8mb4_general_ci instead of utf8mb4_0900_ai_ci?

Regards


texts: MySQL - cannot examine view definition - Error getting DDL for object - No database selected

texts:
I have a view that I can run, it returns a dataset, but whenever I try to alter the view or look at its definition, I get
Error getting DDL for object
No database selected

But I do have a database selected and I can run the view with results.
How do I recover the definition?  Please advise


texts:
I need a short explaining of clustered index.

What is a clustered index?
What are best practices for using the clustered index?


texts: Why do SQL Server uniqueidentifier values look different when converted to Db2?

texts: I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data from SQL Server to flat files. All the columns in my database (in SQL Server) that have the data type of uniqueidentifier are not extracted correctly by DCW. Any data that is of the uniqueidentifier type is written as random symbols and characters. What could be the reason?


texts: Moving SQL server 2008 to a new Server with all DBs, replication and Jobs

texts:
We have to move our Production SQL server 2008 to a new Server (with latest SKU and more RAM). So we need to move all DBs, replications setup (publisher) and all jobs, means everything :). Also we want to keep the SQL instance name, so that we do not have to change this SQL instance name in web.config of the all the web servers.

So could anyone please share the steps including verification if done this earlier.

Please let me know if any more information is required from my side


texts: Merge Replication query causing slow performance

texts:
I have three databases (one publisher, two subscribers) running merge replication and this morning noticed very slow performance. They sync on 10 second intervals but this morning it was taking much longer to see changes get pushed.

I checked to see which queries were running using this:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

and got this as a result:

    (@rowguid UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000')

DECLARE @gen1 BIGINT
DECLARE @gen2 BIGINT
DECLARE @gen3 BIGINT
DECLARE @gen4 BIGINT
DECLARE @gen5 BIGINT
DECLARE @gen6 BIGINT
DECLARE @gen7 BIGINT
DECLARE @gen8 BIGINT
DECLARE @gen9 BIGINT
DECLARE @gen10 BIGINT
DECLARE @gen11 BIGINT
DECLARE @gen12 BIGINT
DECLARE @gen13 BIGINT
DECLARE @gen14 BIGINT
DECLARE @gen15 BIGINT
DECLARE @gen16 BIGINT
DECLARE @gen17 BIGINT
DECLARE @gen18 BIGINT
DECLARE @gen19 BIGINT
DECLARE @gen20 BIGINT
DECLARE @gen21 BIGINT
DECLARE @gen22 BIGINT
DECLARE @gen23 BIGINT
DECLARE @gen24 BIGINT
DECLARE @gen25 BIGINT

SELECT @gen1 = 1262662

SELECT @gen2 = 1262669

SELECT @gen3 = 1262675

SELECT @gen4 = 1262680

SELECT @gen5 = 1262687

SELECT @gen6 = 1263078

SELECT @gen7 = 1263131

SELECT @gen8 = 1263140

SELECT @gen9 = 1263169

SELECT @gen10 = 1263171

SELECT @gen11 = 1263175

SELECT @gen12 = 1263233

SELECT @gen13 = 1263245

SELECT @gen14 = 1263252

SELECT @gen15 = 1263255

SELECT @gen16 = 1263270

SELECT @gen17 = NULL

SELECT @gen18 = NULL

SELECT @gen19 = NULL

SELECT @gen20 = NULL

SELECT @gen21 = NULL

SELECT @gen22 = NULL

SELECT @gen23 = NULL

SELECT @gen24 = NULL

SELECT @gen25 = NULL

SELECT TOP 100 mc.tablenick
    ,mc.rowguid
    ,mc.generation
    ,mc.lineage
    ,mc.colv1
    ,t.*
FROM [DATABASE_NAME].[dbo].[MSmerge_contents] mc
INNER JOIN [DATABASE_NAME].[dbo].[TABLE_NAME] t ON mc.rowguid = t.ROWGUIDCOL
INNER JOIN (
    SELECT @gen1 AS gen

    UNION ALL

    SELECT @gen2 AS gen

    UNION ALL

    SELECT @gen3 AS gen

    UNION ALL

    SELECT @gen4 AS gen

    UNION ALL

    SELECT @gen5 AS gen

    UNION ALL

    SELECT @gen6 AS gen

    UNION ALL

    SELECT @gen7 AS gen

    UNION ALL

    SELECT @gen8 AS gen

    UNION ALL

    SELECT @gen9 AS gen

    UNION ALL

    SELECT @gen10 AS gen

    UNION ALL

    SELECT @gen11 AS gen

    UNION ALL

    SELECT @gen12 AS gen

    UNION ALL

    SELECT @gen13 AS gen

    UNION ALL

    SELECT @gen14 AS gen

    UNION ALL

    SELECT @gen15 AS gen

    UNION ALL

    SELECT @gen16 AS gen

    UNION ALL

    SELECT @gen17 AS gen

    UNION ALL

    SELECT @gen18 AS gen

    UNION ALL

    SELECT @gen19 AS gen

    UNION ALL

    SELECT @gen20 AS gen

    UNION ALL

    SELECT @gen21 AS gen

    UNION ALL

    SELECT @gen22 AS gen

    UNION ALL

    SELECT @gen23 AS gen

    UNION ALL

    SELECT @gen24 AS gen

    UNION ALL

    SELECT @gen25 AS gen
    ) AS genlist ON mc.generation = genlist.gen
    AND genlist.gen IS NOT NULL
WHERE mc.generation >= 1262662
    AND mc.generation <= 1263270
    AND mc.tablenick = 7094001
ORDER BY mc.tablenick
    ,mc.rowguid

which I don't understand very well (note that I changed the database name and table name). The query stays in a running state but after about 500,000ms it seems like the query disappears for a few seconds then reappears.

When I check my sync status I get the following:

Anyone know what is causing this? Can I kill it?


texts: Is there to get space used in files for SQL Server database without negatively affecting IO?

texts:
I need to get the space used within each database file across large numbers (1000+) of databases on a regular basis without hitting disk IO too hard.

I thought I could simple call the FILEPROPERTY(@file, 'SpaceUsed') function or sp_spaceused - however, the disk IO spikes to near 100% for quite a few seconds when I loop over a large number of databases calling either of these functions.

I suppose that SQL Server must interally have some idea of how much space is used (or left) in its files so that it can auto-grow. I wonder if there is some way to get hold of these values (even if they are less accurate than FILEPROPERTY) without causing such as massive hit on IO?

Thanks :)

Update

I have also tried summing the total_pages from sys.allocation_units which seems to be another approach that SSMS uses - see here, but it's equally slow sadly.

Update 2

I realise I can also use DBCC showfilestats (and multiply the UsedExtents by 64), to get the used size of the database file and DBCC SQLPERF (LOGSPACE) to get the used size of the log - initial testing shows these to be better in terms of IO hit.


texts: Separate a column into its components based on another table

texts:
I'm struggling with a very difficult and complicated query here and I need your help.

I have two tables as you can see below:

1) ACCOUNT1:(ATYPCODE int, ATYPDESC varchar(50), ATLASTFLAG varchar(50))

some example data of this table are:

ATYPCODE    ATYPDESC    ATLASTFLAG
 3000          A            0
 19            B            0
 1170          C            0
 1178          D            1
 4000          AA           0
 18            BB           0
 2020          CC           1

Column ATLASTFLAG shows whether this record is the last level .
Table number two is

2) ACCOUNT2:(ATYPCODE int, AGLTCODE varchar(50), AGLTLVL int)

some example data of this table are:

    ATYPCODE      AGLTCODE          AGLTLVL
      3000        3000                1
      19          3000  19            2
      1170        3000  191170        3
      1178        3000  1911701178    4
      4000        4000                1
      18          4000  18            2
      2020        4000  182020        3

Column ATYPCODE is exactly the same as ATYPCODE column in the first table.Column AGLTCODE is bult based on the concatenation of ATYPCODEcolumn as you can see. The point is that the second level added to the first level with two spaces . so we have 3000+space+space+19+Other_levels and we have to separate this column exactly with 4 characters.
and the column AGLTLVL shows the level of each ATYPCODE .

what we want to see as a result is this :

ATYPCODE    AGLTCODE   AGLTLVL   ATYPCODE1     ATYPCODE2    ATYPCODE3     ATYPCODE4   ATYPDESC1  ATYPDESC2    ATYPDESC3      ATYPDESC4

and values for each column

ATYPCODE    = 1178
AGLTCODE    = 3000  1911701178
AGLTLVL     = 4
ATYPCODE1   = 3000
ATYPCODE2   = 19
ATYPCODE3   = 1170
ATYPCODE4   = 1178
ATYPDESC1   = A
ATYPDESC2   = B
ATYPDESC3   = C
ATYPDESC4   = D

and also the next record is

ATYPCODE    = 2020
AGLTCODE    = 4000  182020
AGLTLVL     = 3
ATYPCODE1   = 4000
ATYPCODE2   = 18
ATYPCODE3   = 2020
ATYPCODE4   = 2020(last value is repeated)
ATYPDESC1   = AA
ATYPDESC2   = BB
ATYPDESC3   = CC
ATYPDESC4   = CC(Last Values is repeated)

as you can see some records have 4 levels and some have 3 levels
since the structure of the destination table is fixed,for those with 3 levels , last value ATYPCODE3 and ATYPDESC3should be repeated for columns ATYPCODE4  and ATYPDESC4 .

the only part of query I was able to write was this cause we only nees to store the last level and nothing comes to mind for the rest of the query

SELECT b.ATYPCODE , b.AGLTCODE , b.AGLTLVL
FROM ACCOUNT1 a inner join
       ACCOUNT2 b on a.ATYPCODE = b.ATYPCODE
where a.ATLASTFLAG = 1


texts: Import/Exporting certain columns of a table across two databases and excluding duplicate emails in mysql

texts:
I have run into a problem with mysql queries:

Basically, I have two databases that both contain a table called tblclients however db1 has far more records than db2 and there are some duplicate records as well (the users that have email address in both databases are to be excluded from the operation). I intend to append db1 to db2 in a way that only certain columns get appended as long as the email address is not found.

I have done this mysql but it gives me an error. First it complains that no database is selected and even if I type use db1; before the actual statement, it generates another error:

ERROR 1064 (42000) at line 1: 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 'db1.tblclients (db1.tblclients.firstname, db1' at line 1

any though on this?

Someone told me to use mysqldump instead but I believe it could be done in a one line mysql query to be used in a bash script file.

thanks

insert into db1.tblclients (db1.tblclients.firstname,
db1.tblclients.lastname, db1.tblclients.companyname,
db1.tblclients.email,   db1.tblclients.address1,
db1.tblclients.address2,     db1.tblclients.city, db1.tblclients.state,
db1.tblclients.postcode,     db1.tblclients.country,
db1.tblclients.phonenumber, db1.tblclients.password,
db1.tblclients.authmodule, db1.tblclients.authdata,
db1.tblclients.status,
db1.tblclients.pwresetkey, db1.tblclients.pwresetexpiry,
db1.tblclients.emailoutput) values (select db2.tblclients.firstname,
db2.tblclients.lastname, db2.tblclients.companyname,
db2.tblclients.email,
db2.tblclients.address1, db2.tblclients.address2, db2.tblclients.city,
db2.tblclients.state, db2.tblclients.postcode, db2.tblclients.country,
db2.tblclients.phonenumber, db2.tblclients.password,
db2.tblclients.authmodule, db2.tblclients.authdata,
db2.tblclients.status,
db2.tblclients.pwresetkey, db2.tblclients.pwresetexpiry,
db2.tblclients.emailoutput where not exists (select db2.tblclients.email
where db2.tblclients.email=db1.tblclients.email));


texts: Is It Needed to Normalize of Date Columns


texts: How do I get the current and next greater value in one select?

texts:
I have a InnoDB table 'idtimes' (MySQL 5.0.22-log)
with columns

`id` int(11) NOT NULL,
`time` int(20) NOT NULL, [...]

with a compound unique key

UNIQUE KEY `id_time` (`id`,`time`)

so there can be multiple timestamps per id and multiple ids per timestamp.

I'm trying to set up a query where I get all entries plus the next greater time for each entry, if it exists, so it should return e.g.:

+-----+------------+------------+
| id  | time       | nexttime   |
+-----+------------+------------+
| 155 | 1300000000 | 1311111111 |
| 155 | 1311111111 | 1322222222 |
| 155 | 1322222222 |       NULL |
| 156 | 1312345678 | 1318765432 |
| 156 | 1318765432 |       NULL |
+-----+------------+------------+

Right now I am so far:

SELECT l.id, l.time, r.time FROM
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id
    WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

but of course this returns all rows with r.time > l.time and not only the first one...

I guess I'll need a subselect like

SELECT outer.id, outer.time,
    (SELECT time FROM idtimes WHERE id = outer.id AND time > outer.time
        ORDER BY time ASC LIMIT 1)
    FROM idtimes AS outer ORDER BY outer.id ASC, outer.time ASC;

but I don't know how to refer to the current time (I know the above is not valid SQL).

How do I do this with a single query (and I'd prefer not to use @variables that depend on stepping though the table one row at a time and remembering the last value)?


texts: Calculating Total Time Taken to Replicate

texts:
I wanted to know whether there are any open source tools or in built functions for generating replication metrics. Basically i wanted to calculate the time difference between changes in the Master vs Slave

For eg :

Suppose I insert a million rows in a table in Master, I want to store the total time it took to replicate the same in the standby.

The values in pg_stat_replication are all real time and once all the wal files are streamed it doesn't show the lag anymore, hence i need a way to store the final value. Is there any way of doing this??

I would really appreciate your inputs.