How can I efficiently represent inheritance down a tree? I have a database with three "levels" of objects: A, B, and C. Table A has around 100000 rows, B has 500000, and C has 2 million.

In a fit of stupidity, I designed the database to use joining tables AB and BC, even though these are 1:m and could have been represented as an ParentA attribute in B and a ParentB attribute in C. I did this because I thought at the time that these might be m:m.

Now, I also have a table D that has a m:m relationship with C. To simplify data management for users, it makes sense to allow them to relate D to A or B rather than directly to each C.

So, I have joining tables DA, DB, and DC, and a joining view DC2 that includes DC as well as the inherited DC relationships from joins of DA-AB-BC and DB-BC. I use a UNION ALL for this, and there's some business logic to prevent users from assigning the same D record at two levels.

The problem is, the performance of DC2 kinda sucks. All of the other tables have appropriate covering indexes for these joins, and the covering indexes are clustered. DC2 does not include tables A, B, C, or D, just the joining tables.

There are also tables E, F, G, and H that are similar to D in how they relate to A, B, and C.

What are some strategies I can use to improve performance of these inherited relationships?

Thought of already:

Making DC2 an indexed view is not an option since UNION is not permitted in an indexed view in MSSQL.
I could make DC2 a table and manage it with triggers, but this would be a pain, and I still have EC2, FC2, etc. to deal with. I can't make it where
Changing AB and BC into attributes of B and C might actually slow things down, since the joining tables are lighter and the main tables aren't joined to DC2 often.

I'd look at your indexed view the other way around:

Have table O for your objects, storing A, B and C, and a level field. Then create indexed views for A, B and C based on queries off O. Perhaps use a hierarchyid field to know the full tree for each record.

There's plenty more you could do, but this could be a useful start.


Long running DBCC CHECKDB I am on SQL 2014 Standard doing offline DBCC CHECKDBs (so on a box other than production). For the most part, my process is going pretty quick but for some reason I have this small DB (6gbs) and its taking hours to do the DBCC. Last time it ran it took 9 hours. It seems to freeze at different %'s completion when checking sys.dm_exec_requests. On this same server I'm doing CHECKDBs on databases in the terabytes range without a problem. I am using the following hints in the CHECKDB at this time (and, yes, eventually I'll do full instead of physical only)

WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY, TABLOCK

What could be messed up with this one DB that is causing it to take forever?

If your CHECKDB operations are getting blocked by FT CRAWL, a reasonable solution would be to turn full-text search off for the database after it has been restored. After all, it's not as if you are using DBCC CHECKDB to validate that full-text search is doing the right thing, or that you are restoring this copy of the database in order to facilitate FT queries.

To disable full-text search for a database named foo:

IF FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1
BEGIN
  EXEC foo.dbo.sp_fulltext_database @action = 'disable';
END

You probably don't need the conditional since we already know FT is installed, but it's good to carry along in case this server changes or you move your restores to a different place.


Help with tricky update statement I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.

I have two table variables:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
                       Measurement varchar(max), -- Grams, Kilograms, Liters, etc
                       MeasurementType varchar(max)); -- Weight, Volume, etc

Example:

Precidence | Measurement | MeasurementType
     2     |     G       |   Weight
     1     |     KG      |   Weight
     1     |     GAL     |   Volume
     2     |     L       |   Volume
     3     |     ML      |   Volume

DECLARE
   @Items TABLE(ItemType varchar(max),
                Quantity float,
                Measurement varchar(max),
                ToMeasurement varchar(max));

The @Items table can contain multiple measurements for the same ItemType. For each ItemType I need to identify the largest measurement, taking into account incompatible measurement types and update the ToMeasurement. The ultimate goal is to convert the Quantity of each ItemType to the largest measurement present in the @Items table so items of the same ItemType can be summed. I've already written conversion function and the sum operation.

Given the following table input:

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    NULL
Widget   |    1     |    KG       |    NULL
Widget   |    1     |    ML       |    NULL
Widget   |    1     |    L        |    NULL

Should be updated to:

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    KG
Widget   |    1     |    KG       |    KG
Widget   |    1     |    ML       |    L
Widget   |    1     |    L        |    L

I've rewritten the update multiple times and each time I come up short. At one point I had subqueries going four levels deep. I felt like I was getting close but it was getting so complex I couldn't see the forest for the trees. My most recent attempt is simpler but once again produces incorrect results:

UPDATE A
SET A.ToMeasurement = E.Measurement
FROM @Items A
     JOIN(SELECT C.ItemType,
                 D.Measurement
          FROM @Measurements B
               JOIN @Items C
               ON C.Measurement = B.Measurement
               JOIN @Measurements D
               ON D.MeasurementType = B.MeasurementType)E
     ON E.ItemType = A.ItemType;

I know I'm not even using the Precidence column, which is one of the reasons it is failing to produce the results I'm looking for.

Notes

Here's a query that produces the result I'm looking for (I think) but I'm still not sure how to turn it into an update statement:

SELECT A.ItemType,
       A.Quantity,
       A.Measurement,
       (SELECT TOP 1 M.Measurement FROM @Measurements M
       JOIN @Items C ON C.Measurement = M.Measurement
       WHERE M.MeasurementType = B.MeasurementType
       AND  C.ItemType = A.ItemType
       ORDER BY Precidence)ToMeasurement
FROM @Items A
     JOIN @Measurements B
     ON A.Measurement = B.Measurement;

The performance impact from multiple logical reads could be a result of your UDF.

Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.

http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

