What permissions does the service account need to use database mail? I have an SQL Server 2012 instance running under a managed service account. I've configured database mail with one account and am trying to send a test email, but I'm getting some errors in the server's event logs:

Database Engine Instance=MYINSTANCE;Mail PID=2132;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp', database 'msdb', schema 'dbo'.
Database Engine Instance=MYINSTANCE;Mail PID=2212;
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The read on the database failed. Reason: The EXECUTE permission was denied on the object 'sp_readrequest', database 'msdb', schema 'dbo'.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)
HelpLink: NULL
Source: DatabaseMailEngine

If I make the account running the SQL Server process a sysadmin, this error goes away, and mails send successfully. However, all the research I've done suggests that giving this account the DatabaseMailUserRole role in the msdb database should be sufficient. I've done this and still receive the same errors.
I took a look in the BOL but couldn't find anything.

SQL Server Agent service account requires sysadmin permissions on the instance.

Microsoft KB


There is a lot of Client Locks and IPC Locks in PostgreSQL I'm monitoring out PostgreSQL server using PostgreSQL Workload Analyzer. before implementing streaming replication there was a lot of IPC locks and now, after replication, there is a lot of  Client Locks that wasn't there before!
I could not find any information on these type of locks. They always appear. The onle explanation in POWA is

IPC: Number of wait events due to inter-processcommunication
Client: Number of wait events due to client activity

Is there a problem?
Are these locks prevent queries from executing? select or update queries?

Those are not locks, those are wait event types. If you are searching for them specifically as lock types, that might prevent you from finding much.  It would be nice to know what the actual wait events are, rather than just the general category.  If POWA doesn't provide a way to drill down on that, you can inspect pg_stat_activity directly for the wait_event column.
These probably do not indicate a problem.  But if you already know you have a problem, they could tell you where to look for it.  Do you have a problem?  E.g. are your users complaining about slowness?


Performance of query from this schema Essentially all comments are stored in this schema (dbml)

table comments {
  commentID BINARY(16) [not null] //UUID
  post_date timestamp
  postID BINARY(16) [not null] //UUID
  has_reply bool [default: false]
  // replyTo either points to commentID, or null
  // Essentially won't be loaded as a 'root' comment
  reply_to varchar [default: null]
  comment_content varchar [not null]
  // dislikes will decrease, likes increase
  likes int [default: 0]
  visible bool [default: true]
}

As the user goes through the posts, essentially the database would be queried as following
SELECT * FROM foo.comments WHERE postID = "ID of the post the USER is currently on" LIMIT 0, 20

Because of this setup, I realized this table would grow quite large housing all the comments.
After a while (say ambitiously) 1 million comments are created.  How would mariaDB handle this? Would it be dreadfully slow, if so how should it be modeled?

Will the WHERE clause save me from performance issues?

What is the PRIMARY KEY of the table?  Probably commentID is unique to each row??  If so, add PRIMARY KEY(commentID).

For WHERE postID = "...", you should add INDEX(postID).  That will make that query fast even for a billion rows.

Read about "indexes" (aka, "keys").


Selective data extract at Schema level I am attempting to achieve the following within a single data pump export:

SCHEMA1 = Structures only
SCHEMA2 = Structures only
SCHEMA3 = Structures and data

The answer in this question only works on the table level:

Export Data Pump all data from few schemas except one table structure only?

It suggests removing data from a specific table using a filter:

SCHEMA2.table1: WHERE 1=2

I need the same but applied to all data in the schemas other than SCHEMA3.

How can this be done?

Filtering options in Data Pump are still something that should be improved. Anyway, you could try the below parameters:

directory=data_pump_dir
dumpfile=dump.dmp
logfile=export.log
schemas=schema1,schema2,schema3
exclude=TABLE_DATA:" IN (select table_name from dba_tables where owner in ('SCHEMA1', 'SCHEMA2') union select partition_name from dba_tab_partitions where table_owner in ('SCHEMA1', 'SCHEMA2'))"

This excludes TABLE_DATA for all tables and partitions whose name match the result of the query provided in the exclude part.

And that is why this may not be appropriate for you. If you have a table called T1 both in SCHEMA1 and SCHEMA3, the data of SCHEMA3.T1 will be excluded, because the above query returns T1 from SCHEMA1, and Data Pump will ignore TABLE_DATA of all table called T1. If you have a partitioned table in SCHEMA2, with a partition called P1, the data of the table SCHEMA3.P1 will be excluded. The above may work if your table and partition names are distinct accross all referenced schemas.

Or you could dynamically generate the expdp command or use DBMS_DATAPUMP to filter out all the table data from SCHEMA3.

But I would rather just run 2 seperate exports for SCHEMA1,SCHEMA2 with CONTENT=METADATA_ONLY and a default export (CONTENT=ALL) for SCHEMA3.


SQL Server : how to create user with access only from specific HOSTNAME or IP addres I have one stupid app which I rely on, which has hardcoded connection string inside.

To increase security of my SQL Server I'd love to make SQL users same as one hardcoded inside app, but I want to allow that user to be able use SQL Server only from certain host (ip address).

You can use a Logon Trigger for that.

CREATE TRIGGER TR_check_ip_address
ON ALL SERVER
FOR LOGON
AS
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1'
        ROLLBACK;

END

If you try to connect from an unauthorized IP, you'll get an error:

Logon failed for login 'bob' due to trigger execution.

Remember that Logon Triggers are potentially evil and might also end up locking everyone out of the instance. Be careful!

However, I think you don't really need to do that. If you want to enable connections from a list of known addresses, a firewall is the most suitable tool for the job. The worst thing that could happen is the wrong user connecting from a known IP address, which is quite unlikely if users keep their credentials carefully.

Also, take into account that IP addresses can be spoofed, so I'm not sure which degree of additional security you would be providing.


How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()? How do I use sys.dm_exec_connections in combination with SUSER_SNAME()?

I want to see the username corresponding to each record in sys.dm_exec_connections.

As others have already pointed out the info you want is in sys.dm_exec_sessions. However if you want to pull from both DMVs this would work:

SELECT c.connect_time
 , s.login_time
 , s.host_name
 , s.login_name
FROM sys.dm_exec_connections AS c
   JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id


Calling a .NET method in a SQL Server script In the data migration process, I need to convert some columns values form the source system before writing to the target table.

I've been provided with a Csharp code which reads a string and do the conversions (it is a complicated string manipulations) and then returns the converted string.

What would be the best way to plug this code in the data migration SQL script? I know little about CSahrp programming, and after researching the different sites, found out this can be done by either adding a script component in a SSIS package, or wrapping the .net code in a CLR function which can be called from SQL Server.

As my script is not part of a SSIS package, my preference would be defining a CLR function, but would be ok? What would be the pros and cons of each options?

Also do you think this goal can be achieved using another technique which is easier to do and maintain?

If you know a good link which shows me the process step by step, please share it with me :)

Thank you.

It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script.

CLR Assembly:

Must be installed into a database (both the assembly binary and the exposed wrapper method(s)).
Requires setting up an external project in Visual Studio, something you may not be very familiar with.
Very easy to integrate into your existing script.
Debugging can be a bit involved, because you have to either debug locally (i.e., on the server), set up remote debugging, or set up a comprehensive test suite. All of these can be problematic for different reasons. (Note: setting up a test suite is a good idea, but if time is a factor and you've already received a tested chunk of code, it's much safer to skip it in this scenario than if you converted the code to T-SQL, for example.)

SSIS Script Component:

Means running a totally separate process to do the calculation.
Much easier to get started than with a separate assembly, where you have to provide all the boilerplate code. With SSIS, pretty much all you do is fill in the required method bodies.
BIDS, which is x86, can't debug x64 scripts, so there's some ridiculousness that comes from that. (See here for a quick fix.) This would allow you to debug locally (on your workstation), though, which is an advantage.

While I haven't tested, I'll go out on a limb and say that the performance of both methods should be similar. If performance is a big concern, set up both scenarios and comparison test. In fact, you may want to set up both anyway just for practice, so you can see some of the differences and similarities for yourself, which is way more interesting and fun than reading about it (well, at least I think so).

A third option would be to move the process entirely to SSIS, which is more suited to migration/ETL types of tasks. This may or may not be feasible. You wouldn't necessarily need to retool the whole process, as you can, of course, execute arbitrary scripts.

Finally, another option is to convert the code to T-SQL. Depending on the complexity, though, it may be quite a challenge. Certainly there are manipulation tasks much better suited to using a proper programming language/framework like .NET. Without seeing the code itself, it's impossible to say if it would even be worth an attempt. As I mentioned previously, you would definitely need to develop a comprehensive test suite in this case.

Personally, I would probably create a CLR assembly because I'm strong on the programming side of things and it would be the fastest, least intrusive way to integrate the code. But that's me, and I haven't seen the code, so it may not be the best solution for you and your environment.


Manually trigger "Write current results to disk" during query Using PostgreSQL 12, I have the requirement that some data needs to be "refreshed" each night. That means: The database has to do some calculations based on the current date and write the result to the table.
That works in general. However, the way it works is not perfect, it seems.

Basically, there is a PLV8 query like this:

DO $$

var myData = plv8.execute('SELECT id FROM my_table');
for(let i = 0; < myData.length; i++) {
    plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);
}
$$ LANGUAGE plv8;

my_function_which_refreshes_lots_of_data takes the id of one item and does all the work. It has to be done this way, because triggering of refreshing also needs to be possible for single rows sometimes.

The problem is that somehow this got out of control. Currently, the query runs for more than an hour, has taken 99% of the memory, fills up the swap space and is most of the time in status "D" - "disk sleep (uninterruptible)".
The duration of the query is not a problem, since it usually runs over night, triggered by a cronjob.
However, the memory consumption and the process status (basically somehow like a zombie) is or might be a problem.

So, my idea was:
As far as I know, PostgreSQL keeps all the changes to tables in memory while the query runs and writes them only at the end.
Would it be possible to trigger manually "write to disk what you've got so far and free the memory"?

