texts: How to write SQL queries to populate the tables while synchronizing transactions and account balance?

texts:
I have two tables having the following schema.

CREATE TABLE users(
  `id` INT AUTO_INCREMENT,
  `balance` DECIMAL(10, 2),
  PRIMARY KEY(`id`)
);
CREATE TABLE user_transactions(
  `id` INT AUTO_INCREMENT,
  `user_id` INT,
  `previous_balance` DECIMAL(10,2) COMMENT 'Balance before transaction',
  `amount` DECIMAL(10,2) COMMENT 'It will be either positive or negative and added to the previous balance',
  `updated_balance` DECIMAL(10, 2) COMMENT 'Balance after transaction',
  `created` BIGINT,
  PRIMARY KEY(`id`)
);

I am using MySQL version 5.7. That's a minimal DB structure and indicates all the indexes going to be used. There will be more columns in both tables such as who carried out the transaction etc. I have to keep a record of the transaction in the user_transactions table and update the balance in the users table.

The transactions can be carried out by the user, the parent user, and the system. Therefore, I have to query the balance every few seconds and show it to the user. There will be no delete operations on both tables. Just insert and update on the users table, and only insert on the user_transactions table. Since there's going to be thousands of users and thousands of transactions every day, most probably at the same time, these insert and update queries should be optimized.

There are three approaches I could think of:

Run three queries in a stored procedure as follows.

CREATE PROCEDURE `update_balance` (user_id INT, amount DECIMAL(10,2))
BEGIN
    DECLARE previous_balance DECIMAL(10,2);
    -- fetch previous balance
    SELECT balance INTO previous_balance FROM users WHERE id = user_id;
    -- create an entry in user_transactions table
    INSERT INTO user_transactions(user_id, previous_balance, amount, updated_balance, created) VALUES (user_id, previous_balance, amount, previous_balance+amount, UNIX_TIMESTAMP());
    -- updating the balance in users table
    UPDATE users SET balance = previous_balance+amount WHERE id = user_id;
END

Create a trigger after updating the balance in the users table.
Don't use the balance column in the users table at all and fetch balance from the user_transactions table instead.

1st and 2nd approach will lock the row and prevent any update. It won't be feasible if there is another column in the users table being updated. 3rd approach appears not feasible to me as there's going to be a million records at some point.

This question points at writing schema. While I already have the schema, I need to write an optimized query(or queries) to populate the tables.

So, how to write queries to update balance while keeping every record of the transaction and the balance synced?


texts: Syntax for using Oracle Linked Server with USE keyword

texts:
I am working on a task where some tables from Oracle Peoplesoft have to pulled into SQL 2014. The concerned person gave me the following code and I see that in the end he mentions the comment Use /* Insert your source database here */. I am assuming the openrow set query should be used in place of the comments. Can someone please clarify if this is the way ahead. Pasting some of the code here for your reference

Create Table dbo.Z_MIGRATION_TABLE
(
 AMOUNT_TYPE nvarchar(255) NULL
,BUSINESS_UNIT nvarchar(255) NULL
,LEDGER nvarchar(255) NULL
,ACCOUNT nvarchar(255) NULL
,DESCR nvarchar(255) NULL
,DEPTID nvarchar(255) NULL
,DESCR_1 nvarchar(255) NULL
,PRODUCT nvarchar(255) NULL
,JOURNAL_DATE nvarchar(255) NULL
,JOURNAL_ID nvarchar(255) NULL
,CURRENCY_CD nvarchar(255) NULL
,MONETARY_AMOUNT float
,FOREIGN_CURRENCY nvarchar(255) NULL
,AFFILIATE nvarchar(255) NULL
,PROJECT_ID nvarchar(255) NULL
,LINE_DESCR nvarchar(255) NULL
,DESCR254 nvarchar(255) NULL
,DOC_TYPE nvarchar(255) NULL
,DOC_SEQ_NBR nvarchar(255) NULL
,JRNL_LINE_SOURCE nvarchar(255) NULL
,JRNL_LN_REF nvarchar(255) NULL
,JOURNAL_LINE float NULL
,STATISTIC_AMOUNT float
,[PRIMARY] int IDENTITY(1,1) NOT NULL
)
on [PRIMARY]
go
**Use /* insert your source database here*/**
go

Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE
(AMOUNT_TYPE
,BUSINESS_UNIT
,LEDGER
,ACCOUNT
,DESCR
,DEPTID
,DESCR_1
,PRODUCT
,JOURNAL_DATE
,JOURNAL_ID
,CURRENCY_CD
,MONETARY_AMOUNT
,FOREIGN_CURRENCY
,AFFILIATE
,PROJECT_ID
,LINE_DESCR
,DESCR254
,DOC_TYPE
,DOC_SEQ_NBR
,JRNL_LINE_SOURCE
,JRNL_LN_REF
,JOURNAL_LINE
,STATISTIC_AMOUNT
)


texts: It's possible to recover a deleted pg_hba.conf linux postgresql file?

texts:
If anyone delete the pg_hba.conf file, and don't run "pg_ctl reload", the configuration of the deleted file is still working.
With that in mind, could someone put in a new file that info wherever it is?


texts: Insert results from query, update old existing row if query doesn't still return same value

texts:
I am trying to refactor a table which contains historical data and therefore isn't ever deleted, but has an 'active' field.
Each day we run a query (a few joins, but quite slow) to get some data to insert into this table:

if the row exists already, and is present in the query, we do nothing
if the row exists but the result isn't in todays query, we set 'active' to false
if the row doesn't exist but is in the query, we insert it (with active defaulting to true)

It is possible for an entity to be active, made inactive (same row), then a few days later become active again (would be a new row).
The table is using a surrogate key, as the natural key can be repeated as noted above, though there should only ever be a single entry that is active on the natural key.
I'm curious if there is an action or 'pattern' I can take that will enable such functionality - I realise I'm asking a lot :)
I am using a java library (jOOQ) to generate the sql, so I am only looking for sql as the solution, no PL/pgSQL and no stored procs. I doubt it is possible without 2 passes (1. perform the update where not in query result, 2. perform the insert from query result) but I'm intrigued if there are possibilities.
Edit: Example table history, where ** is an update to same row
pk_surrogate | natural_id | active| date_made_active
1            | 1          | true  | 2020-01-01 -- row for entity 1 added
1 **         | 1          | false | 2020-01-01 -- row for entity 1 no longer present on 2020-01-02, so marked inactive
2            | 1          | true  | 2020-01-03 -- new row for entity 1 added when it was present on 2020-01-03
3            | 2          | true  | 2020-01-03 --new value for 2020-01-03


texts: Read Committed Snapshot, stale data reads?

texts:
I'm running SQL Azure and I have a following case.

I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction starts at the same time as W and continuously executes SELECT statements to read the table that is written by the transaction W.

Normally, the transaction R sees all the the changes immediately after W commits its changes. However, occasionally the transaction R sees the state without the most recent commit from the transaction W.

The question is:

Does the RCSI guarantee that R always see the most recent state of the table?
According to:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query. But is it guaranteed, when having two independent sessions?


texts: How to convert a MySQL database from utf8mb4 to utf8?

texts:
I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_cicollation, to utf8 and utf8_unicode_ci.

How to do that please?

Thank you in advance for your suggestions.


texts: Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

texts:
I'm using SQL Server 2008 Sp3

Lately I'm having errors in the SQL Logs:

  Error: 18059, Severity: 20, State: 1.

      The connection has been dropped because the principal that opened it subsequently
      assumed a new security context, and then tried to reset the connection under its
      impersonated security context. This scenario is not supported. See "Impersonation
      Overview" in Books Online.

I have found via Profiler the relevant originated server and application.

I searched and found someone that says that I need to change the connection pooling in the application's connection string.

Below is the relevant Web.Config file:

<?xml version="1.0"?>
<appSettings>
    <add key="Main.ConnectionString" value="Data Source=db-002\db2;Initial
Catalog=APPDB;TRUSTED_CONNECTION=YES"/>
    <add key="ConnectionHash" value="==QMr1meolHO3YWdTNFOzcTO"/>
    <add key="Database" value="[APPDB]"/>
    <add key="UseApprole" value="True"/>
</appSettings>

I changed it to the following in our test environment:

<?xml version="1.0"?>
<appSettings>
    <add key="Main.ConnectionString" value="Data Source=db-002\db2;Initial
Catalog=APPDB;TRUSTED_CONNECTION=YES;Pooling=false"/>
    <add key="ConnectionHash" value="==QMr1meolHO3YWdTNFOzcTO"/>
    <add key="Database" value="[APPDB]"/>
    <add key="UseApprole" value="True"/>
</appSettings>

I've reset the application, services and IIS. Checked the application and it works.

For some time I didn't receive these errors anymore, but after 3 hours they reverted.

Can anyone give me an idea what can I do?


texts: How do I pull a backup of an oracle database so I can send it to a vendor

texts:
First of all, I know squat about Oracle databases.

A vendor who is looking at converting our data from an older piece of software with an Oracle DB back-end wants a dump of our database to see what they need to do to bring that data over to our new software which is MS SQL Server based. I know in MS SQL Server, I right-click on the database and say backup, and I can create a backup of the database to a file and FTP that backup to the vendor. How would I do the equivalent in Oracle?

It appears like we might have version 10g of Oracle.


texts: Do all users need access to the postgres database in pg_hba.conf?

texts:
I was just testing the user creation process in pgAdmin. I have a database db1 for which I want to create a user to be able to connect to it. I want this to be the only database the user can connect to.

After creating the user, I opened my pg_hba.conf file and added a couple of lines:

host    db1 dbuser  127.0.0.1/32    md5
host    db1 dbuser  ::1/128         md5

The postgres user has access to all the databases:

host    all postgres    127.0.0.1/32    md5
host    all postgres    ::1/128         md5

When I connect to the server through pgAdmin, I get an error indicating that dbuser doesn't have access to the postgres database.

Is this normal? Should dbuser even require access to the postgres database, when I want him to only access db1? As soon as I add postgres to the list of databases for dbuser, I can connect.


texts: Select User who don't own a specific item


texts: Unicode storage of \u202b RLE and \u202c PDE in a Unicode-aware database?

texts:
I'm building a new product for toponyms and in it the Arabic shows kinda like this:

^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$

Actually not quite. This is a real problem for my ASCII-spewing terminal, so I'll make an exception and screenshot text.