To replicate your scenario, I executed the following query:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
             Measurement varchar(max), -- Grams, Kilograms, Liters, etc
             MeasurementType varchar(max)); -- Weight, Volume, etc`

DECLARE
   @Items TABLE(ItemType varchar(max),
            Quantity float,
            Measurement varchar(max),
            ToMeasurement varchar(max));

insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L');

insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume');

Afterwards, I complied and executed the following update statement:

WITH Items_CTE AS
   (
        SELECT A.ItemType,
               A.Quantity,
               A.Measurement,
               (SELECT TOP 1 M.Measurement FROM @Measurements M
                JOIN @Items C ON C.Measurement = M.Measurement
                WHERE M.MeasurementType = B.MeasurementType
                AND  C.ItemType = A.ItemType
                 ORDER BY Precidence)ToMeasurement
       FROM @Items A
       JOIN @Measurements B
       ON A.Measurement = B.Measurement
    )
UPDATE @Items
SET ToMeasurement = cte.ToMeasurement
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;

The UPDATE statement uses a CTE and the results were as follows:

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    KG
Widget   |    1     |    KG       |    KG
Widget   |    1     |    ML       |    L
Widget   |    1     |    L        |    L


MySQL Syntax error when trying to create database trigger When Trying to create the below trigger, I am receiving a syntax error. It looks good to me and I'm having a terrible time trying to figure it out.

  DELIMITER $$
    CREATE TRIGGER trg_pic_hours
    AFTER INSERT ON CREW
    FOR EACH ROW
    BEGIN
    IF (NEW.CREW_JOB = 'Pilot') THEN
    UPDATE PILOT
    SET PIL_PIC_HRS = TEMP_PIL.PIL_PIC_HRS + CHARTER.CHAR_HOURS_FLOWN
    WHERE CHARTER.CHAR_TRIP = NEW.CHAR_TRIP
    AND PILOT.EMP_NUM = NEW.EMP_NUM;
    ENDIF;
    END$$
    DELIMITER;

ERROR 1064 (42000) at line 2: 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 '; END' at line 10

ENDIF;
END$$
DELIMITER;

-->

END IF;
END $$
DELIMITER ;

END IF is 2 words.
Have a space before the delimiter.


Changes made by TransactionA not seen by TransactionB eventhough TransactionA commits them. How is that possible? Is it possible for 2 non concurrent connections to see the same data for a record even as the 1st connection commits its changes?

Here is an example of what I am seeing from the audit logs:

ConnectionA starts TransactionA
    MasterRecordX gets locked for update.
    ...
    DetailRecordY is queried
    DetailRecordY is changed
    ...
ConnectionA commits TransactionA

---

ConnectionB starts TransactionB
    MasterRecordX gets locked for update.
    ...
    DetailRecordY is queried
    DetailRecordY is changed
    ...
ConnectionB commits TransactionB

The problem is that when DetailRecordY is queried on TransactionB, it does not see the changes made by TransactionA even though MasterRecordX is locked at the begining of both transactions.

The audit logs also show that the 2 requests were non concurrent (around a second between one and the other).

Also, DetailRecordY is not queried before MasteRecordX gets locked. The database is mySQL and the isolation level is the default (REPETABLE READ).

The problem was related to the fact that there was a query being executed before MasterRecordX got locked. Here is what happened:

ConnectionA starts TransactionA
RecordW is queried.
MasterRecordX gets locked for update.
...
        ConnectionB starts TransactionB
        RecordW is queried.
        MasterRecordX gets locked for update.
        ...
DetailRecordY is queried
DetailRecordY is changed
...
ConnectionA commits TransactionA
        DetailRecordY is queried
        DetailRecordY is changed
        ...
        ConnectionB commits TransactionB

Because RecordW on TransactionB was queried before DetailRecordY was changed and commited in TransactionA, the changes were not seen by TransactionB. Because of the isolation level (REPEATABLE READ), once a query is executed within a transaction, no later data changes are seen within that transaction.


Can WAITFOR increase CXPACKET waits? If I have some T-SQL code like this:

While @done<1
Begin
WAITFOR DELAY '00:00:30';
Set @done=<some select statement>
End

Is this likely to increase waits for CXPACKET? Presumably the other processor(s) will be waiting whilst WAITFOR is running?

When you issue a WAITFOR command, the task will start having a wait_type of WAITFOR.  This is a benign wait type and can be ignored.

Likewise, when this task enters the suspended state it will get off of the scheduler (processor) so it won't be taking up worker time.  We can see this with a simple example:

use AdventureWorks2012;
go

waitfor delay '00:10:00';
go

In another window you can run the following:

select
    session_id,
    command,
    status,
    wait_type
from sys.dm_exec_requests
where command = 'waitfor';

My results are as follows:

session_id  command   status      wait_type
54          WAITFOR   suspended   WAITFOR

As for your question of how this will reflect in sys.dm_os_wait_stats, upon the WAITFOR wait type's completion, it will then increment the WAITFOR type in sys.dm_os_wait_stats for the duration that the task was waiting for:

select *
from sys.dm_os_wait_stats
where wait_type = 'waitfor';

But as said previously, the WAITFOR wait type is benign and can generally be ignored.


How can I improve my table design for different types of an entity? Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables for different types of Client, each having fields applicable to the respective type and have one master table referencing all of them and have fields applicable to all types.

Currently, I come up with the following design:

But I don't think my design is efficient enough (or even correct and free of errors). What would you suggest?
Also, if this is important in any way, I am planning to utilize MariaDB.

Sorry, but is an entrepreneur not also an individual? You should record the name etc. in individual, then the additional info in another table. Now you may have one person double. And stuff like address should be linked to "entity" - not even client, you may end up having suppliers there too.

The "Data Model Resource Book" has perfect diagrams for address and contact handling including relationships of entities with each other. There is a lot of detail issues you get seriously wrong in your approach.


Service Account Groups in SQL Server 2012 I was tracking down some permissions issues on SSRS 2012 and I started looking for the local groups that I am used to seeing for SQL Server.  I noticed something I had not seen before, the groups have changed in 2012.

I have checked on some of my other 2012 instances and am seeing similar.  Is everyone seeing this?

I have checked BOL but am a finding it a little unclear.  I found this information from Bob Beauchemin.  It looks like he is seeing the same thing I did.

My question is, does everyone else running 2012 see the same thing?

Jon,
From what I've seen, the local Windows groups are going away in favor of using the NT SERVICE SIDs on W2K8R2 - http://msdn.microsoft.com/en-us/library/ms143504.aspx#Serv_SID

HTH,
Nancy Hidy Wilson


Convert Date format into bigint format in postgresql How can I convert date format into bigint format in postgresql?
for eg: '2018-08-20' to '20180820'

postgres=# create table dttest ( dt date ) ;
CREATE TABLE
postgres=# insert into dttest values ( '2018-08-20' ) ;
INSERT 0 1
postgres=# select * from dttest;
     dt
------------
 2018-08-20
(1 row)

postgres=# select to_char( dt, 'YYYYMMDD' ) from dttest ;
 to_char
----------
 20180820
(1 row)

postgres=#

TO_CHAR and other date/time formatting is documented here.

Oh, and if you actually do want to cast to bigint:

select to_char( dt, 'YYYYMMDD' )::bigint from dttest ;


Query A if there is no B I have a table with columns ID, TYPE, DATE, ... and I need all data sets with TYPE <> 'A' (case 1) and the data sets with TYPE = A if there is no TYPE = B with the same ID (case 2). The first case is easy so I think to use UNION to combine the first and the second case. But I have no idea how to select the second case. I'm experimenting with self joins like:

SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B'

This way I get the sets which I don't want. I think I need a subquery but I have no idea in which direction I should work.

Maybe something like this:

SELECT *
FROM MY_TABLE TAB1
WHERE TAB1.TYPE <>'A'
UNION
SELECT *
FROM MY_TABLE TAB1
NOT IN
(SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B')

Example:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  A   | ...
2  |  B   | ...
3  |  A   | ...

I need row 1 (TYPE <> 'A'), row 3 ( TYPE <> 'A') and row 4 (there is no set with same ID and TYPE = 'B'). I don't want row 2, because TYPE = 'A' and there is a data set with same ID and TYPE = 'B'. So the result should be:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  B   | ...
3  |  A   | ...

Use NOT EXISTS:

Declare @T table (id int, [type] varchar(5))
insert into @t(id,[type]) values(1,'B'),(2,'A'),(2,'B'),(3,'A')

SELECT *
FROM @T TAB1
WHERE [TYPE] <> 'A'

UNION ALL

SELECT *
FROM @t TAB1
WHERE [type] = 'A'
    AND NOT EXISTS (
        SELECT *
        FROM @t
        WHERE id = TAB1.id
            AND [type] = 'B'
        )

The above uses UNION ALL (simple concatenation), which is typically more efficient than duplicate-removing UNION [DISTINCT]. Two sets, one with type = A and the other with type <> A cannot possibly overlap, so concatenation is safe here.

You could also express the logic as WHERE [TYPE] <> 'A' OR ([TYPE] = 'A' AND NOT EXISTS ...) but check which method gives you better performance (complex disjunctions do not always optimize well).


How to create an oracle dump(.dmp) file using Toad? We can export the entire database schema(skeleton) along with its data in one .dmp file using oracles "exp" tool.
But how do I do it using Toad only.

I can't use this

C:\Users\kaunds_oliver>exp PORTAL_xxx/xxx@asdb file=E:\\Oliver\\userexpo
rt.dmp log=E:\\Oliver\\userexport.log

As there is no entry in the TNSNAMES.ora for the remote server ="asdb"
Also, I tried doing this

ASDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 172.27.xx.xx) (PORT = 1530))
    )
    (CONNECT_DATA = (SERVICE_NAME = asdb))
  )

But I don't have the privileges to edit anything in C Drive i.e. the TNSNAMES.ora file

How do I create a dump file using specifically "Toad" tool of a database that I am connected to remotely?

What is your Toad version?

I have 9.5 here and there are three options:

Database > Export > Export Utility Wizard
Database > Export > Export DDL
Database > Export > Generate Database Script

There's one last option, in case you only need to get the 'skeleton' for one schema:

Database > Export > Generate schema script

Have you tried any of that?

[EDIT]
I've tried Alex Poole approach and it didn't work for me either.

Sqlplus to the entry:

p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ sqlplus system@sigepsti

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 31 17:07:23 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

(sigepsti)SQL> exit

Expdp error (exp gets the same error):

p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ tnsping sigepsti

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-JUL-2014 17:07:37

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/grid/product/11.2.0/grid/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dborasigepsti)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sigepsti)))
OK (0 msec)
p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ expdp system@'dborasigepsti:1521/sigepsti'

Export: Release 11.2.0.4.0 - Production on Thu Jul 31 17:07:50 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDE-12504: operation generated ORACLE error 12504
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


How to UPDATE NULL values by MAX()+1? I want to assign values to null cells of a column as

UPDATE table 1
SET number = MAX(number) + 1
WHERE number IS NULL

but mysql return error Invalid use of group function.

Assuming that you want - if the max(number) in the whole table is say, 27 - to update  all the rows with NULL, to 28.

You need a subquery or a derived table to first find this max and then join back to the table:

UPDATE tableX AS t
  CROSS JOIN
    ( SELECT MAX(number) AS max_number
      FROM tableX
    ) AS m
SET t.number = m.max_number + 1
WHERE t.number IS NULL ;


Update Query With If Exists I am trying to write a query that will update my table #master12.cg when it is null.  So here are my steps needed:

  1) Check if #master12.uid exists in #check1.dasboot and if it does, then update #master12.cg with that value
  2) Check if #master12.uid exists in #check.uid and if it does update #master12.cg with the value in #check2.redbluegreen
  3) If #master12.uid does not exist in either table then just leave #master12.uid as null

I have tried this query

Update #master12
Set [cg] = case
            when exists (Select [dasboot] FROM [#check1] WHERE [#master].    [uid] = [#check1].[uid])
            when not exists (Select [redbluegreen] FROM [#check2] WHERE     [#master12].[uid] = [#check2].[uid]
       else [#master12].[cg]
       end
WHERE [#master12].[cg] IS NULL

However this query presents an error of:

  Msg 156, Level 15, State 1, Line 4
  Incorrect syntax near the keyword 'when'.
  Msg 156, Level 15, State 1, Line 5
  Incorrect syntax near the keyword 'else'.

How should I write this update statement so that the checks listed above are performed and my table is updated accordingly?

SQLServer case when statement doesn't work like that. You need a condition, and then a result. Your query only has a condition.

You could rewrite your query like that :

Update #master12
Set [cg] = [dasboot]
from #master12 m
join [#check1] c1 on m.[uid] = c1.[uid]
WHERE m.[cg] IS NULL

Update #master12
Set [cg] = [redbluegreen]
from #master12 m
join [#check2] c2 on m.[uid] = c2.[uid]
WHERE m.[cg] IS NULL

Or maybe in a single one like :

Update #master12
Set [cg] = isnull([dasboot], [redbluegreen])
from #master12 m
left join [#check1] c1 on m.[uid] = c1.[uid]
left join [#check2] c2 on m.[uid] = c2.[uid]
WHERE m.[cg] IS NULL

But it will also depend on what you want when you have a uid value present in both tables #check1 and #check2 and which value you want to be used for the update.


Access denied to user@localhost As a root user I did this:

GRANT DELETE, SELECT, INSERT, UPDATE, EXECUTE ON dbName.* TO
'user'@'localhost' IDENTIFIED BY 'apassword';

In 'dbName' I created a procedure 'proc_name' with definer set to 'user'@'localhost'.

Similarly I create a function 'func_name' with definer set to 'user'@'localhost'.

Now the issue is, I'm able to do select func_name(); but I'm not able to call the procedure proc_name()

When i did :

call proc_name(), I get the below error:

  ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'dbName'

Any pointers to the solution would be appreciated.

As @Rick suggested in the question comments. We need to grant all the permissions, procName expects. In my case, procName Creates and Deletes temporary tables. So, granting CREATE TEMPORARY TABLES permissions on dbName.* did the trick


MySQL: Performance issues in heavy multi-threaded environment I have 23 of these tables in one database:

CREATE TABLE foo1 (
  DateTime datetime NOT NULL,
  BidOpen decimal(11, 5) NOT NULL,
  BidHigh decimal(11, 5) NOT NULL,
  BidLow decimal(11, 5) NOT NULL,
  BidClose decimal(11, 5) NOT NULL,
  AskOpen decimal(11, 5) NOT NULL,
  AskHigh decimal(11, 5) NOT NULL,
  AskLow decimal(11, 5) NOT NULL,
  AskClose decimal(11, 5) NOT NULL,
  Volume decimal(8, 2) NOT NULL
) ENGINE = InnoDB;

ALTER TABLE foo1 ADD UNIQUE INDEX AskHigh (DateTime, AskHigh);
ALTER TABLE foo1 ADD UNIQUE INDEX AskLow (DateTime, AskLow);
ALTER TABLE foo1 ADD UNIQUE INDEX BidHigh (DateTime, BidHigh);
ALTER TABLE foo1 ADD UNIQUE INDEX BidLow (DateTime, BidLow);
ALTER TABLE foo1 ADD UNIQUE INDEX DateTime (DateTime);

When running this query in ~70 threads
SELECT MIN(DateTime) FROM fooXY WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>1.7444;
almost each needs ~2 seconds for ~225k rows.

The issues is only when these queries are ran multi-threaded.
Single execution is very fast and as expected.

Environment:

Server has 4GB RAM
MySQL 8.0.15
8 cores
key_buffer_size == 268435456
innodb_thread_concurrency = 64
innodb_buffer_pool_size = 3133495296 [e.q. 75% of available RAM]
MySQL is using all of the available cores.

My question:

How can be this query optimized/rewritten so there is no performance issue?

Update:
As requested, the result of EXPLAIN:

id              1
select_type     SIMPLE
table           foo1
partitions      (null)
type            range
possible_keys   PRIMARY
key             PRIMARY
key_len         5
ref             (null)
rows            113400
filtered        33.33
Extra           Using where

The result of the suggestions in the chat or the answer:

Suggestions by user ypercube:

I changed the DateTime index to PRIMARY KEY.
Changing MIN() to ORDER BY DateTime LIMIT 1 fixed the performance issues.. The job needs now 50% of the time.

Suggestions by user Rick James:

The other indexes - apart from PRIMARY KEY are indeed not needed. The deletion of them also shrunk the database by almost 50%. This alone didn't fix the issue.
Changing the pool size to 50% of the available RAM. I don't see any change in comparison to 75% so I leave the 50% in order to avoid/minimize any possible swapping.

First, some cleanup.  Then I will provide the solution.

First, the UNIQUE(DateTime) obviates the need for any of the other unique keys.  They could be plain (non-unique) indexes.

Second, you have not specified a PRIMARY KEY for your InnoDB table.  This is naughty, but not the end of the world.  I recommend you promote UNIQUE(DateTime) to be PRIMARY KEY(DateTime).

Third; once you have done that, the rest of the indexes (plain or unique) are useless; get rid of them.

The query is a tough one to optimize -- it needs, shall we say, a "2-dimensional" index.  But indexes are only 1D.  WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>999999 would probably involve scanning all the rows with a date after the given one if an index starting with DateTime were used.

So, to make that variation run faster, you would need an index starting with AskHigh.  But then that is essentially useless if you ask AND AskHign > 0.0001.

PARTITIONing won't help because of the same issues -- pruning partitions with low values of DateTime or AskHigh is no better than having an index to avoid it.

With only 4GB of RAM, 3G is too much for the buffer_pool.  Lower it to 1500M.  Swapping is terrible for performance of MySQL.

For further discussion, please provide

EXPLAIN SELECT ...
the size of the table


When Should You Shrink Your Database I have read conflicting articles about shrinking a databases. Basically, our database grew 100% overnight from 150 gigs to 300 gigs b/c I believe it was with staging tables I put in. However, all the extra spaces is unused (150 gigs worth of unused data). Should I shrink my db at this point?  What problems could this do if I shrink the database?  Also, any other suggestions would be appreciated.

There are basically two things to consider:

You cause fragmentation when shrinking the database
If the database grew yesterday, there's a good chance it will grow again tomorrow.

To address 1, you need to defrag indexes once you've shrunk the database. The database will grow when you do this, although probably not back to the original size.

To address 2, ask yourself why the database grew. If you know it was a one time thing, then you're probably OK to shrink it. If you know it's a recurring thing, then it's best not to shrink. If you're not sure, do it once manually, but keep a close eye on things and make sure you don't make a habit of it.

Regularly shrinking is a waste of time, will cause you performance issues and one day you'll run out of space without realising it because the database grows overnight but you didn't realise it was at 99% capacity because it was scheduled to shrink everyday before you got into the office.

Keep in mind that the shrink should be carefully planned maintenance on live servers, it'll conflict with things like backups and might put significant load on the server.


Query results that not are in previous query result I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want

For example i want the id_product of the products in which attribute = 'Product Type' and value != 'Motherboard' AND value != 'Intel Motherboard'

I have the following table:
Atr_basic

column         | type                 | attributes
---------------+----------------------+-----------------------------------------------------------
id             | int                  | (identity) primary key
attribute      | varchar              | not null
value          | varchar              | not null
category       | int                  | not null
id_product     | int                  | not null

The query:

SELECT id_product
FROM Atr_basic
WHERE attribute = 'Product Type'
      AND value != 'Motherboard' AND value != 'Intel Motherboard' AND category = 140

The problem with this query it's only give me the id_product from the products they have Attribute = 'Product Type' but some product doesn't have Attribute = 'Product Type'

I managed to do this query but it takes 120seconds to complete:

SELECT id_product
FROM Atr_basic as ab
WHERE id_product
    NOT IN (SELECT a.id_product
                   FROM Atr_basic as a
                   WHERE a.attribute = 'Product Type'
                   AND a.value != 'Motherboard'
                   AND a.value != 'Intel Motherboard'
                   AND Category = 140)

You are missing the single quote closure after motherboard in both of your queries

AND value != 'Motherboard AND

AND a.value != 'Motherboard

The first query can be more organized as

SELECT id_product
FROM Atr_basic
WHERE attribute = 'Product Type'
AND category = 140
AND value NOT IN ( 'Motherboard', 'Intel Motherboard')


What are the risks of copying MDF/LDF files without detaching the DB first? Where DB backups are not available, our support team has been trained to restore using the following steps:

Get a copy of the MDF/LDF from a file system backup.
Stop the instance.
Overwrite the existing MDF/LDF files.
Restart the instance.

This is without a detach. The MDF and LDF files are simply overwritten and the instance restarted in the hope that the new files can be initialized correctly.

I'm currently trying to write a document describing the appropriate use of detach/attach in this scenario. While I know this is the correct approach, I can't find any resources which describe the risks of the other approach.

Can anyone provide any advice?

This is a terrible way to take a backup. Why aren't they using, uh, backups? Not just because they don't require that you take down the entire instance.

If you just stop the instance, there isn't really a guarantee that the MDF/LDF files will be detached gracefully. So just because you are able to copy them, does not mean they are good. Having the service stopped removes the very protection SQL Server provides by locking the files from file system access in the first place (what is preventing users from accidentally deleting one of these files? They are human, after all). And for all you know the files could be corrupt before you even copy them, or could become corrupt while you copy/move them. If the user accidentally moves them instead of copies them, and something happens to them during that process, you now have zero copies of your database.

Backups are very easy, and protect you from all of the above.

BACKUP DATABASE dbname TO DISK = 'C:\whatever\dbname.bak'
  WITH COPY_ONLY, INIT, COMPRESSION;

Restoring is similarly easy, though if the file structures aren't the same, you may need to use the WITH MOVE arguments. This effort is worth it.

RESTORE DATABASE dbname FROM DISK = 'C:\whatever\dbname.bak'
  WITH MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
       MOVE 'dbname_log'  TO 'C:\...\dbname.ldf';


MySQL query taking too long to execute My MySQL query is taking around 20 minutes to execute which I feel is excessive.

SELECT
  hyp_id,MAX( player_count ) AS MAXPLAYERCOUNT
  , MAX( viewer_count ) AS MAXVIEWERCOUNT
  , SUM( player_count ) AS TOTPLAYER
  , SUM( viewer_count ) AS TOTVIEWER
FROM player_count_log
WHERE
  DATE(added_time) = CURDATE()
GROUP BY hyp_id
ORDER BY  hyp_id

The player_count_log table contains over 63 million records with ~18k added every hour.

Here are screenshots of db and indexes

DATE(added_time) = CURDATE()

is the root of some of the evil

Do not "hide" a column (added_time) inside a function (DATE) if want an index to be used.

If added_time is a DATE, then you can simply do

added_time = CURDATE()

If it is a DATETIME or TIMESTAMP, then do

added_time >= CURDATE() AND
added_time  < CURDATE() + INTERVAL 1 DAY

Then your INDEX(added_time) can be used.


Sync MongoDB Replica set with Remote Replica Set I'm wondering if it is possible (within reason) to sync a new MongoDB Replica Set(RS) with an RS currently in production.

The Problem

We have a site running on AWS that has its own MongoDB Replica Set and we're working on migrating that site to Google Cloud. We want to start a separate RS on Google Compute that will catch up with the AWS RS and stay up to date during our QA lifecycle until we're ready to cut DNS over to Google.

We originally tried adding the replicas on Google as secondaries to the AWS RS to keep them in sync. Our plan was to then take the AWS RS offline and let the Google RS reassign a Primary and continue as the only RS after the site was completely migrated.

This caused some issues when one of the Google replicas got elected as a master and created some syncing issues with our current live site.

The Solution??

I've been debating if it's possible to rsync from the AWS Primary to the GCloud Primary. Is this doable with Mongo? Will the GCloud primary then properly replicate to its slaves?

Or...

Is there a better solution that anyone knows of for this problem?

UPDATE

After trying to add Google nodes to server we're not seeing the node sync:

INFRA-GENERAL-00:SECONDARY> rs.status()
{
"set" : "INFRA-GENERAL-00",
"date" : ISODate("2016-08-03T00:22:37.275Z"),
"myState" : 2,
"term" : NumberLong(2),
"heartbeatIntervalMillis" : NumberLong(2000),
"members" : [
    {
        "_id" : 0,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:35.205Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 1,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:34.728Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 2,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:35.248Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 3,
        "name" : "****:27017",
        "health" : 1,
        "state" : 2,
        "stateStr" : "SECONDARY",
        "uptime" : 338,
        "optime" : {
            "ts" : Timestamp(1470169610, 1),
            "t" : NumberLong(2)
        },
        "optimeDate" : ISODate("2016-08-02T20:26:50Z"),
        "configVersion" : 175485,
        "self" : true
    }
],
"ok" : 1
}

INFRA-GENERAL-00:SECONDARY> rs.config()
2016-08-03T00:23:49.051+0000 E QUERY    [thread1] Error: Could not     retrieve replica set config: {
"ok" : 0,
"errmsg" : "not authorized on admin to execute command {     replSetGetConfig: 1.0 }",
"code" : 13
} :
rs.conf@src/mongo/shell/utils.js:1091:11
@(shell):1:1

You can follow your original strategy with a minor tweak: add Google Compute (GC) nodes into the current replica set and keep them in sync until ready to cut over. The only thing you need to change is to set those new GC nodes to have priority 0. They will be full members of the set, they just cannot be elected primary (or trigger elections).

Once you are ready to switch over to the Google nodes, you need only reset the priority to 1 (or whatever your normal values are), and then step down or remove the AWS nodes from the set. You can guarantee that a GC node is elected primary by giving them a higher priority than the AWS nodes before issuing the step down.


Script that alters all stored procedure on the database I have a database with thousands of stored procedures and I need to alter hundreds of them.

In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds some code at the beginning (if needed).

For example, to write a script that adds SET NOCOUNT ON if not exists. So that it will alter all the stored procedures that does not have it yet.

Does it possible? If yes, can you give me some tips, guides?

I haven't tried nothing yet because I don't have any direction for doing it.

Thanks.

Solved

Thanks to tpet suggestion I solved it. Maybe not the best way, and I should add some more code so it will be more safe.

DECLARE @HelpText TABLE
(
    Val NVARCHAR(MAX)
);

DECLARE @sp_names TABLE
(
    ID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(128)
);

DECLARE @sp_count INT,
        @count INT = 0,
        @sp_name NVARCHAR(128),
        @text NVARCHAR(MAX);

INSERT  @sp_names
SELECT  name
FROM    sys.Procedures;

SET @sp_count = (SELECT COUNT(1) FROM sys.Procedures);

WHILE (@sp_count > @count)
BEGIN
    SET @count = @count + 1;
    SET @text = N'';

    SET @sp_name = (SELECT  name
                    FROM    @sp_names
                    WHERE   ID = @count);

    INSERT INTO @HelpText
    EXEC sp_HelpText @sp_name;

    SELECT  @text = COALESCE(@text + ' ' + Val, Val)
    FROM    @HelpText;

    DELETE FROM @HelpText;

    IF @text LIKE '%SET NOCOUNT ON%'
    BEGIN
        SELECT @text;
    END
    ELSE --Not found, should be added.
    BEGIN
        SET @text = REPLACE(@text, 'CREATE PROCEDURE', 'ALTER PROCEDURE');

        DECLARE @Find NVARCHAR(255);
        SET @Find = 'BEGIN';

        SET @text = STUFF(@text, CHARINDEX(@Find, @text), LEN(@Find), @Find + CHAR(13) + CHAR(10) + SPACE(4) + 'SET NOCOUNT ON;');

        EXECUTE sp_executesql @text;
    END
END

Start out with getting all the procedures from sys.procedures

SELECT  * FROM sys.Procedures

Then use EXEC sp_helptext on each to load the text of the procedures.

Search for text to add, add if needed,

Then load the updated text to a variable and execute dynamically.


MySQL: run a script when logging in? Is it possible, in MySQL, to run a 'login profile' similar to the bash/ksh profile, when connecting with the mysql client? That is to say, something similar to mysql -e 'select * from a_table', but where mysql doesn't terminate after executing the statement?

The mysql client has the init-command option, which does what you want, I think.

From the manual:

  --init-command=str

  SQL statement to execute after connecting to the server. If
  auto-reconnect is enabled, the statement is executed again after
  reconnection occurs.


Count days in date range but only for a specific year within the date range I have several date spans in a SQL Server table.
I am trying to count the number of days in the date span for a given year in the date span.
For all my examples I am trying count the numbers of days in 2019 that the date span covers.
I have the below code which calculates the total days in the date range but is there a way I can just get a count of the days within that date range for 2019 only?
The first date span below covers all of 2019 so I'm hopping to return a number around 365.
The second date span covers only 30 days in 2019 so I would like to have the query return 30.
SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20191231 AS VARCHAR(30)) AS DATE)) AS [Days]

SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20190130 AS VARCHAR(30)) AS DATE)) AS [Days]

Assuming your table has a StartDate and EndDate field, you can use a CASE statement to calculate the days difference between the end date and either 01/01/2019 or the start date of the range.
SELECT ID, StartDate, EndDate,
  (CASE
    WHEN StartDate < '2019-01-01' THEN DATEDIFF(DAY, '2019-01-01', EndDate)
    ELSE DATEDIFF(DAY, StartDate, EndDate)
  END)+1 AS DaysInRange
FROM Dates

db<>fiddle


Disable MEMORY_TARGET after being locked out of database? I'm setting up a local development environment in a virtual machine in which I installed Oracle XE among other software.

When I initially set up the virtual machine, the RAM was set to 4Gb. In this condition, I installed and run Oracle XE smoothly, without problem.

Then 4Gb of RAM proved to be a little to much for the laptops of my teammates, so I stopped the virtual machine, decreased its memory to 2Gb and restarted it.

Since then Oracle XE starts, as far as I can tell from the output of the service, but it refuse incoming connections because it seems MEMORY_TARGET is set too high.

Now, since I'm currently locked out from the database, how can disable the MEMORY_TARGET at all, or at least decrease the amount of space it tries to allocate ? I know I should be able to do it altering some system tables, but I'm not longer able to connect.

Here's some info you can need to give a clearer picture:

cat /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log

WARNING: You are trying to use the MEMORY_TARGET feature.
This feature   requires the /dev/shm file system to be mounted for at
least 1073741824   bytes /dev/shm is either not mounted or is mounted
with   available space less than this size. Please fix this so that
MEMORY_TARGET can work as expected. Current available is 1049767936
and used is 73728 bytes. Ensure that the mount point is /dev/shm for
this directory.
memory_target needs larger /dev/shm

This is instead the status of the service...

$ sudo service oracle-xe status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 21-DEC-2015 12:04:39

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                21-DEC-2015 11:31:33
Uptime                    0 days 0 hr. 33 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/infocertcop/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=infocertcop.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I know I should be able to fix the issue restarting the virtual machine with 4Gb of RAM, change the MEMORY_TARGET configuration and then decrease again the amount of RAM, but I would really prefer to discover how to do it in another way, if possible.

Find the name of the spfile on the filesystem, it should be something like this:

/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora

You should not edit the spfile directly as it contains binary data, but you can create a pure, text-based parameterfile from it:

sqlplus /nolog
create pfile='/tmp/initXE.ora' from spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora';
exit

Edit /tmp/initXE.ora, modify the values you want, then recreate the spfile from it:

sqlplus /nolog
create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/initXE.ora';
exit

Once it's done, try restarting the database.


Simple avg query on large table much slower in PostgreSQL than SQL Server I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012).

measurements:

column         | type                 | attributes
---------------+----------------------+-----------------------------------------------------------
id             | int/serial           | (identity) primary key
measuretime    | datetime/timestamp   | not null
parameter_id   | int                  | not null (foreign key) references parameters(id)
item_id        | int                  | not null (foreign key) references items(id)
value          | float                | not null

and two nonclustered index on measuretime and parameter_id

I've inserted 2.609.280 rows in items (half a year with 5 seconds between each) and 31.311.360 rows in measurements (for each item with 12 parameters).

When I now try to query the average value per day per parameter it performs really well on SQL Server (00:00:02) but pretty bad on PostgreSQL (00:00:53).

SQL Server Query:

select parameter_id, convert(date, measuretime), avg(value)
from measurements
group by parameter_id, convert(date, measuretime)

PostgreSQL Query:

select parameter_id, date(measuretime), avg("value")
from measurements
group by parameter_id, date(measuretime)

Is there anything I can do about this? create an index? some server settings? change the query?

In PostgreSQL 9.6 Date constructors were fixed.

  Improve speed of the output functions for timestamp, time, and date data types (David Rowley, Andres Freund)

You can see the commit fest for this here. It's roughly 20x faster. In addition 9.6 supports parallel sequential scans. So this may be 20x faster on a single core. And, the whole scan may be able to use all of the cores on your system.

Download 9.6
Try again.
Get back with the results.


Database Mirror - Host can not be reached or does not exist I tried mirroring database with GUI and T-SQL, but I had problems in both cases.

Here is some information about infrastructure and SSMS:

INFRASTRUCTURE

Principal server with SQL Server 2017 Enterprise Edition
Mirror server with SQL Server 2017 Enterprise Edition
Witness server with SQL Server 2017 Express Edition
All servers S.O.: Windows Server 2016 Standard
All servers with firewall disabled
All servers in common domain
All servers has same users and privileges
Telnet works perfectly with port 5022 in all servers
I created user to manage service in all servers with GRANT CONNECT ON ENDPOINT::Mirroring

SQL Server Management Studio Info - SSMS:

Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                    14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

I'll explain both error cases, with GUI and T-SQL.

With GUI:

Success configuration endpoints in all servers

But I had this error when I tried start mirroring

It's so confusing because the database must be in recovery status in mirror server. But I read some posts about this and is recommended configure with T-SQL, so

WITH T-SQL:

With T-SQL I had this problem:

  Msg 1418, Level 16, State 1, Line 54 The server network address
  "TCP://HML-SQLS-01-TST.domain.br:5022" can not be reached or does not exist. Check
  the network address name and that the ports for the local and remote
  endpoints are operational.

Let me explain step-by-step I had followed:

PRINCIPAL SERVER

CREATE DATABASE mirror
GO

ALTER DATABASE mirror
SET RECOVERY FULL
GO

USE mirror
GO

CREATE TABLE TESTE(
    id INT IDENTITY(1,1),
    descricao VARCHAR(255)
);

BACKUP DATABASE mirror
TO DISK = 'F:\Backup\mirror-full.bak'
GO

BACKUP LOG mirror
TO DISK = 'F:\Backup\mirror-log.bak'
GO

CREATE ENDPOINT Mirroring
    STATE = started
    AS TCP (listener_port=5022)
    FOR database_mirroring (role=partner)
GO

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

MIRROR SERVER

CREATE ENDPOINT Mirroring
    STATE = started
    AS TCP (listener_port=5022)
    FOR database_mirroring (role=partner)
GO

RESTORE DATABASE mirror
FROM DISK = 'F:\Backup\mirror-full.bak'
WITH NORECOVERY
GO

RESTORE DATABASE mirror
FROM DISK = 'F:\Backup\mirror-log.bak'
WITH NORECOVERY
GO

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

WITNESS SERVER

create endpoint Mirroring
state = started
    as tcp (listener_port=5022)
    for database_mirroring (role=WITNESS)
go

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

MIRROR SERVER

alter database mirror
set partner = 'TCP://HML-SQLS-01.domain.br:5022'
GO

PRINCIPAL SERVER (The error pops in this execution)

--The error pops in this execution:
alter database mirror
set partner = 'TCP://HML-SQLS-01-TST.domain.br:5022'
GO

  Msg 1418, Level 16, State 1, Line 54 The server network address
  "TCP://HML-SQLS-01-TST.domain.br:5022" can not be reached or does not exist. Check
  the network address name and that the ports for the local and remote
  endpoints are operational.

But if I try this command in CMD, its work perfectly:

telnet hml-sqls-01-tst.domain.br 5022

ENDPOINTS IN SERVERS:

Principal server endpoint

Mirror server endpoint

Witness server endpoint

Has anyone been through this or could suggest a way forward?

Related to question but not the answer: While you got SQL Server 2017 with Enterprise Edition why to consider a feature which is under maintenance and might removed in future.

However, in your case, i would suggest to verify following areas (if not done already), hope it must fix the issue:

Make sure all SQL instance services running under same domain account

That service got permissions (as mentioned in OP) on all the SQL instances

Verify end-point ports are different, if one server got multiple SQL instances

Allow end-point ports at firewall (incoming and outgoing), even firewall disabled

Restart all SQL instance services if it's not done so since the last password change

Check SQL Error log to verify same port numbers are listening


bad bind variable in trigger i'm trying to update the table whenever complaint has been registered

create or replace trigger complaint_advertise
    BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
    DECLARE BEGIN
    UPDATE ADVERTISEMENT SET AD_APPEAR='INVALID' WHERE AD_ID=NEW:AD_ID
    END;

But, getting error ERROR at line 2: PLS-00049: bad bind variable 'AD_ID'

The code should read:

create or replace trigger complaint_advertise
BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
DECLARE
BEGIN
    UPDATE ADVERTISEMENT SET AD_APPEAR='INVALID' WHERE AD_ID=:NEW.AD_ID;
END;
/


Migration from MySQL to Oracle generates empty tables I'm using SQL Developer to migrate a database from MySQL 5.7 to Oracle 18c XE. The problem is that the tables are generated but empty, even though I'm specifying that the data should move online. I also looked at the scripts, and there's no data either.

What I see in the log is DataMove.DISABLE_CONSTRAINTS_FAILED. How to fix this?

These are the migration options:

Solution: create a user/schema in Oracle that has the same name as the database you want to import.


Using If and Vlookup in Postgresql This is what my query output looks like and i have modified the data for easier viewing.

TABLE A
                +--+--------------+----------------+----------------+
                |ID|Item Name     | Num of Sales   | Current Stock  |
                +-----------------+----------------+----------------+
                |67| Yellow Table |              23| 0              |
                |68| Red Glass    |              55| 0              |
                |69| Blue Chair   |              54| 0              |
                |70| Black stool  |              66| 0              |
                +--+--------------+----------------+----------------+

However "Current Stock" for "Red Glass" in another table is duplicated because it has actually 2 kind of merchant that provide the stock item in "Table B". This is the output query for the "Table B"

Table B
        +----+--------------+--------------+----------------+
        | Id | Item Name    | Current Stock| Merchant       |
        +----+--------------+--------------+----------------+
        |  68| Red Glass    |             0|Merchant A      |
        |  68| Red Glass    |           700|Merchant B      |
        +----+--------------+--------------+----------------+

What I am trying to result is like this, where the filter is "Current stock" = 0. Because Red Glass is actually not zero due to having stock in Merchant B.

                +--+--------------+----------------+----------------+
                |ID|Item Name     | Num of Sales   | Current Stock  |
                +-----------------+----------------+----------------+
                |67| Yellow Table |              23| 0              |
                |69| Blue Chair   |              54| 0              |
                |70| Black stool  |              66| 0              |
                +--+--------------+----------------+----------------+

Where item similar like "Red Glass" that have more than 1 Merchant in "Table B" should not detected having 0 current stock. I was thinking about doing similar Vlookup in excel to sum the "Current Stock" in Table B. Or using Case When to detect the duplicate row and then sum the "Current Stock" column

This should be easy with a NOT EXISTS subquery. Assuming the tables are as you show them:

selact a.*
from a
where "Current Stock" = 0
  and not exists
      ( select *
        from b
        where b.id = a.id
          and "Current Stock" > 0
      ) ;


MS Access Many-Many Query/Form Assistance I have somewhat of a beginner-type question.

Could someone look at the below relationships and let me know why I can't get the following form/query to only show the unique Appointment letters? I have 143 appointment letters. I have 135 members. I have 585 records in the junction table. I know that I messed up the form source query (below), but how badly am I off? Your expertise is greatly appreciated.

Resources used:

3 Example databases
4 MS Access 2003/2010/2013 books
5 links:
https://stackoverflow.com/questions/13108525/ms-access-2010-query-pulls-same-records-multiple-times-sql-challenge
https://stackoverflow.com/questions/473604/basic-many-to-many-sql-select-query
Querying a many to many relationship in access
Help with Access many-to-many relationship (beginner)
Query Multiple tables in MS Access

Screenshot of Relationships:

Form:

Query:

SQL:

SELECT DISTINCTROW tbl_apptltrs.*, jtbl_apptltrs_mbrs.*, tbl_mbrs.*
FROM tbl_mbrs INNER JOIN (
    tbl_apptltrs INNER JOIN jtbl_apptltrs_mbrs
    ON tbl_apptltrs.ApptLtrsID = jtbl_apptltrs_mbrs.LinkedApptLettersID)
ON tbl_mbrs.MembersID = jtbl_apptltrs_mbrs.LinkedMembersID;

I'm having to do a little bit of psychic debugging here, but generally speaking, you should have the subform bound to a query that joins your junction table to the table of related records. The form inside the subform shouldn't know about the table in the left side of the relationship (tbl_apptltrs).

So the chain is something like this:

Main form bound to tbl_apptltrs.
Subform linked to main form via primary key of tbl_appltrs (e.g. parent form ApptLtrsID = subform LinkedApptLettersID).
Form inside subform bound to query that joins jtbl_apptltrs_mbrs to tbl_mbrs (jtbl_apptltrs_mbrs.LinkedMemberID = tbl_mbrs.MemberID).


Select All Field (if field not exist, make field with null value) I have a table with this field and condition
table cc_queue_detail
ticket_id    field_name_id             data_content
1            category_ticket_1         33
1            category_ticket_2         44
2            category_ticket_1         55

I want to select for table cc_queue_detail and make them into like this:
ticket_id   category_ticket_1    category_ticket_2
1           33                   44
2           55

i haved tried this query, but its only selected with the ticket_id 1
SELECT DISTINCT
a1.ticket_id AS 'ticket_id',
a2.data_content AS 'category_ticket_1',
a3.data_content AS 'category_ticket_2'
FROM
cc_queue_detail a1
JOIN cc_queue_detail a2 ON a1.ticket_id = a2.ticket_id
JOIN cc_queue_detail a3 ON a1.ticket_id = a3.ticket_id
WHERE a2.field_name_id = "category_ticket_1"
AND a3.field_name_id = "category_ticket_2"

result query:
ticket_id   category_ticket_1    category_ticket_2
1           33                   44

i tried change  AND a3.field_name_id = "category_ticket_2" into OR a3.field_name_id = "category_ticket_2" but it not work.
can someone help me to fixed this?

Your issue is you're using an INNER JOIN (JOIN for short) between the same ticket_id of your cc_queue_detail table a1, and two different sets of rows from your second and third instance of the cc_queue_detail table, a2 and a3. This basically is saying the ticket_id has to match between a1, a2, and a3 always, but because of your WHERE clause, that's not possible, since a2 and a3 are different sets of rows from your cc_queue_detail table.
You'll want to use an OUTER JOIN, such as a LEFT OUTER JOIN (LEFT JOIN for short) to accomplish your goal, something like this:
SELECT DISTINCT
    a1.ticket_id AS 'ticket_id',
    a2.data_content AS 'category_ticket_1',
    a3.data_content AS 'category_ticket_2'
FROM cc_queue_detail a1
LEFT JOIN cc_queue_detail a2
    ON a1.ticket_id = a2.ticket_id
    AND a2.field_name_id = "category_ticket_1"
LEFT JOIN cc_queue_detail a3
    ON a1.ticket_id = a3.ticket_id
    AND a3.field_name_id = "category_ticket_2"

Now you'll get all rows back from a1, your entire cc_queue_detail table, and where there isn't a match between the other instances of the table (a2 and a3) there will be NULL values.


How to save and then restore permissions after refreshing a database A database in a STAGING environment is overwritten every N weeks with a PRODUCTION copy that is more recent.

What is the proper way to save and then restore users, roles and explicitly granted permissions as they were before the existing database was overwritten by the production copy?

I do not want to restore users and permissions as they were in PRODUCTION, but as they were in the DEV environment before the database was overwritten.

This refresh process must be automated as there are many different environments with dozens of instances that can potentially host up to 10 databases.

@AaronBertrand is right. You should consider a different method of refreshing your staging environment.  If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production.  However I realize that changing your process can take time (if management will even approve the time).

Essentially you want to create a script that will drop all of your existing permissions from the copy of the prod database and another that will generate all of the permissions that used to be there.  My personal preference is going to be to use a script like sp_dbpermissions.  Run it on your production environment and then copy the drop user script column (second to last column) into a .sql file and save it.  Be very careful not to run it in production of course.

EXEC sp_dbpermissions 'dbName'

You only need the second to last column from the first result set.

Next run the same script on your Staging database.  This time you will want to copy the last column from each of the three result sets.  This will give you the CREATE USER, sp_AddRoleMember and GRANT/DENY commands to add the permissions for this database.  Again save them in a .sql file.  After your restore go ahead and run the first script (to remove the old permissions) then the second (to add the new).

I also highly recommend taking a backup of your Staging database before overwriting it.  This way if there is a problem  you can always restore it back (even if to an alternate location) and confirm any missing permissions.


Host not Allowed Connectivity to MySQL Server I currently have a website and it references mySQL database. The website uses HTML, CSS and PHP.

PHP code embedded inside HTML website, fetches a value and stores it on the SQL database. Then the website reflects the value stored in the database. Basically,  the website is a page counter, for instance every time a user views my webpage it counts it as a numerical value and displays the value on the webpage.

Within my PHP code, I'm supplying correct credentials, such as Host, User, and Password. But, when I try to access the website I receive error code "SQLSTATE[HY000] Host'x.x.x.x' is not allowed to connect to this MySQL server"

I think the issue is mySQL isn't allowing host to access SQL database. The reason I know this, is because I performed 3 steps to log into SQL without a password. when I log into mySQL with no password, I can see the website page counter and it successfully works.

Steps I use to log into SQL without a password are:
1) /etc/init.d/mysqld stop
2) mysqld_safe --skip-grant-tables &
3) mysql -uroot.

When I perform the above steps I can see the page counter working on the website.

What modifications do I need to perform on mySQL to allow my host to access the SQL database?

I've already tried GRANT ALL PRIVILEGES ON .* To root@localhost and it did not work.
(MySQL database is installed on the same host accessing the website)

My web server is Apache 2.2.15 (CentOS). The database server is running mysql Ver 14.14 Distribution 5.5.49. On Linux environment.

Use a SQL commands like this:

mysql -u user -p -e 'Your SQL Query Here' database-name

to figure out what users are accessing your db.

In the results, did you only see localhost and 127.0.0.1 for the host? This would mean that only these two hosts are allowed for access. you can try:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'myIPaddress'

and don't forget to follow that with

FLUSH PRIVILEGES


Reducing impact of SUM(DATALENGTH()) over linked server I have a query that reports back to me how much time elapsed for records to be added. I also in this query track the size of an image that was added to the database. From what I can see it looks like it is returning the whole image from the linked server and then calculating the size. Is there an easy way I can get the calculation of size to occur on the other side of the link so that the return can be faster?

SELECT
    [Date] = CAST(DATEADD(DAY,-@DaysBack,GETDATE()) AS DATE)
    ,NOTES = N.RPMID
    ,TimeTaken = DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate)
    ,PictureSize = SUM(CAST(DATALENGTH(SI.[Image])AS BIGINT))
    ,RecordsAdded = CAST(COUNT(*) AS BIGINT)
FROM
    DBServer1.DB1.dbo.Notes AS N WITH (NOLOCK)
    INNER JOIN DBServer1.DB1.dbo.NoteDistribution AS ND WITH (NOLOCK) ON N.Notes=ND.Notes
    INNER JOIN dbo.Submission_Notes AS SN WITH (NOLOCK) ON SN.RetailPlanGuid=N.RPMID
    LEFT JOIN DBServer1.DB1.dbo.SavedImage SI WITH (NOLOCK) ON SI.Notes=N.Notes
WHERE
    1=1
    AND N.RecordStampWhenAdd > CAST(GETDATE()-@DaysBack AS DATE)
    AND N.RecordStampWhenAdd < CAST(GETDATE()-@DaysBack+1 AS DATE)
    AND DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate) > 0
GROUP BY
    N.RPMID
    ,DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate)

The part in question is the picturesize that is returned. I want the calculations in there to be done on the server where the data is stored and just have the number return to me. Any help on this would be great. Thanks.

I am not sure exactly how you have determined that all of the data is going across the wire (or why that should be the case) but you could always have a computed column:

ALTER TABLE dbo.SavedImage ADD PictureSize
  AS CAST(DATALENGTH([Image]) AS BIGINT);

Or just add a column and calculate that yourself whenever you insert/update a row, either by forcing inserts/updates through a stored procedure, or using a trigger.

And persist and/or index that as necessary, and then just use SUM(PictureSize) in the query.

Of course the big impact is still going to be on the fact that you have to scan the entire table or index in order to generate the aggregate.

This method could also be used to maintain the total size of all columns in some other table, but I'm largely against materializing redundant data unless there is a demonstrated advantage to doing so.

In any case, I really don't think this query is slow because it is moving the whole varbinary data over the wire and then calculating the length at the wrong end; if you have evidence to suggest that is the case, please update the question and we'll try to address it.


Is there any danger using binary data over an UTF-8 MySQL connection? My application uses UTF-8 as the default encoding for all fields, and the connection itself; every connection starts with:

SET NAMES utf8;

I need to store a few SHA-1 hashes though, and for efficiency I'd like to store them in their compact, binary form (20 bytes) instead of their hexadecimal form (40 chars).

These fields are declared as BINARY(20).

I've tested it and everything seems to be working fine, but just to be on the safe side, I thought I'd ask if there is anything I need to know, any pitfalls to avoid? Or is it perfectly safe to transmit binary data over an UTF-8 connection?

From my experience storing such data, is it perfectly safe to transmit binary data when the character encoding of the connection is configured as UTF-8. The character encoding is just that: a way of representing the characters in binary form (this is the crux of the issue in Python bytes/str problems). MySQL rightly does not try to 'encode' binary data.

From the fine manual (emphasis added by me):

  The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.

You should be aware that "the connection" is not UTF-8, but rather UTF-8 is the encoding that the application and MySQL have agreed upon in order to represent text characters.


domain group permissions issue I just installed SQL server 2016 on a new 2012r2 vm. During the SQL installation I added the domain admin group and my domain account to the SA role. After the installation, I can connect using SSMS. The problem is no one in the domain admin group can connect. The group and my account are members of the local admin group on the server and we're only using RDP to connect to the db locally. The error I see in the log is "Login failed for user. Reason: token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.[CLIENT:]"

When I check the effective permissions for the domain admin group I get the following error: Cannot execute as the server principal because the principal "domain\Domain Admins" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I'm a fairly new to SQL server...any help would be appreciated. Thanks!

Those users would need to run SSMS as an administrator in order to connect. It's explained here.


Xquery, insert, Trigger and no place to squeeze stored procedure I have a Stored procedure, which is, basically, xquery pulling data from xml and inserting it into multiple tables. Once it's inserting into last of the mentioned tables, the trigger fires up and then this data is used to do more data manipulation.

it all goes nicely up to a point where I have to do some serious string cutting & splicing. By serious I mean adding to one string client surnames and first names selected from multiple rows and columns from a table. previously it was done by way of 3-rd party software, which shredded xml and called SQL Server stored procedure with said string manipulation for each row from dataset it derived from xml.

Now that I'm using Xquery this is unavailable - insert from select from xquery will not allow to either re-use the said stored procedure and I can't use the bit of code to do the manipulation in xquery.

Sample code for string manipulation:

select top 100 @string = @string + Surname +' & '
from usr_bie
where ref = @ref and code = @code and type = 'Client' order by mc desc
Select @string = left(@string,len(@string)-2) + ', '
Select top 100 @string = @string + FirstName +
(case
 when middlename is not null
 then ' ' + Middlename
 else '' end
) + ' & '
from usr_bie
where ref = @ref and code = @code and type = 'Client' order by mc desc
Select @string = left(@string,len(@string)-2)

I need to assign the result of the above to value in xquery:

Insert into a (col1, col2,...)
Select
t.c.value('(../Ref)[1]','varchar(50)') as col1
....
from @xml.nodes(...)t(c)

I understand that Xquery inserts multiple rows, so there's no easy way to call the old stored procedure for each row, but OTOH I also can't do my @string manipulation as above.

Is there a solution I'm not familiar with?

I converted the string manipulation code to function (again), and this time it works. But there still is work to be done on the other values from xml... So question is: is there a way to do RBAR with xquery, so I can manipulate data in various ways as I need? As it is right now, Insert is being done on multiple rows, so I can't do do simple condition: if @variable = somevalue begin..., where @variable is passed as a stored procedure parameter? And Is there any sense in my sticking to the slow way (rbar), instead of - like @Duffy suggested - going to CTE?

Are you familiar with sql:column and sql:variable?  They allow you to manipulate XML with external variables or tables?  Work through this demo and examine the different 'before' and 'after' results to see if they could help you:

USE tempdb
GO

DECLARE @xml XML
DECLARE @someString VARCHAR(50) = 'test string'

SET @xml = (
SELECT
    object_id AS "@object_id",
    SCHEMA_NAME(schema_id) AS "@schemaName",
    name AS "@tableName"
FROM sys.tables t
FOR XML PATH('table'), ROOT('tables'), TYPE
)

SELECT 'before 1' s, DATALENGTH(@xml) dl, @xml x

-- Use sql:variable to add a variable into the XML
SET @xml.modify('insert element someData{sql:variable("@someString")} into (tables/table)[1]')

SELECT 'after 1' s, DATALENGTH(@xml) dl, @xml x

-- Use sql:column to add a column from a table into the XML
DECLARE @t TABLE ( object_id INT PRIMARY KEY, identity_col SYSNAME )

INSERT INTO @t ( object_id, identity_col )
SELECT object_id, name
FROM sys.identity_columns

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
    t.c.value('@object_id', 'INT') object_id,
    t.c.query('.') tableData
INTO #tmp
FROM @xml.nodes('tables/table') t(c)

SELECT 'before 2' s, DATALENGTH(tableData) dl, tableData
FROM #tmp

UPDATE t
SET tableData.modify('insert element identCol{sql:column("ids.identity_col")} into (table)[1]')
FROM #tmp t
    CROSS APPLY @t ids

SELECT 'after 2' s, DATALENGTH(tableData) dl, tableData
FROM #tmp


Schema name, table name and row count My database has 50+ schemas. Each schema has around 100 tables and some views.

I want a query that tells me the schema name, table name, and total number of rows in each table. The below query:

select table_schema, table_name, table_type, count(*)
from information_schema.tables
group by table_schema, table_name, table_type

...gives the total number of tables with each name, rather than giving the row count. I am using pgAdmin 3 version 1.10

This should do the trick:

select nspname, relname, relkind, reltuples from pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where relkind in ('r', 'v');

Note that the reltuples is an estimate of the rows not the actual count at the moment but generally it's quite accurate (depends on how up to date your statistics are).


Rights needed to see database files in Properties dialog? I have a contractor setting up a database application. The database is set up, the login for the app is set as the database owner, but has only 'public' at the server level.

When the contractor logs into SSMS with the database login, he sees no information in the Database Properties > Files page -- the table is empty. However, exec sp_helpdb database; returns all the info that should be showing in Properties.

Are there any rights I can grant that will show the file info in Properties, without granting excessive rights to anything else?

(SQL Server 2008 R2)

If I'm understanding correctly I believe you need to grant VIEW DEFINITION to your contractor's account. You can do this only on the DBs you want the contractor to access.


Select last row for a certain group I have two tables:

Table users

columns | type   | notes
--------|--------|-------
id      | bigint | PK
...     | ...    | ...

Table events

columns    | type    | notes
-----------|---------|------
id         | bigint  | PK
user_id    | bigint  | FK(`users`.`id`)
event_name | varchar | ...
...        | ...     | ...

Example of Table events

id | user_id | event_name | ...
---|---------|------------|-----
 1 |       1 | A          | ...
 2 |       1 | B          | ...
 3 |       2 | F          | ...
 4 |       3 | C          | ...
 5 |       1 | A          | ...
 6 |       2 | A          | ...

I want get the latest event of a user (e.g: user_id is 1). So far, I found two solutions:

SELECT events.*
    FROM events
    WHERE events.id = (SELECT MAX(events.id) FROM events WHERE (events.user_id = 1));

and

SELECT events.*
    FROM events
    WHERE events.user_id = 1
    ORDER BY events.id DESC
    LIMIT 0, 1;

Which one is the better solution for this case? Or maybe, is there any better solution?

FYI, I use MySQL 5.6.19 and InnoDB is the db engine.

There would be marginal difference if any between both the queries, however, the second query is much simpler, so if it were me, I would prefer the second one!

Another way to do this would be:

SELECT MAX(events.id), events.user_id, events.event_name
FROM events
WHERE events.user_id = 1

Oh, and if you want to get the results of all user collectively, this query would help:

SELECT MAX(events.id), events.user_id, events.event_name
FROM events
GROUP by events.user_id

The output would be something like:

-------------------------------------
MAX(events.id) | user_id | event_name
-------------------------------------
       5       |    1    |    A
       6       |    2    |    F
       4       |    3    |    C


how to run mongo export on macbook? I am using mongodb Atlas (M0), which does not provide db backup feature. I am creating a nodejs script that will export all the collections automatically periodically.
So far I have done the following:

created a programmatic api from my login.
whitelisted my ip
installed mongocli
created a default profile using public and private api keys.

And command looks like this:
mongoexport --uri mongodb+srv://${mongoUser}:${mongoPass}@mongoprojectname.lok0l.mongodb.net/${database} --collection ${collection} --type ${fileType} --out ${fileName} --profile default
I am not able to figure out how to run this command. If I run this command in my terminal I get this error:
/bin/sh: mongoexport: command not found\n

You have to import MongoDB tools via .
Download, unzip, and use the export path and run the command below on the terminal and try the export command again.
export PATH=$PATH:inputmongodbtoolpath

If you already use brew, you can try with it.
brew install mongodb-community
brew services start mongodb-community


Cannot select features to install from SQL Server 2005 SP4 installation process The issue: I cannot select MSSQLSERVER from the feature list I want to update as part of the SQL Server 2005 x64 Service Pack 4 installation process.
Please note exe file is being raised from C:\ with full administrador privilegies.

Server details: Windows Server 2008 R2 Standard x64 bits
SQL Server details: Microsoft SQL Server 2005 - 9.00.5294.00 (X64) (Build 7601: Service Pack 1)
Service Package Details: SQLServer2005SP4-KB2463332-x64-ENU.exe

I have spent the last hour trying to identify where the issue is or similar experiencies over internet but I didn't get it until now.
Any comments will be appreciated.

Thanks in advance

You are running SQL Server build 9.00.5294.00 that is more current than SP4 which is 9.00.5000. Your server has already been updated. (http://sqlserverbuilds.blogspot.com/)
(Build 7601: Service Pack 1) is the version of the operating system.


Optimize query collecting all related items The following table is my menu master table that has an unlimited menu depth. Every menu can have a submenu and a sub-submenu and so on.

The problem I am facing is getting all items that have the same top-level parent.

One solution is to loop while parent is not null and second solution is to create a view with a parent id and all sub menu ids with comma separated column.

Then one select command will give the parent id. My table is as follows:

CategoryID  | ParentID
1           | null
2           | null
3           | 1
4           | 1
5           | 2
6           | 2
7           | 3

Output should be

CategoryID  | AllRelatedCategory
1           | 1,3,4,7
2           | 2,5,6

My current code is as follows. Problem is that it's working only with 3 levels of the hierarchy.

;with cte as
(
select a.CategoryID col1, convert(varchar, a.CategoryID) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a where a.ParentID is null

union
select a.CategoryID col1, (convert(varchar, isnull(b.CategoryID,''))) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a
left join [dbo].[TB_CATEGORY_MASTER] b on a.CategoryID=b.ParentID

union
select a.CategoryID col1, (convert(varchar, isnull(c.CategoryID,''))) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a
left join [dbo].[TB_CATEGORY_MASTER] b on a.CategoryID=b.ParentID
left join [dbo].[TB_CATEGORY_MASTER] c on b.CategoryID=c.ParentID
)

select distinct c.col1,
  STUFF(
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c
where c.ParentID is null

with    cte
        as
        (
            select      CategoryID  as root_CategoryID
                       ,1           as lvl
                       ,CategoryID

            from        TB_CATEGORY_MASTER

            where       ParentID is null

            union all

            select      c.root_CategoryID
                       ,c.lvl + 1
                       ,t.CategoryID

            from                    TB_CATEGORY_MASTER  as t

                        join        cte                 as c

                        on          c.CategoryID = t.ParentID
            )

select     t.CategoryID

          ,(select      case when lvl = 1 then '' else ',' end
                      + cast (c.CategoryID as varchar(10))

            from        cte as c

            where       c.root_CategoryID = t.CategoryID

            order by    lvl

            for xml path('')
            )                   as AllRelatedCategory

from        TB_CATEGORY_MASTER  as t

where       ParentID is null

+------------+--------------------+
| CategoryID | AllRelatedCategory |
+------------+--------------------+
|          1 | 1,3,4,7            |
|          2 | 2,5,6              |
+------------+--------------------+


Create Login command error What is wrong here in following screen shot?

I am trying solutions posted here and here to add a login

Are you 100% sure that this is SQL Server 2008?  This behavior suggests SQL Server 2000.

The SQL Server 2000 syntax was

EXEC sp_addlogin 'recovery', 'ThePassword';
GO

Try that.  If that works, when you get into the server check the compatibility level of all your databases in addition to running select @@VERSION.


MySqldump Error 2013: Lost Connection MySQL During create database backup I am use Prestashop.
am create a script for auto backup of whole database of my site
but from last few given this error and backup terminate at this table.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table ps_layered_filter_block at row: 107002
am change some value in my.cnf file but not work for me
my.cnf file
#Copyright 2019 Bitnami.com All Rights Reserved

#Note: This file will be modified on server size changes

[mysqld]
net_read_timeout = 1200
net_write_timeout = 1900
wait_timeout = 320
long_query_time = 1
query_cache_limit=20M
query_cache_type=1
query_cache_size=512M
innodb_buffer_pool_size=4096M
innodb_force_recovery = 1
innodb_log_file_size=128M
tmp_table_size=64M
max_connections = 2500
max_user_connections = 2500
innodb_flush_method=O_DIRECT
key_buffer_size=64M

[mysqladmin]
user=root

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=512M
bind-address=127.0.0.1
skip-name-resolve=1
default-authentication-plugin=mysql_native_password

character-set-server=UTF8
collation-server=utf8_general_ci
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin

!include /opt/bitnami/mysql/bitnami/my.cnf

but Problem Stil Same.
mysqldump -u root -p bitnami_prestashop > /home/{username}/dbbackup/db_13_07_2020.sql
also try
mysqldump -u root -p --extended-insert bitnami_prestashop > /home/{username}/dbbackup/db_13_07_2020.sql
our database size
| bitnami_prestashop | Size in (MB) |
+--------------------+--------------+
| bitnami_prestashop | 8515.77 |
|
| information_schema | 0.16 |
| mysql | 3.82 |
| performance_schema | 0.00 |
| sys | 0.02 |
+--------------------+--------------+```

