texts: How to determine if the error was a deadlock?

texts:
The following transaction has been selected as a victim of a deadlock:

public void side2() throws SQLException, InterruptedException {
    TestConnectionPool connectionPool = new TestConnectionPool();
    try(Connection connection = connectionPool.get()){
        connection.setAutoCommit(false);
        try {
            PreparedStatement update1 = connection.prepareStatement("update t2 set j=j-1");
            update1.execute();
            Thread.sleep(3000L);
            PreparedStatement update2 = connection.prepareStatement("update t1 set j=j-1");
            update2.execute();
            connection.commit();
        }catch (SQLException ex){
            System.out.println("ex.getErrorCode()=" + ex.getErrorCode());
            System.out.println("ex.getSQLState()=" + ex.getSQLState());
            connection.rollback();
        }
    }
}

Its output is as follows:

            /*
     ex.getErrorCode()=60
     ex.getSQLState()=61000
             */

What should I use to determine whether the error is a deadlock: error code, SQL State, or both?


texts: Syntax error in SQL Server while making Dynamic SQL

texts:
Scenario:

I have a table with 100M+ rows of data so I have created an index with something as:

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
    ON dbo.base_voter (name_voter,home_street_address_1, home_address_city)
    WHERE deleted_at IS NULL ;

Now when I execute the following code, it executed in less time:

SELECT id,
name_voter,
home_street_address_1,
home_address_city
FROM dbo.base_voter
WITH(NOLOCK)
WHERE deleted_at IS NULL
order by name_voter asc
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Now, I want to create this query as dynamic SQL as these parts name_voter asc, 0 , 50 need to be dynamic and will be sent through the backend.

But, when I try to execute

DECLARE @sql nvarchar(MAX);
DECLARE @params nvarchar(1000);

SET @sql =
     N'SELECT id, name_voter, home_street_address_1,home_address_city
       FROM dbo.base_voter
       WITH(NOLOCK)
       ORDER BY @sort_col @sort_dir
       OFFSET @offset ROWS
       FETCH NEXT @limit ROWS ONLY';
SET @params = N'@sort_col nvarchar(100),@sort_dir nvarchar(10), @offset int, @limit int';

EXECUTE sp_executesql @sql, @params,
                      'name_voter','asc', 0, 50;

I receive error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@sort_dir'.
Msg 153, Level 15, State 2, Line 9
Invalid usage of the option NEXT in the FETCH statement.

I am not sure but is it because I am passing column name as a parameter? Or is there some other thing. I just want that query to be executed while preventing from SQL injection and with minimal execution time.

I will be here if you need any further information.


texts: SQL Server Query to EXCLUDE Friday and Saturday of the week & 4PM to 8PM all the days of the week

texts:
I have a requirement to write a SQL Server Query that will fetch all the records of the table excluding Friday and Saturday of all the weeks and data between 4PM to 8PM of all the days of the week

Ex:

If I have the following records in my table

Name ProcessStartTime Status
Name1 2017-04-01 17:00:02.000 SUCCESS(Saturday)
Name2 2017-04-02 21:00:02.000 SUCCESS(Sunday)
Name3 2017-04-03 01:00:02.000 SUCCESS(Monday)
Name4 2017-04-04 05:00:02.000 SUCCESS(Tuesday)
Name5 2017-04-05 18:00:02.000 SUCCESS(Wednesday)
Name6 2017-04-05 15:00:02.000 SUCCESS(Wednesday)
Name7 2017-04-06 23:00:02.000 SUCCESS(Thursday)
Name8 2017-04-07 15:00:02.000 SUCCESS(Friday)

If I execute the Query I should get the Following records only,

Name2 2017-04-02 21:00:02.000 SUCCESS(Sunday)
Name3 2017-04-03 01:00:02.000 SUCCESS(Monday)
Name4 2017-04-04 05:00:02.000 SUCCESS(Tuesday)
Name6 2017-04-05 15:00:02.000 SUCCESS(Wednesday)
Name6 2017-04-06 23:00:02.000 SUCCESS(Thursday)

And the remaining data should not fetched because, those records are either Friday or Saturday as well as date time between 4PM to 8PM(Not only Friday and Saturday but for all the days of the week).

I have my query here below,

SELECT *
  FROM [TABLE]
  WHERE
    DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)

In this query I'm able to get the records not in Friday and Saturday but not able to neglect between 16 to 20 hours. I have written in a such way that query will fetch records between 00 to 16. but I need another condition to select between 20 to 23 hours. If I do that condition, I'm getting
0 records.

This is the further modified query that showing 0 records,

SELECT *
  FROM [TABLE]
  WHERE
    DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    AND DATEPART(hh,ProcessStartTime) > 20 AND DATEPART(hh,ProcessStartTime) <= 23
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)

Please help me on this. If needs more information let me know.


texts: Run multiple stored procedures in sequence

texts:
I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for each one except for the name of the procedure.

In an OO language I would use an abstraction such as an interface or functor and loop over a bunch of objects. That does not work in SQL, but I want to find some way to make this code more concise with less copy and paste repetition. Yes, I know that fundamentally SQL is about set operations and does not support what I want to do very well, but if there is a way, it will make the code much more concise. I also need to capture the result of each stored procedure invocation and do something with it which is not relevant to this question.

Here is what I have so far:

CREATE PROCEDURE dbo.testproc
AS BEGIN
  DECLARE @step INT, @result INT
  DECLARE @tbl TABLE([step] INT, [pname] NVARCHAR(40))

  INSERT INTO @tbl ([step], [pname]) VALUES (1, N'proc1')
  INSERT INTO @tbl ([step], [pname]) VALUES (2, N'proc2')
  INSERT INTO @tbl ([step], [pname]) VALUES (3, N'proc3')
  -- Potentially many more procedures here

  SET @step = 1
  WHILE @step <= (SELECT MAX([step]) FROM @tbl)
  BEGIN
    DECLARE @sql NVARCHAR(60)
    SET @sql = N'EXEC @result = dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC (@sql)
    IF @result <> 0
    BEGIN
      INSERT INTO SomeTable error code and step number
      RETURN
    END
   SET @step = @step + 1
  END
END
GO

When I run the procedure, SQL Server gives me an error because the @result variable that is part of the dynamic SQL is not defined as part of the batch that is contained in the @sql variable. If I modify it like this:

    SET @sql = N'EXEC dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC @result = (@sql)

I get a syntax error.

This works fine except for retrieving the return value of the subprocedures. Is there a way to accomplish my stated goal, and if so, how?

NOTE: based on what I asked here, a cursor would sound like a better implementation than a WHILE loop especially given the table variable. Part of the code that is not essential to this question involves knowing the iteration number, hence the use of a loop control variable.


texts: Upgrading SQL Server 2008 failover cluster node to SQL Server 2012 failover cluster node

texts: Please I want someone to help me with step by step method of upgrading SQL Server 2008 with failover cluster node on two different machines to SQL Server 2012 without losing the existing settings/configurations


texts:
I have a table person with attributes:

id
name
city
state
country

id is the primary key. Is this table in 3NF? If 1000 people live in same city it seems redundant to store the same data for state, city, and country.


texts:
I have a table and I am trying to apply payments to the loan amounts. Payments must be applied to initial loan until it is zero before going to next loan.

First I must say cursors are not my strong point but I gave it a try. I cannot get the cursor to apply the pmnt_amt from the DEAL_FP_CALC_BEG_BAL_AMT until it gets to zero and apply any remaining payments to the next DEAL_FP_CALC_BEG_BAL_AMT.

For example a DEAL_FP_CALC_BEG_BAL_AMT of 5000 will be paid down like this:

5000.00- 1235.3 = 3764.7
3764.7 - 1122.00 = 2642.7
2642.7 - 1035.3 = 1607.4
1607.4 - 1005.4 = 602.00
602.4 -1005.2 = (403.2) Loan paid off
(403.2) + 7500 - 1235.3(next payment) = 5861.5 until this is paid

Table is

FP_MERCH_ID  PMNT_SEQ_ID  PMNT_AMT  PMNT_DT   DEAL_FP_BAL_AMT  FUND_DT
2359         1122         1235.3    10/1/15   5000             9/1/15
2359         1123         1122      10/12/15  5000             9/1/15
2359         1124         1035.3    10/19/15  5000             9/1/15
2359         1125         1005.4    10/24/15  5000             9/1/15
2359         1126         1105.2    10/29/15  5000             9/1/15
2359         1127         1235.3    11/3/15   7500             10/31/15
2359         1128         1122      11/8/15   7500             10/31/15
2359         1129         1035.3    11/13/15  7500             10/31/15
2359         1130         1005.4    11/18/15  7500             10/31/15
2359         1131         1105.2    11/23/15  7500             10/31/15

Here is my code below.
DEAL_FP_CALC_BEG_BAL_AMT is loan amount.

--DECLARE @COUNTER INT
DECLARE @PAYMENT INT
DECLARE @BALANCE INT
--DECLARE @MAXCOUNT INT
DECLARE @RESULTS INT
DECLARE @FP_CALC_BEG_BAL_AMT INT
SET @FP_CALC_BEG_BAL_AMT = (SELECT MAX(DEAL_FP_CALC_BEG_BAL_AMT)
                        From LOOPDATA_NEW where FP_MERCH_ID = 2359
                      )

DECLARE VINTAGE CURSOR
    FOR SELECT PMNT_AMT From LOOPDATA_NEW Where FP_MERCH_ID = 2359
                        Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT

OPEN VINTAGE

    FETCH NEXT FROM VINTAGE INTO @PAYMENT

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @BALANCE = @FP_CALC_BEG_BAL_AMT - @PAYMENT
        /* begin
        set @RESULTS = @BALANCE - @PAYMENT
        end */
    PRINT @BALANCE

FETCH NEXT FROM VINTAGE INTO @PAYMENT

END
CLOSE VINTAGE
DEALLOCATE VINTAGE


texts: SQL Agent Job: Seeking Process flow advice on managing steps