I would do that in my code after plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);.
Of course, if the process crashes or whatever, that would result in an inconsistent state: Part of the items would be processed by my_function_which_refreshes_lots_of_data, while the rest would remain as before. However, that's better than no item being written at all. And definitely better than a query running for so long and consuming all the memory.

I know I could somehow try to split the query at client side. However, that would be less convenient for me for some reasons.

No, PostgreSQL does not keep transactions in progress cached in memory. They are written to disk as usual.

All that happens at commit time is that the write ahead log (WAL) and the commit log are flushed to disk.

So you must investigate further to find the cause for your I/O overload.

Useful lines of investigation are:

use pg_stat_statements with pg_stat_statements.track = all to find the SQL statements that cause the load.
set track_io_timing = on to see which queries contribute most to the I/O load.
check if each function call runs in a separate transaction or not.

If yes, bundle several calls in a single transaction - perhaps the many WAL flushes are killing you.


PostgreSQL: create user who can create new roles? I would like to create a user who can create other roles.
For example: user "dennis" can create roles.
User "dennis" owns database "main" and can only access this database.
Would it be possible to for "dennis" to create a role, which can access other databases?

https://www.postgresql.org/docs/9.5/static/sql-createrole.html

  Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role "user" has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard roles that have the CREATEROLE privilege as almost-superuser-roles.


Writing functions in Postgres that returns strings with dots in reverse like java namespaces Looking for a Postgres 9.6 function that reverse sorts a string the same way that java namespaced packages do.  Preferable if this is just PGSQL, I know that this is simple in the V8 engine or Python.

I.e. if I have the string foo.bar.baz.example.com, I want the string com.example.baz.bar.foo.  My very naive attempts like running a
REVERSE on the string didn't work.

I'm thinking I would need to explode on the periods, (can you explode in Postgres?), start a cursor, loop over the set, insert the last item from the set, shift that item off the set, and continue until the set is empty.

But I don't know how to do any of that.

You can do this with a combination of array functions:

select string_agg(x, '.' order by idx desc)
from unnest(string_to_array('foo.bar.baz.example.com','.')) with ordinality as t(x, idx);

you can put this into a function if you want:

create function reverse_elements(p_words text)
  returns text
as
$$
  select string_agg(x, '.' order by idx desc)
  from unnest(string_to_array(p_words,'.')) with ordinality as t(x, idx);
$$
language sql;


Are these two ERDs the same? Do they represent the same thing? [Weak entities and multi-valued attributes] What I want to create is a table in a database for employees in a company.
Each employee has a unique identifier ID and a name. Each employee also has a list of skills, the skills are represented as a string (i.e. "Cashier", "Manager", etc.).
Multiple employees can have the same skill, for example, John is a Cashier and a Manager and Bob is also a Cashier.
I thought of two ways to represent this using an ERD:

Using a multi valued attribute connected to the Employee entity:

Using a weak entity with the following cardinalities:

What I'm asking is, are these two implementations the same, cardinality and all?

These two diagrams look significantly different to me.
You should have a table for employees, a second table for skills, and a third table mapping each employee to each skill they have.


Finding violations of the symmetry constraint Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up exactly twice in the table.

Sometimes, this constraint is violated, i.e., a pair of friends shows up only once in the table. How do I write a query that will identify all such cases (ideally, using reasonably standard SQL)? In other words, I would like the query to return the list of rows in this table, for which there is no corresponding row with the swapped fields.

An additional question: is there any way to enforce this referential integrity in MySQL?

To find the rows, use a left outer join:

select
    a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID
from
    Friends a left join Friends b
        on (a.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID)
where
    b.friend1ID IS NULL ;


RAM Impact on database operations Possible Duplicate:
  RAM Impact on Oracle database operations

We are seeing drastic change in the amount of time taken for db operations depending on ram size.

The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into a db running on a 16gb ram machine. The inserts are made through jdbc. Both the db machines were 64 bit machines and both were enterprise editions of db.

Interesting thing is that the memory consumption for the db running on 4gb machine never goes above 50-60% of the total capacity - so we do not understand why this difference.
Is there any explanation for this behavior or some more pointers to look into this?

The information you provided is not sufficient to analyze the problem, so people will start guessing (BTW how have you established your theory that the problem is memory pressure?). To analyze the problem you need to enable sql_trace for both sessions, even better set event 10046 for both sessions and then compare the data.

this can be done by executing the following sql once the session has been established

alter session set events '10046 trace name context forever, level 12';

This will generate trace files. These trace files need to be analyzed either manually or with tkprof. You need to pay attention to the wait events.

Are there any other differences between the machines in terms of

Processor
Disk speeds and disk types
Network infrastructure
Other load on the machine
Number of database sessions

As these will affect the performance measurements


Incremental backup between now and backup taken one week ago We do a full backup of our SQL Server 2012 database every night.

A new developer needs an incremental (or differential) backup of a backup we took one week ago. The reason is that for them, importing the data takes several days, as their destination is not SQL server and has to go through several transformations.

There have been 7 full backups since the last backup they imported.

How do I get them the incremental backup that they need? Do I use differential or log shipping and how do I account for that fact that the data they have is one week old with several full backups in between?

You cannot go back in time and get an incremental backup as of a certain time.  If the database is in full recovery mode, you can give them the transaction log backups (if you have them) that go from the backup they restored to the time they want to restore to.

However, if they have been using the database, they will have to restore the backup again and leave it in recovery mode so that the transaction logs can be restored into it.  Once a backup is restored and the database is taken out of recovery mode so it can be used, no more backups (differential, incremental, or log) can be restored to it.

You might also check to see what the developer actually wants as I suspect they used the wrong terminology in the request.  Referencing the transformation, etc., it seems that maybe they are expecting to get a backup that will only restore the new records from the time period.  No matter what backup you give them, they are going to get the entire database (unless you are just going to restore partitions, but based on the question I'm guessing that's not really a good option).


Can column uniqueness be commutative? I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?

My table definition:

CREATE TABLE mytable (
    id serial primary key,
    col1 int NOT NULL,
    col2 int NOT NULL,
    unique (col1, col2)
)

This unicity constraint can be enforced with this unique index:

CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2));

Demo:

test=> insert into mytable (col1,col2) values(1,2);
INSERT 0 1
test=> insert into mytable (col1,col2) values(2,1);
ERROR:  duplicate key value violates unique constraint "idxname"
DETAIL:  Key ((LEAST(col1, col2)), (GREATEST(col1, col2)))=(1, 2) already exists.


Is DBCC SHRINKFILE better than DBCC SHRINKDATABASE? In the documentation to DBCC SHRINKDATABASE, Microsoft is warning to run the command because it's i/o expensive.
However, in the documentation to DBCC SHRINKFILE, there is no such warning.
Now when we use simple recovery and basically have only one database file, is it better to run DBCC SHRINKFILE or DBCC SHRINKDATABASE?

A database can (and is) composed of multiple files. So shrinking the database would shrink all the files it's composed of, whereas shrinking a file is more granular (as Tibor pointed out). Therefore shrinking a database will always be at least as heavy or heavier of an operation than shrinking just a specific file of the database.
That being said, it's generally recommended against to shrink either because they're both heavy operations, generally nonsensical to do because if they grew to a certain size they'll likely grow back to that size soon anyway and a growth operation is also heavy.


Alternatives to LIKE and FTS for mid name search I have a question about what are the alternatives for LIKE '%%' and Full-Text Search when it comes to searching organisation names, firstnames, surnames, etc.

The LIKE '%%' causes table scan, however returns the exact results for names containing . The FTS keeps returning results that are "sort of same". FTS is usually offered as an alternative, but I don't understand why. Isn't this meant for unstructured data search? In my opinion it's no alternative for structured data like names.

I've also tried implementations of SOUNDEX, Levenshtein, etc., etc. But nothing gives me the desired performance.

I'm really curious how the experts handle this!

I was afraid of that already :). I have several "names", like organisationname, and indeed, firstname, surname, etc. So the thing our users sometimes only want to use parts of the name where the part to search for is in the middle of a name.

I've tried the following

SELECT  *
FROM    [dbo].[companies] a
WHERE   a.[organisationname] LIKE '%FEK%'

This returns 60 rows, which is the preferred result. However causes a table scan because of the first % wildcard.

When using FTS

SELECT  *
FROM    [dbo].[companies] a
WHERE   CONTAINS(a.[organisationname], '"*FEK*"')

This returns 11 rows. It seems like the wildcard before the term is eliminated.

SELECT  *
FROM    [dbo].[companies] a
WHERE   CONTAINS(a.[organisationname], 'FORMSOF(INFLECTIONAL, "FEK")')

This returns 2 rows. Inflectional doesn't have any effect (probably "of course").

Hope you can give me a clue on how to better use FTS in this, or perhaps another alternative.

There are only two ways to search text data in SQL Server and you've just eliminated both of them.  I assume you have a name field which has first name and last name in it and you want to be able to search for everyone with the name John?  Can you provide some examples of what isn't working correctly?


Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan I like to find missing indexes on the go, looking at the execution plans!
It can potentially give me an indication where further to look at if I want to improve something that is currently running.
For doing this I use the following query:
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    er.session_id,
    er.blocking_session_id,
    er.start_time,
    er.status,
    dbName = DB_NAME(er.database_id),
    er.wait_type,
    er.wait_time,
    er.last_wait_type,
    er.granted_query_memory,
    er.reads,
    er.logical_reads,
    er.writes,
    er.row_count,
    er.total_elapsed_time,
    er.cpu_time,
    er.open_transaction_count,
    er.open_transaction_count,
    s.text,
    qp.query_plan,
    logDate = CONVERT(DATETIME,GETDATE()),
    logTime = CONVERT(DATETIME,GETDATE())
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE
    CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%<missing%'

It generally works fine; however, I have recently encountered a problem with collation and XML:

Error message says:
Msg 6355, Level 16, State 1, Line 40
Conversion of one or more characters from XML to target collation impossible

I have already found out what is causing it:
 -- get only the applications from Italy:
exec usp_sel_outstandingItems
                                    @startdate='2021-04-07 00:00:00',
                                    @endDate='2021-08-15 00:00:00',
                                    @statusDateStart=NULL,
                                    @statusDateEnd=NULL,
                                    @office=N'UK',
                                    @country=N'IT ',
                                    @userState=N'ParticipantPlaced',
                                    @outstandingBalance=0

My question is:
What inside my query gets upset about the collation?
What can I do to possibly get over this error?
collate hints?
select @@version

Microsoft SQL Server 2016 (SP2-CU15-GDR)
(KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57
Copyright (c) Microsoft Corporation  Enterprise Edition:
Core-based Licensing (64-bit)
on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: )
(Hypervisor)

The problem is that you are converting XML data, which is stored as Unicode (UTF-16 specifically), into 8-bit data. 8-bit data requires a code page to know which set of up to 256 characters to use (or up to approximately 24k characters if using a Double-Byte Character Set, but the vast majority of the time it's 256 characters max). The issue here is that there is a character (or possibly several) in the XML data that either a) does not exist in the code page specified by the default collation of the current database, or b) does not exist in any code page.
For example, the following queries show a character that does not exist in most code pages (or maybe none) and so gets the same error when converting to VARCHAR, but converting to NVARCHAR works:
SELECT NCHAR(0x1234);
-- ሴ

DECLARE @Test XML;
SET @Test = CONVERT(XML, N'' + NCHAR(0x1234) + N'')
SELECT @Test;
-- ሴ

SELECT CONVERT(NVARCHAR(MAX), @Test);
-- ሴ

SELECT CONVERT(VARCHAR(MAX), @Test);
/*
Msg 6355, Level 16, State 1, Line XXXXX
Conversion of one or more characters from XML to target collation impossible
*/

Please convert to NVARCHAR(MAX) instead of VARCHAR(MAX). Your WHERE predicate should be:
CONVERT(NVARCHAR(MAX), qp.query_plan) LIKE N'%<missing%'

(please note the addition of the "N" prefixing the string literal — not exactly necessary, but a good habit to get into).
Converting XML data to VARCHAR is only truly safe if the query is executing in a database that has a UTF-8 collation as its default collation, and that is only available starting in SQL Server 2019.
Finally, to answer your question of "would forcing a collation via COLLATE help?": No, not when converting from XML. You can't add the COLLATE clause to the XML data as collation does not apply to XML (you would get "Expression type xml is invalid for COLLATE clause."). And, placing the COLLATE outside of the CONVERT() doesn't help as that is applied after the conversion, which here fails. You also cannot place the COLLATE just after the VARCHAR(MAX) as that is a datatype and not data (you would get "Incorrect syntax near the keyword 'COLLATE'.").

For more info on working with collations / encodings / Unicode, please visit my site: Collations Info


how to select rows which are mapped to a single record in a one to many relationship? I have two tables, consumer and user. Consumer table has a foreign key for user table,say user_id mapped to user id in user table.
There are some rows in consumer table which have one to one mapping with user table while some rows are mapped to same user id in user table.
I want to select only those rows from consumer table where more than one record is mapped to a same user id.

Unless you are needing to return data that is in the user table you don't really need to involve it in your query. In that case you could implement a query such as

SELECT c.user_ID, c.col1, c.col2
FROM consumer c
GROUP BY c.user_ID, c.col1, c.col2
HAVING COUNT(c.user_ID) > 1

Were you to need a result set including results from both tables I would use a query similar to the one Marco suggests.

SELECT c.user_ID, c.col1, c.col2, h.col1, h.col2
FROM consumer c
INNER JOIN user_table u
      ON c.user_ID = u.user_ID
GROUP BY c.user_ID, c.col1, c.col2, h.col1, h.col2
HAVING COUNT(c.user_ID) > 1


CSV file restore in mongodb Export MySql data in following command.

SELECT id,documentfile
FROM documentuploads
INTO OUTFILE '/home/home2/agri_doc.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

CSV file data

"3","%PDF-1.7\
%����\
1 0 obj\
<<\
/Type /Catalog\
/Pages 2 0 R\
>>\"

I am currently using mongodb shell version v3.6.3. i want to import my csv file in mongo database, and I have created a collection documentupload where only two field id, docs. data is latin1_swedish_ci encoded. I tried following command.

mongorestore -d agrilicense2 -c documentupload  --type csv --file /home/shiva/home/agri_doc3.csv  --fields id, docs --headerline

Show output
error parsing command line options: unknown option "type"
Thank you

You can import the (.csv) file through the following commands

mongoimport -d databaseName -c collectionName --type csv --file locationsOftheCSVFile.csv --headerline

For example here I have created the database name test and collection name StackExchange and going to import the LocarionReq.csv file.

> use test
switched to db test
> db.createCollection("StackExchange");
{ "ok" : 1 }
> show collections
StackExchange

C:\Program Files\MongoDB\Server\4.0\bin>mongoimport -d test -c StackExchange --type csv --file C:\test\LocationReq.csv --headerline
2019-04-11T10:58:53.147+0300    connected to: localhost
2019-04-11T10:58:53.263+0300    imported 1000 documents

Now you can check the total document records through the mongo shell command

>db.StackExchange.find().count();
 1000

If you want to find the document records through mongo shell then run the command such as

> db.StackExchange.find().pretty(); -- an easy-to-read attractive format.

Or

> db.StackExchange.find();

  Note : Run mongoimport or mongoexport from the system command line, not the mongo shell.

For your further ref here


More than 20 columns in database table MySQL I have an object, that is described by 40 properties, is it good a idea to create a table with 40 columns? I think that I cannot break these properties into another table (such as table_properties_1 and other).

What do you think, professionals?

upd:

Offer table will be contain more fields...

There is nothing wrong with tens of columns in a table. Depending on the property types it can be more efficient in terms of space than a separate properties table, and it is almost always faster to query if you want several of the properties at once in one row of a resultset, i.e. it you want to return:

Thing  Prop1  Prop2  Prop3
-----  -----  -----  -----
T1     T1P1   T1P2   T1P3
T2     T2P1   T2P2   T2P3

instead of:

Thing  Property  Value
-----  --------  -----
T1     P1        T1P1
T1     P2        T1P2
...

because you won't have to have many joins (or, worse, subqueries) to the properties table to transpose the data. Of course if you regularly need the data in this transposed format then this recommendation is reversed as you'll need to transpose the other way (with several unions) otherwise. Even if your DB has efficient transposition functions, make sure the data is in the arrangement you are most likely to need it in most of the time.

Most DBs allow many columns per row and are often not much less efficient with wide tables than they are with thin ones (unless you "select *" all the time and/or have bad index choices).

The massive caveat to the above is that you should be careful that you are not creating a nightmare for yourself by way of denormalisation. We could only give advice on that matter if we knew what the column contents were expected to be.


How to warm a MongoDB index We're running a huge MongoDB with a large index (made of of four fields) which was created ad-hoc and should now be superseded by slimmer one (of the first two fields). But the problem is we can't just delete the old one as the warmup of the new one may take hours (or days!). We have two secondary instances, is it possible to only delete the large index on one of those and have that instance take over when the new index is warmed? Or is there some way to guide Mongo to the new indices?
Edit: on a small test database Mongo rejects the new index after I delete the old one and instead uses half of third index, even though the new one is a perfect fit for the query. How to guide/update the query plan?

I tried this on a collection with millions of documents, and hope this is going to work in a multi-billon document collection:

Create the index in the background. This can seriously hurt your .finds. We got timeouts for the duration of the index creation.
Clear the plan cache.
Create a script to scan all your data with hint to warm the index.
Delete the old index.
Clear the plan cache again.
Try to find some data with the new index, and ensure it's used with explain. You must use different keys each time to ensure explain does not cache your query.


Not displaying special characters imported from a file The scenario is a SQL Server instance, a database which is data fed using BULK INSERT operations mainly, and some of the texts inserted contain special characters like ñ because I am working in a Spanish environment.

So, after the first little tests I realize these special characters are not being displayed correctly when I run a simple select, so I start checking everything I can think of:

File encoding: the file to be bulk-inserted has the right encoding: ANSI
Database encoding: the database has the right encoding (thank god): select collation_name from sys.databases where name='DBNAME'; results in SQL_Latin1_General_CP1_CI_AS

Latin1: charset used. This suits me fine enough
General: nothing really interesting here
CP1: this means that it uses the codepage 1 which, in short, means the codepage 1252 <=> codepage used for the encoding WIN-1252 which is fairly similar to Latin1
CI: case insensitive
AS: Accent sensitive, so á is different from a

export data to file and check: file encoding is ANSI but the data is not displayed correctly, there are no special characters, instead I find some other characters that make the text hard to read.

With these tests, I conclude that the data is not being stored correctly, and that is why it is not being displayed and exported correctly. Almost every solution I have found on the internet suggests using nvarchar instead of varchar for string datatype fields, but that does not solve this scenario. What is it that is sabotaging my insertions?

If characters are being imported incorrectly, then there is a problem with one (or both) of the following areas, as this is really a two-step process:

BCP / BULK INSERT does not know how the file is encoded and is interpreting it incorrectly
The destination column is VARCHAR (or CHAR {or TEXT, but don't use TEXT} ) and the Collation of that destination column (not database) uses a code page that does not have a mapping for the incorrectly imported character(s).

It is important to note here that the default Collation for the database where the import table resides is not relevant. The only Collation that matters here is the Collation of each particular string column being imported into. And the Collation of each and every string column can be different, and none of them need to be the same as the database's default Collation. It is just typical to have the Collation of most columns in a database match the database's default as that will be the Collation used when creating new tables and columns and not specifying a Collation via the COLLATE keyword.

Step 1: File Encoding
BCP / BULK INSERT (or most any other code reading a file) will not know what encoding a file is using unless the file is using one of the few encodings to have a Byte Order Mark (BOM). But Extended ASCII encodings don't use byte order marks and thus cannot be programmatically determined (at least not with certainty). When using Extended ASCII encodings, you either need to specify the code page or else a default will be assumed.

As per the MSDN page for the bcp Utility, under the -C option:

  OEM  ->  Default code page used by the client. This is the default code page used if -C is not specified.

You can determine the default code page by opening a command prompt and running either mode or chcp (I prefer chcp as it also allows for changing the code page if you pass in a value).

If your default code page is 850 and yet the file was saved with an encoding of Windows-1252 Latin1 (ANSI), then there could easily be problems interpreting the file since the character mappings are not the same between those two code pages. This has nothing to do with SQL Server in any way.

The ñ character has a value of 241 on code page 1252. BUT, on code page 850 that same character has a value of 164. The file, regardless of anything else, is a series of bytes, and one of those bytes has a decimal value of 241 (because when it was saved, it was told to use code page 1252 which determined that ñ needed to be stored as 241). Now, when BCP reads the file, if it is using the default MS-DOS code page of 850, that same byte value of 241 maps to character ±. If you were to specify a code page of either ACP or 1252 (same thing) via the -C switch, then BCP will know that a byte of value 241 is actually ñ. Or, you could specify a code page of 1255 (Windows Hebrew), then BCP / BULK INSERT will interpret that same byte value of 241 as being character ס.

Step 2: Destination Column Datatype and Collation

Once BCP / BULK INSERT (or any client app) reads the data in, it exists as those mappings, not just the base byte values. Whatever characters were read into BCP / BULK INSERT will be stored in the destination column as that character, as long as that character can be mapped in the destination datatype and Collation. A destination datatype of NVARCHAR, NCHAR, and NTEXT (but don't use NTEXT) can hold all characters, so it won't matter what the Collation is. But, if the destination datatype is VARCHAR, CHAR, or TEXT then the Collation will determine the code page which in turn determines the character mappings. If the destination datatype is one of those last 3 mentioned and uses Collation associated with the same code page used for the file, then everything should work just fine. Or, if the Collation is associated with a different code page, then an attempt will be made to map the character, not the byte value.

Meaning, if BCP / BULK INSERT is working with a code page of 1252 and a character of ñ (value 241 on code page 1252), then if you import that into a VARCHAR column with a Collation of SQL_Latin1_General_CP850_CI_AS -- which uses code page 850 -- then you will see a character of ñ (same character, but value 164 on code page 850) instead of ±, which has the same 241 value on code page 850. But, if you import into a VARCHAR column with a Collation of Hebrew_CI_AS -- which uses code page 1255 -- then you will see ? instead of ס (value 241 on code page 1255) as there is no mapping for ñ on code page 1255.


Query job history for TSX servers from MSX server using TSQL I am attempting to construct TSQL queries to substitute various GUI tools provided by SQL Server Management Studio. One of these tools is the Job Activity Monitor, accessible through the Object Explorer window.

Our current database environment includes a cluster with 3 nodes(one primary and two read-only replicas), as well as a single, independent server standing next to the cluster.

For brevity, I'll call the nodes N1, N2 and N3, and the independent server S1.

Recently, we configured Multi-Server Job Administration for our servers, using S1 as the MSX (master) server, and N1, N2 and N3 as TSX (target) servers.

This means that SQL Agent Jobs that operate on the cluster-nodes are created and managed from S1.

TSX servers report their state, outcome etc to the MSX server, which can be
accessed using said Job Activity Monitor. From the Job Activity Monitor, the job-history of each enlisted server (TSX) can be accessed by selecting View history:

Which takes us to this window:

This is the information I am trying to recreate, using a TSQL query on the MSX server. I want to see the outcome and history of the jobs by all enlisted servers, similar to what is displayed by the GUI window.

I've tried digging through the job-related tables and views of the msdb database, but with no luck. The msdb.dbo.sysjobhistory table on the MSX server contains no history from enlisted servers, and I can't seem to find any good documentation on how else I would go about gathering it. Is this data even accessible through TSQL? Any relevant resource is greatly appreciated.

Whenever you want to see what SSMS is running, you can always fire up a Profiler/Extended Events trace and filter on your login.

Doing this from MSX master and viewing a Multi-Server's job history gets you this query:

SELECT null as instance_id,
     sj.job_id,
     job_name = sj.name,
     null as step_id,
     null as step_name,
     null as sql_message_id,
     null as sql_severity,
     sjh.last_outcome_message as message,
     sjh.last_run_outcome as run_status,
     sjh.last_run_date as run_date,
     sjh.last_run_time as run_time,
    sjh.last_run_duration as run_duration,
     null as operator_emailed,
     null as operator_netsentname,
     null as operator_paged,
     null as retries_attempted,
     sts.server_name as server
  FROM msdb.dbo.sysjobservers                sjh
  JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id)
  JOIN msdb.dbo.sysjobs_view     sj  ON(sj.job_id = sjh.job_id)
  WHERE sj.name = 'MULTI_SERVER_JOB_NAME'


why is io_stall_writes_ms so much higher for tempdb? We have the user and system data files on the same disk drive.  The ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) is below 2 for the user files but the tempdb files are typically over 400.  I see that on a few servers and I'm curious if there is a reason it takes longer to write to tempdb than a regular database data file.

SELECT DISTINCT UPPER(LEFT(mf.physical_name, 1)) AS Directory,
( io_stall_write_ms / ( 1.0 + num_of_writes ) ) as result,
io_stall_write_ms, num_of_writes,
fs.database_id,
fs.[file_id]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]

Thank you,

Short Answer: Seeing higher IO stalls may or may not be a problem in an of itself. You need to look at more information to suss out if it you have an issue. It does seem a bit high, yes, but are you suffering? If so, it is probably because either your IO system is not handling the load right (because it can't, because you have everything on one drive or some other reason) or you are doing too much in TempDB (changing the first problem - the IO performance - is probably an easier and more efficient fix, but first determine if you have a problem)

The longer discussion/answer:

There are two questions at play here -

1.) What do I do when I see high IO Stalls?

First off, "high" is in the eye of the beholder. If you were to ask 10 DBAs what "too high" is for IO stalls you'd probably get 2-3 different answers with numbers in them, 5-6 "It depends" answers and one blank stare. My assumption is an average of 400ms is potentially too high here, especially when the other DBs are 2ms or lower for the average stall time.

Regardless of which database is seeing the high stalls you should approach it the same way. An IO stall is what it sounds like... An IO request taking longer than expected.. Stalling. These happen. They happen all the time in a system with resources being shared and finite resources (really all of our systems). They become an issue when the stalls become performance problems or lead to them. So I trust that you are looking here as a proactive part of monitoring or because you were experiencing performance issues that you are troubleshooting. We also don't want to get lost in just IO stalls. We are looking at a piece of the puzzle and not the big picture. It can be troublesome to just look at wait stats or file stats since SQL was last restarted because you are looking at all time and some maintenance window or heavy load window could skew counters. So make sure you look at the full picture.

But when I suspect I have a disk performance issue or see something off in a query like this, I normally follow a process that looks like:

Look at the wait statistics on the server. @swasheck shared a great link as a comment in a below answer. This takes you to Paul Randal's post on looking at and analyzing wait statistics in SQL Server. Go there.  What kind of waits are you seeing? Do you see waits related to IO performance (PAGEIOLATCH_*, IO_COMPLETION, WRITELOG, etc. ?). If you do this is another indication that you have some IO related performance issues, just like the IO stalls. But it gives you another form of agreement here.
Look at the IO performance. In particular, look inside of perfmon at the Physical Disk:Avg Disk Sec/Read and Avg Sec Disk Sec/Write counters. These measure your latency. Watch these counters over a period of time saved to a performance log file. What did you see for averages? If you are seeing numbers over 0.020 seconds (20ms) this could be an issue. If you see numbers over 40-50ms avg or higher is a more firm indication of a problem. Also look at your spikes? How high do they go and how long do they last? If you see spikes into the hundreds of ms and they last for tens or scores of seconds or more and/or happen frequently you are more likely to have an issue with your IO performance for your workload.
Look at your IO setup. What is it? Local disks? SAN? Storage Array? What kind of throughout and IOPs should you see out of this? Is it sufficient for what you are trying to do? You may have undersized your IO for your workload. Don't just look at your physical spindles, RAID settings, etc. Look at your paths to your disks. Are you pushing everything through a single 1GB link that you are sharing with a lot of other traffic? Can you look at disk performance metrics from the storage's perspective.

(Note: for this wait stats analysis and perfmon analysis - look at various periods and type of usage. Do you have different usage statistics at night than you do during the day? Batch processing windows? Maintenance windows where you rebuild a lot of indexes? Look at these tools during each of these periods and understand what you are seeing for each)

Another IO performance consideration here -

You said system DBs and User DBs are shared. Is this production? If so, that isn't always the best scenario. Are you also sharing log file and data files on the same drives? That isn't the best scenario either. What else shares this storage? In a world where you are worrying about spindles and raid groups and disks and have to make decisions on who gets the best performing disks, I tend to (as a general rule of thumb.. which aren't great to have in the DB world but this one tends to hold true) go with my fastest and most dedicated to TempDB (more on that below), then the log files, then the data files. In a world where you have a big pile of disks on a device like a NetApp, Dell Equal Logic or EMC VNX, etc. you don't need to necessarily worry about separation as much - but you still need to make sure that the total throughput and IOPs your system can deliver (and the switches, cards and cables between you and your disks can deliver) is sufficient for your workload.

2.) What are some reasons TempDB could be higher?

So TempDB is a database and it can have IO stalls like any other database as I just discussed. But what are some reasons TempDB can have higher reads? (not exhaustive, I welcome additions or thoughts in edits, other answers or comments) -

Because of your code - Are you using TempDB a lot in your code purposefully? A lot of temp tables and table variables created and destroyed? Doing a lot of things in TempDB like this? That isn't bad or good necessarily, but you might look at that and understand your intentional TempDB usage pattern.
TempDB is a shared workhorse - TempDB is one database that is used as a temporary space for user defined temporary objects and various work tables and operations used by your entire SQL instance. How many user DBs are there? What kind of workload do you see in general? TempDB is one resource for all things to share.
Inefficient queries and insufficient memory - Perhaps there are queries that aren't using indexes tightly enough or are doing large scan and sort operations. Large hash operations, and the memory on the server isn't sufficient for these. These operations will "spill" to TempDB as worktables behind the scenes. Sometimes this can be avoided with looking at your query plans and indexing or query tuning. Sometimes it happens (more so on warehouse workloads, I find). If you have enough memory, this can help, but these queries can still spill at times. Look a this as well.
Are you using Read Committed Snapshot Isolation level with a fair number of updates in your system? This can also result in increased TempDB activity.

The point is - TempDB is used in a lot of ways, and it doesn't surprise me at all to see it as one of your busiest, if not the busiest, database. It also doesn't surprise me when I see it as having the highest number of and highest average stalls of all databases at a client's site. It is the nature of its workload sometimes. Looking at some of the things I've mentioned here can certainly help you determine if these numbers indicate a problem and if so, how to go deeper in solving it.


Join on clustered tables shows greater cost than non-clustered tables? I have created a cluster and have two tables emp and sep on the cluster.

When I do the simple query...

explain plan select * from emp_cluster join dep_cluster using (dno)

the cost is 26.

I created two other tables on heap (emp_heap and dep_heap). When I do:

explain plan select * from emp_heap join dep_heap using (dno)

the cost is only 15, Which is less than that of the cluster.

I have inserted 33,000 records in the emp tables and 99 records in the department tables. I know that in join cluster behaves well but in my case it the opposite is true?

Most likely using a cluster for this query won't be beneficial.

A cluster in Oracle allows data from multiple tables to be stored physically close when they share a common key (here dno I suppose). This allows some query to perform better but a cluster will intrinsically consume more space than standard heap tables because for each key there will be some unused space. Insert-only heap tables on the other hand are one of the most efficient way to store data space-wise, since the rows fill all blocks nicely up to the HWM.

In your case since you don't have a filter so all rows will be read, producing a FULL SCAN of the data. Because the rows are stored in a more compact manner in heap tables, the cost will be less than the cost for the cluster.

The cluster, however, should have an edge when you look for a specific key, but this will also depend on the distribution of the data (number of rows per key), and on the length of the rows. You could build an example where the heap tables with regular B-Tree indexes will outperform a cluster for single-key queries.

In conclusion, clustering tables in Oracle will help for some queries, but will also be hurtful to others, it has restrictions and drawbacks, it is not a silver bullet for optimal performance. Heap tables are the default for a good reason: they have good performance for most queries.


Is it possible to fetch or update table data by table field type? Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.

Yes, it's very possible to accomplish that but not with a unique query!

First, you need to know where to look for metadata: This kind of information is stored into an internal mysql database called information_schema, this database in used only internally by Mysql every time you execute any kind of query involving any database entities. There you will find all sort of meta information about all the tables, columns, privileges, users, triggers of your databases:

show tables from information_schema;

The solution I know for your problem involves two queries. First one will return a list of columns with a given data_type (you can specify the database, and table you want to look for too):

select columns.column_name from
    information_schema.schemata
    ,information_schema.tables
    ,information_schema.columns
where
    tables.table_schema = schemata.schema_name
    and tables.table_name =  columns.table_name
    and schemata.schema_name = 'your_database_name'
    and tables.table_name = 'your_table_name'
    and columns.data_type = 'the_field's_data_type_you_want_to_filter'
    -- + any other filter you find useful

With the list of column names that satisfy the properties you want (a given data type, in your specific case) all you need to do is a secondary query to select or update data from that list of fields.

I searched in MySql documentation and unfortunately there is no way to select or update a dynamic list of fields only with SQL, thats why a secondary generated query is needed.

Hope this helps.


How do I unit test queries which are based on dates from now? I've had a hard time making unit test for most of my queries because they are tied very closely to current dates and data which is added to the database could modify the result of the unit test.

For the most part I've been able to avoid problems by relying on a second version of the database filled with immutable dummy data. So my unit tests can go on with expected results without much of a hitch.

But there are some queries that I still don't know what to do with. I have a query that fetches two columns, a date and an associated value from a table based on:

WHERE Date > Now()

So my enemy here is that tomorrow isn't today. I am using the actual table not a dev version of this table for the test as data entered in this table is sensibly static and shouldn't be changed once added. It's like a reference table so to speak.

If I were to make an immutable dev version of the table then my problem would be that eventually the table's dates will in two years time pass the latest date within the table and thus result in zero records found.

And regardless of whether I had a dev version or a real version, the results for this table will always change based on the current date.

Why does an answer come only once I have finished writing my question out. A thought just occurred to me, which I must say, I want to avoid, but it seems to me that in order to make  a unit test function, I would have to have my unit test generate the table before running the query. But then, do I write a unit test for the version of the unit test that populates that table??

Is there a more sensical way of achieving this goal?

Conclusion

All the answers are valid. The one I selected as the accepted answer is the one I believe to be closer to a standard tdd way (a purist viewpoint), however it's more complex and annoying. The other answers though are very simple and nice, should not be overlooked. In fact the little database trick that RolandoMySQLDBA mention is genious for mysql tests.

Since I'm working with Access, For now what I am doing is making a default db which will be programmatically appended to the dev tables which are static. When time permits that table will have it's own append table that will alter the dates to work with the now() query.

For now() I guess that will do, time permitted.

If I have understood it correctly, what you can do is declare a Datetime variable and store current values in it, run your statements and use datetime variable in your where clause.

so

DECLARE @Now DATETIME
SET @Now = GETDATE()

Your Insert Statements

Your SELECT WHERE Date > @Now

does that solve your problem ? or have I miss understood your Question ?


Dropping indexes and disk space If I drop a large index and re-create it, does that increase the size of the datafile where the index is stored?

When I drop an index, it's quite fast. However, when I build one, it takes time, and when checking free space on the datafile, I see it has been reduced.

Assuming you are creating the index in the same tablespace that it was previously in, the size of the data files associated with that tablespace on disk should not change and the free space in the tablespace should not decrease.

Of course, normally you are dropping the index so that you can do a large data load, and then rebuilding the index.  If that is the case, assuming the table (or the partition you're loading into) and index are in the same tablespace, it is likely that the bulk load will use the space in the tablespace that the index had previously been using so when you go to recreate the index so the data file may need to grow when you build the index even if the index is no larger than it was.  If you are doing a bulk load, it is also likely that the index will be larger when it is recreated simply because the underlying table is larger and the size of the index is proportional to the size of the table (of course, the size of the index increases as O( log(n) ))


What does @ signify in a query expression? What does the @ symbol signify in this sql expression?

update some_table set some_number=(@some_number+1) where some_id=1;

It seems to return the same result as:

update some_table set some_number=some_number+1 where some_id=1;

@ is the "absolute value" operator.

SELECT @ -1;
 ?column?
----------
        1
(1 row)

It returns the same value for positive numbers.


How to define and query in case of table with Mutually exclusive one-to-many relationships Hi I'm the following situation.

I have 5 tables

Entity
------------
idEntity
entityName
...
...
idCountry_fkey
idRegion_fkey
idAggregate_fkey

Country
---------------
idCountry
countryName

Region
---------------
idCountry_fkey
idRegion
RegionName

Aggregate
---------------
idAggregate
AggregateName

Aggregate_to_Country
----------------------
idAC
idCountry_fkey
idAggregate_fkey

I enforced mutual exclusivity with the following constraint con the Entity Table

    CHECK (
    (("idAggregate" IS NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NOT NULL))
    OR
    (("idAggregate" IS NULL) AND ("idRegion" IS NOT NULL) AND ("idCountry" IS NULL))
    OR
    (("idAggregate" IS NOT NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NULL))
);

I've the following questions:

  is this the proper way to handle this situation
  How do I query in order to have:

index | entityname | Aggregate | Country | Region | Value
----------------------------------------------------------
case1 |  A         |  Eurozone |  null   | null   | 1
----------------------------------------------------------
case2  |  B        |    LATAM  |  Brazil | null   | 22
----------------------------------------------------------
case3  |  C        |  Europe   |  Italy  | Lazio | 44

Case 1) when Entity is related to Aggregate
Case 2) When Entity is
related to Country, and the Country is in an Aggregate Case
Case 3) When Entity is related to Region, and the Region is Related To a Country, and the Country is in an Aggregate

