texts: Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource

texts:
I'm trying to update a cluster to SQL Server 2008 R2 SP3.  I've installed SP3 on the passive node but when I failover the cluster it errors when running through it's upgrade scripts.  The messages & error messages from the SQL Logs when it's back on it's happy node are as follows:

Msg - Exporting ##MS_AgentSigningCertificate## to N:\SQL\DATA\MPData7\MS_AgentSigningCertificate.cer
ErrMsg - Could not export certificate, trying again with random name. If this is a mkmastr build then this is an error.
Exporting ##MS_AgentSigningCertificate## to N:\SQL\DATA\MPData7\MS_AgentSigningCertificate2DC9C8FF-1B52-4B40-8F59-6B7714201D43.cer
Error: 15240, Severity: 16, State: 1.
Cannot write into file 'N:\SQL\DATA\MPData7\MS_AgentSigningCertificate2DC9C8FF-1B52-4B40-8F59-6B7714201D43.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15240, state 1, severity 16.
This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.
Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

There are other error messages related to not being able to recover the master database but I'm not worried about them.
On reviewing the error messages I can see that the path the certificate is trying to be exported to is not the same as the default data path for the instance.
The default path for the instance is N:\SQL\Data\MPData5 which I can see from the server properties and the registry. The SQL Server service account has full read/write access to that path.


texts: When do I create a separate entity when drawing conceptual models?

texts:
I have these 3 scenarios:

Rooms located in a house are assigned a room number within that house - thus, for example, each house has a room number 1. Each room has a phone number etc...

My guess is we do not need to have a separate entity for rooms since each house must have one room then if we delete the room we should delete the house anyway?

A tour guide(identified by tour guide id) may have one or more specialisations but not all tour guides have them.

Specialisations should be a separate class?

If an activity is performed by a Tourism Provider do not record the details of the Tourism Provider who completed the activity.

Should Tourism Provider even be a separate entity because we are not recording the details?


texts: How to delete the primary index of a table in Progress, release 11.6?

texts:
I have made a table, containing an integer field, this field being used in the primary index of that table. Now I would like to replace this integer field by a character one. Therefore I need to remove all indexes of that table, including the primary one. Therefore I'm using the data dictionary, but I get following error message:
You cannot delete the primary index of a table.

How can I proceed?
Thanks


texts: Migrating PostgreSQL DB from AWS to Alibaba Cloud

texts:
I'm migrating PostgreSQL RDS instance from AWS located in Frankfurt to the RDS instance in Alibaba Cloud also located in Frankfurt.
I use Data Transmission tool from Alibaba Cloud. It connects both DB fine, there are no errors on the connections.
The only problem is with versioning:

I've tried downgrading the source DB, but it seems to be impossible. Does anybody have ideas how it could be done?


texts: How to backup and restore a database to a different location and name via command line only

texts:
I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only.
The name of the database on the new location should not be identical to the original name.

I've tried the following commands, but failed when I tried to restore the database:

Backup:

sqlcmd -E -S MyUser\SQLEXPRESS -Q "BACKUP DATABASE original_db TO DISK='C:\tmp\backups\base_db.bak'"

Restore:

sqlcmd -E -S MyUser\SQLEXPRESS -Q "RESTORE DATABASE new_test_db FROM DISK='C:\tmp\backups\base_db.bak'
with move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.mdf',
    move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.ldf'"

When I try to restore the database I get the following error:
"The backup set holds a backup of a database other than the existing 'new_test_db' database."