My question is about those U202B "Right-To-Left Embedding" (RLE), and U202C "Pop Directional Formatting" (PDF). Do those get stored as data? My first assumption was that the characters were rendered and not in the file, but alas they are there..

360    5E03 97E6 5171 548C 56FD 000A 0009 0041 0072 0061 0062 0069 0063 0009 202B 0632    布韦共和国..Arabic..ز
.............................................................................^HERE
389    0645 0628 0627 0628 0648 064A 202C 0009 202B 064F 062C 0647 0648 0631 064A 0629    مبابوي...ُجهورية
.....................................^HERE.....^HERE
422    0020 0632 0645 0628 0627 0628 0648 064A 202C 000A 0009 004E 006F 0074 0065 0073    .زمبابوي...Notes
...............................................^HERE

When storing Arabic in a database, do you typically store \u202b, and \u202c? They seem like they're rendering characters and not technically data? I'm simply wanting to process this text to throw in a database, and wondering if these characters should be present in the database, or stripped before insert.

Background

The screenshot was taken with VIM in a terminal (Kitty) which does not support the Arabic text because all characters get displayed on a grid.
The text comes from text-extraction (using pdftotext)
The pdf was produced by the "United Nations Group of Experts on Geographical Names". You can find the pdf (E/CONF.105/13) freely available here.


texts: How to change a user's name in SQL Server 2000?

texts:
I'm not asking how to change the user's login, which is doable with sp_change_users_login, but really the user's name (which comes under the Name column of the list of users in Enterprise Manager).

Is there a stored procedure to do it? Some other command?


texts: Should I shrink this SQL Server data file?

texts:
I have read many articles describing how shrinking data files causes terrible things to happen floods, famine, fragmentation, and general mayhem.

Here is the cliff notes of my situation:

SSAS company with a central logging database for all client errors.
Recent code roll created a new bug exponentially increasing the size of the database.
Bug is fixed and the useless records have been removed, approximately 70 million.
DB used space is now down to a normal level leaving 200+ gig of white space.

I am unable to find any posts or articles describing what I will gain from reclaiming this space.

Other than backup times increasing, are there ANY negative effects from a significant amount of unused space in a data file?

Please note the following in case it may help give a more informed answer:

The drive is not in danger of filling up
Space is not needed else where on the SAN
Backup times have double in duration (both full and differential)
Our DR SAN replication has slowed slightly.


texts: How much could sequential scan gain by GPU?

texts:
I am considering hiring someone to implement GPU processing in Postgres to speed up sequential scans on a database located on a ramdisk. How much do you believe I could gain using this approach? I could buy any type of graphic card. I know that this question is really hard, but I know very little about GPU, so a very rough guess would help me a lot.

EDIT: Even though I my interest is specifically for Postgres, I could ask this question in a different way: "How large is the potential performance increase by using GPU for sequential scans on an in-memory database". If the answer is that it has a potential of 10-50 times the performance, then I can start investigating more specifically if this could be realized also in Postgres.


texts:
I have a 768M tmpfs dir that I use for mysql temp directory. It occasional fills up when running reports on large date ranges. When this happen the report fails and I get an error 28.

This actually seems acceptable to me because it prevents a query from running too long and put load on the server. (I think)

The advantage to using tmpfs is queries that use temporary tables run 50% faster.

My question is: is the tmpfs fills up while an insert or replace into is running will this fail?

Also it seems that when mysql fills the directory up the files are then deleted when the query fails. Will there ever be a case where it doesn't?

Do you think I could cause any issues using /tmp for MySQL temp dir? I have about 50 concurrent connections but not all of them are running reports at once.

What are the pros and cons of not using tmpfs?


texts: SQL Server 2005 Transactional Replication

texts:
I am in the process of replicating my companies databases from SQL Server 2005 to SQL Server 2012 using Transactional Replication.  When complete we will be running preliminary tests on the database, and if things
look good, we will replicate any data that has changed since the initial replication, and then cutover to the new servers.  The replication is from our production machine which has to be up 24/7.

All of the databases replicate perfectly except one.  This database has a System.Core assembly, due to a dependencey from another assembly , since SQLCLR in SQL Server 2005 does not natively support System.Core.

I am getting the following error from the subscriber:

CREATE ASSEMBLY failed because assembly 'System.Core' is a system assembly. Consider creating a user assembly to wrap desired functionality. (Source: MSSQLServer, Error number: 6596)

I believe that this is due to the fact that System.Core has been natively supported in SQLCLR since SQL Server 2008. This error kills all of the replication onto the subscriber.

I was under the assumption that assemblies do not replicate, but apparently they do, at least for transactional replication.

Is there any way:

1) I can stop the assemblies from being published as part of the replication,

or

2) I can prevent the attempted creation of the assembly on the subscriber

or

3) I can allow the process to continue, even if the 'CREATE ASSEMBLY' fails.

Also, a stored procedure is dependent on the assemblies.  If I prevented the stored procedure from being replicated, is it possible that this would prevent the attempted replication of the assemblies it is dependent on.  I am loathe
to try it without a definitive answer, as I do not want to use our production server to play a game of "What if?"


texts: MongoDB cannot be started after moving the server

texts:
I had a happy MongoDB 3.0.5 installed on a VM on a Windows 2012R2 Server, for light use. I moved the VM to a new physical server without any major changes, and to my surprise it cannot be started; and I can't seem to be able to find the issue on the internet.

The error I'm getting is:

2016-07-09T02:46:26.936+0430 W STORAGE  [initandlisten] database D:\mongodata MYDBNAME could not be opened due to DBException 10080: ns name too long, max size is 127 bytes
2016-07-09T02:46:26.940+0430 I STORAGE  [initandlisten] exception in initAndListen: 10080 ns name too long, max size is 127 bytes, terminating

The database name is a few characters and reasonable, and it used to work. I tried upgrading MongoDB to 3.2.3; didn't work but the error is changed:

2016-07-09T03:54:25.404+0430 I -        [initandlisten] Assertion: 10078:nsToDatabase: db too long
2016-07-09T03:54:26.117+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\util\stacktrace_windows.cpp(174) mongo::printStackTrace+0x43
2016-07-09T03:54:26.118+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\util\log.cpp(136) mongo::logContext+0xb0
2016-07-09T03:54:26.118+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\util\assert_util.cpp(222) mongo::msgasserted+0xfa
2016-07-09T03:54:26.118+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database.cpp(429) mongo::Database::getCollection+0x1ab
2016-07-09T03:54:26.119+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database.cpp(182) mongo::Database::_getOrCreateCollectionInstance+0x35
2016-07-09T03:54:26.119+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database.cpp(214) mongo::Database::Database+0x25b
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\catalog\database_holder.cpp(134) mongo::DatabaseHolder::openDb+0x380
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(337) mongo::repairDatabasesAndCheckVersion+0x517
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(559) mongo::_initAndListen+0xee4
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(659) mongo::initAndListen+0x27
2016-07-09T03:54:26.120+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(892) mongoDbMain+0x216
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten] mongod.exe    ...\src\mongo\db\db.cpp(698) wmain+0x35
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten] mongod.exe    f:\dd\vctools\crt\crtw32\dllstuff\crtexe.c(623) __tmainCRTStartup+0x10f
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten] KERNEL32.DLL BaseThreadInitThunk+0x22
2016-07-09T03:54:26.121+0430 I CONTROL  [initandlisten]
2016-07-09T03:54:26.123+0430 I STORAGE  [initandlisten] exception in initAndListen: 10078 nsToDatabase: db too long, terminating
2016-07-09T03:54:26.133+0430 I CONTROL  [initandlisten] now exiting

I tried running mongod --repair without any luck, it doesn't start at all. I can't have any logical explanations about what could have happened, since everything else on the server is fine.

Here's the exact changes made to the machine:

Host name is changed
Machine is dis-joined and then joined to the domain controller
The IP address of the network is changed

I'll be thankful to know what could have caused this, and how can I fix it.


texts: Will a primary key be added as a clustered index?

texts:
I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.

If I perform an alter table to assign a primary key will this result in SQL Server also creating a clustered index? If so should I expect slowness on the database due to IO from the data being repositioned on the harddisk?


texts:
i have 3 tables: posts, tags and post_tags

in post_tags i store post tags! and a post can have many tags

Now i want to select all tag names of post 1 while selecting post's own columns.

posts columns are: id, title
tags columns are: id, name
post_tags columns are: id, post_id, tag_id

I have reached up to here, but this returns only one tag name for post, not all tags: (any ideas ?)

select
     p.*, t.name as tag_name
from
     posts p
     inner join posts_tags pt on p.id = pt.post_id
     inner join tags t on pt.tag_id = t.id
where
     p.id = 1


texts: Which Is the Used Size Unit In DiskSpace Column at Storage Table In Zoneminder Database

texts:
Trying here, without success change the storage location of Zoneminder events and images, but having trouble with denied access of www-data user, despite have already changed both Zoneminder config and destination directory.

Now seeking here in phpMyAdmin if it could be some sort of database error, once Zoneminder error log acuses zero disk space, even with something about 400Gb free.

My tryout now is change manualy the DiskSpace column in phpMyAdmin, once it could be had been determinated during installation and now it kind of stuck.

Need to know which is the unit used in DiskSpace column, once it acuses the value of 59289474877 and Storage tab in Zoneminder shows 310.4GB of 365.68GB value.


texts: sys.dm_exec_query_stats - what causes total_worker_time to be reset? How to work around that?

texts:
I am monitoring stored procedure executions, mainly the execution times and the CPU usage, using sys.dm_exec_query_stats in the script below.

SET NOCOUNT ON;

declare @lastex datetime
select @lastex = max(datestamp) from tablebackups.[dbo].[_dm_exec_query_stats]

