texts: Grant non Sysadmin the ability to change max memory setting SQL Server

texts:
I've received a request to create a stored procedure that would allow a user (with access to the DB server) to change SQL Server's max server memory (MB). This wasn't the hard part.

The other requirement is that this user should be able to change this only parameter max server memory (MB) and nothing else. What I'd like to know is, if this is possible.

I do realize that, to be able executesp_configure and runRECONFIGURE, the minimum privilege required is ALTER SETTINGS. And providing ALTER SETTINGS would defeat the purpose of the stored procedure (which would be designed to run just exec sys.sp_configure 'max server memory (MB)', @TheReqMemVal) along with a minimum and maximum allowed value for max server memory (MB).

After reading some of the comments, I'd like to add that if responses were to incorporate possible security pitfalls, it would be greatly appreciated.


texts: How secure is a linked server in SQL Server

texts:
I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server.

I have two SQL Servers, say A and B. I want to log all logons using a logon trigger from server A in server B, and it is important that my database be able to insert the log into A, but it should not have delete or update access on B.

I want the log to be made, but I do not trust my sa or other people who have full access on A.

In brief, Server A can insert Logs but can not do anything more than that, and when I want to monitor the logons on A the only possible solution should be doing it on B.

How can I achieve this situation?


texts: Reorder missing numbers in a sequencein two different tables using MySQL

texts:
This is an extended question of reorder/ fill missing value in a sequence

I have a table "number" having a column "n" which is of type int. I inserted some values like,

n
--
1
3
5
9
11

i also have another table "num" having column "n" which is of type int. I inserted some values like,

n
--
7
12
15
23

Now I want the column n of "number" table as 1,2,3,5,6 and column n of "num" table as 4,7,8,9. That is 1 should be as 1, 3 as 2, 5 as 3, 7 from "num" table as 4 and 9 as 5 and so on. The values of column n should be reordered with the missing values on both the tables.


texts: Call UPSERT function multiple times to different inputs

texts:
I have a function:

merge_vehicles(vid, cid, vname, reg_no, name, name_1st)

Can i call it multiple times on inputs like

(2335, 55, '246BDH', '246BDH', '811', 1),
(2336, 55, '038THX', '038THX', '831', 1),
....

The function is an UPSERT implementation returning void. This is meant to be a warehouse database (fact table). We have a live database that gets more than 1000 inputs per hour. We want to use this command to merge our data with the new data.