**on-disk** size

11G /opt/bitnami/mysql/data/bitnami_prestashop
21M /opt/bitnami/mysql/data/mysql
11G /opt/bitnami/mysql/data

Please Provide solution as soon as posibal thanks

Given your DB dimensions try increasing those values a little more ,
Example :
max_allowed_packet=2048M
net_read_timeout=3600
net_write_timeout=3600

Don't forget to restart/reload mysqld aftewards.


InnoDB : Deadlock from one reader and one writer This sort of has me miffed.  One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that table.  So I could see how the reader would "wait" until the update completes but I don't understand why this is considered a "deadlock", where neither the update nor the select can proceed.

Here are some facts :

The table nasd_real has 23,000 rows
Every 15 seconds, an update happens that updates approx 1500 rows
This update takes under 1/2 a second, which I know because I can run it from the command line as the data from the source of the update is still there.
The select is doing an aggregation that joins to nasd_real, the result of that join is inserted int a temp table.

Some settings :

mysql> show global variables like '%tx%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> show global variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

I included the output from show engine innodb status\G below.

If anyone has seen this type of problem under similar conditions, I would most interested what you think!

TIA.

Don

------------------------
LATEST DETECTED DEADLOCK
------------------------
120720  6:58:15
*** (1) TRANSACTION:
TRANSACTION 1902337, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 24 lock struct(s), heap size 3112, 302 row lock(s)
MySQL thread id 45032, OS thread handle 0x7f87c4720700, query id 76514460     localhost prog Copying to tmp table
insert into t_open_existing
select p.portfolio_id
            , op.position_type_id
            , sum(op.position_size * ifnull(nr.last,today.last))
            , sum(op.position_size
     *(ifnull(nr.last,today.last)-ifnull(nr.prev,today.prev))
                                    * (pt.direction))