texts:
Fellow DBAs,
I need some practical advice on how to solve a job timing issue on different servers. I have a SQL Agent job that currently has steps 1-10.  Step #8 has a dependency on a SQL Agent job on another box completing successfully. What is the best approach in checking to see what state this job is in?  Do I place Step #8-10 as a separate job that launches from the original job, after adding a step to the original job that queries the agent job status on the other box? If successfully completed--launches the new job? How would you suggest I manage situations where the job that is the dependency is still running? I'm open to suggestions.


texts: How to identify concurrency issues in Oracle 11g?

texts:
For a short period in time we had a concurrency issue that lead to subsequent faults in our application (Java, Spring, Hibernate).

Since we are using dictionary managed tablespaces, I first thought about too small sequence cache sizes, but we are using locally managed tablespaces. However, the top statement's sequence does have a sequence cache size of 1,000. Other frequently used sequences only have a cache size of 20.

Do you know a good starting point for further investigation?

Feel free to request more detailed statistics.

Additional Information

Begin Snap: 19-Mrz-15 22:00:28, 525, 75.8
End Snap: 19-Mrz-15 22:30:48, 695, 43.9

Top 10 Foreground Events by Total Wait Time
-------------------------------------------

| Event                                    | Waits     | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class  |
|------------------------------------------|-----------|-----------------------|--------------|-----------|-------------|
| cursor: mutex S                          | 2,966,994 | 18K                   | 6            | 18.8      | Concurrency |
| cursor: pin S wait on X                  | 5,515     | 17,1K                 | 3094         | 17.9      | Concurrency |
| library cache lock                       | 96,812    | 13,7K                 | 142          | 14.3      | Concurrency |
| latch: shared pool                       | 1,039     | 11,8K                 | 11314        | 12.3      | Concurrency |
| latch free                               | 7,105     | 7198                  | 1013         | 7.5       | Other       |
| library cache: mutex X                   | 151,055   | 6594,4                | 44           | 6.9       | Concurrency |
| log file sync                            | 463,819   | 5384,6                | 12           | 5.6       | Commit      |
| DB CPU                                   |           | 2859,4                |              | 3.0       |             |
| SGA: allocation forcing component growth | 92        | 1253,3                | 13623        | 1.3       | Other       |
| db file sequential read                  | 403,533   | 1139,1                | 3            | 1.2       | User I/O    |

Top Activity (anonymized)
-------------------------

11s4xj38uffuw | update [SESSIONDATA: no triggers, 4 b-tree indices] set [22 COLUMNS] where [ID]=:23
90wgukkg0du5c | select count(*) from [USER_SPECIFIC_DATA] where [OWNED_BY_USER, uses bindings]
d8da2d7yddqv9 | insert into [USER_SPECIFIC_DATA2]

I found out that for many tables in the top activity statements a histogram was created (HEIGHT BALANCED or FREQUENCY), possibly for adaptive cursor sharing.


texts: Virtualized SQL Server hits 100% CPU and 0 Active Memory

texts:
We recently experienced an odd crash with one of our server. We know what caused it, but we don't know why SQL Server responded the way it did.

SQL Server 2016

Here is what happened:

Friday: Changed MAXDOP from 4 to 2 without checking how much of an impact this would have on compiled queries.

Saturday Morning: Server progressively uses more CPU starting at 2 AM until it hits 100% at 5 AM and Active RAM immediately drops to 500MB from 44GB (what?!?!). Server is rebooted, all seems well.

Monday Morning: at 1:05 AM when one of the nightly batch job starts, the server hits 100% CPU and drops active memory to 500 MB by 1:15 AM.

There are a significant number of large queries that run every morning on this server as part of our nightly batch processing. Looking at the Query Store I can see they were all compiled for 4 cores before the change to MDOP on Friday.

So, all the large queries that used to have 4 cores had to be recompiled for 2, which made all of them much slower during one of the busiest time frames for this server.
I understand how this would significantly affect performance on the server, but I don't understand why the server became completely unresponsive, and had so little active memory.

Does anyone have some experience with this kind of behaviour?


texts:
Is there any way to poll for MySQL database changes using JDBC? I need exactly which changes happened (not a simple yes/no).

I tried using a thread which executes every 15 seconds to check whether my database was changed.

The query was directly executed from MySQLworkbench / cli

I need to execute java function, which is not possible in a SQL trigger.


texts:
I am really fresh in the DB world so firstly,sorry about my noob question.
I have a data warehouse project to perform in a group of 3 people.
When installing SQL Server 2008 R2 Developer in my own machine, when configuring the server, I expected that I could "use the same account for all SQL Services" in order to facilitate the access for all the group's members and I received a few errors after define username and password that block the instalation process.
Should I select Authority/SYSTEM? Or in negative case how can I define a single account without errors?

Thanks,
Francisco


texts: MSSQL - Query had to wait for memory grant

texts:
How do I go about troubleshooting an SP that times out every once in a while. When you include the actual execution plan, it says "The query had to wait for XXs for memory grant"

This does not happen on all the environments where the SP is running.

I have tried increasing the memory the on one of the machines where this occurs, it had no effect (8gb -> 16gb).

I am aware of the Memory Grant query hints, but this is not an option since the minimum requirement is SQL2012 (with no SP) and the hints were introduced in SP3.

Any guidance would be appreciated.

Query execution plan: https://www.brentozar.com/pastetheplan/?id=S1YAhUUbf


texts:
I have a Drupal 7 application running a large Mysql database. It's built on AWS instance (30GB RAM, 8 CPU). Below is the my.cnf copy.

Issue is that my application gets noticeably fast when I restart the mysql which makes me think that current configuration isn't optimal and once the caches get filled, they slow down the server speed.

Any experts who could point out the issue here?

[mysqld]
# moving datadir to /media/db ebs storage
datadir=/media/db/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/dev/shm/
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

local-infile=0
log-warnings=2

skip-external-locking
bind-address        = 127.0.0.1
expire_logs_days    = 1
max_binlog_size         = 100M
log-bin=bin.log
log-bin-index=bin-log.index
binlog_format=row
key_buffer_size                = 512M
max_allowed_packet  = 32M
thread_stack        = 292K
myisam_sort_buffer_size = 64M
thread_cache_size = 50
myisam-recover         = BACKUP
max_connections        = 100
max_user_connections = 150
table_cache            = 16384
table_open_cache       = 10240
table_definition_cache = 4096
thread_concurrency     = 8
join_buffer_size = 4M
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 200M
innodb_buffer_pool_size= 8G
tmp_table_size=64M
max_heap_table_size=64M
wait_timeout=50
interactive_timeout=50
connect_timeout=10
open-files-limit               = 65535
innodb_flush_log_at_trx_commit = 0
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
table_open_cache = 5000
innodb_log_buffer_size= 32M
innodb_log_file_size = 512m
read_rnd_buffer_size = 8M
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout = 25
innodb_write_io_threads = 16
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 24
innodb_read_io_threads = 16
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_autoinc_lock_mode = 2

[mysqldump]
max_allowed_packet  = 32M

[isamchk]
key_buffer  = 32M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
set-variable=long_query_time=1
long_query_time=10
slow_query_log_file =/var/log/mysql/mysql-slow.log


texts:
I have three entities: Document, Asset, and Client

A Document can be linked to several assets. An Asset can have many documents.

A Document can be linked to several clients. A Client can have many documents.

Is it better to have two join tables...

asset_document
    asset_id
    document_id
    ...more fields

client_document
    client_id
    document_id
    ...more fields (same fields as in asset_document)

Or just one join table?

