texts: Find and return items based on matching strings across 17 tables

texts:
I must start by saying that I am at best a sql novice. I am doing some data analysis and consolidation for my company and need to find if items match across any of the tables. The info in the tables came from 3 different databases and a bunch of spreadsheets that had been used.

The information I'm looking to match up is circuit numbers.  Some of the tables have the same design, but the information is not always entered in the same fields or even the same format. The tables do not have the same number of columns and sometimes the string that would match across them is a sub string in one of the other tables. The id's can be both numeric and alphanumeric. One other issue is that no one single table has enough good/correctly entered data to use as a primary table to compare the rest to.

What I am looking to achieve is to somehow search all of the tables and if there is a string that matches across any number of them, I would like to identify where it matched and create a table that contains the info from all columns in the row(s) that contained the match.

Below is a list of the tables, the number columns each has and the fields in that table that could potentially contain a circuit ID.  I know this is confusing and I hope I was able to explain it and even more hopeful that someone may be able to help since doing a manual stare and compare will take months. I have tried everything that my limited knowledge allows from joins to unions to putting it in excel and using tools like vlookup, but the state of the data is creating too many stumbling blocks for me.  It seems that I may need to use some sort of stored procedure to check through the tables and return the results, but this is something that is quite a bit over my head.

Table 1 - 152 columns - CktNum(nvarchar(8)), IXC(nvarchar(25)), BOC(nvarchar(25)), AML(nvarchar(10)), LLOCKTID(nvarchar(25)), assignnum(nvarchar(50))

Table 2 - 17 columns - IXC CKT ID(nvarchar(255)), BOC CKT ID(nvarchar(255)), Local CKTID(nvarchar(255))

Table 3 - 10 columns - Name(nvarchar(255)), Name1(nvarchar(255)), Name2(nvarchar(255))

Table 4 - 21 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 5 - 22 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 6 - 20 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 7 - 20 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 8 - 20 columns - Local Circuit ID(nvarchar(255)), IXC Circuit ID(nvarchar(255)), BOC Circuit ID(nvarchar(255))

Table 9 - 5 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 10 - 5 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 11 - 7 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 12 - 5 columns - Local ID(nvarchar(255)), Circuit ID(nvarchar(255)), ECCKT ID(nvarchar(255))

Table 13 - 18 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255))

Table 14 - 17 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255))

Table 15 - 40 columns - ECCKT(nvarchar(255)), LO CKT ID(float), CKR(nvarchar(255))

Table 16 - 9 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255))

Table 17 - 17 columns - Circuit ID(nvarchar(255)), Customer(nvarchar(255)), Contact(nvarchar(255))


texts: Best way to migrate a database involved in bidirectional replication

texts:
I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2.

The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In other words SQL2005ServerA.DatabaseA and SQL2005ServerB.DatabaseA are involved in bidirectional transactional replication.

Now I have to move these databases to two new servers, call them SQL2008ServerA and SQL2008ServerB.

Is this pretty straight-forward? After disconnecting the application server, do I just backup and restore to the new servers, and then recreate the replication without snapshots?


texts: Is SQL Server Replication transactionally 'safe' across all published tables?

texts:
We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 cluster.

My question is, are the INSERT/UPDATE/DELETE statements on the source database across all published tables guaranteed to be transactionally safe (as the name suggests)? For example, if I perform the following query on the source database:

BEGIN TRAN
INSERT  t1  (mycol) VALUES  ( 0 )
INSERT  t2  (mycol) VALUES  ( 1 )
COMMIT TRAN

Assuming that t1 and t2 are part of the same publication, will the following query on the destination always yield the result below?

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT 'Value in t1: ' + CAST(t1.mycol AS VARCHAR(10)) from t1
SELECT 'Value in t2: ' + CAST(t2.mycol AS VARCHAR(10)) from t2
COMMIT

Result:

Value in t1: 0
Value in t2: 1


texts: Keep selected tables synchronized across MySQL databases

texts:
I've a need that requires me to keep a number of tables from a database (db1) synchronized with another database (db2). db1 & db2 have different structures and a set of tables and their data from db1 will need to kept up to date in db2.

I couldn't find a straight forward way to do this via MySQL replication and I'm open to any suggestions. One crude way is to export the tables from db1 at night, drop them from db2 and import them there. I worry about this approach and would prefer something that doesn't require me to drop any tables. I could also run a script that collects all the data that has been added/modified/deleted in the due course of the day and then runs a script on db2.

Both the databases are hosted on Amazon RDS, on two different instances.

Any suggestions?


texts: How to keep one query from slowing down entire database?

texts:
Using MySQL 5.0.x, we have a process to generate benchmarks for our business that consolidates a bunch of data, then pull out data based on certain criteria. This process used to take 18 hours, it's been creeping up to over 24 hours, meaning it's now intruding on the business day.

The problem with this is that these queries substantially slow down the database. Both the INSERT ... SELECT queries and, even moreso, ANALYZE.

Is there some way to make a single query not slow down the entire system? Can MySQL throttle it to not bog down the whole dbms? If these could continue running during the business day I'd be very happy, but without that, ... I don't know because I've made this as streamlined as I can with our current system. We're testing moving to MySQL 5.5, and the improvements it gives (like InnoDB) but it would be nice to know if throttling was possible, or if there's some setting I'm missing that's causing a single query to drag down the system.


texts: How to query for something like "last active" per column in Access

texts:
Let's Say i have two Tables, let's call them

PC-Info
| ID | Hostname  | Location          |
|:---|----------:|:-----------------:|
| 1  | Hostname1 | This Room         |
| 2  | Hostname2 | That Room         |
| 3  | Hostname3 | The other Room    |
| 4  | Hostname4 | Somewhere Offsite |

And

IsUp
| Timestamp  | Hostname1   | Hostname2   |
|:-----------|------------:|:-----------:|
| 19.02.2017 | up          | down        |
| 20.02.2017 | down        | down        |
| 21.02.2017 | down        | up          |
| 22.02.2017 | up          | down        |

What would a query in Access look like to get the last time the host was "up", bundeled with the other Info? Or is this database flawed from the ground up?

Since the amount of hosts ist fixed and the timestamps will increase over time, I thought it was the better idea to have fixed columns and add rows per timestamp.


texts: SQL Server Table keeps big after schema change

texts:
I have a table in SQL Server 2012 with 460.000 rows.

This Table has the following Schema:

We had an issue with our application so all 500.000 rows were created with Comments filled up with spaces.

This led to a table-size of approximately 3.7GB:

After this I did the following to fix the ending spaces, but the database stays at 3.7GB.

update voucher set comments = ltrim(rtrim(comments))

A quick calculation showed me, that one completely filled row has approximately 280 bytes of raw data and one empty row (all varchars empty) has 32 Bytes.

This means my table should be between 32MB-122MB for 460.000 rows.

Why is the table 3.7GB? How can I reclaim the space?


texts: MongoDB skipping 20 documents when storing in a variable

texts:
ids = db.students.find({}, {
    _id: 1
});

It gives the length of ids as 2993 while students count is 2413. It's not storing the 20 documents which are being displayed on the shell.


texts: Using the hash of text instead of text itself to address a row in Postgres

texts:
I have a project for a website where I have to address thousands or maybe tens of thousands of objects in a postgres table.

Initially I had the url to be:

example.com/object/{ID}/{TEXT SLUG}/

where {ID} is just an integer that corresponds to a serial primary key in my table, {TEXT SLUG} does nothing practically here except for SEO friendliness reasons.

Now, I want to hide my {ID}, since someone may just request all objects incrementing the {ID} using some easy script and reconstruct my database simply, I thought to design the url to be more modern like

example.com/object/{TEXT SLUG}/

Now, if I just address the table using {TEXT SLUG} directly, wouldn't it be much slower than simply address using {ID} as before even with using some index especially when it gets bigger (I expect something in the range of 40K to 60K rows at maximum but I only have 2K currently in the table)?

I thought to use some lightweight hashing algorithm to hash the {TEXT SLUG} and address the table using the hash with adding an additional column having a copy of the hash. Since Postgres has 4 or 8 byte integers,  I am tempted to use some lightweight 128-bit hash algorithm (e.g. Murmurhash), am I thinking right about using the idea of hashing the slug or is there something I am unaware of?


texts:
Our customer has some data in varchar columns. fullname per say. There is an index on it.
Now they want this data to be encrypted and using symmetric keys is the solution proposed.
I was able to change the data in the column with its encrypted version by dropping index, replicate the column and later updated it encrypted data. Related insert SPs and select Views are changed to encryptbykey and decryptbykey procedures. No problem, all working fine.
My question is, after encrypting the column will the index on fullname would be necessary? The purpose of creating an index on encrypted data would be met?
I think no. Expert advice is appreciated.
Thanks.


texts: Use variables in filename with INTO OUTFILE (timestamped filename)

texts:
SELECT *
FROM project
INTO OUTFILE 'C:\desktop\[timestamped filename like 01/05/2014.csv]';

Can anyone help me out?


texts: To normalize or not to normalize for few distinct values

texts:
Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'.

Would you store the party_type in the party table (e.g. party_type = 'Person') or normalize it (e.g. party.party_type = 1 and party_type(id, name) = (1, 'Person'))?
And why?


texts: Changing a column datatype from INT to BIGINT

texts:
I have a large, partitioned table with a field in that needs to upgraded from INT to BIGINT.

My intention is to detach each partition, make the change and then re-attach, using something like this:

ALTER TABLE testTable DETACH PARTITION testTable_201908; -- Should be very quick
ALTER TABLE testTable_201908 ALTER COLUMN recordId TYPE bigint;  -- likely to take ages!
ALTER TABLE testTable_201908 ADD CONSTRAINT con_201908 CHECK ( loadedTime >= DATE '2019-08-01' AND loadedTime < DATE '2019-08-01' ); -- Likely to take ages.
ALTER TABLE testTable ATTACH PARTITION testTable_201908 FOR VALUES FROM ('2019-08-01') TO ('2008-09-01' ); -- Should be quick with the constraint in place.
ALTER TABLE testTable_201908 DROP CONSTRAINT con_201908; -- Should be quick

