texts:
In designing a task database with the usual set of tables (Users, Tasks etc), I'm wondering if there is an accepted way to track changes to fields in the task table?

I could just provide a text log of what changed and when, but can I somehow store the old and new values in their natural type? I could have one field for each datatype (int, datetime etc) and then a bitmask to denote which one has relevant data in it, but it seems like it would be a bit clunky to report on. Another idea was to have one table for each datatype (IntEvents, DateTimeEvents etc) but that looks problematic too. Presumably this is a fairly common problem, has anyone else solved it in an elegant way? It needs to be compatible with SQL Server and MySQL at least, and Postgre as well ideally.

EG:

TABLE: Tasks
ID int pk
Name varchar(255)
Description text
Created datetime
DueBy datetime
Creator int -> users
Owner int -> users
Status int -> statuses
Priority int -> priorities
// etc

How can I create an events table to track changes to all these different datatypes?

The simple text log table could be:

TABLE: Events
ID int pk
UserID int
TimeStamp datetime
Fieldname varchar(32)
OldValue varchar(255)
NewValue varchar(255)

But this is a bit difficult to report on (eg show me all the events where a DueBy date was moved back by more than N days)

I've looked at table design to track significant events associated with an item but I'm not sure it's quite the same problem (unless I've misread it).


texts: Multi-site Failover Cluster Instance for HA and DR to alwaysON Availability Groups

texts:
In my company we have chosen to implement Multi-site SQL Server Failover cluster.
Basically Data center A  has 2 nodes (Active/Passive) and Data Center B geographically located acts as the DR. I believe there will be a SAN replication from  Data Center A Data Center B.

If in future we want to implement a Multi node  AlwaysOn and availability group HADR solution. By that i mean (Data center A  has 2 active node and 2xSANs, and DBs are part of a group. Data center B has an  Active node and its own SAN which act as a DR site).
Can you guide me on how to implement this process or articles that explain this in details.

How can I migrate from a geo-clustreing to an High Availability Disaster Recovery? and what should I consider?


texts: How to close mongodb idle connection(active: false)?

texts:
I have an application running on node and using the mongodb library to establish connection and closing it. But the problem is, from the mongo serverStatus() command i can see that there is still 2k+ current connections still available.

> db.serverStatus().connections
{ "current" : 2993, "available" : 835867, "totalCreated" : 220841 }

and most of it are having the active: false status.

My questions is, is there a way for me to force close these idle connections which after some time will affect my application with an timeout error.

I tried using the killOp() command but these only works if there is still opid value listed in these idle connections.

Appreciate some feedback on how can handle this kind of situations.


texts:
Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1
For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status “VISIBLE ONLINE” in sys.dm_os_schedulers

Worker threads (tasks) can run on a scheduler and those threads (tasks ) can have various status like based on a waiting queue for e.g.

a) suspended in a waiting queue for resource to be available
b) runnable queue (mostly FIFO unless controlled by Resource Governor) or
c) currently running on a scheduler.