document_relationships
    document_id
    object_id (could be a client_id or asset_id)
    type (whether it's a client or asset)
    ...more fields

My guess is that the first one is better but I can't explain why. I'm looking for a strong argument against the second one. My co-developer prefers the second one to reduce duplication and lesser tables to maintain and lesser tables to join in queries.

Take note their could be more entities where Document will be linked to but ...more fields will stay the same.


texts: Check (health) status of MySQL schema table

texts:
I have a large MySQL database which has around 1,500 tables and the file of the database is around 30GB. I would like to suggest me some ways to monitor the health of my database in overall and how i can check the health status of mysql schema table.

How can I monitor the health of my database in overall? I use mysql workbence, but since i am new to this, i don't know what to check!


texts: Connect only once for all queries in SSMS?

texts:
I have an MSSQL database on a remote server. SSMS 2012 asks me to connect every time I double click a query in "Solution Explorer" in my project despite all of them having the same connection. Also, when I open the project - it asks me for every query that's already "open".

Is it supposed to work like this? If so - Any way around this?

Another thing: When SSMS opens - it doesn't have the passwords there. When I double click a query - it does. Is it supposed to be like this?

(I asked a similar question, but had a mistake. Also, added some information here...)


texts: Adding an ID column to a join table (many-to-many) so that it can be targeted

texts:
I'm building a web application for a company that sells cars. They buy leads (personal details of people interested in buying cars) and try to turn them into customers. The company has a number of sales persons, and they want each lead to be distributed to 5 of their salespersons randomly.

However, every sales person must have his own individual copy of the lead. This is because sales persons work on a commission basis, so they need to be able to:

edit/add a lead's details without the other sales persons seeing it
manage tasks for each lead without the other sales persons seeing those tasks

The only option I see for this is to add an id (PRIMARY KEY) column to the salesperson_has_lead table, and change its salesperson_id and lead_id columns to foreign key columns.

However, I always read that it's wrong to add an ID (PRIMARY KEY) columns to a many-to-many join table. Does anyone see any flaws in the solution pictured above, or know of a better option? Any help will be very much appreciated!


texts: TRY/CATCH only reports the last (and most useless) error

texts:
(This is SQL Server 2016 SP2-CU12.)
When running this command interactively, I see the quite useful Msg 3035, Level 16, State 1.
BACKUP DATABASE @DB TO DISK = @targetfile
WITH DIFFERENTIAL, RETAINDAYS = 2, COMPRESSION,
NAME = @buname, STATS = 4

Msg 3035, Level 16, State 1, Line 4
Cannot perform a differential backup for database "ReportServer", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally

But when running it within a TRY/CATCH block, all I get is the Generic Msg 3013, Level 16, State 1.  How do I see the specific and useful message Msg 3035, Level 16, State 1?  The goal of my task is to test the ERROR_NUMBER(), ERROR_SEVERITY() and ERROR_STATE() values and programmatically perform a full backup when Msg 3035, Level 16, State 1 occurs, but not when  Msg 911, Level 16, State 11 Database 'XXX does not exist. and Msg 3201, Level 16, State 1, Line 4 Cannot open backup device 'xxxx'. Operating system error 3(The system cannot find the path specified.). errors happen.
begin try
    BACKUP DATABASE @DB TO DISK = @targetfile
    WITH DIFFERENTIAL, RETAINDAYS = 2, COMPRESSION,
    STATS = 4
end try
begin catch
    SELECT ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState
            , ERROR_MESSAGE() as ErrorMsg;
end catch

ErrorNumber ErrorSeverity ErrorState  ErrorMsg
----------- ------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3013        16            1           BACKUP DATABASE is terminating abnormally.

(1 row affected)


texts: Optimal way to save Social media posts from multiple platforms

texts:
I am working on a platform, that gathers post form multiple sources(Facebook, Twitter etc) and saves them to the database. The plan was to create one table called posts with all the necessary fields for all platforms, even the platform specific ones. So a saved Facebook post might contain an empty column called retweets(Twitter specific).

Another solution is to create a Supertype post which will contain all the general information plus the type(facebook, twitter) and have a relationship with all the platforms specific tables.

A third option is a general and platform specific table with no relationship with each other, Posts contains all the general data and the platforms specific(facebook_posts) table contains the general and the platform specific data they have no relationship with each other only with the user.

What do you think is the optimal option for performance and future proofing? We might want to add more platforms and maybe remove\changes exciting ones. The database might contain 10s of thousands of posts at some point in the future.

Option 1: All information in one table

Table name: Posts
id
title
facebook_id
retweets

Option 2: Supertype with multiple relationships

Table name: SuperPosts
id
type
title

Table name: facebook_posts
id
facebook_id
shares

Table name: twitter_posts
id
twitter_id
retweets

Option 3:  General and platform specific no relationship with each other

Table name: Posts
id
platform_id
text

Table name: facebook_posts
id
facebook_id
facebook_text
shares

Table name: twitter_posts
id
twitter_id
twitter_text
retweets


texts: How to find what prevented in getting a lock?

texts:
Sometimes my update statements fail after 3000 milliseconds with 'Lock request time out period exceeded' error. That 3000 limit is coming from my application through SET LOCK_TIMEOUT 3000 setting, which I guess is not a problem by itself.

My question is: How do I retrieve what else had already locked the same row or table that made it unavailable to this failed update?

I am able to see all the exceptions related to Error: 1222 through traces but it is not clear to me what made it fail.

Is there a way to retrieve that or do I only have to continuously watch through Activity Monitor? Since it doesn't deadlock, everything happens pretty quick there and hard to see it live.

I need to find that by using the existing traces and any information that sql server 2008 R2 keeps.

Please help.


texts: Nullable One To Many w/Join Table - Indexing

texts:
I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables

users

user_id (pk)
user_name
user_dob ...

sessions

session_id (pk)
session_date
session_duration ...

user_sessions

user_id (fk)
session_id (fk)

As you can guess one user can have many sessions, the reason (I believe) the join table was used is because a session can be added before the user exists (and it is linked to the user after they signup), thus avoiding a nullable field in the sessions table...fine.

But I have recently been looking at creating some multi-column indexes to speed up certain queries and have realised that I cannot include the user in this index as the column is in a different table.

As I am fairly new to DB design I am just wondering if the above design is correct? Or am I in fact better off using a nullable FK in the session table to allow me to index the user relationship?


texts:
Say I have the following table, where a group of 3 people from 3 locations work together on a project that is scored.

|   | Score | Possible_Score | Loc_A | Loc_B | Loc_C |
|---|-------|----------------|-------|-------|-------|
| 1 | 80    | 100            | LA    | LA    | Bos   |
| 2 | 90    | 100            | LA    | Bos   | NYC   |
| 3 | 70    | 100            | NYC   | NYC   | LA    |

I want to get overall scores by location, where each project that has at least 1 person from that location contributes to the location's score. So, in this case, I'd want an output like:

|   | Location | Score | Poss_Score |
|---|----------|-------|------------|
| 1 | Bos      | 170   | 200        |
| 2 | LA       | 240   | 300        |
| 3 | NYC      | 160   | 200        |

How can I do this in SQL? I know how to group by location, but I don't know how to group by location when it can be from any of 3 columns. Thank you for the help!


texts: Snapshot and RCSI.When does SQL Server go to VersionStore?

texts:
When SQL Server reads data with SNAPSHOT or RCSI on, how does it know it has to look a record up in the version store vs the table?

Let’s pretend session A modifies a few records of a table and escalates the row-level locks to a page-level one. At the same time another session B tries to read that page. It sees the page being under X lock but what happens afterwards? How does session B know which records on that page are modified and therefore in the version store, and which are not? 14 bytes pointers? But, to me, all the rows on the page can have them: some pointers can be relevant, some can be just outdated.

Thanks!


texts: Best practice for multiple 0,1 to many relationships

texts:
I'm an application developer without a ton of database design experience and I'm wondering if someone can suggest a design/implementation for relating one table to one or more other tables.

For example, if I have Account, Policy and Claim tables, each may have 0 or more Contacts.

I could define multiple foreign key fields in the Contacts table, but there must be a better way.

i.e.

varchar(50) contact_name
varchar(100) contact_address
bigint Account_ID
bigint Policy_ID
bigint Claim_ID


texts: When should a primary key be meaningful?

texts:
Excuse the looseness of any definitions here, but I am trying to explore a simple concept.

A Primary Key uniquely identifies a row. There are possibly other columns in a table with unique values, so they can also uniquely identify a row (Candidate Keys), but the Primary Key is the one designated for the task.

Properties which make a Primary Key more useful include:

guaranteed to be unique; some other unique column values may be duplicated as the table grows
unlikely to change; although Foreign Key constraints can include cascading, it is better not to require this
will not be recycled; a row which is deleted for some reason should not free the old PK value to be reused

For these reasons, I generally recommend that the Primary Key have no intrinsic value, so there would never be a reason to change or recycle a value. That is, it should be otherwise meaningless.

I have seen Primary Keys which include some sort of code, such as a client code based on the name. The obvious problems are (a) if the client name changes then the PK should change too, and (b) there is too much of a risk of conflict with clients with similar names.

A half exception is the use of an auto-incremented number, which has a minor meaning of a sequence number. However, it is still stable.

The question is, under what circumstances, if ever, is preferable to use a primary key with some other real meaning? That is, is there anything wrong with the advice that the PK should be arbitrary, and you can generally get by with a sequence number?


texts: Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

texts:
I'm assuming this is not possible with PK and FK...

Let's say I have tables A, B, and C.

A and B are "parents", and B and C are "children"
A is the root
B can be the child of either A or B
C can be a child of either A or B
B and C, the "children", can have one and only one "parent"

Eg. you could have this structure:

[A]━┳━[C] ┏━[C]
    ┗━[B]━╋━[C] ┏━[C]
          ┗━[B]━┻━[C]

Is there any design pattern where I could delete the root A and the delete could cascade to all the child C and B tables.

The issue I have is with the children B and C. Since they could be a child of A or B, I can't use a single FK, and adding a new FK for each possible parent is not scaleable.

I have tried creating a table P (parent) with a PK id,  so that A and B can have an FK 'id' to say that are that parent, and B and C have an FK 'pid' to say that is their parent. The issue with this is that deleting the "root" A will not cascade to its children anymore, I'd have delete P.

The general question is: how should I represent children that could have one parent OR another?

Thanks.

Additional info

This database structure will be used as a client cache and on a server which is the authority. On the server, when A is deleted all the children shall be deleted and A shall be marked with a tombstone record. When the client synchronizes with the server, the server will read the tombstones and signal (in xml) the client to delete A which shall delete all children and grand-children of A. This shall be the same processing for all root records. Similarly, the signal to delete X shall delete X which will cascade to all its children. I want to avoid special processing where I'd have to actually delete P in order to delete A and all its children.

Useful related link:

entities with two possible parent types


texts: no more data read from socket error with triggers

texts:
I have created one trigger that calls one procedure, using oracle sql developper. Here is the code for trigger :

CREATE OR REPLACE TRIGGER noteTrigger
  BEFORE
    INSERT OR
    UPDATE OF valoare OR
    DELETE
  ON note
BEGIN
  CASE
    WHEN INSERTING THEN
      DBMS_OUTPUT.PUT_LINE('Inserting');
      updateBursa();
    WHEN UPDATING('valoare') THEN
      DBMS_OUTPUT.PUT_LINE('Updating valoare');
      updateBursa();
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
      updateBursa();
  END CASE;
END;
/

Now, the procedure is written here :

  CREATE OR REPLACE PROCEDURE updateBursa IS
v_countBursieri NUMBER := 0;
BEGIN
  UPDATE STUDENTI SET bursa = null;
  FOR v_i IN (SELECT nr_matricol from studenti natural join note group by nr_matricol having avg(valoare) =
          (select max(avg(valoare)) from studenti natural join note group by nr_matricol)) LOOP
          v_countBursieri := v_countBursieri + 1;
  END LOOP;
  FOR v_i IN (SELECT nr_matricol from studenti natural join note group by nr_matricol having avg(valoare) =
          (select max(avg(valoare)) from studenti natural join note group by nr_matricol)) LOOP

          UPDATE STUDENTI SET bursa = 1000/v_countBursieri where nr_matricol = v_i.nr_matricol;
  END LOOP;

END;
/

And when I try to modify note table by this :

INSERT INTO note VALUES ('111', '25',  5, TO_DATE('20/06/2015', 'dd/mm/yyyy'));

I get error :

Error report -
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An Oracle server session was in an unrecoverable state.
*Action:   Log in to Oracle again so a new server session will be created
           automatically.  Examine the session trace file for more
           information.
Error report -
SQL Error: No more data to read from socket

Please help me! Thank you!


texts: MYSQL: weird bug with nested stored procedure

texts:
I have two stored procedures. The first one fetch language codes in my language table.

CREATE PROCEDURE `updateCategoryNameAll` (
IN categorieNumber VARCHAR (10),
IN categoryName VARCHAR (128)
)
BEGIN

DECLARE break BOOLEAN DEFAULT FALSE;
DECLARE languageCode CHAR(5);

DECLARE languageCursor CURSOR FOR
 SELECT `language_code` FROM `ps_lang` WHERE `active` = 1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;
DECLARE EXIT HANDLER FOR 1644 RESIGNAL;

OPEN languageCursor;

language_loop: LOOP
    FETCH languageCursor INTO languageCode;

    IF (break) THEN
        LEAVE language_loop;
    ELSE
        CALL updateCategoryName(categorieNumber, categoryName, languageCode);
    END IF;

END LOOP language_loop;

CLOSE languageCursor;
END//

The second procedure update the category translation based on the selected language.

Here's my problem. In the main procedure, when I'm fetching the languages everything is fine:

But, when I'm trying to fetch the language I want to work with in my nested stored procedure:

SELECT  IFNULL(
              ( SELECT  `id_category`
                    FROM  `pc_import_category`
                    WHERE  `source_category_number` = categorieNumber
                    LIMIT  1), 0
              ) AS id_category,
        IFNULL(
              ( SELECT  `id_lang`
                    FROM  `ps_lang`
                    WHERE  `language_code` = language_code
                    LIMIT  1), 0
              ) AS id_lang
        INTO idCategory, idLang;

Here's the result when I'm fetching for active language in the parent procedure:

There should be only one language (USA english), but it returns both my languages (with the good id, but the second language code should be 'fr-ca'). When I'm fetching for the other language, here's what I got.

On my second iteration (to update the french translation), here's what I got:

As a result, I find myself always updating only my english language (which is the id 1).

Is there some kind of limitation, in MySQL, with the table lock, I'm not aware of?

Thank you


texts: How to make a unique datetime without seconds?

texts:
I need to build a table accepting one row for each minute. If I set the type of primary key to datetime, 60 records can be added for each minute.

Is there any trick to design a table to keep 0000-00-00 00:00 unique?


texts: handle subquery with joins where same result is to be joined with multiple tables

texts:
I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in multiple joins.

    select tableA.aa,tableA.bb, tableK.address, tableP.address from tableA
        LEFT JOIN (
    SELECT
        tableB.ID,
        tableB.Address,
        tableD.Name,
        tableC.tableCName,
        tableC.Country
    FROM
        tableB
    INNER JOIN tableD ON tableB.tableDID = tableD.ID
    AND tableD.Deleted = 0
    INNER JOIN tableC ON tableD.tableCID = tableC.ID
    WHERE
        tableB.Deleted = 0
) AS tableP ON tableP.ID = tableA.aa
LEFT JOIN (
    SELECT
        tableB.ID,
        tableB.Address,
        tableD.Name,
        tableC.tableCName,
        tableC.Country
    FROM
        tableB
    INNER JOIN tableD ON tableB.tableDID = tableD.ID
    AND tableD.Deleted = 0
    INNER JOIN tableC ON tableD.tableCID = tableC.ID
    WHERE
        tableB.Deleted = 0
) AS tableK ON tableK.ID = tableA.bb


texts: Why is Oracle's dbms_metadata.get_ddl returning different results?

texts:
I'm trying to compare two schemas using dbms_metadata.get_ddl. The two schemas were created using exactly the same SQL scripts. However on one primary key I'm getting a difference in the DDLs:

ALTER TABLE "SCHEMA_NAME"."AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA_INDEX"  ENABLE

Vs:

  ALTER TABLE "SCHEMA_NAME"."AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "DATA_INDEX"  ENABLE

Both schemas are located on the same database instance. The script for creating this primary key, used for both schemas, is:

ALTER TABLE "AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX  TABLESPACE "DATA_INDEX" ENABLE;

What could cause the difference in result for GET_DDL, when both are created using exactly the same script?


texts: Skipped rows or rows read multiple times with NOLOCK

texts: Is it possible to stimulate a situation where rows either get skipped or get read multiple times in a select statement? E.g. the way we can cause a blocking or a deadlock...


texts:
I did an innodb force recovery. I went from 1-> and on 5 MySQL booted up. I was able to do mydqldump.
Customer is now saying some of data is wrong. (References to another table)
Is this possible?


texts: I am building a cluster of 4 nodes along with a file share witness. Every month due to server updates the  server that holds the file share will need to be rebooted. My question is while the file share becomes unavailable during that time will the cluster go down?


texts:
Good day everyone!

I want to clarify something about the behavior of master-master replication in MySQL, with respect to the logs i.e. bin-log and relay-log, it use in performing replication.

I assume that in this kind of setup, only one of the 2 db servers is processing DML operations i.e. 'active' server. Read requests are distributed to both of the servers.

So when db1 is the active server, DML operations to it are recorded in its bin-log, and db2 would have to fetch it in, placed it in its relay-log and also process it as a part of the replication process.

Question 1: Does the DML operations committed by db2 during the replication process gets included in its own bin-log?

Question 2: Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?

Question 3 What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?

The reason I am asking this because I could not get the whole scenario yet when db1 goes down and db2 becomes the active db server.

Question 4: How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?

UPDATE 1:

Question 4.1: If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?

Question 5: On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?

UPDATE 2:

Question 5.1: So you're saying that on raw INSERT queries, the actual query itself is the one written on the bin-log. So in essence, when such queries are replicated and committed on the slaves, it is the case that the same tuple would be associated with or will have, different values on its auto_incremented field, across the different databases i.e. slaves?

Example:
Insert this tuple ('Uncle Sam', 'Male', 26), on a table with auto-increment field say, pki

Then, it follows therefore from your answer that pki may have different values for this tuple across the different databases i.e. master and slaves.

Question 5.2:Assuming above is right, would not this be an issue when load-balancing read queries across databases?

Thank you very much!


texts:
I have searched the site and found numerous posts on this issue, but none really answer what I am experiencing.

Background

( I know shrinkfile is bad, but in this case, it makes sense)

Our client has an .mdf file that is 1.65 TB in size, 1.26 TB is used, and about 390 GB is available in that file.  This data file is not intended to grow much at all, and should only contain the system objects. Unfortunately there are large tables created in that file by mistake totalling about another 800 GB that need to be moved to a different file group in order to take advantage of multiple files in the correct file group and a faster disk subsystem.

I wanted to test the shrinkfile before moving the clustered indexes to a new file group so we didn't end up with 800 GB larger DB if the shrink didn't work, which is exactly what is happening.  We are doing this on a new Prod server they will be migrating to shortly, and are using SA credentials with Sysadmin rights.

No matter what commands we run to shrink the file, the command takes about 10-15 seconds to execute it successfully. No errors show up, but the file does not shrink. I have tried the below commands in SSMS, tried to use the GUI shrink file directly, scripting out the GUI commands, and straight T-SQL with no movement on the data file size. Initially I had thought that truncate option was the problem, as their might not have been free space at the end of the file.  But even after running the notruncate keyword first, then using truncate, the file still did not shrink.

The following query shows that the database file does indeed have space available in the file:

SELECT [Segment Name] = RTRIM(name)
,AllocatedMB = CAST(size / 128.0 AS DECIMAL(10, 2))
,UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2))
,AvailableMB = CAST(size / 128.0 - (FILEPROPERTY(name, 'SpaceUsed') / 128.0) AS DECIMAL(10, 2))
,PercentUsed = CAST((CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) / CAST(size / 128.0 AS DECIMAL(10, 2))) * 100 AS DECIMAL(10, 2))
FROM sysfiles
ORDER BY groupid DESC