The table is really big (about 100 millions records per partition), dozens of partitions. I've done some testing, DETACH PARTITION is really quick, ALTER COLUMN recordId TYPE bigint takes ages (but I don't think there's anything I can do about this)

Then I have a choice, if I just re-attach the partition to the parent table (without a constraint) then that takes ages whilst the database checks integrity (at also seems to lock-up the parent table for the duration). OR, I can add a constraint (takes hours) and then re-attach to the parent (takes seconds only)

Is there a way I can force the re-attachment of the partition without checking integrity and without requiring a constraint? After-all, I know the partition was valid before I detached it, and I know I haven't altered any of the relevant data?


texts: Merge Replicate, reinitialise with a different part of the data

texts:
I have a set of database with merge replicate configured between them.

The publisher is an aggregate of three sets of data.
Call them set 0, 1, and 2

There are subscribers that grab one set of the data depending on their geographical region (in SQL terms the HOST_NAME()).

One of the servers was setup with the wrong set.  (0 instead of 1).
I have corrected the function responsible so that servers should now get the correct set.
What do I need to do to force that server to dump its current data and grab the correct set all over again?

Marking the SQL server for a reinitialise from subscription did not do the trick :(


texts: After running VBA, I lose exclusive access to the database

texts:
I wrote some VBA to import an excel spreadsheet easily into a table in my database. This way, the user just clicks a button on a form, and answers some questions about the file to be imported. The problem is that after the import is completed, I try to modify anything else in the database and I'm presented with the message "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." I then have to exit the database and reopen it to make any changes.
I've done this before without problems on other versions of Access. So I don't know if I have a typo someplace, or something has changed from Access 2013 to 2016 that's causing the problem.
Currently, the Database is located on my local machine. Ultimately, this will be moved to a SharePoint site and split into 2 access files. I don't want to do that until I have it mostly working because I don't have anyplace I can upload to SharePoint without a lot of people having access to it.

Private Sub ImportNewTB_Click()
On Error GoTo ImportNewTB_Click_Err 'If an error occurs anywhere along the way, make sure you still clean up the memory before quiting

Dim OwssvrFile As DAO.Database 'This is the open connection to the file
Dim OwssvrInfo As DAO.Recordset 'This is the recordset for the teachers information
Dim fileName As String 'This is the name of the file being opened
Dim dbs As DAO.Database
Dim CurrentTBDB As DAO.Recordset 'This is to make a connection to our current table
'Dim ExcelHdrs(0 To 20) As Variant 'This is an Array with the headers from the Excel file.
Dim numRecords As Integer
Dim WksName As String
Dim TimeStamp As Date

fileName = getOpenFile() 'Use the function I built in the Module
If fileName = "" Then GoTo ImportNewTB_Click_Exit 'If they didn't select anything, then just give up on life and exit

WksName = InputBox("Enter the name of the worksheet: ", "Worksheet Name", "owssvr")
numRecords = InputBox("Enter the number of records in the Worksheet: ", "Num Records", 2412)
WksName = WksName & "$A1:BE" & numRecords + 1

'Once we have a real file, open it already
Set OwssvrFile = OpenDatabase(fileName, False, True, "Excel 12.0; HDR=YES;")
'Create Recordset from the excel file.
Set OwssvrInfo = OwssvrFile.OpenRecordset(WksName)
OwssvrInfo.MoveFirst 'Goto the first line of the recordset

Set dbs = CurrentDb
Set CurrentTBDB = dbs.OpenRecordset("SELECT * FROM CurrentTB")

TimeStamp = Now()

Do
    With CurrentTBDB
        .AddNew
        .Fields!EntryDate = TimeStamp
        .Fields!ProjectName = OwssvrInfo.Fields(0)

        .Update
    End With

    OwssvrInfo.MoveNext 'All of that for entry 1, only 2000 more lines to go
Loop Until OwssvrInfo.EOF

' Tidy up, This closes everything out and releases the memory
ImportNewTB_Click_Exit:
On Error Resume Next 'Basically this says, if there's an error, I don't care, do this anyway
MsgBox "Input Complete!"
OwssvrInfo.Close
OwssvrFile.Close
CurrentTBDB.Close
Set CurrentTBDB = Nothing
Set dbs = Nothing
Set OwssvrInfo = Nothing
Set OwssvrFile = Nothing
Exit Sub

ImportNewTB_Click_Err: 'This produces an error message if one exists
MsgBox Err.Number & " " & Err.Description, vbCritical, "Error!"
Resume ImportNewTB_Click_Exit 'Make sure we still clean up before leaving
End Sub


texts: Ring topology support of MongoDB ReplicaSet and Sharding?

texts:
Just curious if the following ring-like topology supported/recommended or is right for a sharded/replicaset cluster.

version: mongodb 2.4.5

hosts(all vms)

#[MongoDB Config]

mongodb_roles="replset1 replset2 replset3 replset4 replset5 config route"

replset1_ips="192.168.1.27 192.168.1.28"
replset1_port=27011
replset1_shard="s1"

replset2_ips="192.168.1.28  192.168.1.29"
replset2_port=27012
replset2_shard="s2"

replset3_ips="192.168.1.29 192.168.1.30"
replset3_port=27013
replset3_shard="s3"

replset4_ips="192.168.1.30 192.168.1.18"
replset4_port=27014
replset4_shard="s4"

replset5_ips="192.168.1.18 192.168.1.27"
replset5_port=27015
replset5_shard="s5"

config_ips="192.168.1.15 192.168.1.16 192.168.1.17"
config_port=20000

route_ips="192.168.1.15 192.168.1.16 192.168.1.17"
route_port=30000

Thanks,


texts:
The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the DB always to fail with the following exception:

com.ibm.db2.jcc.a.pn: The current transaction has been rolled back because of a deadlock or timeout. Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95

The full log is ([#1] indicates the 1st thread/transaction, [#2] is correspondingly the 2nd):

SQL: create table line (id integer generated by default as identity, line_number integer not null, constraint line_pk primary key (id))
[#1] SQL: insert into line (line_number) values (1)
[#1] SQL: insert into line (line_number) values (2)
[#2] SQL: insert into line (line_number) values (1)
[#2] SQL: insert into line (line_number) values (2)
[#1] SQL: insert into line (line_number) values (3)
[#2] SQL: insert into line (line_number) values (3)
[#1] SQL: insert into line (line_number) values (4)
[#2] SQL: insert into line (line_number) values (4)
...
[#2] SQL: insert into line (line_number) values (1608)
[#1] SQL: insert into line (line_number) values (1608)
[#2] SQL: insert into line (line_number) values (1609)
[#2] SQL: insert into line (line_number) values (1610)
[#2] SQL: insert into line (line_number) values (1611)
...
[#2] SQL: insert into line (line_number) values (1654)
[#2] SQL: insert into line (line_number) values (1655)
[#1] [org.epo.lifesciences.slice.db.DBTest] Thread #1 has failed
org.springframework.dao.DeadlockLoserDataAccessException: StatementCallback; SQL [insert into line (line_number) values (1608)]; The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95; nested exception is com.ibm.db2.jcc.a.pn: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTransl
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLException
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:519)
     ...
[#2] SQL: insert into line (line_number) values (1656)
[#2] SQL: insert into line (line_number) values (1657)
...
[#2] SQL: insert into line (line_number) values (3999)
[#2] SQL: insert into line (line_number) values (4000)
[#2] Thread #2 completed

It looks like that lock space is exhausted much earlier then transaction log is exhausted. Solutions which I see (and seem to work):

Increase lock space, in particular trick the parameters:

locklists – maximum storage for lock list configuration parameter
maxlocks – maximum percent of lock list before escalation configuration parameter

Commit more frequently.

Both of them are not strictly acceptable because:

One need to know in advance what should be the size of locklists which needs to be increased with the growing number of insertions. Thus DB2 cannot automatically adapt to increasing workload efficiently. Also DB instance needs to be restarted when this parameter is changed.
Often commits also mean that there should be a way to roll back all previous commits if something goes wrong at certain point of time. That complicates the application as it needs to implement "revert" logic (savepoints won't help). Moreover intermediate commits will be visible to data requester, so application needs also to track such "uncompleted" tasks and hide the data from client.

The test application works OK both for MySQL, HSQL and MSSQL as is (with no further tricks). So I believe there should be a way to make it working on DB2 without code refactoring and staying on SQL'92 compliant level. Is it possible with enterprise-level DB2? Perhaps I miss something trivial, any feedback is welcomed.


texts: Upgrading from SQL Server 2008 R2 SP1 to SP3

texts:
I have to install the Service Pack 3 on an SQL instance running with the SP1. No test, the install is directly on the production server. Well this is not a complex task and the topic has been already ask for other version, but I'd like to be sure to not forget some steps and having feed back from experienced dba is always welcome. Here my custom planned checklist :

1.list all bugs that have been addressed in the Service Pack.
ie:https://support.microsoft.com/en-us/kb/2509302

2.Take a virtual machine backup and backup all the databases.

3.Disable SQL Server agent.

4.Apply the Sp.

5.Restart server/instance if needed.

6.Check for the Service Pack level of the SQL Instance.

7.Check for all entries in SQL Server Error Log about the Service Pack update and ensure there are no errors written.

8.Refering step N°1, check for eventuals bug issues.

9.Enable SQL Server Agent.

From the client side :

Check for any compatibility problem between his software and the SP3.
After the install, check for client connectivity between DB/software.

Is there any step you'll find pertinent to add/remove/modify to this ?


texts: MSSQLSERVER service does *not* start, port 1433 in use

texts:
Sorry to raise if this has been asked before.
The SQL Server service is defined as manual, whenever I start it I get (event viewer):

Server TCP provider failed to listen on [ 'any'  1433]. Tcp port is already in use.
The SQL Server (MSSQLSERVER) service terminated with service-specific error
Only one usage of each socket address (protocol/network address/port) is normally permitted.

Odd thing both my ethernet and WiFi adapters have ipv6 disabled and it has ran fine for ages.
Environment details:

Windows 2008-R2;
SQL Server 2012 BI SP1;
all MSFT patches applied.


texts:
I'm designing a web application (using Django) for a client that will need a fairly large database behind it. I've never worked with databases that handle large amounts of data before so I'm hoping to get some help.

The basic layout of this database is simple:  there are users, each user can have multiple child entries, and each entry is composed of a description and up to 25 images. Each user is permitted to have 40 entries per month.

So far this is pretty simple. The client says he wants the initial release to be able to accommodate his expected 20,000 users. This web application will be used by individuals within an organization, not the general public. So his estimate of initial users is very accurate.

If the timescale we're looking at is a year, that means that I need a database that can accommodate 20,000 user rows, 9,600,000 entry rows, and 240,000,000 images.

The first thing I want to know is how to store these images. I think the best thing to do is to store URLs to the images and actually store the images themselves outside of the database. But what is the best way to store these URLs? Should I have a comma separated list within each entry? This seems like a simple approach to me, that way I would only need two tables, one for users and one for entries. The entries table would contain an ID, a description, and a comma separated list of image URLs that can be processed by Django to serve content. Again, I've never done anything like this before so suggestions are very much appreciated.

My more complicated question is about scalability. My client says he can expect to have these 20,000 users, but that if his service takes off that number might go as high as 250,000. Is there a way to implement a SQL style database that can handle 20,000 users here but also leave room for expansion, rather than simply buying hosting and using a database such that the possible 250,000 users would be accommodated from the start?


texts: Sql Server 2016 maintenance plan with weekly full backup and 4h differential backups

texts:
I am new to SQL server management, I'm mostly a developer tasked with managing a SQL Server :-(

That aside, please bear with me.

Right now I have made a maintenance task to create a Full Backup every day at 2:00 am, and afterwards delete any backup older than 2 days. That however leaves a whole workday worth of data to be lost in case something went wrong at the right (wrong?) time. So to minimize data loss without without making performance take a hit, I', trying to create one weekly Full Backup and then a Differential Backup every 4 hours. I am however puzzled at how to do this from the management studio, or anywhere else for that matter.

So my question is:

How can I configure this backup 'policy' on my Server?


texts:
We have a
Windows Server 2008 R2 Enterprise x64
Oracle 10.2.0.3
Adempiere - ERP

We had a power breakdown at 03.00 AM.
At 07.30 AM we started the server
At 08.00 our colleagues observed that the ERP is not functioning
We started Adempiere
ERP was not able to connect to Oracle DB
We entered EM Console and we checked the DB status
The listener was functional - up and running
DB was shotdown in progress
We tried to abort shot down via SQL Plus using command :
shutdown abort
Nothing happen
We tried shutdown - no result
We tried shutdown immediate - no result
Then we made Windows Shutdown from start menu
After restart of the server we checked in Windows Services.MSC the Oracle running services
Oracle JoobscheduleORCL was disabled, the rest were running. We set this one on automatic
The listener was functional - up and running
DB was shotdown in progress
We tried to abort shot down via SQL Plus using command :
shutdown abort
Nothing happen
We tried shutdown - no result
We tried shutdown immediate - no result
We tried to restart the Oracle Services with no result
We searched on the forum.oracle.com and we found out that the problem could be that the undotablespace is corrupted.
We tried to fix the table
We used the following command line :

alter database datafile  'xxxxx\undotbs01.dbf'  offline drop;

http://www.dbas-oracle.com/2011/06/recovery-from-undo-tablespace-block.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582

The current situation is :

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> startup
ORA-01031: insufficient privileges


texts: Replication has failed; how to get going on again?

texts:
I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of the boxes.

Now, replication has stopped and when checking the logs on both boxes, I see this message:

CDT FATAL:  requested WAL segment 0000000100000224000000FA has already been removed

It's the same segment over and over again. From my Googling, it would seem that the replication server is trying to retrieve this segment from the master, but it's not there anymore. Ok, but how to get around this? Do I need to make a fresh backup and rsync that over to the slave? Is there an easy way to get the slave back in sync?


texts: Aggregate all joined rows with where clause

texts:
Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects.

Each project has an ordinal assigned, 1-5 (step_number).

I would like to provide a summary for each user group. Each user group summary should include the users' projects and their unique progress of the users' projects and the steps they are at. Importantly, I would like to also like to select users projects with projects for a specific ordinal step. In other words, I would like to include the sibling projects for a users' steps.

For example, below, I would like all users aggregated by user_groups.id with projects that are step 3.

+------------+---------------------------------+
| User Group | Summary (Project Name: 1, 2, 5) |
+------------+---------------------------------+
|          1 | proj1: 2, 3, 5; proj2: 3, 5…    |
|          2 | proj33: 2, 3; proj 44: 1, 3     |
|          3 | proj55: 2, 3, 4; proj 66: 1, 3  |
+------------+---------------------------------+

The initial instinct was to write a query like this:

SELECT
  user_groups.id,
  STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary
FROM user_groups
INNER JOIN users ON user_groups.id = users.user_group_id
LEFT JOIN projects ON users.id = projects.user_id
WHERE step_number = 3
GROUP BY user_group.id

However, that only returns projects where the step number is 3 and not the other steps for that user's other project. That makes sense, but how do I include all the sibling projects for that user_id?

I also tried different joins, but I am guessing and clearly unclear on how to solve this problem with SQL.

Here are some simplified table definitions

                                Table "public.user_groups"
  Column    |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('user_groups_id_seq'::regclass)
 created_at | timestamp without time zone |

                           Table "public.users"
   Column      |            Type             |                       Modifiers
---------------+-----------------------------+-------------------------------------------------------
 id            | integer                     | not null default nextval('users_id_seq'::regclass)
 user_group_id | integer                     |
 gender        | character varying(255)      |
 created_at    | timestamp without time zone |
 first_name    | character varying(255)      |
 last_name     | character varying(255)      |

                                  Table "public.projects"
   Column     |            Type             |                           Modifiers
--------------+-----------------------------+-------------------------------------------------------
 id           | integer                     | not null default nextval('projects_id_seq'::regclass)
 user_id      | integer                     |
 step_number  | integer                     |
 created_at   | timestamp without time zone |
 updated_at   | timestamp without time zone |
 start_dt     | date                        |
 end_dt       | date                        |
 project_name | text                        |


texts: How can we correlate linked server queries running to their source instance?

texts:
We have multiple versions of SQL Server from 2008R2 (I know) to 2016.  There are many linked server queries.  I'm trying to build a tool that will tell me the queries executing on a target server that come from linked server queries executing on another source server.

QUESTION #1)

Can I rely on sys.dm_exec_sessions.program_name = 'Microsoft SQL Server'?

I've hesitated to go that route so far and have been relying on looking at the sql text:

dest.text LIKE '%"Tbl[0-9][0-9][0-9][0-9]"%'

QUESTION #2)

Once I have found the linked server queries running on my target instance, I want to traverse back to the original calling instance.  Is there an easy way to do this?

Right now, I'm heading down the path of looking for the target instance in the sql text of queries running on the source host(s):

AND dest.text LIKE '%$LinkedServerName%'

(Not the variable expansion - this is a powershell snippet, not straight TSQL)

Still, I may have some interesting logic to truly say, "This query is what is causing the query to execute on my linked server" - not to mention the perf hit of the leading wildcard search into my sql text.

Ideas?

Thank you,

-Peter


texts: mysql table replication on just some records?

texts:
I have a big database which contain a table with about 26 million records. Now i need to make some reports on it from a special period of time.

So how to make a replication from that table from a special time, for example, just show replicate the records from 2015-01-01 till now?


texts: Why the value changes when I assign it to a variable?

texts:
I have a table like this:

// mytable
+---------+-------------+
|   id    |    ban      |
+---------+-------------+
| int(11) |   BIT(11)   |
+---------+-------------+
| 1       | 01111111000 |
| 2       | 01111111111 |
| 3       | 00000000001 |
+---------+-------------+

As you see, the value of ban column is a binary number (its datatype is BIT(11)). and when I select it, the value is still a binary number, like this:

SELECT ban FROM mytable WHERE id = 1;
-- output: 01111111000

But when I assign it to a variable, surprisingly the value changes, like this:

SELECT ban INTO @ban FROM mytable WHERE id = 1;
SELECT @ban;
-- output: 1016

Well what happens when I set 01111111000 into @ban? Why it will be changed (1016)?

You know? In reality, each digit (bit) of that 11-bit number (01111111000) refers to something. For example, the second bit determines user's limitation on voting. I mean it the first digit (right to left) is 1, it means the user can vote, if it is 0 it means the user is banned for voting.

Anyway, I don't need to get a decimal like this 1016. I need to assign and keep exactly that 11-bit number to @ban variable. Because I need to check it like this:

IF ( IFNULL((@ban & b'10' > 0), 0) < 1 ) THEN
 -- user can vote
ELSE
-- user cannot vote
ENDIF;

So there is different action for each digit. That's why I don't want a 10-based number like 1016. I want a bit-based number like this 01111111000 in that variable.

To make it more clear, here is the task of third digit:

IF ( IFNULL((@ban & b'100' > 0), 0) < 1 ) THEN
 -- user can flag
ELSE
-- user cannot flag
ENDIF;

See? the third digit (of that 11-bit number) determines user's flagging ability (limitation).

Ok, in conclusion, how can I assign a bit value to a variable without converting its datatype (which seems converting will be happened automatically, so how can I avoid that)?


texts: update/delete records concurrently without impact to other process for handling huge data?

texts:
I tried to googling first, but i didn't get it . . .

So, What are best practices for update/delete records concurrently without impact to other process handle to huge data(>5 millions records)?

Please point to me.

Thanks you.


texts: Evict node from SQL Server 2016 Cluster on Windows Core

texts:
I have 4 nodes cluster on Windows Core with SQL Server 2016 installed on them (4 instances, 1 instance on each node).

I would like to remove 2 nodes from the cluster (2 instances aren't needed).

I can't run the SQL Server SETUP.exe utility in order to Choose "Remove node from a SQL Server failover cluster".

I can open Failover Cluster Manager on another server remove it from there, OR use WAC (Windows Admin Center) to perform the action.

However, most of the links I read about evicting SQL Cluster nodes are using that GUI utility (Setup.exe)

Will evicting via the WAC/Failover Cluster manager without the 'Setup.exe' step  will hurt the cluster's validity ?
What is the correct way to perform it ?

Many thanks,
Roni.


texts: Output parameter not set if stored procedure fails inside a TRY / CATCH

texts:
In SQL Server 2008 (but also in 2014). Let's consider a procedure that has an output parameter. This procedure may produce an error (and will in the following example). I note that the behaviour of the output parameter is not the same if we call the procedure within a TRY / CATCH block.

Example:

create procedure test_output @result varchar(10) output
as
begin
    set @result = 'hello'
    raiserror('This is an error', 16,1)
    set @result = 'error'
end

If we launch the procedure the simple way:

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

we get (and I'm fine with it):

  Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
  This is an error
  Result is: error

If the procedure is now in a try/catch block:

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
    exec test_output @result = @res2 out
end try
begin catch
    set @error_message = error_message()
    raiserror(@error_message, 16,1)
    print 'Result is: '+ isnull(@res2, 'empty')
end catch

we get (and I'm upset):

  Msg 50000, Level 16, State 1, Line 28
  This is an error
  Result is: empty

The error message is OK, but the output parameter is now NULL. If, in a TRY...CATCH context, the execution is halted immediately after the RAISERROR, I would have expected the output value to be set to hello.

Why is it so?


texts:
I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the database.

As far as I can see I have 3 options:

Add a column in the table for each extra value
Add a linked table which references the original table and has records only where we need to store a value
Use the XML data type in the original table and store all of the values in this.

Are there any other options that I've not considered?

I'm trying work out the pros and cons of each method. As far as I can tell 1 would be the easiest and 2 would take the least amount of space but I'm struggling to find many resources for 3.


texts: Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

texts:
So lets say 100, 200, 400, 600 are company_ids and there are 6 administrators who each have their company_ids. The owner of the table User_Info is Admin who is also the owner of other tables and can view, edit, delete, update any tables, procedures, or anything. Has all rights.
Now Admin has created users MKarki and other such in the User_Info table with respective company_ids.
There are other tables inserted using Admin's account in Oracle 10g like customer, employee, shipment and so on. Each of those table have "Company_ID" as a foreign key.
A user when logs into their own session can insert, update, delete the records from the table customer, employee or any other based on the company_id it belongs to. MKarki cannot select, insert, update, or delete records which has company_id 200 (this one is already taken care of).

Example: So the User_Info table has these columns:

Company_ID F_Name    L_Name    Username    Owner   Level_Of_Security
100        Mala      Karki      MKarki     Admin        5
100        Piya      Karki      PKarki      Admin        5
200        Giya      Khadka     GKhadka     Admin        5

Now customer table looks like this:

CUS_ID  Company_ID  F_Na  L_Na    Owner   Sec_Level
23       100        Hor    Loki    MKarki       4
22       100        Bholu  Kam     PKarki       4
34       200        Golu   Pop     GKhadka      4

Based on the tables above: When MKarki logs into her own Oracle account and executes the Select statement: Select * from Customer. She should be able to view all records that have company_Id 100. (This is what I need to know as how this can be implemented???)

So now if PKarki logs into oracle 10g then she should be able to see both of the customer values for company_ID 100 even if one belongs to MKarki since PKarki is also from same company ID.
Now we need to implement a security policy function for the above business policy.

The environment is Oracle 10g and it is for Virtual Private Database

I am unable to get any rows even after setting the context with package, package body, and procedure. (No rows selected or "Error: Policy predicate has errors" if I tweak the Sys_Context a little bit)

I have also created the context with package like this:

Create or replace Context CompAdmin USING Pac_Comp_Adm;

Create or Replace Package Pac_Comp_Adm IS Procedure Get_Company_ID; END; /

Create or Replace PACKAGE BODY Pac_Comp_Adm
IS PROCEDURE Get_Company_ID IS V_Company_ID Number;
BEGIN
Select  Company_ID INTO V_Company_ID FROM User_Info
Where Company_ID = SYS_CONTEXT('USERENV','SESSION_USER');

DBMS_SESSION.SET_CONTEXT('COMPADMIN','Company_ID', V_Company_ID); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END; /

Create or Replace FUNCTION Helo_Comp_ID ( P_SCHEMA_NAME IN varchar2, P_OBJECT_NAME IN varchar2)
Return varchar2 IS V_Hello Varchar2(300);
BEGIN If User = 'ADMIN'
then V_Hello := '';
Else
V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';
END IF;
RETURN V_Hello;
END; /

After this I am setting the policies using the function above as follows:

exec DBMS_RLS.ADD_Policy     ('ADMIN','CUSTOMER','Row_Upper_Sec','ADMIN','Helo_Comp_ID','SELECT,INSERT,UPDATE,DELETE');


texts: Help writing a query to transform rows with json data into 1 download file

texts:
I want to write a query for a table like this:

id  job_id  data (bytea)
--------------------------
 1       1  {"hello": "John"}
 2       1  {"goodbye": "Moon"}
 ...
100      2  {"also": "Hi"}

I want to be able to ask:

> Give me all the data for job #1

and have the text file automatically built like this:

{"hello": "John"}
{"goodbye": "Moon"}

My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it. This seems to work:

    select encode(data, 'escape') from mytable where id='1'

But I would rather not do 2 queries (one to get all rows - and another to get data). Can I issue a single query, and somehow paginate, one row at a time? Number of rows can be large, e.g. 100k.

Any tips to point me in the right direction are much appreciated.

P.S.: Order is not that important to me. E.g. this is ok too (if this helps):

{"goodbye": "Moon"}
{"hello": "John"}


texts: PLSQL Package - Catching Errors in procedure (Oracle)

texts:
I have a problem with my package (and procedure) not catching exceptions.

I have the below code:

CREATE OR REPLACE PACKAGE process_orders
IS
    PROCEDURE add_order (
        order_num   NUMBER,
        cust_num    NUMBER,
        rec_date    DATE);
END process_orders;
/

CREATE OR REPLACE PACKAGE BODY process_orders
IS
    PROCEDURE add_order (
        order_num   NUMBER,
        cust_num    NUMBER,
        rec_date    DATE)
        IS
        status      VARCHAR2(50);
    BEGIN
        INSERT INTO orders VALUES (order_num, cust_num,
            NULL, rec_date, NULL);
    EXCEPTION
        WHEN OTHERS THEN
            status := SUBSTR(SQLERRM,1,50);
            INSERT INTO orders_errors VALUES (SYSDATE,
                order_num, status);
    END;
END process_orders;
/

If in sqlplus I execute: exec process_orders.add_order(2000,1/0,'25-DEC-91'); (notice the div by 0 error in the 2nd arg) I do not get anything inserted into the orders_errors table (and no errors except for the div by 0 error).


texts: GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order

texts:
I read from the documentation that the behavior of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. I was curious: say I have one procedure p1 which requests a lock with GET_LOCK("lock", 0); so that multiple calls to this procedure don't interfere with each other. The procedure releases the lock before terminating. I have another procedure p2 which requests a lock using GET_LOCK("lock", 10);.

Suppose there is a flood of calls to p1, and somewhere in the middle, p2 is called, like this:

...
CALL p1();
CALL p1();
CALL p2();
CALL p1();
#lock released here from first p1() call: who gets it?
...

Theoretically, the second procedure shouldn't be affected much: it waits for the lock to be released and since GET_LOCK in p1 has a timeout of 0, p2 should be the only one requesting the lock when it is released. However, I don't think I can expect this ideal behavior to be true. GET_LOCK from the first procedure ends up taking some small but non-zero amount of time of course, and since the lock isn't assigned in order of request, is its possible for the lock to be passed down successive calls to p1 and p2 has to wait?


texts: left outer join - sort operations in the query plan - any ways of tuning this simple query?

texts:
while working on the query below in order to answer this question:

How to query chart data in a database agnostic way?

Having the following tables:

CREATE TABLE [dbo].[#foo] (
[creation]  DATETIME                         NOT NULL,
[value]     MONEY                                NULL,
[DT]        AS (CONVERT([date],[CREATION])) PERSISTED)

-- add a clustered index on the dt column
CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT)
GO

and this other table for joining:

create table #bar (dt date primary key clustered)
go

the loading of data into these tables can be found here.

But when running the following query:

WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt,
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0)

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

--get rid of the duplicates and present the result
SELECT
THE_DATE,
THE_NUMBER_OF_RECORDS_ON_THIS_DAY,
THE_TOTAL_VALUE_FOR_THE_DAY
FROM RADHE
WHERE THE_ROW = 1

I get something like this picture below, which is exactly what I was looking for.

But the execution plan generated has several Sort and Nested Loops Operations, as you can see on the picture below.

The full query plan can be found here.

this is a very simple operation, a left outer join between 2 tables, the indexes are already ordered, and therefore I was wondering if I could simplify the query plan.

alternatively, I could change the query code.

why exactly do we need nested loops 2 times and sort 2 times in the query plan?


texts: SQL-Server performance metrics: What exactly is Lock Waits/Sec (or: Why is it that unrealistically high?)

texts:
I have a production SQL-Server, which seems to have increasing numbers of request taking extremely long (median response time is low, average response time is growing steadily). Therefore I started monitoring internal stats like the "lock waits per second".
The production instance returns nearly 2.000.000 Lock Waits/Sec, the test instance only ~3.000 Lock Waits/Sec. (This discrepancy is of course not unexpected, since the production instance has all the load, the test instance only a few requests of devs testing the new version of the application.)
Lock Waits/Sec is defined by Microsoft as "Number of lock requests per second that required the caller to wait."
This definition doesn't help me at all though, how could there be 2 million lock waits per second?? There are no even 2 million DB-requests at all, by far? Also this number fluctuates only by single digits over periods of time with very different load; if it is per second, it should move much, much more, shouldn't it?
Therefore my question: What exactly does Lock Waits/Sec stand for, and how do I have to interpret the numbers above?


texts:
I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my simplified db schema (also shown below).
Table products has a stock field. This field must be calculated from products' total purchases and sales quantity.
SUM(purchases.quantity) - SUM(sales.quantity)
I'm looking for simple, performant and most important race conditions safe solution. My current (but not final) idea is to use db triggers here. But I have no mind how to implement them the right way.
CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY,
    name varchar,
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    PRIMARY KEY (id)
);

CREATE TABLE purchases (
    id INT GENERATED ALWAYS AS IDENTITY,
    quantity INT NOT NULL DEFAULT 0,
    product_id INT NOT NULL,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE sales (
    id INT GENERATED ALWAYS AS IDENTITY,
    quantity INT NOT NULL DEFAULT 0,
    product_id INT NOT NULL,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);

INSERT INTO products (name)
        VALUES('Super Power');

INSERT INTO purchases (product_id, quantity)
        VALUES(1, 2), (1, 3), (1, 2);

INSERT INTO sales (product_id, quantity)
        VALUES(1, 1), (1, 2), (1, 2);

SELECT
    *
FROM
    products


texts: Datetime columns in sys.dm_db_mirroring_connections are an hour behind

texts:
I have database mirroring set up on my SQL Server instance.

If I run SELECT * FROM sys.dm_db_mirroring_connections I get information about the mirroring connections, however, all the time columns in that table (connect_time, login_time, last_activity_time) are all an hour behind what they should be.

For example, last_activity_time is currently showing 2019-06-06 15:07:30.444 but the when I do SELECT GETDATE() I get 2019-06-06 16:07:48.324 (obviously a few seconds elsapsed between the two) which is the correct time now.

Where does this DMV get it's times from and how can I ensure they use the correct time?


texts:
I have installed Ola's scripts:

https://ola.hallengren.com/scripts/CommandLog.sql
https://ola.hallengren.com/scripts/CommandExecute.sql
https://ola.hallengren.com/scripts/IndexOptimize.sql

and ran:

EXECUTE dbo.IndexOptimize
@Databases = 'mydbnamehere',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

It took about 20 minutes. and after that I ran this SQL statement:

SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
   DB_ID(N'mydbnamehere')
 , NULL
 , NULL
 , NULL
 , NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30

And I still had about 19 rows with an avg fragmentation between 99 and 40 %

99,65075669383
99,0767455279861
98,7414187643021
98,5574148874784
98,4420080784766
97,8723404255319
97,5903614457831
97,4522292993631
96,8911917098446
96,551724137931
95,4356846473029
87,5
76,1904761904762
75
51,063829787234
50
50
40
33,3333333333333

Did I make a mistake in my parameters?
It did run and rebuilded and reorganized several indexes. It took down the list from about 25 to 19 rows. (4x foreign keys, 3x primary keys, 4 indexes and the rest without a name)


texts: How to compute number of concurrent application sessions using SQL Server table storing session data?

texts:
I have a vendor application which stores session data in a SQL Server 2008 database table. Columns it includes are things like the sessionId, the user's IP address, the datetime stamp when the session was created (i.e. user logs in), and the datetime stamp of when the session was destroyed (i.e. user logs off or system logs off user).

My objective is to analyze all the records in this table and figure out the average number of concurrent sessions across all records.

Now, unfortunately, the session destroy date is not accurate, for reasons outside the scope of this question. Therefore, I'm using a very rough estimate for the duration of the session: 1 hour. And I can always change the number later after I have the design phase sorted out.

I'm certain I can put together a stored procedure to get the average number of concurrent sessions, but I was hoping I can accomplish it with a query.

To simplify things here, let's assume there are 5 records in the table and all were created on the same day and are in GMT time:

sessionId  sessionStart  sessionEnd  Accumulative # of Concurrent Sessions
1          12:00         13:00       1
2          12:15         13:15       2
3          12:30         13:30       3
4          12:45         13:45       4
5          13:00         14:00       4

At 13:00, the first session is destroyed. The number of concurrent sessions stays at 4, since sessions 2 through 5 still exist.

The question is how I can write a query which will output the average number of concurrent sessions? Can it be done? I imagine it would involve multiple joins on the same table, but I haven't quite figured out yet where to start.

The table has under a million records. I have access to a 2012 box and can copy the table there if it helps.


texts: How to use "WHERE" and also "WHERE NOT EXISTS" in the same query and table?

texts:
I am trying to add both WHERE and WHERE NOT EXISTS in same query. Below mentioned query1 is working fine, but it returns all the t1 data which does not belong to company1. So I tried query2, but no luck. How can I get the desired result?

1.

select * from t1 where not exists
(select * from t2 inner join t3 on(t3._id = t2.department_id)where t2.category_id = t1._id AND t3._id = 27 AND t3.company_id = 1)
 ORDER BY "ctsName"

2.

select * from t1 where company_id = 1 AND where not exists
(select * from t2 inner join t3 on(t3._id = t2.department_id)where t2.category_id = t1._id AND t3._id = 27 AND t3.company_id = 1)
 ORDER BY "ctsName"


texts: How do you delete a row that contains a certain value from a specific column across all tables in a database?

texts:
I'm trying to create a query that searches the database for tables that have the column "GtNumber" that has a row numeric value of "2". I want to then remove these rows so that there are no rows within the database that contain a value of 2 for GtNumber.

Is there any way to get this all done with one query?


texts: Performance Tuning for Huge Table (SQL Server 2008 R2)

texts:
Background:
I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test.

Table Features:

No of Dimensions ~ 45
Measures ~ 30
Non-additive measures and other columns~ 25
Current data size ~ 200 Million (2 years data)
Time View: 3 different Month views: Fiscal/Calendar/Adjusted (i.e same row can fall in different months based on which view one is looking for)
Only One view will be required at a time by a user. (ie. only one Month Column will be used in the query, it's stopping us to do partitioning on time view)
Indexes: 1 Clustered Index on the Natural Keys (8 columns).Created 3 covering Non Clustered Indexes one on the each Month column including few Dimension SKs (FKs) and all the measures).
Indexes are huge(total 190 GB) because of this.
Space is not constraint (1 TB allocated)
64 GB of RAM available in server.
Table compression also done.

Requirement:
Queries on this Fact table should give result within 30 seconds (General queries select sum(measure) joining few Dims group by Dim Values). Reports are directly done on top of this Fact table.

Issue:
Any query which includes columns available in the Index works fine, but if we include any other columns which are not in the include..It sucks. It takes more than 5-10 minutes. Can any one suggest some solution where it works fine for any dimension/column we select. Can Index view help in this situation?


texts: MySQL Server is Restarting automatically after some minutes

texts:
I am getting an Issue after everey 2-3 minutes

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    57

I am really not able to find why it is happening. I saw to my error log it says

120419  0:36:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 93884, file name /EBSmnt/mysql/mysql-bin.000233
120419  0:36:04  InnoDB: Started; log sequence number 1692 2597076235
120419  0:36:04 [Note] Event Scheduler: Loaded 0 events
120419  0:36:04 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.43-enterprise-gpl-advanced-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (G

Can Anybody please tell How to handle the Scenario.It is on one of the Production Server.

EDIT

RAM is of 7GB.

And InnoDB Configuration in my.cnf are

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 3G
innodb_data_home_dir = /lvm/mysql
innodb_data_file_path = ibdata1:80G:autoextend
innodb_log_buffer_size = 8M
innodb_log_file_size = 1300M
innodb_log_files_in_group = 3


texts: mysqldump: Does --lock-tables=false work with MyISAM tables?

texts:
So, I have to check and come with a backup solution for a very busy mysql server (with tables over 5 GB). Due to resources constraints, they choose to set all tables with the MyISAM engine. Currently, they use a bash script with calls to mysqldump for every single table, and they use the command --lock-tables=false, because it is imperative for them to be always online, but I really think that's not working the way they intended.

My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?

Also, the only non-locking solution I can think of is to make a slave replication to properly lock the tables and use mysqldump or XtraBackup. My concern with this is getting a hit on performance, since it'll be writing twice and both instances will have to run on the same machine. Is there any other alternative for a non locking solution for MyISAM ?


texts: SSPI error and DotNetMethod exception while connecting to the SQL DB using powershell script

texts:
I wrote the following script with the original connection names and credentials.

$SQLServer = "servername"
$SQLDBName = "dbname"
$SqlQuery = "select * from tbl_Employee "

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;
User ID= xyz; Password= abc; trusted_connection=true;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

After hitting enter I get the following error:

Exception calling "Fill" with "1" argument(s) :"Cannot generate SSPI context."
At line:1 char:17
+$SqlAdapter.Fill <<<< ($DataSet)
+CategoryInfo: NotSpecified: (:)[],MethodInvocationException
+FullyQualifiedErrorId: DotNetMethodException

I am trying to connect to the client's database using the given credentials but with the above code, its taking the windows credentials by default.


texts: Unexpected very slow user count in WordPress

texts:
I have a Wordpress page that returns timeout error every so often. When reviewing the MySQL "slow queries" log, I could notice that there is an extremely slow query coming from Wordpress, but I don't know exactly which module:
SELECT  SQL_CALC_FOUND_ROWS wpe_users.*
    FROM  wpe_users
    WHERE  1=1
      AND  wpe_users.ID IN (
        SELECT  DISTINCT wpe_posts.post_author
            FROM  wpe_posts
            WHERE  wpe_posts.post_status = 'publish'
              AND  wpe_posts.post_type IN ( 'post', 'page', 'attachment',
                        'wp_block', 'products' )
                          )
    ORDER BY  display_name ASC
    LIMIT  0, 10;

The query appears two or more times, with State Copying to tmp table and Time of up to 53815.44 (secs? 15 hours? it's crazy!). And it makes the Mysql process consume 557% of CPU and 8.7% of RAM

The wpe_users table contains almost 3 million entries and the wpe_posts table contains 68,000 entries. The wpe_comments table is also starting to cause problems as it contains 361,000 entries.
I use pagination for comments and posts but not for users. The question is, how can I fix the problem without disabling paging? How can I identify where the user query is coming from if I don't show users anywhere?
What have I tried?

I updated Wordpress to the latest version available.
I tried to identify where these queries come from using the "Query Monitor" plugin but they don't appear.
I verified the only 3 plugins that I have active and none of them generate such queries.
I closed the administration panel and killed said processes to see if the queries were generated by the administration panel but they start again...

I don't have much experience with MySQL configuration. I am using MySQL 5.5.62 with Plesk panel on a VPS 5GB RAM 6 Cores
SHOW CREATE TABLE wpe_users
CREATE TABLE `wpe_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`),
  KEY `user_email` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=3492068 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN wpe_users query
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
| id | select_type        | table     | type  | possible_keys                | key              | key_len | ref  | rows    | Extra                        |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
|  1 | PRIMARY            | wpe_users | ALL   | NULL                         | NULL             | NULL    | NULL | 2780982 | Using where; Using filesort  |
|  2 | DEPENDENT SUBQUERY | wpe_posts | range | type_status_date,post_author | type_status_date | 164     | NULL |    2265 | Using where; Using temporary |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+


texts: SQL Server A network-related or instance-specific error

texts:
I have a windows service that performs a lot of hits to database. Sometimes the database connection is down and I got exception "A network-related or instance-specific error". I got this behavior on two different networks and database servers. In both cases the windows service and the SQL Server are on two different machines.

Is it a normal behavior that SQL server drop connections when there is a lot of hits?

What are chances?


texts: How To Restrict a Column To Only A Default Value

texts:
When I add a "default value" expression to a column, if I do not specify a value for that column during an insert, the column automatically gets its default value. But if a value is supplied during insert, that value is used. How do you restrict a column to only have a default value so that an insert statement cannot write an arbitrary value in that column?

EDIT:
The default value I have in mind is NEWID()


texts: Retrieve additional columns in recursive CTE

texts:
This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.

I manage to do it with two separate queries but there has to be a way to just use one and get the count of the children:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;

How would I pull content column from the parent comment in this fiddle?

http://sqlfiddle.com/#!15/158ea/15


texts:
We have a table with more than 10million rows.

We are altering its column type like this:

ALTER TABLE MY_TABLE ALTER COLUMN MY_COLUMN VARCHAR(3)

What would be the fastest way to do this? Alter column needs to touch EVERY  row right? Is there a way to make this faster?

According to questions and answers here, I'm thinking about, create a new table and migrate data, or create a new column with VARCHAR(3) and migrate only the data inside this column and drop the old column.

Obs. It's taking more than 3hrs.


texts:
We are developing a product system and I was curious to what industry standards were for many Boolean statements.

In our system we have some, what you could call main, bit fields. E.G. isBackordered, isDiscontinued.

We also have a lot of internal processes and debated on using product tags to denote if this product should be processed that way or not. E.G. isManufacturable, isLaserable

I was wondering if it is better in the industry to just create everything as bit fields or acceptable in the industry to use tags for special circumstances like that.


texts:
We have a problem with just one of the databases on our SQL2008 Server. For some reason recently the log file is just growing and growing.

The server runs the following backups

Weekly - Full
Daily - Diff
Hourly - Transactional

I have followed the information here (Why Does the Transaction Log Keep Growing or Run Out of Space?) but without any luck and have confirmed the following:

The database is in FULL recovery mode
Backups are occuring as expected (Hourly, Daily, Weekly)
The log file is full, it's that that there is space that can be recovered
Looking at the log_reuse_wait_desc returns 'NOTHING'

The website associated with the database is the most used site we have, but the database and usage is still small (under 5Gb of data) a few hundred users a day, but the Log file gets over 10Gb after a couple of days.

Anyone any idea what could be causing this? The only way I can find to get around this is to switch to 'SIMPLE' recovery mode. Shrink the file, switch back to 'FULL' then do a new Full backup to start a new chain.

Thanks for any advice you can provide


texts: Is there a way to know if a package is still used ? (I tried with RedGate SQL Search and I found those in SERV-XXX and Maintenance Plans in various Job Steps, but for those in Data Collector I got nothing)


texts: Unconnected tables for predefined options in MySQL DB

texts:
Is it okay to live some tables not connected to anything?

I have a table with a set of discounts for deals. For each deal, discounts can be summed up, so I don't see the point in connecting the "deals" table and "discounts" table by discount id. I also can't just create INT field for a discount in the "deals" table because discount should be predefined in "discounts" table. How to better organize such database?

Here is the screenshot of the scheme of the DB I have came up with so far.

Same problem with services. Multiple services can be provided under one deal. Is there a type which can contain multiple strings or numbers?


texts: Table with clustered index implicitly sorting by unique nonclustered index

texts:
I have a table that captures the host platform that a user is running on. The table's definition is straightforward:

IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[ActivityPlatform] (
        [ActivityPlatformId] [tinyint] IDENTITY(1,1) NOT NULL
        ,[ActivityPlatformName] [varchar](32) NOT NULL
        ,CONSTRAINT [PK_ActivityPlatform] PRIMARY KEY CLUSTERED ([ActivityPlatformId] ASC)
        ,CONSTRAINT [UQ_ActivityPlatform_ActivityPlatformName] UNIQUE NONCLUSTERED ([ActivityPlatformName] ASC)
    ) ON [Auth];
END;
GO

The data it stores is enumerated based on a JavaScript method that uses information from their browser (I don't know much more than that, but could find out if needed):

When I perform a basic SELECT without an explicit ORDER BY, however, the Execution Plan shows that it is using the UNIQUE NONCLUSTERED index in order to sort instead of the CLUSTERED index.

SELECT * FROM [Auth].[ActivityPlatform]

When explicitly specifying the ORDER BY, it correctly sorts by ActivityPlatformId.

SELECT * FROM [Auth].[ActivityPlatform] ORDER BY [ActivityPlatformId]

DBCC SHOWCONTIG('[Auth].[ActivityPlatform]') WITH ALL_LEVELS, TABLERESULTS shows no table fragmentation.

What am I missing that could cause for this? I thought so long that the table was created on a clustered index, it should automatically sort by it implicitly without need to specify ORDER BY. What is SQL Server's preference in choosing the UQ? Is there something I need to specify in the table's creation?


texts:
Ok, so here it goes. I have a table called employee. then I have another table called languages. I need to copy the value of PK of employee table to foreign key in Languages table, rather than entering it manually. So, whenever a new employee is added, I need the value of the EmployeeID, so that I can add the language details. Suppose, the employee knows 2 lanuages, I just add two language details for the same employee.

So, in the end I need something similar to the picture

I hope I could explain the problem this time
Regards


texts: How to setup Galera Cluster when nodes are located multiple sites?

texts:
I am setting up a web app that runs on 2 sites and shares db, Initially I setup 1 Galera Cluster with all nodes in it,

but sometimes SST on bringing node up takes lot of time and service start times out, I am not sure if something is better than rsync for reliable SST on a network with small bandwidth.
then I thought of using either of 2 scenerios but I am not sure if they are good idea or not ?

EDIT: the arrow in 2nd image from proxysql to proxysql is bidirectional.
in the later I have 2 Galera Cluster on each site, and both have master-to-master replication via proxysql , (why from within proxysql so if 1 master node goes down we can update the other master node of Galera Cluster.)
What is your opinion on this ?


texts:
First I`m sorry for my bad English.

My problem is I have one virtual server with PHP backend and one server with one single-table MySQL database. I don't want the file size of the database to exceed 2 gigabytes. I want to switch the data insertion to the second server after the first one will be overflowed, then to the third server after the second one, etc.

How to determine that the maximum size of the database file is exceeded, and switch the data insertion to the second server?

Thanks for advance.


texts: PostgreSQL slow query log entries contain no query details, only duration

texts:
I have a PostgreSQL RDS instance hosted in AWS. I have set the log_min_duration_statement setting to 1 second. When checking my log for slow queries I found the following six entries which don't contain any query/statement:

Does anyone have any idea why this happened? How can I find out which slow queries caused these log entries?

UPDATE Thanks to jjanes for pointing me in the right direction. I found that the "log_statement" option causes duration and statement to be logged separately. Here it is in Postgre docs: https://www.postgresql.org/docs/9.1/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT


texts:
I have very specific question.
I have seen the instructions on how to backup and restore mysql database using SSH but they barely explain the process when backing up on one server and restoring on another server.

What i need is backup the mysql database on server A and restore the mysql database on server B. How do i do this using SSH command line?

I have seen this http://www.ipragmatech.com/backup-restore-mysql but nothing on backing up and restoring on different servers.

Thanks.


texts: Aggregates per half hour intervals within a time span

texts:
Consider the following table:

CREATE TABLE dbo.Events (
    [ClientID] int,
    [EventID] int,
    [EventName] varchar(50),
    [StartDate] datetime,
    [Duration] bigint
)

INSERT INTO dbo.Events(ClientID, EventID, EventName, StartDate, Duration)
VALUES (1, 1, 'Login', '2016-11-27 01:30:00.000', 86400000),
       (2, 1, 'Login', '2016-11-27 00:30:00.000', 86400000),
       (3, 1, 'Login', '2016-11-27 00:00:00.000', 86400000),
       (4, 1, 'Login', '2016-11-28 23:30:00.000', 172800000),
       (1, 2, 'Lock', '2016-11-27 23:30:00.000', 3600000),
       (4, 2, 'Lock', '2016-11-28 23:30:00.000', 1800000)

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

I need to see how many clients were Logged In, and how many clients were Locked, and for how long during a given date range. The date range can span a day/week/month/year, and when it does span more than a day, it should still bucket by half hour.

In the end, the results for a query on the date range 2016-11-28 00:00:00.000 -  2016-11-30 00:00:00.000 would look like so (apologies if I miscalculated the SumDuration or CountClients somewhere, I did this manually but I think it is correct):

EventID  EventName  HalfHour  SumDuration  CountClients
--------------------------------------------------
1        'Login'    0:00      5400000      3
1        'Login'    0:30      3600000      2
1        'Login'    1:00      3600000      2
1        'Login'    1:30      1800000      1
...      ...        ...       ...          ...
1        'Login'    23:00     1800000      1
1        'Login'    23:30     3600000      1
2        'Lock'     0:00      3600000      2
2        'Lock'     0:30      1800000      1
...      ...        ...       ...          ...
2        'Lock'     23:00     1800000      1
2        'Lock'     23:30     3600000      2

The ellipses are for the sake of brevity in repeated results, but there should be 48 results per Event (1 for every half hour in a day). You will also notice that the Duration needs to be calculated during that half hour. It is not in the example data, but it is possible to have a Duration end at any time, not just a half hour mark. Last, I should note the result for 'Login' at 23:30. ClientID 4 had a Login event for both 2016-11-28 and 2016-11-29 during the 23:30-0:00 half hour. So, the Duration sums both, but only counts the Client once.

I have the following, which gives me the CountClients and SumDuration, but doesn't break it up into half-hour increments:

select E.EventID,
       E.EventName,
       count(*) as CountClients,
       sum(datediff(millisecond, I.StartDate, I.EndDate)) as SumDuration
from dbo.Events as E
  cross apply (
              select max(T.StartDate) as StartDate,
                     min(T.EndDate) as EndDate
              from (
                   values(@StartDate, @EndDate),
                         (E.StartDate, dateadd(millisecond, E.Duration, E.StartDate))
                   ) as T(StartDate, EndDate)
              ) as I
where E.StartDate < @EndDate and
      dateadd(millisecond, E.Duration, E.StartDate) > @StartDate
group by E.EventID,
         E.EventName;

How would I go about breaking this up into half hour increments?


texts: How to wrap long lines when SELECTing SQL text columns?

texts:
I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length.

From:

SELECT * FROM test;
test_id |                                  text
--------+-----------------------------------------------------------------------
      1 | Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris lorem

To:

test_id |              text
--------+-----------------------------
      1 | Lorem ipsum dolor sit amet,+
        | consectetur adipiscing elit+
        | . Mauris lorem


texts:
Intro
I have derived this question from my previous question Single Drive vs Multiple Drives

After getting a very detailed answer from a very helpful member RolandoMySQLDBA and learning the fact that multi drives can help a lot in some cases. I started to look into multi drives for my setup which is just a single bit compared to what I found.

More Info
I found quite huge storages like

http://www.newegg.com/Product/Product.aspx?Item=N82E16811152212

While some videos show even GBs of write speed of these kind of equipment

http://www.youtube.com/watch?v=Xjgw2ENYF6w
http://www.youtube.com/watch?v=PniGlSI900k
http://www.youtube.com/watch?v=I8ToJ4Blw74

Real Question
Providing that there is enough RAM to hold indexes, Can MySQL or other open source RDBMS (not enterprise graded RDBMS like Oracle because its running on SAN is quite common) can run on these beasts easily?

BECAUSE they contain true multi drives setup and dozens of heads are available.

I am sure it would not be that simple because if it would have that easy then why most enterprise setups use commodity servers. So, what problems are faced using these extreme storage solutions?


texts: Inline Scalar UDF Under RCSI - Can Results Differ

texts:
SQL Server 2019 introduces Scalar UDF inlining, aka "Froid." This ".. embeds [scalar UDFs] in the calling SQL query."

Previously, scalar UDFs ran under their own execution context, separate from the surrounding query's. One consequence of this is that under read committed snapshot isolation (RCSI) the function could see a different set of values than the containing query sees (link).

Is it possible that a query containing a scalar function, when running in RCSI with concurrent writes, could produce different results depending on whether the function is in-lined or not?


texts:
I'm running SQL Server 2008 R2 and have it set to start the service in the background on startup of the machine. When this happens, what is the process that it goes through to startup?

I believe that the master database loads first, but what order do the other databases load in? How is this determined?

EX: I have databases a1,b1,c1. Does a1 load before c1?

How would I go about setting the priority of a database (e.g. load c1 before b1...)?

I'm looking for a behind-the-scenes look into what happens when the program starts, I am not running any queries on it. Thanks.


texts: SQL Server Buffer Pool Utilization under VMWare

texts:
I recently migrated a physical SQL Server box (2012) over to ESX (5.1) simply for ease of administration.  It is and will be the only guest on the server.  As expected there was a marginal performance hit that for the most part is only noticeable on startup.  Here in lies my issue.

I have noticed that under virtualization the buffer pool takes memory much much much slower then its physical counterpart.

Under the physical install within 2 days running SQL server had allocated all 120gb of memory allocated (max server memory).  However, with practically the exact same setting running in the hypervisor (I have also given the guest full reservation of the memory) it has taken 7 weeks to reach 50gb. This was also painfully slow something like a Gb rise a day, this translated into a few days of slow queries.

So while I have seen this on multi guest boxes and attributed it to memory pressure, I am confused at why it is happening on a single guest box.  I know vmware will compress and dedupe memory, however most of this is unique data.

So my question is:

1) Why exactly is this happening ? I'm interested in the exact mechanism that is causing this.

2) Is there a method to start SQL server with is full memory allocation ?  I remember something like a traceflag for this but havent been able to find anything for 64bit.  And is this necessary ?

Thanks in advance.

Another thing I noticed in the first few days after a restart is that the PLE stays very low in the 500 - 900 range this increases as the buffer pool grows.


texts: MYSQL - Select statement ordered by count of matching rows from another table


texts: database design help with multiple table relationships

texts:
Been scratching my head on how to set this up and looking for your help. Trying to create an API and not 100% how my tables should relate to one another.

So lets say I have users, food, and food details.  The user is able to post what he wants based on the food he selects. But they all should be able to be viewed in a single view if needed. not pizza only or pasta only. Each post should be able to stream together. pizza posts + pasta posts...

Here is what i am thinking below-

User Table: user_id, email, pass
Post Table: post_id, user_id
Pizza Table: post_id, peperoni, sausage, peppers
Pasta Table: post_id, sauce, cheese, onions

Would this way make sense? But then how would i know what pizza or pasta table to look into with just a post_id... This is the part i am a scratching my head over.

Thanks in advance.

Citti

Edit:

api uri would be something like: /api/v1/posts

Example output for this would be a feed per post:

post_id 1 | Steve wants Pizza with Peperoni and Peppers @ 12:40
post_id 2 | Steve wants Pizza with Sausage and Peppers @ 12:35
post_id 3 | Kevin Wants Pizza with Peperoni, Sausage, and peppers @ 12:20
post_id 4 | Steve wants Pasta with Sauce and Cheese @ 12:15


texts: How to update mongodb collection with ssh from a shell script?

texts:
I am trying to update a mongoDb collection via ssh from a shell script but getting the below error :

  MongoDB shell version: 2.6.4
  connecting to: localhost:27017/b2b_tempdb
  2017-12-05T11:04:51.153+0000 SyntaxError: Unexpected token :

Below is the full command :

ssh -n happy@localhost  " mongo --host localhost:27017  --authenticationDatabase admin b2b_tempdb --eval 'db=db.getSiblingDB( \"b2b_tempdb\" ); db[\"b2b_tempdb.activity.preferences\"].update({},{ $set: {'codeId':\"happy.l2l-09l2l121\"} }, {multi:true})' "

Kindly help me in getting it resolved .
Thanks in advance for the help .


texts: Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

texts:
Using PostgreSQL.

The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a single column I'm constantly going to have to break this apart and search via date_trunc.

While PostgreSQL allows one to create an index based on a function, explain shows that this is not being used for conditions using date_trunc.

Table:

development2=# \d reservations
                                        Table "public.reservations"
     Column      |            Type             |                         Modifiers
-----------------+-----------------------------+-----------------------------------------------------------
 id              | integer                     | not null default nextval('reservations_id_seq'::regclass)
 tops            | integer                     | not null
 price           | numeric                     |
 state           | integer                     | not null
 restaurant_id   | integer                     | not null
 seller_id       | integer                     |
 customer_id     | integer                     |
 created_at      | timestamp without time zone | not null
 start           | timestamp without time zone |
Indexes:
    "reservations_pkey" PRIMARY KEY, btree (id)
    "start_date_index" btree (date_trunc('day'::text, start))
Foreign-key constraints:
    "reservations_customer_id_fk" FOREIGN KEY (customer_id) REFERENCES users(id)
    "reservations_restaurant_id_fk" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
    "reservations_seller_id_fk" FOREIGN KEY (seller_id) REFERENCES users(id)

Query:

development2=# explain select id,start from reservations where date_trunc('day', start) = '2014-03-14';
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on reservations  (cost=0.00..1.01 rows=1 width=12)
   Filter: (date_trunc('day'::text, start) = '2014-03-14 00:00:00'::timestamp without time zone)
(2 rows

)

Not even sure how to approach the time search, to_char + IMMUTABLE I guess...


texts: Is there a FOR XML variant that can add attributes with arbritrary values?

texts:
I'm trying to create a query similar to this

SELECT
'foo' AS Detail,
'bar' AS Detail,
'baz' AS Detail
FOR XML PATH ('Header')

To produce something like this-

<Header>
  <Detail type="A">foo</Detail>
  <Detail type="B">bar</Detail>
  <Detail type="C">baz</Detail>
</Header>

and it seems like there's no way to create attributes without assigning column values into them.


texts: I have a high volume MariaDB 10 server thousands of databases of with over a million tables.  Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other layers of caching. Is there a rule of thumb for when to disable query cache for better performance?


texts: Can two SQL “Always on” nodes have different datafiles position on disk?

texts:
I have two Always on instances SQL server 2014 configured on two servers (primary read/write, secondary only sync). First instance has 13 db and the second has only one. I need to move all the transaction log datafiles from a disk to a new one. All the transction logs reside in a disk (for example D: on both nodes).
I would like to stop the sync from the secondary, move the transaction log position (for example from D: to E:) and then resync all.
In this way I will have a primary node working with transaction log on old D: and secondary node working on a new E: file system.
Can someone help me to understand if SQL Always on can work with different datafiles paths on the two nodes?
Second question (if the first is feasible): all the services will continue to run on the first node. When I will try to resync the secondary node, how much confidence can I have to achieve the activity without reconstructing the secondary from a backup?
[UPDATE]
Try to answer the below question for more detail about my second question.
I have those steps in my mind (but I don't know if they are correct)
Preliminary status: SERVER1 primary node. SERVER2 secondary. Two instances running.
One DB at a time cycle those three steps:

Stop sync on SERVER2 (SERVER1 working).
Move the transaction log from the old position to the new one on SERVER2 (It should be something like detach-update config-move datafiles-attach)
Start Sync

After all do this two:

Failover both instances to SERVER2
Do the same on SERVER1

The above steps should guarantee that all the instances continue to work without service outage (in my mind).
I'm trying to figure out how much safer it can be to perform those steps instead of creating a new disk and adding new tranlog datafiles on it without moving the old one (only stopping autogrowth).
Thanks so much


texts: How can I enable symbolic link support in my MySQL Server. When I run show variables like 'have_symlink'; I got result as disabled.


texts: How to control execution plans in SQL Server

texts:
I've faced a problem with SQL Server generating poor execution plans.
I have 3 databases with the same structure and the same set of procedures.

My query looks like this:

select
d.Id as DirectoryId,
d.MeetingId  as MeetingId,
--...
from dbo.MeetingsDirectories d
left join dbo.Meetings m ON m.Id = d.MeetingId
left join (select * from dbo.fnLatestDirectoryData()) dat on d.Id = dat.MeetingDirectoryId
...

The difference is

The first db cached following execution plan. Which is terrible because basically it executes fnLatestDirectoryData as many times as there are rows after I joined dbo.Meetings with dbo.MeetingsDirectories. My evidence is that it number of executions corresponds with the number of actual rows that come into final nested loop in upper left corner. But execution of fnLatestDirectoryData is way too expensive and if number of rows is ~3k it starts to lag (about 32sec)

I just recompiled procedure by altering it. And instead of doing clustered index scan it inserted table spool right before going to nested loops. I think it just multiplied results of fnLatestDirectoryData and cached them. But it's size is about 1000x1000 which worries me. Nevertheless the execution time dropped to 8sec.

In the the third case I added hash hint to left join and observed that my function called once and execution time dropped even further. I also tried adding merge hint but they both work relatively the same time.

left hash join (select * from dbo.fnLatestDirectoryData())

My questions are:

Can I prevent degradation of execution plans from second stage to first, so I always have fixed execution plan for each stored procedure? I don't have enough evidences, but I feel like sometimes SQL Server decides to recompile execution plans, and if they are bad my app performance drops significantly.
Is the third option is a right way to grantee that I would have only one execution of fnLatestDirectoryData?
Can I reliably fix the order of joins? I see joins happen in different order, so if I do something like group them in pair (select (select from tableA join tableB) join tableC) it will actually help.


texts: Database size limit in Sybase Express edition

texts:
I'm currently testing out the Sybase 16.0 Developer edition on RHEL. Turns out the limitation on the max. concurrent connections is a real problem for me, so I'd like to switch to the Express edition.

My main concerns are the following:

The documentation mentions a limitation of 5 GB as max. DB size. Does this mean I can have a 20 GB device with 4 x 5 GB databases inside or that an entire server in Express edition won't go past 5 GB total?
Will it be easy to migrate devices created in Developer edition into Express edition?


texts:
I run:

show grants for root@localhost;

and I see

 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

From mysql docs:

https://dev.mysql.com/doc/refman/5.5/en/proxy-users.html

  This enables the external user to be a proxy for the second user; that
  is, to have the privileges of the second user. In other words, the
  external user is a “proxy user” (a user who can impersonate or become
  known as another user) and the second user is a “proxied user” (a user
  whose identity can be taken on by a proxy user).

But I am not understanding what they mean. I got the system from another worker who left the job and want to make sure if everything is secure and do not know if this grant is even needed. But if it does not make any security issues, I can leave it.

Can somebody explain in more simple way?

Update:

How can I connect as as another user? I was trying various combinations, of username password, but I cannot make it connect.

For example I used user root, but password from another user, which did not work. Tried another user and root password, also did not work.

Update:

Or this maybe means that root user can connect as other user? How to do that at least for example if so?


texts:
Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via

alter user %username% account unlock

and instead of typing the same line multiple times for each user I believe some cycle construction can be used but don't know how to write it in PL/SQL. Can somebody advice on this?


texts: How do I list all databases and tables using psql in a pipable format?

texts:
This question deals with getting a list. So I get this:

$ psql --list
                                    List of databases
      Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
----------------+----------+----------+-------------+-------------+-----------------------
 testdb1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 odoo           | openerp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 odoo-demo      | openerp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 odoo8          | openerp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

I could use psql --list|grep UTF8|awk '{ print $1 }' to get a list of just the databases. Can psql also give me a clean useful list directly?


texts:
I looked on the internet and I found your post on the stackexchange.
I will try to explain in more detail what we really need
I do not know well to write English
It is a system for airporttaxi

id   date       time        direction

7   2014-04-15  21:35:00    to
8   2014-04-15  15:20:00    from
9   2014-04-15  17:35:00    to
10  2014-04-15  08:15:00    to
11  2014-04-15  14:40:00    to
12  2014-04-15  11:35:00    to
13  2014-04-15  14:35:00    from

from / to = from airport / to airport

I need a mysql script we calculate the difference between the time of driving the same day
example

script needs to sort the first time

08:15:00
11:35:00
14:35:00
14:40:00
15:20:00
17:35:00
21:35:00

then calculate the difference between each ride, starting with the first

I want something like this

start        end          dif_min

08:15:00     11:35:00     200
11:35:00     14:35:00     180
14:35:00     14:40:00     5
14:40:00     15:20:00     40
15:20:00     17:35:00     135
17:35:00     21:35:00     240
21:35:00     00:00:00     25

I much appreciate it if you help me, and I hope you understand what I want


texts:
I'm starting to design a data warehouse for a company. The first questions we're trying to resolve are regarding their support ticketing system. My initial schema is as follows

Now one of the questions we want to ask is historically how many tickets were active at anyone time.

The problem is that a ticket will be created one day but maybe open over several days / weeks / months without being updated or created again, meaning we only have one fact record when the ticket was created even though the ticket was opened every day.

I'm not sure what the best way of handling this is, a thought that's just come to me is this.

At the start of the day any ticket which hasn't been marked as resolved has another ticket entered into the fact table at the start of each day regardless of whether there's any updates? Does that seem like a sensible solution? Or am I missing something simpler?

Any feedback on the schema as it is would also be greatly appreciated, as we still have time to change it and get it right from the start.


texts: Get count for different columns with different conditions from same table into one result table?

texts:
I have a table, action using postgresql:
is_jump | is_throw | is_punch
true      true        false
false     true        false
false     true        true

These are all booleans.
I want to get a count for each of the actions (when true) into one result table in one query:
is_jump_count | is_throw_count | is_punch_count
     1              3                1

I can get the count for one of them at the time, that's the easy part, but, how can I combine it all in one query so that I get one result table with the counts by column like shown here?
I have implemented it this way, but, I wonder if there's a better way to accomplish this given that in the end I think I'll end up with about 15 actions to track count for...:
SELECT a.ct is_jump, b.ct is_throw
FROM
( SELECT COUNT(*) ct FROM action WHERE is_jump=true and user_id=6 ) a,
( SELECT COUNT(*) ct FROM action WHERE is_throw=true and user_id=6 ) b;


texts: When is dm_exec_procedure_stats updated?

texts:
I just created a new proc and ran it, but it is not showing up in my query:

SELECT  schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time LastElapsedTime, ps.object_id
FROM    sys.dm_exec_procedure_stats ps JOIN
        sys.objects o ON ps.object_id = o.object_id
WHERE   DB_NAME(ps.database_id) = 'MyDatabase'

Should I not expect a new proc to appear in the results of this query immediately after running the proc for the first time?

I've run the proc I created several times now, and I've run sys.dm_exec_procedure_stats several times as well yet it still doesn't appear. I have the assumption that running a stored procedure will always cause a procedure plan to be cached for it if one does not already exist... but wondering if that's a correct assumption now?


texts: Does the transaction isolation level affect indices as well?

texts:
Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages?

E.g. using ISOLATION LEVEL READ UNCOMMITTED, what effect (if any) do the ALLOW_PAGE_LOCKS or ALLOW_ROW_LOCKS on an index have??

ALTER INDEX IX_FirstName ON Employee
SET (ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)

I can't seem to find a definitive answer anywhere - the MSDN documentation on the transaction isolation levels really only talks about the data pages....