My question is say for e.g. a task (SELECT query in this case) first goes to a scheduler (let's call it scheduler A) but found that it has to wait until pages are brought into memory (IO Operation) it is now put into waiting queue in suspended state, once the IO is complete than that particular worker thread (tasks) is signaled and has been put into runnable queue in order in which it was arrived into that queue.

Does the task always have to go to scheduler A to finish its quantum or it can switch its context and can now run on any idle scheduler (let's call it scheduler B) with no runnable tasks? If there is a context switch what is its effect?


texts: Call a stored procedure for every select result

texts:
I have an stored procedure to delete from a long series of tables. It uses one main ID as parameter. I have to delete a bunch of rows based on a view that show that lot of tables. I want to apply the deleting procedure to every filtered result from that view. Some thing like:

SELECT id FROM view WHERE another_id=100;

And then apply the stored procedure to those results like:

CALL deleteProcedure(id);


texts: SQL Link Connection OLE DB provider Query Execution Failed

texts:
I have created linked connection in SQL and by which I am connecting Oracle server using OLE DB provider "OraOLEDB.Oracle".

SQL Server = 2008 R2 Oracle = 10g

Normally, When I am executing Insert or Select query it works fine, but some times the same query does not execute well and failure. Same query If I execute again then it works fine.

The Error message is as below.

The OLE DB provider "OraOLEDB.Oracle" for linked server "ABC" could not INSERT INTO table "[ABC]..[M_SCM].[MY_TBL]". err src line: 23 SP_MY_PROCEDURE

Can any one suggest the cause of the issue? and let me know if any solution.


texts: How do I determine if a SSIS transform is non-blocking, partially blocking, or fully blocking?

texts:
There are several blogs which claim to categorize SSIS Transforms into blocking (asynchronous), non-blocking(synchronous) and partially blocking(asynchronous).

When looking into the specific question:
Is multicast synchronous(non-blocking) or asynchronous(partially blocking)?

One resources claims async:  "The Multicast is an asynchronous (also known as partially blocking) transformation" source:  http://social.technet.microsoft.com/wiki/contents/articles/7392.ssis-multicast-transformation.aspx

and another syncronous:
https://jorgklein.com/2009/04/14/ssis-lookup-is-case-sensitive/

Other resources claim the DQS Cleansing transform is a non-blocking transform, but it seems to me to be partially blocking.

Please don't answer with a link to a list of transforms categorized by type.  The answer to this question will hopefully enable a more rigorous method to PROVE a correct answer.

As the partially-blocking and blocking transformations will move data to new buffers as opposed to the non-blocking which operate on the buffer in place, I suspect that the solution will be to watch buffer creation during transform execution, but I am unsure (a) if this will produce the definitive answer and (b) how to do this.

I believe that a non-blocking transform will not cause a new execution tree to begin, so it is possible that the answer lies in logging the PipelineExectionTrees and PipelineExecutionPlan.  This may differentiate between synchronous and asynchronous, but may not differentiate between partially blocking and fully blocking.


texts: Is it possible to create an SQL transaction that rollbacks on syntax errors?

texts:
In may day-to-day job, I often have to create SQL files containing minor changes (no DDL operation usually) in order to migrate those to production databases easily.

The problem is, even with syntax checking, I often run into issues with syntax errors, which I notice only when I try to run said queries.

Even if I wrap those in transactions, insertions and deletions made before the point of the error are performed regardless.

Is there a way to rollback on syntax errors in a transaction?


texts: SQL Server Agent 2012 Job Schedule Report

texts:
I am looking for a report that will display graphically what jobs are scheduled on the server and when. My goal is to very quickly be able to glance at it and find a time where a new job will not interfere with other jobs.

I have been looking at the "Standard" reports but have not seen anything...I'm pretty sure I could make one...but just wanted to be sure I wasn't missing something if the wheel had already been invented and packaged with SSMS.


texts:
I am trying to use an Oracle OleDb source in my SSIS package with no luck, every time I try to connect to the server I get the error:

  ORA-12154 tns could not resolve the connect identifier specified

, knowing that:

1 I installed Oracle 11g express edition

2 I added an entry for the alias in my tnsnames.ora file

3 ORACLE_HOME variable pointing to my Oracle installation

4 Path & TNS_ADMIN variables pointing to the directory containing the tnsnames.ora file

5 I am using a Windows Server 2012

What am I doing wrong?


texts: Get normalized representation of SET datatype

texts:
In MySQL you can SELECT SET values from a table using this syntax:

CREATE TABLE `test_set` (
  `int` int(11) DEFAULT NULL,
  `set_2` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT * FROM test_set WHERE set_2 & 1;

or equivalent using = operator.

For a given table:

int | set_2
-----------
12  | a,b
-----------
34  | c,b

I'd like to make a query that would return:

int | set_2
-----------
12  | a
-----------
12  | b
-----------
34  | c
-----------
34  | b

How would such query look in mysql/mariadb? According to the book I am reading, it should be something in the lines of:

SELECT T.int, S as set_value
FROM test_set T, T.set_2 as S

Similarly, set_gen() function can be applied to reverse normalized representation to the one containing sets:

SELECT T.int, set_gen(T.set_2)
FROM test_set T
GROUP BY T.int

What is the syntax for denormalization in mysql/mariadb?


texts: SQL Server Maintenance Plan multiple database backups in a single folder

texts:
I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day.

For ex. I need to backup db1, db2, db3 and dump all these 3 .bak files into a single folder with a date.

Is this possible?

Any help would be really help. Thanks in advance.


texts: Restore only one datafile in SQL Server 2008 from partitioned database

texts:
We have a partitioned database that I'd like to get copied over on a semi-regular basis to another server for testing updates. Since the partitioning is based on a date column, the most current data exists in one file.

Is it possible to restore just one datafile from the backup to the other server?

Complicating issue: we use Symantec's BackupExec. If this is possible using plain ol' backup from SQL Server, I can probably get that done, though.


texts: Quorom Configuration for 3 Node Windows cluster

texts:
We have 2 node onsite and the 3rd node on DR  and there is no disk witness. Due to maintenance I set the DR site to be primary and the other 2 nodes to be manual in asynchronous mode  and after both the 2 node went down together and the whole cluster went down due to quorum could not achieve disk majority. The Dynamic quorum failed I believe since both the on site 2 nodes went simultaneously.

Since this is a 3 node cluster so the node majority quorum will be used, my question is: if I face similar scenario when the on site 2 node will go down together in that case how can I keep the cluster operational on one node / DR site..


texts: Why is my SQL .mdf file 30GB when my data is only 4GB

texts:
I used this query to find the size of the data in each of my SQL tables and then aggregate it into one number:

SELECT sum(t.totalspacemb) FROM (
SELECT
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages,
    sum(a.used_pages) as UsedPages,
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND
    i.index_id <= 1
GROUP BY
    t.NAME, i.object_id, i.index_id, i.name) t

I've truncated millions of rows of data from a large audit table and subsequently shrunk the db and the files, but the size of the .mdf stays the same.  I switched to simple logging and shrunk again which reduced the log file, but the .mdf is still the same.  Why am I unable to recover the free DB space?


texts: How to configure SSL mongodb connection in yml file spring boot?

texts:
I was trying to setup ssl mongodb connection from spring boot but I couldn't able to find a way. How can I add pem and ca file to below connection?

Please note I am using replica set

data:
     mongodb:
         authentication-database: admin
         database: <database>
         host: <host>
         password: <password>
         port: <port>
         username: <username>


texts: Trying to avoid bad MySQL database design - can't figure out how to avoid too many columns

texts:
I am pretty much a beginner having only created a few small php web applications relying on a MySQL database. I am now working on a somewhat larger project. I am in the planning stages of setting up the database and am having difficulty wrapping my mind around one issue.

The database will be used for Lego. The main tables will be parts and sets. Under the sets table I will require a listing of the part numbers that are in each set as well as a listing for the quantity of those parts in the set. My limited knowledge of how to set this up would see me setting up something like...   part_1, part_2, part_3... etc & qty_1, qty_2, qty_3... etc. The problem with this is that there will be some sets that have hundreds of parts which makes the method I can think of unfeasibly messy. Also I would like to have the functionality for a user to be able to query the database and recover all sets that have a particular part. With the setup I have in mind I can't see this as possible as it would potentially require a search through 100+ columns.

I am sure there is a simple solution that my noobness cannot see and one which I cannot find online via the keyword searches I have been making (I am 4 hours into that search and have learned a lot but not what I wanted...). Any advice would be very greatly appreciated.

Apologies for not giving you code. The website will be done in PHP with a MySQL database. At the moment I am at the stage of drawings in a notebook and do not have anything coded down.


texts: Can I construct a trigger that deletes existing rows before inserting new ones in SQLite, which lacks FOR EACH STATEMENT?

texts:
Here’s my SQLite schema:

CREATE TABLE IF NOT EXISTS deps (
    target TEXT NOT NULL,
    user TEXT NOT NULL,
    dependency TEXT NOT NULL);

CREATE INDEX IF NOT EXISTS targetUser ON deps (target, user);

In words, the deps table (a dependency tree) is intended to have multiple
rows for each (target, user) pair—all rows with the same (target, user) pair are dependencies of target for some user.

In my application, a user only batch-updates the dependencies of a target. By “batch” I mean that I would (1) DELETE all rows containing the (target, user) and then (2) INSERT the new rows fresh.

I would like a trigger to combine these two steps in the SQLite database, so that before I insert new rows for a given (target, user), the database deletes all old rows for this pair. I came up with this:

CREATE TRIGGER IF NOT EXISTS clearOldDeps BEFORE INSERT ON deps
    BEGIN
        DELETE FROM deps WHERE target = NEW.target AND user = NEW.user;
    END

but alas! SQLite triggers are all FOR EACH ROW, and it lacks FOR EACH STATEMENT, so if I use this trigger above and insert some rows for a given (target, user) pair, each row inserted deletes the rows that were inserted just before!

Observe:

sqlite> INSERT INTO deps VALUES ("t1", "u2", "e1"), ("t1", "u2", "e2"),("t1", "u2", "e3");
sqlite> select * from deps;
t1|u2|e3

I went to add three rows, and wanted any pre-existing rows for ("t1", "u2") target–user pair to be deleted, but I only got the last row inserted, because it deleted all previous ones.

If SQLite had FOR EACH STATEMENT triggers, I suspect this trigger could be made to work using that, but since it doesn’t, is my only recourse to first DELETE then INSERT? Is there any more concise way?


texts: Any disadvantage to automatically changing FULL backup on AlwaysON Secondary to COPY_ONLY?

texts:
I've been playing around with Ola's maintenance scripts recently, and they are absolutely great. However, I was considering one scenario and was surprised when I found it wasn't catered for. Maybe I'm just missing something, so hopefully someone can help me out.

I would like to push out Ola's DB backup script to all of my instances, including those hosting AlwaysON groups. I know the script handles Primary/Secondary preferences, but if you set your backup preference to Secondary Only, then allow the script to run a normal FULL backup, the backup will not be completed because the Secondary only allows COPY_ONLY backups.
I've edited Ola's DB backup script to include a @CopyOnSecondary switch where if set to Y, the backup will automatically change the FULL backup on a Secondary to a FULL COPY_ONLY backup on the Secondary.

Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?

Cheers.

EDIT: Listing answers
 - Taking only COPY_ONLY FULL backups will not allow for DIFF backups or log truncation via log backups.


texts: will this insert query take too long to run?

texts:
I am bulk inserting from a node server to a psql database by constructing the query like

let query = `INSERT INTO ${table} (timestamp, lattitude, longitude, type) VALUES `;
locationData.forEach(location => {
    query += `('${location.timestamp}', '${location.lattitude}', '${location.longitude}', '${type}'), `;
});

this api gets called every 3 minutes by a device that logs location every second, meaning atmost 180 rows will be inserted in each query. will it be a bottleneck?


texts: `SELECT FOR UPDATE` in master-master mysql setup

texts:
I am benchmarking a database schema with a dozen workers processes and was wondering what a SELECT FOR UPDATE does in a master-master configuration.

For the moment it seems to work, but I am either doing INSERT on one and SELECT FOR UPDATE + UPDATE on the other, or doing it on both at the same time.

There is a big lag in the replication (the numbers shows that it keeps growing over time). What would happen if I do some SELECT FOR UPDATE + UPDATE on both while still doing some INSERT on one and not both ?

Can the database be corrupted ?


texts: SUM rows FROM a table only if one row have some match criteria with the rest of the rows

texts:
Sorry if the title is a little bit odd, but I am struggling even with explaining myself on this.

I am trying to create a soccer stats database. Right now I have a 'boxscore' table like this:

id_boxscore  id_player  id_team  goals  fouls
-----------  ---------  -------  -----  -----
1            1          1        1      3
1            2          1        3      5
1            3          2        2      8
2            4          5        0      6
2            4          5        0      6
3            2          1        5      9
3            3          1        11     4

So, for some advance data calculations, I need to know the accumulated totals from the team from games where the player participated. The good query should give me something like this if, for example, I am selecting accumulated stats from id_player = 1:

id_player  accGoals   accFouls
---------  ---------  --------
1          4          8

Right now my query is looking like this:

SELECT SUM(b.goals) FROM boxscore as b, (SELECT b.id_boxscore from boxscore as b
WHERE b.id_player = 1) AS c WHERE b.id_boxscore=c.id_boxscore

But is giving me the accumulated stats from games where the player with id 1 has played (both from his team and from the opponent):

id_player  accGoals   accFouls
---------  ---------  --------
1          6          22

Any ideas? I am sure this is really simple but I am in a dead-end right now.

Thanks in advance!


texts: SSMS Suggested Indexes and Compability Mode

texts:
I am running an old production application which has a backend DB hosted on SQL2008R2 in Compatibility Mode 80 (SQL2000). When I run bits of TSQL to view actual execution plans SSMS is giving me suggested indexes which are only legal for SQL2005+ e.g. ones with included columns. How can I prevent it from doing this? I would like to view sensible indexes it might suggest.

NB: The Compatibility Mode can't be changed. Yes we are replacing the application, this will remove the need for the DB entirely but not for some time.

NB: Already tried, SSMS is configured "Script for server version" as SQL Server 2000.


texts: Return the most recent event in a series of same title events

texts:
I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance:

Each time a product is moved from position A to Position B, it is recorded (From_Function) / (To_Function) along with its operator and timestamp.
On rare occasions, re-work is required, and that unit will show the same from / To instance with different timestamps.

I am currently calculating age in each position with a datediff,  how do I return only the most recent in this scenario?

I got it down to if there is no group by clause it will return the date needed with a max(route_date_time); however, the total qry is many lines long with aggregate functions so a group by is necessary. How can I return only the one line that is needed with the most recent date?


texts: how to retrieve mongodb admin password installed on AWS EC2

texts:
3rd party developers have installed MongoDB on AWS EC2 instance and later handed over code to me.

Now I don't know what's the password for admin user of MongoDB but I do have ssh keys to connect to that EC2 instance.

My query is how to reset password for MongoDB admin user ?


texts:
I have a MySQL db hosted on a commercial server as a shared account. Recently I have been getting an error: 'DBI connect('xxxxxx',...) failed: Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'.

However I can ssh into my host but trying to execute "mysqladmin flush-hosts' tells me 'DBI connect('xxx:example.com','xxxuserxxx',...) failed: Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'".

My host cannot help me (outside their scope) but I am hoping there is a way either to escalate my privileges, or execute a mysqladmin command.

Anyone?


texts:
I'm working on the query bellow. Its structure is based on someone else's similar query...

SELECT
    tptype.Code AS ThirdpartyTypeCode,
    ptf.Ticker AS TextId,
    finIn.InternalCode AS CompanyCode,

    TP.BrokerFocus,
    TP.EntryDate,
    TP.EndDate,
    TP.AccountingFundIdentifier,

    contacts1.FirstName AS Contact1FirstName,
    contacts1.LastName AS Contact1LastName,
    contacts1.Title AS Contact1Role,
    contacts2.FirstName AS Contact2FirstName,
    contacts2.LastName AS Contact2LastName,
    contacts2.Title AS Contact2Role,

    ParentThirdParty.TypeCode AS ParentTypeCode,
    ParentThirdParty.CompanyCode AS ParentCompanyCode

FROM dbo.ThirdParty AS TP
    LEFT JOIN dbo.ThirdPartyType as tptype on TP.ThirdPartyTypeId = tpType.Id
    LEFT JOIN dbo.Portfolio AS ptf ON TP.FundId = ptf.Id
    LEFT JOIN dbo.FinancialInstitution AS finIn ON TP.FinancialInstitutionId = finIn.Id

    LEFT JOIN (SELECT ThirdParty.Id, ThirdPartyType.Code AS TypeCode, FinancialInstitution.InternalCode AS CompanyCode
               FROM
               dbo.ThirdPartyType AS ThirdPartyType INNER JOIN dbo.ThirdParty AS ThirdParty ON ThirdParty.ThirdPartyTypeId = ThirdPartyType.Id
               INNER JOIN dbo.FinancialInstitution AS FinancialInstitution ON ThirdParty.FinancialInstitutionId = FinancialInstitution.Id) AS ParentThirdParty
               ON TP.ParentThirdPartyId = ParentThirdParty.Id
    LEFT JOIN
        (SELECT contact.Id, contact.PersonId, dbo.Person.FirstName, dbo.Person.LastName, contact.Title
         FROM (SELECT dbo.ThirdPartyContact.Id, dbo.ThirdPartyContact.Title, dbo.ThirdPartyContact.PersonId, ROW_NUMBER() OVER (ORDER BY dbo.ThirdPartyContact.Id) as rn
               FROM dbo.ThirdPartyContact
               INNER JOIN dbo.Person ON Person.Id = dbo.ThirdPartyContact.PersonId
               WHERE dbo.ThirdPartyContact.ThirdPartyId = TP.Id
               ) AS contact
         inner join dbo.Person ON Person.Id = contact.PersonId
         WHERE rn=1) AS contacts1
         ON 1 = 1
    OUTER APPLY
        (SELECT contact.Id, contact.PersonId, dbo.Person.FirstName, dbo.Person.LastName, contact.Title
         FROM (SELECT dbo.ThirdPartyContact.Id, dbo.ThirdPartyContact.Title, dbo.ThirdPartyContact.PersonId, ROW_NUMBER() OVER (ORDER BY dbo.ThirdPartyContact.Id) as rn
               FROM dbo.ThirdPartyContact
               INNER JOIN dbo.Person ON Person.Id = dbo.ThirdPartyContact.PersonId
               WHERE dbo.ThirdPartyContact.ThirdPartyId = TP.Id
               ) AS contact
         inner join dbo.Person ON Person.Id = contact.PersonId
         WHERE rn=2) AS contacts2

As you can see, an APPLY is used to select the first line of results from the a sub-query. As I understand it, the APPLY was used here to replace LEFT JOIN, because there is no join condition, since the selection is only based on the row number.

I was wondering if I could rewrite this part for it not to use APPLY anymore, but a LEFT JOIN instead. As a first step, I wondered if I could leave the sub-query as it is, but write the LEFT JOIN with a condition that would always be true:

LEFT JOIN
    (SELECT contact.Id, contact.PersonId, dbo.Person.FirstName, dbo.Person.LastName, contact.Title
     FROM (SELECT dbo.ThirdPartyContact.Id, dbo.ThirdPartyContact.Title, dbo.ThirdPartyContact.PersonId, ROW_NUMBER() OVER (ORDER BY dbo.ThirdPartyContact.Id) as rn
           FROM dbo.ThirdPartyContact
           INNER JOIN dbo.Person ON Person.Id = dbo.ThirdPartyContact.PersonId
           WHERE dbo.ThirdPartyContact.ThirdPartyId = TP.Id
           ) AS contact
     inner join dbo.Person ON Person.Id = contact.PersonId
     WHERE rn=1) AS contacts1
     ON 1 = 1

But I get the following error:

The multi-part identifier "TP.Id" could not be bound.

I'm not sure I understand why this doesn't work.


texts:
I'm doing the leetcode SQL problems and kind of confused with the code below:

select distinct Num from (
    select
        Num,
        case
            when @prevNum = Num then @count := @count + 1
            when (@prevNum := Num) is not null then @count := 1
        end n
    from Logs, (select @prevNum := NULL) r
    order by Id
) a where n >= 3

Two questions:

First when (@prevNum := Num) is not null what is the logic value of assignment expression in SQL?
I have googled MySQL and Oracle reference book, this forum and I got nothing about it. And this condition is really confusing.

Second the n after case end statement, I know n stands for the alias of case statement, but there are two variables in case end statement here, how do SQL know that n stands for @count but not @prevNum?

Thanks

The question is as follows:

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.


texts: Postgres strategy for UPDATE SET x = NULL

texts:
It is not clear the strategy Postgres takes when columns are set to NULL:
UPDATE tbl SET
  col1 = NULL,
  col2 = NULL
WHERE created < current_date - INTERVAL '1 year';

Documentation https://www.postgresql.org/docs/current/mvcc.html is a bit lengthy and techy so I cannot reliably deduce:
if setting to NULL is performed in place or affected rows/pages are copied?
Looks like any UPDATE should create new row for MVCC semantic but what if setting to NULL is a special case?
For GDPR conformance I think to null every personal historical data and I try to understand implications of massive periodic UPDATE SET x = NULL. Should I think of VACUUM after that?


texts:
I have a this schema:

CREATE TABLE Pairs(
   id    VARCHAR(8)  NOT NULL
   ,name  VARCHAR(40) NOT NULL
   ,value VARCHAR(1000) NOT NULL
   ,PRIMARY KEY (id,name));

I want to write a query to retrieve the id which is present in several rows with several constraints on name and value like:

name like 'forname%' AND value like 'CAMI%'
name like 'projet%'  AND value like '%gociation'
name ... AND  value ...
...

The cardinality of the constraints is unknown at compile time, it comes from a GUI.

I wrote this query:

select * from (
      SELECT * FROM Pairs WHERE ( name   =  'projet'   ) AND ( value like '%gociation' )
union SELECT * FROM Pairs WHERE ( name like 'forname%' ) AND ( value like 'CAMI%' )
) as u order by id

the result is:

id      name    value
AEMIMA  projet  renegociation
AENABE  projet  renegociation
AEREDH  projet  renegociation
AGOGLY  projet  renegociation
AHOGAL  projet  renegociation
AHSPID  projet  renegociation       <<<<<<<<<<<<
AHSPID  fornameE    CAMILLE         <<<<<<<<<<<<
AIOSAP  projet  renegociation
AIPNEU  projet  renegociation

Only the <<<<<<<<<<<< marked lines are good and the id I want is AHSPID.

How can I eliminate the lines which doesn't match ALL the constraints?


texts: SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

texts:
a couple servers I manage are recording a lot of events in the system_health XE session.

name: security_error_ring_buffer_recorded
api_name: ImpersonateSecurityContext
calling_api_name: NLShimImpersonate
error_code: 5023

The error_code 5023 should be (System Error Codes):

ERROR_INVALID_STATE    5023 (0x139F)

The group or resource is not in the correct state to perform the requested operation.

I have no failed login event in the event security log, nor in the SQL Server log.


texts: Has the definition of vertical scaling changed - and if so what do we call it

texts:
15 years ago when we were figuring out how to scale SQL databases to huge systems, we used the term Horizontal scaling to mean split up your tables so that the same schema existed on multiple machines - and Vertical scaling to mean put some tables on machine A, and some on machine B.

Recent definitions (here and on Google) seem to have morphed in that Vertical scaling is now defined as throwing more hardware on your single database.

So what is the term for "split up your single database into multiple databases such that some such that you have different tables / schemas living on your various DBs"?


texts: Add unique constraint on combination of 2 columns

texts:
I'm trying to make two columns (combined) unique in a mysql table. I've searched the stack, but the solutions don't work.
So I'll explain by using an example

cartID   productID
10       7           -> allowed
10       8           -> allowed
11       7           -> allowed
11       7           -> not allowed, the combination of 11 7 already exists in table

The 'solutions' I found are the following:
ALTER TABLE cart
  ADD CONSTRAINT uq_cart UNIQUE(cartID, productID);

But this just seems to add a constraint on each column individual (so a 10 8 would also throw an exception if 10 7 already existed).


texts: Can operations in a loop cause table locks?

texts:
I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but I don't think it's important in this case, feel free to correct me).

I want to know whether single row operations within a while loop could cause the same kind of lock on a table? The operations are mostly inserts, each creating a single. Can this be optimized in the background to lock the entire table?

If it can't lock the whole table, can it have the same type of effect, monopolizing the resources or operations on that table until it is completed?

We are using MS SQL Server 2008r2, and a spring batch application is calling stored procedures in the database. The while loop in question is inside that stored procedure which commits as an entire transaction (not per loop or anything like that).

EDIT: Below is a cut down version of the loop (it is a production application):

select @MinItem = min(ItemId) from Item

while @MinItem is not null
begin

    declare @x, @y, @z

    select @x = i.x
           @y = i.y
           @z = i.z
    from Info i
    where ItemId = @MinId

    insert into Sales (a,b,c)
    select x,y,z

    --two more inserts like above but to different tables, followed
    --by an update to another table

    select @MinItem = min(ItemId) from Item
    where ItemId > @MinItem

end


texts:
I have MariaDB in Galera cluster of 3 nodes. Periodically, each node in the cluster restarts by sending yourself a signal 11. I scoured the Internet in search of a solution to the problem, but to no avail. The only thing that could be similar to my problem, it - https://serverfault.com/questions/678128/percona-cluster-signal-11. But I have not found confirmation of this.
I duplicated the problem to the developers MariaDB, but they are in no hurry to solve it: https://jira.mariadb.org/browse/MDEV-14584

error.log:

2017-12-05  9:27:13 140599126464256 [Note] WSREP: (8f3cb7d9, 'tcp://0.0.0.0:4567') turning message relay requesting off
171205 11:38:01 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.2.10-MariaDB-log
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=15
max_threads=102
thread_count=23
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 240475 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fddf40009a8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...

server.cnf:

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
bind-address = 0.0.0.0
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam_recover_options = BACKUP
max_connections = 100
sql_mode =

# Logs
log_error = /var/lib/mysql/logs/mysql_error.log
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/logs/mysql_slow.log
long_query_time = 2

# Cache parameters
query_cache_size = 64M
query_cache_limit = 2M
table_open_cache = 4096
thread_cache_size = 200
key_buffer_size = 16M
thread_stack = 128K
join_buffer_size = 2M
sort_buffer_size = 2M

# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 512M
tmp_table_size = 512M
query_cache_type=1

# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT

# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
skip-character-set-client-handshake
#skip-name-resolve

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on = ON
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://192.168.59.78,172.16.140.29"
wsrep_cluster_name = "site-appdb"
wsrep_node_address = "192.168.59.78"
wsrep_node_name = "site-appdb01"
wsrep_sst_method = rsync
wsrep_sst_auth=repl_user:+vQvI9Fe8Nr6
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
#innodb_locks_unsafe_for_binlog = 1
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
wsrep_slave_threads=1
innodb_file_per_table
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]


texts: Syntax shorthand for updating only changed rows in UPSERT

texts:
I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed):
INSERT INTO public.test_upsert (some_id, a, b, note)
VALUES
  ('c', 1, true, 'asdf')
