texts:
Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records,

1 record for transaction start: T_i start,
1 for transaction changes on write(x) operation: T_i,X,V_1,V_2, where X is the data item that has been changed from V_1(old value) to V_2(new value).
And 1 for transaction commit(or abort),T_i commit.

How can I see these log records in SQL Server? how can I see recovery phase (analyses, redo and undo) in SQL Server?

Edit:
The given link in comment, will show me the table log that which executed transaction for example has delete command or update command. I want to show for example which changes Transaction T_i has made to a data item.
For example T_1,A,100,150 means transaction T_1 has changed the value of A from 100 to 150.


texts: how to config SSRS 2005 email setting with google apps

texts: My Company recently switches email service to google apps (asp.google.com). all the current subscriptions in SSRS2005 are configured to MS exchange server and no longer workable. Do someone knows how to configure subscriptions to google apps. Thanks in advance.


texts: Using a Different Windows Account for Invoke-Sqlcmd

texts:
I'm attempting to make a Powershell script that needs to interact with SQL Server, so part of the script will involve invoke-sqlcmd. However, the script can't be scheduled as a job in SQL Server and thus make use of the Agent to act as the Windows account used for authentication. Instead, it is going to be run using a different service under an arbitrary Windows account. The documentation for invoke-sqlcmd states that the -Username and -Password parameters are for SQL Server authentication, not Windows, and that it defaults to using the Windows account that is running the script for authentication if no SQL Server parameters are provided.

Is there any way to allow invoke-sqlcmd to use an arbitrary Windows account for authentication instead of the one it's being run under? I am aware that a possible solution would be to make accounts on the SQL Server instances that I want to run the script for and just use the SQL Server authentication option on the command, but I would ideally not want to have to do that. Some other research has also shown that a certificate could be an option to pursue, but I don't think that invoke-sqlcmd can take in certificates or otherwise utilize them like suggested. I could be wrong here though.


texts: Is it appropriate to use a timestamp as a DISTKEY in Redshift?

texts:
I'm having a little trouble understanding how to select a DISTKEY for a table I'm working on.

Consider the following table:

create table test_table (
    country     char(2)       encode zstd,
    record_time bigint        encode zstd not null,
    ip          bigint        encode zstd,
    identifier  varchar(41)   encode zstd not null,
    lat         numeric(10,3) encode zstd,
    long        numeric(10,3) encode zstd,
    PRIMARY KEY (event_time, hash)
)
DISTKEY(event_time)
SORTKEY(country, event_time, hash)

My understanding is that DISTKEYs are only of real importance if a table is to be joined with others.

This table will be the only one on its cluster, and thus, wont be joined with other tables. Since that's the case, am I right in assuming that a DISTKEY is unnecessary/redundant, or does a DISTKEY affect more than meets the eye?


texts: Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?

texts:
So i was reading MySQL innoDB, which apparently stores table data by default on a clustered Index (b+tree) based on primary key, and the tuples are in the leafs of that b+tree

https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

i was wondering do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite,  store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree?

and does Clustered index on primary key in databases basically mean storing the table based on primary key on a B+tree table?

(sorry if this is a general question but i cant make 4-5 seperate questions asking the same for each of the databases i mentioned, if you only know about one or few of them please tell)

also if its a database does not use clustered by default, can you explain how does it structure the files, for example do they make a b+tree on primary key which the leafs point to some sort of address inside a heap or...?

EDIT :

so far we got the answers for all of them except SQLite, if anyone have any info on how tables are actually stored in SQLite by default please do tell.


texts:
I have three servers: P1, A1, and R1.  P1 and A1 are production oltp servers.  On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports).  I am working on cleaning up our linked server infrastructure.  P1 and A1 both have linked servers to R1, and SSMS won't let me remove them because they are being used for replication.

Currently both of these linked servers are using the sa account on R1 for authentication.  I'd like to replace this with a least privileged account.  What privileges would P1 and A1 need in order for replication to work?

In case it matters, all of the publications are transactional replication.

Also, sql server versions:

P1: 10.0.5500
A1: 9.0.5069
R1: 10.0.4000


texts: MYSQL+MyISAM+Need information regarding check table statement

texts:
The MySQL CHECK TABLE statement checks a table or tables for errors.
Does the statement put an auto lock on a table before checking table for errors?
Does the statement perform a READ ONLY operation?
I have executed the CHECK TABLE command from MYSQL workbench (on same table copy/replication) operation going on,I got
Lost connection to server during query
error message for CHECK TABLE statement, read timeout interval (in seconds):[30]
Can this action can corrupt my copy/replication? (or because of data Copy/replication running on table, table is having auto lock ON so CHECK TABLE statement failed which is not going to impact copy operation). Please provide information so i will understand process better.


texts: Secondary filegroup for Full Text Index only - rebuild when lost

texts:
Here's our config:

We have all our data in PRIMARY file group
We have our Full Text Index in another SECONDARY file group which resides on a very fast (but very unreliable) SSD drive. By "unreliable" I mean - it's a Amazon cloud server temporary drive (can be entirely lost in case of emergency restart/hardware failure).

Full-text index is not critical and we're ready to lose it completely as we can obviously rebuild it from scratch.

The question is: is there a way to revive the database in case the secondary file group is 100% lost, without going through the full backup/restore cycle? Like, simply delete the lost file group, then recreate it, then rebuild the index.

(We do have regular full database backups, log backups etc. etc. etc. so we can "restore to the point in time" but this implies long outage)

P.S. Also, any thoughts on how viable our approach is would be very much appreciated.


texts: How to determine which SQL Server user or database overuses server resources

texts:
I have a Microsoft SQL Server instance on my server with more than 100 databases and users. Sometimes, the SQL Server process use 100% of server CPU and I want to know which query/user/database is causing this and using so much resources.

For MySQL, I use the mysqladmin pr command to see which users are currently connected. Also, the slow query log shows the queries that take more than X seconds to be completed.

I would like to know if there are similar commands for SQL Server.


texts: SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order ("desc")

texts:
We have a large database, about 1TB, running SQL Server 2014 on a powerful server. Everything worked fine for a few years.  About 2 weeks ago, we did a full maintenance, which included: Install all software updates; rebuild all indexes and compact DB files. However, we did not expect that at certain stage the DB's CPU usage increased by over 100% to 150% when the actual load was the same.

After a lot of troubleshooting, we have narrowed it down to a very simple query, but we could not find a solution. The query is extremely simple:

select top 1 EventID from EventLog with (nolock) order by EventID

It always takes about 1.5 seconds! However, a similar query with "desc" always takes about 0 ms:

select top 1 EventID from EventLog with (nolock) order by EventID desc

PTable has about 500 million rows; EventID is the primary clustered index column (ordered ASC) with the data type of bigint (Identity column). There are multiple threads inserting data into the table at the top (larger EventIDs), and there is 1 thread deleting data from the bottom (smaller EventIDs).

In SMSS, we verified that the two queries always use the same execution plan:

Clustered index scan;
Estimated and actual row numbers are both 1;
Estimated and actual number of executions are both 1;
Estimate I/O cost is 8500 (Seems to be high)
If run consecutively, the Query cost is the same 50% for both.

I updated index statistics with fullscan, the problem persisted; I rebuilt the index again, and the problem seemed to be gone for half a day, but came back.

I turned on IO statistics with:

set statistics io on

then ran the two queries consecutively and found the following info:

(For the first query, the slow one)

  Table 'PTable'. Scan count 1, logical reads 407670, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(For the second query, the fast one)

  Table 'PTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note the huge difference in logical reads. The index is used in both cases.

Index fragmentation could explain a little bit, but I believe the impact is very small; and the problem never happened before. Another proof is if I run a query like:

select * from EventLog with (nolock) where EventID=xxxx

Even if I set xxxx to the smallest EventIDs in the table, the query is always lightning fast.

We checked and there is no locking/blocking issue.

Note: I just tried to simplify the issue above. The "PTable" is actually "EventLog"; the PID is EventID.

I get the same result testing without the NOLOCK hint.

Can anybody help?

More detailed query execution plans in XML as follows:

https://www.brentozar.com/pastetheplan/?id=SJ3eiVnob

https://www.brentozar.com/pastetheplan/?id=r1rOjVhoZ

I don't think it matters to provide the create table statement. It is an old database and has been running perfectly fine for a long time until the maintenance.  We have done a lot of research ourselves and narrowed it down to the info provided in my question.

The table was created normally with the EventID column as the primary key, which is an identity column of type bigint. At this time, I guess the problem is with the index fragmentation. Right after index rebuild, the problem seemed to be gone for half a day; but why it came back so quickly...?


texts: Does a database update force index rebuild regardless of updated field?

texts:
With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated.

For example, if I have a table with 20 fields, the first 10 of which are referenced in either individual or covering indexes, is the speed of updates to the other 10 non-indexed fields affected?


texts: Should we add extra 5 columns or build a separate table?

texts:
Currently we have a table with 35 fields in it.

Now, we got a requirement to add 5 extra columns but these columns will not be fill always.

So, I was thinking just add one column and have that id as the foreign in another table with 5 or less rows depends on the availability of data.

Which is better

add the column ?
add a new table ?


texts: MySQL group concat not showing distinct values when joined to another table

texts:
I have one view that gives me comma separated values and want to use that data in another view. In the first view, I am using group_concat( distinct ...) and when I run that view by itself, I can see only the distinct values as expected.

However, when I join that view to another table, I am now getting duplicates.

SELECT
group_concat( distinct tag separator ', ') AS tag,
FROM
fruit

tag is already group_concated in the first view. Running this query gives me:

apples, oranges, apples, pears, figs, oranges

Apples and oranges are duplicated. Why is this?

EDIT:

I've paired this down as much as I could. Here is the view with no joins.

SELECT
tag
FROM
fruit

Here are the rows returned:

apples, oranges
apples, pears
figs, oranges

How do I get a group_concat for the column with an output of:

apples, oranges, pears, figs


texts:
I am trying to execute the below statement which creates a database called service2019:

DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));

EXEC (
'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY
( NAME =' + '' + @dbname +'' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + '''' + '''.mdf'''  + ', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
 LOG ON
( NAME =' + '' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + ''''+'''.ldf''' + ', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'
)

I get the correct database name and logical names. The problem is I get wrong physical names:

'service2019'.mdf

'service2019'.ldf

Could someone explain to me how these quotes actually work? There are some parts where it is clear but sometimes it becomes a challenge to figure out where to put those quotes.


texts: List of all missing order numbers that are not in the table

texts:
Our workplace has started to scan all documents rather than manually keep them in archives etc.

On average we are scanning 1,000 documents per time (this happens about 30 times per day).

Each time we scan 1,000 documents we get a small amount of errors/warnings on OCR.

Is it possible to provide the lowest and highest order numbers in to a variable and get SQL server to output a list of all missing order numbers that are not in the table between the lowest and highest order numbers?

For example

OCROrderNo
---------------
9001
9002
9006
9007
9008
9009

Lowest number 9001, highest number 9009

MissingOrderNos
--------------------
9003
9094
9005

Additional information

As per the answer, this what I am attempting:

DECLARE @Start INT = 1497389
        , @End INT = 1498264

SELECT @Start + N - 1
FROM   dbo.Numbers
WHERE  N <= ( 1 + @End - @Start )
EXCEPT
SELECT [ORDERNUMBER]
FROM   [dwdata].[dbo].[ORDERS]
ORDER BY [ORDERNUMBER] DESC

Resulting in:

  Msg 207, Level 16, State 1, Line 10
  Invalid column name 'ORDERNUMBER'.

  Msg 104, Level 16, State 1, Line 10
  ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.


texts: whether a big (but not used) ibdata1 slow anything down

texts:
Hi I have an ibdata1 that is around 10 gig.  I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore.

I recently read how to clean things up :

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

My question is, does anyone know if I just leave the big ibdata1 file there, does that cause any kind of performance hit?  I know I can reclaim disk space by cleaning things up, but that is not an issue right now.  Not taking the system down for a day would be a bigger problem.  So, more concerned with server performance or any other side affects.

Thanks!

Don


texts: SQLAnywhere: How to determine which path delimiter is used on database?

texts:
I need to build up a dynamic path from inside an SQL script. It started with something like

select left(
    db_property('File'), charindex (
        DB_PROPERTY ( 'Name' ), db_property('File')
    ) - 1
)

but need to add a subfolder there. For the subfolder, from my understanding, I would need to have the path delimiter so it can run under Windows as well as unixoid systems (and who knows if there will be some other systems somewhere maybe using ? as delimiter ;))

I've checked the db-properties but didn't found anything fitting. Is there a good way to determine the delimiter?


texts: Execute a T-SQL job with a different connection string

texts:
Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string?

The reason I want to do this is is to a avoid a trigger on the database that specifically distinguished on the Application Name used by the connection string.

I know you can execute an integration package with a different connection string, but I hope this is possible for T-sql jobs as well.


texts: how service sid operate under the covers?

texts:
This question is out of curiosity and it left me in confusion whether service account as any role to play in SQL Server startup and shutdown?

Can anybody explain how SQL Server 2008 Service Sids work and what is difference between Service SID and Service Account?

Little background :
From SQL Server 2008, I see the service sid's are added to the SQLServer Groups which gets created on the computer.
For example, I have installed SQL Server 2008 instance on my Desktop PC and when open Computer management and check the sql server groups for my newly installed instance,
I see that Service SID is added to the group.

Start -> Run -> compmgmt.msc

I used the below command at command prompt to view the SID. I see the same SID is getting added to the windows groups created by sql server instance.

sc showsid MSSQLSERVER

Now my actual question is, prior to SQL Server 2008, we used have domain account used as service account and this is one which gets added to the Groups which are created during SQL Server installation.
But from SQL Server 2008, it is the service SID which is being added to the Groups which gets created.
If the service SID is now assigned the necessary permissions, what is the need for the Service account then? Is is just a container for the service?

It makes even more interesting if we consider Windows Server 2008 and SQL Server 2008 clusters.
On Windows Server 2008 cluster, the cluster service runs as Local system  and considering I am using service sids instead of domain group, how come WFCS is able to communicate with Active Directory to bring the CNO's (sql network name) online and offline.

Any comments on that? Basically I wanted to know how these SID's works under the covers and what is the use service account from SQL Server 2008 onwards?


texts: Backup standby database using LVM snapshot

texts:
Objective:  Backup standby database (synchronous streaming replication) using LVM snapshot.

When running pg_start_backup:

postgres=# SELECT pg_start_backup('label');
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

Is it possible to copy backup_label and tablespace_map from the primary database's cluster directory into the standby database's cluster directory in order to perform the LVN snapshot backup of the standby cluster directory?


texts: Finding count of null values in 500 columns of a single table

texts: I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?


texts: How can I keep a result set without keeping the transaction open?

texts:
The following documentation describes how to see the refcursor returned from a function, here, like this:

    CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

This works for me. However, if I want to keep the results on my screen, I have to keep the transaction open. When I execute COMMIT, my result set is discarded. When I execute both FETCH and COMMIT at the same time, the first result set is discarded.

Is there a way to commit the transaction but keep the result set? The version of PgAdmin is 1.18.1.


texts: Deleted MySQL “root” user in phpMyAdmin through XAMPP local host…how to restore?

texts:
I was working in phpMyAdmin (XAMPP localhost) to add a user for a new database I created for a local WordPress website, and came upon the list of users named "root". Since I did not remember ever creating these root users, I tried to delete them. I don't know what I clicked. I don't remember hitting any delete button, but this resulted in deletion of root. After that, all my databases were gone along with all users. Now only "Database operations information_schema" is there in phpMyAdmin panel.  Now when I click on the "Databases" tab in phpMyAdmin, it simply returns a red warning "No privileges to create databases". There's no user tab. I can still see my databases in MySQL folder in XAMPP folder, and all WordPress websites work fine. But I am unable to create a new database/user nor am I seeing my existing databases. I searched google, and found a similar query here with a solution.
Deleted MySQL "root" user (using phpMyAdmin)...how to restore?
However, I tried most of the steps in the solution, but nothing came up. Could anyone please help me with a solution to add root user as well as databases back in phpMyAdmin using XAMPP localhost? I'm a novice, so need step-by-step instructions please. Your help will be greatly appreciated. Thank you!


texts:
I have SQL Server 2014 on my Windows Server 2012 R2. I can connect locally to SQL Server from Management Studio using Windows Authentication.

I decided to switch to SQL Server Authentication (because I need access from other computer). I created new Login using Management Studio, but get error: Login failed when trying to connect.

Then I changed password for sa and tried login as sa, but again get the same error:

I restarted SQL Server, but it didn't help. What I do wrong?

From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.


texts: Table 'performance_schema.threads' doesn't exist. SQL Error: 1146)

texts:
I'm using MySQL '5.6.29-log' on Windows 2012 and performance_schema is missing
few tables.

How does I re create performance_schema? I'm looking at MySQL :: MySQL 5.6 Reference Manual :: 22.2.1 Performance Schema Build Configuration and  I cannot find anything(

Please advise.


texts: Are there best practices or standardized syntax for writing transformation logic?

texts:
This may not be the appropriate forum, but its the closest I could find.  I write a lot of source to target mappings for work.  These documents just describe the operations/logic/transformations/etc. needed to get the data from A to B.  I find myself phrasing the same thing different ways, or sometimes the descriptions get convoluted if the logic is complex.

Are there any best practices for writing the transformation logic for relational databases?  For example, how to say 'Join tables A,B, and C for each combination of fields D,E, and F select the maximum value between fields G and H.'  I feel that somebody has probably standardized the natural language for relational database transformation logic, but I can't find anything to that effect.


texts: Potential corruption in sys.sysdercv, latch errors from ghost cleanup process

texts:
We're running SQL Enterprise 2016 (13.0.4411).  I have a database that was using service broker to handle some workflow, long story short, it was designed incorrectly and none of the conversations were closed correctly.  The sys.sysdercv system table blew up to about 270,000,000 rows and 140GB in size.

I created an agent job that grabbed the top 50,000 conversation handles from sys.conversation_endpoints in DI or DO state, and closed them with cleanup.  This agent job ran for a couple of weeks, and finally got the majority of the table cleaned out.  However, now it appears that there may be a corrupted page in sys.sysdercv.

The ghost cleanup process (spid 596 in this case) has been chugging along for about 37 hours now.  Checking it with sp_whoisactive shows that it's trying to get a lock on sys.sysdercv.  I'm also seeing the following error in the SQL log:

A time-out occurred while waiting for buffer latch -- type 3, bp 000000FA1CA98B80, page 1:21335899, stat 0x10b, database id: 13, allocation unit Id: 281474981494784/4784128, task 0x000000AF954AACA8 : 0, waittime 300 seconds, flags 0x100000001a, owning task 0x000001B98D8D04E8. Not continuing to wait.

The allocation unit ID corresponds to the clustered index on sys.sysdercv.  The owning task address corresponds to the ghost cleanup process.  I tried to run the following:

DBCC PAGE (13, 1, 21335899, 0) WITH TABLERESULTS;

This just hangs and doesn't return anything (sp_whoisactive shows that it's blocked by the ghost cleanup process).  I also tried to run this:

DBCC CHECKTABLE ('sys.sysdercv') WITH TABLOCK, ALL_ERRORMSGS;

Same deal, it hangs, then eventually times out with a latch error complaining about the same page.

At this point, I'm unsure of what to do.  There is no data I currently care about in sys.sysdercv or sys.sysdesend (no messages queued at the moment), so I was hoping I could just truncate these tables, which would (ideally) get rid of the offending page.  I can't truncate system tables though.  I can't kill the ghost process in order to do DBCC CHECKTABLE.

I thought about stopping the SQL service, enabling trace flag 661 on startup to prevent the ghost cleanup process from running, and then doing the table check.  However, I'm also seeing this error message:

One or more recovery units belonging to database 'xxx' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

So I don't know if the database will even come back online if I restart the SQL service.  My differential backups have been failing due to this issue, and DBCC OPENTRAN shows that the ghost cleanup is preventing the log space from being reused.

So...all that said, is there anything at all I can do to fix this problem?  Is there some magical trace flag that would allow me to truncate system tables?  Can I safely restart SQL without the ghost cleanup process in order to run DBCC CHECKTABLE (as in, will my database come back online since it hasn't been able to checkpoint in the past day and a half)?

Any help would be greatly appreciated.


texts:
When I run mongorestore I get error running create command: 24: Too many open files.

I've updated my launchctl limit and ulimit.

When I run lanchctl limit I get:

cpu         unlimited      unlimited
filesize    unlimited      unlimited
data        unlimited      unlimited
stack       8388608        67104768
core        0              unlimited
rss         unlimited      unlimited
memlock     unlimited      unlimited
maxproc     709            1064
maxfiles    256000         256000

When I run ulimit -a I get:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 256000
pipe size            (512 bytes, -p) 1
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 709
virtual memory          (kbytes, -v) unlimited

My mongo version is 3.4.7

My macOS is 10.13.2

How can I complete mongorestore?

Also worth mentioning: if I try running mongorestore more than once without logging out of the computer or restarting, I get cannot make pipe for command substitution: Too many open files and my computer crashes (This was a brand new iMac as of August 2017).


texts: In which extent can I use the Oracle Data Provider for .NET Managed Driver?

texts:
Good evening everybody,

I am planing to dive into the oracle database world. Additionally, I want to develop some tools, which make my life easy by checking and modifying information in different tables.

Since I would develop the tools in C#, I found the Oracle Data Provider for .NET Managed Driver, which seems to be really nice.

Before using it, I have some questions:

Is it free to use?
Regarding to the license (http://www.oracle.com/technetwork/licenses/distribution-license-152002.html), I should be able to use the managed driver. Or do I interpret it in a wrong way?

For me it is not clear, if I could also use my tools at customer side? Or could I give the tool to some customer to help him or does it lead to a license problem already?
What about selling the tool? For me it is hard to interpret the answers from the license text.

I hope you can help me.

Best regards
Seth


texts: MySQL: self-administration for multiple users

texts:
My MySQL users have rights to administer their own databases, including create/drop database. I do not have a shared phpMyAdmin installation, however I do let users install it themselves.  Most of the users do not need it and they end up mailing me to create databases for them.  That is not a problem for me but I want to give the users more control and at the same time keep things simple.

Users have access to a simple web based control panel and I want to add a GUI where they can create, drop, rename their databases and do one or two more basic MySQL things like view stats.  Most of the users do not need the capabilities of phpMyAdmin, just the ability to create and drop databases.

Which MySQL user should do these actions like create/drop etc?
Should I:

Create an additional admin user which has grants to create/drop databases for all users and set right grants on them.  I would not let users create/drop databases by themselves although allowing create/drop would probably not break anything.
Let the user do it with his own MySQL user?  That would mean the user will need to enter his MySQL password every time he wants to create/drop/whatever.  I could save his password  as plain text in his account table (arghhhh... no way).

I like the idea of letting the user do everything with his own MySQL user and password because he can only break his own things and it makes my application more secure at the same time.

However, I do not want to make it complicated by forcing users to remember their MySQL password.  On the other hand I do not want to save clear text passwords for their MySQL account.  This approach is broken because user could change his password with SET PASSWORD = PASSWORD(?) and my clear text password would not be up-to-date and web GUI would no longer work.

The first solution is surely the way to go but I still have a feeling there is perhaps a third solution I have missed.  Is there something similar used by some big webserver adminstration tools? Or are they also using a dedicated admin user for this stuff?


texts: Inserting unrelated data while a transaction is occuring

texts:
I'm thinking about implementing transactions to my app to help with consistency issues. I'm using MySQL.

I'm wondering what might happen if while inserting several rows as a transaction, the server will also need to insert a single row, that is completely unrelated.

To be more clear, I will present a scenario (hypothetical) of a store:

The application server in running nodejs, and connected to a MySQL server. It then receives a request to add a stock of 50 new computers, and needs to insert the 50 rows representing their SN to a table stock, plus a row in another table shipments, all 51 rows must be inserted in an atomic manner (all or none). While this is happening, the same nodejs server receives a request to register a new customer, and is required to add one row to a table customers.

In that scenario, what will happen if the transaction fails and must be rolled-back? Will the row in customers be ommited as well? Is this scenario even possible? Or maybe it depends on the implementation, and if so, how to implement it so that this scenario is garanteed never to happen?


texts: Modifying a result set in a Stored Procedure, then returning that modified set

texts:
I am currently in the process of designing my database for a mobile/web application. When I look at competing apps I see a massive flaw in how they handle user locations. Due to the nature of the application this could cause privacy and security issues for the users.

I need to store all user's locations in my database, but I want to reduce the possibility of an outside source gaining access to this data if the user does not want it shared.

After researching possible ways of doing this, I believe it is in my best interest to return values through stored procedures, limit db user access etc.

What I want to achieve is returning the results of a stored procedure. After the results have been modified. In this case, the procedure will run through the results. Check a value on the record and omit the relevant data if required.

From what I have read and understood the best way for me to achieve this is through creating a temporary table. Run a Select Query on my actual table into a cursor. Loop through the results, check the relevant value in the record, the insert into the temp table accordingly.

E.g.

Begin Procedure
Check if temp table already exists, drop if necessary
Create Temp Table, based on schema of the "permanent" table.
Run SELECT * FROM PERMTABLE WHERE Location within bounds ORDER BY Location ASC INTO CURSOR
LOOP THROUGH CURSOR
IF 'Show_Loc' IS FALSE
INSERT INTO TEMPTABLE WITHOUT LOCATION
ELSE
INSERT AS NORMAL

SELECT * FROM TEMPTABLE
DROP TEMPTABLE
END

Is there a better way of achieving this, or am I on the right track?

Also I am concerned that there will be a large volume of requests to the same procedure. Will there be any issues if the procedure was called multiple times at the exact same time in regards to temporary tables? Do I need to generate random names, or do MySQL/SQL Server handle this automatically?


texts: How can I search for a hex string in oracle?

texts:
There's a database record that is incorrect. The name field displays like this in a browser ( this is incorrect ):
Thcˇodore

And when I look at the record via SQL results, I see:
Thc\xCB\x87odore

I'm trying to search for other records that might be affected, so I would like to query the name field for any value "LIKE" \xCB\x87
I've tried using HEXTORAW and many other combinations including:
REGEXP_LIKE(NAME, \'\xCC\x88\' )

REGEXP_LIKE(NAME, \'[\xCC\x88]\' )

NAME like '%' || HEXTORAW(\'CC88\') || '%'

NAME LIKE ‘%’ || X’CC’ || X’88’ ||’%’

NAME like \'%\' || \'\x88\' || \'%\'

Can't seem to get any of them to work, and I can't find any other examples or documentation online. Maybe I've been googling for the wrong things but this is what I've tried to google:

oracle escape hexadecimal
oracle hex to ascii
oracle like hexadecimal query
oracle pcre Hexadecimeal select
oracle query binary character
oracle REGEXP_LIKE hexadecimal
oracle select \x0A
oracle select escape character hex
oracle select hex codes
oracle select like \x0a
oracle select REGEXP_LIKE \x0A
oracle select X 0A

How can I search for \xCB\x87 with Oracle?


texts:
Currently I'm just trying to delete objects with the Name column being Null from a temp table with the following...

delete from #PulledData
where #PulledData.Name0 = NULL

I've also tried = 'NULL', = '%NULL%' etc. but nothing seems to work. I'd appreciate some help on the matter.


texts:
We're using SqlServer to store users session state of our webapps. We've recently noticed the ASPState.log_ldf file has reached 50GB.

Is it safe to put the web apps down and then drop and recreate the ASPState DB? I don't see the point of having backups of this DB so we would also set simple recovery mode in order to avoid this problem in the future and spare storage space.


texts: Why is the authentication_string different from the other one if the passwords are the same?

texts:
I've set the same passwords for the following accounts: 'root'@'%', 'root'@'127.0.0.1'.
When I type select user, host, authentication_string from mysql.user I get different values for the authentication_string.

Is it because the other way of encryption is used for external accounts like '%'?


texts:
I have to 2 table, the first is a MEMORY table with 3.000.000 record the second is at the moment empty. The structure of these table is identical, both have a int(11) primary key, the two table have all primary key field different (the same key is not possible in both tables)

If I query the first table I have an answer in 0.1 seconds, the query on second table is immediate 0.01 seconds.
But merging the 2 with the UNION ALL is dramatically slow (1 min 21 sec).

SELECT * FROM memtable1 LIMIT 1000

  (0.1 sec.)

SELECT * FROM table2 LIMIT 1000

  (0.01 sec.)

SELECT * FROM memtable1
UNION ALL
SELECT * FROM table2 LIMIT 1000

  (1 min 21 sec.)

How can I improve the speed of the query?


texts: Should every table have a single-field surrogate/artificial primary key?

texts:
I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are 'artificial'.

However, it sometimes seems to be a matter of policy to have an auto-incrementing integer field as the primary key of each table. Is this always the best idea to have such a single-field key and why (or why not)?

To be clear, this question is not about artificial vs natural - but about whether all artificial keys should be single-field


texts: How to add a column in table via stored procedure?

texts:
create or replace procedure SP_PARAMETER_TEST -- this is my table
(
       NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum"
)
is
begin
  ALTER TABLE PARAMETER_TEST ADD NAME_KEY VARCHAR(2);
end SP_PARAMETER_TEST;

I want to add the column which has text value "Gypsum". But I am getting errors. How can I do this?


texts: MySQL create user incorrectly updates user table

texts:
I recently upgraded from mysql-community 5.7.4 to 5.7.8. All packages show the newer version is correctly installed. I reinitialized my database using mysqld --initialize and corresponding steps. I then restored from a full dump using the standard method (ie, zcat dumpfile.gz | mysql). The server started up fine; I was able to start slave and catch up to the master, still running on 5.7.4. Now the weirdness begins. The user tables don't match. Even if I stop slave and restart my mysqld process, the following (and similar) commands do something strange:

create user 'test'@'%' identified by 'test334455' ;

It puts the hash into the "plugin" field of the user table:

mysql> select user,host,password,plugin,authentication_string from user where user='test';
+------+------+----------+-------------------------------------------+-----------------------+
| user | host | password | plugin                                    | authentication_string |
+------+------+----------+-------------------------------------------+-----------------------+
| test | %    |          | *443C443E4789A416587C0A5892C7D345B73B9B3B | NULL                  |
+------+------+----------+-------------------------------------------+-----------------------+

To make sure this isn't some weird package-specific bug, I initialized a new database and with a separate mysqld process, verifies the command works as expected. Somehow restoring from a dump or connecting as a slave to an older version causes the problem.

However, existing user entries (ones restored from the dump) are correct and work just fine.

UPDATE: It appears the password field was removed between 5.7.5 and 5.7.6, but the restore-from-dump (not replication!) recreated the column; but the current version's create user commands still assume a particular order to the columns (** facepalm **).

So the Question now is: How do I safely update the master database on 5.7.4 to 5.7.8, preferably without dump/restore?

UPDATE: The Mysql documentation does indeed cover this particular quirk, more or less, on a page well-worth bookmarking (Because really, MySQL, you have no shame in making incompatible changes to sub-minor versions.) https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html


texts: Can I make PL/SQL functions more strongly typed?

texts:
I like to use overloading to speed up development, and I want to structure my code to make future changes easier. I have a function that accepts a NUMBER or a VARCHAR2, but I want it to throw a compilation error if I pass in a DATE.
Here's an illustration of the problem I want to avoid:
DECLARE
   PROCEDURE do_something(
      in_ IN VARCHAR2);

   PROCEDURE do_something(
      in_ IN NUMBER);

   mystery_variable_1 VARCHAR2(32767);
   mystery_variable_2 NUMBER;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN VARCHAR2)
   IS
   BEGIN
      dbms_output.put_line('do_something called with VARCHAR2 argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN NUMBER)
   IS
   BEGIN
      dbms_output.put_line('do_something called with NUMBER argument ' || in_);
   END do_something;
BEGIN
   mystery_variable_1 := 'Hello, World!';
   mystery_variable_2 := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
END;
/

Here's the output showing the implicit cast from DATE to VARCHAR2:
do_something called with VARCHAR2 argument Hello, World!
do_something called with NUMBER argument 42
do_something called with VARCHAR2 argument 05-APR-63

I don't want that third line to be allowed.
The best work-around I have so far is to create a honeypot overload that throws an application error. This catches the issue in testing but not at compile time.
DECLARE
   PROCEDURE do_something(
      in_ IN VARCHAR2);

   PROCEDURE do_something(
      in_ IN NUMBER);

   PROCEDURE do_something(
      in_ IN DATE);

   mystery_variable_1 VARCHAR2(32767);
   mystery_variable_2 NUMBER;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN VARCHAR2)
   IS
   BEGIN
      dbms_output.put_line('do_something called with VARCHAR2 argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN NUMBER)
   IS
   BEGIN
      dbms_output.put_line('do_something called with NUMBER argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN DATE)
   IS
   BEGIN
      RAISE_APPLICATION_ERROR(-20001, 'do_something called with DATE argument ' || TO_CHAR(in_, 'YYYY-MM-DD HH24:MI:SS'));
   END do_something;

BEGIN
   mystery_variable_1 := 'Hello, World!';
   mystery_variable_2 := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3);
END;
/

If I leave this work-around in place for the benefit of the maintenance developer, I get an unsightly PLW-06006 error when my private function is excluded from my package.
Can I trap this at compile time instead?


texts: What is the term that describes the use of OFFSET and LIMIT in a query

texts:
I am looking for a technical term (or phrase, or something concise) that describes the process of limiting the number of records returned from a query through the use of OFFSET and LIMIT.

For example, I have an application that displays a bunch of records in a table. If I do something naive like this

SELECT * FROM table

It might be slow, especially if there are millions of records, so I optimize my application by using a paginated user interface that will display 50 records at a time. So for example, I want to get records 101 to 150 because the user decided to go to page 3, I might say something like

SELECT * from table LIMIT 50 OFFSET 100

I don't know what kind of words I should be using to describe my design decisions so that others will immediately understand what I'm doing.


texts: How to export data from multiple tables to csv from remote postgres server (AWS RDS) to local machine?

texts:
I need to export data in CSV format from many (undertimined amount) tables from a remote postgres (aws RDS PostgreSQL 10.6) server to a local machine.
When the server was in its own machine (AWS EC2) this was possible with a function that used a loop to iterate over the necesary tables then used execute and COPY commands to create the CSV files directly onto the same server from which they could by copied over where needed.
But since the server was migrated to an RDS instance, I can't use that approach anymore because I have no direct access to the filesystem on the postgres server. I can only connect to it remotely via a client.
Every solution I've seen until now requires either filesystem access to the server or works with only one set of data at time with a known table that produces only one csv file on the local machine.
Is there a way to achieve what I need ?


texts: What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean?

texts:
I have a question regarding following output:
[nikola@192 ~] $ sudo su - postgres
[postgres@192 ~] $ psql dvdrental postgres
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql (11.7, server 11.8)
Type "help" for help.

dvdrental=#

What do following two messages stand for and why? Messages I am asking about:

psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql (11.7, server 11.8) - BTW, for this psql --version command I get following output:
psql (PostgreSQL) 11.8 (had to install PostgreSQL Server version 11.7 in order to perform command such as \dt and \l)
...and also this output:

postgres=# select * FROM version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1 20200408 (Red Hat 9.3.1-2), 64-bit
(1 row)

P.S. I'm running PostgreSQL 11.8 on Fedora 30.
UPDATE1:
As requested by @LaurenzAlbe output for rpm -qa|grep postgres command is:
[nikola@192 ~] $ rpm -qa|grep postgres
postgresql-11.7-2.fc30.x86_64
postgresql11-11.8-1PGDG.f30.x86_64
postgresql11-libs-11.8-1PGDG.f30.x86_64
postgresql11-server-11.8-1PGDG.f30.x86_64
postgresql-server-11.7-2.fc30.x86_64

UPDATE2:
I've ran dnf remove postgresql postgresql-server command -> databases are still there and I can access them via pgAdmin4 and psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql message is no longer showing up. But, there is now new issue -> when I access postgres shell in terminal using sudo su - postgres command and then perform psql some-database-name postgres I get message

bash: psql: command not found...
Install package 'postgresql' to provide command 'psql'? [N/y]

I presume this is asking me to install AGAIN Fedora package which in 1st place I am trying to get rid off (to not show psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql) message).
Any suggestion how to solve this without installing Fedora package but from PostgreSQL Global Development group?
P.S. BTW, I've installed Postgres server by following this link which uses PGDG packages (but something didn't work right on my machine and I guess I've been offered by Fedora30 OS to install Fedora packages which is obviously what I did :) ).


texts:
I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now working with big data (300-750gb) for the first time.

As a result, I have no concept of how long things should be taking. I think my database is performing very poorly, but having never worked on these scales I don't really know.

So here's my question: even basic queries are taking me at least 8 hours on a 237gb table. Vacuum takes ~6 hours. And a query pulling out distinct pairs of values:

CREATE TABLE UserPairs AS
SELECT DISTINCT a, group_a, sum(quantity) FROM cdr GROUP BY (a, group_a) HAVING type = 'DATA' AND group_a IS NOT NULL;

ran for 8 hours before I aborted.

An attempt to build 4 hash indices over main columns ran for 24 hours then crashed.

Hardware: 3 cores, 12 gb ram Windows 8 server VM. (I know, but I don't have control over my hardware. Long story).

So basically: within an order of magnitude, how long should I expect basic queries to take in postgres for tables of this size?

And if this seems way off, how do I get more precise benchmarks? I'm running pgbench now, but can't find resources on how to interpret the results. This listserv exchange suggests there aren't any repositories of results...

1.5 billion rows.
Settings are default, looks like work_mem = 1mb, maintanence_work_mem = 16mb.
IO System: it's a VM so the disk is just listed as a "VMware virtual disk SCSI disk device", but Performance Monitor says avg Disc sec/transfer is 0.010sec and average disk queue length is 2.16.


texts: MySQL / MariaDB pid ended (cannot start mysql) in CentOS

texts:
I have had a system with MySQL up and running for years.  I recently accidentally moved all the directories in /var/lib/mysql to /var/lib/mysql/mysql2 and then fixed it and moved them back...  or so I thought.  I cannot get MySQL to start now.  When starting through cPanel I get:

Service Error
The “mysql” service failed to start.

Startup Log
Starting MySQL. ERROR!

Log Messages
160102 16:11:42 mysqld_safe mysqld from pid file /var/lib/mysql/host.suicideprevention.com.pid ended

mysql has failed. Contact your system administrator if the service does not automagically recover.

When trying to start it with mysqld -start I get:

160102 16:13:17 [Note] mysqld (mysqld 10.0.23-MariaDB) starting as process 3723 ...
160102 16:13:17 [ERROR] Fatal error: Please consult the Knowledge Base to find out how to run mysqld as root!

160102 16:13:17 [ERROR] Aborting

160102 16:13:17 [Note] mysqld: Shutdown complete

The error file created (.err) contains:

160102 16:17:15 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_index_stats"' in the cache. Attempting to load the tablespace with space id 33982.
2016-01-02 16:17:15 7f85301f9700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
160102 16:17:15 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_index_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
160102 16:17:15 [ERROR] InnoDB: Failed to find tablespace for table '"mysql"."innodb_table_stats"' in the cache. Attempting to load the tablespace with space id 33983.
2016-01-02 16:17:15 7f85301f9700  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
160102 16:17:15 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_table_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
160102 16:17:15 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.26-76.0 started; log sequence number 49285133043
160102 16:17:15 [Note] Plugin 'FEEDBACK' is disabled.
160102 16:17:15 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
160102 16:17:15 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
160102 16:17:15 [Note] Server socket created on IP: '::'.
160102 16:17:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
160102 16:17:15 mysqld_safe mysqld from pid file /var/lib/mysql/host.suicideprevention.com.pid ended

I have tried chown and chmod and nothing is working.  I am in a panic.


texts: Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

texts:
I have the following table

[dbo].[myObj]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fk_ParentID] [int] NULL
)

and I have a relationship from fk_ParentID to ID.

I want to change the Delete Rule in SQL Server Management Studio under the Window "Foreign-Key Relationships"->Insert and Update Specifications->Delete Rule to ON Delete Cascade, but it is not possible.

Can you tell me why, and how I can make this possible?


texts:
The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks like this :

 id    | status   | planned_date
-------+----------+--------------
 1     | inactive | 2015-03-12
 2     | active   | 2015-03-13
 3     | inactive | 2015-03-13
 4     | inactive | 2015-03-14
 5     | active   | 2015-03-12
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17

Should be returned as

 id    | status   | planned_date
-------+----------+--------------
 5     | active   | 2015-03-12
 2     | active   | 2015-03-13
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17
 4     | inactive | 2015-03-14
 3     | inactive | 2015-03-13
 1     | inactive | 2015-03-12

Note: For this example to be simple, only two statuses are used, but this is an enumeration of 6 different values for this field; new, pending, active, inactive, cancelled, completed. Also, there are other fields that need to be sorted: planned_date, and priority (a numeric value from -3 to 3, 0 being "normal"). I am mentioning this only as a FYI, as I don't believe it to be much relevant, and that the general idea can be understood with the simple example above.

To sort by the status field (an enum) I simply perform

SELECT id, status, planned_date
FROM tbl_tasks
WHERE ...
ORDER BY status ASC;

Now, how do I sort the planned_date field (ASC for active status, and DESC for inactive status)?


texts: Constraint many to many table between two child tables

texts:
New to this exchange site so I hope my question isn't inappropriate.

Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently.

One table has a One-To-Many relationship with two other tables. These two tables then have a Many-To-Many relationship between them. Shouldn't I somehow ensure (other then in the programming insertion) that the items that are linked together belong to the same parent table.

Let's put it in a Scenario (This is a fictional scenario):

Division table where each row is a division in a company. It has One-To-Many relations with a Employee table. So each employee belongs to one and only one division.

The Division table also has One-To-Many relations with a Project table where specific projects are logged. The project also has to register how many hours a employee has logged with this specific project. I would do this with a Many-To-Many table between Employee and Project and a Hours column.

This should all work fine ( I think at least) except that theoretically nothing in the database would ensure that the Employee and Project belong to the same Division even when they should and in fact both belong to a entity in that table.

What is a good way to ensure the intention of this schema and the integrity of the data is handled by some abstract relational database. Would you construct the database in any other way or would you put some constraint on the Many-To-Many relationship?


texts: How do I recover my database after losing a tablespace with indexes?

texts:
I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it fails and the tablespace is lost.

The docs (and various blogs) warn that losing any tablespace probably leaves you with a database broken beyond recovery. Is this really the case, if I'm only losing my indexes?

I'm assuming that after failure I'll have a slow but functioning database which I can then safely reindex, perhaps using my default tablespace (or a new one on another SSD). Am I wrong? What would actually happen to my database, and how easy would it be to restore it to its pre-fail state?


texts: How to influence Oracle backup output size?

texts:
I have two environment one Prod one Dev. Dev is created by Prod data and both have a similar database size. However, the backup output size varies greatly. Prod is about 300 M while Dev always above 1G. Both using incremental Level 1 backup.

Why would this be happening?


texts:
I am using MySQL version 5.5 and I am trying to use load data infile and I am getting errors.  I've looked at the documentation and it looks like I am doing exactly what the docs specify.

Running this command in mysql

LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error.

  ERROR 1148 (42000): The used command is not allowed with this MySQL version

Running the following command

LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TER' at line 1

What is wrong with my syntax?
The data in the file is

126,126text,moretext
127,127text,moretext


texts: Add running count of specific state changes

texts:
I have a table in PostgreSQL with below fields, where timestamp is simplified as integers:

ts  status
1     m
2     m
3     i
4     s
5     s
6     i
7     i
8     m
9     s
10    m

I want to break then aggregate rows based on the presence of the 's' status, to spit out an additional column which I can use to easily group rows:

ts  status  flag
1     m      1
2     m      1
3     i      1
4     s      1
5     s      1
6     i      2
7     i      2
8     m      2
9     s      2
10    m      3

The dynamic column flag shall increment whenever status changes from 's' to something else.

I don't see a clear pattern to partition by. How to do this?


texts: Unique relationship attribute for every pair of entities

texts:
I have entities "Student" and "Course" and they are connected with relationship many-to-many called "Exam". The relationship "Exam" has an atribute "Grade". For this ER model, I have made a relational schema:
Student(StudentID, StudentName, DoB)
Course(CourseID, CourseName)
Exam(StudentID, CourseID, Grade)

Can someone explain how would you put in a relational schema the fact that a student can't get multiple grades for a single course? For example, we can't have records like (Student1, Course1, Grade1) and (Student1, Course1, Grade2).


texts: Get and Put performance testing on Google BigTables (and other integrated DBs)

texts:
What are some effective ways to perform programmatic performance testing on database operations, especially in environments where the databases themselves do not offer dedicated tools?

For example, in Google App Engine, entire page-loads are evaluated as one operation which may include specific database operations. This problem is also likely present in SQLite and other integrated DBs. As it is difficult to completely abstract the (equivalent of) selects and inserts that need to be tested, are there any recommended database tools to perform more thorough diagnostics on these sorts of queries?


texts: How can I get the last items by date desc then by id desc?

texts:
I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data with some more metadata. The histories can be inserted in any order. That is the importer can just put them in any order. With each history that is added there is a next due date which states when they have to take the course again. I need the live employee course table to be updated with the latest record from the history ordered by NextDueDate Desc, then by Id Desc. There are two properties(HasAttachments, UniqueId) that need to be copied from the history table. The employeeId and companycourseid act as a reference to the history table.

Here is the code I came up with. I think this will work but I need some second opinions.

WITH History AS (
    SELECT  Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments,
        ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY
        NextDueDate DESC, Id DESC) AS rownum
    FROM TrainingHistory h
),
Latest AS
(
    SELECT h.* FROM History h
    WHERE h.rownum = 1
)
 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from Latest l INNER JOIN
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND
      l.EmployeeId = ec.EmployeeId

I just put the results I need for updating into a temp table so I can easily update the required UniqueId and HasAttachments columns into the employee course table.

update EmployeeCourse
set HasAttachments = ISNULL(
        (select HasAttachments from dbo.TempEmployeeCourse where Id
         EmployeeCourse.Id), 0),
UniqueId = ISNULL(
        (select UniqueId from dbo.TempEmployeeCourse where Id =
         EmployeeCourse.Id), newid())
GO

DROP TABLE [dbo].TempEmployeeCourse
GO

This seems like the most elegant solution. Is there anything I might be missing here?

EmployeeCourse

CREATE TABLE [dbo].[EmployeeCourse](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [CompanyCourseId] [int] NOT NULL,
    [HasAttachments] [bit] NOT NULL,
    [UniqueId] [uniqueidentifier] NULL,

 CONSTRAINT [PK_Employee_Courses] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

TrainingHistory

CREATE TABLE [dbo].[TrainingHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [NextDueDate] [date] NULL,
    [CompanyCourseId] [int] NULL,
    [UniqueId] [uniqueidentifier] NULL,
    [HasAttachments]  bit NOT NULL DEFAULT 0,
 CONSTRAINT [Training_History$PrimaryKey] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


texts:
In our data model we have classes with one to many relations to eachother

TestRun (HasMany) => TestData (HasMany) => AnalyzeRun (HasMany) => Output parameters.

We have to query this data in multiple parts, sometime just getting a lot of runs with few Output parameters. Other times all Output parameter for fewer runs.

Row counts will be pretty big

TestRun: 1.000.000 records
TestData: 40 * TestRun
AnalyzeRun: 4 * TestData
Output: 50 * AnalyzeRun

Among all the tables there will be about 8.000.000.000 records.

We have tried to model this as NoSql but don't seem to find a good way for this. Could this design benefit from NoSql or be redesigned?

We do not require this but want to see if a Relation Sql server or NoSql like RavenDb would fit best for our needs.

there will only be insert operations (or update if you model the TestRun as one document and add child items later).

Main focus will be on reading and combining part of the entire relation for about 1000 TestRuns and after that getting the full stack for a limited amount of TestRuns.


texts: Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id

texts:
I have a table like this one, that shows payments from each client, for different dates:
|  stream_datetime | customer_id | order_status | rn |
|:----------------:|:-----------:|:------------:|:--:|
| 04/06/20 11:19AM |      1      |   completed  |  1 |
| 05/06/20 10:54AM |      1      |   completed  |  2 |
| 06/06/20 10:59AM |      1      |   completed  |  3 |
| 08/06/20 09:27AM |      2      |    failed    |  1 |
| 09/06/20 11:02AM |      2      |    failed    |  2 |
| 01/11/20 05:59PM |      3      |   completed  |  1 |
| 02/11/20 05:59PM |      3      |   completed  |  2 |
| 03/11/20 10:01AM |      3      |   cancelled  |  3 |
| 04/11/20 09:20AM |      3      |   completed  |  4 |
| 05/11/20 10:25AM |      3      |   completed  |  5 |
| 01/13/20 03:29PM |      4      |   completed  |  1 |
| 02/13/20 03:29PM |      4      |   completed  |  2 |
| 03/13/20 03:29PM |      4      |   cancelled  |  3 |
| 04/13/20 03:29PM |      4      |   completed  |  4 |
| 05/13/20 03:29PM |      4      |   completed  |  5 |
| 06/13/20 03:29PM |      4      |   completed  |  6 |
| 07/13/20 03:29PM |      4      |   completed  |  7 |
| 08/13/20 03:29PM |      4      |   cancelled  |  8 |
| 06/20/20 03:29PM |      5      |    failed    |  1 |
| 07/20/20 03:29PM |      5      |   completed  |  2 |
| 08/20/20 03:29PM |      5      |   completed  |  3 |
| 09/20/20 03:29PM |      5      |    failed    |  4 |
| 10/20/20 03:29PM |      5      |   completed  |  5 |

I want to calculate the difference of days a client canceled his plan.
The challenge here is the fact that the client can cancel more than one time, so client 4 must
be counted as a churned client two times, but client 3 would be counted as a churned client
only one time.
I want to consider only clients that has an order_status = completed followed by (not necessarily in the next month) an order_status = cancelled.
I want too to create a column called purchase_day that keeps the day of the payment.
Obs.: the column rn means row number for the specific client.

EDITED:
Sorry. I made some mistakes and writing the question.
Maybe the order_status = cancelled come first than order_status = completed. This is due a mistake in the business, but it can happen. If this happen, so we cannot consider as a churned client.
So this is my expected result (now this is ok):
|   purchase_day   | customer_id | lifetime|
|:----------------:|:-----------:|:-------:|
| 01/11/20 05:59PM |      3      |   60    |
| 01/13/20 03:29PM |      4      |   60    |
| 04/13/20 03:29PM |      4      |   122   |

As you can see:

client 1 never cancelled (so he doesn't need to be in the result)
client 2 never cancelled (so he doesn't need to be in the result)
client 3 cancelled one time (his lifetime is equal 60 days)
client 4 cancelled two times (one lifetime of 60 days and another lifetime of 122 days)
client 5 never cancelled (so he doesn't need to be in the result)


texts: mongo 3.6 replication and write concern timed out

texts:
Not sure how to ask this.
I have a process that is importing thousands of records to the database using a bulkwrite.
I process 1000 documents and then write that and then get the next thousand and write that etc.
This works perfectly in a 1 database scenario and i'm working on setting up replication.
When replicating, I'm getting a write concern error 64 "waiting for replication timed out" frequently.
I do have journaling turned on and have the rs.config set with w:majority.
My understanding is that means to be sure its written to at least the journal before the response is returned.
I'm not sure how best to handle the write concern error though.
Do i just capture it and continue on? or do I need to reprocess some records?
Both primary and secondary have the same document count when executing the count() request on the collection.
Any guidance on what best practices are for this would be helpful.
thanks.


texts: Known vulnerabilities for when a guest executes a stored procedure on SQL Server

texts: I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How secure is it if I let the other company's IT department execute stored procedures on our database server? They would have to know the database name, address, procedure name and of course I could set them a new login. My worries are, are there "easy" ways of bypassing security and accessing other data in the database?


texts:
The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process.

According to the mysql docs:

  On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.

Now, how does the percentage approach makes sense here when we are talking about actual numbers?

Lets say that currently my db uses 80 gb of ram and 20 gb of ram is left for other tasks.
After a period of time, my database grows bigger and i decide to increase the buffer pool size. To keep the 80% proportion, i have to increase the total ram of the server, even though other processes did not require more ram memory.
Why not just add the additional ram while keeping the same 20gb of ram for the other tasks?


texts: Move Complete ORACLE from one system to another

texts:
I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server].

Entire ORACLE HOME ( bin , control files, data files everything on oracle) is on 2nd Disk (which is not a OS disk)

Since OLD server had reached end of life (from HP) a new HP machine with upgraded RAM (OLD system had 16 GIG where as new system as 64 GIG).

Now Since All oracle Dependencies (RPM, libraries, environmental variables, JAVA, Users and groups) are already made on new server.

So Now Question is ..

Can I remove Disk [Oracle HDD] from OLD server and insert it on NEW server and just start Oracle (sqlplus "/ as sysdba" or dbstart and lnsctrl).

Can it will give any challenge.
(I have cold backups, export backup, & RMAN backup too).

I just want to give a try on above case.

Will this work ??


texts: Getting 'specified network password is not correct' when trying to change password

texts:
After changing my network password, I went straight to the SQL Server Configuration Manager (SQL Server 2008) to update the passwords for the SQL Server and SQL Agent services. (Background info: the services run under my domain account.)

However, I kept getting the below error after clicking on apply:

The specified network password is not correct. [0x80070056]

I guarantee the password entered was definitely the correct one because I have restarted my workstation and used it to log on successfully. Most answers on the web tend to focus on the password but I know that's just the symptom not the root cause.

I know it is difficult to answer questions like this one unless you have experienced it first-hand. So I am asking if anyone else has seen this behavior before and what they did to resolve it.


texts: Could too many idle connections affect PostgreSQL 9.2 performance?

texts:
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high.  When running ps aux, I see ~250 "idle" connections (which I believe to be too many).  I haven't started doing a full diagnosis, but I wanted to know if this is a good place to start looking.

I am also using PgBouncer with transaction-level pooling.  I am suspecting that I can easily reduce the number of idle connections by tuning the pool size.  However, I don't want to start doing too many changes unless there is a good reason to do it.

Can many idle connections in PostgreSQL 9.2 affect performance?

Thanks very much!


texts:
I have a large table with a column that contains duplicates.

I want to add a unique constraint on this column for future entries only (not the old data).

Is this possible?


texts:
I am having a hard time deciding what's the best approach for my database design (I am using PostgreSQL 12)
We have customers, each customer has a fleet of vehicles, each vehicle is identified as units and given a unique name. We install devices in vehicles. A device can be moved from vehicle to vehicle or swapped if there is an issue. A unit can change vehicle too, or be sold to another company (which in turn could become our customer). We need to record every change, vehicle change, device change, who installed what where.
My issue is that it seems I have two different constraints depending we are talking at a specific moment or over time. For example:

A device can be in one vehicle at any given time. So it's a simple one-to-one relationship.
Overtime a device could be associated with a different vehicle since they can change vehicles.

Same goes for every association, one-to-many at one point in time, but many-to-many if we consider overtime.
My thinking is if I'd take a many-to-many approach is that I have to have exclusion constraint pretty much on every table. An exclusion constraint to say for example that a device cannot be associated with another vehicle if a previous record in the table hasn't been invalidated. I am concerned this will end up with too many exclusion constraints.
CREATE TABLE vehicle_device (
  id SERIAL PRIMARY KEY,
  vehicle_id INT REFERENCES vehicle (id),
  device_id INT REFERENCES device (id),
  installer_id INT REFERENCES installer (id),
  replaced_by INT REFERENCES vehicle_device (id)
)
ALTER TABLE vehicle_device ADD CONSTRAINT unique_exclusion
    EXCLUDE (device_id WITH =, device_id WITH =) WHERE (replaced_by IS NULL) DEFERRABLE;

Or does it make more sense to have:
CREATE TABLE vehicle_device (
  vehicle_id INT REFERENCES vehicle (id),
  device_id INT REFERENCES device (id),
  installer_name INT REFERENCES installer (id),
  PRIMARY KEY (vehicle_id, device_id)
)

And an extra separate table for recording historical changes.
Which of the two design make more sense?
Edit:
Going with the separate historical table. Would an inherited table to keep the historical data make sense ?


texts: Extracting Data from multiple archive tables

texts:
I am developing a report in report builder 3.0 where I am extracting data from a table dbo.odcalls.
The problem is this table is archived on monthly basis to a separate database (HN_Backup), and I need to combine the data from all archived tables and tables which will be generated in future and give the results. I have 2 approaches to do it:

union all tables - the issue will be for archive tables in future as 'if exist' will not work with union.
insert every new row added in odcalls to a different table with only insert/update (not delete).

I would avoid using triggers or replication for this.
I can't make changes to the current structure. I was wondering what would be the best way to move every new entry in odcalls to another aggregated table, from where I will run my report.
Please let me know if there is any workaround.


texts: Denormalization Strategies: When is it appropriate to create redundancy for faster querying?

texts:
So I sometimes see databases purposely add redundant information for faster querying. I saw that this was mentioned here but I was wondering when would this be better than indexing, or creating a view? Or perhaps it is never better?

I know there are definitely scaling issues with adding this redundancy, but is there a time when it is more ideal to sacrifice scalability for immediate speed gains?


texts:
Given a table 'employees'

 employee_id | salary | department_id
-------------+--------+---------------

Only using SQL find all the variants of employee-transfers from one department to another, so that average salary in both 'departure' and 'arrival' department grew.

PS I was asked the question on a interview, which never gave an answer, and Google is of little help.


texts:
I am trying to implement a simple table in MS - ACCESS where im using two PK's for say a WEATHER table.

  PK LOCATION
  PK ID
  DATE
  TEMPERATURE

I wish to increment the id based on the location, so if PARIS has 2 rows then the next would be for example 3, and if there is a new entry for Location then it would be 1.

very much like this link

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Help greatly appreciated.


texts: How to group records by a two-value column ignoring small gaps?

texts:
I have a subquery with a column named code that only has 1 and 0 values and an ID column because I query a lot of codes for each ID. I want to group by said column (and by ID) and by ocurrence but want to ignore groups with less than 3 records, so that bigger groups get merged.

I use ROW_NUMBER() - ROW_NUMBER() over (partition ...) to generate a group number which I use to group records by, but sometimes the records get mixed up caused by a coincidence in the sequence of the row number and the partitioned row number. This is an example

code | row_number | row_number_partition_by_code | group_number
------------------------------------------------------------------
  0  |     1      |             1                |     0
  0  |     2      |             2                |     0
  1  |     3      |             1                |     2
  1  |     4      |             2                |     2
  0  |     5      |             3                |     2
  1  |     6      |             3                |     3
  0  |     7      |             4                |     3
  0  |     8      |             5                |     3
  0  |     9      |             6                |     3
  0  |     10     |             7                |     3
  1  |     11     |             4                |     7
  0  |     12     |             1                |     0

As you can see, some groups contain both codes!

After this, I do

GROUP BY group_number
HAVING COUNT(1) > 2

to eliminate small groups, and generate another group number to merge adjacent groups.

How can I group these values homogeneously?

More Details

All records are ordered by ID and by date (the later in descending order) in every ROW_NUMBER().

The final result I expect is the time in minutes of the latest code group for each ID, and merging groups that are separated by less than 3 minutes.

I'm more interested in the groups with the value 1, so if the latest code group is a 0 ( considering it is not a couple minutes long ) then ignore that ID completely.

Usually, there is one record per minute. Sometimes it can take hours without a record for any ID


texts: how to set default charset while create new field

texts:
Is there any command in mysql that makes the following:
if I create a new field and I set the type to TEXT than it automatically knows that by this I mean not latin1_swedish_ci but utf8_hungarian_ci charset.

I would like to apply this changes to the whole database without create a new table.


texts: ORA-01017 error when conecting to Oracle Database Standard Edition 2.12.2.0.1.0 for Microsoft Windows x64 (64-bit)

texts:
I've installed and created non-CDB database (single tenant) database. Created a user account using the SYS user, granted it the CONNECT and CREATE SESSION roles. When I attempt to login using the newly created account I get the "ORA-010170: invalid username/password; login denied" error.

I'm I missing something with respect to the Oracle 12c Standard database?? Since I've created a "normal" database I should be able to login with a non SYS account.

Thanks!!


texts: MySQL do not accepts wildcard for tables name level grant

texts:
Trying to grant a user select privilege on multiple tables using wildcards as following:
GRANT SELECT ON 'DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password';

But keep giving the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password'' at line 1

Server version: 5.7.30-log MySQL Community Server (GPL)
I'm sure that the syntax of the query is correct, but for some reason it won't accept it, Any advise?


texts: Is mongodb upsert the right option for write intensive use case?

texts:
I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 - 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates per day and read load would be 600 - 800 TPS

Since we have opted for an embedded data model, we are using upsert queries in most places. We have unique indexes built on the right fields to make the search faster. Out of the 30 million TPS day, we will be performing at least 10 million upsert per day

I would like to know:

Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications?
Considering the load, I suspect that too much of upsert would created high IO and crash the system.


texts: How do I reduce SharePoint 2010 SQL server log files?

texts:
I have a work place portal built on SharePoint 2010 and the SQL Server drive space is full. I am using SQL Server 2008 R2. Of the 800 GB disk space, the log files have taken approximately 730 GB. There are many huge log files and what I want to know is:

How do I reduce the sizes of the log files?
Which log files can I reduce?
Can anybody help with a step by step guide on how to do this? This is a production server.


texts: NULL value in query_plan column from the sys.dm_exec_query_plan DMV

texts:
I've written a query to return data from the sys.dm_exec_query_plan DMV, but for a specific stored procedure the query_plan column which should contain a link to the XML version of the plan is NULL.

select
    qs.sql_handle
    , qs.statement_start_offset
    , qs.statement_end_offset
    , qs.plan_handle
    , execution_count
    , st.text
    , substring(st.text, (qs.statement_start_offset/2)+1,
        ((case qs.statement_end_offset
            when -1
                then datalength(st.text)
            else
                qs.statement_end_offset
            end - qs.statement_start_offset) / 2 + 1)) as [Filtered text]
    , qp.query_plan
from sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
where st.objectid = object_id('myProcedure')
order by qs.sql_handle
    , execution_count desc

I've checked the documentation and indeed it says that the columns is nullable, but does not say anything about the reasons why its nullable.

Although the procedure has been ran at least two times, my understanding is that the plan cache should have information saved about this stored procedure and its compiled execution plans, but this query returns NULL.

I've also looked in the procedure code to check if it has the RECOMPILE option both at procedure level and at statement level and it doesn't.

My next assumption is that the query_plan column is NULL because all of the queries from the procedure (and from the above result set) are either inserting or reading data from #temporary tables.

Is my assumption correct and is that the reason why no query plan is stored for these queries? Or is there possibly another reason?

Update:

I've checked based on @Shaky's comment if the procedure is encrypted, by verifying the value in sys.dm_exec_sql_text.encrypted and it's 0.

I've also tested my assumption that the procedure plan is not stored because of the use of temporary tables in the queries and for that I created a dummy stored procedure which stored the result in a temporary table and then displayed the data from that table and its execution plan is returned by my query.


texts: Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my production environment.


texts: Understanding Stolen and foreign memory usage between 2 app database servers

texts:
Below is more for my understanding on how to interpret the data from my perfmon counters for 2 database servers with diff app structure but sort of similar workload

  Server A has total of 1 TB RAM with databases of approx 10 TB with
  below metrics via 4 NUMA node config

  Server B has total of 1 TB RAM with databases of approx 16 TB with
  below metrics via 4 NUMA node config

Server B has more MAX memory assigned to it due to size of databases being large out there. Yes i know MAX memory settings can be improved little better, but would plan later if i can understand above numbers better for stolen and foreign memory.

What do those numbers tell me as i see foreign memory seems to be approx 50 GB for one server and stolen being 150 GB on other server where foreign just seems to be 5 GB.

Sorry for my lack of knowledge here, but can someone guide on better way to interpret this data and what possible changes one can think of tweaking here?


texts:
I am working on importing data from an old non-relational database, to a new relational one.  I am currently working on importing data into the following table:
Table: job_part
    job_id   INT NOT NULL --References ID Column of Job table
    part_id  INT NOT NULL --References ID Column of Part table
    quantity INT NOT NULL --Amount of parts required for the job
    PK = job_id & part_id

The data I have is in a .csv file which has the following columns;

Part Number - Value will be referenced with Part table to get part_id value
Job Number - Value will be referenced with Job table to get job_id value
Amount - Value to be stored in Quantity column

My problem is when there are data rows like the following:
CDDM 42, P0164, 1
CDDM 42, P0164, 2

As you can see, the Part Number, and Job Number are the same, while the quantity value is different.  Because the table I want to insert this data into has the PartID and JobID as a composite primary key, these pieces of data will give me an error when I try to import it.
What I want to do, is to take this data, and before I put it in my table, put it into a temporary table that combines all of the matching key pairs.  So for my problem example above, instead of having the two rows that will cause and error I want the result to be:
CDDM 42, P0164, 3

Essentially, find every matching pair of key values, and add their quantities together so they can be a single row of data.
However I am unsure how to go about solving this problem.  I am using SQL Server.


texts: Improving the query standards and performance run time

texts:
For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i tune it up further as it runs over hours. I will try to get a repro, but for now, let me know if below can help:-

Query

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM
       TABLE1,TABLE2,TABLE3

WHERE TABLE2.ID=TABLE1.ID
AND   TABLE2.COLUMN1='Data'
AND   TABLE3.ID=TABLE1.ID
AND   TABLE3.COLUMN2='Value'
AND   TABLE1.COL1=CAST('20190914' as DATE)
AND   DATEPART(hour,TABLE1.COL1) BETWEEEN 1 AND 2

For Table1 there is no CI, we just have Unique NON CI as PK

TABLE 2 and TABLE 3 also does not have any CI, they both have Unique non CI on both ID and datetime column
TABLE1.COL1 is a datetime column

Above query is run multiple times in 12 batches a day for different date range in between clause to collect data between 24 hours, like 0-1, 1-2, 2-3 , 3-4 .... 21-22,23-24

Is there a better way to perform above run?

Thanks


texts:
I have a SQL Server 2014 database table containing a code and two identifiers:

DECLARE @Foo TABLE (code CHAR, id_a INT, id_b INT)
INSERT INTO @Foo (code, id_a, id_b) VALUES
('A', 1, 2),
('B', 2, 1),
('C', 3, 4),
('D', 4, 0),
('E', 5, 3)

The first two rows of the table form a two-way match, as the first row links IDs 1 and 2, and the second row links them in reverse.

I want to find all rows that don't take part in a two-way match, and to that end I've written the following query:

SELECT code FROM @Foo WHERE CONCAT(id_a, ':', id_b) NOT IN (
    SELECT CONCAT(id_b, ':', id_a) FROM @Foo
)

This is working (it returns codes C, D and E for my sample data), but I'm concerned about the performance of those concatenations once the table has millions of rows in it.

I haven't tested the performance with a large dataset (that's tomorrow's job), but in the meanwhile I was wondering if there's a more elegant and performant way I can structure this query?

P.S.
The query plan indicates that I'm doing table scans inside a scalar compute inside a nested loop, which doesn't sound like it'll scale too well:

  |--Nested Loops(Left Anti Semi Join, WHERE:([Expr1007] IS NULL OR [Expr1008] IS NULL OR [Expr1007]=[Expr1008]))
       |--Compute Scalar(DEFINE:([Expr1007]=concat(CONVERT_IMPLICIT(varchar(12),[id_a],0),':',CONVERT_IMPLICIT(varchar(12),[id_b],0))))
       |    |--Table Scan(OBJECT:(@Foo))
       |--Compute Scalar(DEFINE:([Expr1008]=concat(CONVERT_IMPLICIT(varchar(12),[id_b],0),':',CONVERT_IMPLICIT(varchar(12),[id_a],0))))
            |--Table Scan(OBJECT:(@Foo))


texts: Can SQL Agent job notifications be sent to one operator for success and another for failure?

texts:
We have a requirement that some people are sent notification on success, and other on failure.

But the UI seems to only allow a combination of 1 operator + 1 condition.

So I was wondering if we could do perhaps by transact sql in the MSDB?


texts: SQL Server 2016 Database Full Text Catalog not being updated

texts:
I have a database with a Full Text Catalog enabled, however the Catalog hasn't updated since about 4 weeks ago.

There are no errors in the event logs, the SQL Server logs or the last SQLFT log file, which is also timestamped at the last time the Catalog updated, and has no errors in the file. The FT daemon last restarted around 6 weeks ago, and is running

The table change tracking is set to "Automatic" and Full-Text Indexing is enabled.

In the catalog, the table/column has track changes set to automatic.

Queries against the Full Text Index are returning data up to the point that the catalog was last updated, but nothing newer.

select * from sys.dm_fts_index_keywords( DB_ID('db'),
OBJECT_ID('dbo.table') )

returns 198k rows of data.

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'dbFullTextCatalog' -- change here !

SELECT name as FTCatalogName,
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs

Returns the Last populated as 4 weeks ago and the current status as Idle (0).

Any ideas what may have caused the catalog to stop updating, and what the resolution may be? This is a production system so I cannot "just restart the server"

Thanks in advance for any ideas what may be causing this!


texts: CLR Stored Procedure on Azure SQL Managed Instance error on execution: "Assembly in host store has a different signature than assembly in GAC"

texts:
I have a CLR stored procedure which executes correctly when deployed to local SQL Server instances from SQL Server 2012 - 2017. I can successfully deploy to an Azure SQL Managed Instance but when I execute the procedure I get the following error:

  Could not load file or assembly 'System.Net.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050).

I have tried playing around with the project references but to no avail - the only version of the System.Net.Http.dll that can be deployed to the Managed Instance is one that errors on execution.


texts:
Has anybody run into the issue where an application (arcserv) using SQL Server Express 2008 is uninstalled on a server running SQL Server 2014? When arcserv was uninstalled, it or user deleted the SQL Server agent. What was/is the agent recovery process,

run the repair from the install media
run the install media again
uninstall SQL Server and install again

Our efforts to repair ran into multiple issues including registry component keys that we could not access due to missing permissions.


texts: Better way for multiple joins on same table but different id ranges

texts:
In our application we have a slow performing reporting query that needs to be optimized.
The performance loss is mainly because there are up to 11 left joins on the same table and we can't put in foreign keys. There already are indexes on the typeid as they are foreign keys to a Type table. The situation can be reduced and abstracted to the following:

USE tempdb;

IF OBJECT_ID('person') IS NOT NULL
  DROP TABLE person;
IF OBJECT_ID('AdditionalColumnValue') IS NOT NULL
  DROP TABLE AdditionalColumnValue;
IF OBJECT_ID('AdditionalColumn') IS NOT NULL
  DROP TABLE AdditionalColumn;

CREATE TABLE person (
  Id INT PRIMARY KEY
);
CREATE TABLE AdditionalColumn (
  Id   INT PRIMARY KEY,
  Name VARCHAR(100)
);
CREATE TABLE AdditionalColumnValue (
  Id                 INT PRIMARY KEY,
  EntityId           INT,
  AdditionalColumnId INT FOREIGN KEY REFERENCES AdditionalColumn (Id),
  [value]            NVARCHAR(4000)
);

INSERT INTO person VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO AdditionalColumn
VALUES
  (1, 'EatsStoneFruit'),
  (2, 'EatsBerry'),
  (3, 'EatsCitrus'),
  (4, 'FavoriteAnimal');
INSERT INTO AdditionalColumnValue
VALUES
  (1, 1, 'mango'),
  (1, 1, 'lychee'),
  (1, 2, 'blueBerry'),
  (1, 2, 'raspberry'),
  (1, 3, 'lemon'),
  (1, 3, 'orange'),
  (1, 3, 'blood orange'),
  (1, 3, 'grapefruit'),
  (1, 4, 'earthworm'),
  (1, 4, 'raven'),

  (2, 1, 'mango'),
  (2, 2, 'raspberry'),
  (2, 3, 'lemon'),
  (2, 3, 'grapefruit'),
  (2, 4, 'tardigrade');

-- Query
SELECT person.Id, fruits.value fruits, animals.value animals
FROM person
LEFT JOIN AdditionalColumnValue fruits on fruits.EntityId = person.Id and fruits.AdditionalColumnId in (1,2,3)
LEFT JOIN AdditionalColumnValue animals on animals.EntityId = person.Id and animals.AdditionalColumnId in (4)
-- up to 9 more of these

I have the idea I should be able to optimize by somehow partitioning on the typeId's and then only do the costly join once.
But I can't figure out how.

As I expect people to wonder why I'm not solving this problem on a DML level:
This table allows our consultants to extend any table with non-standard fields on request.
As they can be linked to any table the link between the tables is on convention, not on foreign key.
It probably was a good solution when it was used sparingly, but things evolved.  The business value of this system's flexibility is considerable and it will be a hard sell to change anything about it. If I can't get this to work reasonably fast setting up a batch job to to denormalize the data would be our plan B.

UPDATE
in my effort to boil the problem down to it's core I made some mistakes. Updated the query and added buildup sql.


texts: How can I identify and fix bottlenecks in an Oracle11gR2 import

texts:
I've recently been tasked with moving our Oracle 11g database from a linux CentOS desktop to a windows 2008 server. Given I've not done much behind the scenes stuff with oracle before I'm a bit out of my depth.

I did a full expdp from the source server and imported it to the target server (i built tablespaces before the load but nothing else).

Everything went well enough (a few errors that were related to having a different global_name which, from documentation, appeared ignorable.

The problem I am having is that the new server appears to take longer to run queries compared to the old server. This is in spite of more cores and more RAM. I think the problem is due to I/O limitations as the old server ran on an SSD.

I get the same 'explain plans' on queries yet getting through the blocks on v$sql_longops just seems to take longer... Can anyone provide details on what steps to take to check and compare output so I can try to ascertain the underlying issue?


texts: Instant File Initialization on Shared drive

texts:
I asked something similar to this before, but previously I had asked about moving backups to a shared location. This time I am curious: if I want to restore a database on a shared drive, do I need to enable IFI on that server or just the server that is running SQL Server?

The reason I ask is I am restoring a pretty large database and its been stuck at 100% restored for the last couple hours. The wait type in sp_whoisactive is:

(28472716ms) `PREEMPTIVE_OS_WRITEFILEGATHER.

The only time I've seen that is when IFI is not on, but I do have it enabled on the SQL Server, but it is not enabled on the shared drive server.


texts: How do you correct a corrupted index in Oracle?

texts:
I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf'

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

Now I am stuck on which index is that actual segment name. (I think :/ )
I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

*
ERROR at line 1:
ORA-01418: specified index does not exist

Even though if I do "select index_name from dba_indexes", that index name shows up.
What am I doing wrong?  This is a 10.2 Oracle server.

Thanks in advance!