Result:

Segment Name    AllocatedMB UsedMB  AvailableMB PercentUsed
Prod1            1657703.06 1269089.75  388613.31   76.56

ShrinkFile commands Run:

DBCC Shrinkfile (N'Prod1', 0, Truncateonly)

Result: (file did not shrink)

DbId FileId CurrentSize MinimumSize UsedPages   EstimatedPages
5       1              212185992        384         162439904       162439904

Additional ways I've tried running the shrink file:

DBCC SHRINKFILE (N'Prod1' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'Prod1' , 1396001)
DBCC SHRINKFILE (N'Prod1' , 0, NOTRUNCATE)
DBCC SHRINKFILE (N'Prod1' , 1600000) -- done to try a smaller shrink amount

Questions

I would expect if this was indeed shrinking the file by almost 400GB, SSMS would take more than 10-15 seconds to run, it would take hours before they query completes correct?
Is there some sort of data type issue that might prevent a shrink from working?  I found this article: Deleting LOB Data and Shrinking the Database and it mentions LOB data (there is LOB data in that file) but this should just take a longer time to complete, nor prevent a shrink as I see it.
Anything else I am missing here? The syntax works correctly when I try it on my laptop, albeit with my small test DB, so its not the syntax of the commands from what I can tell.


texts: Declaring a scalar variable that its a table valued parameter?

texts:
I get error 137 that I must declare a scalar variable (@facilities), although I'm declaring it in the Create stored procedure as a readonly variable, so I wasn't sure what I'm missing here. any advice will be appreciated.
I created a table valued parameter to hold multipple integer values, that I can call later in my stored procedure. the goal is to get all the documents implementing in each facility for the one division (6) for a specific date. Please see a section of the code below:

use DevDocMgt_new
CREATE TYPE dbo.Facilities AS TABLE
(FacilityID TINYINT PRIMARY KEY);
go

declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5);
go

