Same Join Constraints on Multiple Columns We have a situation that I have been able to recreate with the following simple example.  I have the following two sample tables:
CREATE TABLE contact_info
(
  id INT UNSIGNED AUTO_INCREMENT,
  priContactId INT,
  secContactId INT,
  blahBlah VARCHAR(32),

  PRIMARY KEY(id)
);

and
CREATE TABLE name_lookup
(
  id INT UNSIGNED AUTO_INCREMENT,
  contactID INT,
  contactName VARCHAR(32),

  PRIMARY KEY(id)
);

I populate them as follows:
INSERT INTO contact_info(priContactId, secContactId, blahBlah) VALUES(1, 3, "Team A"), (4, 2, "Team B");
INSERT INTO name_lookup(contactID, contactName) VALUES(1, "John Doe"), (2, "Mary Smith"), (3, "Jose Garcia"), (4, "Larry Brown");

Obviously, the contents of the tables are as follows:
+----+--------------+--------------+----------+
| id | priContactId | secContactId | blahBlah |
+----+--------------+--------------+----------+
|  1 |            1 |            3 | Team A   |
|  2 |            4 |            2 | Team B   |
+----+--------------+--------------+----------+

+----+-----------+-------------+
| id | contactID | contactName |
+----+-----------+-------------+
|  1 |         1 | John Doe    |
|  2 |         2 | Mary Smith  |
|  3 |         3 | Jose Garcia |
|  4 |         4 | Larry Brown |
+----+-----------+-------------+

We would like to perform a JOIN operation so that we get output like this:
+-------------+-------------+--------+
| John Doe    | Jose Garcia | Team A |
+-------------+-------------+--------+
| Larry Brown | Mary Smith  | Team B |
+-------------+-------------+--------+

The join constraints for both the priContactId and secContactId columns are the same and I am having tough time figuring out what the JOIN query should look like.
FYI, we are using MySQL version 5.6.49.

This is an interesting example of where an (indirect) SELF-JOINs prove useful!
In order to answer your problem, I did the following (all the SQL below is available on the fiddle here):
I used your DDL and DML as supplied in the question - thanks for that (and a +1 - you've only asked two questions, so I'd consider you a New Contributor and it's great to see that some people take the trouble to supply DDL and DML - if only all OP's did the same!).
CREATE TABLE name_lookup
(
  id INT UNSIGNED AUTO_INCREMENT,
  contact_id INT,
  contact_name VARCHAR(32),

  PRIMARY KEY(id)
);

CREATE TABLE contact_info
(
  id INT UNSIGNED AUTO_INCREMENT,
  pri_contact_id INT,
  sec_contact_id INT,
  blah_blah VARCHAR(32),

  PRIMARY KEY(id)
);

Populate them:
INSERT INTO name_lookup(contact_id, contact_name)
VALUES
(1, "John Doe"), (2, "Mary Smith"),
(3, "Jose Garcia"), (4, "Larry Brown");

and
INSERT INTO contact_info(pri_contact_id, sec_contact_id, blah_blah)
VALUES(1, 3, "Team A"), (4, 2, "Team B"), (1, NULL, "Team A");

Notice the final record which has sec_contact_id = NULL - see the discussion in the EDIT below. I'm implicitly assuming that the team identity is determined by the pri_contact_id - define as necessary.
You'll also notice that I use snake_case or lower_case_with_underscores - personal preference - pick a style and stick to it!.
I've left extra fields in the SQL in the fiddle so that you can see the thought processes involved and how I arrived at a solution!
SELECT
  nl1.id, nl1.contact_id, nl1.contact_name,
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah
FROM name_lookup nl1
JOIN contact_info ci1
  ON nl1.contact_id = ci1.pri_contact_id;

Result:
id  contact_id  contact_name    pri_contact_id  sec_contact_id  blah_blah
 1           1      John Doe                 1               3     Team A
 4           4   Larry Brown                 4               2     Team B

So, now we join name_lookup back on itself using contact_info as the link between them.
SELECT
  nl1.id, nl1.contact_id, nl1.contact_name,
  nl2.id, nl2.contact_id, nl2.contact_name,
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah
FROM name_lookup nl1
JOIN contact_info ci1
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;

Result:
id  contact_id  contact_name    id  contact_id  contact_name    pri_contact_id  sec_contact_id  blah_blah
 1           1      John Doe     3           3   Jose Garcia                 1   3    Team A
 4           4   Larry Brown     2           2    Mary Smith                 4 2      Team B

So, having obtained our result, we can now clean up the SQL (only SELECT required fields - reduce any network traffic as well as I/O on the server) as follows:
SELECT
  nl1.contact_name AS "Con_1 name",
  nl2.contact_name AS "Con_2 name",
  ci1.blah_blah AS "Team"
FROM name_lookup nl1
JOIN contact_info ci1
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;

Result:
Con_1 name     Con_2 name     Team
  John Doe     Jose Garcia  Team A
Larry Brown    Mary Smith   Team B