from member.portfolio as p
 , today
 , position_types as pt
 , member.open_positions as op
LEFT JOIN nasd_real AS nr
 ON   ( op.symbol_id = nr.symbol_id )

 where p.person_id =  NAME_CONST('p_person_id',1)
    and p.portfolio_type_id =  NAME_CONST('l_portfolio_type_id',1)
 and op.portfolio_id = p.portfolio_id
 and today.symbol_id = op.symbol_id
 and op.load_id <  NAME_CONST('l_today',5177)
 and pt.position_type_id = op.position_type_id
 group by p.portfolio_id, op.position_type_id

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 409609 n bits 320 index `PRIMARY` of table `    prog`.`nasd_real` trx id 1902337 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 190232C, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
57 lock struct(s), heap size 14776, 942 row lock(s)
MySQL thread id 42342, OS thread handle 0x7f87c4567700, query id
  76514371     localhost prog Sending data
update  nasd_real,
            stream_last
 set             nasd_real.last_updated = stream_last.last_updated,
            nasd_real.volume = stream_last.volume,
            nasd_real.last = stream_last.last,
            nasd_real.high = stream_last.high,
            nasd_real.low = stream_last.low
where   nasd_real.symbol_id = stream_last.symbol_id

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 409609 n bits 320 index `PRIMARY` of
 table     `prog`.`nasd_real` trx id 190232C lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 409604 n bits 320 index `PRIMARY` of