--Get proc counts since the starting point table creation
insert into DBA.dbo.SPStats
SELECT  bcp.datestamp as StartTime
        ,getdate() as EndTime
        ,@@Servername as 'Server'
    ,isnull(DB_NAME(st.dbid),'') DBName
    ,isnull(OBJECT_SChEMA_NAME(st.objectid,st.dbid),'') [Schema]
    ,isnull(OBJECT_NAME(st.objectid,dbid),'') StoredProcedure
      ,max(cp.usecounts)-max(bcp.usecounts) as Execution_count
      ,--case
      --when sum(qs.total_worker_time)-sum(bqs.total_worker_time) < 0
        --  then (9223372036854775807 - sum(convert(bigint,bqs.total_worker_time))+sum(convert(bigint,qs.total_worker_time)))
    --else
    sum(qs.total_worker_time)-sum(bqs.total_worker_time)
    --  end
    AS 'total_cpu_time'

      ,case max(cp.usecounts)-max(bcp.usecounts)
        when 0
            then NULL
        else
            (sum(qs.total_worker_time)-sum(bqs.total_worker_time))/(max(cp.usecounts)-max(bcp.usecounts))
        END
            as 'CPU_Per_execution'
      ,cast(convert(float,(max(cp.usecounts)-max(bcp.usecounts)))/convert(float,(datediff(s,bcp.DateStamp,getdate()))) as Numeric(18,3)) as 'Executions_Per_Second'
      ,sum(qs.Total_Logical_Reads)-sum(bqs.Total_Logical_Reads) as Total_Logical_Reads
      ,(sum(qs.Total_Logical_Reads)-sum(bqs.Total_Logical_Reads))/datediff(s,bcp.DateStamp,getdate()) as Total_Logical_Reads_per_sec
      ,sum(qs.Total_Logical_Writes)-sum(bqs.Total_Logical_Writes) as Total_Logical_Writes
      ,(sum(qs.Total_Logical_Writes)-sum(bqs.Total_Logical_Writes))/datediff(s,bcp.DateStamp,getdate()) as Total_Logical_Writes_per_sec
      ,datediff(n,bcp.DateStamp,getdate()) as 'Elapsed Time (m)'
 FROM sys.dm_exec_cached_plans cp
    inner join tablebackups.dbo._dm_exec_cached_plans bcp
        on  cp.plan_handle = bcp.Plan_handle
    join sys.dm_exec_query_stats qs
        on cp.plan_handle = qs.plan_handle
    inner join tablebackups.dbo._dm_exec_query_stats bqs
        on cp.plan_handle = bqs.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where
    cp.objtype = 'proc'
    and  qs.last_execution_time > @LastEx

group by DB_NAME(st.dbid)
        ,OBJECT_SCHEMA_NAME(objectid,st.dbid)
        ,OBJECT_NAME(objectid,st.dbid)
        ,bcp.DateStamp
order by max(cp.usecounts)-max(bcp.usecounts) desc

--Create baseline tables to get a starting count point
if object_id(N'tablebackups.dbo._dm_exec_query_stats') is not null
    drop table tablebackups.dbo._dm_exec_query_stats
if object_id(N'tablebackups.dbo._dm_exec_cached_plans') is not null
    drop table tablebackups.dbo._dm_exec_cached_plans

select getdate() as DateStamp,* into tablebackups.dbo._dm_exec_query_stats
from sys.dm_exec_query_stats
select getdate() as DateStamp,* into tablebackups.dbo._dm_exec_cached_plans
from sys.dm_exec_cached_plans

I basically saves the contents of DMVs into tables (tablebackups) so that I can compare the situation as it is now (straight from the DMVs) and the situation as it was (from the tablebackus tables).

works fine.

Only problem and hence this question:

Sometimes the total_worker_time now is less that what it used to be, for a reason I haven't identified, and therefore it gives me negative values.

I have been run this script as a stored procedure that is called by a job every 5 min.

so in the last 5 min I know how many times each procedure has been executed, that has been accurate, but the CPU usage has not been, for the reason above.

what can I do to tackle this?


texts: MySql - multi-column index using only null values from one column

texts:
I have a Innodb table with a index that looks at 3 columns.

ColA is integer and always has a value.
ColB is varchar and always has a value.
ColC is datetime and sometimes has a value (NULL).

My index appears to be looking at all records representing ColA,B,C (NULL or NOT NULL).

Is it possible to have the index only store ColC NULL values?  This would shorten the index considerably (ie 27K to 1K).


texts: querying sys.database_principals accross a linked server

texts: I'm attempting to get a listing of the users and roles they are associated with on a remote server to be input into a database on the local server.  But when I use the linked server sys.database_principals only shows me information for the login that the linked server is using.  How do I get a listing of users and roles by database to a central db location?


texts: SQL Server, how to execute a job against all databases on server?

texts:
I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan.

I have a stored procedure that I'd like to run on these databases, but I cannot create the stored procedure in these databases.

I tried experimenting with using exec sp_msforeachdb and DECLARE @command = '<my sp>' but I ran into the problem with escaping too many single quotes '.