Create PROCEDURE dbo.GetImpDocsTwo
(
@facilities dbo.Facilities ReadOnly,
@facilitydivisionID tinyint = 6)
AS
BEGIN
SET NOCOUNT ON;
SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,
        ts.TransmittalNumber, ts.TransmittalVersion,
        CASE WHEN TransmittalSiteLeadImpDate IS NOT NULL
                  AND (TransmittalSiteLeadFacility = @facilities OR TransmittalSiteLeadFacility = @facilityDivisionID)
             THEN TransmittalSiteLeadImpDate ELSE TransmittalImpDate END AS FacilityImpDate
FROM    dbo.DocumentInfo di
        INNER JOIN dbo.TransmittalSheet ts ON di.DocumentTransmittalImp = ts.TransmittalID
        LEFT OUTER JOIN dbo.TransmittalSiteLead tsl ON ts.TransmittalID = tsl.TransmittalSiteLeadTSID
        Inner Join @facilities as F on f.facilityID = tsl.TransmittalSiteLeadFacility

-- Select documents implementing in the facility
WHERE   (tsl.TransmittalSiteLeadFacility = @facilities OR
         tsl.TransmittalSiteLeadFacility = @facilitydivisionID)
end


texts: Encrypted data using MS SQL SERVER 2008 R2 ENTERPRISE EDITION. Can we decrypt data using 2008 R2 STANDARD VERSION?

texts: I have a database backup which is encrypted by using MS SQL SERVER 2008 R2 Enterprise Version. I now need to decrypt the data. My company cannot afford license for MS SQL Server Enterprise Edition as it is costly. Can we decrypt database backup on MS SQL 2008 R2 Standard version? Or any other cost effective solution is greatly appreciated.


texts:
create procedure pro3(@deptid int, @SumSalary int out) as
begin
    select @SumSalary = sum(e.salary)
    from emp e
    inner join dept d
        on e.deptid = d.deptid
    where d.deptid=@deptid;
end

declare @SumSalary int

exec pro3 @deptid = 10, @SumSalary = @SumSalary out --Error:Must declare the scalar variable "@SumSalary".

I got the below error

Must declare the scalar variable "@SumSalary".

Please help me


texts:
I'm trying to get all likes/dislikes of multiple post.
The problem can be summarized as:

posts table: id, content, user_id (fk)
users table: id, name
likes table: post_id (fk), user_id (fk), is_liked, is_disliked

To fit my use case, I need to get the list of all likes/dislikes so I've first tried this:

SELECT
    posts.id, posts.content, users.name as author
    ARRAY(SELECT user_id FROM likes WHERE is_liked and post_id = posts.id) as likes,
    ARRAY(SELECT user_id FROM likes WHERE is_disliked and post_id = posts.id) as dislikes
FROM
   posts
   INNER JOIN users ON posts.user_id = users.id

This is clearly not optimized. That's why I've tried something with another join on likes table and the array_agg function but I can't figure out how to get two different arrays for likes and dislikes.


texts: How to balance SQL Server memory with SQL Server Analysis Services memory

texts:
We have many systems that have the same configuration

One Server (virtual or physical)
Running SQL Server (SQL) and SQL Server Analysis Server (AS)
multiple cores
16gb RAM

Each night the SQL Server will do about 2-3 hours processing, followed by 2-3 hours of AS processing. Then throughout the day only the AS are queried.

Assuming this is a dedicated server, and no other apps are of concern, and that  the two sets of processing are completely synchronous - no overlaps always one after the other - how can I best set the SQL and AS server memory limits.

The reason for asking is that if I don't set a limit for SQL it will grab all the memory it can. However - my understanding is that SQL will happily relinquish this memory if:

It's not using it and
Another service/program requests it.

So from a logical perspective I believe allow SQL to take as much as it needs, but I'm not so sure about AS' TotalMemoryLimit. I'm not sure if AS will relinquish it's memory. In fact reading more leads me to believe that it is wrong to let it take it all.

Does this mean that I need actually set limits for both? I'm confused as to what the best practises should be, and what we need to be measuring considering the processes don't overlap.

Hope this makes sense.


texts: Performing projection first and then selection?

texts:
Consider a simple query:

Select username,usertype from Table1 Where usertype='Manager'

Most probably the DBMS will be performing selection first, and then projection
i.e. first pulling out rows form Table1 having usertype='Manager'

Is there a case where performing projects first, and then selection would be more
optimal or better in any means?


texts: Where can I find some guidance on index strategies?

texts:
Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.

As a general rule, which fields should be indexed?
Which fields should not be indexed?
What are the rules for using indexes while striking a balance between too many and not enough indexes in order achieve performance improvements, not degradation?


texts: MySQL database column to Store Phone numberss

texts:
What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table?

Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, AreaCode and PhoneNumber).

My application will primarily target an Indian Market and will grow to other markets as well. I prefer to have varchar columns for storing Phone Numbers.

But should I have 3 columns for Office Phone and 2 Columns for Mobile Phone? Or 3 Columns for all kinds of phone numbers with Area Code as Optional?

I have seen similar question here but no answers yet.


texts:
I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this:

update tableA
set * = tableB.*
from tableB where tableA.id = tableB.id

I tried in psql, it doesn't work. I have to list every column like this:

update tableA
set c1 = tableB.c1, c2 = tableB.c2, ...
from tableB where tableA.id = tableB.id

tableB is created use create .. like tableA. So they are basically identical. And the reason I'm doing it is that I need to load .csv data to a temp table tableB and then update tableA based on the new data in tableB. tableA needs to be locked as little as possible and tableA needs to keep integrity. I'm not sure 'delete then insert' would be a good option?


texts:
I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows:

16 characters long string
10 characters long string
blank value

Now, I want to convert blank and 10 characters long string to 0 and 16 characters string to 1. But table is quite large so I think I should avoid any experiment and pursue the best approach.

First, I thought to create and index then update the column and then change data type.


texts: SQL Server Management Studio 18 won't open (only splash screen pops up)

texts:
I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits.

Running ssms with the -log parameter reveals an error message:

  CreateInstance failed for package [Task Scheduler Package]Source:
  'mscorlib' Description: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=15.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  System.IO.FileLoadException: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=15.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0,
  Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
  ---> System.IO.FileLoadException: Could not load file or assembly 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

  WRN: Assembly binding logging is turned OFF. To enable assembly bind
  failure logging, set the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There
  is some performance penalty associated with assembly bind failure
  logging. To turn this feature off, remove the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog].

  at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly
  assembly, String name, Boolean throwOnError, Boolean ignoreCase,
  ObjectHandleOnStack type)    at
  System.Reflection.RuntimeAssembly.GetType(String name, Boolean
  throwOnError, Boolean ignoreCase)    at
  System.Activator.CreateInstance(String assemblyString, String
  typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder,
  Object[] args, CultureInfo culture, Object[] activationAttributes,
  Evidence securityInfo, StackCrawlMark& stackMark)    at
  System.Activator.CreateInstance(String assemblyName, String typeName)
  at System.AppDomain.CreateInstance(String assemblyName, String
  typeName)

  System.IO.FileLoadException: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

  WRN: Assembly binding logging is turned OFF. To enable assembly bind
  failure logging, set the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There
  is some performance penalty associated with assembly bind failure
  logging. To turn this feature off, remove the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog].

I tried running "Repair" on SSMS, and tried a repair of C:\ProgramData\Package Cache\{AAA9F15B-AF45-4562-9991-93A848D3A902}v15.0.28307.421\redist\vs2017_isoshell_for_ssms.msi, but the problem persists.


texts: Must declare the scalar variable "@VarName"

texts:
Could anyone shed some light on this error for me? To a beginner of course. This will probably be no more than a simple fix for someone else.
I'm trying to read in the Product to be used in the where clause.

alter proc Proc1
@ProductID int
as
go
select ProductName
from Products
where ProductID = @ProductID


texts: My question is how do you put my zip code 07601 as an integer? I've tried to google several times this question and never got an answer. The reason I'm doing this is to because its important information for something I need to fill out


texts: Upgrade SQL Server 2012 Express to Evaluation Edition

texts:
Is there a way to upgrade an instance of SQL Server 2012 Express to SQL Server Evaluation Enterprise?

I tried using the SQL Server Installation Center and clicking Maintenance, and then Edition Upgrade. I selected to upgrade the Express Instance to Evaluation, but I get an error on the Edition Upgrade Rules step, which says "The selected SQL Server instance does not meet upgrade matrix requirements." I also checked out the Supported Version and Upgrade Paths on Supported Version and Edition Upgrades and Express 2012 to Enterprise 2012 is supported, so it would make sense that Express 2012 to Evaluation Enterprise 2012 should be supported especially since they are both free and Express has less features than Enterprise.
Am I doing something wrong? Is there a better way to perform this upgrade? Thanks,


texts: Is storing values of a unique field shared between many web forms bad practice?

texts:
I am curious if it is considered a bad practice to use a unique table to store values asigned to a unique field that is shared between several webforms.

The site with webforms will carry thousands or hundred of concurrent visitors, I want to know performance implications of this practice.


texts: MySQL table partition truncation not to propagate on a slave

texts:
I have a table now. It has 7 seven partitions, and each day I truncate a partition(not delete, just empty the partition).

I have a use case where I would like to have a full backup of the table as well. The backup I want to keep is obviously on some other instance preferably a slave.

So in concise form, I want to know if is it possible that truncation query never runs on slave.

But other queries, runs fine.

I am open to any other better approach to achieve the same.


texts:
I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer.

The query looks like that:

SELECT c.Id
,c.NAME
,(
    SELECT TOP 1 p.NAME
    FROM ContactPerson p
    WHERE p.CustomerId = c.Id
        AND p.Priority = 1
    )
,(
    SELECT TOP 1 p.NAME
    FROM ContactPerson p
    WHERE p.CustomerId = c.Id
        AND p.Priority = 2
    )
,(
    SELECT TOP 1 p.NAME
    FROM ContactPerson p
    WHERE p.CustomerId = c.Id
        AND p.Priority = 3
    )