ON CONFLICT (some_id)
DO UPDATE SET
  a = excluded.a,
  b = excluded.b,
  note = excluded.note
WHERE
  test_upsert.a IS DISTINCT FROM excluded.a OR
  test_upsert.b IS DISTINCT FROM excluded.b OR
  test_upsert.note IS DISTINCT FROM excluded.note
RETURNING *;

My question is: is there shorthand for this? I basically want to either insert a new record update the rows when any of the columns being inserted are distinct from the existing columns. Writing out each column individually gets quite verbose, although it is more explicit.


texts: Oracle Upgrade Standard to Enterprise Edition

texts: I need to upgrade several Oracle Database 12c Standard Edition to Enterprise Edition. The only upgrade path I found is to reinstall the Oracle software. Is that the only way to upgrade?


texts:
I am trying to setup MySQL InnoDB Cluster. I am using version 5.7.20 of the MySQL server. I am able to create a cluster. When I try to add the first slave is when I run into my issue. It complains about the slave having group transactions the master does not have. The error is as follows:

  This member has more executed transactions than those present in the group. Local Transactions: c5109632-32bb-11e8-a081-005056a142d2:1-5 > Group transactions: a43235a8-32bb-11-e8-97c3-005056a16fa6:1-47, a4c00453-3344-11e8-b0fb-005056a16fa6:1-14