table
`prog`.`nasd_real` trx id 190232C lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

Due to how MySQL handles replication of INSERT INTO ... SELECT, it will actually do some locking on the rows of the tables involved in the SELECT portion of your query. Here is an older article detailing the issue.

The solution is to use row-based replication and set the transaction level to READ-COMMITTED.

To clarify, the locking of INSERT INTO .. SELECT will happen regardless of if you are using replication at all, it is just part of the internals.


SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to I currently have a query in SQL 2008 that can return me a list of tables for a given database, provided I have View Definition access to them.

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from information_schema.Tables
order by TABLE_NAME

The way our database roles are managed, the existing select permissions are nicely configured across roles, and adding the new View Definitions is done as a single schema-viewing role.

Is there a way I can limit this to the tables the current user has select permission on?

Just add WHERE clause and use HAS_PERM_BY_NAME:

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from information_schema.Tables
where (TABLE_TYPE = @TableType) -- Optional parameter
AND HAS_PERM_BY_NAME (
   QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME),
   'OBJECT', 'SELECT') = 1
order by TABLE_NAME


High IO when switched to InnoDB We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times.

The query is type of INSERT ... ON DUPLICATE KEY UPDATE

I don't know if the problem is specific to ON DUPLICATE. Maybe we should try to update and if no rows were changed - do an insert. I guess MyISAM is doing exactly the same thing.