,(
    SELECT TOP 1 p.NAME
    FROM ContactPerson p
    WHERE p.CustomerId = c.Id
        AND p.Priority = 4
    )
,(
    SELECT TOP 1 b.AccountNumber
    FROM BankAccounts b
    WHERE b.CustomerId = c.Id
        AND b.STATUS = 'active'
    )
FROM Customers c
WHERE c.STATUS = 'active'

This is a simplified version of the query. I have multiple subqueries, often referring to the same table under different conditions. If I didn't use subqueries I would get many rows for one customer but I need just one row with all the available data.

How can I rewrite this query so it runs fast? The current version generates high CPU usage, over 200MB/s IO operations and it takes around 6 hours to collect data about 70 000 customers.


texts: Recommended replication strategy for fast global access to MySQL DB

texts:
The app i'm working on has users from all over the world and due to the nature of the app, i need to minimize all delays. My application servers are located in different data centers across the world and load balancing is in place. The load of the DB isn't that high, so that's not an issue, the issue is simply that i need to minimize delays across long distances somehow.

Currently, there's a single DB server and it's located in Europe and the issue is when my app server in South-East Asia requests data from the DB in Europe, the complete duration for the query easily increases tenfold (from 50ms to 500ms).

My initial idea is to add another DB server in Asia, what kind of replication should i set in that case? Or is this even a good way to go?

Any guidelines and suggestions for this scenario are welcome.


texts: PostgreSQL 9.6 optimizer uses index even though it's slower

texts:
I'm new to indexes and plans, so I had to ask for some help.

I have a table where I keep the following attributes:

id text NOT NULL
t timestamp NOT NULL
cost int NOT NULL

My query on the db is :

SELECT AVG(cost)
FROM my_table
WHERE my_to_char(t) = 'Sunday   '
;

The return time of the above query is about 4secs.
So, in order to increase query's performance I created an index on my_to_char(t).

But this proved to have the opposite result. The query now returns within 30 secs.

However, the query planner chooses to use the index rather than go for a sequential scan.

How can the index be slower than seq scan on the first place. Any explanation?

Maybe because there are too many rows with my_to_char(t) = 'Sunday   '?

EDIT - Query Plan

Aggregate  (cost=72841.43..72841.44 rows=1 width=32) (actual time=28383.473..28383.473 rows=1 loops=1)
Output: avg(cost)
->  Bitmap Heap Scan on my_table  (cost=900.60..72732.77 rows=43462 width=4) (actual time=120.778..28091.814 rows=1237954 loops=1)
Output: id, t, cost
Recheck Cond: (my_to_char(my_table.t) = 'Sunday   '::text)
Rows Removed by Index Recheck: 3053757
Heap Blocks: exact=33988 lossy=26432
->  Bitmap Index Scan on btree_date  (cost=0.00..889.74 rows=43462 width=0) (actual time=111.785..111.785 rows=1237954 loops=1)
Planning time: 0.270 ms
Execution time: 28384.284 ms


texts:
My left table holds more store names than the right table.  I thought a quick left join would have that dataset returned, but to no avail!

Data In From clause has the most options and I want to show all of them
Data In Left Join clause has some but not all of the options I want to show

I created the CTE to get a SUM() and pull the data into the main query
If I use the CTE to display the storeName I get 15 returned that are null instead
of the storeName(s) that exist in the From table that do not exist in the Left Join
Table

Here is query I use that is not giving results I am after.

;With DataFromOne As
(
    Select   StoreName
             ,DataFromOne = Count(ramalad)
    FROM     DataFromOne
    WHERE    EntryDate BETWEEN '20170101' AND '20170331'
    GROUP BY StoreName
)
Select   ec.StoreName
         ,[Emp Count] = COUNT(Employee)
         ,[PT Count] = COALESCE(ec.DataFromOne,0)
FROM     DataFromOne ec
LEFT OUTER JOIN DataFromTwo fji
ON       ec.StoreName = fji.StoreName
WHERE    fji.saleDate BETWEEN '20170101' AND '20170331'
GROUP BY ec.StoreName, ec.DataFromOne
ORDER BY ec.StoreName ASC;


texts: Create a view with dynamic column headers based on original table row data?

texts:
Given the two sample tables here:

Tickets Table

ID  User    Description
------------------------------------
0   James   This is a support ticket
1   Fred    This is a ticket too

Properties Table

ID  TicketID    Label           Value
---------------------------------------------------------
0   0           Engineer        Scott
1   1           Engineer        Dale
2   0           Manu            Dell
3   1           Manu            HP
4   0           OS              Windows
5   1           OS              Linux

How can I arrive at a view like this:

ID  User    Description                 Engineer    Manu    OS
---------------------------------------------------------------------
1   James   This is a support ticket    Scott       Dell    Windows
2   Fred    This is a ticket too        Dale        HP      Linux

It is important to note that the properties table would not always be the same. Some tickets may have properties that others do not.

Is this even possible?


texts: SQL server 2016 restore impact of mirroring configuration?

texts:
We have Windows Server 2016 and on it SQL server 2016 standard edition is installed. Another windows server 2016 machine has mirroring of SQL server configured.

Lets call Primary SQL server as A and Mirrored SQL server as B.

ON A we will delete DB and restore from some existing backup.

My question is do I need to recreate mirroring configuration between A & B?


texts: How to pivot multiple columns in PostgreSQL 9.5?

texts:
I have the sample data as follows and want to find if I can use crosstab function in this case.

Sample Data:

ITEM   | CD | TYPE |PARTS |  PART | CNT |
Item 1 | A  | AVG  |2     |  1    | 10  |
Item 1 | A  | AVG  |2     |  2    | 20  |
Item 1 | B  | AVG  |2     |  1    | 10  |
Item 1 | B  | AVG  |2     |  2    | 20  |
Item 1 | A  | SUM  |2     |  1    | 10  |
Item 1 | A  | SUM  |2     |  2    | 20  |
Item 1 | B  | SUM  |2     |  1    | 10  |
Item 1 | B  | SUM  |2     |  2    | 20  |

Expected Result:

ITEM   | CD | AVG_1 | SUM_1 | AVG_2 | SUM_2 |

Item 1 |  A | 10    | 10    | 20    | 20    |

Item 1 |  B | 10    | 10    | 20    | 20    |

In addition, if the parts are 3, then there will be an additional row for both AVG and SUM for A and B codes. This is dynamic and user can enter this value. How can multiple rows be flattened ?

In this requirement, Item 1 and code A has to display Part 1 Sum and Average and then Part 2 Sum and Average and this can change based on the value in Parts column.

Appreciate your help on this !!


texts: Simple SQL query but huge table - how to optimize?

texts:
I've got this very simple MySQL query:

SELECT target FROM table WHERE goal_id=1 AND year>=2015 AND year<=2020

The table has about 5 millions rows, though. And as a result, it's very slow (about 10 seconds).

What could I do to improve things? Would an index help, and if so on which column?


texts: Best way to store data with small differences for collective analysis

texts:
I have a collection of CSV files each containing data about the same topic, but with small differences. For example units carrying out a job might in one file be identified by one field, in the other by two. Others again might include a marking field for urgend jobs, others not (there it has to be created during analysis). However, the overall information is the same. One important aspect is that the data contains subsets that are identified by IDs like a job ID and data for all related tasks.

Since the collection of files is constantly growing I like to consolidate all the data in one database. This would also allow comparing the different datasets. I was looking at NoSQL DBs like Mongo DB that was recommended to me due to the differences in structure of the data. So, summarizing the situation I see two choices:

(Keep the structure) Storing the data as it is in a NoSQL DB (if possible). My concern regarding this solution is if the mentioned job / task structure can be modeled with this appropriately.
(Loose original data) Defining a structure for the data and restructuring the data before saving it in the DB. The downside of this is obviously the restructuring. Especially since I like to store the data as pure as possible to ensure analysis results are reproducible from the original data.

I am doing my analysis with R. So, my question is what is your experience with either of the two versions and do you have a recommendation.

UPDATE:
As mentioned, analysis is done with R. So the database should at least be queried from inside R in the manner that both jobs as well as related tasks need to be retrieved. Regarding analysis: These are not very complex. As mentioned the main structure of the data is such that it contains data about jobs with different tasks related to each job. So, regular analytic steps to do are:

Computing summary statistics for each job from the tasks.
Adding markers for both jobs and tasks to identify different categories.
Computing summary statistics for different categories.
Computing time differences within a task (from several timestamps), between tasks and between jobs.


texts: Quickest way to apply SPARSE on huge existing tables

texts: I have a bunch (> 40) of columns in a huge table (> 27 m records) which I am attempting to set as SPARSE. Each column takes up to 40 minutes to be altered. Is there a way to do this more quickly? Is there some metadata I can modify to quickly set these columns as SPARSE and rebuild the table?


texts:
I read about STI (Single Table Inheritance) and like the idea.

Does PostgreSQL handle 95% NULL values in some columns if I have about 1M rows well?

Does it impact performance if there many columns (~70) and many of them contain mostly NULL values?

Infos about STI:

SQLAlchemy http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html?highlight=single%20table%20inheritance#single-table-inheritance
http://martinfowler.com/eaaCatalog/singleTableInheritance.html

PS: unfortunately there is not tag for "STI" in this StackExchange site yer


texts: output psql results and errors to a file

texts:
I want to display the results of psql queries on a file.In the log just the name DO for the anonymous Bloc is being displayed.

The following is being executed from a shell script file :

psql --host=${HOST} --port=${PORT_IP} --dbname=${DATABASE_NAME} --username=${USERNAME} -f $scriptSQL -v v1=$fichierLog -v v2=$annee

and here is the content of scriptSQL :

\o :v1
\timing on
DO $$
declare
    -- programme de lancement de la purge des redevances

    -- date creation : 20/02/09

    annee       varchar(4); --  annee a purger

begin

    annee := ':v2';

  -- lancement de la fonction de purge
    SELECT pckg_activ.purgeRedevance(annee);

end $$;