This is fine because I log into the MySQL shell (on the master) and run the follow:

SET GITD_NEXT='c5109632-32bb-11e8-a081-005056a142d2:1'; BEGIN; COMMIT;

I do that for the five ids and then I end with

SET GTID_NEXT='AUTOMATIC';

That gets me past that error, however I get a new error. It is:

  Plugin group_replication reported: Group contains 2 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to an higher rate of transactional aborts.

Then I get:

  Slave SQL for channel 'group_replication_recovery': Error 'Unknown error 1396' on query. Default database: ''. Query:

When I set all of this up. I created 3 brand new instances on 3 different servers. I created a cluster manager user account "bob" on all 3 instances. I ran all of the grants for that user on all 3 instances. I then did the checkInstanceConfiguration from all 3 instances. They all passed. I then did the configureLocalInstance on all 3 instances. Then I created my cluster and tried adding a slave.

My question is how can I get around the 'group_replication_recovery' SQL error. What did I do wrong?


texts: How to create a tablespace on Windows 7 for Postgresql 9.2?

texts:
I try to run this simple command:

CREATE TABLESPACE tb_rec
  OWNER postgres
  LOCATION 'd:/tablespaces';

However, the tablespace is not created and I got this message:

ERREUR:  n'a pas pu configurer les droits du répertoire « d:/tablespaces » : Permission denied

For non french speakers:

ERROR: was not able to configure the directory rights « d:/tablespaces » : Permission denied

I didn't find any postgres user (net user) and I'm stuck on this...

How to give access to the directory to postgres?

Windows 7
Postgres 9.2


texts:
In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make it faster.

//Get the first day and last day
$dateInitial = strtotime('2018-08-21');
$dateFinal   = strtotime('2019-08-21');
$final       = array();

$sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity
              FROM (
                    SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing,
                           SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production,
                           ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting
                    FROM (
                          SELECT MAX(id) AS id
                          FROM sw_sowing
                          WHERE status != 0
                          AND id_tenant = :id_tenant
                          AND date <= :dates
                          AND multiply != 1
                          AND id_product = 1
                          GROUP BY id_production_unit_detail
                    ) AS sw
                    INNER JOIN sw_sowing ON sw_sowing.id = sw.id
                    INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
                    INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
                    WHERE pr_varieties.code != 1
                    GROUP BY sw_sowing.id_product, sw_sowing.type
                    HAVING type NOT IN('ER','PR')
              ) AS s";

$statement    = $this->db->prepare($sql);

//get the sunday dates between two dates and bind the variables
for ($i = $dateInitial; $i <= $dateFinal ; $i = strtotime('+1 day', $i)) {
        if (date('N', $i) == 7){
          $values = [
            ':dates'      => date("Y-m-d", $i),
            ':id_tenant'  => 1
          ];

          $types = [
            ':dates'      => Column::BIND_PARAM_STR,
            ':id_tenant'  => Column::BIND_PARAM_INT
          ];

          $result   = $this->db->executePrepared($statement, $values, $types);
          $final[]  = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        }
      }
      return $final;

But despite this it is not so fast. The query lasts 10 seconds and I would like it to be faster.

I have also indexed the tables. I would like some opinion on how to best optimize this query or if the way I am doing the query is not adequate.

This is a question that I did before about why I use GROUP BY and MAX(id)

https://stackoverflow.com/questions/52209300/get-max-ids-by-group-mysql


texts: Generate ER Diagram with view permissions

texts:
Currently my environment that I'm working with is using Oracle DB 12 and I have been granted view permission only.

The issue is that the ER diagram that is given to me is not complete and sometimes retrieving data from different tables can be a challenge and time-consuming.

Therefore, is there any easier way that I could generate the full ER diagram myself or at least the relations with the current permissions I have?


texts:
The following code returns some rows with [Error] = 2

SELECT
 v1.[name] as EventName, tr.TextData, tr.HostName, tr.ApplicationName, tr.LoginName, tr.SPID, tr.Duration,
 tr.StartTime, tr.EndTime, tr.Reads, tr.Writes, tr.CPU, tr.ServerName, tr.Error, tr.ObjectName, tr.RowCounts
FROM
 sys.trace_events as v1
 INNER JOIN fn_trace_gettable(@v_path, DEFAULT) as tr ON tr.EventClass = v1.trace_event_id
WHERE
 tr.TextData IS NOT NULL AND
 (tr.EndTime > @v_maxEndTime OR @v_maxEndTime IS NULL)
 OPTION(RECOMPILE);

Where can I see the list of error codes for fn_trace_gettable to understand what this one means?


texts: How can I keep two databases on different sites in sync?

texts:
I'm working on a web application that exists at two sites. Each system is hosted on it's own LAMP stack, with access restricted to specific users on the network.

When content is updated via user input on one side, an XML is transferred from that system to the other to update it, so both systems match.

These files are transferred by a 'guaranteed delivery' system. However, sometimes the XMLs get bunched up and arrive in the wrong order, causing problems at the receiving end.
This is particularly noticable with rapid user input, as the delay from the transmission system causes the files to 'bump into eachother'.

It was suggested that database replication would be more efficient to keep the systems in sync.

My question is: Is replication suitable for small incremental changes, does it have to update every single item each time or just the part that has changed?

Another question I have is, what protocol would the databases use to communicate from two separate sites?


texts: How can I see execution time of an ALTER TABLE statement?

texts:
Is it possible to see the execution time of an alter table command?
I think with \timing on in psql, it shows more than the execution time, but also the client round trip, and maybe other things as well. What I want is the "execution time" shown by explain analyze. But I can't run alter table through explain analyze (right?).


texts: Why can I execute this query but not see its execution plan?

texts:
Consider the following query:

SELECT table_name,
  to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '
  ||owner
  ||'.'
  ||table_name)),'/ROWSET/ROW/C')) AS COUNT
FROM all_tables
WHERE UPPER(owner) = UPPER('my_schema');

This query runs just fine and displays output. But doing EXPLAIN on this query produces:

  ORA-01039: insufficient privileges on underlying objects of the view

As per the Oracle documentation:

  You must have the privileges necessary to execute the SQL
  statement for which you are determining the execution plan. If the SQL statement accesses a view, then you must have privileges to access any tables and views on which the view is based.

  To examine the execution plan produced by an EXPLAIN PLAN statement,
  you must have the privileges necessary to query the output table.

I do have privileges on the table accessed by SQL as the query runs fine.
By output table I assume it means dbms_xplan.display which is to store calculated plan. I think that I have privilege on that aswell as following runs fine:

EXPLAIN PLAN FOR SELECT * FROM myschema.mytable;
SELECT * FROM TABLE(dbms_xplan.display);

What's missing here?


texts: Diagnosing a maintenance plan that's ballooned in execution time

texts:
Environment is SQL 2008 R2, 26GB RAM, 4-core Xeon E5-2650 @ 2.6GHz.

Please see the image below, our maintenance plan which reorganises indexes (not rebuilds). This is run on a weekly basis, and within one week, this plan went from taking ~5 minutes way up to 1hr - 1hr:15, and it has not improved since. We've had to disable this plan and work around this in another way.
I've looked at the amount of data in the database and it hasn't grown by a considerable enough volume to cause this issue.

I've been in touch with our hosting company to see if anything has changed within our environment, but they are saying nothing's changed, there's no limitations/restrictions in place on our VMs, IO seems ok. We haven't made any changes to the underlying application neither.