It will be something like:

SELECT e.index,
       e.entityname,
       COALESCE(a1.AggregateName, a2.AggregateName, a3.AggregateName) AggregateName,
       COALESCE(c2.CountryName, c3.CountryName) CountryName,
       r3.Region,
       e.Value
FROM Entity e
-- when idAggregate_fkey is specified
LEFT JOIN Aggregate a1 ON a1.idAggregate = e.idAggregate_fkey
-- when idCountry_fkey is specified
LEFT JOIN Country c2 ON c2.idCountry = e.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac2 ON ac2.idCountry_fkey = e.idCountry_fkey
LEFT JOIN Aggregate a2 ON a2.idAggregate = ac2.idAggregate_fkey
-- when idRegion_fkey is specified
LEFT JOIN Region r3 ON r3.idRegion = e.idRegion_fkey
LEFT JOIN Country c3 ON c3.idCountry = r3.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac3 ON ac3.idCountry_fkey = c3.idCountry_fkey
LEFT JOIN Aggregate a3 ON a3.idAggregate = ac3.idAggregate_fkey

Check join order and conditions for each branch - I may make a mistake.
Check if some joins may be INNER.


Database design for cancer mouse models 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:

[

As I said in my previous comment, you should have two main tables.

Observation (the origin of all cases)
Clinical_case (linked to an observation - note that "case" is an SQL keyword).

It appears to me that you are overcomplicating things with so many tables. I think that any report made by a health professional should have the same status - be it a health care worker or a vet - you can keep track of this with staff_id. Have a closed field to indicate the mouse is getting well or (gulp...) being euthanized.

What database are you using to track these experiements/observations? I would recommend PostgreSQL if you haven't yet made a choice. What is your development environment?

Go with the simple system system first - remember Occam's razor - "Entities should not be multiplied beyond necessity". But, should you really need to, you can make it more complex - as Einstein said, "Everything should be made as simple as possible, but not simpler." :-). Better to start simple rather than over-engineering.

[Edit - in response to OP's question about why PostgreSQL is better than MySQL]

more datatypes (IP address...)
CHECK CONSTRAINTS MySQL has workarounds (Triggers...) but these are clumsy and error prone,
CTEs - common table expressions - also known as the WITH clause or subquery refactoring,
Analytic functions,
a richer procedural language (or should I say language(S)),
a CREATE DOMAIN command - very useful and supported by Firebird, Oracle as well as PostgreSQL. Not by MySQL, but maybe by other RDBMSs.
generally (opinion now) - more of a focus on data integrity and correctness. MySQL's fame is built on the fact that it was quick (and nasty) - just perfect for the first Internet boom (1995 - 2000). It also was easy to set up on Windows (unlike PostgreSQL until version 8 (.3?)). It was also trendy with the GPL rather than the *BSD licence.


My HD crashed and I can't start MySQL I am on Windows 8, my SSD got corrupted and I installed Windows on a new drive. I had before MySQL 5.6.12, now I installed 5.6.17 and tried to move the entire data dir. The location of the MySQL installation is the same as before. But when I start it I get the following error:

2015-02-23 17:44:44 12492 [Note] Plugin 'FEDERATED' is disabled.
wampmysqld64: Incorrect information in file: '.\mysql\plugin.frm'
2015-02-23 17:44:44 12492 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-02-23 17:44:44 12492 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-02-23 17:44:44 12492 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-23 17:44:44 12492 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2015-02-23 17:44:44 12492 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-02-23 17:44:44 12492 [Note] InnoDB: Not using CPU crc32 instructions
2015-02-23 17:44:44 12492 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-02-23 17:44:45 12492 [Note] InnoDB: Completed initialization of buffer pool
2015-02-23 17:44:45 12492 [Note] InnoDB: Restoring page 0 of tablespace 0
2015-02-23 17:44:45 12492 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
2015-02-23 17:44:45 12492 [ERROR] InnoDB: space header page consists of zero bytes in data file .\ibdata1
2015-02-23 17:44:45 12492 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-02-23 17:44:45 12492 [ERROR] Plugin 'InnoDB' init function returned error.
2015-02-23 17:44:45 12492 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-02-23 17:44:45 12492 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-02-23 17:44:45 12492 [ERROR] Aborting

mysql_upgrade fails with FATAL ERROR: Upgrade failed

Thank you very much for your time.

Before doing any attempts for recovery make sure to have backup of data files.

" tried to move the entire data dir."

  make sure my.ini has correct paths.

Look at error that points at innodb_data_file_path.

  Do confirm your ibdata file(s) are at right location.


What is meant by "nonclustered legacy instance of SQL Server"? When I research nonclustered legacy instance in SQL Server, all I get is some results about indexes. This is obviously not related to or affiliated with indexes, so what is meant by "clustered instance of SQL Server" and nonclustered ones?

Context: SQL Server 2014 Upgrade Technical Guide (pdf download)

Example extract:

non-clustered just means it's not part of a Failover Cluster Instance ("FCI" -- which is fundamentally different from a Windows Server Failover Cluster ("WSFC")). An FCI requires a WSFC, but the opposite is not true. A non-FCI instance of SQL Server is typically called a standalone instance. A standalone instance can be installed on a WSFC (and take part, or not, in Availability Groups). You can see how they work together here.

legacy is not a special word here, it is just an adjective that means older. There is no more meaning to "legacy instance" than there is to "ancient instance" or "deprecated instance" or "no longer supported instance." In this context 2005, 2008, and 2008 R2 are mentioned explicitly, but 2012 would be in that bucket as well.

The doc portion you reference just means you can't convert a standalone instance of SQL Server to a clustered instance as a part of an upgrade.


Data Warehouse - DB2 to SQL Server: how & what impacts on ETL? I'm working towards migrating an IBM DB2 database to SQL Server, however the DB2 database sits in a Data Warehouse environment.

As I am very new to Data Warehousing, DB2 and DB Migration between different types of databases, I would like to ask about how to best approach this and understand what components are involved (listing or linking elsewhere is fine - happy to read a bunch of things or learn something new).

The context:

The ETL tool in use, and to be kept in use, is Informatica
The only application that queries the DB is Cognos
There are 4 DB2 DBs in use on 2 (non-windows) hosts, one of these is 'H1' which has a 'DB2-DW-PROD' DB used as part of the Data Warehouse
I am interested in migrating the 'DB2-DW-PROD' DB to some space I have on an existing (Windows) SQL Server host, 'H2' under the name 'SQL-DW-PROD'. This host currently has several DBs on it for other purposes (in one instance) and can have SSRS/SSIS or other services/resources installed or configured if needed.
I would like to clean up the poorly maintained DB a little bit during the migration, if possible, based off what has not been used/updated in the past couple of years.

What I would like to know, directly or indirectly:

Should I create a new instance on H2 or is a single new database enough?
Does the new instance/DB need particular resources? If so, where from?
Should I use a particular Microsoft tool or use some type of import/export to get the data I need? (Is Informatica involved in this process?)
Someone mentioned 'metadata tables' for Informatica and/or Cognos, should I be wary of something?
Another mentioned maintaining sequence values -How is this best achieved; is it needed?
Any changes on the Informatica side are not my personal concern but the work of another, as such, Is there anything I should provide this person (apart from the address of the new db)?

I'm very new in this field, so anything dumbed down is greatly appreciated. That being said, anything complicated but necessary or good to know is as well. It's a steep learning curve for me but I'm kind of stumped right now and need a hand.

I have no control over the process - Informatica and Cognos must be kept as they are. Also, at this stage, I am not able to view the DB2 DB but I know it is not too large. At the very, very most, assume it's 1 TB for the purpose of this question.

At the moment I am simply trying to understand the process I will follow and what I will need to look out for when the time comes.

EDIT: Have received plenty of feedback on the instance vs database part of the question which I greatly appreciate. However, I am still confused as to how I should approach migrating the data accross. I.e. 'how, or what should I use, to migrate such that I have everything I need to support Informatica and cognos?'

The most pressing issue is that I don't understand any of informatica's/cognos's dependencies or know how db2 works. I am not confident that simply copying user table data accross is enough and would really like some confirmation or pointers to know exactly what to do.

One thing is hardware resources. The current server will use a certain amount of memory, CPU and IO to do its job. For a 1TB database this is likely to be non-trivial. If you move that work to a new server it is still going to need (more-or-less) the same amount of memory, CPU and IOPS. If the new server is already hosting applications and serving their needs it may not have capacity to also accommodate the new needs of the migrated application. Result: everything runs slowly; sadness abounds.

DW work tends to use big sequential reads. OLTP work usually has small random IO. If you mix these two on one hardware box it could prove tricky to configure the storage (SAN array?) for best performance for either, likely resulting in mediocre performance for both.

Each instance of SQL Server has a fixed overhead, such as tempdb and minimum memory. The more instances there are on one box the more overhead has to be paid for. Also you'll likely have to over allocate resources to each instance to accommodate peak load, again costing more. With everything in a single instance this cost can be spread. The flip side is that it can be more work to isolate normal work from a greedy task on the same instance, but not impossible. Making this decision would be a consultancy engagement, not a Q&A post.


Problem with union casting integer to ceiling(decimal) I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.

The problem is that this query is generated by an external library, so I don't have control over this code, at this level at least.
Do you have some idea how to fix this?

SELECT 20 AS x
  UNION SELECT null
  UNION SELECT 2.2;
+------+
| x    |
+------+
|  9.9 | -- why from 20 to 9.9
| NULL |
|  2.2 |
+------+

Expected Result

+------+
| x    |
+------+
|   20 | -- or 20.0, doesn't matter really in my case
| NULL |
|  2.2 |
+------+

Adding more context, I'm using Entity Framework 6 with an extension library http://entityframework-extensions.net/ to save changes in batches, specifically the method context.BulkSaveChanges();, this library creates queries using "select union".

Looks like a bug to me and I can confirm this puzzling behaviour in:

10.2.14-MariaDB

If possible you can cast the integer value to a double:

SELECT cast(20 as double) UNION SELECT null UNION SELECT 2.2;

or make sure you have the double value first:

SELECT 2.2 UNION SELECT null UNION SELECT 22;

Further observations after reading the comments in @Evan Carroll's answer

select 20 union select null union select 2;
+------+
| 20   |
+------+
|   20 |
| NULL |
|    2 |
+------+

Ok, using int values does not seem to produce the error.

select 20 union select null union select 9.0;
+------+
| 20   |
+------+
| 9.9  |
| NULL |
| 9.0  |
+------+

ERROR: Seems like output is decimal(2,1)

create table tmp as select * from (select 20 as x
                                   union
                                   select null
                                   union
                                   select 9.0) as t

describe tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | decimal(2,1) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

The error is not isolated to the command line interface, it exists for python2-mysql-1.3.12-1.fc27.x86_64 as well:

>>> import MySQLdb
>>> db = MySQLdb.connect(host="localhost", user="*****", passwd="*****", db="test")
>>> cur = db.cursor()
>>> cur.execute("SELECT 20 union select null union select 2.2")
3L
>>> for row in cur.fetchall() :
...     print row
...
(Decimal('9.9'),)
(None,)
(Decimal('2.2'),)

Oddly enough the error disappears if null is moved first or last:

select null union select 20 union select 9.0;
select 20 union select 9.0 union select null;

+------+
| NULL |
+------+
| NULL |
| 20.0 |
| 9.0  |
+------+

If null is placed first, resulting type is decimal(20,1). If null is placed last resulting type is decimal(3,1)

The error also disappears if another leg is added to the union:

select 20 union select 6 union select null union select 9.0;
+------+
| 20   |
+------+
| 20.0 |
| 6.0  |
| NULL |
| 9.0  |
+------+

resulting type decimal(20,1)

adding another null in the middle preserves the error:

select 20 union select null union select null union select 9.0;
+------+
| 20   |
+------+
| 9.9  |
| NULL |
| 9.0  |
+------+

But adding a null at the beginning fixes it:

select null union select 20 union select null union select null union select 9.0;
+------+
| NULL |
+------+
| NULL |
| 20.0 |
| 9.0  |
+------+

As expected casting first value to decimal(3,1) works.

Finally, explicitly casting to decimal(2,1) produces the same error but with a warning:

select cast(20 as decimal(2,1));
+--------------------------+
| cast(20 as decimal(2,1)) |
+--------------------------+
| 9.9                      |
+--------------------------+
1 row in set, 1 warning (0.00 sec)


Best order of execution of mysql maintenance tools After a change of data type or index for exemple. Given some queries to do maintenance tasks. What's the best order of doing these tasks?
ANALYSE TABLE foo;
CHECK TABLE foo;
OPTIMIZE TABLE foo;
REPAIR TABLE foo;
ALTER TABLE foo Engine=X; --defragment task

Does order matter? Like after an OPTIMIZE (which arrange disk space) which could eventually lead to corruption, it's better to do a CHECK after?
Does one task affect another and does one task is a repeatation of another? Like I have the feeling that OPTIMIZE is the same as defragment and CHECK is the same as REPAIR.

It depends on what storage engine you use. The default storage engine has been InnoDB since MySQL 5.5 (circa 2010), so I will assume that's what you use, since you did not specify.

OPTIMIZE TABLE <name> does the same as ALTER TABLE <name> FORCE or ALTER TABLE <name> ENGINE=InnoDB. This should be done with care, because it rebuilds the whole table, and that locks the table and takes a long time, proportional to the size of the table and number of indexes. Because it copies data pages to a new table, and rebuilds indexes, this accomplishes defragmentation. It also accomplishes the same as ANALYZE TABLE <name>. I almost never run OPTIMIZE TABLE. I prefer to use pt-online-schema-change to defragment InnoDB tables, because that does its job without blocking clients from reading or writing the table.

ANALYZE TABLE <name> accomplishes a resampling of pages to update table and index statistics. You can do this anytime. It is quick regardless of the size of the table, and it does not lock the table. At my workplace, I developed a scheduled task to run ANALYZE TABLE on all the tables about every ten days (it runs every day, and analyzes 1 out of 10 tables each time).

CHECK TABLE <name> validates the pages of the table for corruption. If a corrupt table or index is detected, this may halt the MySQL Server. Refer to documentation for details. FWIW, I don't think I've ever run CHECK TABLE on an InnoDB table.

REPAIR TABLE <name> does nothing for InnoDB. It works on MyISAM, ARCHIVE, and CSV tables. I have definitely never run this on any InnoDB table.

I can't think of any reason you would run all of these statements on the same table during the same session, regardless of the order you run them in.


MySQL Workbench: "Operation has completed with warnings" As I synchronize my database, MySQL Workbench reports:

  "Operation has completed with warning. Please see logs for details."

But the Message Log just says:

Fetching schema list.
OK
WARNING: Server configuration check

I don't know what this is supposed to mean. Is the Workbench warning me about some dubious relations I implemented in my database or is something wrong with my server? What does it want from me?

Btw: The generated script runs without errors. The database gets generated and migrated to the server.

The message is a bit unconcrete, so here's a translation: it appears when the statement 'SELECT @@lower_case_table_names' returns -1 or 0 (only for Win and OS X). So check, if case sensitivity is an issue for you.

Update:

The message you see is in fact only the title for a (potential) error dialog. The actual message is not listed in that small log. Look in the WB log (Help -> Show Log File) for the details.


Cache all mysql table Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached... But could i send all my table data to cache via some query, to improve speed, so that simple my table will be in ram memory?
I read a lot about encreasing cahce size etc... But how can i send this all data in cache not when user do some query (if so user see data from cache)...

*************************** 1. row ***************************
       Table: ART_LOOKUP
Create Table: CREATE TABLE `ART_LOOKUP` (
  `ARL_ART_ID` int(11) NOT NULL,
  `ARL_SEARCH_NUMBER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_KIND` int(3) DEFAULT NULL,
  `ARL_BRA_ID` int(11) DEFAULT NULL,
  `ARL_DISPLAY_NR` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_DISPLAY` int(11) DEFAULT NULL,
  `ARL_BLOCK` int(11) DEFAULT NULL,
  `ARL_SORT` int(11) DEFAULT NULL,
  KEY `ARL_ART_ID` (`ARL_ART_ID`),
  FULLTEXT KEY `ARL_SEARCH_NUMBER` (`ARL_SEARCH_NUMBER`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: ARTICLES
Create Table: CREATE TABLE `ARTICLES` (
  `ART_ID` int(11) NOT NULL,
  `ART_ARTICLE_NR` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `ART_SUP_ID` int(11) DEFAULT NULL,
  `ART_DES_ID` int(11) DEFAULT NULL,
  `ART_COMPLETE_DES_ID` int(11) DEFAULT NULL,
  `ART_CTM` blob,
  `ART_PACK_SELFSERVICE` int(11) DEFAULT NULL,
  `ART_MATERIAL_MARK` int(11) DEFAULT NULL,
  `ART_REPLACEMENT` int(11) DEFAULT NULL,
  `ART_ACCESSORY` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE1` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE2` int(11) DEFAULT NULL,
  `Quantity` int(5) DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Waittime` int(3) NOT NULL,
  `datetime_of_update` datetime DEFAULT NULL,
  KEY `ART_ID` (`ART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

via privacy can't show all

Certain database engines do support pinning objects to cache, Oracle and MS-SQL to name a couple. MySQL does not have this option, as far as I know.

On the other hand I cannot think of many use cases for this option, as modern database systems are very good at managing cache. MySQL offers block caching with an LRU algorithm through the InnoDB storage engine.

There is also an in memory storage engine, but there is no easy and efficient way replicate changes back to disk.

There are complete in-memory databases out there if you want everything in RAM, including MySQL Cluster.

On a sidenote, in practical use there is a trade-off between I/O and CPU resources. More cache means less I/O but more CPU is consumed, and there is a real possibility of introducing new bottlenecks.


Can I limit an index to only contain the newest relevant row? I have a table in PostgreSQL 9.2 that records login sessions called user_login_session. This table contains a user_id, start_timestamp and end_timestamp columns, amongst others. This table has started growing as my system gains traction, and I can't purge old records for now.

I've defined an index on this table but I'm concerned about its growth over time. Is it possible to define an index which only keeps a handle to the newest login session row per user ID? Older rows are not accessed operationally.

This is a classic case for partitioning.

When you insert a new record, have a trigger delete the old record from the user_login_session_latest sub-table and insert it into the user_login_session_history sub-table. Have both of them inherit from an empty top-level user_login_session table so you can still query the two transparently. You will need an additional trigger-maintained column like is_latest to partition on, since you can't do constraint exclusion on a condition that refers to other rows.

A partial index will work, but not as well as you might hope; it's hard to write a partial index based on the concept of "latest" (you need an app to set a flag), and it might not boost things as much as you expect. The partial index points at a page, but if that page contains 10 old records and one new record you might not gain that much; you'd need to regularly CLUSTER the table to see much benefit. Partitioning will be a much better option.

Alternately, you can maintain a materialized view of the "latest" users in another table using triggers or application logic. I'd generally prefer to use partitioning in preference to a materialized view for this.

Remember to tune autovacuum to vacuum your tables quite frequently if there's lots of updating/deleting going on.


Column store index on a large fact table I have seen column store index and also tested it. I have no doubt admiring that it is lightning fast. But every tutorial and blogs I have considered, says about its limitation "table will become read only" and I won't be able to insert/update/delete data in a table. And at the same time it is recommended to use them in data warehousing projects where data is in huge amount and updated periodically. On based of that I have come up with the solution to

Disable index, do insert-update-delete and then rebuild the index

Will this be a right approach to deal with a large amount of data in fact table?

Will the cost (in terms of time) in creating/rebuilding index can be more than regular process?
Generally, data in fact table is queried until the cube is processed. I have a cube which works on views instead of table; so will it be cost efficient for me to keep a column store index on fact table?
If I have a fact table which gets updated every day. So, will it be a good practice to disable or rebuild indexes?
Do they behave similar to index in SQL Server? I mean, Select will be fast but update operation will take more time.

move to SQL Server 2014 that offers updateable clustered columnstore index.
if stuck to 2012, use partition switch to update the nonclustered columnstore index.


MySQL Editor Hangs on attempt to Launch I have just downloaded the latest Community Installer (5.5.27) along with the MySQL WorkBench (5.2.41). I clicked OK on all default settings on setup.

When I try to "Open a connection to start querying" on the WorkBench home page and chose "Local Instance MySql 55", I keep getting stuck on a never-ending "Opening SQL Editor" screen ("An SQL Editor instance for "Local Instance MySql 55" is opening and should be available in a moment")

Any idea on what could be wrong ?

EDIT :
I am able to access it from command line and query it successfully. But still not from query editor.

Thanks Mika, I tried your solution in workbench (5.2.41). It worked after I cleared the passwords in the vault (click Manage Connections) and restarted the WorkBench.

To clear passwords stored in the vault, I did the following:

Open MySQL WorkBench the you will see the home page
Click Manage Connection at bottom left corner.
In Manage DB Connections, click Clear button besides Store in Vault...
Close it and restart WorkBench.
Double click Local instance mysql in home page, now you db will popup.


Get the total count of every child foreign keys I have been trying this in SQL EXPRESS 2016. Here is my problem:

I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table.

Table 1

CREATE TABLE [dbo].[Equipament]
    [ID] [nvarchar](50) NOT NULL,
    [AL] [nvarchar](50) NULL,
    [Lvl] [nvarchar](50) NULL,
    [IDParent] [nvarchar](50) NULL,

Table 2

CREATE TABLE [dbo].[Conns]
    [Conns] [nvarchar](50) NOT NULL,
    [IDEquip] [nvarchar](50) NOT NULL,
FOREIGN KEY([IDEquip])
REFERENCES [dbo].[Equipament] ([ID])

What I'm trying to get is a CTE that in a recursive way gets:

The total count of connections of each child (say its lvl 3 for
example)
For each lvl2 equipement the total sum of the of its lvl3' child
conections
For each lvl1 equipment the total sum of its childs

My attempts have failed mainly because there are no level 1 and 2 equipments in the Conns table, and cant have a LEFT JOIN in the recursive CTE.

The closest I got was to this:

;WITH QUERY AS
(
    SELECT E.ID,E.IDParent,L.conns
    FROM Equipament E
    LEFT JOIN conns L ON E.ID=L.IDEquip
    WHERE E.IDParent IS NULL

    UNION ALL

    SELECT E.ID,E.IDParent,L.conns
    FROM Equipament E
    JOIN conns L ON E.ID = L.IDEquip
    JOIN QUERY P on P.ID = E.IDParent
)
SELECT
    E.ID,
    SUM(S.Total) AS LTotal
FROM Equipament E
LEFT JOIN ( SELECT Q.ID, COUNT(Q.conns) AS Total
                FROM QUERY Q
                GROUP BY Q.ID
            ) as S
ON E.ID = S.ID
GROUP BY E.ID
ORDER BY E.ID
option (maxrecursion 0)

EDIT The fiddles requested:
dbfiddle link

With the example data the expected output should be something like:

+---+-------------------------------------+
|ID |   Total Conections Dependancy       |
+---+-------------------------------------+
|AA |   1 (CE + BA)                       |
|AB |   4 ( BB + BC)                      |
|BA |   0                                 |
|BB |   0                                 |
|BC |   4 ( CD + CB)                      |
|CA |   0                                 |
|CB |   3                                 |
|CD |   1                                 |
|CE |   1                                 |
+---+-------------------------------------+

Only the C level equipemnts have connections, and there might be a direct jump from level A to level C.

Is this possible to do? Can someone point me in the right direction?

Thank you,

Yes, it's possible to do it using either a recursive CTE or a recursive function (at first I couldn't figure out how to solve it using CTE, so I coded the solution using the function and, since it might be useful to someone else, I'm keeping it on the answer as well). Here's how you can do it:

Recursive CTE

;WITH QUERY AS
(
    SELECT IDEquip, 1 AS Quantity
    FROM Conns

    UNION ALL

    SELECT E.IDParent AS IDEquip, 1 AS Quantity
    FROM Equipament E
        INNER JOIN QUERY Q ON E.ID = Q.IDEquip
)
SELECT E.ID, ISNULL(SUM(Q.Quantity), 0) AS Total
FROM Equipament E
    LEFT JOIN QUERY Q ON E.ID = Q.IDEquip
GROUP BY E.ID;

The trick to solve it using the recursive CTE was to query the values from back to front.

Recursive Function

CREATE FUNCTION dbo.GetAllDescendants(@id nvarchar(100))
RETURNS INT
AS
BEGIN
    RETURN(
        SELECT SUM(Total + ISNULL(dbo.GetAllDescendants(ID), 0))
        FROM (
            SELECT ID, IDParent, COUNT(IDEquip) AS Total
            FROM Equipament
                LEFT JOIN Conns ON ID = IDEquip
            GROUP BY ID, IDParent
        ) AS TotalConns
        WHERE IDParent = @id
    )
END;

With this query you can get the total of descendants, but not the connections for the ID itself:

SELECT ID, ISNULL(dbo.GetAllDescendants(ID), 0) AS [Total Conections Dependancy]
FROM Equipament
ORDER BY ID;

To get the exact result you described you should run it like this:

SELECT ID, IIF(COUNT(IDEquip) > 0, COUNT(IDEquip), ISNULL(dbo.GetAllDescendants(ID), 0)) AS [Total Conections Dependancy]
FROM Equipament
    LEFT JOIN Conns ON ID = IDEquip
GROUP BY ID, IDParent
ORDER BY ID;


Sudden periods of performance degradation For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version 1.1.8) will shoot up to over 70% and one group of queries will start becoming very slow. This will last for 4-5 minutes and then disappear, only to come back the next night with a new set of queries becoming slow. The following stats were captured during a recent occurrence of this happening:

Com_delete = 5.86666666666667 / sec
Com_create_table = 0 / sec
Threads_created = 1.61666666666667 / sec
Key_read_requests = 78.7666666666667 / sec
Bytes_received = 4766588.83333333 / sec
Qcache_not_cached = 0 / sec
Created_tmp_disk_tables = 224.45 / sec
Com_replace = 0 / sec
Innodb_buffer_pool_write_requests = 1537.2 / sec
Created_tmp_tables = 590.916666666667 / sec
Com_update = 82.0666666666667 / sec
Com_stmt_execute = 1053.75 / sec
Key_writes = 0 / sec
Innodb_row_lock_time = 0 / sec
Com_select = 38798.35 / sec
Connections = 3.55 / sec
Select_range = 1492.38333333333 / sec
Innodb_data_read = 24389768.5333333 / sec
Com_insert = 61.8666666666667 / sec
Table_locks_waited = 0 / sec
Bytes_sent = 66554017.7666667 / sec
Key_reads = 0 / sec
Sort_rows = 25265.05 / sec
Select_full_join = 0 / sec
Select_scan = 182.583333333333 / sec
Slow_queries = 1.06666666666667 / sec
Innodb_data_written = 3407197.86666667 / sec
Key_write_requests = 17.05 / sec
Innodb_buffer_pool_read_requests = 3687732.7 / sec
Questions = 41779.7166666667 / sec
Qcache_hits = 0 / sec
Threads_connected =
Threads_running =
Innodb_row_lock_time_avg =
Qcache_queries_in_cache =
Qcache_free_memory =

We can compare this to right now:

Com_delete = 3.88333333333333 / sec
Com_create_table = 0 / sec
Threads_created = 0 / sec
Key_read_requests = 1 / sec
Bytes_received = 3421724.13333333 / sec
Qcache_not_cached = 0 / sec
Created_tmp_disk_tables = 159.45 / sec
Com_replace = 0 / sec
Innodb_buffer_pool_write_requests = 989.65 / sec
Created_tmp_tables = 428.3 / sec
Com_update = 50.4166666666667 / sec
Com_stmt_execute = 728.4 / sec
Key_writes = 0 / sec
Innodb_row_lock_time = 0 / sec
Com_select = 28194.15 / sec
Connections = 0.866666666666667 / sec
Select_range = 1107.58333333333 / sec
Innodb_data_read = 19804706.1333333 / sec
Com_insert = 38.95 / sec
Table_locks_waited = 0 / sec
Bytes_sent = 48218433.8666667 / sec
Key_reads = 0 / sec
Sort_rows = 46135.2833333333 / sec
Select_full_join = 0 / sec
Select_scan = 125.633333333333 / sec
Slow_queries = 0.533333333333333 / sec
Innodb_data_written = 3794304 / sec
Key_write_requests = 0.6 / sec
Innodb_buffer_pool_read_requests = 2814013.88333333 / sec
Questions = 30203.2833333333 / sec
Qcache_hits = 0 / sec
Threads_connected =
Threads_running =
Innodb_row_lock_time_avg =
Qcache_queries_in_cache =
Qcache_free_memory =

Aside from the fact that the stats all generally seem higher, could there be some clue here as to what's going on? Besides running this stats script, are there are any other tools I can use to monitor what might be causing this instability?

During that period can you get a list of the sql processes running?

show full processlist;

Is this a server where you don't have root access to the actual OS like in AWS?

If you do have root access and this is something you can research it might be unrelated to MySQL, i.e., some other process on the server is affecting performance. This might be out of scope of the question but use iostat, top, free for utilities to use in that regard.

For MySQL specific strategies you can also enable the query log which will log every query. Careful with this because it'll fill up disk very quickly depending on query traffic. Also requires a restart. If you can't do that then you probably want to watch traffic on the wire:

tcpdump -i any -s0 -A -n -nn port 3306

or

tcpdump -i any -s0 -A -n -nn port 3306 -w /tmp/mysql.pcap

to output to a file and then view with a UI tool like Wireshark. Then you can see the actual queries running.


Sql Server Agent REPL Jobs I know that replication uses Sql Agent jobs to pull subscriptions. Are these jobs supposed to stay in an 'executing' state? (Sql 2008 SP1)

Replication agents run as jobs scheduled under the SQL Server Agent.  They can be scheduled to run continuously or on a recurring schedule.

If the replication has been scheduled to run continuously, then yes, it could be continuously 'executing'.


Datagrip EXPLAIN ANALYZE Gui? In Datagrip (using 2018.2.4), you can right-click on a query and choose 'Explain' which gives nicely formatted output. Is there anyway to get this nicely formatted output using instead 'Explain Analyze'?

Unfortunately, not yet, Bob =( https://youtrack.jetbrains.com/issue/DBE-5373


PostgreSQL: Can i use tuple in where clause? Actually might be a simple question:
Given 2 tables:
table1: A,B,C
(A1,B1,28471),
(A1,B2,01244),
(A2,B1,1283a),
(A2,B2,82r7e);

table2: A,B,D,E,F,G
(A1,B1,18,1,6,8),
(A2,B2,18,2,3,0),
(A3,B1,18,7,1,4),
(A4,B2,18,1,9,6);

Will the following statement result in a the following result given the example tables:
SELECT E,F,G FROM table2 WHERE (A,B) IN (SELECT A,B FROM table1)

expected result:
 E F G
(1,6,8), -- (A1,B1)
(2,3,0); -- (A2,B2)

Can someone confirm this is a valid method to select based on tuples?
asking as the most likely next step is to UPDATE the E field by adding +5 for matching results

Thanks to Peter Vandivier (couldnt flag his comment as the answer)
Yes it is possible:

What have you tried – Peter Vandivier 12 mins ago


Single slave - multiple master MySQL replication I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done? is there a way to define multiple master hosts?

As of October 2013 MySQL (> 5.7.6) supports multi-source replication.

MySQL Multi-Source Replication in MySQL Reference Manual

http://www.mysqlperformanceblog.com/2013/10/02/mysql-5-7-multi-source-replication/

http://on-mysql-replication.blogspot.com/2013/09/feature-preview-mysql-multi-source-replication.html