\timing off
\o
\q

nothing is being displayed apart from DO.

Is the DO Anonymous Bloc the reason why nothing is displayed on v1 (the output file) ?

Any solutions ?

Thanks


texts: One to many relationship query result in a single row and dynamic multiple columns

texts:
In a one-to-many relationship query, I would have a single row and dynamic multiple columns.
In this example the expected result is to have for each owner all the associated phone numbers in a single row, starting from a multiple rows table.
The goal is to get a json ready to be imported in a MongoDB database.
SELECT o.id, p.numbers

FROM owner o
INNER JOIN phones p ON o.id = p.owner_id

WHERE o.id=1

getting the following result
o.id     p.numbers
-----    ------
1        333-555-888
1        222-777-666
1        555-657-555
1        ....

The result should be:
o.id     p.numbers1     p.numbers2     p.numbers3     p.numbers4
-----    -----------    ----------     ----------    ----------
1        333-555-888     222-777-666   555-657-555     .......

This Q & A MySQL single table static and dynamic pivot doesn't resolve the issue since "Kode 1" or "Kode 2" are the different phone numbers for me.
I am using MySQL 5.7.24. I want to get for each owner (t1) every phone number (t2) associated to him, in a single row with multiple columns. (Each phone number in a dedicated column) and they can be different.
I want to export to csv. There can be around 10 phone numbers per owner.


texts: How to clean up memory usage of a database after a big sql query

texts:
I made a select query which returned about 20 million rows.
Memory usage jumped from 300MB to 3.x GB.

How can I release the additional memory for this database after the query is done, without restarting the SQL process?

I tried:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Declare @dbid int = db_ID()
DBCC FLUSHPROCINDB (22)
DBCC FREESYSTEMCACHE ('Comments')
DBCC FREESESSIONCACHE

But it only decreases memory usage by 300MB.


texts:
I have one table t1 like this

a|b|c
-|-|-
1|1|2
1|1|3
1|1|6
1|2|3
1|2|4
1|2|7
1|3|5
2|3|2

I would like a query where the results would be similar to running multiple selects like this:

(SELECT * FROM t1  where a=1 AND b=1  LIMIT 2)
UNION ALL
(SELECT * FROM t1  where a=1 AND b=2  LIMIT 2)

....

(SELECT * FROM t1  where a=1 AND b=n  LIMIT 2)

result:

a|b|c
1|1|2
1|1|3
1|2|3
1|2|4
1|3|5

How can I accomplish that without knowing how many instances of b there are for a specific a?


texts:
I came up with:

drop table if exists idtemp;
create temp table idtemp as
      select documentid from taskflag where taskid='coref' and state = 2
         order by statechanged asc limit howmany;
create unique index on idtemp(documentid);

-- trim taskflag to the first N docs ordered by coref.
delete from taskflag where documentid not in (select documentid from idtemp) ;

This is very slow when there are 120k records in taskflag and I'm keeping 10k.

Taskflag looks like:

\d taskflag
                Table "public.taskflag"
    Column    |            Type             | Modifiers
--------------+-----------------------------+-----------
 documentid   | character varying(64)       | not null
 taskid       | character varying(64)       | not null
 state        | smallint                    |
 statechanged | timestamp without time zone |
Indexes:
    "taskflag_pkey" PRIMARY KEY, btree (documentid, taskid)
    "task_index2" btree (documentid)
    "task_index4" btree (taskid, state, statechanged)

Explain says:

                                QUERY PLAN
----------------------------------------------------------------------------------
 Delete on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
   ->  Seq Scan on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..449.00 rows=10000 width=146)
                 ->  Seq Scan on idtemp  (cost=0.00..184.00 rows=10000 width=146)
(6 rows)

Should I just arrange for the temp table to contain the ones I am keeping?


texts:
I am new to PostgreSQL and have questions regarding the backup procedure.

How do I check whether a PostgreSQL database was backed up successfully?
And how do I monitor the backup?

I have tried the following command to backup the database, but there's no output:

pg_dump [connection-option...] [option...] [dbname]


texts: Errors starting mysql after moving to a new directory

texts:
Ubuntu 18.04
mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64)

I moved my mysql installation from the default /var/lib/mysql location to an external drive, using the following:

sudo /etc/init.d/mysql stop
sudo rsync -av /var/lib/mysql /media/E
sudo mv /var/lib/mysql /var/lib/mysql.bak

and then edited the /etc/mysql/mysql.conf.d/mysqld.cnf file to point the datadir param to its new location media/E/mysql.

Attempting to restart mysql, I got:

[....] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

On the off chance, I went back and edited the log_error param within the mysql.conf.d file to point to its new location as well (as it previously pointed to the original /var/lib/mysql location).  Still wouldn't start, and left with the following output from journalctl -xe:

chris@chris-x1c6:/usr/share/mysql$ journalctl -xe
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 audit[26261]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/media/E/mysql/error.log" pid=26261 comm="mysqld" reque
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.356540Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.357247Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.357287Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1) starting as process 26261 ...
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359205Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359247Z 0 [Warning] Can't create test file /media/E/mysql/chris-x1c6.lower-test
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359818Z 0 [ERROR] Could not open file '/media/E/mysql/error.log' for error logging: Permission denied
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359828Z 0 [ERROR] Aborting
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359846Z 0 [Note] Binlog end
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: Initialization of mysqld failed: 0
Feb 29 12:36:46 chris-x1c6 mysqld[26259]: 2020-02-29T20:36:46.359874Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Feb 29 12:36:46 chris-x1c6 systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 29 12:36:46 chris-x1c6 systemd[1]: mysql.service: Failed with result 'exit-code'.
Feb 29 12:36:46 chris-x1c6 systemd[1]: Failed to start MySQL Community Server.

I've found other answers (here, here) that suggest tweaking AppArmor policy settings, but seems a little janky and wonder:

(1) if I did something wrong in moving the existing mysql installation;

(2) whether there's a solution preferable to editing the AppArmor policy file.


texts: Getting Random Sample from large database MYSQL (No Auto Inc Field)

texts:
You were so helpful with my last question, I thought id throw another one at you thats stumping me.

Basically I got a table, requirements from company was it was all supposed to be 1 table so I got myself 1000 columns, and 100million rows (... life sucks).

Anyway they want me to generate for them 500,000 row random sample of their data. Problem is my PK is a VARCHAR(50) of their ids and has no increment system to it.

My rig is not overly powerful so a ORDER BY RAND() would be very ill advised!

Currently I am working on a Stored Procedure to maybe make a temp table then export it to csv?

Its not currently done, but if I am going the wrong direction let me know!

CREATE PROCEDURE randomResults()
BEGIN
    DECLARE results CURSOR SCROLL FOR
    FOR
    SELECT * FROM massiveTable;
    DECLARE nth INTEGER;
    SET nth = 0;
    DECLARE hits INTEGER;
    SET hits = 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    CREATE TABLE tmp LIKE massiveTable;
    -- open
    OPEN results;
    -- fetch

    WHILE hits <= 500000 DO
        FETCH NEXT FROM results;
        IF (nth mod 5) THEN
            INSERT INTO
            SET hits = hits + 1;
        END IF;
        SET nth = nth + 1;
    END WHILE;
    -- end fetch
    -- close
    CLOSE results;
END$$
DELIMITER ;

I am sure you guys have a better way of doing this, was just planning to get every 5th record and put it into a tmp table then export it...

My Table:

TABLE massiveTable (

staffhashID VARCHAR(50) PRIMARY KEY,
email VARCHAR (100),
marketCat1 tinyINT(1),
...
1000 of them
) engine = MYISAM;

an example of the PK:
    433kfdgsd3gfsd232


texts: Is there a performance difference between a Dropped Index and a Disabled Index, for a NON-Clustered Index

texts:
I'm aware that there are substantial differences between Dropping and Disabling a CLUSTERED Index, but I want to know about NON-CLUSTERED indexes.
And I'm aware that Disabling an Index retains the Metadata of the index definition, making it easier and lower maintenance to reinstate the Index afterwards.
But from a straight "Performance of INSERTs, UPDATEs and DELETEs" point-of-view ...
Is there any performance difference between having Disabled a Non-Clustered Index, and having dropped it entirely. Either during the table edits, or for the CREATE vs the REBUILD afterwards?


texts: Review of my Grade Calculator Database Design

texts: I'm currently making a Grade Calculator which will be used by students of a university to calculate their own grades. I'd greatly appreciate feedback on the design I've made. Do you see any obvious flaws to it? Could I do something a better way? If you think there's room for improvement I'd be very interested in hearing and will act upon all suggestions pointed out me! I hope my Entity Relationship Diagram and its annotations is enough information to go on. Please let me know if anyone needs any clarification on anything. Thanks in advance!


texts:
I want to make SQL server connection encrypted. I know that a certificate is needed, but I haven't been able to.

So far, I:

created certificate in IIS.
added certificate in MMC "Certificates" snap-in.
in SQL Server Configuration manager for my server:

registered certificate
forced secure login
stopped & started "MSSQLSERVER" service

Starting SQL Server service always throws an error. Is there something wrong with the above steps? What are the correct steps?

I am using SQL Server 2012 and Windows Server 2012 R2.


texts: stored procedure vs. function in postgresql and which is better for executing dml statements

texts:
I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure?

My basic steps are:

RENAME TABLE FOOBAR_1234.table1_org TO FOOBAR_1234.table1_old;
DELETE FOOBAR_1234.table1_old WHERE 1=1;
RENAME TABLE FOOBAR_1234.table1 TO FOOBAR_1234.table1_org;

What I also need is the flexibility to parameterize the ID as in 1234 with any ID, example 5678.

If you have an example, that would be helpful too.


texts: Does the `hot_standby_feedback=on` may cause `SSL SYSCALL error: EOF detected` error?

texts:
On a AWS Master slave hot standby replication schema on Amazon RDS with a single master and a single slave. Both master and replica are being hosted over Amazon RDS on eu-central-1 and the master is used for reading as well. For that we use a rotating dns record that is aliased both on slave and on master database as well.