From what I can see, other things have started to fail within this week period too - some queries which ran perfectly fine are starting to timeout. I'm at a bit of a loss as to what could have caused this and am unsure how to retroactively find out. Could anyone point me in the direction of where to begin to discover what could have been wrong here?


texts: Customize jsonb key sort order involving arrays


texts: SQL Server--If logic in stored procedure and the plan cache

texts:
SQL Server 2012 and 2016 Standard:

If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?

And if on the following execution, the value of the parameter changes, will it re-compile and re-cache the stored procedure, since a different branch of the code must be executed? (This query is quite expensive to compile.)


texts:
In my company we have multiple virtual machines hosting SQL Server 2008 R2 and some of these machines perform differently than others, some because of very busy Vmware hosts some because of slower connections to NAS.

Is there a way I can run some SQL code in a test SQL database or other known best practices I could use to run some performance tests in each VM and baseline/benchmark performance so that I can understand which machine behaves better than others prior to moving those machines to Prod or UAT environments? Thanks, Davide.


texts:
I would like to migrate my existing Visual Studio SSRS Reports to the cloud while keeping my database on-premise.  Any advice on resources for getting started would be greatly appreciated - tutorials, training sites (pluralsight, cbtnuggets, etc).

Thanks in advance


texts:
I have a server with 150GB of diskspace. Today I uploaded a dataset of 30GB. I cancelled the import due to internet dying, then noticed there was 29GB of space missing in the database (meaning the CSV was uploaded, but not deleted when I broke the operation). When uploading the data once again, it broke again and I lost another ~25GB. Now there isn't enough free space to upload the data.

This is hosted on AWS RDS, Postgres 10.6.

Is there a way to fix this? I read about VACUUM. But will this delete records? I'm hosting at the moment ~70GB of data and don't want to lose any records. What's the best way to go about this?


texts:
This is a follow up question from my previous post:
Does this simple database design work even if it's cyclic?

I am a graduate student working on breast cancer. My mice are genetically engineered to develop cancer, and I want to keep track of the their health status.

Each day, animal care staff check each cage. If they make at least one clinical observation, they report it to the vet. The report looks something like:

The animal care staff will color the boxes, and the vet will confirm (check-mark = "yes", x = "no). Notice that several observations can be reported at once. Keeping track of the number of cages is also important because several cages (with the same protocol and researcher) may have the same observations, and the animal care staff will only make one report.

Once something is found, a clinical case is opened (the report becomes the case sheet). Subsequently, if additional observations are made with cages that already have a case number, the vet simply writes them in the
"notes" sections of the first report. Hence, several reports can be grouped into one case. When I enter a new report/case, I want the database to verify if a case already exist. If yes, append the new report. If not, create a new case. When the mice are healthy again or euthanized, the case is closed.

Finally, I need to keep all the "false positive" observations made by the animal care staff. Here is my tentative database model:

I have two transition tables, I would greatly appreciate the community's feedback. Thank you very much!

EDIT:

[


texts:
I am fairly new to SQL and am a bit out of my depth with the query I have been working on. I am attempting to modify a query created by someone else to fix an error. the code I have modified is below:

SELECT COUNTY                               COUNTY,
DECODE(RATE,'115A','R','C')             R_C,
SUM(MTH_CHG)                            MO_CHG,
SUM(YTD_CHG)                            YR_CHG,
COUNT(CUST)                             TOTAL_CUST
    FROM RPT_BLOC_VW,
        (SELECT SRAT_CODE               RATE,
        NVL(billed_chg,0) + NVL(adj_chg,0) MTH_CHG,
        0                             YTD_CHG,
        BLOC_CODE,
        FROM RPT_REV_CON_ETL,
        (SELECT DISTINCT cust_code, prem_code AS CUST
            FROM   rpt_rev_con_etl
            WHERE  PROCESS_DATE BETWEEN vStartMonth AND vEndMonth
                   AND UTVSRAT_desc = '115')
        WHERE PROCESS_DATE BETWEEN vStartMonth AND vEndMonth
        AND UTVSRAT_DESC = '115'
    UNION ALL
     SELECT         SRAT_CODE          RATE,
         0                             MTH_CHG,
         NVL(billed_chg,0) + NVL(adj_chg,0) YTD_CHG,
         BLOC_CODE
        FROM RPT_REV_CON_ETL
          WHERE PROCESS_DATE BETWEEN vStartYear AND vEndMonth
        AND DESC = '115'
     ) DATA
     WHERE DATA.BLOC_CODE = RPT_BLOC_VW.BLOC_CODE(+)
 GROUP BY COUNTY, DECODE(RATE,'115A','R','C')
 ORDER BY COUNTY, DECODE(RATE,'115A','R','C') DESC ;

What I am looking for is a way to combine the second and third select statements into one. They have the same From and Where clause so I am fairly certain it should be possible, but I cannot think of a way to do it that doesn't mess up the distinct clause and screw up the count I am trying to get.

I am pretty sure there is something obvious I am overlooking.

-edited to include full query-


texts: TRUNCATE TABLE statement sometimes hangs

texts:
Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?

I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.

The hanging statement is simply:

TRUNCATE TABLE sampledb.datatable;

What can cause this to happen, and how could I fix it?

Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.


texts:
I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this?

DELIMITER $$
DROP PROCEDURE IF EXISTS archive_table $$
create procedure archive_table ()
set @min=(select min(dob) from test
              where dob < DATE_SUB(CURDATE(), INTERVAL 30 day));
while @min is not null
begin
    delete from test where dob = @min;
    commit ;
    select min(dob) from test
            where dob > @min
              and dob < DATE_SUB(CURDATE(), INTERVAL 30 day) );
END WHILE;
END$$

ERROR:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
 near 'while @min is not null
begin
    delete from test where dob = @min;
    commi' at line 1


texts: Using SELECT in the WHERE clause of another SELECT

texts:
I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, it happens that I call something like 40 select clause (over tcpip).

I have reminiscences from SQL-Server reminding me to minimize the number of interactions between my remote application and the database. Having analyzed my selects, I do think  I could reduce this number to 3 SELECT clauses, using joins. But I don't remember the syntax for using the result of a SELECT in another SELECT.

E.g.:

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

This other SELECT would be simply of the kind:

SELECT identifier FROM another_table WHERE something='something'

Here is the simplified tables layout, declined a number of times for different item_types ... (3 totally different types, hence the 3 SQL queries if optimized).

table passage
  id_passage PK
  business_field_passage bytea

table item
  id_item PK
  id_passage FK
  business_field_item text

table item_detail
  id_item_detail PK
  id_item FK
  business_field_item_detail text
  image_content bytea

There are several id_item for one id_passage.
There are several id_item_detail for one id_item.

How would you write that?
What is the name for describing the action of redirecting one select into another (if any)?


texts:
I have a column called nickname and have created index for it.

 create index test_idx on test_table using btree (nickname);

My query is,

 select * from test_table where nickname ilike '%pga%';

test_table is having around 100k records. when I'm doing explain, i am getting some cost which is same as before creating the index. Why is that.?

(using Postgresql 9.2)

Edit 1:

nickname column is character varying(100)


texts: Limiting query to certain amount of rows evenly distributed

texts:
Given a table:

id
--
1
2
4
6
8
10

I would like to be able to specify a limit and have that amount of rows returned evenly distributed across the entire set when it is ordered by a specific criteria. For example, 3 rows when the set is ordered by id would give me:

id
--
1
4
10

In the above scenario, either 4 or 6 could have been returned as the middle row. I am not specifically bothered as to which way the selection is rounded.

Ideally I'd like to do this in the most efficient way i.e. the entire table isn't parsed initially.

I found an excellent post here Select every n-th row, don't pull the entire table, regarding evenly distributed data sets but struggled when trying to calculate n in order to limit the final data set to a specific amount.

I'm currently running MySQL 5.6 on Ubuntu.

Ids are non sequential.
This database is dynamic so I can't hardcode the amount of rows or have select statements that reflect errors in a particular set of results.


texts: "ORA-03113: end-of-file on communication channel" on startup

texts:
I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem...

An Oracle database hung. Shutdown of the database sat for a few hours and then it quit. It wouldn't restart. The server was restarted. Oracle was restarted. Going step by step: startup nomount works, alter database mount works, alter database open returns ORA-03113. This is all on localhost - not over the network. The machine has no firewall of any kind running.

Any idea how to get past this ORA-03113 error? I've been on the phone with support in India for the last 4.5 hours and I haven't found anyone helpful yet.


texts: Postgres repmgr - downstream nodes not attached

texts:
I'd configured repmgr replication on node1 and node3 (primary and standby respectively), and the setup worked sucessfully creating new records and objects on standby as expected.
But after some weeks I'd noticed that replication wasn't working anymore, however some repmgr commands are returning results as the replication are working.
I tried to restart and register again the standby node, but it doesn't worked.

How can I continue to replicate?

Here's status of nodes:

-bash-4.2$ psql -V
psql (PostgreSQL) 10.3

NODE1 - PRIMARY

-bash-4.2$ repmgr node check
Node "node1":
    Server role: OK (node is primary)
    Replication lag: OK (N/A - node is primary)
    WAL archiving: OK (0 pending archive ready files)
    Downstream servers: OK (this node has no downstream nodes)
    Replication slots: OK (node has no replication slots)
-bash-4.2$

NODE3 - STANDBY

-bash-4.2$ repmgr -f /etc/repmgr/10/repmgr.conf node check
Node "node3":
    Server role: OK (node is standby)
    Replication lag: OK (0 seconds)
    WAL archiving: OK (0 pending archive ready files)
    Downstream servers: CRITICAL (1 of 1 downstream nodes not attached; missing: node3 (ID: 3))
    Replication slots: OK (node has no replication slots)

-bash-4.2$ repmgr node status
Node "node3":
    PostgreSQL version: 10.3
    Total data size: 2393 MB
    Conninfo: host=node3 user=repmgr dbname=repmgr connect_timeout=2
    Role: standby
    WAL archiving: disabled (on standbys "archive_mode" must be set to "always" to be effective)
    Archive command: /bin/true
    WALs pending archiving: 0 pending files
    Replication connections: 0 (of maximal 10)
    Replication slots: 0 (of maximal 10)
    Upstream node: node3 (ID: 3)
    Replication lag: 0 seconds
    Last received LSN: 4/AC000000
    Last replayed LSN: 4/AC000140


texts: Database Schema design of server running status log

texts:
I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running PUMA server and another one is running NGINX and so on. I want to store the running status of the instances and their application servers in a database.

Like, If Instance A is running or not, that I can get by continuously hitting into the exposed port and store it in the database. I am scanning it in each minute. There will be 3 columns I can think of right now,

Instance A :-

Instance ID    Running Status   Time

Instance A        Running        10:10:04
Instance A        Running        10:11:04
Instance A        Running        10:12:04

Next, I want to store the status if Application Servers within Instance A running or not. There will be 3 columns I can think of right now,

Let's say PUMA and NGINX are running in Instance A

Instance A :- (Here also I am scanning it in each minute)

Application Name   Running Status   Time

PUMA                  Running       12:10:20
NGINX                 Running       12:10:20
PUMA                  Running       12:11:20
NGINX                 Running       12:11:20

I am using Postgresql. What will be the recommended schema design here ? Should I create a table for each instance and how will I map the applications with that particular Instance ? If I take Instance ID as a primary key then duplicate values in that column are not possible.


texts: update records that are there in .csv file which will be given as input

texts:
I have .csv file with list of Ids, using those IDs in file- giving them as input, I need to update the record in table,

I have around 4000 Ids so 4000 update statements are not possible, need a procedure to automate this.

can you please tell me if there is any procedure to make this happen.


texts: sql 2008, select from a view as a different user, like owner

texts:
In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view.

I need to check id a certain account exists, so I need to see all the existing sql account accounts, regardless the permissions of the current account, I mean, just like it used to be in previous versions.

Is there some option like 'EXECUTE AS OWNER' but for views?

Something like

create view dbo.vUsers select as owner as

select * from sys.database_principals


texts: MySQL daisy chain replication with different replication binlog_formats

texts: This is very unconventional and not normally recommended but I need to know if this is possible so here it goes. I have two mysql clusters each with a primary and standby setup. Cluster A replicates to cluster B using statement based replication. We are using statement based replication because we have triggers set on cluster B we need to fire that won't fire with row based replication. I now have a temporary requirement for a project to have a cluster C with row based replication being used. Is it possible (I know it's crazy) to daisy chain the replication by setting up cluster C and have B replicate to C using row based replication? Or do all replicas HAVE to replicate using the same type of replication?