We tried to change various parameters for InnoDB Storage Engine according to recommendations on this site, but nothing helped.

Here is our configuration:

Variable_name   Value
innodb_adaptive_flushing    ON
innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size  8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 8589934592
innodb_change_buffering all
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite  ON
innodb_fast_shutdown     1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_trx_commit  0
innodb_flush_method
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_io_capacity  200
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog   OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    187695104
innodb_log_files_in_group   2
innodb_log_group_home_dir   .\
innodb_max_dirty_pages_pct  75
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files   300
innodb_purge_batch_size 20
innodb_purge_threads     0
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  64
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_spin_wait_delay  6
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_sample_pages   8
innodb_strict_mode  OFF
innodb_support_xa    ON
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  1.1.8
innodb_write_io_threads 64

OBSERVATION #1
You have innodb_thread_concurrency set to 8. In MySQL 5.5, it is 0 by default. Setting innodb_thread_concurrency to 0 allows InnoDB to decide the best number of concurrency tickets to handle at the same time. I discussed way back on May 26, 2011 : About single threaded versus multithreaded databases performance
OBSERVATION #2
You have innodb_log_file_size set to 179M (187695104). If you have already set this value based on Proper tuning for 30GB InnoDB table on server with 48GB RAM, then 179M is set for your environment. If you did not change this and there are lots of data to change with the INSERT ... ON DUPLICATE KEY UPDATE, then you should consider raising the log file size to 1G. Please follow the steps from my post How to safely change MySQL innodb variable 'innodb_log_file_size'?
OBSERVATION #3
Since MySQL 5.5's InnoDB Storage Engine can access multiple CPUs and multiple core, you need to set innodb_buffer_pool_instances to a number higher than 1. I just discussed this a week ago in another post : Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?.
Essentially, you need to find out how many physical cores there are (numactl --hardware), and set innodb_buffer_pool_instances to that number.
Give it a Try !!!