It is a table function. (I have defined alike functions to every table we have - that's 5.) Result is, it will update existing rows, if they exist, otherwise inserts new row.

Postgres version is 9.3.

Function is a version of the accepted answer here:

Idiomatic way to implement UPSERT in PostgreSQL


texts: Linux SQL Standard Edition being killed by OOM killer

texts:
We have launched Linux with SQL Server standard edition for production environment, but the memory restriction feature in SQL Server is not working properly.

The server has a total of 32 GB of RAM.  We initially allocated 25 GB to SQL Server in mssql.conf and experienced crashes due to the OOM killer.

We have since reduced this to 18 GB memory, but it's still utilizing all the server memory, and the service is eventually killed.

What else can we do to prevent the SQL Server service from being killed by the OOM Killer?


texts:
On my production system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns CPSDOM\mconnors.

We use Windows authentication on our DBs.

On my test system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns dbo.

So I my test system I went to security/logins/ Login Properties/UserMaping and entered in CPSDOM\mconnors for the user. When I run SELECT CURRENT_USER, I still get back dbo. I went back to the Login properties/UserMapping to verify the change was still there and it was.

What am I missing?


texts: How can I make only *one* user controlled database

texts:
How can I system admin let it be me, but another users and admins must be cannot access my database.
My database located on customer server. I deleted all sql users, there's only sa user. This persons don't know my sa password. But this persons created sa similar users, and can access my database. I want block this users to my db.


texts: Speeding up COUNT(*) with multiple equality, in and join conditions

texts:
I've got a little problem with how my SQL Server query performs.

This basically looks as follows (It's been generated by Dapper):

SELECT COUNT(*)
FROM Data AS D
INNER JOIN #Permission AS P
    ON P.CategoryId = D.CategoryId
    AND P.IndustryCode = D.IndustryCode
    AND P.GeographyId = D.GeographyId
WHERE D.Local = 0
AND D.DatasetId = @DatasetId
AND D.CurrencyId IN (@Currency0, @Currency1, @Currency2)
AND D.ExchangeId IN (@Exchange0, @Exchange1, @Exchange2);

Table has tens of millions of records and my counts seem to be too slow, taking like 15 seconds to execute, which is far too much. My temporary table contains about 100k rows. But this might vary due to user input.

This is the execution plan: https://www.brentozar.com/pastetheplan/?id=ryXwfmp8m

Table has the following relevant indexes:

CREATE NONCLUSTERED INDEX IX_Data_Test ON dbo.Data (Local, DatasetId, CurrencyId, ExchangeId) INCLUDE (CategoryId, IndustryCode, GeographyId)
WHERE Local = 0;

CREATE NONCLUSTERED INDEX IX_Data_Another ON dbo.Data (CategoryId, IndustryCode, GeographyId, Local, DatasetId, CurrencyId, ExchangeId)
WHERE Local = 0;

Both indexes seem to be useful, but SQL Server will ignore the second one and it builds query that will do the following:

Seek predicate on Local
Seek predicate on DatasetId
Seek predicate range on CurrencyId (will build a internal values table first)
Predicate range on ExchangeId
Then do a HASH MATCH with #Permission table

What could be general recommendations to speed performance up of such a query? I have not yet tried dropping indexes and keeping the ones that have (CategoryId, IndustryCode, GeographyId) as first keys, but being able to filter these rows first would help a lot.

Also, I dislike IN clause that causes seek predicate ranges. Is it a good idea to rewrite query to run query per @Currency and per @Exchange and then SUM() their counts?


texts: PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files?

texts: I have a PostgreSQL 9.1 hot standby slave that had all of its wal files removed. How can I restart the standby server and have the slave resync from the master? Thanks


texts: Some tables not found after moving MySQL database from Linux to Windows

texts:
I had a MySQL database which I was working with from Linux. Some of the schema/table names were in mixed case. I then needed to work with the same database on Windows, but because of the enormous size, doing mysqldump simply did not work (I know it's usually the best option, but I spent many days trying to make it work, to no avail). So, I copied the data files to an external drive and then set the Windows machine to look there when starting the MySQL server.

This worked great, except that I hadn't realized the mixed casing would be a problem on Windows. I can see the schemas/tables in the MySQL Workbench Navigator, but cannot perform any operations on them. If I try, I get error code 1146: Table 'x' doesn't exist.

The original database I was using on Linux, unfortunately, has been corrupted since then, so I am unable to go back and change the case of the schemas/tables there. I tried having the Linux machine read the copied data with the goal of changing the mixed case names, but it is no longer able to read the copied data since it's been modified on the Windows machine.

Is there some way I can enforce case sensitivity on Windows? Or is there some way I can change the cases of the schemas and table names without using MySQL (since it thinks those tables don't exist)? Any other solutions? I really need the data in the mixed case schemas/tables but currently see no way of getting it out.


texts: Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

texts:
I am looking at snapshot replication between two servers. Here is what I have:

500GB database
~500MB nightly bcp loads
~50MB daily transactions

I was asking other DBAs in the company about which replication type to use. I was told to use Snapshot Replication. However, from what I understand and have been reading, every night after the load the snapshot will completely copy the database over to the distributor and then completely overwrite the other server.

Do snapshots work in deltas or is it a complete copy every time?


texts: Postgresql responses are slow without internet

texts:
I have a Postgres service in Centos 7 and it works normally.

Each time when the internet service (by an IPS) is down, the responses are slow, other times the DNS is reset by an electrical trouble, forcing to set 192.168.2.1, and the responses are slow too.

Trying to solve the trouble, I change the DNS (192.168.2.1 - this is my lan dns) to [8.8.8.8] (google's one) and the responses comes back to work normally.

Anyone can tell why this happens?
Have I configured something wrong in the installation?

Note: this happens in 15 database servers (one per store).


texts: Is there any reason for an index to contain the primary key?

texts:
I inherited a project using SQL Server 2005.  Recently there were some performance problems and I started looking at the indexes, and found

_dta_index_survey_25_135059617__K1_K10_K19:
    id ASC, sent_date ASC, group_id ASC

and

_dta_index_survey_21_364632442__K18_K1_K2_K9:
    group_id ASC, id ASC, campaign_id ASC, sent_date ASC

which appears to be generated by some kind of automatic performance tuning tool.  I think SQL Server comes with something like that but I'm not familiar with it.

Anyway, id is the primary key, and what I'm trying to understand here is how it could possibly be beneficial to include the primary key within a secondary index... because you have to have the leftmost pieces of information to use an index and if you know id then you've got the row anyway.

Can someone explain why a tool would have generated these indexes and if there's any point in keeping them?

Just because the indexes are getting used doesn't mean they're a good design.  SQL Server is using the wrong indexes lately, and I'm trying to determine why. More specifically, queries against this table that ran in milliseconds a month ago are now taking multiple seconds.  The table is huge (4M rows), and growing steadily, but at the same pace it's been going at for years.

Almost every query joins across group_id or campaign_id.  Often both.  The application almost never specifically selects or sorts by the PK, but I could see how ID could be useful when comparing or merging lists of IDs within the database engine.  ... But id is also an identity column, and an index holds lists of row ids, so it still seems redundant to have the ID in the key when it's also in the index "buckets".  And the additional fields could still be covered by the index without being part of the key.

I discovered that (id, sent_date, group_id) hardly ever gets read.  Meanwhile (group_id, id, campaign_id, sent_date) is one of the hottest indexes.  Next I found that an index which I thought was pretty reasonable (campaign_id, sent_date, group_id, id) also has a index_columns_include of every other column in the table, and occupies more space than the primary clustered index on id!  (but it's also pretty hot...)  Surely I can replace it with a simple (campaign_id, sent_date, group_id)?


texts: Log all table updates for all tables in MySQL

texts:
Is there anyway I can get the last updated time of a set of tables? By last updated time, I mean not only when the table's schema changed but when any of the rows changed or even when rows were created/deleted in the given set of tables.

I tried both SELECT * FROM information_schema.tables and SHOW TABLE STATUS FROM myDb and I only see the create_time which is only when table was created.

I can alternatively create triggers like:

CREATE TRIGGER tbl1_delete AFTER DELETE ON tbl1
FOR EACH ROW
BEGIN
  UPDATE table_updates SET update_time = now() WHERE table_name = tbl1
END

But, now I have to repeat this over and over for deletes, updates, creations and schema changes. And, I want to automate this for all my tables!


texts:
We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load.

It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary tables to pass the data to main SP?

DELIMITER $$

drop procedure if exists `GeoAreaFlattened_Select`;

create procedure `GeoAreaFlattened_Select`(
    _areas MEDIUMTEXT,
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
begin

drop temporary table if exists areas;

-- areas
call CreateAreas(_areas, _comparisonGroup, _parentArea, _areaType);

SELECT
    areas.ID,
    areas.Code,
    areas.Name,
    areas.LevelId,
    GeoAreaLevel.Name AS AreaTypeLabel,
    GeoAreaLevel.Identifier AS AreaTypeIdentifier
FROM
    areas
INNER JOIN
    GeoAreaLevel
ON
    areas.levelid = GeoAreaLevel.id
ORDER BY areas.name ASC;

drop temporary table areas;

end

The nested SP:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
drop procedure if exists `CreateAreas`;

DELIMITER $$

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    end if;

END


texts: Seeking Suggestion for faster DB Insertion Policy

texts:
I have written a server which keeps on receiving concurrent requests from 30K-40K different clients concurrently. Now I need to dump all those individual requests into a database. The database right now is SQL Server.

My server has a thread pool for handling 30k-40K clients and I literally can't call some blocking calls in those threads to directly insert into SQL Server. This will literally beat the design of my server.

What efficient design should I choose for inserting into the database?

Does anyone recommend a MQ (Message Queues) design? Like from server threadpool, threads directly write into MQ and the consumer will take care of dumping what ever exists in MQ into SQL database. But again if there is some fishy happens in SQL like latency in response, then the entire Queue will be kept on piling up on and on.

This is an undergrad final year project. I am sorry but I am not a DB expert. I will be using some community edition of SQL Server for Proof of Concept, and there will be one huge table close to 14-15 columns where activity of each client will be recorded.

Even for batch upload, I need to buffer data from each client to somewhere. Right now my concern is about deciding the strategy From Server to some layer (let's it call x) that will pool the data. I am not going to directly call INSERT From the Server receive thread, for obvious reasons. There will be a separate process/service that I call consumer that will take care of doing Bulk Insert in DB. I seek experts to get me the view about data polling techniques, like RabbitMQ etc. I have coded the Server in C/C++.

There is no FIFO requirement, but indeed whoever sends the data first and that data needs to get saved into DB soon. There are 30K-40K clients and at any second at least there are close to 60% of the clients sending data to Server.


texts: Display posted on date and time like1 hr ago or 30 min ago

texts:
I want to show the posted date like 1 hr ago, or 30 min ago. This is my code:

declare @date datetime ='2014-07-24 11:50:22.033';
SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN
  CASE WHEN DATEPART(HH,@date)=DATEPART(HH,GETDATE()) THEN
      CAST(DATEDIFF(MINUTE, @date, GETDATE()) AS NVARCHAR(50)) +' Minutes ago '
     ELSE
      CAST(DATEDIFF(HOUR, @date, GETDATE()) AS NVARCHAR(50)) + ' Hours ago  ' END
 ELSE
  CAST(DATEDIFF(DAY, @date, GETDATE()) AS NVARCHAR(50)) + ' Days ago.. '
END AS Postedon

I am getting a problem when the posted time is 11:50 and the server time is 12:10. In that case, I would like to show "20 min ago", but my code showing 1 hr ago.


texts: How to full text search worked with special character like "of"?

texts:
How to search with fulltext search if keyword has special character like

"of", "-" ...etc

select * from table1
join CONTAINSTABLE (table1,(Title,Description_HTML),
'"of" and "Department"')
tb2 on tb2.[key] = table1.ID

I am unable to view the result?


texts:
I'm running into a problem when trying to run any of the jobs created by Ola's script:

  The process could not be created for step 1 of job
  0x19DAD4A7019FB04D9C984027FCA2F4E3 (reason: The system cannot find the
  file specified).  The step failed.

I've made sure that sqlcmd is in the PATH environment variable, and I've also put the sql agent service account in local administrators to make sure it isn't a permission issue.

When I log into the server locally with an account that has local admin rights and sysadmin on the sql server and try to run sqlcmd.exe I get the following:

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [2]. .

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout
  expired.

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and i f SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..

Has anyone else run into this issue?


texts: MariaDB uses HAProxy IP instead of the WebServer IP

texts:
I have a HA Setup that goes like this:
I made a user for the web server:
grant all on user_db.* to 'db_user'@'10.10.10.26' identified by 'password';

But when I try to access it via this command: Haproxy runs on 3306
mysql -u db_user -p -h 10.10.10.14 --port=3306

I get:
Access denied for user db_user@10.10.10.15 or 10.10.10.16

Why does it use my HAProxy IPs? Is there any way to make it use my Web Server IP instead, for security reasons. Or is there any way to prevent my HAProxy servers from receiving data from a different IP? Example: allow only 10.10.10.26 then block the others.


texts: find rows with no child and if has child latest child

texts:
I have a simple table like this:

id
parent_id
created_at

with parent/child relationship.
If a row is child, then it has a parent_id, else its parent_id is 0.

Now I want to select all rows that either have no child (so itself) or have a child, so get the latest child by created_at (and not include itself in the final results).

As a visual example you can look at this tiny picture:

I just want rows 24 and 27 to be selected.


texts: More table or more database for better performance?

texts:
Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside.

Which option is better for server performance?

1) 400 databases each have 1000 table?

2) 4000 databases each have 100 table?

Update: I see i need to put some more details, sorry for trying to ask simple as much as i can.
Site will based on wordpress multisite with http://wordpress.org/extend/plugins/hyperdb/

1 website with 10 database table generally wont pass 6-7 mb as total database info. There will be 9-10 heavy website that will have serious database info, i will put them in a special database. ( lets say 4096 database, 96 reserve for vip,heavy sites and split all other sites in 4000 database )

Server is:
http://www.wiredtree.com/managedhybrid/
After reaching some point, i am planning move to pure dedicated server which will based on raid10 15k RPM SAS mysql discs and different media discs

So 40.000 sites but all of them sharing same php files, just different databases and different upload folders. This is how wordpress.com working. Same rules but my sites wont be a blog.

Please ask what other info do you want, i will try to explain more. I am very sorry if i break any rules or something.

( Sorry for bad english too :P )


texts: Extend Query to include additional column

texts:
I have the following query, which gives me (in addition to other columns) the Direction ID only:

SELECT Users.Name, Users.Surname, Transactions.Timestamp, AccessPoints.Description as Door, Readers.Direction
    FROM (Readers INNER JOIN (AccessPoints INNER JOIN Points_Readers ON AccessPoints.PointID = Points_Readers.PointID)
    ON Readers.ReaderID = Points_Readers.ReaderID) INNER JOIN (Transactions INNER JOIN (AccessNumbers INNER JOIN Users
    ON AccessNumbers.AccessNumberID = Users.AccessNumberID) ON Transactions.Number = AccessNumbers.Number)
    ON Readers.ReaderID = Transactions.ReaderID
    WHERE (Transactions.Timestamp>#2018-08-15  06:50:16#) ORDER BY Transactions.Timestamp;

What I want to do is extend the query to include Directions.DirectionsText by joining Readers.Direction to Directions ON Directions.Direction. I'm having trouble with exactly where to place the join so that I don't keep getting errors in the FROM clause. (I still haven't figured out how to get INNER JOIN clauses working with Access' need to use parentheses for joins.)

The columns in Directions are Direction (numeric) and DirectionText (String)


texts:
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.

I need something like

SELECT * FROM table AS t WHERE ANY(t.* IS NULL)

I don't want to have to do

SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL

This would be a HUGE query.


texts: Finding when a currently running Extended Events session started?

texts:
For old-school traces, I can look in sys.traces or the SQL Server log to find when a trace was started. Is there something similar for Extended Events? I came across this blog entry about using a DDL trigger but I'm wondering if there is something better.

SQL Server 2012-2014


texts: VACUUM ANALYSE not reducing index bloat, only VACUUM FULL

texts:
We are experiencing many slowdowns due to index bloat. On trying to optimize the index, recreating it seems to generate a much smaller index:
reviews_hotel_id_connection_id_review_date_created_at_review_id: original index
reviews_hotel_id_connection_id_review_date_created_at_new: exact duplicate of the original index, recreated
 reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 5296 kB
 reviews | reviews_hotel_id_connection_id_review_date_created_at_new       | 2944 kB

The new index is only 55% the size of the original index!
Running VACUUM ANALYSE reviews doesn't result in any index reduction!
Running VACUUM FULL reviews makes the original index have exactly the same size as the new index:
 reviews | reviews_hotel_id_connection_id_review_date_created_at_new       | 2944 kB
 reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 2944 kB

Although VACUUM FULL locks the table even for reads! Is are an option for optimizing indexes without causing downtime?


texts: Can't create linked server SQL Server 2005

texts:
I'm new to doing linked server and I have this error.

"The linked server has been created but failed a connection"

On the same virtual machine I have two SQL Server instances

one 2000 (VMDEVCIAQ\NMP)
one 2005 (VMDEVCIAQ\NMPUTIL).

My setup :

The 2005 try to modify tables in the 2000.
I enabled remote connection on both instance.
I tested manually connecting to the instance of 2000 and it worked.

What I input in the form

Linked server: NMP
Provider : Microsoft OLE DB Provider for SQL Server
ProductName: SQLServer
Data source : VMDEVCIAQ\NMP

In the security tab I set "Be made using this security context" with the tested login and password.

So any idea why this would fail ? Any pointer or doc would be appreciated.

Cheers


texts: How to connect to Oracle 11g using Oracle-SQL-Developer from a Mac

texts: I am currently running Oracle 11g on my Windows 10 machine, but I use my mac a lot and I want to connect to the installed Oracle 11g on my windows from my mac using  SQLDeveloper. How can you achieve that? I have made a few attempt using videos on youtube and following a few documentation on Oracle website without any success.


texts: DBCC CheckDB result sent to OSQL poses confusion

texts:
Reformulated the Question

I have a .BAT script that runs the DBCC CheckDB and repairs the database if needed. The result of the DBCC CheckDB is sent to OSQL through the -b flag and the .BAT determines if there's an error using "errorlevel 1"

The problem I'm seeing is that when there's an error that is repaired, the result returned to OSQL still shows as if there were errors... but in reality it was repaired and there isn't any errors left.

Is there a way that the DBCC checkdb would return results to the .bat based on the end result (If there are still errors or not)? maybe with a different errorlevel, a different OSQL flag or maybe using a different method?

In summary, My script returns an error message even though the dbcc checkdb fixed the errors.

More info

Below is a simplified version (simplified as much as I can)... this script usually runs with parameters (10 of them)...

    rem p8=REPAIR  --> Parameter REPAIR when the user has agreed for possible data loss
    set p8=BASIC

    :WINauth
    if %p8%==REPAIR goto WINRepair

    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "DBCC CHECKDB(DemoCorruptMetadata2008R2)" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto WINDBCheck
    goto end

    :WINDBCheck
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto GeneralError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "DBCC CHECKDB(DemoCorruptMetadata2008R2,REPAIR_REBUILD)" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto WINDBCheckError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE"
    goto end

    :WINDBCheckError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE"
    goto DBCheckerror

    :WINRepair
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto GeneralError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "DBCC CHECKDB(DemoCorruptMetadata2008R2,REPAIR_ALLOW_DATA_LOSS)" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto WINDBRepairError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE"
    goto end

    :WINDBRepairError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE"
    goto DBRepairerror

    rem +++++ Exit Routine +++++

    :GeneralError
    echo Cannot Put the database in Single user mode. > c:\DBCC_CHECKDB.log
    goto end

    :DBCheckerror
    echo We suggest to use the "Repair" Option > c:\DBCC_CHECKDB.log
    goto end

    :DBRepairerror
    echo CHECKDB with higher level of repair failed. > c:\DBCC_CHECKDB.log
c:\DBCC_CHECKDB.log
    goto end

    :end


texts: Mongodb find() not working as expected, PHP

texts:
Mongodb find() seems Not to work, I have tried all I could and nothing works so far. using PHP7+

I have a Document in the strure like the image below

In SQL I could do something like:SELECT....... where 'common' LIKE '%Burk%'

The simple query is to call the countries collection and produce Burkina Faso as output

  QUERY 1:

$countries_tb= $db->selectCollection('country_city_data');
 $countries = $countries_tb->find([],[
    'common' => new MongoDB\BSON\Regex('Burk')]);
var_dump($countries);

the var_dump prints a list of all the countries in the database instead of getting only Burkina Faso

  QUERY 2:

when I do find(without the empty []), it var_dumps nothing necessary

$countries = $countries_tb->find(/*without the []*/ [
    'common' => new MongoDB\BSON\Regex('Burk')]);

var_dump($countries);

I get this:

I have also tried this format.

  QUERY 3:

 $countries = $countries_tb->find(
                     array('name'=>
 array(  'common'=>new MongoDB\BSON\Regex('Burk'))));

                var_dump($countries);

Still no success.

As suggested by @noobProgrammer from SO below, doing like this:

$countries_tb->find(['name' =>
                  ['common'=>new MongoDB\BSON\Regex('Burk')]],
                  ['typeMap' => ['root' => 'array', 'document' => 'array']]);

Produces this;


texts:
I know that this query will lock table2:

UPDATE table1... SELECT .. FROM table2

How about this query ?

INSERT INTO table1... SELECT .. FROM table2

Does this query also create a lock on table2?


texts: Is binary log purging done by expire_logs_days equivalent to PURGE BINARY LOGS?

texts:
In a MySQL replication environment where replication is done with binary logs, if you run PURGE BINARY LOGS on the master, the server will not delete logs that are currently being replicated by a slave according to the documentation:

  This statement is safe to run while slaves are replicating. You need not stop them. If you have an active slave that currently is reading one of the log files you are trying to delete, this statement does not delete the log file that is in use or any log files later than that one, but it deletes any earlier log files.

I couldn't find however any description of whether or not the log purging done by setting the expire_logs_days variable has the same behaviour, e.g. it will not touch logs that are currently being replicated.

Can anyone tell me if the two are equivalent?


texts: Database Exceptions with Jetty 8.1.0 with BTM

texts:
We are running Jetty 8.1.0 with BTM 2.1.2.

We are using Postgres as our DB.

Everything works great except that when we run any query with a condition where sometable's primary=null, the server loses its connection to the DB. The following is the exception  we see:

  org.postgresql.util.PSQLException: ERROR: invalid input syntax for
  integer: "null"at......long stack dump ensues... ...Jun 15, 2012
  12:20:19 PM org.eclipse.jetty.server.AsyncHttpConnection handle INFO:
  EndPoint making nolong progress:stack 100dump
  SCEP@162b333{l(/74.93.245.209:60506)<->r(/10ensues.99.21.87:80),d=true,open=true,ishut=false,oshut=false,rb=false,wb=false,w=false,i=5r}-{AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3,h=-1,b=20909,c=-1},p=HttpParser{s=0,l=16,c=0},r=1}
  AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3,h=-1,b=20909,c=-1},p=HttpParser{s=0,l=16,c=0},r=1
  Jun 15, 2012 12:20:19 PM org.eclipse.jetty.server.AsyncHttpConnection
  handle WARNING: Closing EndPoint making no progress: 200
  SCEP@162b333{l(/74.93.245.209:60506)<->r(/10.99.21.87:80),d=true,open=true,ishut=false,oshut=false,rb=false,wb=false,w=false,i=5r}-{AsyncHttpConnection@18fc7ca,g=HttpGenerator{s=3

Any query after this incident breaks with the following exception

It's happening on our Production server! What is causing this problem?

UPDATE:

Here is the code!

I am aware that we shouldn't query with primarykey = null as primary can never be null. But its a parameter and null validations were missed!

Surprisingly, Jetty 6 works great with it! Even after running this query, it has no problem at all. Its just Jetty 8-1-0 thats lossing DB connectivity permanently upon running such query. Until I restart the ser

String query = "select * from table where primarykey="+parameter;
Statement lStatement   = null;
Connection lConnection  = null;
ResultSet lResultSet   = null;
try  {
   lConnection = DBAccess.getConnection();
   lStatement  = lConnection.createStatement();
   lResultSet  =  lStatement.executeQuery( query );
   while ( lResultSet.next() )    {
       mLogger.info("The result set is : "+lResultSet.toString());
   }
}  catch( Exception e )  {
    e.printStackTrace();
    mLogger.error("Exception occurred while trying to runQuery : "+e.getMessage());
}  finally  {
    DBAccess.closeResultSet( lResultSet );
    DBAccess.closeStatement( lStatement );
    DBAccess.closeDBConnection( lConnection );
}

UPDATE

can Someone please help me with this. I am still looking for an answer. I am running into different problems day by day with Jetty 8-1-0.

I removed the Transaction management config in Jetty.xml and deployed with default JNDI config. Things were smooth for sometime.

Last week, the server started crying with "Too Many open files" issue. It got fixed once I increase the number of files that can be open on the server.
Today, Jetty is dead again with OutOfMemoryError.
Whats noteworthy here is the Memory leaks happen at the same place where the "Too May Open Files" error occurs. I am guess its the HTTPClient thats causing the problem. (Well, I will post the same in stackoverflow. I figure its not a DBA issue. But all these started at the same place. so, I want to keep a record of it here if that makes sense.


texts:
I am in the process of writing a restore database script. I want to be able to connect as sa and run the restore script.

When I run SET SINGLE_USER does this mean that my login is the only user access to the database? Or how is this determined? I want to use this as I get an error saying other connections are active.

Below is my restore script to date. Are there any improvements I can make to this?

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks SET READ_ONLY;
GO

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH
MOVE N'AdventureWorks_dat' to N'H:\Data\AdventureWorks.mdf',
MOVE N'AdventureWorks_log' to N'E:\Logs\AdventureWorks.ldf',
STATS = 5;

GO

ALTER DATABASE AdventureWorks SET MULTI_USER;
GO

ALTER DATABASE AdventureWorks SET READ_WRITE WITH NO_WAIT;
GO


texts: Altering the location of Oracle-Suggested Backup

texts:
On one database, the Oracle-Suggested Backup scheduled from Enterprise Manager always ends up in the recovery area, despite RMAN configuration showing that device type disk format points elsewhere.

As far as I can see, the scheduled backup job is simply:

run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA_OEM_LEVEL_0';
backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;
}

Asking RMAN to show all reveals that device type disk is indeed configured to store elsewhere:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/s01/backup/PROD11/PROD11_%U';

If I run the script manually,  the backupset is placed at the above location, when the script is run from the job scheduler the backupset goes to the RECO group on ASM,

Why might Oracle still choose to dump the backupset to the db_recovery_file_dest?

Ultimately, how can I change the backup destination?


texts: SELECT items that has one or more specific TAGS

texts:
I have three database tables.

One with items, one with tags and one with items_has_tags.

Tags

CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'tag one'),
(2, 'tag two'),
(3, 'tag three'),
(4, 'tag four'),
(5, 'tag five');

Items

CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `items` (`id`, `name`) VALUES
(1, 'first item'),
(2, 'second item'),
(3, 'third item'),
(4, 'fourth item');

Item has tag

CREATE TABLE `item_has_tag` (
  `item_id` int NOT NULL,
  `tag_id` int NOT NULL,
) COMMENT='';

INSERT INTO `item_has_tag` (`item_id`, `tag_id`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 3),
(2, 4),
(3, 2),
(3, 3),
(3, 4),
(1, 2),
(4, 3),
(4, 4),
(4, 5);

I want to only select items that have all the specific tags, such as the items that item_has_tag.tag_id = 1 AND item_has_tag.tag_id = 2 AND item_has_tag.tag_id = 3 for example.

edit

The amount of specific tags varies from 1 to 4-5.

edit
It is the items list that needs to be returned.

Would you like to help me?


texts: Anyone else had problems uninstalling SQL Server 2016 RC0?

texts:
We installed RC0 the day it was released and, for needs of space and project transitioning, needed to reclaim the space.

As I couldn't find an uninstall option in the installation UI, I resorted to the right-click and uninstall via the Programs and Features control panel list. Up comes the 3 options and Remove is what I needed.

The uninstall stated all green ticks for all components, having selected everything available.

Once closed out, the P&F list still shows everything present, albeit the version and size against the 64-bit SQL Server program are cleared out.

I can still right-click and remove, which is weird, so, seeing only the SDK, check that, remove and it clears a few more things that weren't in the selection list.  More weird.

Reboot the server!  Done.  Entrails are still all there.  Huh?  Right-click remove, same SDK there, select, remove, more unlisted things removed.

Resorted to individually removing the 2016 artifacts, many and various, not all starting with a common name, of course, so look for "2016" and whack away.  Lots of "if you whack this, x and y and 20 others won't work..." warnings.  Whacked 'em.

So, I'm still left with:

Microsoft SQL Server 2016 RC0 (64-bit) - no size or version
Microsoft SQL Server 2016 RC0 Setup (English)

Stubborn little diggers.

Does anyone have any advice on how to cleanse the server of RC0, please, especially as we'll likely put RTM on the machine when the time comes..

Thanks in advance (from an accidental DBA)


texts: What is the difference between SQL Server Native client versions 10 and 11? Can I use Native Client 10 with SQL Server 2012 running?


texts: Vaccum Analyze on tables (azure database for postgresql)

texts:
Looking for some inputs on the below.
How to run VACCUM ANALYZE command manually on all tables in a database on Azure database for postgres. Can i get the script pls?
-Kevin


texts:
I am trying to select records in a postgresql db where username is not like a list of strings.

SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%';

The issue is there are a lot of these values. Is there a way to create an array of those and say something like:

SELECT * FROM rails_db WHERE username NOT LIKE ARRAY[my values];


texts: Select only those records that have different/multiple values for a particular column

texts:
Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values:
Member table
ID   LASTNAME    FIRSTNAME    EMAIL
567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
678  Black       Ted          tedblack@gmail.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

I would like the result to be:
567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

Notice that Ted Black is missing because he only has one entry for email address.
I should clarify that my membership table has more than 4 columns. There are additional columns for phone and address, etc. And there could be multiple entries for a member because he/she has more than one phone number or address. I only want to capture those individuals who have multiple email addresses.
This is part of a database cleanup and a primary key will be added. I should clarify further that some persons could have multiple entries with same email address. At this phase I don't want to capture those multiple entries with the same email address but only those have who multiple entries with different email address.


texts: Ensuring that 1 row has 5 specific records referencing it

texts:
Context: a TEST has many CASEs. Typical.

However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5).

I could do something like this:

create table TEST (
   ID           Integer                                   not null,
   CASE1_OK       varchar                                 not null,
   CASE2_OK       varchar                                 not null,
   CASE3_OK       varchar                                 not null,
   CASE4_OK       varchar                                 not null,
   CASE5_OK       varchar                                 not null,
   CHECK        (CASE1_OK IN ('PASS', 'FAIL', 'INVESTIGATE'))         ,
   ...
   constraint pk_TEST primary key (ID)
)   ;

That's obviously a violation of 1NF.

Is there a way of having a separate CASE table while ensuring that TEST record can not be inserted without having exactly 5 existing CASE rows for it?


texts: postgres cluster: Crash of master crashes the replicas too

texts:
Environment:
Postgres version: 9.6
Cluster of 3 servers, with Patroni and etcd
Scenario:
When index of tables were started with 16 parallel requests (on a 16CPU machine), postgres on the master crashed with Linux OOM killer. This was a 124GB machine. We understand that spawning so many parallel requests needs more memory and we have addressed that.
Problem:
However, the concern is that, when the master crashed due to OOM, all the replicas too crashed. This is something not expected and puts the high availability of the cluster in question. We can easily simulate this and every time the behaviour of the replicas is exactly the same.
The logs of the master when the crash happened:
2020-12-16 09:54:44 UTC [11619]: [9-1] user=,db=LOG:  checkpointer process (PID 30834) was terminated by signal 9: Killed
2020-12-16 09:54:44 UTC [11619]: [10-1] user=,db=LOG:  terminating any other active server processes
2020-12-16 09:54:44 UTC [30838]: [1-1] user=,db=FATAL:  archive command was terminated by signal 3: Quit
2020-12-16 09:54:44 UTC [16870]: [1-1] user=postgres,db=mydbWARNING:  terminating connection because of crash of another server process
2020-12-16 09:54:44 UTC [16870]: [2-1] user=postgres,db=mydbDETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-12-16 09:54:44 UTC [16870]: [3-1] user=postgres,d=mydbHINT:  In a moment you should be able to reconnect to the database and repeat your command.
...
2020-12-16 09:54:59 UTC [24609]: [1-1] user=postgres,db=mydbFATAL:  the database system is in recovery mode
...
2020-12-16 09:55:04 UTC [22780]: [4-1] user=,db=LOG:  redo done at 52712/BAFFD3C0
2020-12-16 09:55:07 UTC [11619]: [13-1] user=,db=LOG:  database system is ready to accept connections

The logs of the replica, when the crash happened:
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-12-16 09:54:44 UTC [13293]: [2-1] user=,db=FATAL:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
...
2020-12-16 09:54:55 UTC [12843]: [19675-1] user=,db=LOG:  restored log file "00000007000526C70000005D" from archive
...
2020-12-16 11:19:18 UTC [12843]: [38972-1] user=,db=LOG:  restored log file "0000000700052712000000C1" from archive
2020-12-16 11:19:20 UTC [18104]: [1-1] user=,db=LOG:  started streaming WAL from primary at 52712/C2000000 on timeline 7

Question: Will the postgres crash on the master (due to OOM/corrupted shared memory), necessarily cause a similar crash on the replicas too? Is there a way to circumvent this?*


texts: Postgres files postgresql.conf and pg_hba.conf - which take priority?

texts:
Which of the two postgres configuration files postgresql.conf and pg_hba.conf takes priority?
pg_hba.conf controls client authentication methods including 'md5' and 'scram-sha-256'
postgresql.conf includes an entry for password_encryption which can be 'md5' or 'scram-sha-256' (or blank, I guess)
So if these values aren't set to the same thing, which takes priority?


texts:
I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer.

I use this query:

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name";

But the size I see in HeidiSQL for RDS is much smaller.

Before = 253MB
RDS = 100MB

An example of a table with similar rows, but very different size:

Before

RDS

Is there any possible reason, or I am missing something?


texts: Migrate logins tagged to specific databases users

texts:
We are doing migration from SQL2008R2 to SQL 2014 for some of the databases hosted on 2008R2 to 2014 and remaining DB's to other servers.

I am aware of migrating the login using dbatoo or using sp_helprevlogin..

But using that it would move all the logins from 2008R2 instance to 2014.

Since we are only moving some databases we only need to move their respective logins or i should say only those logins that are present under users in the database geting migrated to 2014 instance.

How can we achieve this intelligently via script, because doing manually is taking lot of time as we have hundred of logins and about 40 databases


texts: Trigram search gets much slower as search string gets longer

texts:
In a Postgres 9.1 database, I have a table table1 with ~1.5M rows and a column label (simplified names for the sake of this question).

There is a functional trigram-index on lower(unaccent(label)) (unaccent() has been made immutable to allow its use in the index).

The following query is quite fast:

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')));
 count
-------
     1
(1 row)

Time: 394,295 ms

But the following query is slower:

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword and some more%')));
 count
-------
     1
(1 row)

Time: 1405,749 ms

And adding more words is even slower, even though the search is stricter.

I tried a simple trick to run a subquery for the first word and then a query with the full search string, but (sadly) the query planner saw through my machinations:

EXPLAIN ANALYZE
SELECT * FROM (
   SELECT id, title, label from table1
   WHERE lower(unaccent(label)) like lower(unaccent('%someword%'))
   ) t1
WHERE lower(unaccent(label)) like lower(unaccent('%someword and some more%'));

Bitmap Heap Scan on table1  (cost=16216.01..16220.04 rows=1 width=212) (actual time=1824.017..1824.019 rows=1 loops=1)
  Recheck Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more%'::text))
  ->  Bitmap Index Scan on table1_label_hun_gin_trgm  (cost=0.00..16216.01 rows=1 width=0) (actual time=1823.900..1823.900 rows=1 loops=1)
        Index Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more%'::text))
Total runtime: 1824.064 ms

My ultimate problem is that the search string comes from a web interface which may send quite long strings and thus be quite slow and may also constitute a DOS vector.

So my questions are:

How to speed up the query?
Is there a way to break it into subqueries so that it is faster?
Maybe a later version of Postgres is better? (I tried 9.4 and it does not seem faster: still the same effect. Maybe a later version?)
Maybe a different indexing strategy is needed?


texts:
I'm trying to create a new field based in another (sum field)

SELECT sum(price) AS old_price,
   old_price+100 AS total_price
FROM sales

This returns Unknown column 'old_price' in 'field list'

sum is just a example, my real issue is to use with a select inside this field

What am I doing wrong?

Thanks.

--edit
I've used variables to reuse the alias, updated code:

SELECT @old_price := sum(price) AS old_price,
   @old_price+100 AS total_price
FROM sales

If you think that it is not a good practice, tell me.


texts:
I am trying to create a SP which will run batch update on about 100 million rows in a table. I created the below SP for it:

delimiter //

CREATE PROCEDURE `bulk_update`(IN table_name varchar(30))
BEGIN
DECLARE rows INT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET rows = 1;
WHILE rows > 0
do
SET autocommit=1;
SET @query =CONCAT('update ',table_name,' set is_summarized="N";');
PREPARE update_stmt FROM @query;
EXECUTE update_stmt;
SET rows = row_count();
select sleep(1);
commit;
DEALLOCATE PREPARE update_stmt;
END WHILE;
END //

It gives below error >>

    ERROR 1064 (42000): 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 'rows INT;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET rows = 1;
' at line 3

Strange part is that I am able to create the SP in MySQL-5.6, but it fails in MySQL-8 only. Can someone point me to what I am missing? Thank you


texts: joining tables without duplicate rows / NULL values

texts:
so I have multiple tables which I have to merge when query but both join and union doesn't seems to achieve what my object is

Union:

mysql>
SELECT  NAME, currentstatus, NULL AS username, NULL AS status
    FROM  statustable
    WHERE  Name like '%ahsan%'
UNION  ALL
SELECT  NULL AS NAME, NULL AS currentstatus, username, status
    from  awsaccount
    WHERE  username like '%ahsan%';
+--------------+-------------------+----------+--------------------------------+
| NAME         | currentstatus     | status   | username                         |
+--------------+-------------------+----------+--------------------------------+
| Ahsan Naseem | activated         | NULL     | NULL                           |
| NULL         | NULL              | ACTIVE   | ahsan.naseem       |
| NULL         | NULL              | INACTIVE | ahsantest                      |
| NULL         | NULL              | ACTIVE   | ahsantest2                     |
| NULL         | NULL              | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL              | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL              | INACTIVE | decent.ahsan         |
| NULL         | NULL              | INACTIVE | decent.ahsan_cli               |
+--------------+-------------------+----------+--------------------------------+
8 rows in set (0.00 sec)

Join:

mysql>
select  *
    from ( SELECT * from  statustable where     Name like '%ahsan%' ) as ab
    JOIN ( SELECT * from  awsaccount  where username like '%ahsan%' ) as asb;

    +--------------+-------------------+--------------------------------+----------+
    | Name         | currentstatus | username                       | status   |
    +--------------+-------------------+--------------------------------+----------+
    | Ahsan Naseem | activated         | ahsan.naseem                    | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantest                      | INACTIVE |
    | Ahsan Naseem | activated         | ahsantest2                     | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantestuserprofileupdate     | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantestuserprofileupdate_cli | INACTIVE |
    | Ahsan Naseem | activated         | decent.ahsan         | INACTIVE |
    | Ahsan Naseem | activated         | decent.ahsan_cli               | INACTIVE |
    +--------------+-------------------+--------------------------------+----------+
    7 rows in set (0.00 sec)

What I am trying to achieve is :

+--------------+-------------------+----------+--------------------------------+
| NAME         | currentstatus    | status   | username                       |
+--------------+-------------------+----------+--------------------------------+
| Ahsan Naseem | Active            | ACTIVE   | ahsan.naseem        |
| NULL         | NULL              | INACTIVE | ahsantest                      |
| NULL         | NULL              | ACTIVE   | ahsantest2                     |
| NULL         | NULL              | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL              | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL              | INACTIVE | decent.ahsan        |
| NULL         | NULL              | INACTIVE | decent.ahsan_cli               |
+--------------+-------------------+----------+--------------------------------+

I am new to sql and will be grateful for your advice and suggestion


texts: In SQL Server, data returned by sys.dm_os_performance_counters for "SQLServer:Broker Statistics" is not changing over time

texts:
In SQL Server [Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64)], I'm trying to determine just how much activity our Service Broker queues are processing. One tactic is to use sys.dm_os_performance_counters, and the stats in "SQLServer:Broker Statistics".

I am definitely getting data (no "zero rows" problem), and there is definitely significant activity on the queues... but the values returned are not changing over time.

For example, over the past 12+ hours, counter_name Enqueued TransmissionQ Msgs/sec returns 12502, and this number has not changed.

I haven't been able to find any references on this online. Any idea what might cause this?


texts: Simple Search on 13 Terabyte Oracle Database to return relevant rows (.net app? Java?)

texts:
I am sure there are some easy ways to do this... I have a simple flat Oracle table which is 13 terabytes or so in size due to a large number of rows accumulating over the years.  I need to do a search on it (can be looking for matches on any of a number of fields), and would like to return the most relevant records in a list, like top 50 or top 100 which I can then click on for more detail (will then query specifically one field in that particular record).

What .net or Java - library or package would you recommend for this kind of search in Oracle?  Any chance there might be an example open source application or example like that out there that does this exact thing?  I am hoping to find something that allows me to prioritize matches in certain columns first, and finds matches quickly despite the large table size, and can easily be integrated into the second part of the requirement which is a regular query based on a "thread" to which that record belongs.

Thank you.


texts:
I'm so extremely lost on 4NF it's not even funny.  I've been reading on it for literally days and, except for about 15 minutes ago, I wasn't sure that I had even broken the surface.

I think I might have just had the light bulb moment, but given the difficulty I had on this, I'd appreciate some confirmation. These are my notes:

Am I even close on this?


texts: How can I get SSMS to persist my custom keyboard shortcut?

texts:
I'm on Windows 10, and SQL Server 2012 Developer, with SSMSBoost installed.

I want to assign Ctrl + K, Ctrl + F to do SSMSBoost's Format command.  I do it through the normal Tools → Options → Environment → Keyboard → Keyboard method, and the shortcut works.  But when I quit SSMS and reopen it, the shortcut setting is gone.  Doing Exports of the settings in each state and diffing the two files also reflects this difference.

How do I get it to remember the shortcut between runs?

EDIT:

No added keyboard shortcuts stick at all, for any command.  Oddly enough, deleted ones do stick.


texts: Retrieve all product from one category (tree model)

texts:
I'm using MySQL, have categories in a tree model. One product belongs to only one (leaf) category.

What I want to accomplish is, given one category (any, could or not be a leaf or a root), return all products in a GROUP BY sentence.

I have managed to make the query,  but the category table is about 100.000+ rows so it is very slow to execute.

SELECT
        Publication._id, GROUP_CONCAT(title) as title,
        GROUP_CONCAT(sold_quantity order by Scrap.stamp) as sold,
        GROUP_CONCAT(Scrap.stamp ORDER BY Scrap.stamp) as dates,
        GROUP_CONCAT(price ORDER BY Scrap.stamp) as prices,
        Category.path_from_root
    FROM
        Publication, Scrap, Category
    WHERE
       (path_from_root LIKE '%" + cat + "%' OR
        path_from_root LIKE '%" + cat + "%') AND
        Category._id = Publication.category_id AND
        Scrap.id = Publication.scrap
    GROUP BY(Publication._id)
    ORDER BY Publication._id
    LIMIT 1,10

If I remove the category table, the query is super fast. I have the ability to change the  database structure. Category structure:

CREATE TABLE `Category` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `_id` varchar(300) NOT NULL,
 `name` varchar(300) DEFAULT NULL,
 `status` tinyint(4) NOT NULL DEFAULT '1',
 `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `total_items` int(11) NOT NULL DEFAULT '0',
 `active` tinyint(4) NOT NULL DEFAULT '1',
 `parse` tinyint(4) NOT NULL DEFAULT '0',
 `path_from_root` varchar(500) DEFAULT NULL,
 PRIMARY KEY (`id`)
)


texts: Capturing mongo replica set state from mongo shell

texts:
I am trying to capture mongo replica set state from mongo shell. E.g.

[root@server ~]$ mongo
MongoDB shell version v3.6.8
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.6.8
rs0:SECONDARY>

I'd like to capture that SECONDARY keyword, and to do it in one command. I've tried something like mongo > output, however it requires a manual ctrl+c to terminate the process.

I have tried
parse rs.status() output to display one secondary node for mongodump destination

But the solution doesn't always work. Different mongo version can output different message. ARBITER node does not have authentication data either.


texts:
I have a database that once held a large table: ~6 billion rows, ~6TB of disk space, ~10 rows inserted/second. The rest of the tables, all told, comes to ~500MB and have infrequent writes. So the big table was completely dominating the database.

I've since deleted that table, moving some of its work to more compact tables, so the DB load isn't gone but the disk space pressure is.

Other than the DROP TABLE bigguy that I ran, is there anything I need to do to make Postgres feel happier? Are there any global indices that need to be cleaned out?

Postgres 9.5 running on Amazon RDS.


texts: MongoDB restoring to existing replica set (avoid creating a new one)

texts:
I have a MongoDB cluster runnning on 3 AWS EC2 instances, 1 for primary and 2 secondary nodes. I'm trying to perform a backup and restore without having to recreate the replica set. mongodump seems a bad option for the long term if the DB grows too much. So I tried to follow MongoDB documentation to create volume backups for /data and /journal from a secondary but how the backup is restored when using a replica set remains unclear. So:

Will the primary and other secondaries be updated if I restore the backup to only one secondary?
If not, can I restore the secondary backups to the primary? (Including /data and /journal volumes). What should I do afterwards? Will the primary replicate the changes to the secondary?
Can I avoid recreating the replica set?


texts: How to update xml using value from a sql query

texts:
I have a table with an XML column. I need to insert various nodes and then set those nodes using values from a sub-query.

Setup:

use tempdb

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteInfo] [xml] NULL,
    [InVal] Varchar(50) NULL)

insert into [dbo].[Sites] ([InVal]) select 'ABC' union select 'DEF' union  select 'GHI'

update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'

update [dbo].[Sites] SET [SiteInfo].modify('insert <SiteID/> into (/SiteInfo[1])')

Here is my update in it's most simple form.

update Sites SET [SiteInfo].modify('replace value of (/SiteInfo/@SiteID)[1] with sql:column("InVal")')

I have tried with both singleton and non singleton nodes

The end result should preferably not be a singleton. i.e something like:

<SiteInfo>
  <SiteID>ABC</SiteID>
</SiteInfo>


texts:
I have two tables:

Games(
    name,
    current_step, #foreign key to Steps table
    #other fields...
)

Steps(
    name,
    game_id, #foreign key to Games table
    #other fields...
)

In other words what I want to achieve is a Steps table where each step is linked to its game and in some way I want to keep track of current step. Which is the best design to do this? Is circular foreign key acceptable?


texts: Service Broker - Add to queue but don't process till later

texts:
Our current implementation has a data coming into the system onto a Service broker queue a small amount of processing is performed in case more pertaining data comes in, then a SQL Agent job running every two minutes looks for any data that has reaches its 'time to process' and then adds it to another service broker queue.

Is there a way to instead to add something to a service broker queue but not receive it till 5 minutes time (or 20 minutes, or 1 day depending on the data)

There's the message_enqueue_time so you could technically delay everything in the queueRECEIVE * FROM queuetable where message_enqueue_time < dateadd(min,-30,getutcdate()), but there's things that need processing at different times from introduction to the queue.

So I'd like to say this entry doesn't want to be picked up till x time and then this doesn't want to be done till y time(which could technically be a week from now).

Is there any way to do this purely within Service Broker or any other way to make the service (next to) real time instead a pulse of data processing every 2 minutes (and not just run the Agent job more often)


texts: Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

texts:
I've a table [order] where a simple select shows cardinal difference between
Estimated Number of Rows and Actual Number of Rows and in a strange way.

Generally Estimated Number of Rows skews as compared to Actual by in this scenario its totally ironic. Moreover "Number Of Execution" is 1.

Query :

SELECT
ord.paymentmode AS [HowTheyPaid],
ord.id AS [OrderId],
ord.ISBackEndSystemMigrated,
ord.ISDuplicate,
ord.ISMerged
FROM

DedicatedDentalPlans_Stage.dbo.[order] ord
WHERE
ISBackEndSystemMigrated=0
AND ISDuplicate = 1

Table

USE [DedicatedDentalPlans_Stage]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Order_ISBackEnd]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF_Order_ISBackEnd]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Order__ISDuplica__382F5661]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF__Order__ISDuplica__382F5661]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Order__ISMerged__39237A9A]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF__Order__ISMerged__39237A9A]
END

GO

/****** Object:  Table [dbo].[Order]    Script Date: 03/09/2016 02:11:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 03/09/2016 02:11:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Order](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SubscriberId] [int] NOT NULL,
    [BillingAddressId] [int] NOT NULL,
    [ShippingAddressId] [int] NULL,
    [OrderStatusId] [int] NOT NULL,
    [ShippingStatusId] [int] NOT NULL,
    [PaymentStatusId] [int] NOT NULL,
    [PaymentMethodSystemName] [nvarchar](max) NULL,
    [OrderDiscount] [decimal](18, 4) NOT NULL,
    [OrderTotal] [decimal](18, 4) NOT NULL,
    [SystemUserId] [int] NULL,
    [UserType] [int] NULL,
    [PaymentMode] [varchar](20) NULL,
    [Deleted] [bit] NOT NULL,
    [AutomaticRenewal] [bit] NULL,
    [CreatedOnUtc] [datetime] NOT NULL,
    [ETLControlId] [int] NULL,
    [ETLGenerationId] [int] NULL,
    [ISBackEndSystemMigrated] [bit] NULL,
    [CheckNumber] [varchar](20) NULL,
    [ISDuplicate] [bit] NULL,
    [ISMerged] [bit] NULL,
 CONSTRAINT [PK__Order__3214EC0722AA2996] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_ISBackEnd]  DEFAULT ((0)) FOR [ISBackEndSystemMigrated]
GO

ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [ISDuplicate]
GO

ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [ISMerged]
GO

Solution Opted so Far with no result:
Updated Statistics related to same index and clustered index with FULL SCAN.


texts: Scheduling a task to run after a SQL SERVER 2012 executes a different set of scripts?

texts:
Im working on a project for a job I am interning for. I've had a few months of database design.  I am still learning all the techniques.

I am currently trying to schedule a console app to run every so often. But first, my DBA script(s) need to be processed by the DBA productions team. The scripts create some tables and performs a data dump.   After, the script(s) is ran, I would like the console app to run 10, 20, 30, 45 minutes after the scripts are executed. (Those are test intervals.) I am thinking that an event trigger is the way to go, but I am not sure?


texts: Pros and cons of row level security Microsoft SQL Server

texts:
What are the pros and cons of row level security?

We are porting a database from a desktop app to a web app and want to know the best way to make sure sensitive data is secured.

For our application there will be a limited number of users logging onto the system, e.g. trusted contractors rather then members of the public.

We could of course write our queries to check permissions on a case by case basis, or set up rows so that only contractors from that organization can see that row.

What criteria are used to decide what is best practice?


texts: Speed of Split Database vs ODBC with SQL Server vs ODBC with Azure Cloud

texts:
I would like to be able to host my data either in the cloud or in SQL Server, as having all my data in one Access file just doesn't seem the most secure to me (or am I wrong?).

My forms contain many subforms (7 to 30), and it takes only a few seconds to load them all with a split Access database. When my tables are linked to SQL, it takes 5 times as long to load the queries; and when they're linked to the Azure Cloud, any query that pulls from multiple tables takes an unbearable amount of time to load (on the order of minutes).

My question is: is this normal? Is keeping it as a split Access database really the only way to maintain good performance? Clearly the Azure Cloud is not an acceptable place to pull data from, but I was under the impression that SQL Server should be nearly as fast...

edit: Here are some details about my setup.

The query that is taking a while to process is qryTask (which retrieves about 2500 records). Its SQL code is below (even if I remove the ORDER BY line and calculated fields, it's still slow. So I think it has to do with the fact that there are multiple tables involved because another query that has only one table is fast).

SELECT tblTask.JobNum, tblTask.Sequence, tblTask.SuperintendentID, tblJob.Closed, tblTask.Approved, tblJob.Revenue, tblJob.Notes, tblJob.Certified, tblEstimator.SortID, tblDivision.SortID, tblTask.Complete, tblTask.Ordering, tblTask.NumOfLaborers, tblDivision.Division, tblSuperintendent.SortID, tblJob.EstimatorID, tblTask.DivisionID, tblJob.JobSiteName, tblJob.JobSiteStreet, tblJob.JobSiteCity, tblTask.ID, tblJob.Customer, tblTask.Item, tblTask.ItemDescription, tblTask.StartDate, tblTask.EndDate, tblTask.WeekendWork, tblEstimator.EstimatorNum & '-' & tblEstimator.FirstName & ' ' & tblEstimator.LastName & '\20' & Mid(tbltask.JobNum,3,2) & ' JOBS\' AS JobMidFilePath, 'TaskID:' & tblTask.ID & ' JobNum:' & tblTask.JobNum & ' DivisionID:' & tblTask.DivisionID & ' Item:' & tblTask.Item AS AuditInfo, tblSuperintendent.FirstName & ' ' & tblSuperintendent.LastName AS SuperintendentName
FROM tblSuperintendent RIGHT JOIN ((tblEstimator RIGHT JOIN tblJob ON tblEstimator.ID = tblJob.EstimatorID) RIGHT JOIN (tblDivision RIGHT JOIN tblTask ON tblDivision.ID = tblTask.DivisionID) ON tblJob.JobNum = tblTask.JobNum) ON tblSuperintendent.ID = tblTask.SuperintendentID
ORDER BY Switch(tblTask.JobNum='***FULL***',0,tblTask.JobNum<>'***FULL***',1), tblDivision.SortID, tblSuperintendent.SortID, Nz(tblTask.[Ordering],99999999), tblTask.JobNum, tblTask.Sequence;

Here is the diagram for the query:

And here is some sample data from the query:


texts: Can we use save transaction command only in nested transactions?

texts:
I am a newbie to SQL and I came across a situation where I have to use save transaction, but can any one please let me know if save transaction should only be used in nested transactions ? If not what is the difference between having a save point and not having a save point transaction in a Stored procedure where we don't use nested transactions ?

Thanks !!


texts: Insert where not already available for FK?

texts:
I'm looking for a simpler way to do this as I'm clearly thinking like a developer instead of a DBA.  How would a DBA tackle this?

I have a table with 3 column:  PK, Name, FK.

PK   Name   FK
1 someNameA 1
2 log     1
3 someNameB 2

I want to say if a foreign key of n does not have "log" for a name value then add it to the table.

I think I'm heading down a dark road as this seems rather vebose:

declare @ids table (ids varchar(8))
declare @log_ids table (ids varchar(8))
declare @nolog_ids table (ids varchar(8))

insert into @ids
    select fk
    from table
    where fk is not null

insert into @log_ids
    select fk
    from table
    where fk is not null
    and name like 'log%'

insert into @nolog_ids
    select distinct ids
    from @ids ids
    where ids.ids not in (select ids from @log_ids)

while ((select count(1) from @nolog_ids) > 0)
begin
    declare @currentid int
    set @currentid = (select top 1 ids from @nolog_ids)

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid
end


texts:
This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server.  For those that have used it, under what circumstances did you experience query regression?

It certainly seems like a potential performance benefit across the board, I'm considering enabling it globally in our non-production environment and letting it sit for a couple months to ferret out any issues.

Are the fixes in 4199 rolled into the optimizer by default in 2014 (or 2016)?  Although I understand the case for not introducing unexpected plan changes, it seems odd to keep all these fixes hidden between versions.

We're using 2008, 2008R2 and mostly 2012.


texts: Severity 20 Error upon disconnecting Dedicated Administrator Connection

texts:
Starting working with Dedicated Administrator Connections on SQL Server 2008 R2 servers. All is working fine (once port 1434 was opened), but am getting severity 20 errors when I disconnect the admin connection.

From within SSMS, I am opening a new query window then connecting via the DAC using connection string, "admin:". All is well. However, when I disconnect, my SQL Alert for Severity 20 errors fires, alerting me to it. The description of the alert reads:

  "Could not connect because the maximum number of ‘1’ dedicated
  administrator connections already exists. Before a new connection can
  be made, the existing dedicated administrator connection must be
  dropped, either by logging off or ending the process. [CLIENT:
  XX.XX.XX.XX]" The IP address is mine.

I'm confused as what could be attempting the connection at the same time I am disconnecting from the DAC. Or, is it something else. No connections to the same server from my SSMS via any query window or Object Explorer. Other users may be connecting to this SQL Server (its a dev box) but not on DAC (only opened port 1434 before testing).

Any thoughts?


texts: How should I store a large number of dynamically generated heterogeneous tables?

texts:
I'm a developer on a reporting application that runs complex user-defined queries, stores the results, and then sends the result to Excel generation, web-based tables, and graphs, and so on. Currently, we're storing the entire result set serialized as a clob in an existing table.

These result sets may be large (some >1GB) and expensive to generate (many minutes). Regenerating them on-the-fly is not an option. They also are heterogeneous -- each result set will likely have very different columns. The result sets are immutable -- they will never be modified, although they will eventually be deleted. Each result set will be read occasionally, but often in bursts (e.g. if someone views the data via our web interface, then we expect many more requests soon).

Currently, these thousands of result sets are taking up a lot of DB space. In addition, it is difficult to manipulate the values other than to select the entire result set or the first n records. We would like to be able to sort and filter the result set. It seems logical that if we have tabular data that we'd like to sort and filter, then storing it as a table is the obvious answer.

However, I'm not sure what the right way would be to do this in Oracle. The naive solution would be to dynamically run CREATE TABLE statements for every one, naming them from a GUID or something. This kind of vaguely feels like an unwise thing to do, maybe unless we also move this data to a separate database. I looked up global temporary tables, but we need them to persist across sessions. A co-worker suggested using external tables, but I'm not sure I understand the performance implications of that. Basically, I'm in over my head from an Oracle architecture standpoint, and I'd appreciate advice from more experienced developers and DBAs.

So: given these constraints, what approaches should I consider?


texts: Mysql same DB, same query, different users - different error messages

texts:
On my AWS RDS Mysql 8.0.16 server I have one DB (my-db) and 2 users: the master user (created automatically by RDS) and an app user created by me:
create user if not exists `appuser`@`%` identified by 's3cr3t';
grant all on `my-db`.* to `appuser`@`%`;

When I run the delete from Things where id = 123 query on behalf of the appuser, the error message I get is:

[23000][1217] Cannot delete or update a parent row: a foreign key constraint fails

(1217 and no constraint details)
When I run the same query as a master user, the error message is:

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (my-db.Sandwiches, CONSTRAINT SandwichesThingIdFK FOREIGN KEY (thingId) REFERENCES Things (id))

(1451 and there are constraint details)
How do I make appuser get 1451 and all the details?


texts: How can I clone a MongoDB database in a script?

texts:
I'm a newbie to mongo, and I've written the following script to clone a database in Mongo:

echo 'Enter db to clone: '
read dbToCopy
echo 'Enter name for cloned db'
read newDbName
dumpUri="mongodb://$un:$pw@$ip:$port/$dbToCopy?authSource=test"
restoreUri="mongodb://$un:$pw@$ip:$port/$newDbName?authSource=test"
mongodump --uri=$dumpUri --archive|mongorestore --uri="$restoreUri" --archive

I'm getting the below output & error, and can't find any information online. Any ideas, or leads that I could follow?

2020-02-20T15:28:01.592-0800    writing *****.Stacks to archive on stdout
2020-02-20T15:28:01.594-0800    writing *****.Queues to archive on stdout
2020-02-20T15:28:01.596-0800    writing *****.LFRs to archive on stdout
2020-02-20T15:28:01.624-0800    Failed: archive writer: error writing data for collection ***** to disk: error reading collection: Failed to parse: { find: "Nodes", skip: 0, snapshot: true, $readPreference: { mode: "secondaryPreferred" }, $db: ***** }. Unrecognized field 'snapshot'. / Mux ending but selectCases still open 4
2020-02-20T15:28:01.641-0800    preparing collections to restore from
2020-02-20T15:28:01.695-0800    reading metadata for *****.Nodes from archive on stdin
2020-02-20T15:28:01.696-0800    restoring *****.Nodes from archive on stdin
2020-02-20T15:28:01.717-0800    no indexes to restore
2020-02-20T15:28:01.718-0800    finished restoring *****.Nodes (0 documents)
2020-02-20T15:28:01.719-0800    done
2020-02-20T15:28:01.719-0800    Failed: error demultiplexing archive; archive finished before all collections were seen (*****.LFRs)```

I tried using --nsfrom/--nsto, but I still get the same error.


texts: How do I add minutes to a time data type?

texts:
I have a stored procedure which inserts two records into a table, the difference between the records is that the time column of the second record is @MinToAdd after the first:

CREATE PROCEDURE CreateEntry
    /*Other columns*/
    @StartTime time(2),
    @EndTime time(2),
    @MinutesToAdd smallint
    AS
BEGIN
    SET NOCOUNT ON;

    SET @MinutesToAdd = @MinutesToAdd % 1440;   --Prevent overflow if needed?
    IF (@MinutesToAdd > 0)
    BEGIN
    INSERT INTO ClientNotification (/*Other columns*/ startTime, endTime)
        OUTPUT inserted.id
        VALUES
               (/*Other columns*/ @StartTime, @EndTime),
               (/*Other columns*/ @StartTime + @MinutesToAdd, @EndTime + @MinutesToAdd);
    END
    ELSE
    BEGIN
        /*Whatever ELSE does.*/
    END
END

What is the correct way to add @MinutesToAdd minutes to @StartTime and @EndTime?
Please note I am using the time data type.

Update:
A correct answer should contain the following information:

How to add minutes to to a time data type.
That the proposed solution does not result in a loss of precision.
Issues or concerns to be aware of in the event that the minutes would be to too large to fit in a time variable, or risk of rolling the time variable over.  If there are no issues then please state so.


texts: Logical replication - Manually triggering update of data on destination tables (after schema change)

texts:
I'm testing out Logical replication on Postgresql (10.4 at the moment) with a quite specific use case.

I have some central DB (publisher) from where I need to replicate few tables to distributed DB's (subscribers) (across different servers).

This is the scenario:

Table in Master DB changes (gets new column). Lets say that this new column is of type "timestamp without timezone", not null, with default value of now().
Because of 1) replication stops working, which is fine. (logical replication target relation <relation> is missing some replicated columns)
After some time, distributed DBs also get the same change (new column).
At this point, replication starts working again and new rows are replicated, including the data from new column (for new rows).
However, for existing rows, the values (timestamps) from new column in master DB table and replicated DB table are different (by time between kicking off the migration on master table and replica tables) and do not get updated up until row changes in Master DB/source table.

My question: is there a way to "force" update new column data replication without truncating table data, updating all rows in source table or similar very time&resource consuming operations?

Also, performance related note - there will be 1-10 million rows in one of tables that is being replicated.

I'm open to all suggestions&ideas.

Thanks.


texts: Postgresql table size growth after adding column

texts:
I have added boolean type column to table with roughly 100 million rows.
Afterwards I have manually executed UPDATE statements to set the new column value to true/false depending on other column values.
UPDATES were done overnight (took a few hours).

Then I set the column to NOT NULL.

After all these steps table size increased almost twice. ~40GB -> 68GB.

I have used following query for updating in chunks (To not lock the table completely):

WITH cte AS (
    SELECT id from results_storage  WHERE condition < 100   AND newboolean IS NULL  limit 10000
    FOR    UPDATE SKIP LOCKED )

UPDATE results_storage r SET       newboolean = FALSE FROM   cte WHERE  r.id = cte.id RETURNING r.id;

What could be the cause?
Is there any way to reduce the space taken?
I have expected single boolean column to take very little extra space.
(+~100MB for each 100M rows as boolean should take 1 byte storage).


texts: SQL problem with checking next and previous rows

texts:
I have a list and the returned table looks like this. I took the preview of only one car but there are many more.

What I need to do now is check that the current KM value is larger then the previous and smaller then the next. If this is not the case I need to make a field called Trustworthy and should fill it with either 1 or 0 (true/ false).

The result that I have so far is this:

validKMstand and validkmstand2 are how I calculate it. It did not work in one list so that is why I separated it.

In both of my tries my code does not work.

15756 should be 0 in validkmstand2 as it is larger than the next value.
15757 should be 0 in validKMstand as it is smaller than the previous value

Here is the code that I have so far.

 FullList as (
SELECT
    *
FROM
eMK_Mileage as Mileage
)

, ValidChecked1 as (
SELECT
        UL1.*,
        CASE WHEN EXISTS(
            SELECT TOP(1)UL2.*
            FROM FullList AS UL2
            WHERE
                UL2.FK_CarID = UL1.FK_CarID AND
                UL1.KM_Date > UL2.KM_Date AND
                UL1.KM > UL2.KM
            ORDER BY UL2.KM_Date DESC
        )
        THEN 1
        ELSE 0
        END AS validkmstand
FROM FullList as UL1
)

, ValidChecked2 as (
SELECT
        List1.*,
        (CASE   WHEN List1.KM > ulprev.KM
                THEN 1
                ELSE 0
                END
        ) AS validkmstand2
FROM ValidChecked1 as List1 outer apply
    (SELECT TOP(1)UL3.*
            FROM ValidChecked1 AS UL3
            WHERE
                UL3.FK_CarID = List1.FK_CarID AND
                UL3.KM_Date <= List1.KM_Date AND
                List1.KM > UL3.KM
            ORDER BY UL3.KM_Date DESC) ulprev
)

SELECT * FROM ValidChecked2 order by FK_CarID, KM_Date


texts: How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL?

texts:
I would like to say that i had configured "Automatic DB Backup" as well as "Automatic DB Cleanup Task" through "Maintenance Plan" in MS SQL Server 2012 on my Production Database. And that is working properly. I had set up my "Maintenance Clean up" task "Older than 4 Weeks".
Using the following T-SQL Statement:-

use msdb
select * from
dbo.backupmediafamily;

I am able to view the backup history of my DB. That is working properly through maintenance plan schedule.
I setup Automatic Backup of 3 Databases through the "Maintenance Plan", But I had configured Maintenance Cleanup Task to delete files older than 4 weeks. During the last month there are 90 (Backup) files . I configured this plan on 26-03-2015, so now my Maintenance Cleanup Task is working now and I also delete (backup files, .bak) from the specified location.

Now I am able to check out deleted backup file from specified location with Maintenance Plan. But after few days or few months I may be not able to collect exact deleted backup file records as well as not recognize exact days delete backup file records.
So, I want to know exact deleted (.bak) records from through T-SQL.


texts: Is it free to download an Oracle Enterprise Edition image from Docker?

texts:
I would like to learn more about Oracle (I am more of an SQL Server developer).  I am thinking about downloading the Docker image: https://hub.docker.com/_/oracle-database-enterprise-edition?tab=reviews
I am a developer and this is strictly for learning purposes.  I have read lots of other similar questions on here like this one: Is Oracle free to download? (dated 2013), however they are many years old.  One of the answers in the question I have posted a link to says: "strictly self-educational purposes".
The reason I am concerned is because before downloading the image you have to supply a company name i.e. it is a mandatory field.  There is no company because this is strictly for learning purposes and the reviewed comments on the Docker webpage are from "verified purchasers". I was thinking about downloading the express edition but some of the features I want to learn about are not available with the express edition.
Can I use the EE image for learning purposes?


texts: Does UDP Port 1434 need to be open for named instances with static TCP?

texts:
I have built a named instance which is running on a static port, 50101.

The network guys have opened the port on the firewall to allow incoming connections. I have asked them open UDP port 1434 too, as I believe the SQL Browser Service runs on this port and needs to pass the correct port for the named instance back to the requesting connection. Even if the named instance is running on a static TCP port.

Is that correct?

SQL Browser / UDP 1434 is not just for dynamic ports, right?

It needs to be open if the port is not explicit in the connection string?

Really need to end this argument!

MP


texts:
I'm trying to import a large (30GB) SQL file (generated using mysqldump) into an RDS instance MySQL 5.7 database, from an EC2 instance on the same VPC.
It's importing most without a problem, but missing around 5 tables from the end of the dump.
Looking around for related logs in the AWS console I can't really find much of use, all I'm seeing is this:
2021-02-17T12:16:20.168722Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10360ms. The settings might not be optimal. (flushed=599 and evicted=179, during the time.)
2021-02-17T12:16:36.461195Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5988ms. The settings might not be optimal. (flushed=490 and evicted=124, during the time.)
2021-02-17T12:16:46.848239Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6766ms. The settings might not be optimal. (flushed=447 and evicted=0, during the time.)
2021-02-17T12:17:16.371538Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10380ms. The settings might not be optimal. (flushed=370 and evicted=240, during the time.)
2021-02-17T12:19:22.153786Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 27942ms. The settings might not be optimal. (flushed=299 and evicted=637, during the time.)
2021-02-17T12:21:19.368279Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 16509ms. The settings might not be optimal. (flushed=361 and evicted=136, during the time.)
2021-02-17T12:23:01.420623Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5097ms. The settings might not be optimal. (flushed=349 and evicted=125, during the time.)
2021-02-17T12:23:16.836488Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8285ms. The settings might not be optimal. (flushed=307 and evicted=121, during the time.)
2021-02-17T12:23:52.965471Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 19104ms. The settings might not be optimal. (flushed=299 and evicted=607, during the time.)
----------------------- END OF LOG ----------------------

I'm not really sure where to start looking without any logs. Only thing I can think of is maybe lack of memory, but looking at the stats it still had "some" memory left on the RDS instance while it was importing.
When I check the file the CREATE TABLE statements are definitely there for the missing tables.
Can anyone give any tips on how I can figure out what's going wrong?


texts: HTML entity decoding with oracle DBMS_XMLGEN.convert

texts:
Here, I am trying to convert the value, where I don't understand I am going wrong. Your help is highly appreciated!

Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;

to

Here I am, THis is me there's nowhere else on earth I rather be Here I am,

For this I used the following query which didn't work, Can you please tell me where I am wrong?

select DBMS_XMLGEN.convert(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&\1'), 1 ) as message from dual;


texts: Restore user in DataBase that in master have mapped the child database but in child database doesn't have the user created

texts:
I don't know why te following database arrive to this point but this is the situation

In master security you can see the defcon\axadmin user with DAXTest database mapped

But if you go to the database secury the user AXAdmin doesn't exist.

I can't edit any information about mapping of AXadmin user because throw the following error:

Also I can't delete this user in master because when I create again have the same mapping.

And if you check with te following sql:

use master
go
select * from syslogins where name='DEFCON\axadmin'
go

use daxtest
go
select * from sysusers where name='DEFCON\axadmin'
go

You can see the user in master database but doesn't exist in DAXTest

I try to create in DAXTest

CREATE USER [defcon\axadmin] FOR LOGIN [defcon\axadmin];

The user have another session start name

But if I try to edit

ALTER USER [DEFCON\axadmin] WITH LOGIN=[DEFCON\axadmin]

You can't edit the user because doesn't exist

Error:


texts: Pgadmin4 : unable to login.Getting error failed to create ssh tunnel

texts:
I am using CentOs7, On the command line I login to the unix server via ssh (sshpass -p $PASS ssh linux_user@linux_box_ip ;;) and then give the below command after logging to the linux box.
$ psql -h <db_host_ip> -p <db_port> -d <db_name> -U <db_user>
<password>

How to achieve the same in the pgadmin4. I am trying the below steps

Can you please advise what am I doing wrong here, and how to fix the issue. Thanks for your help.


texts:
I'm trying to figure out how to backup the users, roles, and what permissions they have on a SQL Server instance.  I need to be able to restore the users, roles, and permissions without changing anything else (so backing up the entire database is out).  I'm not trying to figure out what permissions are required to be able to make a backup, or restore one.
The reason I'm looking into this:
I need my QA environment to retain its current users, roles, and permissions.  I also need to refresh the QA instance (data and objects) based on my prod instance.  Our QA team has some elevated permissions that we don't want them to lose after the QA environment is refreshed.  The elevated permissions are not documented, and the person who granted them is no longer with the organization.  Nobody is confident that we know what they all are.


texts: What happens with a synonym if the table which its based on gets deleted?

texts:
we are working on a project to implement a large ERP system. We found out that one of the tables has a synonym to another database. Both databases are in the same instance. Our project leader has decided that one table has to be deleted. This table turns out to be the source table of the synonym.

In my opinion does this also effect the synonym. Am I right?
I think, because the source table is deleted, the synonym cannot retrieve any data anymore and will have empty columns.


texts:
My database currently has a primary Key/Clustered index on the ID column for each table.  However, the application that connects to the database is always looking at views defined as:

SELECT * FROM Table WHERE DeletedDate IS NULL

Would it be best to add the DeletedDate to the clustered index or is it better practice to have a nonclustered index with the DeletedDate as an include?


texts: Add [Is Primary Key Column] to this query

texts:
I made this query, to list tables, its columns, data type and etc:

select t.name as 'Table',
       c.name 'Column',
       ty.name 'Data_Type',
       c.max_length as 'Max_Lenght',
       c.is_nullable as 'Null',
       c.is_identity as 'Identity'
from sys.tables t
    join sys.columns c on t.object_id=c.object_id
    join sys.types ty on c.system_type_id=ty.system_type_id
        order by 1

I have 2 questions:

Ques.1:

How can I add a column 'Is primary key?' to it? I could not find a table that help me ( I took a look at sys.indexes, sys.foreign_keys, INFORMATION_SCHEMA.table_constraints ).

Ques.2:

In sys.columns, int values have the max_lenght as 4, and some other fields too. What is this 4? 4kb per data? datetime and decimal too.

Thanks.


texts:
I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot.  I'm having trouble locating where the snapshot files are located from within a query.

Essentially we have several environments and they are not consistent on where they place these files so I need my code to be smart enough to put these files back where they were.

Just to clarify: When I say the snapshot files I mean the .ss files.


texts: Why is the default width for a MySQL signed MEDIUMINT 9 instead of 8?

texts:
If I create a signed MEDIUMINT column without a width specifier, MySQL automatically adds a width of 9. However, the range of a signed MEDIUMINT is -8388608 to 8388607, which only needs 8 display characters max including the sign.

I tested all the other *INT types without a width and the default width MySQL assigned matched the worst case maximum number of characters needed to output the datatype. Why does a signed MEDIUMINT default to +1 character?

https://dev.mysql.com/doc/refman/5.7/en/integer-types.html


texts: Horizontally scaling SQL Server, distributing the database with sharding

texts:
I wanted to know if there is any way to distribute a SQL Server (I'm using 2012 version) database accross multiple nodes. I'm trying to compare READ queries performance between SQL Server and MongoDB. The distribution is all set with MongoDB with different shards using virtual machines and I wanted to make a similar setting for SQL Server, but I'm facing lots of trouble.

Is there any material on how to do that? Is that as tough process as it seems?