texts: How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

texts:
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically run involve joins of 10-20 tables and can run for quite long, one to several minutes being no exception at all. Only very few users access the database at the same time (5 being the maximum).
I moved the server from a Lenovo Thinkpad T61 with a 2.2 GHz Dual Core and 4 GB of RAM to the following brand-new machine with hand-selected components:

Intel i7 3770, 4x 3.4 GHz (running @ 4x3.7 GHz)
Z77 chipset
16 GB of DDR3 1600 RAM
Windows 7 Prof 64-bit
Windows and MySQL server run on a Intel 520 series SSD drive.

First tests (running the same query on both machines) showed a definitive improvement in speed for the new one, but the queries still take a lot of time and I had expected more of a boost. The queries in question are fairly well optimized, i.e. all tables have proper key that are also being used as of "explain extended".

Now to my current MySQL settings:
First I should mention that I moved from MyISAM to Innodb long time ago.

Some of my my.ini tweaks (i.e. departures from default settings):

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
#tmp_table_size=35M
tmp_table_size=4000M
max_heap_table_size=4000M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#innodb_buffer_pool_size=96M
innodb_buffer_pool_size=800M

general-log
expire_logs_days = 60
general_log_file = "F:/my_query_mysql.log"
log-output = TABLE
optimizer_search_depth = 0 #meant to cure the "statistics state" bug in some queries

I'd like to know whether somebody would suggest changes to the above numbers or even further settings that I do not know of.

I'd appreciate any helpful remark.

Steve

EDIT: I have two queries involving joins across 10-20 tables and ran them on my Lenovo notebook and the new PC. Query #1 took 3m36s on the new machine vs 9m11s on the laptop; Query #2 took 22.5s on the workstation vs 48.5s on the laptop. So the execution speed was improved by roughly the factor 2-2.5.
On the workstation, not even 50% of the RAM was used. The average CPU load across the four cores (as reported by Windows Task Manager) was only about 13%. The load on a per-core basis (as reported by Core Temp) was about 25-40% for ONE core, while it was <=10% for the others, indicating that MySQL does not make use of multiple cores for a single query.


texts:
I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure like so:

INBOX

Drafts

Outbox

Sent Messages

Trash

Junk Mail

etc, etc.

As I add users to my system, this information will be repeated across most users. So here's my question:

Considering these are small-ish strings, am I better off to just store all the mailbox information in one row (ie: mailbox name, attributes, delimiter, parentId, hasChildren) or should I store the mailbox names in a separate table and join on a foreign key?

Right now I'm thinking the former as the cost of storing the extra information is probably less than the cost of throwing more computing power at the database to maintain the same performance. But I am a novice when it comes to database design and I want my application to scale well.


texts: upgrading from Postgresql 9.1 to 9.3 on Ubuntu using Londiste

texts:
I am undertaking a project to upgrade our Postgresql production database from 9.1 to 9.3 using Londiste.

The reason I choose Londiste is to avoid substantial downtime from restoring the dump file or using pg_upgrade.

I have to upgrade 5 interconnected instances on separate servers at the same time. And, I am testing it on our test db servers.

From this website, I know that someone has used Londiste to upgrade to Postgres higher version before.

So, I would appreciate if anyone can share his/her experience here.

Thanks


texts:
I have an ingestion process which loads data into a specific collection at a very high rate. Some of these documents have to be loaded into another system in batches of 100K . Means as soon as I find 100K documents for the given criteria I load them into another system. Go ahead with the next 100K and so on. The ingestion is running in one process (a java instance) and upload of 100K docs happen in another java instance. The find query which reads the documents one by one to prepare a set of 100K docs is really slow when the ingestion process is on.

When I switch off the ingestion process, the find query is performing fast at the rate of 500 docs per 3 seconds. While ingestion is on it drops to 500 docs in 7 - 13 seconds.

What could be the reason?


texts: SQL Server 2008 R2 - The request failed or the service did not respond in timely fashion

texts:
SQL Server failed to start and throwing error - "The request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details."

I have tried the solution given here
I have tried multiple solutions but neither have worked\

Tried to built-in account to Local System, Local Service and Network service.
Reinstalled the SQL Server.
Disabled the VIA in SQL Server network configuration.

Any solution to solve my problem?
Window Error Log:

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
TDSSNIClient initialization failed with error 0x80090331, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The client and server cannot communicate, because they do not possess a common algorithm.
TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.

SQL Server error log:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.


texts: Two SQL Server databases share the same MDF and LDF files but have different data

texts:
I have a strange circumstance where I have a production database and a restored copy as another database on the same SQL Server instance, and both of them are using the same MDF and LDF files (i.e. they have the same "Logical" files in SQL Server Management Studio).  From what I've found while researching this, it is possible, but all indications are that they would both be pointing to the same data, a reasonable assumption.

However, in my case, both databases actually contain different data.  If I run a select statement on two tables with the same name, one in each database, they return different data.  I've double, triple, and quadruple verified this.  How is this possible?  None of the MS docs or anything I've found have talked about two DBs sharing the same MDF files but having different data.

There are no NDF files involved, and there is one file group in each DB with only one file in each file group.


texts: Cant start Oracle DB due to control file errors

texts:
I'm trying to start Oracle db, but it yields an error which is shown below:

I have checked the control_files and these are listed below:

Check the accessibility of both files:

       $ ll /app/oracle/oradata/ENOVIA/control01.ctl
       ls: cannot access /app/oracle/oradata/ENOVIA/control01.ctl: No such file or directory
       $ ll /app/oracle/oradata/ENOVIA/control02.ctl
       ls: cannot access /app/oracle/oradata/ENOVIA/control02.ctl: No such file or directory

Error alert log file:

  ORA-00210: cannot open the specified control file
  ORA-00202: control file:
  &apos;/app/oracle/oradata/ENOVIA/control02.ctl&apos;
  ORA-27037: unable to obtain file status
  Linux-x86_64 Error: 2: No such file or directory
  Additional information: 3
  ORA-00210: cannot open the specified control file
  ORA-00202: control file:
  &apos;/app/oracle/oradata/ENOVIA/control01.ctl&apos;
  ORA-27037: unable to obtain file status
  Linux-x86_64 Error: 2: No such file or directory
  Additional information: 3

How can I solve this?


texts: Create a trigger to fill a field according to acondition in sql

texts:
I want to create a trigger to fill a field according to acondition as below description
For example:
I have a table for all insps that have attachments named: “inspectionAttachments” and I have another table “inspections” that contains all insps and I want to fill for example column/field named “shop” in the inspections table to define which insps that have attachments or not
So, I want to fill the shop field with Yes if at least this insp has one attachment and fill this field with No if there is no attachement
How I can write the trigger to make what I want
Thanks for any help

Best,
Lubna


texts: Stop pg_restore from returning false errors?

texts:
How do you stop pg_restore from returning an error code if it encounters a meaningless non-error like “schema "public" already exists”?

I'm trying to automate a database transfer, and after I upgrading PostgreSQL to 12, pg_restore throws an error condition for things that aren't actually errors.