Et voilà - result as desired!
EDIT (NULLs in sec_contact_id):
It was pointed out to me that my answer wasn't as comprehensive as I might have liked it to be. What if sec_contact_id is NULL which, after all, is possible - you might have made the first one but a follow-up hasn't been completed yet?
So, I changed the table slightly and it now contains (as you'll see above - a considerably changed fiddle is available here - I wanted to run it on PostgreSQL also):
 Con_1 name   Con_2 name      Team
   John Doe  Jose Garcia    Team A
   John Doe         NULL    Team B
Larry Brown   Mary Smith    Team B

so, now, you have to use INNER JOINs thus:
  nl1.contact_name AS "Con_1 name",
  nl2.contact_name AS "Con_2 name",
  ci1.blah_blah AS "Team"
FROM contact_info ci1
    LEFT JOIN name_lookup nl1
        ON nl1.contact_id = ci1.pri_contact_id
    LEFT JOIN name_lookup nl2
        ON nl2.contact_id = ci1.sec_contact_id
ORDER BY nl1.id, ci1.blah_blah;

Result:
  Con_1 name      Con_2 name      Team
    John Doe     Jose Garcia    Team A
    John Doe            NULL    Team B
 Larry Brown      Mary Smith    Team B

So, now the record with the sec_contact_id = NULL datum appears in your resultset.
A couple of words of advice:

You really should consider (strongly) consider upgrading from 5.6 to the current version of MySQL 8 - you'll get window functions, generated columns, CHECK constraints - it's at version 22 now, and I haven't heard many complaints, so it'd be a good option!

Many people consider NULLs to be undesirable and avoid them like the plague - I tend to fall into this category. Therefore, you might wish to consider having two contact tables - pri_contact_info and sec_contact_info. You can decide if you wish to do that and ask a new question - if you do so, let me know here also!

You might consider answering this question (a comment below your original post): Can there ever be more than two people on a team? I e. There would be 3 columns with team members names for the same row?!

the PRIMARY KEY of the name_lookup table should be contact_id - the surrogate key id adds nothing to the mix! I assume that contact_id is assigned more or less randomly to employees as they are hired? So, it's essentially a surrogate key itself. Surrogate keys have their place, but sometimes they're not the way to go!

MySQL is by far and away the worst of the major RDBMS's when it comes to standards support, plus it has a plethora of non-standard "extensions" - in future, maybe you could delimit your SQL strings with apostrophes ' and not double quotes "? My personal suggestion is that you use double quotes for aliases of field names for the final result (presentation, not content) as I have done - it will make your SQL more readable and portable!


mysql_insert_id() on galera mysql cluster I have percona mysql galera cluster. It inherited a MyISAM database.
In order for the galera to work it had to alter to innodb. Replication is working so far so good. However I have a php application which calls for mysql_isert_id() after every insert.

In a few words :
1. A big table with many inserts flying in.
2. On every insert (in the old MyISAM db) they lock the table, do the insert query, call mysql_insert_id(), and after that unlock table.

for the replication (innodb), lock table doesn't count anymore. My question is how, can we be sure about the id that mysql_insert_id() returns when there are hundreds of ofther inserts comming in a matter of seconds?

last_insert_id() and mysql_insert_id() return the same value, but as far as I understand  (last_insert) reserves ID before the actual insert(?), and mysql_insert_id do the insert and after that calls for an ID ?

I'm lost in the documentation....

The "insert_id" is local to the connection.  No locking is needed to get the value.  This applies in all flavors of multi-processing, clustering, engines, etc.

Still, you may need "transactions" for other purposes.  You have not described the code enough to say whether you need to exactly replace LOCK TABLES (etc) with BEGIN..COMMIT.  Or whether some variation is needed.

One thing to learn is the following pattern:

BEGIN;
SELECT ... FOR UPDATE;  -- rows you need to look at before changing
do stuff
UPDATE those rows
COMMIT;

The "do stuff" could include a ROLLBACK and exit from the flow.

AUTO_INCREMENT:

The INSERT reserves the id before the operation.  In some cases, it will reserve an id but then not use it.  Example: INSERT IGNORE when it hits a dup key.

Galera (and any other Multi-Master setup) skips ids.  That is, one node will assign ids 1,4,7,10,...; another node: 2,5,8,...; the third: 3,6,9,...  Depending on the flow of INSERTs, you may see most of the numbers used, or you may see lots of gaps.  Also you may "see" id=124 "before" id=123 shows up.

More tips on moving to Galera and InnoDB


How to specify the system database location when installing SQL Server? It seems that we can't specify a location for the system databases when installing SQL Server. I want to know whether this is true. Take a look at the image I attached.

Thanks!

you can select the root directory path here


Numeric Latitude & Longitude values are rounded up when querying SQL Server Database Developing an application that is using Google Maps. Latitude and longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the nearest 100th, thus making the place marker on google maps inaccurate.

Any suggestions on how to always return the true value of the number ?

The solution to this issue was due to the fact that the way the code was being pulled from the database. In coldfusion if you are going to query the numeric value from the database, you must use a sql query param and specify the return type of the data.


Why is vacuum at a critical level in PostgreSQL when I'm running autovacuum? I just checked the state of vacuum in my PostgreSQL database and it's at a critical level:

$ check_postgres --action=last_vacuum
POSTGRES_LAST_VACUUM CRITICAL: DB "postgres" DB: postgres TABLE: pg_catalog.pg_shdepend: 09:22 August 12, 2013 (40 weeks 2 days 4 hours 9 minutes) | time=0.52s postgres.pg_catalog.pg_shdepend=24379780s;86400;172800

but I am running autovacuum. Why would this happen?

check_postgres isn't very sophisticated software. It just checks if a particular table was vacuumed in the last 2 days (or as configured). For tables with no updates or deletes it is perfectly normal to not be vacuumed for extended periods of time. You can either ignore this or configure check_postgres to ignore this particular table.

I don't think this functionality of check_postgres is of much use. Maybe for some particular tables. Definitely not so much for system tables like pg_catalog.pg_shdepend.


supplementary characters in FreeTDS I have a C++ application that connects to SQL Server 2008 through FreeTDS unixODBC on an RHEL box.

However, i am unable to insert supplementary characters to the database through FreeTDS unixODBC.

[FreeTDS][SQL Server]Error converting characters into server's character set. Some character(s) could not be converted

This is the query that my application is hitting

INSERT INTO mytable (SAMPLE) VALUES(N'乕乭乺丕')

== version details ==
unixODBC version - 2.3.0
freeTDS version - 0.95

I can successfully insert the characters in BMP.
This characters are inserted perfectly fine.
INSERT INTO mytable (SAMPLE) VALUES(N'乕乭乺丕')

While using Microsoft SQL Server Native Client for Linux, the supplementary characters are inserted properly.

== freetds conf ==
[mysever.mydomain.com]
host = mysever.mydomain.com
port = 1433
tds version = 7.3
debug flags = 0xffff
dump file = /tmp/dump.log
dump file append = no
client charset = UTF-16
use utf-16 = yes

unixODBC v2.3.0 has been built with

./configure --prefix=/usr/local/ --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE

FreeTDS v0.95 has been built with

./configure --prefix=/usr/local --sysconfdir=/usr/local/etc --with-tdsver=7.3

Things to look at / try:

Looking at the documentation again (now that you have posted the config file and configuration options), I think your setting of client charset is incorrect. You specify --with-iconv-char-enc=UTF-8 when configuring the build for the ODBC driver, so I suspect you need to also use UTF-8 when specifying the client charset option (or whatever encoding your app code is using).
Try rebuilding FreeTDS with --enable-odbc-wide-tests. This might help narrow down where the issue is (assuming the test uses Supplementary Characters).
Did you see the note about "Version 0.95 removed support for iconv" on the FreeTDS page for How to build: Configure and make? Not sure what, if any, impact this has, but if nothing else helps then you need to verify this part (i.e. that your build of iconv works and that it is being used).
Looking at the unixODBC website, the main page has a version history and you claim to be using 2.3.0 which, according to that history, was released in April 20th, 2010 (almost 6 years ago). Since then there have been several "wide character"-related bug fixes, as well as many other fixes. Even if some other change (#1 perhaps) fixes the immediate issue, I would still upgrade to the most recent version of this driver, 2.3.4, which was released on August 31st, 2015.
Something to consider, perhaps, but if the Microsoft driver does work, why not just switch to using that?


Same query, Same linked server. Different result I have two instances (SQL2008R2_A and SQL2008R2_B), they have same linked server configuration to Oracle_C. I used the same query to select from Oracle_C. SQL2008R2_A returned the correct result but SQL2008R2_B get nothing.
And I tested linked server connection of both instances. They are all successfully.
Interesting...

Thank you for your response. Before post this question I already double checked the settings for both linked server and TNS name file. There is nothing different between them.

Fortunately, I resolved it by just re-create the linked server at Server B. It might need to refresh some cache.

Thanks again and have a great one:)


Is it possible to have a script that double quotes a view or proc I apparently cannot figure out the key words to google search for what I want, so I need help finding out how to do this. I feel like this may be a duplicate, but I just cannot find this anywhere else.

I would like to be able to easily save a bunch of create table/view/proc scripts to a file. The file has an easy-to-execute format that will drop and then re-create all of the objects, so it can be run on multiple databases.

This format works well for me as I have to make changes to some objects during development, and then make the same changes on multiple other databases. I can just change the database and click execute.

However, some of the scripts have A LOT of quotes in them, and it gets QUITE annoying to have to double/quad quote them every time I make a change or add a new object to the file. For instance...

IF NOT EXISTS (SELECT * FROM sys.views WHERE NAME = 'MF1' AND schema_id = (SELECT schema_id FROM sys.schemas WHERE NAME = 'CFConversion'))
BEGIN

EXEC('CREATE VIEW [CFConversion].[MF1]
AS
...
...
...
CROSS APPLY (
VALUES (''T'', ''FIT', FIT, '''')
      ,(''T'', ''SIT', SIT, '''')
      ,(''T'', ''FUTA', FUTA, '''')
      ,(''T'', ''Employee_SUI'', EMPLOYEE_SUI, '''')
      ,(''T'', ''Employee_SDI'', EMPLOYEE_SDI, '''')
...
...
...

With tons of values in the Cross Apply, it just gets annoying. How can I easily export the definition of an object to have double/quad quotes?

In case this helps anyone else, an alternative approach to the using double single-quotes to represent a single single-quote in a string is CHAR(39).  That sentence is hard to follow, so as an example:

'Don''t'

can be represented using this approach as

'Don' + CHAR(39) + 't'

While this may not look as pretty with quick strings, it may clear up issues with large blocks of DSQL.  To further clean it up, I also suggest you mask special characters with variables such as:

DECLARE @tick CHAR(1) = CHAR(39)
SELECT 'Don' + @tick + 't code with DSQL'

In my scripts that I want to keep clean, I often take this approach with Tabs CHAR(9) and newline (crlf) characters CHAR(13) + CHAR(10)


Should images be separated from posts in MongoDb? I am working on a project with Meteor.js and I am using CollectionFS with GridFS to easily store images in MongoDb. Now I am not the best with databases and database design, but I heard that in nosql database you should try to denormalize data. So to my real question, what is preferred?

One:

An Images collection and then a Posts Collection with a corresponding imageId?

Only a Posts Collection(FS.Collection) and then save the image along with title, etc?

I feel like this is stupid, but somehow I think they should be separated.

You feel like they should be separated because that makes perfect sense in the relational world.
But as you said in the NoSql world and in MongoDB in particular, you want to group like-items together.  I've not done extensive research on Mongo, but have spent some time with the online classes and I believe the answer is to store them together.  You probably realize there is no such thing as a join in Mongo and therefore if you wanted to get 100 rows and get their corresponding images, you'd have to get the IDs for the 100 rows and then get 100 rows by their identifier (object_id or whatever).  In other words, you have to do manual joins.


PostgreSQL GIN index not used when ts_query language is fetched from a column I've got a table that stores some multilingual content:

CREATE TABLE search (
  content text NOT NULL,
  language regconfig NOT NULL,
  fulltext tsvector
);

CREATE INDEX search_fulltext ON search USING GIN(fulltext);

INSERT INTO search (language, content) VALUES
  ('dutch', 'Als achter vliegen vliegen vliegen vliegen vliegen vliegen achterna'),
  ('dutch', 'Langs de koele kali liep een kale koeli met een kilo kali op zijn kale koeli-kop.'),
  ('dutch', 'Moeder sneed zeven scheve sneden brood'),
  ('english', 'I saw Susie sitting in a shoe shine shop. Where she sits she shines, and where she shines she sits.'),
  ('english', 'How can a clam cram in a clean cream can?'),
  ('english', 'Can you can a can as a canner can can a can?');

UPDATE search SET fulltext = to_tsvector(language, content);

To make sure I always search in the correct language I use these queries:

SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
(1 row)

SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'vlieg');
(1 row)

Because hardcoding the language doesn't give the correct results:

SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'shine');
(0 rows)

SELECT FROM search WHERE fulltext @@ to_tsquery('english', 'vlieg');
(0 rows)

The problem however is that PostgreSQL doesn't use the GIN index when using the first set of queries and instead does a sequential scan:

(Note: I've disabled scanning using SET enable_seqscan = OFF; for these examples because of the low amount of rows)

EXPLAIN ANALYZE SELECT * FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
---
Seq Scan on search  (cost=0.00..17.35 rows=2 width=136) (actual time=0.040..0.044 rows=1 loops=1)
    Filter: (fulltext @@ to_tsquery(language, 'shine'::text))
    Rows Removed by Filter: 5
Planning time: 0.039 ms
Execution time: 0.064 ms
(5 rows)

While it does when hardcoding a language:

EXPLAIN ANALYZE SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'vlieg');
---
Bitmap Heap Scan on search  (cost=12.63..23.66 rows=82 width=0) (actual time=0.044..0.044 rows=1 loops=1)
  Recheck Cond: (fulltext @@ '''vlieg'''::tsquery)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on search_fulltext  (cost=0.00..12.61 rows=82 width=0) (actual time=0.037..0.037 rows=1 loops=1)
        Index Cond: (fulltext @@ '''vlieg'''::tsquery)
Planning time: 0.128 ms
Execution time: 0.065 ms
(7 rows)

So my question is: Is it at all possible to use a column in the ts_query to use the correct language config and still have Postgres use the GIN index?

I'm using PostgreSQL 9.4.

EDIT:

Here's the execution plan from the real table:

Using a column for language config:

Seq Scan on search  (cost=0.00..8727.25 rows=188 width=0) (actual time=0.725..352.307 rows=1689 loops=1)
  Filter: (fulltext @@ to_tsquery(language_config, 'example'::text))
  Rows Removed by Filter: 35928
Planning time: 0.053 ms
Execution time: 352.915 ms

When hardcoding the language:

Bitmap Heap Scan on search  (cost=28.65..4088.92 rows=1633 width=0) (actual time=0.514..10.475 rows=1684 loops=1)
  Recheck Cond: (fulltext @@ '''exampl'''::tsquery)
  Heap Blocks: exact=1522
  ->  Bitmap Index Scan on search_fulltext  (cost=0.00..28.24 rows=1633 width=0) (actual time=0.333..0.333 rows=1684 loops=1)
        Index Cond: (fulltext @@ '''exampl'''::tsquery)
Planning time: 0.180 ms
Execution time: 10.564 ms

EDIT #2

Tried it with Postgres 9.5, same result

I suggest a solution with partial expression indexes:

CREATE TABLE search (
   search_id serial PRIMARY KEY
 , language  regconfig NOT NULL  -- order of columns matters a bit
 , content   text NOT NULL
   --  *no* redundant fulltext tsvector
);

No redundant fulltext column - makes the table smaller, which helps overall performance.
Create one partial expression index for every relevant language:

CREATE INDEX search_fulltext_dutch ON search USING GIN(to_tsvector('dutch', content))
WHERE language = 'dutch'::regconfig;
CREATE INDEX search_fulltext_english ON search USING GIN(to_tsvector('english', content))
WHERE language = 'english'::regconfig;
-- more?

All of the partial indexes together are only about as big as your total index.

Then match index conditions in the query:

SELECT * FROM search  -- does not return useless column fulltext now
WHERE  language = 'dutch'::regconfig  -- match partial index condition
AND    to_tsvector('dutch', content) @@ to_tsquery('dutch', 'vliegen')

UNION ALL
SELECT * FROM search
WHERE  language = 'english'::regconfig
AND    to_tsvector('english', content) @@ to_tsquery('english', 'vliegen');

-- more?

You get bitmap index or index scans this way.
Another index on language may be useful for other purposes, this query does not need it.


Can I query from two databases if my web application is connected to one database? I have a local database, DB1, that I created and is connected to my web application. My web application takes in stored procedures from DB1 and queries them. My application connects to SQL Server using my Windows Authentication account.

I have access to another database, DB2, on a different server that I have read only access to using the same windows account. Can I still query from DB2 if my application is only connected to DB1?

When I execute stored procedures in DB1 that also query stuff from DB2 it works on SQL Server Management Studio but I get an error when I try to do it on my application.

You need to add DB2 as a linked server on your DB1 instance.
Once created, then you can specify [LinkedServer].[Database].[schema].[Table] to query the objects on that server. Other than linked servers, there is no other way for a connection to discover and connect to another server(even if you have access) for security reasons. If you create a linked server, you are specifying a connection that server DB1 can establish and query DB2, all from BD1, not from the client.

Hope that helps.
How to create a linked server in MSSQL


POS Performance Issue AX 2012 R3 I have slowness.

Can anyone review this plan (PasteThePlan) and tell me what I need to optimize? This is the actual plan.

That is a part of process to get Customer Info from an application. In a trace I found that query takes a long time. It took about 14 sec and in another POS it took less than 1 sec.

I'm using SQL Server 2016 - Express Edition

Assuming this is the store database for a Retail POS and not your main AX database you should be fine running it on Express Edition. Since the store database only contains a subset the data necessary for the store to function without a permanent connection to the main system, and transactions should only be kept for the time needed to allow the system to sync to the main database these store databases are usually fairly small.

However, your estimates are way off (now that you posted the actual plan) in this database, which could explain why your query runs better on another POS (assuming you mean that is in a different store/location with another database behind it) if that database happens to have better statistics.

Please see AX Retail: Retail store maintenance and SQL Express to see how you can schedule maintenance with SQLCMD, but I would suggest you use Ola Hallengren's scripts instead, the FAQ explains how to set it up on a SQL Server Express instance.

I also think you have customized the logisticselectronicaddress table, so you might want to review the indexing on that table, or post the table definition here so we can help you.


Alternating usernames while connecting to mysql database My server has a max_connections value of 400 and a max_user_connections of 250. Since I am always connecting with the same user name, my effective max_connections is 250 instead of 400. To solve this, I though to connect with one username 50% of the time, and another username 50% of the time:

if(mt_rand(1,2) == 1){
  $con = mysqli_connect(DB_SERVER, DB_USER_1, DB_PASS) or die(mysql_error());
}
else{
  $con = mysqli_connect(DB_SERVER, DB_USER_2, DB_PASS) or die(mysql_error());
}

My question is, are there any downsides to doing this relating to the fact that the same person might alternate back and forth between different users? Or is this not a problem as the previous connection is "forgotten" and when the user refreshes we are starting a new connection and there is no relation to what user he/she connecting with last page refresh.

In general, is there a better way to deal with this?

I would opt to increase the number of connections instead of owning two different users/configurations in the same host with same privileges.

Unless, there is a persistent connection, php itself will close the connection explicitly even though you do not close them yourself.


How to safely insert a jsonb into a table? I wrote this prepared statement for inserting jsonb data into a table:

PREPARE posts_insert(jsonb) AS
  INSERT INTO posts(data) VALUES($1) RETURNING ID;

Though I'm concerned if this query has some kind of vulnerabilities. The json comes from the client side of my application, so it could basically contain anything. Is it dangerous to use jsonb (that comes from the user) within queries? What can I do to make it safe or more safe?

Is it dangerous to use jsonb (that comes from the user) within queries?

Not it's not dangerous because that JSON is essentially just text as it comes from the client, and you've protected yourself by using prepared statements. This text will be transparently fed to the internal function jsonb_in, and you'll be totally safe.


Packages in Data Collector folder What are those in the Data Collector folder ?  I tried to delete some and got the message that it was conflicting with some reference.

Those packages are related to Management Data Warehouse.

If you open SQL Server Management Studio you will find a node 'Data Collection' where you can right click to configure data collection and also have access to some historical reports about server activity.

Have a look at System Data Collection Sets where for example it states:

  The Query Activity collector type collects data by using the
  QueryActivityCollect.dtsx SSIS package, and uploads data by using the
  QueryActivityUpload.dtsx SSIS package.

For more information see Data Collector Architecture and Processing.

I'm not sure how you got to a point where you get 2 versions of each package (where one is appended by (1)). I tried reproducing that by enabling/disabling collection or creating a new MDW but maybe you got into that state by upgrading and then re-enabling data collection or something.


Merge subscription behaviour I have a merge subscription with the ExchangeType parameter set to 1 (upload only).
Rows are transferred to the publisher as expected.

If I delete a row at the subscriber, the corresponding row at the publisher doesn't get deleted. Accidentally, this is the exact behaviour I desire, but I can't find any documentation stating it's by design.

Before going live, I would like to have something documenting the thing.

Can anyone confirm this is the expected behaviour?

Sorry for the confusion. I missed an option in the article that decides how DELETEs are treated.

The option is called "delete_tracking" and it's set to 0. It is documented here: http://msdn.microsoft.com/it-it/library/ms174329.aspx


EXPLAIN output suggests that my index is not being used I have set up my table with an index only on done_status(done_status = INT):

When I use:

EXPLAIN SELECT * FROM reminder  WHERE done_status=2

I get this back:

id  select_type  table     type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE       reminder  ALL   done_status    NULL NULL     NULL 5     Using where

But when I issue this command:

EXPLAIN SELECT * FROM reminder  WHERE done_status=1

I get the following returned:

id  select_type  table     type  possible_keys  key          key_len  ref   rows  Extra
1   SIMPLE       reminder  ref   done_status    done_status  4        const 2

The EXPLAIN shows me that it uses 5 rows, the second time 2 rows.

I don't think the index is used, if I understood it right first time it should give me 3 rows. What do I do wrong?

SHOW INDEX FROM reminder:

Table    Non_unique  Key_name     Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment
reminder 1           done_status  1             done_status  A          5            NULL      NULL          BTREE

explain extended:

id  select_type  table     type  possible_keys  key          key_len  ref    rows  filtered  Extra
1   SIMPLE       reminder  ref   done_status    done_status  4        const  2     100.00

show warnings didn't show anything of interest.

You misunderstand what the 'rows' field is. It is the number of rows that mysql estimates it shall need to read to satisfy your query. This value can be quite inaccurate. It does not mean this is the number of rows in the result - or the actual number of rows read by mysql


Multilingual Dictionary Database question Let's say someone is coding a multilingual dictionary in PHP, and he wants to let people submit both words and definitions in English and Spanish, but wants to track it so they can be filtered.  Tables thus far are as follows:

words
languages
definitions

Languages are in a separate table to allow for expanding later on.  That being said, would it be better practice to create a separate table for associating words and definitions with their respective languages and join when querying, or to add a language column directly into each table referring to the id column in languages?

If it were my decision, it would look like this:

words (wordID, word, languageID, date_of_addition)
related_words (wordID, otherword0
definitions (wordID, definition, order)
languages (languageID, languageName, languageIdentifier)
language_name_translations (languageID, languageTranslatedID,
                            languageTranslatedName)
  So that this table can be joined back on the previous one to use the two ids
  to show for instance that 2 (spanish) is presented to users of 1 (english) as
  "Espanol" (really it's just because this is _my_ definition that it makes sense
  to me, you would work on your own definitions of course)
synonyms (wordID1, wordID2)
antonyms (wordID1, wordID2)
word_origins (originID, wordID1, wordID2, notes)

So I would suggest at this point that you go get a copy of OED and identify all the parts that they present in their listings. Find out things like related words and word origins and consider that every single entry is a list in some fashion or another, so every presented part should be an entity in a table.


Kill concurrent materialized view refresh PID Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them.

Is there some other way to obtain the PID, in order to perform pg_terminate_backend(pid)?

That is not true.

CREATE MATERIALIZED VIEW slow AS
SELECT i,
       pg_sleep(1) IS NULL AS no
FROM generate_series(1, 10) AS i;

CREATE UNIQUE INDEX ON slow (i);

REFRESH MATERIALIZED VIEW CONCURRENTLY slow;

That statement does not complete immediately, but hangs until the materialized view is refreshed.

Then, while the statement is running, in another session:

SELECT pid, state, query FROM pg_stat_activity ;

  pid  | state  |                      query
-------+--------+--------------------------------------------------
 12618 |        |
 12620 |        |
 13049 | active | REFRESH MATERIALIZED VIEW CONCURRENTLY slow ;
 13032 | active | SELECT pid, state, query FROM pg_stat_activity ;
 12616 |        |
 12615 |        |
 12617 |        |
(7 rows)

SELECT pg_cancel_backend(13049);

 pg_cancel_backend
-------------------
 t

The statement is canceled. In the first session, I see:

ERROR:  canceling statement due to user request

Perhaps you are confused by the fact that the query column in pg_stat_activity does not only show the current query, but the latest query that was run in that session.  So if the state is idle, then the statement is done and the materialized view is already refreshed. Then of course canceling the query has no effect.


troubleshooting mysqlimport very large text file I've been attempting to import a very large (390 GiB) text file using the mysqlimport client.
I've been successful at importing a 14 GiB text file as well as numerous smaller files using this method. Server version is 5.7.28

The specific command used is:

mysqlimport -u root --fields-terminated-by='\t' --fields_escaped_by=\\ homo_sapiens_variation_98_38 -L *.txt

The my.cnf file I'm using:

[mysql]
local-infile = 1

[mysqld]
secure-file-priv = ""
local-infile = 1
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 8G
innodb_write_io_threads = 64
innodb_log_buffer_size = 256M
innodb_read_io_threads = 64
innodb_buffer_pool_size = 32G
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT

[client]
loose-local-infile = 1

It always hangs (repeated 3 times) when it reaches an auto increment value of 2 324 322 623 (i've spaced for clarity).

The number of lines in the file is 2 324 037 635 (determined by sed -n '$=' transcript_variation.txt)

This suggests the file has been read but the process of writing the data did not take place.

-Update-

Table structure

CREATE TABLE `transcript_variation` (
  `transcript_variation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(11) unsigned NOT NULL,
  `feature_stable_id` varchar(128) DEFAULT NULL,
  `allele_string` text,
  `somatic` tinyint(1) NOT NULL DEFAULT '0',
  `consequence_types` set('splice_acceptor_variant','splice_donor_variant','stop_lost','coding_sequence_variant','missense_variant','stop_gained','synonymous_variant','frameshift_variant','non_coding_transcript_variant','non_coding_transcript_exon_variant','mature_miRNA_variant','NMD_transcript_variant','5_prime_UTR_variant','3_prime_UTR_variant','incomplete_terminal_codon_variant','intron_variant','splice_region_variant','downstream_gene_variant','upstream_gene_variant','start_lost','stop_retained_variant','inframe_insertion','inframe_deletion','transcript_ablation','transcript_fusion','transcript_amplification','transcript_translocation','feature_elongation','feature_truncation','protein_altering_variant','start_retained_variant') DEFAULT NULL,
  `cds_start` int(11) unsigned DEFAULT NULL,
  `cds_end` int(11) unsigned DEFAULT NULL,
  `cdna_start` int(11) unsigned DEFAULT NULL,
  `cdna_end` int(11) unsigned DEFAULT NULL,
  `translation_start` int(11) unsigned DEFAULT NULL,
  `translation_end` int(11) unsigned DEFAULT NULL,
  `distance_to_transcript` int(11) unsigned DEFAULT NULL,
  `codon_allele_string` text,
  `pep_allele_string` text,
  `hgvs_genomic` text,
  `hgvs_transcript` text,
  `hgvs_protein` text,
  `polyphen_prediction` enum('unknown','benign','possibly damaging','probably damaging') DEFAULT NULL,
  `polyphen_score` float DEFAULT NULL,
  `sift_prediction` enum('tolerated','deleterious','tolerated - low confidence','deleterious - low confidence') DEFAULT NULL,
  `sift_score` float DEFAULT NULL,
  `display` int(1) DEFAULT '1',
  PRIMARY KEY (`transcript_variation_id`),
  KEY `variation_feature_idx` (`variation_feature_id`),
  KEY `consequence_type_idx` (`consequence_types`),
  KEY `somatic_feature_idx` (`feature_stable_id`,`somatic`)
) ENGINE=MyISAM AUTO_INCREMENT=2324322623 DEFAULT CHARSET=latin1

RAM 128Gb
State: "Repair with keycache"

Any suggestions on how I can troubleshoot and import this file?

The problem with this starts with the my.cnf

The table is using the MyISAM Storage Engine but you have no MyISAM specific options in my.cnf

You are getting the "repair by keycache" because the MyISAM Key Cache is way too small. The default key_buffer_size in MySQL 5.7 is 8MB. Also note that you could have set up the MyISAM bulk insert buffer, whose default is also 8MB.

If you had added this to the my.cnf

[mysqld]
key_buffer_size = 1G (or 2G)
bulk_insert_buffer_size = 256M (or 512M)

and restarted mysqld, you could have bulk loaded without changing the data files all that much.


SQL Server: How to export database with linked servers I'm a web developer and I want to export a database to  work on it in my local machine.

I'm using generate script with data. the script is generated but without the linked servers used by some Views.

Is their a way to generate a full script with linked servers or do I have to create it manually with sp_addlinkedserver?

Linked servers are server-level objects--they are not contained within the database. Therefore you will need to move them separately.

In Management Studio, in the Object Explorer, Navigate to Server Objects --> Linked Servers --> [the linked server you want].

Then just right-click --> Script Linked Server as... --> CREATE to --> [pick a destination]. This will not script out passwords.

If the linked server has stored credentials, you will need to re-input the password. You can either edit the call to sp_addlinkedsrvlogin in the generated script (replace "########" with the real password), or edit the linked server from Management Studio after creating it with the wrong password. Saving the script with the password embedded is not a good security practice, so I generally opt to either edit the script at runtime, or edit the authentication settings after the fact.


How to use SSL/TLS between server and client with Postgres? I don't know much with it comes to security. I'm deploying a virtual machine with my database in a cloud service. I would like to encrypt all the communication between this server and any clients. How can I do that ?

More information:
I have read a lot of material from postgres documentation [1,2], but I can't understand much of it. I have followed this tutorial and now I have these files:

valter@eniac:test$ ll
total 28
drwxr-xr-x 2 valter valter 4096 Jan 25 12:54 ./
drwxr-xr-x 3 root   root   4096 Jan 25 12:50 ../
-rw-rw-r-- 1 valter valter 1834 Jan 25 12:53 privkey.pem
-rw-rw-r-- 1 valter valter 4783 Jan 25 12:54 server.crt
-rw------- 1 valter valter 1675 Jan 25 12:53 server.key
-rw-rw-r-- 1 valter valter 3672 Jan 25 12:53 server.req

But I don't know what to do with them. Where are root.crt and root.crt files ? How can I generate them ? Where do I put these generated files ? And what should I do on the client side ?

Do you just want to use a certificate on the server which the clients use to authenticate the server?  (This is the normal mode of operation).  The server then authenticates the clients using password challenges just as if ssl were not being used.

Or do you want to use client certificates to authenticate the clients to the server as well as the server to the clients?  This is rare, as it requires a certificate authority to issue certs to all of the users, and requires users to properly manage them.

Assuming the first of these, you don't have need root.crt files on the server.  All that you need to do now is edit postgresql.conf to turn ssl=on, and edit pg_hba.conf to either require or allow your clients to use ssl (hostssl requires it, or host allows it.)

Note that with the default client configuration (at least if the client is psql), all that you really get from using ssl is encryption, not true server authentication.  That is because the client just accepts whatever certificate the server sends it, and uses that cert to set up an encrypted channel.  So a bad guy can run a PostgreSQL server with a self-signed certificate, and if it can trick your clients to connect to it rather than your real PostgreSQL server, they will happily connect to it and send their queries to it.


Database normalization, 3 mistake [stucked] - Homework I've been given a homework at university, which says I should make a database with 7 tables [see picture below]. The scheme I made is good, except some normalization is needed, as my tutor told me. (Side note: I have already done normalization with CONTINENTS and COUNTRIES tables)

After I have contacted with my tutor, he told me I'm missing two normalization in this schema and there is a database modelling mistake. However, this is all I know about my mistakes.

My thoughts about my possible mistakes:

In CUSTOMERS table COUNTRY_NAME should be a FK, referencing COUNTRY table's COUNTRY_NAME.

(In this case the CONTINENT_ID is not necessary in my opinion)
In ORDERS table the TOTAL_AMOUNT is redundant because of ITEM table's QUANTITY. Is it a problem, and if so, how can I get rid of it?
There are lots of 'CITY' columns with VARCHAR2 type. This can be a problem when 1) the user misspell them in other tables or 2) VARCHAR2 is taking up too much storage. Should I make a ZIPCODES table with zipcode and city columns and use it instead?
Repeating records: In WORKERS table there is a column called EMAIL. In my opinion a worker can have multiple e-mail addresses, so in this case: Can this cause me trouble, i.e. causing repeating records?
CUSTOMER/WORKER table and DIVISION/WORKER table have a N:M (?, not so sure) relationship. So is it necessary to make another table between them?

I would be really thankful if somebody can find the missing normalizations and answer my questions.

Responses to clarification requests

About the modelling mistake: I don't have much information about business rules. It's because of this lesson is about normalization, so this is kind of a cherry on the cake. Not a big problem if I won't find it.

About the normalization: I'm pretty sure, that I should decompose the current structure up as mentioned via comments. The question is, what I'm missing here. I'm starting to go blind because of staring too much at them.

Note: my university days are long behind me - I'm coming at this from a real-world perspective. Doesn't mean the answers are the expected ones, but may help give you an argument for your choices. A good instructor would consider such thing....

Agreed - a customer logically would be in a country, not a continent.
While TOTAL_AMOUNT is presumably the sum of QUANTITY_SHIPPED * PRICE for all  Items in an Order, I wouldn't consider it a normalization issue. It's a stored calculation. In a real system, the total amount could be impacted by discounts or coupons, and (in the other direction) by sales tax. So, it's arguably appropriate.
First, the CITY fields in customer, country, and division wouldn't necessarily match each other anyway. Second, a given city can have multiple zip codes, and a given zip code can cover multiple cities (at least in the United States). If you repeated the customer's address in the order, that would probably be considered a normalization error. (Arguably - I have had Amazon orders shipped to several different addresses....)
While a person certainly can have multiple email addresses, unless the assignment specified that you had to account for multiple email addresses, I would leave that be.
I assume each customer has one and only one sales rep, whereas each sales rep (worker_id) might have multiple customers; and, that each worker belongs to one and only one division. No need for a table in between, then. Many-to-many relationships are where you normally need some sort of mapping table to define the connections.

I assume that the tool you are using points the connections at essential random points on each table's box; if so, it's hard to be certain all the relationships are indeed set up with the correct directionality; if you haven't, I'd double-check that.

You have a salesrep_id in both customer and order. Again, real-world experience tells me that's wise (Real sales reps often move from one territory to another, and, if customers have real sales reps, you might be very interested in just who sold Customer X what, years later). However, in the context of your assignment, that might be considered a denormalization.

The only other thing that leapt out at me as I looked at the diagram was that there was no product table. I find that somewhat surprising. If there was a separate product table, then of course some of the fields in the items table would move there.


How to configure w=1 and j=1 by default I am new to Mongodb and I would like to confirm my configuration of write concern with majority and journal are correct.

I have three mongodb nodes:

1) In the mongo.conf of all mongodb servers, i added

storage:
    journal: enabled: true

2) in the mongodb shell, i entered

cfg = rs.conf()
cfg.settings.getLastErrorDefaults = {w: 1, j: true}
rs.reconfig(cfg)

Am I correct?

if yes, my second question is, what is the purpose of step 1 for?

Thanks

Yes, you have done correct steps to configure default write concern settings.
What comes to the second question, as it is written here at the documentation:

WiredTiger uses checkpoints to provide a consistent view of data on disk and allow MongoDB to recover from the last checkpoint. However, if MongoDB exits unexpectedly in between checkpoints, journaling is required to recover information that occurred after the last checkpoint.
With journaling, the recovery process:

Looks in the data files to find the identifier of the last checkpoint.
Searches in the journal files for the record that matches the
identifier of the last checkpoint.
Apply the operations in the journal files since the last checkpoint.


Query using a filtered index but wrong estimated number of rows I have a table with a nonclustered index on a datetime2 field.

On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(deleted).

There are 451047 records with the datetime2 field set at NULL but only 7095 are marked as A.

Every query in the application looks for active records only, so, every query that's looking for NULLs in the datetime field was getting really bad estimates and, with that, bad execution plans.

Simple example:

I decided then to create a filtered non clustered index but estimates were still incorrect:

It looks like i still get the old estimates, even if the query is using properly the filtered index. Does anyone know the reason of this behaviour?

These are the new statistics of the filtered index:

Table definition:

CREATE TABLE [TYDATPRD].[HAND00F](
    [STDRECSTS] [char](1) NULL,
    [HDHAND] [numeric](14, 0) IDENTITY(1,1) NOT NULL,
    [HDCHKINDT] [datetime2](7) NULL,
    --lots of other columns which I don't think are needed

    CONSTRAINT [PK_TYDATPRD_HAND00F] PRIMARY KEY CLUSTERED (
        [HDHAND] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

Nonclustered filtered index definition:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] ON [TYDATPRD].[HAND00F] (
    [HDCHKINDT] ASC
)
WHERE [STDRECSTS]='A'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO

This is not intuitive but try adding the char[1] column to the filtered index definition:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT]
ON [TYDATPRD].[HAND00F]
(
    [HDCHKINDT], STDRECSTS
)
WHERE [STDRECSTS] = 'A'
WITH (DROP_EXISTING = ON) ON [INDEXES];

In my testing, this led to the right estimates.

As an aside, I noticed that the plans in your question were different - one was properly parameterized and the other had a constant. Be very careful about how you're doing local testing with local variables and typed constants, especially if in the real world this will be done by parameters to stored procedures.

Also, be very careful about case - literals in the where clause like = 'a' and = 'A' will generate different plans if they don't get parameterized, because the query text is different. Never mind that in some collations they won't yield the same results. (And does that char(1) column need to allow NULL?)


Unknown column in field list Please help i dont know what i'm doing wrong.

CREATE DEFINER=`root`@`localhost` PROCEDURE `setState`(IN `statename` VARCHAR(100), IN `countryID` INT(11))
    MODIFIES SQL DATA
BEGIN
      SET @statename := `stateName`;
      SET @countryID := `country_idcountry`;

    INSERT INTO `state`(
        `stateName`,
        `country_idcountry`
    )
    SELECT
        `stateName`,
        `country_idcountry`
    FROM
        (
        SELECT
            @statename AS `stateName`,
            @countryID AS `country_idcountry`
    ) AS tmp
    WHERE NOT EXISTS
        (
        SELECT
            stateName
        FROM
            state
        WHERE
            stateName = @statename
    )
    LIMIT 1;
END;

When i execute the statement with

call ccc.setState('Region Maritime', 8);

it returns

Error Code: 1054. Unknown column 'country_idcountry' in 'field list'

But my STATE Field is shown below :

the 'country_idcountry' is a foreign key from the country table.

You In  para meter is not country_idcountry it is countryID

see

PROCEDURE `setState`(IN `statename` VARCHAR(100), IN `countryID` INT(11))

So you mus use

SET @countryID := `countryID`;

To grab the country_idcountry.

Be aware when country_idcountry is a foreign key you must have the county id already in your database.


How can I establish whether it would be a good idea to upgrade from Express to Standard? In Express 2019 running on Linux, If I right click my database I can see that it is size 11506 Mb with 1963 Mb free space. Currently showing 17 users.

I am trying to work out whether it would be better to delete records or upgrade.

Given the cost to upgrade is significant, how can I establish what performance benefits we might gain?

If we trial upgradeing can we roll back?

I am trying to work out whether it would be better to delete records or upgrade.

This is a business decision, not a technical one. Do not just delete data "to make space" - if the data is important and must be retained, then the organization will have to find a way to pay to keep it around.

You can't downgrade SQL Server in the way you're suggesting.

If you want to explore the possibility of upgrading to a paid edition of SQL Server, I suggest setting up a new server with Developer Edition installed (Developer Edition is free). Then take a backup of your current database, restore it to this test server, and run a sample of your workload against that.

If you decide you need to upgrade to Standard Edition, this method (backup & restore) is also how you'll move your data over. I do not recommend performing an in-place upgrade (which is what your proposed method suggests).

If you're currently satisfied with the overall performance of your setup, and only looking into upgrading due to the database size limitations of Express Edition, you have a few options:

Create a second database which mirrors the first, splitting the data between the two. This will require application changes
Move "old" data to a second (archival) database based on age, deleting it from the original database
Just delete the old data altogether
Implement page- or row-level compression on your tables & indexes to buy some more time. But eventually, you'll hit the 10GB limit anyway.
Migrate to a cloud-hosted database platform like Azure SQL DB where you'll pay for what you use

Options 2 & 3 should only be considered if the business requirements for the data allow for it. Option 5 should only be considered if the business's data governance policies allow for it.


Yes/No columns in logical database design I have an assignment where I have to design a logical model.

I have a relation NURSE that has "nurse_id" and "certification". The certification attribute has yes/no values, so, should I move it to another relation, or is it okay to keep it in the same NURSE relation?

And is the suitable data type for that attribute (CHAR)?

Thank you,

For a logical database design you don’t need to specify TINYINT or CHAR — that’s a physical consideration. Some DBMSes such as Postgres have a BOOLEAN data-type which would be preferred, but again, a physical design.

In your logical model you could assume a binary-valued datatype with Yes and No as the two values (or a ternary-valued type if NULL should also be allowed).

You have two questions actually; for the other, yes, I would keep the flag in the same relation.


Auditing sysadmin logons with Extended Events The goal is to audit sysadmin logons as efficiently and safely as possible without auditing logons of other users, so as not to be logging excessive data.

I have found the information on auditing sysadmin logons by using a logon trigger, and in general that would accommodate our needs.  However it does have the drawback of being synchronous, which means that users can't log on if the trigger fails for some reason, and it will slow down their logon a tiny bit.

With the logon trigger, I can use IF IS_SRVROLEMEMBER ('sysadmin') = 1 to dynamically determine if the user is a sysadmin, but something like this does not appear to be possible with audit objects.  It appears that audit filters have to be a simple server_principal_name = 'domain\username' (i.e., you can't use any SELECTs or functions to determine if the user is a sysadmin).

So the only thing left that might even be possible, as far as I've found, is using Extended Events, so I wanted to ask the community if this is something I should bother looking into.  The event would probably have to capture all logons (again, as the filters can't run code), and then we'd have to have something that consumes the event to filter and log the relevant activity.

This may work for you, but by no means do I consider it elegant (or even good for that matter).

First, configure the server to log all successful and failed logins.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO

Now, to audit those logins that are in the SYSADMIN fixed server role, you can run through the following process:

CREATE TABLE #ErrorLog
(
    LogDate DATETIME NULL,
    ProcessInfo VARCHAR(20) NULL,
    [Text] VARCHAR(MAX) NULL
)

INSERT INTO #ErrorLog
exec xp_readerrorlog 0, 1, N'Login succeeded', NULL, NULL, NULL, N'DESC'

SELECT *
FROM (
    SELECT IS_SRVROLEMEMBER('sysadmin', SUBSTRING([Text], CHARINDEX(CHAR(39), [Text]) + 1, CHARINDEX(CHAR(39), [Text], CHARINDEX(CHAR(39), [Text]) + 1) - CHARINDEX(CHAR(39), [Text]) - 1)) AS isSysAdmin
    , SUBSTRING([Text], CHARINDEX(CHAR(39), [Text]) + 1, CHARINDEX(CHAR(39), [Text], CHARINDEX(CHAR(39), [Text]) + 1) - CHARINDEX(CHAR(39), [Text]) - 1) AS loginName
    , *
    FROM #ErrorLog
) InstanceLogins
WHERE isSysAdmin = 1

DROP TABLE #ErrorLog

What this does is pull all successful logins from the current error log, identify the login name, and pass it into the IS_SRVROLEMEMBER function you were already tying into with your Login Trigger approach.

This will potentially balloon your ERRORLOG, but it won't block/slow logins like a failing trigger may cause.  It will also provide login times and if you have some SQL Auditing in place, you may be able to correlate these with activity, etc.  At any rate, I think this will give you what you're looking for.  Final note: you don't have to audit the failed logins, but I find that information more useful than not.


How do you join three or more records from the same table? I have table with a lot of data in it, and we would like a way to essentially flag certain documents to indicate that they are related to (x) other documents:

TABLE A
-----------
id
name
created_at
modified_at

Records:   id        name              created_at              modified_at
            1       arlo g    2020-04-29 12:30:00      2020-04-29 12:30:00
            2     kenny ws    2020-04-29 12:32:00      2020-04-29 12:32:00
            3      roger w    2020-04-29 12:36:00      2020-04-29 12:36:00
            4     frank lw    2020-04-29 12:41:00      2020-04-29 12:41:00
            5    depeche m    2020-04-29 12:43:00      2020-04-29 12:43:00
            6       manson    2020-04-29 12:46:00      2020-04-29 12:46:00

Now - assume a user is viewing document id 1. They want to tie it to documents 3 and 4.

If a user then opens document 1, the system can easily pull documents 3 and 4.

On the same note - if a user opens document 3, they can easily pull documents 1 and 4.

Is this possible? I am not sure how to structure a query clause to pull the document ids for a given document, such that the list is the same each time no matter which one of those documents is queried on.

If a user then opens document 1, the system can easily pull documents 3 and 4.
On the same note - if a user opens document 3, they can easily pull documents 1 and 4.

This looks like the relation have NO parent-child type. So the solution may be
CREATE TABLE r ( group_id INT UNSIGNED,
                 table_a_id INT UNSIGNED,
                 PRIMARY KEY (table_a_id)
              /* PRIMARY KEY (group_id, table_a_id) */
               );

So if you need in "if a user opens document 3, they can easily pull documents 1 and 4", then all 3 table_a values (1,3,4) must belong the same group.
The primary key selection is dependent by the fact does a definite document may belong to one group only or to more than one group.
When you need to obtain the whole group members by one member of a group you will use
SELECT t2.*
FROM table_a t1
JOIN r       r1   ON t1.id = r1.table_a_id
JOIN r       r2   ON r1.group_id = r2.group_id
JOIN table_a t2   ON r2.table_a_id = t2.id
WHERE t1.name = 'initial document name'


How to work with JSONB column I use Postgres and jsonB column

I have the following table:

id(VAARCHAR(64)) | value(JSONB)
--------------------------------
1                |[{"x": "10", "y": 1}, {"x": "32110", "y": 12}]

X has always unique

I can add a new element in this sequence.

I can check that sequence contains the element with x value "10" or not.

But I can't
**Remove element with the special value of x (remove value which contains x:"10")?

Find the maximum value of y?**

Can anybody help me with this?

Is a remove

 UPDATE table_name
    set jsonB_column_name=(SELECT jsonb_agg(z.a)
                                   FROM  (SELECT jsonb_array_elements(t.jsonB_column_name) as a
                                          FROM   table_namet
                                          where id='3a-a7ed-bf88dcdde0ab') z
                                   WHERE NOT a @> '{"x":"10"}')
    where id='1';

Is a search

select max(c.jsonb->>'y')
from (SELECT jsonb_array_elements(t.jsonB_column_name) as jsonb
      FROM   table_namet) as c;


How Does Max Server Memory Affect PLE on a Host That Is Not Memory Bound? I came across an unusual scenario on an instance where only half the physical RAM was being utilized but I was observing PLE values between 1-3 seconds constantly.

Windows Server 2003, Enterprise 5.2.3790 (32-bit) SP2
4 GB RAM
2 Xeon 2.3 GHz CPU
SQL Server 2008 SP2 (10.0.4000)

Min/Max Server Memory were set at defaults and the most resource intensive query was the backup history check by Quest Spotlight. Only 1.6 GB of RAM was utilized, 1.5 of which by buffer and procedure cache.

After setting Max Server Memory to 3 GB, PLE started to climb and is currently at 21 minutes.

I am wondering why Max Server memory would have this positive impact on PLE when less than half the onboard RAM is currently utilized. My first thought is O/S memory trimming was in play before the change in the setting but how can I confirm this?

I'd suspect most of the memory was in use by the procedure cache or another component before you set the maximum memory limit. When you change the maximum memory limit, caches are flushed (you'll see this in the error log), which would have made more space available for caching data pages, which would in turn improve PLE.

If that is what happened, the problem will likely return in time as usage from the components that were using memory before the flush grows back. As you may know, Glenn Berry has some useful queries for analysing memory usage in SQL Server.

As somewhat of an aside, 1.6 or 1.7 GB was roughly the maximum memory 32-bit SQL Server could use on 32-bit Windows under a default configuration. There are some settings that can allow it to use more memory, but they can cause memory starvation of the kernel or user processes if not carefully managed. On newer, 64-bit versions of Windows and SQL Server, memory management is generally far less problematic.


TFS Installation Fails over to mirroring secondary despite not having a Fail-over partner specified We have TFS Version 15.105.27412.0 installed on a server (Server A) and have it configured to point at our database server (Server B).

We have the two TFS databases (tfs_defaultcollection and tfs_configuration) on ServerB and they are mirrored with ServerC, however the connection string in the configuration manager for TFS is not "mirroring aware" - see screenshot below from TFS Administration Console

Furthermore, if I interrogate the TFS configuration database as follows

SELECT * FROM Tfs_configuration.dbo.tbl_Database

This confirms the connection strings for both databases do not have failover partners specified.

We had a planned failover this morning from ServerB to ServerC which included both of the TFS databases and much to everyone's surprise, TFS continued to function with the databases failover and running on ServerC.

I stopped the SQL Server service on ServerB and ran an ExtendedEvents session on ServerC (looking for queries that accessed the TFS databases) and can confirm that the TFS application was indeed querying ServerC.

How did this happen? The TFS installation has no idea that ServerC exists, is something happening at the SQL Server level?

Specifying Failover Partner in the connection string is only for an initial connection.  After connecting the client is notified of the failover partner, and will use that for reconnecting in case of a failover.

  [After connecting to the principal] the data access provider downloads
  the server instance name of the current mirror server. This name is
  stored in the cache as the failover partner name, overwriting the
  client-supplied failover partner name, if any.

Connect Clients to a Database Mirroring Session

So even applications not specifically configured for database mirroring will discover the failover partner at startup.

  our TFS installation has no failover partner specified

So on reboot or cold start TFS will fail to connect if server specified in its connection string is not available and hosting the principal database.  But if a failover occurs after TFS has succesfully connected to the principal it will know to reconnect to the failover partner.


MySQL - varchar length and performance Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?

This is a very common "exam/interview question". I will answer as good as I can:

In the standard row formats for InnoDB and MyISAM (dynamic/compact) a VARCHAR(50) and a VARCHAR(255) will store the string text in the same way- 1 byte for the length and the actual string with between 1 and 4 bytes per character (depending on the encoding and the actual character stored).

In fact, if I remember correctly, I recall someone modifying the data dictionary with an hexadecimal editor in order to change something like a VARCHAR(50) into a VARCHAR(100), so it could be done dynamically (normally, that requires a table reconstruction). And that was possible, because the actual data was not affected by that change.

That is not true with VARCHAR(256), because then 2 bytes (at least) for the length are always required.

So, that means that we should always do VARCHAR(255), shouldn't we? No. There are several reasons.

While InnoDB may store a varchar in a dynamic way, that is not true for other engines. MyISAM has a fixed row size format, and MEMORY tables are always fixed in size. Should we care about those other engines? Yes, we should, because even if we do not use them directly, MEMORY tables are very commonly used for intermediate results (temporary tables on memory), and as the results are not known beforehand, the table has to be created with the maximum size possible -VARCHAR(255) if that is our type. If you can think about the wasted space, if we are using MySQL's 'utf8' charset encoding, MEMORY will reserve 2 bytes for the length + 3 * 255 bytes per row (for values that may only take a few bytes on InnoDB). That is almost 1GB on a 1 million table -only for the VARCHAR. Not only this causes unnecessary memory stress, it may provoke the actions to be performed on disk, potentially slowing it down thousands of times. All of that because of a poor selection of its defined data type (independently of the contents).

It has some consequences for InnoDB, too. Index size is restricted to 3072 bytes and single column indexes, to 767 bytes*. So, it is very likely that you won't be able to index fully a VARCHAR(255) field (assuming you use utf8 or any other variable length-encoding).

Additionally, the maximum inline row size for InnoDB is half a page (around 8000 bytes), and variable-lenght fields like BLOB or varchar, can be stored off-page if they do not fit on the half-page. That has some consequences in performance (sometimes good, sometimes bad, depending on the usage) that cannot be ignored. This caused some weirdness between the COMPACT and DYNAMIC formats. See, for example: error 1118: row size too large. utf8 innodb

Last but not least, as @ypercube has reminded me, more than 1 byte for the length may be required even if you are using VARCHAR(255), because the definition is in characters, while the length stores bytes. For example REPEAT('ñ', 255) has more than 2^255 bytes in utf8, so it would require more than 1 byte for storing its length:

mysql> SELECT LENGTH(REPEAT('ñ', 255));
+---------------------------+
| LENGTH(REPEAT('ñ', 255))  |
+---------------------------+
|                       510 |
+---------------------------+
1 row in set (0.02 sec)

mysql> SELECT CHAR_LENGTH(REPEAT('ñ', 255));
+--------------------------------+
| CHAR_LENGTH(REPEAT('ñ', 255))  |
+--------------------------------+
|                            255 |
+--------------------------------+
1 row in set (0.00 sec)

So the general piece of advice is to use the smallest type possible, because it can potentially create performance or management problems otherwise. A VARCHAR(100) is better than VARCHAR(255) (although a VARCHAR(20) would be better), even if you do not know the exact length. Try to be conservative because, unless the table is too large, you can always change the definition later.

Update: Because the exploding popularity of variable-length strings, for example, with the usage of emojis, Oracle has been pushing for improved performance for those cases. In the latest MySQL versions (5.6, 5.7), InnoDB has been set as the default engine for both intrinsic and explicit temporary tables meaning that variable-length fields are now first-class citizens. That means that there may be less reasons to have very constrained character lengths (but those still exist).

(*) Second Update: large_prefix_index is now enabled by default on the latest MySQL versions (8.0), but that is still true for older versions or if you are using lagacy innodb file/row formats (other than dynamic or compressed), but now by default, single column indexes can be up to those 3072 bytes.


Get more information from Oracle audit trail I just turned on auditing for an Oracle 11g database. I'm interested in getting more information about each event; for example, when I see a CREATE_INDEX event, I'd like to know which field was indexed. Is there any way to do that?

Sure, use one of the below settings:

AUDIT_TRAIL

  db, extended

  Performs all actions of AUDIT_TRAIL=db, and also populates the SQL
  bind and SQL text CLOB-type columns of the SYS.AUD$ table, when
  available. These two columns are populated only when this parameter is
  specified.

  xml, extended

  Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL
  bind information in the audit trail.

alter system set AUDIT_TRAIL=db, extended scope=spfile;
shutdown immediate
startup


Can not drop db in sharded environment I am trying to delete some old dbs in my sharded set up.
Step i am taking are :

Stop Balancer.
Drop Db
Start balancer

But in some of the DBs i am getting these errors.

"errmsg" : "exception: collection's metadata is undergoing changes. Please try again."

"errmsg" : "exception: Error locking distributed lock for chunk drop. :: caused by :: 13661 distributed lock ibeat20140904.dgrpCount/iBeat55:27017:1420803001:1804289383 had errors communicating with individual server x.x.x.x:27017 :: caused by :: field not found, expected type 7"

When i checked the db using show collections some of those collections were removed but not all.
 x.x.x.x:27017 is ip of my config server.
I check hash of all config servers and they seems to be in sync.

Looks like this is similar to: https://jira.mongodb.org/browse/SERVER-17243
This is not fixed as of November 16, 2015.
From the link:

Description
After dropping the collection for a sharded collection, via mongos, the chunks are still visible in the config server. Same if the database is dropped.
This collection had balancing failures, which seems to have left a lock in place, preventing full cleanup.


How does one query a SQL Server Linked Server when SQL Server Authentication is specified? I have established a linked server and am attempting to query a database to which I have a SQL Server username and password, however I can't find the syntax for specifying the userid/pw in the query and get the following error:

Msg 18452, Level 14, State 1, Line 1
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I know from http://msdn.microsoft.com/en-us/library/ms175537(v=sql.105).aspx that windows authentication is preferred, but it is a vendor database and not up to me.

Also from that link: "you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server."
I am not an Windows admin on the local machine and not any kind of admin on the linked machine.  Is there no other way?

Open the properties of your linked server. Select Security, and click the "be made using this security context" radio button. Put your SQL Server login in this section. Clear out any logins listed in the box above "Local server login to remote server login mappings", and try again.


Newly created index not appearing Here's a weird thing: In SQL Server 2017 Standard, I created a new index on one of my tables which already had a few indexes on it, by running the following in SSMS:
create unique index UQ_GtsCbotCurve_InvCatProTypMdOd
ON GtsCbotCurve (InventoryId, CategoryId, ProviderId, TypeId, MarkDate, ObservationDate)

SSMS said "Query executed successfully".
I hit F5 to refresh SSMS's view of the table, but the new index did not appear in the Indexes node under that table in the object explorer.
I listed the indexes on the table using the system catalog tables like this:
select *
from sys.indexes ix
     inner join sys.objects ob on ix.object_id = ob.object_id
where ob.name = 'GtsCbotCurve'

and the new index did not appear there.
But when I rerun the create index statement, it errors out, saying "an index or statistics with that name already exists on GtsCbotCurve".
I disconnected from the server, and reconnected, but the index still doesn't appear.
What could be going on here? Does the index actually exist? How do I get it to appear in the management tools?

Is it possible you had just an empty space highlighted in SSMS when you ran the query? If so, it'll still say "query executed successfully" even though nothing actually executed. If you're able to create the index now, then it definitely didn't create the first time.


Does SQLite choose wrong query plan when rowID is the second column of an index? I was testing the performance of my DB and found that SQLite wasn't choosing the right query plan for my query.

My schema:

CREATE TABLE event (
    ID       INTEGER PRIMARY KEY NOT NULL,
    netAddr  INTEGER             NOT NULL,
    date     INTEGER             NOT NULL,
    value    INTEGER             NOT NULL);
INSERT INTO event ( netAddr, date, value )
    WITH RECURSIVE cnt(x) as (
        VALUES(1)
        UNION ALL
        SELECT x+1 FROM cnt LIMIT 1000000
    ) SELECT 250, x, x+1000 FROM cnt;
CREATE INDEX myIdx ON event ( netAddr, ID );

The query:

SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

It ran too slow and I inspected the query plan:

EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

which gave me

0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=?)

SQLite refuses to use the second column of the index even though it is sorted the way that the query wants it to be.

Then I tested with a different index:

CREATE INDEX dateIdx ON event ( netAddr, date );
EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND date > 100 AND date < 110
ORDER BY date DESC LIMIT 1;

which gave me

0|0|0|SEARCH TABLE event USING INDEX dateIdx (netAddr=? AND date>? AND date<?)

The query plan is now as I expect it to be.

Why doesn't SQLite use the whole of the index i.e. SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)?
What is wrong with having the rowID as a second column in an index?
Is there a way to make it work without adding another column in the table or in the index? (I have little storage for the DB.)

EDIT:

Tried with SELECT value FROM event WHERE id = (SELECT id FROM event WHERE netAddr = 250 AND id > 100 AND id < 110 ORDER BY id DESC LIMIT 1) LIMIT 1; and SELECT value FROM event WHERE netAddr = 250 AND ID > 100 AND ID < 110 ORDER BY netAddr DESC, ID DESC LIMIT 1; as ypercubeᵀᴹ proposed - the results are the same as the "slow" query.

Tried to change the name of ID column to be something else - the results are the same.

EDIT 2:

SQLite version 3.8.2 and 3.8.8.1

In theory, there's nothing wrong with having the rowid as a second column in an index.
And in practice, with the current SQLite version, it works as you expected it to:

sqlite> .eqp on
sqlite> SELECT value FROM event
   ...> WHERE netAddr = 250 AND ID > 100 AND ID < 110
   ...> ORDER BY ID DESC LIMIT 1;
--EQP-- 0,0,0,SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)


Database Design - When to use "attributes" rather than additional columns I'm wrestling with a schema design issue.

I'm attempting to determine when I should add another column instead of making it an "attribute".

Rather than adding a numerous columns to a table, I could use an attribute table, with supporting tables if needed, to store this information.

When users decide they need to track another field, it just gets added to the attribute table.

I'm doing the same thing for people on the request. Rather than putting ApproverID, ExecutiveApproverID, ManagerID columns in the table, I add them to a contacts table or approvers table so that a record can have as many related items as needed. This also prevents the user interface from having to add new fields.

This definitely makes understanding the schema more difficult, but it also makes it more flexible. Of course, those attributes will be limited on how detailed they can be in terms of a sub-hierarchy (unless I want to get really complicated).

I'm just getting this off the ground for some business users who will maintain it, so I can't make the schema too complicated.

Do you think pursuing this strategy is a good idea, or should I pursue a design the just adds columns to tables?

There are two schools of thought with this type of design:

The developer / DBA.
The users.

The Developer/DBA will want to create a system that self-maintains.  In other words precisely what you are pursuing above.

The Users will want a very simple system that they can understand without really understanding anything other than their day-to-day job.

You will be successfully fulfilling your role if you create a system that is easy to manage and not too difficult to learn.

My experience tells me normally the user wants a flat-file based system where they can easily see all the fields "they would ever need" on a single form, without having to design anything themselves.  As a developer/DBA I have created both flat-file based designs and highly normalized designs.  The flat-file designs were me deciding I could no longer tolerate trying to convince the user that they really did need to be able to extend their design without getting me to do the work in the future.  Needless to say, around 10 milliseconds after I put their "everything I'll ever need" system into production, they wanted to add some fields.  Me bitter?  Never.

If you can find a user that understands how to manage the normalized design you want to utilize, get them on your side and have them vocalize their concerns about being able to extend the design without having to involve I.T. for every minute field change.  Then build a really slick user interface that allows them to build the fields (attributes) they want.  Then build the back-end the way YOU want.  Don't make it so complex that if you put it down for 6 months you'll have a hard time understanding what you did.


ORACLE RAC Installation : Why there is a need to create a symbolic links for asm disk By reading the note 1538626.1 from MOS Meta Link, I know that we should use udev to change the ownership of some device for the further installation of oracle real application cluster:
I met a lot of similar udev rules for the purpose of changing the owner of asm disk  in internet as follow:

ENV{DM_NAME}=="data01", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="data02", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="data03", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="data04", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="crs01", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="crs02", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="crs03", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"

After the rules is defined, we can restart the udev and get the device with ownership changed

#ls -l /dev/dm*
brw-rw---- 1 root    disk     253,  0 Dec 15 14:18 /dev/dm-0
brw-rw---- 1 root    disk     253,  1 Dec 15 14:18 /dev/dm-1
brw-rw---- 1 root    disk     253, 10 Dec 15 14:18 /dev/dm-10
brw-rw---- 1 oragrid asmadmin 253,  2 Dec 15 14:18 /dev/dm-2
brw-rw---- 1 oragrid asmadmin 253,  3 Dec 15 14:18 /dev/dm-3
brw-rw---- 1 oragrid asmadmin 253,  4 Dec 15 14:18 /dev/dm-4
brw-rw---- 1 oragrid asmadmin 253,  5 Dec 15 14:18 /dev/dm-5
brw-rw---- 1 oragrid asmadmin 253,  7 Dec 15 14:18 /dev/dm-7
brw-rw---- 1 oragrid asmadmin 253,  8 Dec 15 14:18 /dev/dm-8
brw-rw---- 1 oragrid asmadmin 253,  9 Dec 15 14:18 /dev/dm-9

At the same time, because we use the key SYMLINK in the udev rules, we can get the symbolic links which act as alternative names for the device node

#  ls -l /dev/iscsi/*
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-crs01 -> ../dm-5
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-crs02 -> ../dm-7
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-crs03 -> ../dm-8
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data01 -> ../dm-9
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data02 -> ../dm-2
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data03 -> ../dm-3
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data04 -> ../dm-4

It seems that such SYMLINK has no relationship with the rac installation, why is the purpose of adding the sysmlink property in the udev rules?Can I get rid of sysmlink in the udev rules?

It is not mandatory, do not use it if you do not want to.

Creating such symlinks just makes it easier to distinguish the ASM disks. You can even set asm_diskstring to /dev/iscsi/* with the above.


mysterious load on server due to mysql i was facing heavy load on my server approx two years back and that time i have tried to optimize every single issues like query raid battery my.cnf etc and after that the server load was always under one inspite of heavy load. for last one year i was regularly handling more than 10000 users at any point of time without any issue but suddenly everthing changed last week. whm started showing heavy load on server.i guessed that raid battery backup is not working and requested my host to replace it and they replaced it and after that i was expecting the situation to improve but nothing changed and my host informed that your battery was fine but we have replaced it.
then i thought my temp might be getting exhausted but its completely under control

   Every 0.5s: df -h /tmp                                                                                Sat Mar  8 03:51:29 2014

     Filesystem      Size  Used Avail Use% Mounted on
     /usr/tmpDSK     4.0G  376M  3.4G  10% /tmp

used slow.log to register the slow query and only few query are showing however i have set the slow log time to 5 sec.
then i tried mysqltuner.pl and as i said i had already optimized to best of my knowledge two years back now its not recommending anything

my raid array was at 96% few days back and today it is showing 80%

      Unit  UnitType  Status         %RCmpl  %V/I/M  Stripe  Size(GB)  Cache  AVrfy
      ------------------------------------------------------------------------------
      u0    RAID-5    VERIFYING      -       80%     256K    3725.27   RiW    ON

       VPort Status         Unit Size      Type  Phy Encl-Slot    Model
       ------------------------------------------------------------------------------
       p0    OK             u0   1.82 TB   SATA  0   -            WDC xxxxxxxxxx-02W3
       p1    OK             u0   1.82 TB   SATA  1   -            WDC xxxxxxxxxx-01U1
       p2    OK             u0   1.82 TB   SATA  2   -            WDC xxxxxxxxxx-02W0

      Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
      ---------------------------------------------------------------------------
       bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx

i am getting this from whm which shows heavy load from mysql

  20155 (Trace) (Kill)  mysql   0     181.3   19.9  /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.xxxxxxxx.com.err --open-files-limit=8376 --pid-file=/var/lib/mysql/server.xxxxxxx.com.pid

any suggestion to resolve this issue.

Please pay attention to the raid status: VERIFYING. The progress is displayed as %V/I/M. You can see the verify schedule with the show verify command parameter. As long as the raid is being verified I assume the io performance isn't great. You should use iostat or vmstat to get a better picture what is happening.

The cache is enabled for your raid unit. You might want to test if the overall performance is better if you only enable the write cache but disable the read cache
Furthermore the bbu status seems to wrong - the battery has 0 hours estimated backup capacity.


Why is setting open_files_limit making MySQL slow? We noticed that adding open_files_limit in /etc/my.cnf is making server slow.

Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

Looking back at my answer to your original post, I recommended a value for open_files_limit. Apparently, mysqld saw that 3145728 was the default value deemed safe enough to use. Hopefully, you changed ULIMIT in the OS to match. Otherwise, you will get lots  of table cache issues.

NOTE: mysqld will try to choose a fair default value for open_files_limit based on the amount of memory available at the time mysqld starts. There is a warning from the MySQL Documentation:

  mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed.

Even if you raise innodb_open_files, there is the possibility of caching file handles against the open .ibd files. See Percona's take on it : http://www.mysqlperformanceblog.com/2009/11/18/how-innodb_open_files-affects-performance/. That may also attribute somewhat to the slowness you are experiencing.

In your accepted answer, you stated you set open_files_limit to 1048570, a value mysqld felt was too low. Removing open_files_limit=1048570 from my.ini simply allowed open_files_limit to be defaulted to 3145728. You could increase it from that value, not lower it.


One field with multiple values? I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem:

2 tables:

 - Drivers (id_driver, name, etc...)
 - Trucks (id_truck, model, id_driver,
   etc...)

The user will fill a form with the truck's info, but a truck may have more than one driver, and thats my problem. I don't know what to do there.

Thanks in advance.

This is likely to be a many to many relationship.

create table drivers (
  driver_id int primary key,
  ...
);

create table trucks (
  truck_id int primary key,
  ...
);

create table drivers_trucks (
  driver_id int references drivers(driver_id),
  truck_id int references trucks(truck_id),

  primary key (driver_id, truck_id)
);


Oracle 11g user created with Create session but able to update the table? I created a readonly user with only permission granted was create session and read only privilege, using the link:
https://stackoverflow.com/questions/7502438/oracle-how-to-create-a-readonly-user

Now when I login with the new user I created I am able to update the table.
Can anyone suggest me what I could have done wrong?

There is no such thing as a read only privilege. If someone grants update,delete,insert on x to public, everyone who can create a session can modify the contents of that table.

Also, if you create a user x and using a dba account create a table x.y, the user x - who owns the table y - can modify the contents of his/her table, because he/she is the owner of that table.


Find "second-greatest" string in each "group" I'm doing some data analysis and want to find an easy way to examine all the members of each "group" in a group by function.

Like, 3 agents may be involved in an order. I want to quickly examine the three agents that were 'grouped' in this order for various reasons.

Usually, I would use group_concat for this (easy way to see all grouped strings). However replicating that using a 'group by' appears difficult and unwieldy for now in SQL sever.

Right now, rough-and-dirty, I would max(username) and min(username) to quickly find 2 (and 90% of orders probably have 2 or less people. Is there a way to do mid(username) or 2nd-highest(username), or percentile(50th, username)? That would be a great, quick way to find this relevant data. For some reason, the previous answers I've seen describing group_concat on SQL Server do not sound straightforward to me.

Sample data for instance:

employee  purchase_id
bill        1
bob         1
chrissy     1
mike        2
bill        2
bob         3

Currently I have this:

purchase_id, employee_count, complicated metric
1                 3              blahblah
2                 2                dsflsajf
3                 1                98%

I would like to see at a glance:

purchase_id, employees, complicated metric
1            (bill,bob,chrissy)   blahblah

However the group_concat seems very confusing to use with a group by statement - or simulating group_concat with SQL Server. So instead, how bout this.

select max(employee), min(employee)

purchase_id,  max(employee), min(employee)
1              bill            chrissy

in the example you see that bob is omitted, as max/ min will only find the two endpoints. If there was some kind of function to pull the second highest value, or 50th percentile value, on strings, that would be helpful.

Something like this would split the results into multiple columns, but you would need to know in advance the maximum number of employees per purchase_id:

With Ordered_cte As (
    Select employee,
        purchase_id,
        RowNo = Row_Number() Over (Partition By purchase_id Order By employee)
      From tbl_purchase_employee)
Select purchase_id,
    Employee1 = Max(iif(RowNo = 1, employee, Null)),
    Employee2 = Max(iif(RowNo = 2, employee, Null)),
    Employee3 = Max(iif(RowNo = 3, employee, Null))
  From Ordered_cte
  Group By purchase_id;


Update row contents on some condition For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also store the highest bidder (Which should obviously be updated each and every time someone bids way higher than the previous highest bid).

How would I achieve something like that?

Split things in two --

Some table(s) would hold the 'current' info, such as the latest bid for a given item.
Some table(s) would keep the 'history', such as a list of all the bid values and who, when, what, etc.

When a new bid comes in, you would do two things: UPDATE a row in the first table and INSERT a row into the second table.

By separating "current" from "history" it may help you think through how to structure the data and formulate the queries.


Create index on multicolumn type Using Postgres 9.6

I am trying to create an index on a type adt_flds with a field also named adt_flds

 create type adt_flds as
(
      created_at timestamp
    , updated_at timestamp
    , created_by text
    , updated_by text
    , created_by_client_addr inet
    , updated_by_client_addr inet
    , created_by_client_cmptr text
    , updated_by_client_cmptr text
);

My attempt is as follows for

schema = document

table = training_complete_history

field = adt_flds

field column = created_at

SQL

create index training_complete_history_adt_flds_index
on document.training_complete_history ((adt_flds).created_at);

Unfortunately, my attempt yields the following error.

  [42601] ERROR: syntax error at or near ")" Position: 114.

How would I properly index created_at inside adt_flds?

You need an extra pair of parentheses around the indexed term if it is not a column or a function call.

In the words of the documentation:

  The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.

CREATE INDEX training_complete_history_adt_flds_index
ON document.training_complete_history (((adt_flds).created_at));

I think it is a bad idea to use a composite type if you want to use parts of it in a WHERE or join condition. It violates the first normal form.


How would I run DELETE queries on a Master Without Executing Same DELETEs on a Slave? If I want to run a query that will only execute on the master mysql server and not the slave. assuming we have an environment with replication through query logs. Could I and how?

The idea is to have some records that are archived on the slave. Lets say the master is a production website and only requires data that has been entered in the past month to operate correctly but the slave is where data from years will be saved and later analyzed to change variable on the main (production/master) server.

There is a much, much simpler way
A Replication Slave relies on the Master's binary logs so as to ship SQL from them into the Slave's local relay logs. Just tell the Master not to record the SQL
SQLSTMT="SET SQL_LOG_BIN=0;"
SQLSTMT="${SQLSTMT} DELETE FROM tablename WHERE column='our filters'"
mysql -u... -p... -Ae"${SQLSTMT}"

The key here is to disable binary logging in its session. The Slave won't know what hit the Master because the Master will execute the DELETE and not record the DELETE.
Give it a Try !!!


SSPI handshake failed with error code 0x8009030c - only stops when SSMS is closed I was getting the following error every 4 minutes on the same sql server:

  SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: ipAddress here]

All the googling I do points towards being unable to connect when running something, but that's not quite my problem...

The errors were coming from the same ip address every time, which I tracked down to one of our developers. They have permission to log onto that server, and had been successfully running queries on it earlier, through sql server management studio.

We looked at what they had running, and decided to shut down one application at a time until the errors stopped. We did Visual Studio first, and got another error soon after. Then we closed all instances of sql server management studio, and the errors stopped.

Why was SSMS repeatedly (every 3-4 minutes) trying to connect to this server, and why would it have been failing, when all queries were run with a valid login? Is there a way to prevent this happening in future?

Possibly the developer is using a 3rd-party tool that provides a plug-in for SQL Server Management Studio. The 3rd party plug-in is started with SQL Server Management Studio and is trying to connect to the SQL Server using the credentials the process was initially started with.

If the initial process owner of the 3rd-party tool is running with an un-privileged account that does not have access to he SQL Server instance the developer is connecting to, then you will possibly see multiple error messages in the ERRORLOG file and in the Windows Event Log on the target server (see Link below for examples).

If the initial process owner of the 3rd-party tool is not allowed to delegate permissions on its own object, then you might see the SSPI Handshake error.

There is a Microsoft Blog article titled Common ‘SSPI handshake failed’ errors and troubleshooting which provides you with some ideas on how to find the main cause.

Most of the SSPI Handshake erros are because a process can not handover/delegate the permissions during the SSPI Handshake to another user/process.


Which database transaction log will increase? I found a query miss written in one of our databases like this

Use database2
go

delete database1.dbo.table1
from table1 as t1
join table2 on t1.column1 = t2.column2 and t2.column3 = 1

I am having a hard time getting my head around it as it looks like it will delete EVERYTHING from database1.dbo.table1 if column3 in table1 or table2 = 1 and if the join condition is successful (Please correct me if I am wrong). It is not meant to even touch database1.

Also, if that happens, which database transaction log will get huge, database1 or database2? Query runs in Database2 but delete is happening for table in database1.

Thanks for your input.

Creating a minimal, complete, and verifiable example will help you better formulate questions in future, and allow other folks to help you more easily.

I've created one:

CREATE DATABASE db1;
CREATE DATABASE db2;
GO
USE db1;
CREATE TABLE dbo.table1 (id1 int, id2 int);
USE db2
CREATE TABLE dbo.table1 (id1 int, id2 int);
CREATE TABLE dbo.table2 (id1 int, id2 int);

INSERT INTO db1.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db1.dbo.table1(id1, id2) VALUES (3, 2);
INSERT INTO db2.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db2.dbo.table2(id1, id2) VALUES (1, 2);

DELETE FROM db1.dbo.table1
FROM dbo.table1 t1
    INNER JOIN dbo.table2 ON t1.id1 = table2.id1

Looking at the tables is instructive:

SELECT *
FROM db1.dbo.table1;

╔═════╦═════╗
║ id1 ║ id2 ║
╚═════╩═════╝

SELECT *
FROM dbo.table1;

╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝

SELECT *
FROM dbo.table2;

╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝

Cleanup:

USE master;
DROP DATABASE db2;
DROP DATABASE DB1;

The takeaway appears to be that all rows in db1.dbo.table1 are deleted, which makes sense since the FROM dbo.table1 JOIN dbo.table2 ... clause does not reference database1.dbo.table1 at all.