How to create multiple container databases in 12c R1? As multiple databases can be created under the same/different Oracle home(s) in Oracle 11g on a host, please advise me on creating multiple container databases to create multiple pluggable databases under them to overcome monolithicity of Oracle 12c.

you can create it manually or automatically through dbca

How to start and stop pluggable databases in Oracle 12c

Because the instance architecture of pluggable databases is entirely      different from a non-container database,  one would imagine that managing their state of readiness is also  different.
     Well, it’s true.

The first thing to remember is that because the CDB maintains the
       instance for which all PDBs share,
       that instance must be up and open for people to be able to connect to the PDBs.
       Starting and stopping the CDB is not different from non-CDBs.
       The next thing to remember is that when you start a CDB,
       all of its associated PDBs are left in MOUNT state, which means
       that, by default, they are not opened with the CDB.

Unfortunately, 12cR1 doesn’t offer an option to change this behavior.
        However, 12c does provide a new type of trigger that will fire if it
        detects a CDB opening and will then open specified PDBs.
        See the Oracle documentation for further information on setting this up.
        After starting and opening a CDB, you can open any corresponding PDBs
        like so

    alter pluggable database orclpdb2 open;
    -- Pluggable database altered.
    -- or
    alter pluggable database all open;

To close PDBs, you can essentially do the opposite of the preceding  commands:

 alter pluggable database devpdb1 close;
 -- Pluggable database altered.
 -- Or
 alter pluggable database all close;