For example, my database setup script is basically:

sudo psql --user=postgres --no-password --host=localhost   --command="DROP DATABASE IF EXISTS mydb;"
sudo psql --user=postgres --no-password --host=localhost   --command="CREATE DATABASE mydb;"
sudo psql --user=postgres --no-password --host=localhost --dbname mydb  --command="DROP USER IF EXISTS myuser; CREATE USER myuser WITH PASSWORD 'supersecretpassword';"
sudo psql --user=postgres --no-password --host=localhost --dbname mydb  --command="GRANT ALL PRIVILEGES ON DATABASE mydb to myuser;"
sudo pg_restore -U postgres --format=c --create --dbname=mydb /tmp/mydb_snapshot.sql.gz

However, even though the last line logically succeeds in loading the database snapshot, it returns an error code, reporting that:

pg_restore: error: could not execute query: ERROR:  database "mydb" already exists

It seems like a change in PostgreSQL v12 is that creation of a new database also now populates it with a default public schema, which conflicts with the public schema in any snapshot. I never ran into this issue in v11 or v10.

I could modify my script to ignore stderr, but then I'd be blind to actual problems.

How do I tell pg_restore to ignore this error, but still report other errors?


texts:
I have an application heavily relying upon timezones, and I am using PostgreSQL for storing and querying my data. I'm letting the user choosing their own timezone, by showing the list of postgresql's supported timezones, in their extensive form (i.e. Europe/Rome). All the information concerning time/date are crunched by PostgreSQL itself, whereas my scripting language (PHP) simply fetches data and serves it without altering time-based data.

When the user wants to modify her/his own timezone, I return the timezone list by using a query like this:

SELECT tz.name AS measure, tz.utc_offset AS utc_offset, tz.is_dst AS is_dst FROM pg_timezone_names() tz;

Users will be able to choose their timezone by specifying the extended timezone name tz.name. I'm not using nor allowing any timezone abbreviation in any way.

According to the official docucmentation I can change the timezone abbrevs, but I'm not finding any piece of information about the Olson DB's  version used by Postgres. I really would like to have control on the Olson DB, so:

Where can I find any information about the timezone's DB version used?
Is it possible updating the timezone's DB by replacing the newest official IANA version? Consider a legacy system with Postgres 8.4 and no chance of updating it.

Thanks so much guys


texts: ExceededTimeLimit: Operation timed out after uprgade from 3.2.8 to 3.2.10

texts:
We are running our production cluster using version 3.2.8, Our cluster consists of four mongos , three config servers and ten shards-3-node-replica-sets , we perform both primary and secondary reads.

Very frequently we hit SERVER-26159 and our mongos crashes. According to the bug report the issue was solved on 3.2.10 and we upgraded on Oct 11.

10 minutes after the upgrade completed we start receiving errors that the mongos can't connect to the shards due to timeout. Timeouts were very frequent and reduce our processing throughput to 20%. We forced to rollback to 3.2.8 and the issue gone away, we don't receive any timeout but the SERVER-26159 returned.

Oct 11 06:40:03 [NetworkInterfaceASIO-TaskExecutorPool-1-0] Successfully connected to node1-48668:48668
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-1-0] Successfully connected to node0-49179:49179
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-4-0] Connecting to node0-49627:49627
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-1-0] Failed to connect to node0-49630:49630 - ExceededTimeLimit: Operation timed out
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-4-0] Connecting to node1-49631:49631
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-4-0] Connecting to node0-48668:48668
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-4-0] Connecting to node0-48574:48574
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-1-0] Failed to connect to node0-49631:49631 - ExceededTimeLimit: Operation timed out
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-1-0] Failed to connect to node0-49631:49631 - ExceededTimeLimit: Operation timed out
Oct 11 06:40:03 mongos16026 mongos.16026[27341]: [NetworkInterfaceASIO-TaskExecutorPool-1-0] Failed to connect to node1-48574:48574 - ExceededTimeLimit: Operation timed out

I check MongoInc for relevant jira's but I didn't find any. Has anyone else deal with this issue? Is this a known bug in 3.2.10 and is there any workaround?


texts: PostgreSQL - trying to speed up query responses from view that has join and group by

texts:
I have these two tables:

precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).

Column   |         Type
---------+----------------------------
 id      | varchar
 country | varchar(2)
 time    | timestamp without time zone
 freq    | varchar
 prec    | float(4)
(2,481,069 rows)

areas: store multipolygon areas for all Europe down to municipality resolution.

Column   |         Type
---------+----------------------
 id      | varchar
 country | varchar(2)
 geom    | geometry
(162,573 rows)

I need to create a view that combines the spatial and temporal values from these tables, so I created it this way:
create or replace view prec_geo
as select * from
    (select
        max(country) country,
        id,
        "time",
        sum(prec) as prec,
    from precipitaion
    where id <> 'ALL'
    group by "time", id) as prec,
    areas.geom
where prec.id = areas.id and prec.country = areas.country;

The view now shows the columns and values I need to make further queries.
In particular, I need to make queries selecting different time and freq.
EXAMPLE:
select geom from prec_geo where "time" = '2020-03-13 00:00:00' and freq = 'daily' and country = 'it';

I created different indexes on my tables in the hope to speed up the queries.
In precipitation table I created:

a combined index on multiple cols (time(1), freq(2), coutry(3))
a non unique index on id col (because it is used in the join to create the view)

In areas table I created:

a spatial PostGIS index on the geom col
a combined index on multiple cols (country(1), id(2))

I then ran VACUUM ANALYZE on both tables.
Still, the result of the query takes about 7 to 10 seconds, while I would need faster responses.
What would you suggest in order to increase performance?
Info on my setup: PostgreSQL10, PostGIS 2.5.


texts: Oracle - Is it possible to maintain transaction across multiple schemas?

texts:
I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower layer. So there is this scenario:

I execute a SP in the upper layer which results in some inserts in both layers but for some reason one of the transactions in the lower layer fails. Is it possible to rollback the whole thing?


texts:
I have a Microsoft SQL Server 2012 server used by two Active Directory groups.

How can I configure this groups so that:

group 1:
Can do everything on database level, including read everything, write everything, add new databases, modify or delete them, and restore backups.
However, the group can not change the settings of the server or shut it down.
group 2:
Can read and write in every database but cannot modify its structure nor change server settings.

I've tried to add the groups to the model database, which works fine when creating new databases.  However, if there are restores from other servers, the groups have no access to it.

Is there a way to define a server wide role or right, such that it applies to every database, regardless if the database was created or restored?


texts:
I have two tables with the following structures:

CREATE TABLE USERS
(
  USERID VARCHAR2,
  USERNAME VARCHAR2
);

CREATE TABLE CONFIRMATIONS
(
 PERSONELID NUMBER,
 ACCOUNTING VARCHAR2,
 FINANCE VARCHAR2,
 HR VARCHAR2
);

ACCOUNTING,FINANCE,HR columns of the the CONFIRMATIONS table correspond to the USERID column in the USERS table. I need a query that will put USERNAME corresponding the USERID if in any of the ACCOUNTING,FINANCE,HR columns have a value in it. So suppose we have the following data in the tables:

USERID|USERNAME           PERSONELID | ACCOUNTING| FINANCE |  HR
---------------           ----------------------------------------
   1    Jack                  113          2          1      NULL
   2    Rony                  120          3         NULL      2
   3    Michel

I need something looking like this:

PERSONELID | ACCOUNTING | FINANCE |  HR
----------------------------------------
    113         Rony        Jack    NULL
    120        Michel       NULL    Rony


texts: Can a single SQL Server instance issue the same SYSDATETIME twice?

texts:
I am contemplating using a datetime2(7) as a clustered index.

If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming we don't do something like set the system clock back)?

Alternatively, what is a cohesive strategy for ensuring uniqueness on such a column, even if it that comes at a cost of time precision?

For my use, I really only care about the seconds, but I will be doing a lot of range queries on this column so the CI seems appropriate.


texts: Backup Files with Append option growing huge Vs Better Options

texts:
We have close to 5 Databases .

Below are the .mdf and .ldf sizes .

NOTE : Along with this we do also have the FileStream enabled for these Databases .

Kindly suggest me on below :

As of now we are using the third party tool for Backing up the data for these databases .

Most importantly they are appending backup files and now they have grown close to 800 GB .

After all , now I am just thinking of proposing the idea of daily individual files instead of appending the backup files daily and letting them grow huge .

I am thinking of it for two reasons .

For Restore of the Database from backup file in a little comfortable way , than restoring a huge file .
Next , I am thinking it will be easy to purge the backup files easily , I mean the old backup files .
Backup file maintenance , we can maintain last 5 days or so and remove the previous ones .

Please let me know :

What will be the disadvantage of my above plan .
How is it different from appending backup files and daily individual backup files with Date Time Stamp appended to the filename daily basis .
Will the Database backup also includes the FileStream Data ?
How better I would have thought than what I am thinking now . Which is more recommended and better , Overwrite / Append / Individual Backup Files with Date-Time Stamp ?

Thanks for all your time .


texts: Joining from database A to view in database B; where is processing done?

texts: If I join tables from database A to a view in database B (same server), are the joins within the view in database B processed on database B before joining to the tables in the current database?


texts:
We have a database running on SQL 2008 R2, in 2000 Compatibility mode.

Is there a way to configure the database so that implicit insert statements (i.e. those without a column list) are invalid?

I want to stop developers writing scripts like this:

Insert into foo
values (2, 3)

and instead use

Insert into foo (Val1, Val2)
values (2, 3)

especially within stored procedures, but across the board if at all possible.


texts: How can I measure the number of IOPs for a certain query

texts:
I need to estimate the max IOPs for a certain MySQL DB (one that does not exist yet).