On my slave database I have the following setting as well:

hot_standby_feedback=on

I receive the following error on the replica:

SQLSTATE[HY000]: General error: 7 SSL SYSCALL error: EOF detected (SQL: ^some_query^)

Also, after further investigations I found out that the error happen both on insert and select queries as well.

And I try to debug this error. The one issue I thought was the connection capacity that may cause memory starvation. But I was proven wrong:

SHOW max_connections;

That has value 1722 and the total active connections are about ~17 (without the one that does replication) on my slave database.

Also in the memory in the slave database has no drop as well it stays in a leveled position as seen on this image:

Both master ans slave database instances are db.t3.xlarge ones. Hence, it has 4vCPUs, 16GB ram and a 100GB Storage. So being an issue or resources is out of question.

Therefore, I wonder how does the hot_standby_feedback=on may trigger this error and how? Also does the replication may cause unexpected connection termination as well because I use a single replica instead of 2 ones?

In the latter case how I can list past server-terminated connections between a time range?

The database connections are performed over a php Laravel application worker. There are 2 servers that run 4 processes each and for queuing system is used Amazon SQS.


texts: Differntial back up: what happens to existing data?

texts:
Ok, this is a question borne purely from paranoia.  But i want to ask all the same.

If I make changes to a db, and then apply a differential back up at a later date, what happens to the changes I have made?

So:

1: Take full back up.

2: Take diff.

3: Change data in the DB.

4: Apply diff to db.

EDIT:

Basically I'm copying a db, and I'm wondering if its quicker for me to reprocess some data, rather than wait for a diff.  But if the diff arrives before the processing finishes, what would happen to the data I have reprocessed when the diff is applied.

EDIT 2:

The reason for wondering is that we are moving data centers.  So i have one currently live environment, and am in the process of moving live to a new location.  We have large DBs though that are taking days to move, and im wondering if i just re process the data (days of processing).  If the diff arrives before the reprocessing is finished, i was wondering what the result of applying the diff would be (rollback to when the diff was taken?), or if we would have to restore both the full and then apply the diff, making reprocessing pointless.

(My gut tells me it will roll back to the when the diff was taken, but i want to really make sure this is right).

Cheers.


texts: Replace query result with case/when failing

texts:
Using MS SQL, I like to replace the information in the OEM_Description column when the column Item translation (IT).Description is NULL and when the language setting of the customer C.[Language Code] is English (ENG) into the original item description (I.[Description])

I have come up to this query:

SELECT
    "OEM_Description" =
CASE
   WHEN  (C.[Language Code] = 'ENG' and IT.[Description] = NULL)
   THEN I.[Description]
  ELSE IT.[Description]
END,
C.Name as Customer_Name

However the information is only being replaced if I test on "C.[Language Code] = 'ENG'" as soon as i add the test for IT.[Description] = NULL nothing is replaced anymore.

What do I wrong?


texts: getting the output of delete statement in UDF in postgres

texts:
In Postgres in a user defined function I do

execute format('delete from table where session_id = %L', id);

ran without execute postgres (may) return(s) the following:

DELETE 0

Is there any way I could get this output in the user defined function? What I am looking is something like:

execute format('delete from table where session_id = %L', id) into log;


texts: Can I have more than one db_recovery_file_dest?

texts: Can I use more than one db_recovery_file_dest? I have several partitions which could be used, but I think it is not possible to have more than one db_recovery_file_dest.


texts:
I have a really terrible set of data that I've inherited and part of my job is going through and cleaning it up to something that's actually usable.  Currently, I'm trying to update a column in my table based on the name of entry.

Table: (category is currently null for almost everything)

+-----------------------------------------------------+
| programID | programName | programVersion | category |
+-----------------------------------------------------+

My query:

update Programs
    set category="Adobe Suites"
    where programName
        in (
            select distinct programName
                from Programs
                where programName regexp '^adobe.*cs[1-9]$'
            );

The problem is, no matter whatever I try I keep getting the error:

  ERROR 1093 (HY000): You can't specify target table 'Programs' for update in FROM clause

I'm not sure what other way this could be done.  Any ideas on how to replace the from statement with something else?


texts: Why dropping all foreign keys from a script is not working?

texts:
I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import.
So I execute the below code with no syntax errors as it displays the msg that says "Alter Table [ParentTable] Drop Constraint FK_Name"... for a total of 23 keys, but yet when I refresh the Server or the DB, and Tables, I still see them when I expand the Keys node for each table? so why its not dropping them?
Also, do I need to convert it to a permanent table or put the script in a stored procedure in case if I want to run it again for testing purposes? Please advice.

I used the following script from "Swastik Mishra" posted by "Pinal Dave"
http://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

    SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema,     ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +      ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '

GO’
FROM
@table
–ADD CONSTRAINT:
SELECT
‘
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +   ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ +   ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + ']. [' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)

GO’
FROM
@table
GO


texts: how to pull data source information on data-driven subscriptions

texts:
I would like to know more about the data-driven subscriptions on our ReportServer.  I am specifically looking for what shared data sources are being used, but the connection string would also suffice.

I have poked around the Subscriptions and DataSource tables, but nothing seems to give me the information I need.  Any ideas?


texts: Upgraded Postgres; how to access now-missing databases?

texts:
My local connection on my Mac (10.9.5) wasn't working to my Postgres (9.3) database (and therefore I couldn't actually export the data), so I tried upgrading it to 9.4. Luckily I can now connect to the database, but... My old databases (and tables, views, etc.) are now missing...

Is it possible to recover these? How would I go about doing that?


texts: Trigger quest updating a column as data is entered in multiple columns at different times

texts:
What I am looking at is I have a project I'm doing and within it there is to be a column that would be turned into a GPA based off their individual assignment scores.

My question is would it be possible to utilize a trigger to get that to update this GPA column when each assignment score is entered?

If it is how would I go about doing this?

I know this would be an on update trigger I'm just clueless as to the rest of it due to the data it would be working off of is from multiple columns or will be by the end of a student's term. If more info is needed please let me know.


texts: Foreign Key as Primary Key when "converting" conceptual entity-relationship elements to logical relations

texts:
Hi guys I'm trying to implement database models using MySQL. One thing struck me when I was trying to "convert" an entity-relationship diagram (ERD) to relations (or SQL tables).

Note: Please keep in mind that a) "relationship" from the entity-relationship view and b) "relation" from the relational model are different constructs.

As far as I know, the following points are the standard when converting entity sets and relationships (as per the entity-relationship view) to relations (as per the relational model).

Entity Sets: Simply use all the attributes as columns (key attributes become the primary key).
Relationships (Many-to-Many): Use key attributes (from both entity sets) as relation attributes (i.e., table columns). So this means that when I'm trying to create a relation (table) for any relationship, I have to bring the key(s) from existing relations (tables) as a foreign key.

But when I looked up, almost everyone told me I shouldn't use a referenced foreign key as a primary key for that table (because foreign keys should allow non-unique values). This doesn't make sense because all kinds of relationship have to reference the keys from entity sets (relations).

I'm totally confused, somebody please help me!


texts: serializability ordering after one transaction finishes in postgres

texts:
Suppose, I have a table product with integer column price.

Here are two transactions that I begin at the same time (almost):

T1:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE "product"
SET price = 13;

T2:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

insert into "product" (id, price) values ('three', 10);

Now suppose I commit T2 first. And then T1.

My result after both transactions finish is:

| id  |  price
---------------
| one | 13   |
| two | 13   |
| three| 10  |

This is perplexing to me as I would have expected the serialized order to be T2 -> T1 (since T2 finished much befor) but the result set implies the serialized order is T1 -> T2.

Also, I might also have been okay with T1 getting aborted because of T2 committing.


texts: Slow query compilation, lots of reads during compilation phase

texts:
I have a query of the following form

exec sp_executesql N' UPDATE table  SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' WHERE (column5=@P1           AND column6=@P2           )',N'@P1 nvarchar(36),@P2 int',N'458986156148',87

I cannot change anything about this query itself. It comes in from an application.

Every time it is executed the update values change, causing a new query plan to be generated rather than the previous plan being reused.

Forced parametrization is on but doesn't seem to have an effect on this query, probably by the way it is executed.

When executing the query itself I see a total of 4 reads for the update. In this case execution itself didn't cause an update as there are no rows returned for the where clause.

Using the profiler I see that the time between the query coming in and starting is almost the entire time of the query (1 second). Actual execution is almost immediate.

The profiler shows 455.000 reads!! which are not shown in the query execution itself.

So now I'm wondering

Can I force the query to use the same plan here? A plan guide seems only usable for the same query or for a query that is affected by forced parametrization.
Can we increase the compilation speed of this query? Where do the 455.000 reads come from? There are a lot of statistics on that table (+- 100) but 455.000 reads seems rather excessive.

This is on SQL Server 2019, no cumulative updates yet. I scanned the CU changelog for anything that might have to do with this.

edit/ Further investigation showed that there are a lot of locks on other tables during compilation time. I have 300 tables having a foreign key referencing the primary key in my table where I want to insert.

All foreign key relationships are trusted. Would there be any way to prevent these checks during compilation phase?

edit 2/ The dependencies are no foreign key constraints but views on the table. All views using that table have the SCH-S lock on them during execution which is expected. It's not clear if this is also causing the reads...

edit 3/ Apparently the 455.000 reads are done by scanning the sys.sysmultiobjrefs system table more than a million times. This doesn't seem proper behavior.


texts: Shutting down mongod while mongodump is running

texts: Can we shutdown mongod instance while the mongodump is running? Does mongodump operate by itself or it is a client for a mongod server?


texts: Invalid object name after aliasing a table?

texts:
When I run the following query

select distinct A.movieID, A.avgStars from
(select mID as movieID, avg(stars) as avgStars
from Rating
group by mID) A
where  A.avgStars <= all(select A.avgStars from A);

I get the error "Invalid object name 'A'."

Do you know why I'm getting this?

Thanks!