To drop pluggable database

 drop pluggable database orclpdb2 including datafiles;

You can unplug a PDB from a CDB with this syntax:

alter pluggable database orclpdb2
unplug into
      '/{installationdir}|{backupdir}/orclpdb2pxml';

reference0

reference1


Is there an alternative to isolation level SERIALIZABLE? I use MySQL 5.7 and want to run an export task on a products table. The process basically runs like this:

SELECT * FROM products WHERE exported = 0
Loop over the result set in my programm and write it to a file
UPDATE products SET exported = 1 WHERE exported = 0

So each product should only be exported once.

I found that I have to wrap the above in a transaction with isolation level SERIALIZABLE because I have another process that inserts records at the same time.

Otherwhise that other process could insert rows with exported=0 while I'm in step 2) which would be missing in the file but get updated to exported=1 in step 3).

Is this the best way to solve this kind of problems? Or are there any drawbacks or pitfalls I'm no aware of?

Give this a try:

BEGIN;
SELECT * FROM products WHERE exported = 0 INTO OUTFILE "x/y/z" FOR UPDATE;
UPDATE products SET exported = 1 WHERE exported = 0;
COMMIT;

And I don't think it matters what the isolation mode is.

I am not sure of mixing INTO OUTPUT with FOR UPDATE; if it give you trouble, make it two SELECTs.

Note that this does obviate your program with the loop and write.  (And it will probably run faster.)

Another approach may be to ping-pong 2 tables.  One is receiving new rows; the other is being exported.  No need to have the exported flag.  More details:  http://mysql.rjweb.org/doc.php/staging_table (especially the section on flip-flop staging).


Best way to ALTER date column to timestamp on a large table? I have to ALTER a date field to timestamp on a huge table having 1 billion records.

I am using Postgres 9.5. I learnt that when we use alter query a heavy “access exclusive” lock is acquired, which shuts everything else out of the table.

How can we achieve this efficiently?

How to do it efficiently and how to do it without blocking everyone else for the duration are diametrically opposed goals.  The efficient way is to schedule a downtime window in which to do it.

If that can't be done, then:

Add a new column of the desired type, with a NULL default value.
Change all the code which changes that old column or inserts new rows to make sure they correctly populate the new column as well (but make sure they don't depend on the new value already being correct--they just make it be correct)
Create an index to efficiently find rows where the new column is still NULL
Write a script to update rows with NULL values of new column to set them to be correct based on the value of the old column.  Do this in chunks that are as large as feasible, given that the first row in the chunk will be locked for the duration of the processing of the chunk.
Once all chunks of this are done and verified, change all the code to rely on the new column rather than the old column.
Change all the code to stop trying to maintain the old column
Drop the old column
Drop the index that supports finding still-NULL values of the new column (unless it is still useful)