(When my database didn't exist I got the following error on restore - "Logical file 'original_db' is not part of database 'new_test_db'. Use RESTORE FILELISTONLY to list the logical file names.")

Any ideas how to do it properly?

Thanks for the help.


texts: Add row after every rows of a table from select statement

texts:
There are two tables. I want to add every record of one table under every record of the other table. The two tables and the result table are shown below.

TABLE_1    TABLE_2     TABLE_3
TYPE        NAME        NAME      TYPE  VALUE
T_1         PARTY_1     PARTY_1   T_1    100
T_2         PARTY_2     PARTY_1   T_2    200
                        PARTY_2   T_1    120
RESULT_TABLE
RESULT     VALUE
PARTY_1     -
  T_1       100
  T_2       200
PARTY_2     -
  T_1       120
  T_2        0

How to do it? I am using SQLite database.

I have done so far ...

string qry = "SELECT " +
            "[TABLE_2].[NAME] AS [RESULT] " +
            "FROM [TABLE_2] " +
            "UNION " +
            "SELECT " +
            "'  - ' || [TABLE_1].[TYPE] " +
            "FROM [TABLE_1]";

But it shows as per below...

RESULT
PARTY_1
PARTY_2
- T_1
- T_2


texts: What is the effect on primary keys when moving a CLUSTERED index to a new filegroup?

texts:
I'm moving a CLUSTERED index from one filegroup to another, using the with drop_existing=on functionality.

Do I need to be concerned about primary keys?  In other words, is there some special handling I need to do to retain the existing primary/unique key?

I have tables on the primary filegroup that I need to move to a new filegroup. There are some tables with and some without indexes, some with and some without clustered indexes, and amongst those, some with and some without Primary Keys.

I'm wondering if there is any special handling I should consider for the tables with Primary Keys during the CREATE INDEX statements?

Does this even matter in this scenario?


texts: Save Access SQL code to text file or Python variable?

texts:
This may not have a good answer but maybe?

So I have a number of Access databases that query a SQL Server through ODBC to generate reports. The SQL Server backend is being changed out and I need to translate Access Queries to SQL Server queries of the new backend. I have mappings of all the current SQL Server backend to new backend. Some of the fields in the SQL Server are encrypted and only accessible through Access and as far as I know the person that has the password to decrypt the SQL Server fields is MIA/presumed KIA.

The document I have that lists out all the reports that need to be recreated has the Access field names not the SQL Server field names. Is there a good way to programatically get the SQL Code from Access and save it off as a text file or something I can parse with Python? My thought is if I can do this I could parse it to get the original field names not their aliases. Then I can feed that into my program for outputting the new SQL Server table and fieldnames.


texts: Has the postgres sorting order changed between versions 11.5 and 13

texts:
Doing a simple query to a postgres v 11.5 database yields the following results
sql select * from table order by name.;
[2021-03-09 13:27:49] testing.INFO: Hall, Hunter and Allen;;;;Hall PLC;;;;Miller, Lloyd and

However doing the same query on the newer version 13 produces
Hall PLC;;;;;;Hall, Hunter and Allen;;;;;;Miller, Lloyd and Bailey

I can't explain this other than what result i'm given and I can't find any more information on this matter.
Can anyone point out the change thats effected the output?


texts: Automate Microsoft SQL Script and send the Result to email

texts:
My organization asked me to setup monitoring for below query and the query  needs to run every hour(Automated) , if the below query status results other than STARTED or COMPLETED send them as failure and I'm new to SQL please can some help me with this to tune the query and with email requirement?
SQL query
 SELECT
    i.JOB_NAME, e.STATUS
FROM
    BATCH_JOB_EXECUTION e
        JOIN
    BATCH_JOB_INSTANCE i ON i.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID
WHERE
    i.JOB_NAME IN ('STORE_DOCS')


texts: How can I optimize this query over this logging table?

texts:
I am trying to optimize a table layout for a table that logs events.

The log table contains three relevant columns: Timestamp, ItemId, LocationId
Each row means that at a given time, a certain item has been seen at a certain location.

2017-01-01 10:00    Item A has been seen at location 1
2017-01-01 10:01    Item A has been seen at location 1
2017-01-01 11:00    Item B has been seen at location 1
2017-01-01 11:01    Item B has been seen at location 2
2017-01-01 11:02    Item A has been seen at location 2
2017-01-01 11:03    Item B has been seen at location 1

There are about 100 different locations, 20.000 new items per day, a million events per day, and 14 days of logs.

Now I need to run queries on this data, such as:

Which items were at location '1' at time '2017-01-01 11:00'
(= which items have been seen at location 1 before time '2017-01-01 11:00', and were not seen elsewhere after being seen at 1 but before '2017-01-01 11:00'

To get this data I can execute

SELECT DISTINCT  ItemId
FROM events e1
WHERE LocationId = 1
  AND e1.TimeStamp < '2017-01-01 11:00'
  AND NOT EXISTS (SELECT 1 FROM events e2
                  WHERE e2.LocationId <> e1.LocationId
                    AND e2.ItemId = e1.ItemId
                    AND e2.TimeStamp >= e1.TimeStamp
                    AND e2.TimeStamp <'2017-01-01 11:00')

Currently, this query takes about 15 seconds, when there is zero load on the database. The goal is to execute this query in less that 100ms, with heavy load. I don't think this is possible with the current design.

I have an index on item and location, and a clustered index on timestamp

Is there a table layout that would allow me to perform this query more efficiently?

Or is there a query that would work with the existing table?


texts: rows are rendered not in serial order as appeared in database

texts:
an wondering why my data though in serial order by id when i look at the records in the database but when rendered, they are not in order of id?

for example:
database

id animal
1  dog
2  lion
3  tiger

html View on the browser

id  animal
2   lion
1   dog
3   tiger


texts: Best way to organize compound indexes on partitioned table (date by range)?

texts:
I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date column.

Since MySQL creates an automatic index on the primary key (I'm assuming this), does this mean that since my date column is not on the left part of the index, then would it be beneficial to create a single index on just the date column?

95% of my queries use the date column under WHERE
5% of my queries use the id under the WHERE clause
none of my queries use both columns under the WHERE clause

I used to have just a single index on the date column, but since I'm using partitions I'm forced to add the date column to the primary key. Should I create a single index on the date column? I'm asking this because my tables are big (30GB) and I don't want to add unnecessary overhead.


texts: Force index using the unused index columns having effect as order by for the result set?

texts:
if I have a index for (col1, col2, col3, col4), my query put col1 and col2 in the query where clause and force MySQL to use the above index. Would my query result be sorted by col3 and col4 automatically?

Table is something like this:

CREATE TABLE IF NOT EXISTS `jobs` (
    `job_id` varchar(36) NOT NULL,
    `job_status` varchar(30) NOT NULL,
    `created_at` datetime NOT NULL,
    `lease_date` datetime,
    `priority` int NOT NULL,
    PRIMARY KEY(`job_id`),
    INDEX `job_status_priority_lease_date` (`job_status`, `priority`, `lease_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The full query I want to do is the following:

select * from jobs use index (job_status_priority_lease_date)
where status="IN_PROGRESS"
and priority=0
limit 100

Would my query result be sorted by the lease_date automatically? Because of some limitation, I cannot use the order by statement.


texts: How can we trim bad data and output clean trimmed data using DQS or MDS?

texts:
Until now, we have used stored procedures for data massaging and cleansing but we have now decided to move to using Data Quality Services (DQS) and Master Data Management (MDS) in our data warehouse projects.

There is one very common function which we use in our stored procedures - it trims and replaces the , character with white space e.g.:

UPDATE [Table] SET Column=(ltrim(rtrim(replace(Column,',',''))))

I have spent hours to figure out a way to achieve this using DQS and MDM but couldn't find any way to achieve the same goal.

I would really appreciate if you guys can help me out with this.
In fact, thank you already for your precious time.


texts: Update statistics for all databases all tables in a server

texts:
Hi I have been working on a script to update statistics of all tables on all my DBs. The idea is to parametrise it later on, but just as a quick fix, and not wanting to implement Ola's scripts today, follow the script below.

I have tested it on a few servers, but before I schedule it to run on a live server Sunday morning, would like to get some ideas and share it with you.

SET NOCOUNT ON

DECLARE @DBS TABLE (I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    DBNAME SYSNAME NOT NULL )

DECLARE @I INT
        ,@Z INT
        ,@SQL VARCHAR(1008)
        ,@j INT
        ,@Y INT
        ,@MYDB SYSNAME

INSERT INTO @DBS
SELECT s.name
FROM sys.databases s
INNER JOIN SYS.master_files F ON S.DATABASE_ID = F.database_id  AND F.data_space_id = 1
WHERE S.STATE = 0 -- online
  AND S.database_id > 3 -- exclude master, tempdb and model -- I left msdb
  AND S.is_read_only = 0 -- read/write
  AND S.user_access = 0 -- multi_user
  AND S.NAME NOT IN ('TableBackups',
                     'Troubleshooting')
ORDER BY F.SIZE DESC

SELECT @Z = @@ROWCOUNT
SELECT @I = 1

WHILE @I <= @z BEGIN

    BEGIN TRY DROP TABLE #T END TRY BEGIN CATCH END CATCH
    CREATE TABLE #T( I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, MYSQL VARCHAR(1008) NOT NULL )

    SELECT @MYDB = QUOTENAME (S.DBNAME )
           ,@SQL = 'USE ' + QUOTENAME (S.DBNAME ) + ';' + CHAR(13) + '
                    ' + CHAR(13) +
                   'INSERT INTO #T(MYSQL)  SELECT ''UPDATE STATISTICS '' + QUOTENAME(NAME) FROM SYS.TABLES WHERE TYPE = ''U'' ' + CHAR(13)

      FROM @DBS s
        WHERE s.I = @I

    PRINT CAST (@SQL AS NTEXT)
       BEGIN TRY

            EXEC (@SQL)
            SELECT @Y = @@ROWCOUNT

       END TRY
       BEGIN CATCH
           SELECT @Y = 0
       END CATCH

    SELECT @J = 1

    WHILE @J <= @y BEGIN

       SELECT @SQL = 'USE ' + @MYDB + ';' + CHAR(13) + MYSQL
       FROM #T
       WHERE I= @J

       BEGIN TRY

            EXEC (@SQL)

       END TRY
       BEGIN CATCH
       END CATCH

       PRINT CAST (@SQL AS NTEXT)

       SELECT @J = @J + 1

    END

    SELECT @I = @I +1

END /*WHILE*/


texts: As part of the testing to check for authorized users having access to applications and libraries on AS 400 systems, I need help to figure out a list of users having access to applications hosted on the AS 400 systems, need to understand is there way to list down users grouped to access specific libraries for certain applications and all users given access to and revoked access and also the privileges assigned to each users, over a period of time for the testing.


texts: SQL query runs fast on local computers but slow on production server

texts:
I am running a fairly basic query on my local development machine which runs almost instantly. However on the server its taking around 3-4 seconds to return results. I have ran Client Statistics on both machines and get these results.

Local machine:

Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements    0
  Rows affected by INSERT, DELETE, or UPDATE statements 0
  Number of SELECT statements   3
  Rows returned by SELECT statements    17
  Number of transactions    0
Network Statistics
  Number of server roundtrips   2
  TDS packets sent from client  2
  TDS packets received from server  9
  Bytes sent from client    342
  Bytes received from server    30141
Time Statistics
  Client processing time    16
  Total execution time  508
  Wait time on server replies   492

Server machine:

Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements    0
  Rows affected by INSERT, DELETE, or UPDATE statements 0
  Number of SELECT statements   3
  Rows returned by SELECT statements    17
  Number of transactions    0
Network Statistics
  Number of server roundtrips   2
  TDS packets sent from client  2
  TDS packets received from server  9
  Bytes sent from client    342
  Bytes received from server    31560
Time Statistics
  Client processing time    16
  Total execution time  3638
  Wait time on server replies   3622

What could be the reason for it taking so much longer on the server? Its causing webpages to load slowly and hence users and search engines are penalising me.


texts: GROUP BY uninterrupted sequence of logs for same location

texts:
I'm having a hard time describing my problem or finding it via Google.

I want to be able to group rows based on location and employee, but the group should be split if a row with the same location occurs.

Basically a way to determine shifts. If another employee logs a row at the same location then the last created_at should be the end date of the previous employee.

Starting from this (employee_logs):

location | employee | created_at
---------+----------+--------------------
A        | Bart     | 2015-09-01 09:00:00
B        | Homer    | 2015-09-01 09:05:00
A        | Bart     | 2015-09-01 09:30:00
A        | Bart     | 2015-09-01 10:00:00
A        | Homer    | 2015-09-01 10:05:00
A        | Homer    | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00
A        | Bart     | 2015-09-01 14:00:00

The desired result is (shifts):

location | employee | start               | end
---------+----------+---------------------+---------------------
A        | Bart     | 2015-09-01 09:00:00 | 2015-09-01 10:00:00
B        | Homer    | 2015-09-01 09:05:00 | 2015-09-01 09:05:00
A        | Homer    | 2015-09-01 10:05:00 | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00 | 2015-09-01 14:00:00

The hard part is that the sequence should be interrupted when another person creates a log at the same location. I can't seem to solve this with the usual techniques.


texts: Keep one row per ID while keeping previous order (from ORDER BY)

texts:
I'm actually working on a big query and stuck at the very end.
I'm working on MySQL 5.7.

ID // SubID // Criteria 1 // Criteria 2 // ... // Critera n
1 //   2   //    x        //    y      //  ... //   z
1 //   3   //    x        //    y      //  ... //   z
1 //   1   //    x        //    y      //  ... //   z
2 //   1   //    x        //    y      //  ... //   z
3 //   1   //    x        //    y      //  ... //   z

I would need for each ID the best (ID, SubID) pair, based on the existing order which is based on sorting on criteria 1 to n.

I had a look on several solutions but:

GROUP BY picks randomly any (ID, SubID) pair and doesn't take into account the existing order
GROUP BY + ORDER BY is not sufficient as I need to sort on complex criteria
I can't recover the subID info if I use DISTINCT

I have the feeling that the solution is fairly simple but I'm really stuck and I can't find similar problems/solutions on the Internet (maybe I just need some sleep ;) )

Just to make it clear: I need to keep 1 row per ID but in the same order as before, and to return at least ID // SubID (but it would be perfect if it could return all the criteria as well).

Thank you in advance for your help guys!

EDIT

Ok so to add a little bit of context here, my criteria are either boolean or float values that I computed in a subquery.

It looks like:

SELECT * FROM
(SELECT *,
CASE(formula) THEN 1 ELSE 0 as criteria_1,
CASE(formula) THEN 1 ELSE 0 as criteria_2,
ABS(formula), as criteria_3,
SQRT(formula) as criteria_4
FROM subquery
WHERE criteria_1 = 0
ORDER BY criteria_2 , criteria_3 DESC, criteria_4, value_z)
"Remove (ID,SubID) duplicates while keeping the previous order"

I want to replace the last sentence (the one between quotes) by something, but GROUP BY ID, SubID just randomly picks any (ID, SubID) value.


texts: How to remove earliest/latest row by two columns (userId, StatusCheck)?

texts:
I have crosschex database that is used to store logged in/out data. Sometimes it happens that user clock in/out few times. I would like to remove that duplicates leaving the earliest when log in and latest when log out.
Here is what I have:

USE crosschex;
SELECT UI.Name, INOUT.UserId, INOUT.CheckTime, S.StatusText
FROM dbo.Checkinout AS INOUT
INNER JOIN dbo.Userinfo AS UI ON
INOUT.Userid = UI.Userid
INNER JOIN dbo.Status AS S ON INOUT.CheckType = S.Statusid
WHERE
    UI.Groupid = 1 AND INOUT.CheckTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-3, 0) AND INOUT.CheckTime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-3, 1)

Here is the result:

"Zeab Simon"    "6" "2018-05-04 07:58:06.000"   "In"
"Zeab Simon"    "6" "2018-05-04 07:58:15.000"   "In"
"Zeab Simon"    "6" "2018-05-04 07:58:38.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:45.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:46.000"   "In"
"Zeab Simon"    "6" "2018-05-04 16:34:46.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:48.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:49.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:50.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:51.000"   "Out"
"Patty" "5" "2018-05-04 20:31:36.000"   "Out"

What I would like is to remove the earliest and lattest for each userId and StatusText In/Oou to get that:

"Zeab Simon"    "6" "2018-05-04 07:58:06.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:45.000"   "In"
"Zeab Simon"    "6" "2018-05-04 16:34:51.000"   "Out"
"Patty" "5" "2018-05-04 20:31:36.000"   "Out"

How can I do it?


texts: How to migrate subset of data from production to develop by scrambling sensitive data?

texts:
I like to create a tool which scrambles the production sensitive data and added it into another dev DB. I've built a couple of scripts that make random social security numbers, shift birth dates, scramble emails, etc. But I faced a problem while we have a reference for that column.

For example:- if we scramble tables tableA and TableB,
TableB have a forieng key from tableA(ssn)

 TableA
    ssn firstName                  lastName
    .......................................
    111   arun                        prasanth
    222   ann                         antony
    333   sruthy                      abc
    666   new                         abc

TableB(ssn Foreign key reference)
id  ssn   Place
................
1   111   kerala
2   222   usa
3   333   ekm
5   666   chennai

Desired output should be

 TableA
    ssn firstName                  lastName
    .......................................
    999   gdfsgs                    trttr
    956   hsggghhgh                 pipip
    855   hdsgfgsddd                lklkl
    233   ghfgfggfgf                mkmk

TableB(ssn Foreign key reference)
id  ssn   Place
................
1   999   kerala
2   956   usa
3   855   ekm
5   233   chennai


texts: Allow another user to access my Oracle table

texts:
I would simply like to allow a colleague to view and edit the Database I've created.

I've tried:

GRANT ALL on FISHTABLE to JDOE;

and it returned Grant succeeded but nothing changed for him and he still wasn't able to view my table.

I also tried

GRANT SELECT smahala.fishtable to jdoe@example.org;

but that failed with SQL Error: ORA_00990: missing or invalid privilege.

Is my issue that I don't have the administrative authority to allow someone else to view my Oracle table? Any advice is appreciated, thanks.


texts:
So I've got a hosting package from hostgator with an MS SQL database and a local SQL Express 2012 database running. I'm trying to investigate syncing the two together, two-way syncing, and I'm unable to use the SQL replication tools in the SQL management studio, can I use the replication tools? - or do I need to use another tool?

Thanks!

Sorry if this is vague, please let me know and I can clarify.


texts: Sql Server (2017?) Lat/Long index and query

texts:
TLDR: Given a set of LAT/LON => index and return "nearby" results.

I'm working my way through a MEAN Node.js/MongoDB/Express/Angular book. It's definitely not something I'm comfortable with - I'm more comfortable with Sql Server. I'm also looking at Sql Server 2017 in docker containers.

The exercise I'm working on has a Yelp Type application: Given a location, show nearby businesses and give the user the ability to Rate those businesses.

Where I'm at is planning on moving the backend database/api from MongoDB/Express/Node into Sql Server, Asp.net MVC.

Given the sample layout

Is this where I should be looking? I don't see exactly what I'm looking for...

Create table with lat/long coords
index the coords
query nearby

I'm still looking, but I'm not finding what I'm looking for...

edit: Close question here, but no cigar

edit: getting closer (mainly from this blog post... Now trying to figure out how to index and quickly find "close" points

USE master
GO

IF EXISTS(select * from sys.databases where name='MEAN')
    DROP DATABASE MEAN
GO

CREATE DATABASE MEAN
GO

USE MEAN;
GO

CREATE TABLE [dbo].[Landmark] (
    [ID]                INT IDENTITY(1, 1),
    [LandmarkName]      VARCHAR(100),
    [Location]          VARCHAR(50),
    [Latitude]          FLOAT,
    [Longitude]         FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
       ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
       ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
       ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
       ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
       ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
       ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GO

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326);

GO

DECLARE @source geography = geography::Point(40.689168,y    -74.044563, 4326);

SELECT *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]


texts: SQL Server database Mirroring on different servers

texts:
I tried to perform SQL Server database Mirroring(with SQL Server 2008R2). And I got success to perform mirroring with "High Safety" mode.

And for that I have used three instances of SQL Server database.One for Principal second for Mirror and third for Witness.

As I mentioned I used three instances but all of those instances was on the same SQL Server.

But when I tried to perform mirroring with different SQL Server instances then it was not successful.

So I want to know can we perform mirroring with different SQL Server instances? Or we can only do it with instances on same server?

Also in mirroring how many instance we can configure as a Mirroring server ?

And is it a right way to achieve database availability? Or is there any other/advance way to achieve database availability and security?


texts: How to move a 80GB MySQL Database with least effort and least time offline?

texts:
Im a software developer with 4 years experience but brand new in my company, my first job is to move a 80GB mySQL database from a very very small and old database server to a brand new.
The problem: It is the alltime working database for nearly everything in my company... I can't reduce the data to less gb because it is all needed data.
So: How to do this and how to achieve this with the least time offline so around 500 people can work again fastly...

Information requested for Help:

SELECT COUNT(*) as '# TABLES', CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') as DATA, CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') as INDEXES, CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') as TOTAL, engine as ENGINE FROM information_schema.TABLES GROUP BY engine;

Answer:

3 NULL NULL NULL NULL
13 1.36G 1.70G 3.05G InnoDB
13 0.00G 0.00G 0.00G MEMORY
1159 44.04G 34.89G 78.90G MyISAM


texts: Understanding PostgreSQL Execution Plans

texts:
I'm trying to understand the motivation of PostgreSQL 12 query planner here.

Database Structure

CREATE TABLE subjects
(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
);

CREATE TABLE metadata
(
    id SERIAL PRIMARY KEY,
    label TEXT NOT NULL UNIQUE,
);

CREATE TABLE subject_metadata
(
    subject_id INT REFERENCES subjects (id),
    metadata_id INT REFERENCES metadata (id),

    nval BIGINT NULL,
    strval TEXT null,
    values JSONB NULL,

    PRIMARY KEY(subject_id, metadata_id)
);

CREATE INDEX idx_subject_metadata_values ON
   subject_metadata(subject_id, metadata_id, strval, nval);

Data Set

Table subjects contains ~10 million rows, table metadata ~1000 rows and subject_metadata ~400 million rows.

Query

I need to run queries for subject having 1 to N metadata references. So my naive approach was to simply join subject_metadata for each queried metadata:

SELECT d.id, d.name, dmd1.strval, dmd2.nval as lang
FROM subjects d
  JOIN subject_metadata dmd1 on dmd1.subject_id = d.id
  JOIN subject_metadata dmd2 on dmd2.subject_id = d.id
WHERE d.name > '0'
  AND (dmd1.metadata_id = 2 AND dmd1.strval = 'us')
  AND (dmd2.metadata_id = 16 AND dmd2.nval > 100)
ORDER BY d.name
LIMIT 100

This works quite well for two joins with query times < 80ms. Plan:  https://explain.depesz.com/s/PgXg

Joining subject_metadata a third time leads to a drastic performance drop. The following query - which only differs from the original by an additional join - takes 43 seconds to complete:

SELECT d.id, d.name, dmd1.strval, dmd2.nval as lang
FROM subjects d
  JOIN subject_metadata dmd1 on dmd1.subject_id = d.id
  JOIN subject_metadata dmd2 on dmd2.subject_id = d.id
  JOIN subject_metadata dmd3 on dmd3.subject_id = d.id
WHERE d.name > '0'
  AND (dmd1.metadata_id = 2 AND dmd1.strval = 'us')
  AND (dmd2.metadata_id = 16 AND dmd2.nval > 100)
  AND dmd3.metadata_id = 605
ORDER BY d.name
LIMIT 100

Plan:  https://explain.depesz.com/s/sNrU

Rewriting this query by changing the order of join (joining subjects last), brings the performance in line with the first query:

SELECT d.id, d.name, dmd1.strval, dmd2.nval as lang
FROM subject_metadata dmd1
  JOIN subject_metadata dmd2 on dmd2.subject_id = dmd1.subject_id
  JOIN subject_metadata dmd3 on dmd3.subject_id = dmd2.subject_id
  JOIN subjects d on dmd3.subject_id = d.id
WHERE d.name > '0'
  AND (dmd1.metadata_id = 2 AND dmd1.strval = 'us')
  AND (dmd2.metadata_id = 16 AND dmd2.nval > 100)
  AND dmd3.metadata_id = 605
ORDER BY d.name
LIMIT 100

This version completes in 150ms. Plan: https://explain.depesz.com/s/XEjL

I would like to understand why re-ordering the joins makes such a huge difference and/or if there's even a better way to implement this.


texts: How to Import Data to MySQL RDS Instance?

texts:
I have a huge database approx 200GB (Some tables are more than 25GB in size).

We are willing to migrate to MySQL RDS Instance.but as it does not provide any ssh access so we can't copy file system over there(Like backup taken from Hot backup tools).

we will have to take the dump using mysqldump but it may take days to complete the backup and in same in restoring it into RDS.

As backup will lock the tables for a huge time so we will also require a long downtime.

How Can we do it Quickly?I am looking for Quick and dirty solution?
Also What are the limitation of the RDS Instance?


texts:
Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.)

I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but perhaps there are other functions I am missing?


texts: Execution plan issue requires reset on SQL Server 2005, how to determine cause?

texts:
We have a  web application that delivers training to thousands of corporate students running on top of SQL Server 2005. Recently, we started seeing that a single specific query in the application went from 1 second to about 30 seconds in terms of execution time. The application started throwing timeouts in that area.

Our first thought was that we may have incorrect indexes, so we reviewed the tables and indexes. However, similar queries elsewhere in the application also run quickly. Reviewing the indexes showed us that they were configured as expected.

We were able to narrow it down to a single query, not a stored procedure. Running this query in SQL Studio also runs quickly. We tried running the application in a different server environment. So a different web server with the same query, parameters and database. The query still ran slow.

The query is a fairly large one related to determining a student's current list of training. It includes joins and left joins on a dozen tables and subqueries. A few of the tables are fairly large (hundreds of thousands of rows) and some of the other tables are small lookup tables. The query uses a grouping clause and a few where conditions. A few of the tables are quite active and the contents change often but the volume of added rows doesn't seem extreme.

These symptoms led us to consider the execution plan. First off, as soon as we reset the execution plan cache with the SQL command 'DBCC FREEPROCCACHE', the problem went away. Unfortunately, the problem started to reoccur within a few days.

The problem has continued to plague us for awhile now. It's usually the same query, but we did appear to see the problem occur in another single query recently. It happens enough to be a nuisance.

We're having a heck of a time trying to fix it since we can't reproduce it in any other environment other than production. I have downloaded the High Availability guide from Red Gate and I read up more on execution plans. I hope to run the profiler on the live server, but I'm a bit concerned about impact. I would like to ask - what is the best way to figure out what is triggering this problem? Has anyone else seen this same issue?


texts: Why would mysql "show global status" query be taking 15 minutes?

texts:
I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s.

Any idea how to determine the cause of this?


texts: What is more appropriate sp_updatestats or UPDATE STATISTICS?

texts:
In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place.

At the end of this process I would expect  sp_updatestats to run, so that statistics for all this new data is updated.

However, while inspecting the code I noticed the following line instead:

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Can you compare that with running sp_updatestats? That is what is the differences and advantages/disadvantages of one over the other?


texts:
Current status

I have a MySQL (MariaDB 10.2) server with various databases, to which a lot of people have access to. Which means that users can create MyISAM or InnoDB tables/databases.

Problem

Mentioned MySQL server doesn't have any serious fallback/backup, except daily backups. There used to be simple master-slave replication in use, but after numerious sync problems, it had to be stopped. I guess that most of problems came from mix of MyISAM and InnoDB tables, but I couldn't pinpoint the problem with 100% accuracy.

Question

What is the best way to set-up some kind of replication/fallback, that would work with minimum overwatch?

One of the problems is also that size of databases is around 100GB and downtime is undesirable. If I want to set-up a replication, is there any option to do it without serious downtime?

Cloud solutions aren't an option.


texts: redesign database around selecting rows by time range

texts:
I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine by date range like:

SELECT info.sessionID, info.otherstuff, t.time FROM info
INNER JOIN
(SELECT session.sessionID, session.time FROM session
WHERE session.time > :afterSomeDate AND session.time < :beforeSomeOtherDate) as t
ORDER BY t.time DESC

This causes an exponentially large bottleneck in query time as the date range increases. As I am completely redesigning the database, any suggestions are valid including different DB types. There is currently a 3 month RANGE partition on the time column, as well as an INDEX.

During my research I have done a few tests directly in the MySQL database. For instance a query to select every session in my table in a date range of 2 months, has a duration time of .453s and fetch time of 81s (returned 1.7 million rows). According to the accepted answer of this question, my bottleneck could really be the network connection between database and the application.

What else could I do to get a significant performance increase?


texts: Quoted execution trapped by operation mask error using plperlu in postgres sql function

texts:
I am trying to execute this command:
/data/encrypt_args -encryptedemail=lzdukxIDiM14ZccWeNMWBomF/4K4pg9k/uuQa9D4="

through a postgressql function created like this:
CREATE OR REPLACE FUNCTION ScriptDecrypt(email text) RETURNS text AS $BODY$
use warnings;
use strict;
my $email = $_[0];
my $output = `/data/encrypt_args -encryptedemail=$email`;
return($output);
$BODY$ LANGUAGE plperlu;

But I get this error while compiling this function:
'quoted execution (``,qx)' trapped by operation mask at line 5.

Am I using wrong syntax or is it a different issue?


texts: database normalisation on object that is attached to another object that can change

texts:
I am building an inventory management application for equipment attached to machines. The app is to allow scheduling and reporting on maintenance and service of the equipment. The machines have make/model information and also site location lookups. There is also daily task information that is attached to the equipment which gets marked as completed and this is used to produce reports. My app entities look like the following:

Location
Sub-location (fk to location)
Make
Model (fk to make)
Machine (fk to model, fk to sub location)
Equipment (fk to machine)
Task (fk to equipment, read below for more info on fk's)

I need to produce reports and calendars based on task information and would like to see tasks by make/model and also site location in separate reports.

The problem is that a task may be completed while the equipment is at Location A and then the equipment moves to Location B - the equipment might also be removed from the current machine and attached to a new machine. I need to allow my app to support this reality and give the user good historical reporting and future scheduling.

My current solution is as follows:

On the Task model, I have a fk to both site location and machine which is copied from the equipment and machine when the task is created. When the equipment location or machine is changed, I update all incomplete ie. completed tasks have their data frozen.

The benefit I see to this is that I can keep my queries simple without having to always join tasks to equipment and then machine to get location and machine/model/make info and I can also support equipment moving without having to introduce some history type table and a complicated join based on todays date etc.

Can anyone advise on pros/cons to this approach or a new approach?

I'll also add that this is a MySQL/PHP application where I am using laravel and eloquent ORM.


texts: Finding discrepancies between soft link parents and children

texts:
In our system relationships between tables are often (but not always) defined in the application code rather than the database (annoying I know). There's a naming convention which outlines any soft (application side) links.

Here's an example:

On table Oddie, ODD_ID is the Primary key it's got a soft link to table Michael which is called MIC_ODD_ID. There's no Foreign Key constraints between Oddie and Michael as it's defined in the application.

Because of this foreign keys to tables can be created with different amounts of data precision from the "parent" table. So from the previous example, ODD_ID could be a NUMBER(8) and MIC_ODD_ID could be a NUMBER(10).

This is obviously not ideal so I've been tasked with finding all the offending columns. I've been having trouble putting a query together to do this. I'm looking on the DBA_TAB_COLUMNS table and using a substring to outline which columns are similar using this:

SUBSTR(COLUMN_NAME, -0, 6)

Which pulls back the last 6 characters of the COLUMN NAME which should be the same across tables. Using the example from above this would pull back ODD_ID from the Oddie and Michael tables.

I'm having trouble then comparing these strings against each other to find where the data precision isn't the same.

An ideal output would be the offending (Not the same precision) strings and their DATA_PRECISION's along with their OWNER and TABLE_NAME's.

Thanks in advance to anyone who tries to help.


texts: Replication with Repmgr + native streaming replication

texts:
I have an environment where the old DBA used the repmgr. And now I need to add a second replica to use as a "dump server".

But my question is regarding using the native mode of PostgreSQL via stream replication. When I perform base_backup and a second replica is added, can it cause any side effects on repmgr?

I've never used repmgr in prod and I just want to make sure that a simple replica can ruin the whole cluster.


texts: Different results returned when ignoring Primary Key index. Daylight Savings Time related bug?

texts:
I'm hoping you can help explain this behavior or try to reproduce the issue so I can be confident filing a bug report.

Essentially, I get different results from these queries:

# returns 0
select count(*) as COUNT_WITH_INDEX
from a
where id = 1  and begin_time='2018-11-04 01:01:00.000';

# returns 1
select count(*) as COUNT_WITHOUT_INDEX
from a ignore index (PRIMARY)
where id = 1  and begin_time='2018-11-04 01:01:00.000';

The key difference is the use of ignore index (PRIMARY).

In case the date didn't stick out to you immediately, that date falls in the "Fall Behind" hour of Daylight Savings transition for 'US/Central' timezone.  1:01am occurred twice on 2018-11-04.  I only found issues with timestamps that fall in this window, so I suspect its a bug with how DST rules are applied.

Regardless of whether or not I need to use convert_tz() to correctly get the date I want, there's still the fact that I get different results with and without the PRIMARY key index.

Full test case:

create database if not exists test_dt;
use test_dt;

drop table if exists a;

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `begin_time` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000',
  PRIMARY KEY (`id`,`begin_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

set TIME_ZONE='+0:00';

insert into a values(1, '2018-11-04 06:01:00.000');
insert into a values(1, '2018-11-04 07:01:00.000');

set TIME_ZONE='US/Eastern';

select * from a;

# returns 0
select count(*) as COUNT_WITH_INDEX
from a
where id = 1  and begin_time='2018-11-04 01:01:00.000';

# returns 1
select count(*) as COUNT_WITHOUT_INDEX
from a ignore index (PRIMARY)
where id = 1  and begin_time='2018-11-04 01:01:00.000';

set TIME_ZONE='US/Central';
# repeat w/ Central if you like

Side note: Install Time Zone data to use named time zones.

Tested on:: CentOS 7 w/ 5.6.36 and 5.6.43.  I don't have 5.7 or 8.0 installations handy.

Another side note:  The initial problem I encountered was that joins between parent-child tables were not returning data with composite PK (id, timestamp).  Since timestamps are stored in UTC format, I wouldn't think DST dates would be an issue, but here I am.

Do you have an explanation for the behavior?
Do you think it's a bug?

Thanks!

Edit
Additional info per comments

  What result do you get if you drop the p.k?

set TIME_ZONE='+0:00';

insert into a values(1, '2018-11-04 06:01:00.000');
insert into a values(1, '2018-11-04 07:01:00.000');

select * from a ;

select 'Set time_zone=US/Central' as msg;
set TIME_ZONE='US/Central';
+------------------+
| COUNT_WITH_INDEX |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

drop index `primary` on a;

select count(*) as COUNT_DROPPED_PK
from a
where id = 1  and begin_time='2018-11-04 01:01:00.000';

+------------------+
| COUNT_DROPPED_PK |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

set TIME_ZONE='+0:00';

select * from a;

+----+-------------------------+
| id | begin_time              |
+----+-------------------------+
|  1 | 2018-11-04 06:01:00.000 |
|  1 | 2018-11-04 06:01:00.000 |
+----+-------------------------+

Notice If I change the TZ to US/Central, drop the PK, then set the TZ back to '+0:00', I can see that the data is screwed up.  Both records are 06:01 time, when one was inserted 07:01.  That behavior is not logical to me since the timestamps should always be UTC in the backend.

  1) Show the output of set TIME_ZONE='US/Eastern'; select * from a; 2) What if set TIME_ZONE='-4:00';?

set TIME_ZONE='US/Eastern';
select * from a;
+----+-------------------------+
| id | begin_time              |
+----+-------------------------+
|  1 | 2018-11-04 01:01:00.000 |
|  1 | 2018-11-04 02:01:00.000 |
+----+-------------------------+

set TIME_ZONE='-4:00';
select * from a;
+----+-------------------------+
| id | begin_time              |
+----+-------------------------+
|  1 | 2018-11-04 02:01:00.000 |
|  1 | 2018-11-04 03:01:00.000 |
+----+-------------------------+


texts: MySql - How to monitor the amount of reads/writes to the DB

texts: Is there a way to fetch the amount of reads / writes made to the entire DB (MySql) in a given period, e.g. the amount of read / writes in the last hour?


texts: Commit or rollback SQLServer-SQLTransaction when no data changed

texts:
we use the SqlTransaction-class in the backend to access data stored in the MS-SQLServer.
In some cases we definitely know that no data were changed by this transaction (and no errors occurred).
So the question is: How to close transactions in these cases?
Shall we use Commit() or Rollback(), or none of this both functions or something else?
The aim is to find the most performant way to finish the transaction in a "reasonable" way.


texts: SQL Server Mirroring can't connect to mirror database

texts:
I am at my wits end with this. I am trying to set up mirroring between a development server and my local machine.

The error that keeps coming up is:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://machineabc:7023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Which occurs when I go through the GUI or run

ALTER DATABASE TESTING SET PARTNER = 'TCP://machineabc:7023'

I restored the database first with the .bak and with no recovery then the .trn with no recovery. The local database has (Restoring...) next to it...

I have run select * from sys.database_mirroring_endpoints and it all looks good (Partner, started, both have rc4 encryption).

Any suggestions?
Edit: So I tried again and I did GRANT CONNECT on the access point then when I tried setting the partner I did a net stat and this was the result:

Here are the results of the connectivity tests in Craig Efrein's post:


texts: What would cause an elastic pool to exhibit high "Sql Server process core percent" metricsj, but low DTU / CPU

texts:
Backstory:
We have an azure sql elastic pool with several databases in it and around 750GB of data.
We've been having some sporadic outages which correlate with the metric "Sql Server process core percent" hitting a max of 100% for a duration.
Normally, the process core percent metric trends with the cpu percentage metric pretty closely. However, during these outage scenarios, the correlation is actually inverse. The CPU metric (max aggregation) bottoms out, when the process core percent (max aggregation) is peaking.
If I use average for the aggregation, the trend of them correlating is maintained.
TL;DR:
What would cause the "Sql Server process core" metric to max out independent of the CPU metric in an Azure Sql Elastic Pool?


texts: Increased memory to server, but do I need to change page-file size?

texts:
I'm running SQL Server 2017 on Windows Server 2012 R2 and have increased the memory from 256 GB to 512 GB.
I've noticed the following configuration for Virtual Memory:

C: [OS]   = 2 GB
G: [DATA] = None
H: [LOGS] = None
T: [TEMP] = None

There is a lot of advice out there talking about setting the size to 1.5 or 2 times the size of the memory that is installed.  Am I reading this correct or have I misread the articles and they apply to something else?

There is also articles suggesting not to worry about the virtual memory as SQL should only be using RAM and not virtual memory, and the OS only requires 2GB of virtual memory?


texts:
this question continues this one:
I have table that simulate a file system or directory of text files with the following columns:
 file_name varchar2 not null primary key,
 file_text clob,
 file_size number,
 creation_time timestamp,
 modification_time timestamp

I want to update size and modification_time according to the clob's text whenever it been changed, but, as I understood, it can't be done by a trigger if updated by a SELECT text FROM table WHERE ... FOR UPDATE and lob.write(...) command.
I don't want to have a job for scanning the tables over and over again, in manner to update those values.
Can it be done in any other way?
Thanks!


texts: What are the alternatives for prefixed indexes in Oracle?

texts:
You can create an index and define prefix length, so the index will store only first starting symbols from each of column value. It looks like this in MySQL:

CREATE INDEX table_idx ON emp(last_name(8));

Are there an alternative how to do that in Oracle?


texts: Minimum permission needed for GRANT CRUD ON

texts:
I have a certificate set up to do cross database CRUD operations.  The certificate is linked to a user in each destination database.  I sign any stored procedures in the source database with the certificate, and grant the CRUD access to the user in the destination database.  Everything works fine with this setup.

We recently hired a couple of developers to help with the workload, and they are now creating stored procedures that need to be signed.  I found that by giving them CONTROL of the certificate, they are able to sign the stored procedures in the source database.

However, they aren't able to grant CRUD access to the user in the destination database.  Is there a way I can allow them to do that without giving them db_owner or db_securityadmin in the destination database?  Is there a better way to manage this task?


texts: How to segment account info and profile info?

texts:
I am putting a rough SQL database design together and have gotten stuck on how to segment information about the users.

(Note before I get into anything, stored passwords are salted and hashed, I just write "password" for brevity)

For reasons I can't go into due to NDA, our database will have two user groups - human users and fake users. Human users will have login information (screen name, email address, password). Fake users only need a screen name. All users, human and fake, need profile information (first name, last name, gender, location, etc). Most of the profile information is only displayed on the user's profile page. However, depending on the user's settings, either their real name or screen name may be displayed under their avatar throughout the front-end. Email address is not visible in the user's public profile.

I can't decide how to segment this information into tables. My first instinct was to have three separate tables,

"accounts" (for humans - screen name, email, and password)
"user_profiles" (first name, last name, gender, location, etc)
"fake_user_profiles" (screen name, first name, last name, gender,
location, etc).

But this runs into several problems:

Each user, human or fake, needs a unique ID - no human should have
the same ID as any fake user. I would have to enforce UIDs across two separate tables
I have two almost identical tables with
only one field different between them

My next idea was to put all of the users, human or fake, together. Then I'd have two tables:

"accounts" (screen name, email, password)
"profiles" (first name, last name, gender, location, is_human, etc)

With this approach, it would make sense to put the UID for each user (human or fake) into the "accounts" table. But what would I do with the "email" and "password" fields that should be non-null for humans but serve no purpose for fake users?

Would there be a better approach than either of the two I've described? Would it be better to put all the information in one table?


texts: "parameter name used more than once" error when declaring function - why this is happening?

texts:
create function test(a integer, b integer)
returns table (a integer, b integer) as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

throws exception:

ERROR:  parameter name "a" used more than once
CONTEXT:  compilation of PL/pgSQL function "test" near line 1

Why this is happening? I thought that function argument name has nothing to do with returned table column name. Is there a workaround to have function argument named "a" and also have column named "a" in a table returned from function?

I know that I can return setof record but then I have to explicitly "define" columns returned from function in select query:

create function test(a integer, b integer)
returns setof record as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

select * from test(1, 2) as (a integer, b integer)


texts: Can a plpgsql stored procedure monitor for a query and kill it?

texts:
I want to write a function that monitors for a particular query becoming a blocker (i.e. it is both blocked by a query, and is blocking another query) and terminate it. Here is my current code, which I have amended to use left join for testing purposes so that it does not require the query to be blocked:

CREATE OR REPLACE FUNCTION monitor_sql_and_terminate_blocker(IN p_query text, OUT result text)
  RETURNS text AS
$BODY$
DECLARE
  monitor_sql text;
  rec record;
BEGIN

  monitor_sql = '
with blocker as
(
  select distinct
    waiting.pid  pid
  , other.pid    blocker
  from pg_stat_activity
  join pg_catalog.pg_locks  waiting
    on waiting.pid = pg_stat_activity.procpid
    and not waiting.granted
  join pg_catalog.pg_locks  other
    on ( ( other."database" = waiting."database"
           and other.relation = waiting.relation )
         or other.transactionid = waiting.transactionid )
    and other.pid <> waiting.pid
  where current_query not like ''%<IDLE>%''
)
, blockers as
(
  select pid, array_to_string(array_agg(blocker),'','') blocker_list
  from blocker
  group by pid
)
, blocking as
(
  select blocker, array_to_string(array_agg(pid),'','') blocking_list
  from blocker
  group by blocker
)
select
    procpid
from pg_stat_activity
left join blockers on blockers.pid = pg_stat_activity.procpid
left join blocking on blocking.blocker = pg_stat_activity.procpid
where current_query = ''' || p_query || '''
';

LOOP
  FOR rec IN EXECUTE monitor_sql
  LOOP
    RAISE NOTICE 'Terminating procpid %', rec.procpid;
    PERFORM pg_terminate_backend(rec.procpid);
  END LOOP;
  PERFORM pg_sleep(1);
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

It is invoked like this:

select monitor_sql_and_terminate_blocker('select * from very_large_table')

However, it just loops infinitely and never does anything. If I run the query manually, it finds the process and returns the procpid which I can then terminate manually.

This is because of transaction isolation, the function only sees the queries that were running when it started. If I run the monitor function while the query is running, it kills it successfully and then keeps trying to kill it again and again. What can I do to work around it?

My current solution is to move the loop out into a shell script that runs psql to invoke a version of this code that runs once and then exits.


texts: How to calculate MySQL Transactions per second

texts:
How can I calculate MySQL Transactions per second.

I tried to use formula (global com_commit+global com_rollback)/uptime since flush.

I had also tried (global insert+ global delete+ global update)/uptime since flush.

The outcome of inserts+deletes+updates is less than commits+rollback. The outcome of commits+rollbacks is 8 times larger than inserts+deletes+updates. So confused on the formulas which one to take.


texts: I changed the max memory setting but I don't see memory usage rise at OS level

texts:
We have increased the RAM capacity on the OS to 40G and I have changed the max memory parameter to 36G in SSMS.

When I select "current value", it shows 36G.

However, monitoring memory usage at OS level doesn't show more memory being consumed.

I know that, in theory, it's possible to change the max memory setting while SQL Server is still running, but does it really work? Do I need to restart the server? Or am I being impatient?

Thanks


texts: For a partitioned table, how do I determine via T-SQL what is the next partition number that will be used?  For example, the switch-in today went into partition number 50.  I want to build the new switch-in table to contain the boundary values for partition number 51. I'm currently doing this manually and want to automate it via stored procedures. I'm using SQL Server 2012.


texts: Is there any function like checksum in Oracle

texts:
I need to calculate the checksum of some columns. The built-in function checksum is used to calculate the checksum in Microsoft SQL Server.

Is there any built-in function checksum in Oracle?


texts: How to do Postgresql 9.2 version check in Linux?

texts:
$ systemctl restart postgresql-9.2.24

Failed to restart postgresql-9.2.24.service: Unit not found.


texts:
I have SQL Server Express on my VPS(2GB RAM) with a database size of 1.5 GB that I expect will increase to 15 GB. I have noticed that SQL Server is using 1.5 GB RAM even when only small queries has been run against the database.

Is there any relation between memory and database size? Can I tune my SQL Server so it can handle memory better and not use as much memory as it does now?

Here is the memory status of SQL Server:

VM Reserved,1455488
VM Committed,19320
AWE Allocated,0
Reserved Memory,1024
Reserved Memory In Use,0


texts: Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

texts:
I have following example query.

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);

begin
      select '01-01-2000', '30-11-2011'

      into :pStartDateBegin,:pEndDateFinish
        from dual;

end;

-- SELECT  :pStartDateBegin,:pEndDateFinish FROM dual;

WITH EXAMPLE
AS
(
    SELECT OWNER,TABLE_NAME FROM DBA_TABLES T
    WHERE T.LAST_ANALYZED BETWEEN :pStartDateBegin AND :pEndDateFinish
)
SELECT * FROM EXAMPLE;

When I run this query using TOAD. It runs successfully.

XXXX rows selected.
Time End: 06.12.2011 12:05:37
Elapsed Time for Script Execution: 3 secs

In SQL Developer, I get following Error.

Error report:
ORA-06550: line 12, column 2:
PLS-00103: Encountered the symbol "WITH"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

In sqlplus, I have similar error.

ATILLA@DENEME > @WithAndBindVariablesExample.sql
WITH EXAMPLE
*
ERROR at line 12:
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "WITH"

If I use following notation

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);

exec :pStartDateBegin := '01-01-2000';
exec :pEndDateFinish := '30-11-2011';

WITH EXAMPLE
AS
(
    SELECT OWNER,TABLE_NAME FROM DBA_TABLES T
    WHERE T.LAST_ANALYZED BETWEEN to_date(:pStartDateBegin,'DD-MM-YYYY') AND to_date(:pEndDateFinish,'DD-MM-YYYY')
)
SELECT * FROM EXAMPLE;

It runs successfully all three tools.

It seems that I lack a basic knowledge about this. I would like pointers to tutorials about this behavior, explanations etc.


texts: Performance issue with composite primary key or not on Transactional Replication with AlwaysOn Subscribers?

texts:
I configure identity column with composite primary key on Transactional Replication scenario to maintain unique row value.

    CREATE TABLE [dbo].[AD_CLIENT_DETAIL_MST](
    [CLIENT_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SERVER_ID] [char](1) NOT NULL CONSTRAINT   [DF_AD_CLIENT_DETAIL_MST_SERVER_ID]  DEFAULT ('A'),
    [C_NAME] [varchar](300) NOT NULL,
    [CLIENT_TITLE] [varchar](10) NULL,
    [C_FIRST_NAME] [varchar](100) NOT NULL,
    [C_MIDDLE_NAME] [varchar](100) NULL,
    [C_LAST_NAME] [varchar](100) NULL,
    [C_DOB] [date] NULL,
    [RESELLER_ID] [bigint] NOT NULL,
    [RESELLER_NAME] [varchar](300) NULL,
    [CITY] [varchar](50) NULL,
    [STATE] [varchar](50) NULL,
    [COUNTRY] [varchar](50) NULL,
    [COMPANY_NAME] [varchar](500) NULL,
    [COMPANY_LOGO_PATH] [nvarchar](max) NULL,
    [COM_ADD1] [varchar](300) NULL,
    [COM_ADD2] [varchar](300) NULL,
    [BANK_NAME] [varchar](50) NULL,
    [BANK_ACCOUNT_NO] [varchar](50) NULL,
    [PAN_NO] [varchar](20) NULL,
    [TIN_NO] [varchar](20) NULL,
    [BANNER] [varchar](50) NULL,
    [YOCC_NO] [varchar](15) NOT NULL,
    [PRIMARY_NO] [varchar](15) NOT NULL,
    [SECONDARY_NO] [varchar](15) NULL,
    [ALLOWED_CHANNEL] [int] NOT NULL,
    [ACTIVE_OPERATOR] [int] NOT NULL,
    [CALL_ROUTING_TYPE] [bigint] NOT NULL,
    [CALL_ROUTING_PATTERN] [bigint] NOT NULL CONSTRAINT [DF_AD_CLIENT_DETAIL_MST_CALL_ROUTING_PATTERN]  DEFAULT ((0)),
    [LIST_STATUS] [bigint] NOT NULL,
    [SMS_PROVIDER_ID] [bigint] NOT NULL,
    [EMAIL_PROVIDER_ID] [bigint] NULL,
    [IS_BLOCK] [bit] NOT NULL,
    [REG_DATE] [datetime] NOT NULL,
    [TOTAL_USER] [int] NULL,
    [TOTAL_ADMIN] [int] NULL,
    [EXP_DATE] [datetime] NULL,
    [CHANNEL_COUNTER] [int] NULL,
 CONSTRAINT [PK_AD_CLIENT_DETAIL_MST] PRIMARY KEY CLUSTERED
(
    [CLIENT_ID] ASC,
    [SERVER_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

So, when i define two primary key then i will give some slow query performance or not ?


texts: Indexed view not being used - SQL Server

texts:
I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and index.

I want to leverage on the advantage of indexed view, wherein if I've a similar query or the exact same query somewhere else, the query optimizer chooses the indexed view so that performance is improved. I tried using the exact same query that was used to create index (with an additional date filter) but query plan doesn't seem to change. Index on the view is still not used even if I explicitly use the view in the query. I could only get the query optimizer to use the index with noexpand hint. Note that I'm using SQL Server enterprise edition.

Any suggestions?


texts: How to recover/restore corrupted Innodb data files?

texts:
A while ago, my Windows 7 system on which a MySQL Server 5.5.31 was running crashed and corrupted the InnoDB database. The weekly backup that's available does not cover all the tables that were created in the meantime, therefore I would endeavor to recover as much as possible from the data. Right after the crash, I copied the whole data folder of MySQL to an external drive. I would like use this as the starting point for my rescue attempts.

In the following I'll describe the steps of my (not yet convincing) rescue attempt so and would be thankful for any comments or guidance on how to improve it:

I've now done a fresh install of MySQL Server 5.5.31 on another PC
I stop the MySQL service with "net stop MySQL" at the command prompt.
I already figured that I need to adjust the size of the innodb log file in the my.ini file as it deviates (256 MB) from the default value (19MB).
In the my.ini, I also set innodb_force_recovery=6
In the data folder of the fresh installation, I overwrite the ibdata1, iblogfile0, iblogfile1 files with those recovered from the crashed machine. I also copy the relevant database (UPDATE: and the mysql) folders into here (NOT the standard mysql, test and performance folders).
I start the MySQL service with "net start MySQL".
I go into MySQL Workbench, open my server instance, go to Data Export, basically leave the default settings, and have every table of my databases exported as an individual dump file. I also set stored procedures to be dumped. Otherwise I do not change the default settings there.
I start the dump process; it makes its way through 43 out of 195 tables. Of these 43,

some cannot be recovered yielding an error "mysqldump: Got error: 1146: Table '...whatever...' doesn't exist when doing LOCK TABLES",
but many can. I assume that when the dump does not yield any error, the table's data is non-corrupted.
Then, after the 44th, all the other table dumps fail as it is reported that the server cannot be connected to anymore:
"mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect
Operation failed with exitcode 2"
These errors then go on for all the remaining tables from the 44th to the 195th.
For the 44th table itself, the error is the following: "mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table ...table 44... at row: 57". So it seems that for this table corruption is or begins at row 57.

Now to my questions:

Why is the connection breaking down given that innodb_force_recovery is set to 6?
How to proceed? I worked out what the 44th table was where the connection was lost and can try to resume the process from the 45th table. But isn't there a better way to do it?
Once the data has been copied and the server restarts well, should I just try a dump of each table or what alternatives are there?

Thanks.

UPDATE: Additional notes for my later reference
- When re-creating stored routines backed up using SHOW CREATE PROCEDURE ... and SHOW CREATE FUNCTION ..., they must be imported using  DELIMITER //
(create procedure code of procedure 1)//
(create procedure code of procedure 2)//
DELIMITER ;


texts: How to measure the memory usage of a Mongodb aggregation?

texts:
I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I read that this can significantly slow down performance.

I would like to optimize the query but I cannot figure out how to measure how much memory is being used by the aggregation.
I also need to run the pipeline on multiple collections in production so I need to know how the memory usage will scale with collection-size.

Does anyone know how to do this? The .explain() method does not seem to show any information on memory usage and I haven't found anything in the docs/forums.


texts: mariadb / mysql wont change table_cache / table_open_cache

texts:
I've configured my i7 64gb ssd server with centos7 with the following my.cnf

[mysqld]
key_buffer = 12M
max_allowed_packet = 16M
table_cache = 4096
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size = 48M
read_buffer_size = 32M
read_rnd_buffer_size = 104M
myisam_sort_buffer_size = 100M
myisam_max_sort_file_size = 1024m
#myisam_max_extra_sort_file_size = 1024m
myisam_repair_threads = 1
thread_cache_size = 32
query_cache_type = 1
query_cache_size = 512M
query_cache_limit = 8M
max_connections=50
wait_timeout=60
tmp_table_size = 256M
max_heap_table_size = 64M
#innodb_io_capacity = 400
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 24M

But when i run mysql_tuner.pl
i still get

TABLE CACHE
Current table_open_cache = 482 tables
Current table_definition_cache = 4096 tables
You have a total of 1027 tables
You have 482 open tables.
Current table_cache hit rate is 15%
, while 100% of your table cache is in use
You should probably increase your table_cache

Of course i restarted my mariadb instance. but i have no clue.
Search turned up empty as well.

Hope someone could help out Cheers.


texts:
How do I convert two integers, one for date and one for time, into a timestamp?

Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM.
Given 20200304 and 946, return a timestamp for April 3, 2020, 9:46 AM.
Given 20200304 and 2359, return a timestamp for April 3, 2020, 11:59 PM.
Given 20200304 and 0, return a timestamp for April 3, 2020, 0:00 AM.

I'm running Version 7, Revision 1.


texts: The Filegroup PRIMARY for the database CRM running out of space

texts:
I have a db for which I am getting error as shown below.

The Filegroup «PRIMARY» for the database "CRM" in SQL instance "MSSQLSERVER" on computer "prod" is running out of space.

sqace details in MB are as below: mdf file now has reached 249 GB.

CRM mscrm       D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CRM.mdf  249157
CRM mscrm_log   E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\CRM_log.LDF 5137

D and D drives are having more than 50 GB free space. C and temp has more than 30 GB and 20 GB free space each .. No drive is running out of space.

growth is set as auto with no restriction at this database.

Any clues ?

Thanks
Hungry DBA


texts: What happens if I GRANT priveleges (for example SELECT) ON a table TO some group role? What privileges will the table's elements have in this case: indexes, sequences, columns? Should I explicitly GRANT privileges to them if I REVOKE ALL ON SCHEMA public FROM GROUP role and FROM PUBLIC previously?


texts: User defined function performance disadvantages

texts:
I have a database with a lot of UDFs that are called by a long running process involving lots of data manipulation and calculations.

My thinking in using UDFs is to separate out logical units of information from the tables underlying.  For example, if i am trying to get information about a car i might have several tables like Color, Model, Year, etc that i would have to join each time to get a Car.  Instead, I would have a function like fnCar() to get a denormalized view of the data.

I call these functions a lot during my long running process and I'm wondering if it would be better if instead I had a denormalized working table,view, or temp table to do my data manipulation and calculations.  Is there some disadvantage to using UDFs in general that I should be aware of in terms of performance?

For example, I make some calculations using a UDF.  I then unpivot that data and store in a table.  Whenever i need to use that data again, I call a UDF to pivot the data back out.  The reason we do it this way is to keep our calculations flexible.  We don't want to change the data model if we add/remove/change the calculations.

--Calculate some values in a function

declare @location table
(
    id int,
    lattitude float,
    longitude float
)

insert into @location select  1, 40.7, 74
insert into @location select  2, 42, 73
insert into @location select  3, 61, 149
insert into @location select  4, 41, 87

declare @myLattitude float
declare @myLongitude float
set @myLattitude =43
set @myLongitude = 116

declare @distance table
(
    id int,
    distance float
)

insert into @distance
select id, sqrt(power(lattitude-@mylattitude,2)+power(longitude-@mylongitude,2))
from @location

--Store unpivoted data in a table
declare @unpivot table
(
    id int,
    attribute varchar(100),
    attributeValue float
)

insert into @unpivot
(
    id,
    attribute,
    attributeValue
)
select id
    ,attribute
    ,attributevalue
from
(
    select
        L.id,
        L.Lattitude,
        L.Longitude,
        D.Distance
    from @location L
        inner join @distance D
        on L.id=D.id
) a
unpivot
(
    attributeValue for attribute in
    (lattitude, longitude, distance)
) x

--retrive data from store via pivoting function for reporting

select *
from @unpivot
pivot
(
    max(attributeValue) for Attribute in (lattitude, longitude, distance)

) x


texts:
My question sound stupid but i didn't understand and searching on google didn't helped either.

I have a SQL query which i use in SCCM Reporting.
I just want to say that my hands are bit tight in SQL and database related stuffs.
Below is the SQL query.

   SELECT top 3 *
 FROM fn_appdtclientsummarizedstate(dbo.fn_LShortNameToLCID(1033)) acs

This is, i get from existing report (SSRS).

I checked the SCCM database but this table, views is not there. Hence i can't make the modifications as needed.

If you can help me then i would be very grateful.

I spent almost 2 days but no luck finding/understanding this query.


texts: Does restoring a full backup bring it to the backup START time or the backup FINISH time?

texts:
Backstory: I'm trying to restore copies of several SQL 2012 databases to a different environment, but the application needs them to be (roughly) "synchronized" in time.

(Of course the ideal way to do this is by using FULL recovery mode using full and log backups, and then do point-in time restores using the same timestamp. But in my case, I don't need them perfectly synchronized, just within ~10 minutes or so, and I'd rather deal with only full backups and restores. I do have control over when the full backups run, but the problem is that the databases are of very different sizes, so the full backups run for very different lengths of time.)

So my question: should I synchronize the start time of the full backups? Or the finish time?

In other words, when I restore a full backup, will I end up with how it looked when the backup started, or when it finished?


texts: Mysql REPLACE INTO query for Multiple rows insertion

texts:
I'm trying to add multiple rows using 'replace into' , but its updating one row.

REPLACE INTO user_balance
(user_id,acc_type,currency,balance,enable_deposit,
enable_withdrawal,pending_balance,update_ip)
VALUES  (NEW.id,1,'USD',0,1,0,0,NEW.ip_address),
(NEW.id,1,'GBP',0,1,0,0,NEW.ip_address),
(NEW.id,1,'EUR',0,1,0,0,NEW.ip_address),
(NEW.id,1,'BTC',0,1,0,0,NEW.ip_address);

Is this right, the way am doing it?

PS: am employing a trigger to update the balance table whenever an user is created in the users table. It works well with if I replace one row, for multiple rows, it updates only the last values.


texts: Parallel Sequential Scan on SELECT statement, but not on SELECT ... INTO

texts:
The following query

EXPLAIN
SELECT c.global_id
FROM centers_res17 AS c
LEFT JOIN regions_res17 AS r ON ST_Contains(r.wkb_geometry, c.wkb_geometry)
WHERE r.global_id IS NULL;

says that a Parallel Sequential Scan on centers_res17 will be used. This is good and the query is fast (8 workers are used, and the CPU is busy)

Now, if I do a SELECT c.global_id INTO foo the query plan indicates a Sequential Scan, which is much slower.

I tried to:

CREATE the foo table before (also as a TEMP)
Wrap the above statement in a WITH clause and then select its results into foo.
CREATE foo AS (^^Above statement^^)

None of the above uses multiple workers to do a Parallel Sequential Scan (according to the query plan and actually running the query). I could just make the select statement 'as is' and then copy-paste the values from my results window ... not optimal.

Please explain to me, why the Parallel Sequential Scan is not used, or how to modify my query to have the results selected into foo.

I am using PostgreSQl 9.6 on Win10.


texts: Are clustered indexes in Azure data warehouse ever a good idea on large tables?

texts:
Azure data warehouse supports both clustered and non-clustered indexes in addition to columnstore (which is the default for any new table).

I know that having a large clustering key is normally discouraged, as it forces sql server to internally generate a larger key to guarantee uniqueness (and unique constraints are not supported). Of course there are loads of benefits of using columnstores, such as  batch mode and segment elimination, however columnstore will use hash match and hash join operations for analytical queries.

Experimentally I've seen situations where a clustered key performs better where all the grouping columns are included in the clustering key (4-5 columns, some varchars), because the guaranteed order allows a stream aggregate to be used instead.

My tables are distribution aligned and the estimated execution plan shows that the queries against the columnstore indexes are indeed using batch mode.

What are the potential pitfalls with this approach, is this scalable for billions of rows of data?


texts: What is the cheapest way to mirror/replicate data to multiple servers on SQL Server?

texts:
I have an SQL Server 2014 Enterprise Edition with a lot of data.  I would like to mirror the data from that to at least 3-4 servers.  The mirrors are all SQL Server 2014 Standard Edition (no more money is available for Enterprise licenses).

How do I mirror the data from my main box (with the Enterprise Edition license) to other boxes?  I tried the mirroring feature, but it seems that it only allows single mirror.  I could you use Always On Availability groups, but that would require that all mirrors also be Enterprise Edition (unless I am reading the docs wrong).

At least one of the mirrors needs to be there almost real-time (1-2 minute delay is fine) data replication.  The other mirrors could have 1-2 hours delay.

So what are my choices?

P.S.  All the secondary servers are just read only.

P.S.  The purpose of the mirrored boxes are partially to off-load readonly queries to them.  These mirrors need to have near real-time data replication.  Another purpose is for analytics, which is a heavy load.  Today everything is on the same box and we are forced to do analytics at night so as not to disrupt users and there is just not enough time.

P.S.  The servers are nearby each other - on the same subnet, connected via a 10Gb link.

P.S.  Our license also allows a no cost upgrade to SQL Server 2016 when it becomes available.  Does that change anything?


texts: Can you restore purged MySQL binary logs?

texts:
I have two situations I need some clarification.

1) Let's say I made a backup of all the MySQL binary logs before I purged a single binary log file (the oldest one, possibly the very first one).

  mysql-bin.000001

Can I restore this single binary log from the backup I made? Just copy and paste it from my backup to the MySQL data directory and I can use it again as if it was never deleted in the first place?

2) Another situation, let's say I purged every bin log except the current one but after a month or two I needed to restore something from the middle of the purged bin logs like:

  mysql-bin.000099

Can I just restore this single file from backup and do whatever it is I need to get back from this bin single bin log file? Or I would need to restore everything before I could use that file?


texts: Selecting the max date out of a text group

texts:
This query selects the questions with the same subject

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id                                                       as si
       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;

It gives me result something like

new subject message_id product_id si question_date
subject1    65299   0   5   2018-03-11 11:03:02
subject1    64522   0   5   2018-03-10 14:53:05
subject1    64511   0   5   2018-03-10 14:51:18
subject2    65214   0   5   2018-03-11 15:40:36
subject2    64625   0   5   2018-03-11 13:28:25
subject2    65245   0   5   2018-03-11 13:03:41
subject2    64365   0   5   2018-03-11 12:49:01
subject3    65007   0   5   2018-03-29 19:58:15
subject3    64043   0   5   2018-03-29 18:42:20
subject4    65562   0   5   2018-03-09 14:23:44

But just want to get the recent(only 1 ) subject1,subject2,subject3,subject4

I added these lines to get it

  WHERE question_date IN (
    select max(question_date) FROM messages
  )

Which has become

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id                                                       as si
       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x
  WHERE question_date IN (
    select max(question_date) FROM messages
  )

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;

But it doesn't return me 1 record from each, but no result. What am I missing?
Also it would have been great if you can provide a more performant query.

This is my table description

                                                Table "public.messages"
     Column      |              Type              | Collation | Nullable |                   Default
-----------------+--------------------------------+-----------+----------+----------------------------------------------
 message_id      | integer                        |           | not null | nextval('messages_message_id_seq'::regclass)
 product_id      | integer                        |           |          |
 subject         | text                           |           |          |
 body            | text                           |           | not null |
 customer_id     | integer                        |           | not null |
 message_type    | integer                        |           | not null |
 question_date   | timestamp(0) without time zone |           | not null |
 seller_id       | integer                        |           | not null |
 deleted_at      | timestamp(0) without time zone |           |          |
 created_at      | timestamp(0) without time zone |           |          |
 updated_at      | timestamp(0) without time zone |           |          |
 market_place_id | bigint                         |           | not null |
 answer_source   | integer                        |           |          |
 answer_id       | bigint                         |           |          |
 hide_question   | boolean                        |           | not null | false


texts: SQL Server Service Account Configuration - one account per Server?

texts:
Planning to re-create new service accounts for our Sql-Servers, I wondered if - from a security perspective - it maybe better to have one domain account per Service and per Server or to create one domain-account per service only and re-use those on the different servers?

DOMAIN\Service-SSRS
DOMAIN\Service-SSAS
DOMAIN\Service-SSIS

vs.

DOMAIN\Service-SQL01-SSRS
DOMAIN\Service-SQL01-SSAS
DOMAIN\Service-SQL01-SSIS
DOMAIN\Service-SQL02-SSRS
DOMAIN\Service-SQL02-SSAS
DOMAIN\Service-SQL02-SSIS
etc.

We are doing this because we want to have a uniform and consistent configuration on all servers, something that is not yet in place today. I feel like having single accounts for every server might allow more flexibility and so is counterproductive...

Are there specific pros / cons? Is there a lot of usage of having separated service accounts for every server?


texts: How to concurrency will be handled in this case?

texts:
Say I have employee table with 100 records . Out of 100 records, 60 records having middle_name as null

Say in two separate sessions(say Session_A, Session_B),  I execute below query at same time

    update employee middle_name ='test_middle_name' where middle_name is null

My question is is there a possibility Session_A can work on X records where Session_B work on Y records where X+Y=60 on is it always
single session will get lock on all 60 records and there will be nothing to process for  another session ?

I understand both process will try to get the exclusive row level lock on applicable rows but  process will try to acquire those lock row by row or in one go.

My Understanding:-

My understanding is It is possible that Session_A can work on X records where Session_B work on Y records where X+Y=60. Reason is each process
will get some CPU cycles(say in round robin fashion) and pick the records based on available records whose middle_name is null


texts:
I have a single table that holds the following data columns:

id = Auto Increment
provider = Twitter or Facebook (depends what they use to login with)
uid = User ID
name = Their full name (e.g. Chris Burton)
image = Profile Image
link = Social network profile link

In that same table I also have the following columns

comment = Comment text
article_id = Article ID
date = Date of when comment was submitted

My question is, should I keep the user login data separate from my comment data? Or is it more efficient to keep it together?


texts: How can I parse mysql bin log file for statements (Row/Mixed Format)

texts:
I have a mysql database running with binary log enabled. Unfortunately, a statement was executed by the client, which turned out erroneous. Since a number of records have been "affected", I want to begin by parsing the bin-log for data-changing statements from that time period. Then we might be able to begin picking up the pieces.

I have used:

mysqlbinlog -v --base64-output=decode-rows --start-datetime="2015-06-26 08:30:00" --stop-datetime="2015-06-26 09:30:00" data/mysql-bin.000005 > C:\temp\binlog.txt

The output is quite intimidating. What I would appreciate is a strategy for parsing out any INSERT, UPDATE or DELETE statements. Keep in mind I'm on MIXED MODE.

Thank you.


texts: Postgres How to remove garbage memory capture by data folder

texts:
I see in the postgres data folder that 124 GB are being used.

But when I execute the following SQL statement to check the total disk space used by the database, it shows 74 GB.

SELECT pg_size_pretty(sum(relpages::bigint*8*1024)) AS size
FROM pg_class;

I have only database here, so the data folder size must be nearer to 74 GB, but on the file system it is occupying 124 GB.

I could not find out what other disk space is being used. I successfully executed vacuum full to clean up disk space.

Can someone tell me what could be residing in the data directory? If it is not required, then I would like to free up this disk space.


texts: Should SQL full text search match the value test2 when searching for test?

texts: if i have table data that is indexed using full text search in Microsoft SQL server 2005 that includes raw table data such as "test2" should a query using FTS find a match if the search string is "test"?


texts: Teradata SQL Assistant - CALL Failed HY000: User Defined Function source on the client is not supported by the driver

texts:
Using Teradata SQL Assistant to install a user defined R script fails with an error.
The call looks like this:

SET SESSION SEARCHUIFDBPATH = demouser; -- Tells the linux user tduser, where to find the installed script
DATABASE demouser;                                      -- Sets the active database
CALL SYSUIF.INSTALL_FILE('test','test.R','cz!C:\\Temp\\sample2.R');     --installs the script with <script_name>, <script_filename>, and <script_local_location> to active database

The error message is

  CALL Failed. HY000: User Defined Function source on the client is not supported by the driver

I am using the current ODBC 64 bit Version from the ODBC Driver for Teradata - Suite 15.10.08.


texts:
I'm working on a project which requires millions of images to be stored for their relative rows.

I have decided to store the images on the file system and store the image's path in a separate table which has a foreign key to the row which the images belong to.

Since I will be storing millions of images, I'm a bit concerned about keeping track of these images. I will have to add a few computers/hdds to store the images. From what I have read I should be using a distributed file system. Am I correct?

Are there any best conventions I should follow before I start implementing this? I'm thinking about storing the images and giving them a UUID name, and to store the UUID as the path of the image. Then my application ill contact the rest of the path.


texts: error whilst installing prestashop via docker-compose up

texts:
Can somebody please help when I try to install a Prestashop local environment I receive this error and when I visit localhost it refuses to connect. for any more details don't hesitate to aks. thank you
021-01-31T23:29:12.526523Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-01-31T23:29:12.548265Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-01-31T23:29:12.548384Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-01-31T23:29:12.567231Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-01-31T23:29:12.567703Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-01-31T23:29:12.567730Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-01-31T23:29:12.568365Z 0 [Note] InnoDB: 5.7.33 started; log sequence number 41897827
2021-01-31T23:29:12.568546Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-01-31T23:29:12.568669Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-01-31T23:29:12.571447Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210131 23:29:12
2021-01-31T23:29:12.573313Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-01-31T23:29:12.573351Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-01-31T23:29:12.574119Z 0 [Warning] CA certificate ca.pem is self signed.
2021-01-31T23:29:12.574165Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-01-31T23:29:12.574670Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-01-31T23:29:12.574734Z 0 [Note] IPv6 is available.
2021-01-31T23:29:12.574745Z 0 [Note] - '::' resolves to '::';
2021-01-31T23:29:12.574758Z 0 [Note] Server socket created on IP: '::'.
2021-01-31T23:29:12.578313Z 0 [Warning] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2021-01-31T23:29:12.595011Z 0 [Note] Event Scheduler: Loaded 0 events
2021-01-31T23:29:12.595447Z 0 [Note] mysqld: ready for connections.
**Version: '5.7.33' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
2021-01-31T23:29:13.303581Z 2 [Note] Got an error reading communication packets


texts: CAST throwing error when run in stored procedure but not when run as raw query

texts:
Something very odd is happening here.

I have a query that looks like this.

SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000

When run as a raw query it returns data just fine.

When i place it in a stored procedure that alters the where clause it throws this error.

Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2]
The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.

So here is the oddity. I go through all the possible data for that where clause with a query like this.

SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000

and

SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000

And this is what i get back.

17
12
9
19
8
14
6
16
11
13
7
10
0
18
5
15
4

Nowhere does it have anything remotely too large for a SMALLINT. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.

I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.


texts:
I'm stuck on trying to delete some information on my MySQL database.

I've never used a database this big and when I developed this application I didn't imagine it would get so big (maybe small for you guys, but for me it's huge).

My thought was:

I have a table with "mos" Managed Objects (PK moid) (Image 1)
I have a table with "periods" (PK periodid) (Image 1)
I have a table that joins "Period" with the MO (PK moperiodid, FKs periodid, moid) (Image 1)
I have 200 tables (the number of tables is variable according to user inputs) that uses the moperiodid as FK (Image 2)
Then, when I want to clean the "historical data", I just delete cascade from MO or from PERIOD.

That was what I thought, but in my real world, it isn't happening well.

Everything works fine for Selects and Inserts, but for the first time I want to begin cleaning data, the main idea was to keep only the last X days (configured according to users input also).

For every day I have 3094555 new moperiods, even the delete of only one is not working:

0   13  22:46:19    delete from parser_customer_period_mo where id = 1  Error Code: 1205. Lock wait timeout exceeded; try restarting transaction    51.169 sec

And after every try the InnoDB Writes/Reads per Second goes crazy for 1 hour. (Image 3)

I have no idea about how I'll delete the old information, and this is the small system, I'm building the second one of 30 systems, and the second will have 12 times the amount of data.

Image 1:

Image 2:

Image 3:


texts:
I have sensitive information in multiple databases on the MySQL Server. I need to record which user have inserted or updated the record and incase of update we should be able to see the previous and the new values.

Is it possible in MySQL?


texts:
Is it possible to get the number of dead/live tuples for TOAST table as we do for normal table?

CREATE TABLE messages1 (message text);

INSERT INTO messages1
SELECT (SELECT
        string_agg(chr(floor(random() * 26)::int + 65), '')
        FROM generate_series(1,10000))
FROM generate_series(1,10);

SELECT reltoastrelid::regclass
 FROM pg_class
 WHERE relname = 'messages1';

 DELETE FROM messages1
WHERE ctid IN (
    SELECT ctid
    FROM messages1
    ORDER BY 1
    LIMIT 6
);

postgres=# SELECT reltoastrelid::regclass
postgres-#  FROM pg_class
postgres-#  WHERE relname = 'messages1';
      reltoastrelid
--------------------------
 pg_toast.pg_toast_147777
(1 row)

postgres=# select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='pg_toast.pg_toast_147777';
 relname | vacuum_count | n_tup_del | n_live_tup | n_dead_tup
---------+--------------+-----------+------------+------------
(0 rows)


texts:
I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction:

Given two transactions executing the same code (auto-commit is false):

(1)  get data of row A
(2)  write fetched data increased by 1 to row A
(3)  commit

And let's assume that the isolation-level is REPEATABLE_READ. This means that in (2) the value that gets increased by 1 needs to be the same as fetched in (1). But if the two transactions execute at the same time this might happen if there is no mutual exclusion involved:

Transaction 1 and 2:

(1) read value of 4
(2) write 5
(3) commit

They would both see 4 and then write 5 even though the result should be 6. So do I have to do locking there myself or does MySQL take care of the locking?