So in the end, I'd like to say something like:
For all databases, run this block of code. Then schedule this job with SQLServerAgent.

    BEGIN
    SET NOCOUNT ON

    --DECLARE VARIABLES
    DECLARE
    @max INT,
    @min INT,
    @table_name NVARCHAR(256),
    @table_schema NVARCHAR(256),
    @sql NVARCHAR(4000)

    --DECLARE TABLE VARIABLE
    DECLARE @table TABLE(
    id INT IDENTITY(1,1) PRIMARY KEY,
    table_name NVARCHAR(256),
    table_schema NVARCHAR(256))

    --CREATE TEMP TABLE THAT STORES INFORMATION FROM SP_SPACEUSED
    IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
    BEGIN
    DROP TABLE #results
    END

    CREATE TABLE #results
    (
    [database_id] [int] NULL,
    [server_name] [nvarchar](256)  NULL, --- new, server name
    [database_name] [nvarchar](256)  NULL, --- new, db name
    [table_schema] [nvarchar](256) NULL,
    [table_name] [nvarchar](256) NULL,
    [table_rows] [int] NULL,
    [reserved_space] [nvarchar](55) NULL,
    [data_space] [nvarchar](55) NULL,
    [index_space] [nvarchar](55) NULL,
    [unused_space] [nvarchar](55) NULL
    )

    --LOOP THROUGH STATISTICS FOR EACH TABLE
    INSERT @table(table_schema, table_name)
    SELECT
    table_schema, table_name
    FROM
    information_schema.tables

    SELECT
    @min = 1,
    @max = (SELECT MAX(id) FROM @table)

    WHILE @min <= @max
    BEGIN
    SELECT
    @table_name = table_name,
    @table_schema = table_schema
    FROM
    @table
    WHERE
    id = @min

    --DYNAMIC SQL
    SELECT @sql = 'EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']''' --get dataA

    --INSERT RESULTS FROM SP_SPACEUSED TO TEMP TABLE
    INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    EXEC (@sql) --set dataA^

    --UPDATE SCHEMA NAME
    UPDATE #results
    SET table_schema = @table_schema WHERE table_name = @table_name
    UPDATE #results
    SET
    database_id = (DB_ID()),
    server_name = (select @@SERVERNAME),
    database_name = (SELECT DB_NAME() AS [Current Database]);

    SELECT @min = @min + 1
    END

    --REMOVE "KB" FROM RESULTS FOR REPORTING (GRAPH) PURPOSES
    UPDATE #results SET data_space = SUBSTRING(data_space, 1, (LEN(data_space)-3))
    UPDATE #results SET reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))
    UPDATE #results SET index_space = SUBSTRING(index_space, 1, (LEN(index_space)-3))
    UPDATE #results SET unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))

    --INSERT RESULTS INTO TABLESIZEGROWTH
    INSERT INTO TableSizes.dbo.TableSizeGrowth (database_id, server_name, database_name, table_schema, table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    SELECT * FROM #results

    DROP TABLE #results
    END


texts: Mysql - Problem speed of access on query (Low latency query sometime)

texts:
My web application is real time site that need a speed of access on every query (SELECT, UPDATE, INSERT) that must less than 200ms

Because one of my php script task is query (SELECT, UPDATE, INSERT) the work on every secound
and then loop every secound alway across web server running.

Well, On average 98% each query time is less than 0.001-0.005 secound on each query stagement. the problem is
Sometime around 3 time per hour, the query (SELECT, UPDATE, INSERT) can take up to 1s and may be 5s, so they has waiting and effect the next query loop that damage for whole business application.

i so find the solution such as row locking, turning mysql it not ensure speed of access less than 200ms, sometime it query 5s ooh!!
but the solution that i found to reducing the problem are move that from INNODB to MEMORY but sometime still a problem and the important thing are the need the recovery when system has crash.

  Example >> in some UPDATE problem that table size < 1M with 30 record and update on primary key it not sence

the question are it has many thing effect each other. HOW to slove this problem. How to start to find problem.

Do you have any idea or algorithm or if u need more some information i will give you.

thank you
so sorry about my english. i am thai


texts: Restore Postgresql database from table files

texts:
Is it possible to restore a Postgres database when only having access to the table files under $PGDATA/base/<db_oid>? The system catalogs are empty.

Background

Somebody with sudo executed an rm -Rf under /var/lib. Random files where deleted, but not the ones of the database I am interested in. Postgres would not start, even after I restored several files/directories by hand. So I created a new $PGDATA and copied the database files in, hoping that Postgres would automatically read them. Apparently, Postgres relies a lot on its system catalogs to maintain links between what is on disk and what the user sees.


texts: Reading from Postgres SQL in a Jupyter Notebook

texts:
I am trying to read data directly from Postgres to my python environment with the below:

import psycopg2 as pg
import pandas.io.sql as psql
conn = pg.connect(database="abcd",user="postgres", password="xxxx")
df = pd.read_sql('SELECT * "xyz"', conn)
df.head()

But I get the below error when reading:

  DatabaseError: Execution failed on sql 'SELECT * "xyz"': syntax error at or near ""xyz""
  LINE 1: SELECT * "trans"


texts: Filter on a window function without writing an outer SELECT statement

texts:
Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. Here is the sqlfiddle.

SELECT MERCHANTID, WAREHOUSEID, PRODUCTCODE
FROM (
  SELECT 0
  , WMP.MERCHANTID
  , WMP.WAREHOUSEID
  , WMP.PRODUCTCODE
  , RANK() OVER (PARTITION BY ML.MASTERMERCHANTID, WMP.PRODUCTCODE ORDER BY ML.PREFERENCEORDER ASC NULLS LAST) MERCH_RANK
  , RANK() OVER (PARTITION BY WMP.MERCHANTID, WMP.PRODUCTCODE ORDER BY WM.PREFERENCEORDER ASC NULLS LAST) WARE_RANK
  FROM MW_WAREHOUSEMERCHANTPRODUCT WMP
    LEFT OUTER JOIN MW_WAREHOUSEMERCHANT WM ON 0=0
                AND WMP.MERCHANTID  = WM.MERCHANTID
                AND WMP.WAREHOUSEID = WM.WAREHOUSEID
    LEFT OUTER JOIN MW_MERCHANTLINK ML ON 0=0
                AND WMP.MERCHANTID = ML.LINKEDMERCHANTID
    LEFT OUTER JOIN MW_WAREHOUSEMERCHANTPRODUCT MASTER ON 0=0
                AND ML.MASTERMERCHANTID = MASTER.MERCHANTID
                AND WMP.PRODUCTCODE     = MASTER.PRODUCTCODE
  WHERE 0=0
    AND WMP.STOCKLEVEL > 0
    AND NVL(MASTER.STOCKLEVEL, 0) <= 0
)
WHERE 0=0
  AND MERCH_RANK = 1
  AND WARE_RANK  = 1
;


texts: Multi query with group by , join them in different columns

texts:
One table has different columns for each column I want to do a group by function. I get unique values for each column in the table.

Origin Table data:

+----+--------------+--------------+
| id | customername | amazonnumber |
+----+--------------+--------------+
|  1 | Mark         |         1122 |
|  2 | Pieter       |         1122 |
|  3 | Jean         |         1122 |
|  4 | Jean         |         1122 |
|  5 | Janis        |         1123 |
|  6 | Janis        |         1123 |
+----+--------------+--------------+

At this moment i do this:

SELECT customername FROM tablegroup  group by customername;
SELECT amazonnumber FROM tablegroup  group by amazonnumber;

This is great i get for each column the unique value.

I want to combine them so i get all the data vertical in columns.
I have tried this but, this give not the right results:

SELECT * FROM
(SELECT customername FROM tablegroup  group by customername) b
 CROSS JOIN
(SELECT amazonnumber FROM tablegroup  group by amazonnumber) c;

This is the wrong result:

+---------------+--------------+
| customername  | amazonnumber |
+---------------+--------------+
| Mark          |        11222 |
| Pieter        |        11222 |
| Jean          |        11222 |
| Janis         |        11222 |
+---------------+--------------+

End Result:

+---------------+--------------+
| customername  | amazonnumber |
+---------------+--------------+
| Mark          |        11222 |
| Pieter        |        11223 |
| Jean          |              |
| Janis         |              |
+---------------+--------------+


texts: Switching replication master in cascading replication scenario

texts:
Imagine I have 3 PostgreSQL 9.3 databases (A, B, and C) set up in a cascading, streaming replication scenario (i.e. am NOT manually shipping WAL files, am having Postgres ship them over TCP internally) like so:

A --> B
      |
      V
      C

where A is the replication master for B, and B is the replication master for C.

If B fails (goes offline but doesn't failover) and I need to make C use A as replication master as quickly as possible, is it safe for me to skip rebuilding C from A and just set up A as C's replication master? I would do so by turning off the Postgres service on C, modifying the master host in the replication.conf file on C to point to A, and then starting the Postgres service on C. I've done this in practice and C replicates off of A without any data loss in my tests, but are there some cases where doing this could result in data corruption on C?

Intuitively, this should be a safe operation. Since neither B nor C do a failover, they will remain on the same timeline as A, and since the new master for C is "upstream" of the previous master, C can never be ahead of the WAL position of the new master.

Followup question (to be answered iff the previously described operation is deemed "safe"):
Imagine that I perform the above steps, B comes back online, I rebuild it from A and start binary streaming replication from A such that I now have the following scenario:

A --> B
|
V
C

where A is the replication master for both B and C.

I want to make B become C's replication master again, and like before, I want to minimize C's downtime. Can I stop the Postgres service on C, ensure that the WAL playback position on B is further along than that of C (waiting for B to catch up with C if C was "ahead" of B in WAL playback), then switch the replication master host for C to point at B in the recovery.conf file and start the Postgres service?

Once again, I've done this in practice and it seems to work, but I want to be sure that there aren't any scenarios that result in data corruption on C. In this second case, assume that replication from A to B is verified to be working correctly when Postgres on C is turned off (if replication from A to B breaks while C is still replicating from A, there can be divergence between C's and B's databases, which will obviously cause problems). Is this a safe operation?


texts:
I'm trying to transform the following relation to 3NF but can't figure it out.
The relation is (a student can only have 1 major):

STUDENT (StuID, StuName, Major), Primary Key = {StuID}

I tried the following transformation, splitting it into 2:

STUDENT (StuID, StuName)
MAJOR (StuID, Major)

But I don't think it is correct because there are still anomalies. For example, if the school decides to introduce a new Major, they can't add it unless a student has selected that major.

Can someone guide me how I should proceed with this problem.

Second try:

STUDENT (StuID, StuName, MajorID)
MAJOR (MajorID, Major)


texts: MySQL - how to use having in group by (SQL query)

texts:
Have a query like this I use to find the number of products that match a number of words in a list of words:

SELECT GROUP_CONCAT(DISTINCT w.word) AS words, COUNT(DISTINCT w.word) AS wordcount,
COUNT(*) AS productcount
FROM productwords pw JOIN words w ON pw.wordid = w.id
WHERE w.word IN ('lenovo', 'laptop', 'computer')
GROUP BY w.word HAVING COUNT(DISTINCT w.word) >= 1

The query works like it should when matching 1 word, but for some reason it doesn't return matches of 2+ words.

i.e. the output is like this even though there are lots of products that matches 2+ words:

lenovo;1;320
laptop;1;7900
computer;1;8200

Any ideas for fixing this, so it will also return the 2+ word matches?

i.e. so it returns something like this:

lenovo;1;320
laptop;1;7900
computer;1;8200
lenovo laptop;2;278
lenovo computer;2;302
laptop computer;2;3200
etc.

Tables are like this:

products (id, name)
[1..*]
productwords (id, productid, wordid)
[*..1]
words (id, word)


texts: Postgres function assign query results to multiple variables

texts:
I need to assign values to 2 variable as below in Postgres function.

a := select col1 from tbl where ...
b := select col2 from tbl where ...

How can I assign 2 values to 2 variables in one line command?

Like

a,b := select col1,col2 from tbl where ...


texts:
I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time.

Given this criteria, I'm wondering what would be good practice in setting the default fill factor for this database as well as other best practices I may want to look into applying given this criteria.

Any input would be greatly appreciated.

Thanks


texts:
I attempted to create a partial unique index for a table like example 11-3 here:

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

However, (to mimic creating the index in a production environment) I additionally used the CONCURRENTLY keyword, so:

CREATE UNIQUE INDEX CONCURRENTLY tests_success_constraint ON tests (subject, target)
    WHERE success;

If course, I understand, according to this, that creating an index concurrently takes "significantly longer to complete", however, in my test environment on a table with ~20K rows and no other users of that table, this command has now run for an hour and is still going at it and I feel that this is a bit too much (especially when looking forward to applying the command in production where there is traffic on that table).

Of course, the table contains some more columns than the example and a UNIQUE constraint against (the equivalents of) (subject, target) but it does not look like anything that should hinder the creation of a new index that much.

I see that there has been a fellow sufferer but as that case is with a huge table compared to mine, I tend to think that his problem is not applicable to me.

The only thing that I think is relevant is that the success column in my case currently consists of mostly NULLs, only a few rows that contain TRUE there. Would that be the thing that makes that much of a difference?
Or any other hidden caveats that I may have stumbled on?


texts: How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server

texts:
I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent.

However, the person who designed this had the underlying table as all varchars, when really it would be nice to have decimals for marks and char for 'A'. To this end, I thought I would make an instead of update trigger that moves 'A' values to an absences table and puts marks in the existing marks table. I can then change the data type in my marks table to decimal.

This is my trigger code:

INSTEAD OF UPDATE
AS
BEGIN

SET NOCOUNT ON;

IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = '' OR (SELECT weekReviewWk1 FROM inserted) = NULL)
BEGIN

    UPDATE assessmentData
        SET assessmentData.weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentData.studentID = inserted.studentID
    UPDATE assessmentAbsences
        SET weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentAbsences.studentID = inserted.studentID
    RETURN
END

IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = 'A')
BEGIN
    UPDATE assessmentAbsences
        SET assessmentAbsences.weekReviewWk1 = inserted.weekReviewWk1
        FROM inserted
        WHERE assessmentAbsences.studentID = inserted.studentID
    UPDATE assessmentData
        SET weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentData.studentID = inserted.studentID
    RETURN
END

BEGIN

    UPDATE assessmentData
        SET weekReviewWk1 = TRY_CONVERT(decimal(4,1), (SELECT weekReviewWk1 FROM inserted))
        FROM inserted
        WHERE assessmentData.studentID = inserted.studentID
    UPDATE assessmentAbsences
        SET weekReviewWk1 = NULL
        FROM inserted
        WHERE assessmentAbsences.studentID = inserted.studentID
END
END

The trigger works for decimal values, however I get "Error converting data type varchar to numeric" when I attempt to update the table with 'A'. The absences table has char as the type and the value should just be inserted into that, with nothing in the actual marks table, thus not violating the decimal type constraint. Why is this happening?

In addition, is there a better way to handle this scenario?


texts: Stored procedure to replace a string in an entire database

texts:
I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is:

BEGIN

declare t, c varchar(100);

declare TABLES cursor for SELECT table_name, column_name
                                FROM `INFORMATION_SCHEMA`.`COLUMNS`
                                WHERE `TABLE_SCHEMA`='MY_DATABASE';

declare exit handler for 1329 begin end;
open tables;
looping: loop
fetch tables into t,c;
SET @s1= CONCAT('UPDATE ',
          'MY_DATABASE', '.', t, ' SET ',
          c, ' = replace(', c,', \'', old, '\'', ' , ', '\'', new, '\')');

 PREPARE stmt FROM @s1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
end loop;
close tables;
END

The error when calling the procedure with any parameter is the well known:

  1064 error in syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near 'column_to_update(c) = replace(column_to_update(c), 'old' , 'new')' at line 1


texts: How to automatically backup a MySQL/Amazon RDS database?

texts:
I would like to get automatically MySQL backups to sync them to another provider as secondary backup.

The RDS Snapshots itself are not useful in this case because the are "hidden" in AWS and can only be restored in RDS.

Maybe there is already a working solution to get MySQL dumbs to S3.


texts: Inner Joins between Subsets of the Same Table

texts:
All, I am trying to figure out the syntax for doing joins between subsets of the same table.

I have:

Employee ( EmpId PK , EmpFirst, EmpLast, EmpMid, DateHired, SSN, DateBirth, Gender, PhoneNum, ReportsTo)

And I want to find , for each employee, the person they report to.

So I am thinking of using:

select empFirst, emplast, empId as Managers inner join
       (select employeeid, empfirstname, emplastname, reportsTo, from Employee) AS Staff
on Managers.employeeid= Staff.reportsTo.

But it seems I need to do something that does not make sense to me, the part between the ** s:

**select managers.employeeid, managers.empfirstname , managers.emplastname , staffmembers.emplastname , staffmembers.reportsTo **
from (select employeeid, empfirstname, emplastname from employee) AS managers
inner join
(select employeeid , empfirstname, emplastname, reports to from employee) AS Staff
on managers.employeeid = staff.reportsTo

Why do we use the part between the ** s?  I am trying to see the logic behind
the syntax, but I am having trouble getting it. Besides, this  inner join of subsets (I assume each subset is itself a table) does not seem to agree with
the usual definition of the inner join of tables.

Is it possible to just rename the table as, say Staff, keep the original one and then use a join between staff and employee?


texts:
MySql cluster is unable to restore whole dump file. Error:

  MaxNoOfConcurrentOperations and increase MaxNoOfAttributes

This is the command I tried:

mysql -u username -p create database databasename; use databasename; source /path/to/dump/file;

While restore dump sql it says increases MaxNoOfConcurrentOperations in some tables and increase MaxNoOfAttributes in some tables so out of 320 tables restore only store 50 tables, rest tables are not restore with above error.


texts: Using EXCEPT in a recursive common table expression

texts:
Why does the following query return infinite rows?  I would have expected the EXCEPT clause to terminate the recursion..

with cte as (
    select *
    from (
        values(1),(2),(3),(4),(5)
    ) v (a)
)
,r as (
    select a
    from cte
    where a in (1,2,3)
    union all
    select a
    from (
        select a
        from cte
        except
        select a
        from r
    ) x
)
select a
from r

I came across this while trying to answer a question on Stack Overflow.


texts: MySQL - can connect via "reverse engineer" but not by double-clicking the connection using the same connection info

texts:
A connection to MySQL Server on the same network fails when double-clicking the connection in MySQL Workbench (i.e. trying to connect the standard way).  It gives the error message:

"Cannot Connect to Database Server.  Your connection attempt failed for user 'xxxxxx" to the MySQL server at xxx.xxx.xxx.xxx:3306: Lost connection to MySQL server during query".

However, in the same MySQL Workbench, if I tell it to do Database -> Reverse Engineer and use that same connection that fails by double-clicking, it works just fine instantly and is able to retrieve the remote schema details.  Python also connects just fine and can run a query and display the results.  MySQL command-line client cannot connect (it just hangs).
Workbench can connect to the local MySQL instance just fine when double clicking the connection, just not the remote one.
I tried uninstalling and reinstalling Workbench as well as removing the antivirus and reverting to Windows Defender and rebooting but nothing worked.  Initially when double clicking the connection using the default settings of SSL set to "if required", I'd get an error message of:

"SSL connection error: error:00000000:lib(0):func(0):reason(0)"

But after turning SSL to "No" to eliminate that as a potential problem, then the behavior described above is what happened.
I also tried creating a new connection and even a new user account but no luck.
This is using MySQL Workbench 8.x connecting to MySQL Server 8.x.


texts:
In the below query, there are repeated calculations such as the three calls to SUM(p.amount). Does MySQL re-calculate for each function call or is there some kind of memoization optimization under the hood? If not, how can this kind of query be optimized for maximum performance?

It seems like it would be faster after the first calculation to get the next one by the alias name, total_payemnts, but that just throws an error.

SELECT LEFT(c.last_name, 1) AS 'last_names',
    SUM(p.amount) AS 'total_payments',
    COUNT(p.rental_id) AS 'num_rentals',
    SUM(p.amount) / COUNT(p.rental_id) AS 'avg_pay'
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY LEFT(c.last_name, 1)
ORDER BY SUM(p.amount) DESC;

This query runs on the MySQL Sakila sample database.


texts: unable to copy binary string from table to notepad

texts:
I have stored 391603 characters JSON document in the column, when I am trying to copy it and past in notepad full file is not coming. I can only see 43679 characters.

how to resolve this..


texts: What happens if there is missing data in the slave MySQL server

texts:
At the data center there was electricity outage. And it appears that at that time my replication stopped syncing because it said something like this; "I cannot find the next position. Last proper position was at mysqld-bin.000137 at 1234567890. What is going on?" Then I said "Ok, whatever, just start reading mysqld-bin.000138. It looks like the next file". And now, after an half hour it looks like there is no problem. I mean it replicates.

However I don't know whether there is missing data in my slave. If there is missing data what is going to happen? Eventually will the slave be synchronized if there is lots of deletes, inserts, and updates?


texts: Is OPTION (RECOMPILE) used in production environments?

texts:
Is OPTION (RECOMPILE) used in production?
This option seems to get a lot of bad press. Is it deserved?
I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of Report ETL ssis agent queries.  These queries are executed (to the best of my knowledge) sequentially and at scheduled intervals.
Back History:

SQL Server 2016
ETL Queries that cause clustered index scans when run through the ssis agent. These queries take minutes to complete and cause heavy impact.
The same query and parameter run via a local stored procedures executes in less than a second.

Wait are you certain OPTION (RECOMPILE) is the answer?

Unknown.
But I need to know whether this is a really bad idea before I try.

The risks I'm aware of:

There have been at least two serious bugs in the past related to
OPTION (RECOMPILE). Two queries run at the same time could swap
result sets. Ugh !!
https://support.microsoft.com/en-us/topic/fix-rare-possibility-of-incorrect-results-when-you-use-option-recompile-for-queries-inside-a-procedure-in-sql-server-2014-or-sql-server-2012-c247fbb5-4125-dd0f-7789-7b1c126f8241

So given the above - is this option actually used in the real world? Is it acceptable that I recommend (and test) this as an option for a production environment?

UPDATE
I was asked to provide more details. I mentioned that I do have other posts all related to this topic.  Let me give more information on that:

The root problem is that queries coming from an application server
are taking longer than 60 seconds. Normally these queries take 4 to
10 seconds. Through a lot of pain, I've determined that the time outs
line up with the ETL queries.  4 queries out of 15 to be specific.
A contributor to the problem is found within the application servers. Specifically the isolation level is set to serializable within the hibernate layer; which I have learned is not optimal for high volume production environments.

Let me share the other questions:
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?


texts: How to get return value from pl sql procedure and assign it to variable?

texts:
Code:

   execute x.procedurename(row.accc_no, row.bill_no, 0, null, row.total_balance, 'A',assgn_scen_site_cv, :assgn_scen_site_cv);

Its my procedure.

How to get return value from this proc and assign it to variable ?


texts: SQLCMD -L returns a number of servers that definitely do not have SQL installed

texts:
I have been running this command in our test domain:

sqlcmd -L

And have found that amongst all the valid results, it also returns several servers that definitely do not have SQL server installed. It is possible they may have had SQL installed on then in the distant past, but definitely not currently.

How is this possible?  I would have thought SQL Browser would have to be installed and running on a server for it to respond to the sqlcmd -L broadcast.

In case it helps, the servers that are false positives are:
-a domain controller
-a server for Microsoft SCCM
-a server for PKI management

Thanks
Mike


texts:
Recently I have searched for NOLOCK option in SQL Server. What I've discovered is that when a transaction is active on a table, SQL Server does not allow even reading from a specific table until the transaction is either committed or rolled back (as far as I understood from When should you use “with (nolock)”).

At the same time, other RDBMSes, such as PostgreSQL, allow you to read values from a row that has an active writing transaction on it. They would just give you the values that were there prior to the writing. Particularly that seems to be the behaviour with PostgreSQL MVCC (as I have learned from the Introduction to Chapter 13. Concurrency Control).

And I have this burning question. Why is there a possibility for deadlocks in SQL Server while in other RDBMSes you just get the old value before the new one is written?

NOTE: I am asking because I may have understood incorrectly.


texts:
I am trying to restore some data from mysql bin log file. But when it encounter some special characters, query gets break and I am not able to continue after that. Is there any way to handle special characters while restoring from mysql bin log.

Here are the special characters which are creating pain:

\^@A\^@l\^@\ \^@\<E2>\ \<AC>\ \^\\^@Q\^@a\^@t\^@i\^@f

and Here is the command I am using for this task:

mysqlbinlog some-bin.0016 --start-position=2812316 | mysql


texts: Can I use dbms_redefinition with bulk data transformations for better results?

texts:
Here is a situation for bulk loading in Oracle:

Schema A  { T1, T2, T3, T4}
Schema B  { TR1, TR2}

Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of data that need to be loaded into empty schema B. There is both data transformation and data mapping involved between schemas.

Would dbms_redefinition package help here? With my reading, it seems to work to redefine table within the same schema. Not sure if it works in the above situation. Also, how does this approach compare to others like CTAS.


texts: Change the Row Order to alphabetical then numeral

texts:
I have a table with a username as unique & primary key.

I ran this Query to order them in ascending order

ALTER TABLE  `table` ORDER BY  `Username` ;

So the rows are ordered like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho16793 | test1    |
| soho4595  | test2    |
| soho504   | test3    |
| soho931   | test4    |
+-----------+----------+

But I want them like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho504   | test3    |
| soho931   | test4    |
| soho4595  | test2    |
| soho16793 | test1    |
+-----------+----------+

How can I achieve this ?


texts: How to model a database for a small school portal

texts:
Good day,
My problem is how to model databases.
I most times get confused, not knowing what table to create or what will be needed.

Is there like a standard someone can take to get things working?

My current project is a simple University Staff management application,
to help the School Admin:

See the distribution of staff arranged by rank in every unit of the
university(programme, dept and faculty).
Carry out transfer of staff
Carry out promotion of staff
Carry out removal of staff
Carry out addition of staff
Answer simple queries, e.g, display professors in faculty of Arts by dept, etc.


texts: SELECT , GROUP AND DISPLAY IN TABULAR FORM

texts: I Have a table as above. How can I get an output (as shown below) with a single mysql query.


texts:
I have a table (Events) with the following columns:

ClientID, EventID, EventName, StartDate, Duration

Consider the following data:

INSERT INTO Events(ClientID, EventID, EventName, StartDate, Duration)
VALUES (1, 1, 'Login', '2016-11-27 1:30:00', 86400000),
       (2, 1, 'Login', '2016-11-27 0:30:00', 86400000),
       (3, 1, 'Login', '2016-11-27 0:00:00', 86400000)

As you can see, the Duration is in milliseconds and can span multiple days.

In the end, I would like to return the following for 2016-11-28:

EventID  EventName  Sum(Duration) Count(ClientID)
--------------------------------------------------
1        'Login'     7200000          2

The question being answered here is "How many clients were logged in on 2016-11-28 and for how many milliseconds?". 86400000 ms is 1 day. So, the first row would have 1.5 hours on 2016-11-28 (1:30 after midnight), the second would have .5 hours (0:30 after midnight), the third would have 0 hours (0:00 after midnight). Add those up, you get 2 hours or 7200000 ms and 2 clients since the 3rd did not have a duration on that day.

I have spent days toying with CASE statements to try to do this, and I really feel like there has to a better approach.

DECLARE @StartDate as DateTime, @EndDate as DateTime
SET @StartDate = '2016-11-28'
SET @EndDate = '2016-11-29'

SELECT
    EventID, EventName, COUNT(ClientID) AS CountClients,
    SUM(CAST(DATEDIFF(ms, CASE
                            WHEN StartDate < @StartDate
                               THEN @StartDate
                            ELSE StartDate
                         END,
                         CASE
                            WHEN DATEADD(ms, Duration, StartDate) >= @EndDate
                               THEN @EndDate
                               ELSE DATEADD(ms, Duration, StartDate)
                         END) AS bigint)) AS SumDuration
FROM Events
WHERE
    ((StartDate >= @StartDate AND StartDate < @EndDate)
    OR (DATEADD(ms, Duration, StartDate) >= @StartDate AND DATEADD(ms, Duration, StartDate) < @EndDate))
GROUP BY EventID, EventName

I am really struggling with how to do this efficiently, considering that I will need to get partial durations for dates that span more than a day. Any help would really be appreciated.


texts:
I am currently watching this video on Entity Framework and about 15 minutes in, the speaker says that table value functions occasionally out perform UDF's.

My question is in what scenario do TVF's out perform UDF's?


texts: How to add in carriage-return/line-feeds without starting a new row in a tabbed txt SQLite import

texts:
I'm using a tab-delimited .txt file to save my data before importing it into a SQLite database with the SQLite Manager add-on for Firefox.

I've tried a couple of ways to add carriage-return/line-feeds within my columns but failed mostly, ending up with SQLite treating them as multiple rows.

I added a carriage-return/line-feed in SQLite Manager and then looked at the format which seems to be just simple returns but copy-and-pasting that into a text file for later importation doesn't work.

How can I add carriage-return/line-feed into columns within a text file that I can later import into SQLite?


texts: Can postgres make use of both GIN and BTREE indexes in a single query?

texts:
Let's say I have GIN index on column A that allows me to filter table fast on that column by appropriate WHERE condition which would be slow without that index. However I would like also to order filtered rows by using ORDER BY statement on, let's say column B. Ordering without index is slow, so I use BTREE index on column B. The problem is that pg don't want to use both indexes - if both WHERE cond on column A and ORDER BY B are present in query, pg uses only GIN index. When I remove WHERE cond from query, then it uses BTREE index on col B.

What I can do to force pg to use both indexes, one to filter rows and second one to order them?


texts: What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics?

texts: We will get several thousands products in our database. The problem is that one product can have several different names e.g. "iPad 16GByte UMTS schwarz" or "iPad 16GB 3G black" and so on. But they are the same product. We would like to combine them together for an exact analysis (like how many products in which city in a specific period of time). What would be the best approach? What are the best experts for this?


texts: Create shared user mapping in PostgreSQL foreign data wrapper server

texts:
We are using a foreign data wrapper to query across databases on a single PostgreSQL RDS. The foreign data wrapper server needs a user mapping for each user who will query against the remote server. However, adding the user mapping for each user may prove to be error prone.
All of our users who need to query against the foreign data server have a shared role, e.g. role_name, on our PostgreSQL server.
How can we share the foreign data wrapper server user mapping between users?


texts: Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

texts:
I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this

SELECT *
FROM
  ( SELECT "people"."id", "name", "class",
    ROW_NUMBER() OVER (PARTITION BY "class" ORDER BY COUNT("violation") DESC) AS "v"
    FROM "people"
    INNER JOIN "discipline" on ("discipline"."people_id" = "people"."id")
    GROUP BY "people"."id", "name", "class"
  ) AS "v_table"
WHERE v < 3

The query seems inefficient because it sorts the count first with ORDER BY and then assigns a ROW_NUMBER. If I already have it sorted over a partition, how can I skip the ROW_NUMBER assignment & get the top 2.

Update :
Adding data tables & SQL fiddle (actually violation column is redundant, simply an entry in discipline table means that there's been a violation. If I remove it, should the Count be done on "discipline"."people_id"? like this )

Discipline                     People
-------------------------      --------------
id  people_id  violation       id  name  class
1   1          True            1   Rob   A
2   1          True            2   Jen   B
3   2          True            3   Tom   C
4   3          True            4   Ted   A
5   4          True            5   Tim   A
6   1          True            ...
7   4          True
...

Using PostgreSQL 9.3


texts: What is the best way to rebuild a date from integer inputs?

texts:
I've got a bunch of financial reports, and we want to be able to pass them two inputs (year and quarter) as variables.

I'm doing it this way, but I really don't like it:

    declare @quarter int,
    @year int,
    @date date

    set @quarter = 4
    set @year = 2018

    set @date = cast(@year as varchar(4)) + '-01-01'
    set @date = dateadd(quarter, @quarter - 1, @date)

    print @date

Question What is the best way to reconstruct a date from integer inputs?

desired result:

    2018-10-01


texts:
I have a MySQL Community database on a remote server and I am trying to get Microsoft Access to read data from it. This data is sensitive. The problem is, MySQL doesn't care about the username that I give it in the .DSN file. It lets me read the ENTIRE database no matter what! How do I get it to actually check the username that I give it in the .DSN file?

edit: I'm not a DBA, I'm some intern who is over his head. Please don't assume that I know anything about anything  :)


texts: Adding a constant IF not in an other table

texts:
So I have this query.

SELECT *
FROM `Comment`
WHERE `Target` = '".$_GET['pp']."'
AND `Id` NOT IN (SELECT `Target`
                 FROM `Delete`
                 WHERE `Type` = 'comment')
ORDER BY `Id` DESC;

This selects comments which aren't deleted and displays them to members.

How could I modify it to select everything but with an additional boolean (called Deleted)?

I've heard about IF statements in SQL but don't know whether it's appropriate here or how it works.


texts: Left join with same table making query too slow -- MySQL

texts:
We have a table server_user_disk_usage which tracks the disk usage of users from different servers.
Query is to find sum of last disk usage of each user from different servers

SELECT
m1.user_name,
sum(m1.disk_usage) as disk_usage
FROM server_user_disk_usage m1
LEFT JOIN server_details s on s.id = m1.server_id
LEFT JOIN server_user_disk_usage m2
ON (m1.server_id = m2.server_id  and m1.user_name = m2.user_name AND
m1.epoch < m2.epoch)
WHERE m2.epoch IS NULL
GROUP BY m1.user_name
order by cast(sum(m1.disk_usage) as unsigned) desc;

Response is correct but its too slow when moved to production because of huge data. Any help will be appreciated


texts: How we can we know SQL Replication components installed in SQL server

texts:
Can somebody please let me know how can we identify whether Replication components installed in SQL server.

Tried below:

1.we had changed the server name earlier so while checking with configure distributor and publication giving the below warning.

TITLE: Configure Distribution Wizard

  SQL Server is unable to connect to server 'xxxxxxxx'.

ADDITIONAL INFORMATION:

  SQL Server replication requires the actual server name to make a
  connection to the server. Specify the actual server name, 'zzzzzzzzz'.
  (Replication.Utilities)

i will rename the server.

while tried with configure subscription giving below warning.

TITLE: New Subscription Wizard

  Microsoft SQL Server Management Studio is unable to access replication
  components because replication is not installed on this instance of
  SQL Server. For information about installing replication, see the
  topic Installing Replication in SQL Server Books Online.

ADDITIONAL INFORMATION:

  Replication components are not installed on this server. Run SQL
  Server Setup again and select the option to install replication.
  (Microsoft SQL Server, Error: 21028)

Now, Please suggest whether the Replication components installed in SQL instance


texts: ms sql query multiple table colums as identical tag (sequel)

texts:
Extending this original question

I am using SQL Server Express 2017

Pretending this is my source table:

+-------------+---------------------------+
| ID | Name   | Age  | HairColor | HasCar |
+-------------+---------------------------+
| 1  | Bert   | 25   | blonde    | 1      |
| 2  | Agnes  | 61   | white     | 0      |
| 3  | Diane  | 43   | brunette  | 1      |
+-------------+---------------------------+

How can get a result looking like this?

<MyRootElement>
    <MyGroupingTag Group="1">
      <MyTag Value="1"      />
      <MyTag Value="Bert"   />
      <MyTag Value="25"     />
      <MyTag Value="blonde" />
      <MyTag Value="1"      />
    </MyGroupingTag>
    <MyGroupingTag Group="2">
      <MyTag Value="2"        />
      <MyTag Value="Agnes"    />
      <MyTag Value="61"       />
      <MyTag Value="white"    />
      <MyTag Value="0"        />
    </MyGroupingTag>
    <MyGroupingTag Group="3">
      <MyTag Value="3"         />
      <MyTag Value="Diane"     />
      <MyTag Value="43"        />
      <MyTag Value="brunette"  />
      <MyTag Value="1"         />
    </MyGroupingTag>
</MyRootElement>

Because of the Group attribute in MyGroupingTag I can't use something like

SELECT... FOR XML PATH('MyTag'), ROOT('MyGroupingTag')

The Group itself is a simple row count.


texts: I'm going to drop a database. I want to monitor if any user is still using the database for a couple of months. Besides profiler, is there a better way to monitoring activities on a database?


texts: Stored procedure with recompile causing CPU spike

texts:
There is a heavily queried stored procedure that uses a bad plan once in few days. As the number of execution grows, one of the bad plans causes a 100% CPU spike. In order to mitigate this, we have changed the procedure to do a recompile every time it executes. Now, the CPU usage stayed beyond 90% constant.

What is a better way to deal with this problem?
How would I do a rollback for an immediate solution?


texts: Enforcing lowest transaction isolation level across concurrent sessions?

texts:
I am trying to learn transactions better in PostgreSQL and came across this observation.

I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am using CREATE TABLE *table_name* AS *sql_stmt*  (CTAS) syntax. I would consider this a safe operation to execute concurrently (across my multiple sessions) because it is not updating any data, thus I would want to ensure that I have the least restrictive lock possible at all times.

That being said, multiple sessions are still creating data (the table) so I am quite worried that if the SQL statements contain references to the same table, I might be invoking a lock, even if I know that it is safe for the operations I have in mind. In other words, I know that all my operations are read-only (so can use a lock that is not restrictive) and that no table will have data which is updated, with the simple caveat that I am also saving the result as a table (so this is no longer 100% read-only; might invoke some restrictive lock at some point?). Thus, my question is as follows: how can I ensure the lowest lock restriction across all concurrent sessions while I do my CTAS workload?

For an example, let us say I have two sessions. One is issuing the following command:

CREATE TABLE t1 AS SELECT * FROM facttable;

The other, concurrently issues:

CREATE TABLE t2 AS SELECT * FROM facttable;

How can I ensure that such operations do not invoke a lock since they are referring to the same table?


texts: Regular expression in a SQL case statement

texts:
I have records formatted as such:

CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

What I am trying to accomplish with a REGEX case statement in Oracle SQL is to have a field in a view that lists the opening alphas in one column, the following digits in a second column, and the two concatenated in a third column.  The beginning of the record will have two or three alphas followed possibly by a blank space, a /, a -, or a digit.  How can I separate these out?  I've tried:

  case
  when subj_no like '%' then
     regexp_substr(subj_no, '^[A-Z]+') subj_no
  end subj_no,

The end result for the given data should be:

COL 1           COL 2    COL 3

CBS405 505      CBS      405 505
BR404 504 604   BR       404 504 604
AP490           AP       490
APR501          APR      501
MP300           MP       300
ACX501          ACX      501
ACX400          ACX      400

How can I use a regular expression in a case statement to arrive at that output?


texts:
Hope the question isn't too vague.

I have a user who has a HOSTED web application that helps run his day to day business. The application is very useful to the client and his customers, as they can also see their data (live).

The user is concerned about potential down time (if the host goes down or he gets Internet issues) - if this happens he is unable to work.

The ideal situation (in my head at least) is I create a local version of the application for him, and the local database syncs to the HOSTED database after every change he makes (or after every minute on a timer) - this would guarantee his customers see their data live; and heaven forbid, if he loses connectivity, he can continue working.

I've downloaded SQLYog, and the sync tool in that works perfectly - but I'd like something automatic and unobtrusive. Is there any tool that manages this out there?


texts: Unable to drop the table since its showing waiting for meta data lock

texts: We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on that particular table. Does anybody know how to resolve it?


texts: Postgres list constraints that use a stored procedure

texts:
I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.

something like:

CREATE TABLE payment (
  amount bigint,
  CONSTRAINT amount_gt_0 CHECK (round_amount(amount) > 0)
);

where round_amount is user-defined function.

Is it possible to get a list of all constraint or at least tables that use such constraints?

postgresql version is 9.6


texts: mongodb heartbeat - unicast, broadcast or multicast?

texts:
I can't find any good doc or article (except http://docs.mongodb.org/manual/core/replication-internals/) about this in the internet. Anyone knows how mongodb heart beat signal operates?

The reason this question is asked is because I need to find out the bandwidth that mongodb uses for its operation, so we can calculate the optimum network bandwidth if we have a cluster of 3 to 5 instances.

Bottom line, is this a necessary point to consider when mongodb are used in production?


texts:
I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool.  I've setup a database link back to itself and the link works fine.  Then I'm running this:

impdp username/password@instancename schemas=TARGET_SCHEMA
      network_link=LINK_BACK_TO_TARGET
      directory=HOME_DIR logfile=IMPDP_COPY_BACKUP.log
      remap_schema=TARGET_SCHEMA:SOURCE_SCHEMA
      TRANSFORM=STORAGE:n:table;

Everything works fine in my two tests systems, but in Oracle 11gR2 production, it gets to the moving of tables and produces this:

Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in
    KUPW$WORKER.UPDATE_TD_BASE_PO_INFO [UPDATE
    "ADMIN"."SYS_IMPORT_SCHEMA_04" mtu SET
    mtu.base_process_order = NVL((SELECT mts1.process_order
    FROM "ADMIN"."SYS_IMPORT_SCHEMA_04" mts1 WHERE
    mts1.process_order > 0 AND mts1.duplicate = 0
    AND mts1.object_schema = mtu.base_object_schema
    AND mts1.object_name = mtu.base_object_name AND
    mts1.object_type = mtu.base_object_type AND
    mts1.processing_state != :1 ),
    (-1000 - (SELECT MIN(mts2.process_order) FROM
    "ADMIN"."SYS_IMPORT_SCHEMA_04" mts2 WHERE
    mts2.process_order > 0 AND mts2.duplicate = 0
    AND mts2.object_schema = mtu.base_object_schema
    AND mts2.object_name = mtu.base_object_name
    AND mts2.object_type = mtu.object_type)))
    WHERE mtu.process_order > 0 AND mtu.duplicate = 0
    AND mtu.object_type = 'TABLE_DATA'
    AND mtu.processing_status = :2]
ORA-01427: single-row subquery returns more than one row

I've tried it a few times, but I'm clueless whats going on.

I'm also open to other ways to clone a schema.


texts: Just use WSFC without Always On Failover Cluster Instance?

texts:
There was some unanswered questions about the difference between "Windows Server Failover Clustering" and "Always On Failover Cluster Instance".

From MS document:

A Windows Server Failover Clustering (WSFC) cluster is a group of independent servers that work together to increase the availability of applications and services. SQL Server 2017 takes advantage of WSFC services and capabilities to support Always On availability groups and SQL Server Failover Cluster Instances.

So we have 3 terms

Windows Server Failover Clustering
Always On availability groups
SQL Server Failover Cluster Instances

Does that mean i have to combine 1 with 2 or 3? Or i can only use 1 only?

Because in my case (2 EC2 instance with SQL Server 2016 Standard), here's what i did

On first EC2, i installed Failover Clustering Manager role from Server Manager
Then i created cluster and add nodes
I did some other configurations including DNS, network, witness
Now when one node go off (by stop EC2), the other node go online
In SQL Management, i just configured Merge Replication between 2 SQL Server insides 2 EC2

In this case, i used only WSFC? The reason why i asked is because in FCI document, it stated:

Storage
Contrary to the availability group, an FCI must use shared storage between all nodes of the FCI for database and log storage. The shared storage can be in the form of WSFC cluster disks, disks on a SAN, Storage Spaces Direct (S2D), or file shares on an SMB. This way, all nodes in the FCI have the same view of instance data whenever a failover occurs. This does mean, however, that the shared storage has the potential of being the single point of failure, and FCI depends on the underlying storage solution to ensure data protection.

But i noticed that i didn't have any shared storage.

@The "Are “Always On Failover Cluster Instances” and “SQL Server Failover Clustering” the same thing?" didn't explain it.


texts: Replicating from MySQL 5.1 master to 5.6 slave failing because 'INSERT ... VALUES (NOW())' results in 'Error_code: 1062'

texts:
I am migrating away from some old MySQL 5.1 servers to some new MySQL 5.6 servers. During this process, I'm creating a new MySQL 5.6 slave from an existing MySQL 5.1 slave, using the procedure in the mysqldump reference guide.

For example, if my MySQL 5.1 servers are named 'master1' and 'replica1' and I have a new MySQL 5.6 server named 'replica2', the following should make replica2 a second slave of 'master1':

replica2 % mysqldump --login-path=replica1 --all-databases --dump-slave --include-master-host-port --apply-slave-statements --lock-all-tables  --add-drop-database > all.sql
replica2 % mysql < all.sql

And this seems work well, but replication fails with the following error complaining about duplicate entries for the primary key.

2015-06-12 10:00:00 1234 [ERROR] Slave SQL: Worker 0 failed executing transaction '' at master log mysql-bin.009332, end_log_pos 12341234; Error 'Duplicate entry '8072' for key 'PRIMARY'' on query. Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())', Error_code: 1062

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is triggering the error here? Can I get replication to work without skipping rows with SET GLOBAL sql_slave_skip_counter = 1;?

Some additional details:

I'm using classic MySQL replication, and GTIDs are currently disabled.
The MySQL 5.1 servers are using STATEMENT-based replication, but the new MySQL 5.6 servers are using ROW-based replication.
I don't own the application code, and I cannot change the SQL.


texts: Oracle 11G Clob column with where clause

texts:
I am using a query to get data from a CLOB column. I need to add a WHERE clause to it to only get the rows where Error = 0 from the CLOB column.

The query I am using atm is:

select lt.T_ID, lt.SUCCESSFUL_TIMESTAMP,
       clob_xml_element_text(lm.XML_DATA, 'year-of-manufacture') "year",
       clob_xml_element_text(lm.XML_DATA, 'make') "make",
       clob_xml_element_text(lm.XML_DATA, 'model') "model",
       clob_xml_element_text(lm2.XML_DATA, 'mt') "odometer"
from tabletest2 lt
join tabletest lm on lt.T_ID = lm.T_ID
LEFT JOIN tabletest lm2 on lm.T_ID = lm2.T_ID and lm2.TYPE_ID = -129
where lm.TYPE_ID = -29
order by lt.SUCCESSFUL_TIMESTAMP ASC;

Output:

T_ID    SUCCESSFUL_TIMESTAMP    year    make    model odometer
167951  01/01/18 00:00:08.541000000     2009    Kia Sportage 2.0 Auto   82396
167979  01/01/18 00:02:42.613000000     2009    Mazda   2 Sport 5 DR 1.5 4AT    98589
174570  01/01/18 00:03:33.738000000     2015    Mercedes-Benz   CLA-Class CLA 250 2.0P/7AT/SL   null
198700  01/01/18 00:03:35.599000000     2008    Mitsubishi  Galant  null

Expected output

Remove the two rows where the odometer is null.

I tried to use:

(existsNode(lm2.XML_DATA,'/mt[Error]') = 0)

It works but when running the query with no limit I get the error:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

UPDATE:

Updating this question with the tables DDL.

CREATE TABLE tabletest
   (
  "TYPE_ID" NUMBER(10,0) NOT NULL ENABLE,
  "T_ID" NUMBER(10,0) NOT NULL ENABLE,
  "XML_DATA" CLOB,
  CONSTRAINT tabletest_pk PRIMARY KEY (T_ID)
   );

   CREATE TABLE tabletest2
    (
   "T_ID" NUMBER(10,0) NOT NULL ENABLE,
   "SUCCESSFUL_TIMESTAMP" TIMESTAMP(3) NOT NULL ENABLE,
   CONSTRAINT tabletest2_t_id
    FOREIGN KEY (T_ID)
    REFERENCES tabletest(T_ID)
    );

    INSERT INTO tabletest ("TYPE_ID", "T_ID", "XML_DATA") VALUES
    (
      '-29',
      1,
      '<?xml version="1.0" encoding="UTF-8"?>
    <l-ana version="2.9">
       <vehicle>
          <Error>0</Error>
          <year-of-manufacture>1999</year-of-manufacture>
          <make>Toyota</make>
          <model>Hilux</model>
          <mt>235998</mt>
       </vehicle>
    </l-ana>'
    );

    INSERT INTO tabletest ("TYPE_ID", "T_ID", "XML_DATA") VALUES
    (
      '-29',
      2,
      '<?xml version="1.0" encoding="UTF-8"?>
    <l-ana version="2.9">
       <vehicle>
          <Error>10</Error>
          <year-of-manufacture>1989</year-of-manufacture>
          <make>Toyota</make>
          <model>Hilux</model>
          <mt>535998</mt>
       </vehicle>
    </l-ana>'
    );

The disare output is to only get records where Error = 0.


texts: How to improve InnoDB DELETE performance?

texts:
So I have this audit table (tracks actions on any table in my database):

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and I need to start archiving outdated items. The table has grown to about 50million rows, so the fastest way I could delete the rows was to delete it a table at a time (based on tableName).

This works pretty well but on some of the tables that are write-heavy, it won't complete. My query deletes all items that have an associated delete action on a tupleID/tableName combination:

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

I let this run on my server for 3 days and it never completed for the largest table. The explain output (if I switch the delete to select:

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

So 4 million rows shouldn't take 3 days to delete, I would think. I have my innodb_buffer_pool_size set to 3GB, and the server is not set to use one_file_per_table. What other ways can I improve InnoDB delete performance? (Running MySQL 5.1.43 on Mac OSX)


texts:
I already asked this question on SO, prior to realizing that there may have been a more suitable place.

I am trying to implement an IS-A relationship between tables. Say I have a singer and a band which are artists

I want every artist to have an ID which is inherited by the band or singer.

In the code I wrote it appears that a band and a singer may have the same ID. How do I prevent that? And is there a better way to implement this? Reading material is most certainly welcome.

CREATE TABLE artists
( id number not null primary key
);

CREATE TABLE singers
( id number not null primary key,
  name char(50) not null,
  last_name char(50) not null,
  foreign key(id) references artists(id)
);

CREATE TABLE bands
( id number not null primary key,
  name char(50) not null unique,
  contact char(50) not null,
  participants number not null,
  check (participants > 0),
  foreign key(id) references artists(id)
 );

insert into artists(id) values(1);
insert into singers(id, name, last_name) values(1, 'an', 'Bish');
insert into bands(id, name, contact, participants) values(1,'un','an',2);

as you can see I was able to add a band and a singer with the same ID, under the same "parent."


texts: Install PostgreSQL using source code or using apt-get?

texts:
I want to install PostgreSQL on a server that runs Ubuntu 14.04.2 LTS.

I understand that I have two options:

Install PostgreSQL using the source code distribution
Install PostgreSQL using apt-get install postgresql postgresql-contrib with these instructions.

Which option should I use?

Will there be a difference in the outcome?
Will both options result in the same set up and configuration (assuming I choose the default installation with option #1)?
Is there a situation where I would prefer option #1 over option #2 and vice versa?


texts:
I read from a post about comparing MySQL and PostgreSQL, there is one who said:

  The biggest disadvantage of PG for me had been the lack of
  replication, but it's recently gotten both asynchronous and
  synchronous replication.

Source: https://www.quora.com/What-are-pros-and-cons-of-PostgreSQL-and-MySQL

So my question is: What does that "PostgreSQL lacks replication" mean?


texts: Overloaded Constructor for SQL Server stored procedure?

texts:
I'm making a stored procedure and I want it to accept a different number of parameters and have different behavior based on the number of parameters supplied. So let's say if I supply one parameter (for example a number) it returns the number. If I supply it with two numbers, I want it to add them together. If I supply it with a string, and a number I want it to concatenate the string with the number and display it--Similar to the behavior of overloaded constructors in Java.

Is there such a functionality with Stored Procedures in SQL Server, or am I better off just creating separate stored procedures. I only wanted to go this route to make my application structure simpler.

Thanks


texts: Concatenate Data from different row from same table

texts:
This is SQL source Table. I need to concatenate this table like the table below,appreciate your help.

PE_CASE   PE_ITEM   PE_HIS_SEQ     PE_COMMENT

200       0001      9989           Comment 1
200       0001      9990           Comment 2
200       0001      NULL           Comment 3
200       0002      9986           Comment 4
200       0002      9987           Comment 5
200       0002      NULL           Comment 6
201       0001      9926           Comment 7
201       0001      9937           Comment 8
201       0001      NULL           Comment 9
201       0002      9933           Comment 10
201       0002      9921           Comment 11
201       0002      NULL           Comment 12

I need to concatenate above table like this.

PE_CASE   PE_ITEM   PE_HIS_SEQ     PE_COMMENT

200       0001      NULL           Comment1 + Comment2 + Comment3 + Comment4
200       0002      NULL           Comment 4  + Comment 5  + Comment 6
201       0001      NULL           Comment 7  + Comment 8  + Comment 9
201       0002      NULL           Comment 10 + Comment 11 + Comment 12


texts:
If I have a Book table and Author table, with Book having a foreign key to Author, many rows in Book may have the same author. If I then run this query:

SELECT * FROM Book
INNER JOIN Author ON Author.Id = Book.AuthorId
WHERE Author.LastName = 'Shakespeare'

how will the underlying data be formatted and sent to the client? In essence - will each Book row have it's own Author (i.e. the data from Author is duplicated for each row), or will there be one single Author entry (representing Shakespeare)?

UPDATE:

To be clear, I just want to know whether joining tables will increase packet size (and transmission time) significantly, or if the protocol has some compression to ensure that only unique entries are transmitted.


texts: How do query notifications detect changes in the query result?

texts:
I am working with a legacy database that has implemented query notifications against a table called ColorList.

While running a server-side trace for an unrelated issue, I noticed the query

SELECT color FROM ColorList

was being executed every 10 milliseconds.

Is this how Query Notification works? Does SQL Server store the results of the original query and then run the query endlessly until it detects a change?


texts: backup/replication over inconsistent network

texts:
I need to setup a MySQL database server for a small number of local (write) users (~6) in a remote area with somewhat irregular internet access via WiMAX. Outages are not at predictable times or of predictable duration. I will have another small number of users (only really need read access) at a central office and...

I would like to know what backup/replication options are amenable to this scenario?

My fallback seems to be taking a nightly dump and running rsync as a cron job to sync it to local NAS + remote storage (central office + S3).

I'd prefer doing either replication or log-shipping to a remote server at the central office for r/o query users + dumps w/rsync to local NAS + remote S3 backup. But I'm unsure of the effects of extended network outages (hours to day) and/or significant packet loss.


texts:
My database in SQL Server 2008 went into suspect mode, and I fixed it by changing to emergency mode then I used this query

DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS);

And now my data letters in my tables changed from Arabic to question marks, I want to recover the encoding without dropping the tables or losing the data


texts:
I installed mysql and mysql-server from the repos (MySQL version 5.5).
Then tried to start it, but got an error.

[root@server]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Job failed. See system logs and 'systemctl status' for details.

Here is the log:

121118  2:41:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121118  2:41:38 [Note] Plugin 'FEDERATED' is disabled.
121118  2:41:38 InnoDB: The InnoDB memory heap is disabled
121118  2:41:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121118  2:41:38 InnoDB: Compressed tables use zlib 1.2.5
121118  2:41:38 InnoDB: Using Linux native AIO /usr/libexec/mysqld: Can't create/write to file '/tmp/ibhsfQfU' (Errcode: 13)
121118  2:41:38  InnoDB: Error: unable to create temporary file; errno: 13
121118  2:41:38 [ERROR] Plugin 'InnoDB' init function returned error.
121118  2:41:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
121118  2:41:38 [ERROR] Unknown/unsupported storage engine: InnoDB
121118  2:41:38 [ERROR] Aborting

121118  2:41:38 [Note] /usr/libexec/mysqld: Shutdown complete

121118 02:41:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Fresh installation, nothing changed prior to that, just ran yum update.

Here is the systemctl status trace

[root@linyansho /]# systemctl status mysqld.service
mysqld.service - MySQL database server
  Loaded: loaded (/lib/systemd/system/mysqld.service; disabled)
  Active: failed since Sun, 18 Nov 2012 02:45:19 +0300; 5min ago
  Process: 864 ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID (code=exited, status=1/FAILURE)
  Process: 863 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 842 ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n (code=exited, status=0/SUCCESS)
  CGroup: name=systemd:/system/mysqld.service


texts:
I am having a problem when running sp_updatestats on my database it generates an error when my emp table is updating and disconnects I will paste the error here

Updating [dbo].[emp]
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Is it because any problem with the table when I run DBCC checktable(emp) , it doesn't showed any problems.Then what will be the cause

Update:
How can we find the root cause of the problem without installing a service pack as mentioned by @gbn ,how can we eliminate it. what if the problem is occurring in version having sp2 installed ,If it  happens in a production server and installing service pack is the only way to resolve it ,as a dba what we can do?