The OS is Linux and the DB is MySQL 5.1.x (though I don't think the DB brand is important for the asked topic)

The questioned table is extremely simple. user table with some attributes. and the dominant query is:

SELECT * FROM users WHERE username='';

I think about populating a table with huge amount of records and use iostat to calculate the IOPs average per query. Later I can use the same method to test SELECT.

Is this a reasonable test for measuring IOPs?
Are there other better methods to accomplish this?

Thanks


texts: Windows Authentication for a non-local non-domain Login

texts:
I have SQL Server 2008 R2 installed on a Windows Server 2008 R2 box.  There is no domain in the network.

Is there any way for me to add a Windows Login on SQL Server for a non-local and non-domain user?  For instance, if the server is Server1 and a PC on the network is PC1, how can I add PC1\User1 as a Windows Login on the SQL Server instance installed on Server1?


texts:
My SYSAUX tablespace is 98% full. In my tablespace statement AUTOEXTENSIBLE=yes.

So I want to know that will it add a new data file if it reaches 100% or if not then should I make makesize to unlimited?


texts: Duplicate entry for key. even though it is not

texts:
Above image describes my tables 'Details'

And above is my entire table only one row. and  i have tried 'INSERT' to table

INSERT INTO `details`(
  `Name`,
  `dob`,
  `age`,
  `qualification`,
  `ExpYr`,
  `ExpMnth`,
  `Email`,
  `Contact`,
  `resume`)
VALUES (
  'viviek',
  '0899-06-21',
  '1117',
  'hihh',
  '2',
  '3',
  '1026viivek@gmail.com',
  '9645904409',
  'Array')

but it showing an error like

  Duplicate entry '2147483647' for key 'Contact'

Why is it showing error with different value than key value in table for contact?


texts:
This SO answer outlines how MySQL organizes its database into files, but doesn't describe the structure of the files themselves.

I am wondering, at the physical level, how relational database management systems store their records in files. What the structure of the file is roughly, where the columns/values are (if it's just a CSV), and how it is traversed.


texts:
Hello guys can someone help me,
I create a two table and one of the table contains a foreign key, i have been trying to select the row that contains data in the table but instead MYSQL is give me only the column without the data or the row, to be specific both table contains one row
example of what i was trying to do in mysql is
SELECT CourseName FROM course WHERE CourseName='Information Technology'
i was also trying to search for a course that contains the same name on the database, so as to notify the user that the course already already exist


texts: How can I verify that my SQL Server edition is Express?

texts:
I have a SQL Server (version 11.0.5058). I tried to restore a database from a bak file and I got an error saying:

  CREATE DATABASE or ALTER DATABASE failed because the resulting
  cumulative database size would exceed your licensed limit of 10240 MB
  per database.

I was told that I do not have an Express instance, and thus I do not have a database limit of 10GB. I was not able to find information to verify this. Although under Help->About the word Express does not occur. How can I verify the database limit information?

What can be the problem?
Is this something in my configurations I can change?


texts: What does this Oracle TO_CHAR format do: "FM99990.90"

texts:
I have this number format and not able to find examples of how it will look. It is not listed on Oracle website. This below SQL is what I have and not sure what the result will look like since I don't have access to Oracle SQL at the moment to be able to run and see the result.

to_char(deductionAmount, 'FM99990.90')

Does anyone know? Deduction amount is in this format most likely: xx.xx


texts: mongodb setFeatureCompatibilityVersion command not found

texts:
I'm trying to upgrade my mongodb from 3.2 to 3.4 and I try to set the compatibility version by:
db.adminCommand( { setFeatureCompatibilityVersion: "3.2" } )

But it says

{
        "ok" : 0,
        "errmsg" : "no such command: 'setFeatureCompatibilityVersion', bad cmd: '{ setFeatureCompatibilityVersion: \"3.2\" }'",
        "code" : 59
}

I'm not sure why the command is not found. Can somebody point me out in whats wrong?

Here is the mongodb version info from db.serverStatus().version

  3.2.20

P.S: I'm running the command on admin database and authenticated on it as a user with root role.


texts:
I setup my MariaDB on my virtual machine and I can successfully connect to it locally.
I can ping the machine too. However, when I contact via the host machine, I do not understand why instead of 192.168.0.20, my machine insist on contacting a wrong ip 192.168.0.8?
C:\applications\mysql_x64\bin>mysql -u remoteroot -h 192.168.0.20
ERROR 1045 (28000): Access denied for user 'remoteroot'@'192.168.0.8' (using password: NO)


texts: Is it realistic to expect a database of size over 50 GB (some 300+ million records) to be imported within 3-4 hours on a single server (MySQL)?

texts:
I have hundreds of millions of rows in a text/csv file (genomics database btw - each record is less than 255 characters long...).

Ideally I'd like to make them searchable since right now my best bet is spiting them (a little help from cygwin!) and reading them one by one as a text file ~500mb from notepad++ (yes...i know...) - so this is very inconvenient and caveman-like approach.

I'd like to use MySQL but maybe others, have budget of up to $500 for Amazon instances when needed - maybe 32gb ram some xeon gold and 200gb hard disk on Amazon can do it? No problem to use up to 10 instances each of which doing concurrent insert/loading.

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram - if I make it to even 50,000 rows/second and then be able to query it with say phpmyadmin in normal time -  I'd be happy. Thanks!


texts: MongoDB - Database vs Collection memory-wise

texts:
Lets say for example i need to have 5 collections, each collection is about 10GB.
What is the difference in performance, with emphasis on memory usage, between assigning each said collection to a database, versus having all of these collections in the same database?

Also, in this scenario, whats the difference between MMAPv1 storage engine and WiredTiger?

EDIT: Spoken to a MongoDB team and they assured me there should be no difference between having multiple collections in a single database versus one collection for each multiple databases.


texts: Export to format compatible with SQL Server

texts:
I need to provide data in a format that could be imported into a Microsoft SQL Server database; however, I do not have Windows or access to a SQL Server instance.

The data is currently in several CSV spreadsheets. I looked at BULK INSERT but found their support for CSV is quite limited, such as not recognizing double quotes (documentation: Specifying Field and Row Terminators). I have read that edge cases like when a field happens to include a quote (and so needs to be escaped) are not supported.

Is there a more reliable intermediary format that could be generated from Linux/OSX? For example MySQL dump, Excel, Sqlite, XML, etc?

I also need this import process to create the tables and not require coding. The person importing is non-technical. It would be a one-off process.

The target system is SQL Server 2008, though I am ideally interested in a solution not tied to a specific version.
The data to import is ~100MB in a single CSV file.
There are embedded field/row delimiters, which works fine when the fields are quoted with the Python csv module.
There is no need to handle foreign keys, indexes, constraints, etc.
The encoding is UTF-8.
The source is scraped data from German web pages currently stored in a CSV file. I could easily import it from there into MySQL / Postgres if that was a reliable vector.

I won't be able to test the import because I don't have direct access to the SQL Server database, so am looking for a more reliable approach. For example a MySQL dump, XLS, XML, etc.


texts: Why Model database in SQL Server is in FULL recovery mode and other system databases are in simple recovery mode?


texts:
If I have a table A which has a composite (two column) foreign key on another table B, should I:

a) create a two-column key in B to match the foreign key in A?
b) or, MySQL automatically creates the 'missing key' in B to match A's foreign key?
c) or, if neither a) nor b) should I assume MySQL might do a table scan on inserting into A?

I'm trying to avoid dubious keys in B as my index already grows at an alarming rate. However, I do not want to penalize insertion/updates into  A and would rather take the index growth hit.

Please advise.


texts: Can SQL Server disable IntelliSense on SSMS?

texts:
I am using SSMS 2016 (ver: 13.0.15000.23) and connecting to SQL Server database 2016 (ver: 13.0.5081.1).

IntelliSense is not working on my SSMS.

I tried the following:

Tools >> Options >> Text Editor >> Transact-SQL >> IntelliSense, and select Enable IntelliSense
Tools >> Options >> Text Editor >> Transact-SQL >> General, and verify Auto list members and Parameter information check boxes are checked
Query >> IntelliSense Enabled
Verify that SQLCMD is disabled
Refreshed IntelliSense cache (Ctrl+Shift+R)
Making sure no compile errors are in the query
Uninstall and reinstall SSMS

I asked other members in my team and IntelliSense only works sporadically for them, if it does.

Question:
Is there a server setting which is disabling IntelliSense on SSMS?

I've already gone through all of these questions:

IntelliSense not working but it is enabled
IntelliSense is not working at SQL Server Management Studio
Why is SQL Server 2008 Management Studio Intellisense not working?
SQL Server Intellisense not working on some servers
SQL Server 2008 R2 intellisense doesn't work
SSMS IntelliSense doesn't work on particular database

I have limited permissions on the instance, but I can select and update records in the database I'm working in (I didn't try insert).  I can see around 1,600 names of objects and columns by running:

SELECT o.name, c.name
FROM sys.objects o
    INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.is_ms_shipped = 0;


texts: How can I repair a large myisam database when diskspace is low?

texts:
MariaDB 10 | CentOS 5.x

I have a large myisam database with some tables that are marked as crashed.  The collective size in /var/lib/mysql/fooDatabase/ is 17 GB.  I have 4 GB of space left.

Initially, I tried running mysqlcheck --verbose --auto-repair fooDatabase but that failed and in logs I see:

[Warning] mysqld: Disk is full writing '.fooDatabase/foo_TABLE.TMD' (Errcode:28 "No space left on device"_. Waiting for someone to free space...)

I restarted mariadb and reclaimed the disk space but I'm back to square one here.

I'm now running repair table foo_TABLE; but I'm not sure if I'll experience the same problem or not.

At this point, I've squeezed as much free disk space out of the system as I can.  Are there any other options?  Are there any repair operations that are more sparing with temporary disk space?  Any other options?


texts: MySQL Group Concat one cell when other cells also match