Find which permissions are being used for a given user on a object Is there any way in which I can see what permissions are being used to determine a users access to a given object? I'm able to see which permissions have been granted to various roles and users within a database, but am having problems with these reports not lining up with what is actually happening.

For example, the SQL below will return my effective permissions on a given object:

SELECT * FROM fn_my_permissions('dbo.SomeTable', 'OBJECT')
ORDER BY subentity_name, permission_name ;

Which returns:

entity_name         subentity_name permission_name
------------------- -------------- ---------------
dbo.SomeTable                      INSERT
dbo.SomeTable                      SELECT
dbo.SomeTable                      UPDATE

What would be very useful is something along the lines of below, which explains where each line item is coming from:

entity_name         subentity_name permission_name reason
------------------- -------------- --------------- ----------------
dbo.SomeTable                      INSERT          dbowner
dbo.SomeTable                      SELECT          role: datareader
dbo.SomeTable                      UPDATE          role: sysadmin

Note, there is a similar question on the site, however the accepted answer is a query that returns the permissions that are set, not where said permissions are coming from.

Edit: The reason for asking was that there were 'shadows' of former permissions as seen and explained here. However if I had access to something like the above, it would have shown the SQL Server thought I was a member of a given role and thus pointed me in the right direction...

Have a look at sys.user_token (http://technet.microsoft.com/en-us/library/ms188421.aspx) and sys.login_token (http://technet.microsoft.com/en-us/library/ms186740.aspx). They tell you which roles/logins/users SQL Server is involving when figuring out the permissions for the current user.

Then you can use sys.database_permissions (http://msdn.microsoft.com/en-us/library/ms188367.aspx) and sys.server_permissions (http://msdn.microsoft.com/en-us/library/ms186260.aspx) to get the permissions associated with each token. (They even tell you who granted the permission.)

Finally, sys.database_principals (http://msdn.microsoft.com/en-us/library/ms187328.aspx) and sys.server_principals (http://msdn.microsoft.com/en-us/library/ms188786.aspx) can be used to translate the principal_id into a name.

I took a stab at combining the system views above. It might not be perfect, but it should get you started:

SELECT
        T.name token_name,
        T.type token_type,
        T.usage,
        T.is_login_token,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        UDP.default_schema_name,
        UDP.create_date,
        UDP.is_fixed_role,
        --UDP.authentication_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        SP.create_date,
        --SP.is_fixed_role,
        DP.class_desc,
        DP.permission_name,
        DP.state_desc,
        QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
        O.type_desc object_type_desc,
        C.name column_name
  FROM (SELECT 0 is_login_token,* FROM sys.user_token
        UNION ALL
        SELECT 1 is_login_token,* FROM sys.login_token
       )AS T
  LEFT JOIN sys.database_principals AS UDP
    ON T.principal_id = UDP.principal_id
   AND T.is_login_token = 0
  LEFT JOIN sys.server_principals AS SP
    ON (UDP.sid = SP.sid AND T.is_login_token = 0)
    OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
  LEFT JOIN sys.database_permissions AS DP
    ON (DP.grantee_principal_id = UDP.principal_id
        AND T.is_login_token = 0)
    OR (DP.grantee_principal_id = SP.principal_id
        AND T.is_login_token = 1)
  LEFT JOIN sys.all_objects AS O
    ON DP.major_id = O.object_id
  LEFT JOIN sys.all_columns AS C
    ON DP.major_id = C.object_id
   AND DP.minor_id = C.column_id;


NON-Random UUID keys in custom distributed DB Is there any reason other than security for using random (or even semi-random) UUIDs as primary keys to database records?

Is there any reason why I should not WANT to be able to use the UUID to lookup a record? In my case I think that would actually be a FEATURE.

I'm creating a distributed / replicated database with security builtin to it at the SQL level. To access a record, the client MUST first obtain a "grant" that allows the client to access records in "partitions". Each record has an indexed int field that will be checked with every SQL operation using a clause like 'WHERE partid IN (123, 234, 345)'. This is mostly to make queries fast but it is also used to for security / access control. To get those partition ids in your security context, you must first obtain a suitable grant.

So I don't care if someone knows the key of a record. They either have explicit permission to access it or they don't. Obscuring keys by making them random seems like a completely orthogonal characteristic. No?

The part about security and grants is really not critical. I was just trying to stress that I have a security strategy at the SQL level (described more here) and thus I don't (think I) need the UUIDs to be totally random. So there is no minimal reproducible example to post. It's just a conceptual question mostly. A UUID can be unique and not be random. I'm just trying to figure out if there are unforeseen issues with using non-random UUIDs are a primary key.

Yes, you can use a sequentially generated UUID as a primary key.  SQL Server even has it built in:

CREATE TABLE dbo.keytest
(
    kt_guid uniqueidentifier NOT NULL
        CONSTRAINT keytest_pk
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT keytest_kt_guid_df
        DEFAULT (NEWSEQUENTIALID())
);

Insert some rows, and SELECT them:

INSERT INTO dbo.keytest DEFAULT VALUES;
GO 10

SELECT kt.kt_guid
FROM dbo.keytest kt

Results:

╔══════════════════════════════════════╗
║               kt_guid                ║
╠══════════════════════════════════════╣
║ D9A1905A-ADBE-E911-9440-005056805C06 ║
║ DAA1905A-ADBE-E911-9440-005056805C06 ║
║ DBA1905A-ADBE-E911-9440-005056805C06 ║
║ DCA1905A-ADBE-E911-9440-005056805C06 ║
║ DDA1905A-ADBE-E911-9440-005056805C06 ║
║ DEA1905A-ADBE-E911-9440-005056805C06 ║
║ DFA1905A-ADBE-E911-9440-005056805C06 ║
║ E0A1905A-ADBE-E911-9440-005056805C06 ║
║ E1A1905A-ADBE-E911-9440-005056805C06 ║
║ E2A1905A-ADBE-E911-9440-005056805C06 ║
╚══════════════════════════════════════╝

In fact, in situations where good insert performance is desirable, it may be more effective to use sequential GUIDs instead of randomly generated ones.  Sequential GUIDs make great keys since they are monotonically increasing, which reduces unnecessary page splits when inserting rows into the index.


What are the low-level storage requirements? Typically, DB engine fulfils some request and any modifications must stay persistent (you call it durability). How is the persistence is ensured/assumed? Which requirements are imposed on the HW/OS?

Particularly,

When does the server does the flush? Can it happen that OS flush command propagates into some intermediate buffer and does not propagate down to the HDD plates? Today, many storage manufacturers and interfaces support out-of-order write buffers. What are implications?: Should you take any action to counteract it? Does ram buffer on disk matter? Are any UPS (and which) required to support the RAM buffer?

What should I guarantee if I have my own "DB" - storing my data in plain files? I assume that DB does the best. What is that best so that I could do the same to guarantee that data is not lost? Is there a simple procedure? Are there any courses that teach the safe storage?

You didn't mention the Database platform, I can give insight on SQL Server.

SQL Server ensures durability with a concept called WAL. Write ahead logging. This means that all changes are first written to log before the are applied tot the data files.

When a row needs to be altered. The corresponding data (and possibly index) pages get fetched from disk into the buffer pool (in memory).

In memory, the page(s) get updated, the page is then marked as dirty. Done. But what happens when you have a power outage? The memory is cleared right?

Why isn't the page then written to disk immediately? Because this could give major disk contention. Imagine that you have a page with a couple of hundred rows on it. It would create a lot of disk IOs if for every row update the page is written to disk.

Therefore a different mechanism is used. A mechanism where it's no longer necessary to write data pages to disk every time a single update has taken place on that page.

Whenever a row is updated, the change along with the information to undo the change is first written to a log file. Once that is done, the client that issued the change is given an acknowledgement that the change is in fact written to disk (hardened) and that the client can continue with the next statement.

This way, when you have a power outage, after which the database server starts again, all finished (committed) changes can be read from the log file and still be applied and all unfinished or rolled back changes can be undone.

Eventually it would be nice if all those data pages that are dirty and hanging in memory are applied to the data file right? For starters you don’t want days changes in log files that need re-applying after a server restart.  Therefore the dirty data pages are occasionally flushed to the data file(s). This is called a checkpoint. The interval on which checkpoints occur is based on the time it would take to recover the database after a restart. (The time it would take to read all the changes in the log file and apply them to the data file (s).
But key to understand is that data pages and whether or not they are written to disk are not important for durability.

Back to the log file. To make sure that once SQL Server gives a log file write IO request to the OS and the OS gives back acknowledgement that the IO is handled it is in fact really hardened to disk and not hanging around in OS cache, the log file is created with the following flag: FILE_FLAG_WRITE_THROUGH. This tells the OS NOT to cache any write IOs that are going to this file.

-- update

Atomicity is achieved by the fact that your changes are all wrapped in transactions. (Either implicitly or explicitly). So when you update a record you have a minimum of 3 log records:

BEGIN TRAN
UPDATE RECORD X
COMMIT TRAN

Let’s for simplicity sake assume that these 3 records are send to disk in 3 separate IOs.
The durability rule dictates that the client can only get a acknowledgment if 1, 2 and 3 are hardened to disk.

Atomicity rules dictates that either the complete transaction is applied or none of the steps of the transaction.
So in the above example, if you have a failure after IO #2 has been hardened to disk. After a reboot the database server will have a look in the transaction log.
All transaction records that are in the transaction log and have not been applied to the data file AND have a final COMMIT TRAN record will be applied to the data file.
However, for the above transaction, the database server will not find a COMMIT TRAN record in the log file.  You could have to scenario’s now.

SQL Server looks in the data file to find out if UPDATE RECORD X has
been applied to the data file. (1 possibility could be that you had
a checkpoint right after log record 2 was generated and just before
your crash.) In this case, since the database server can’t find a
commit record, it assumes that the whole transaction most be rolled
back. It will undo the change. Since your client application never
got a acknowledgement that the transaction was committed. Your now
back in a consistent state. The application must retry the
transaction.
SQL Server looks in the data file and find out that nothing of this
transaction was every applied to the data file yet. It will just
disregard these log records.

--END Update

Most modern hardware that handles IOs have mechanisms that handle power outage. For example controller card cache is backed up with batteries, SANs have the same principles. The only think to keep in mind is that even if they do have these mechanisms, if any of your hardware allows you to continue operation even if the battery is not present or is failing that you implement proper error event monitoring and take action accordingly.

The transaction Log manager sends the IOs to the OS in guaranteed order. Once a hardware appliance tells the OS that the IO is taken care of, it in fact means that the IO from that moment on is either in battery backed cache or is in fact on disk. In other words: the IO is reproducable after a power outage.  The IO hardware appliance might write IOs to physical disk in a different order for optimization purposes. But this is no longer important for us.

IO ordering is something of concern when you implement SAN level mirroring. But that is another topic.

What do you mean with your own DB? You are going to develop your own RDBMS? At a minimum you need these 3 core concepts:

Only acknowledge to the client application that the transaction is in fact commited when either a log records is written to disk that can fully reproduce the change or the change itself is written to disk. (If you don't want to implement something like WAL)
For hardening written to files, implement FILE_FLAG_WRITE_THROUGH flag on file initialization. To make sure the windows OS doesn’t cache.
Use hardware that has battery backed cache. And monitor on battery
problems

Detailed SQL Server information can be found in the SQL Server I/O White Papers.

More information about ACID. Atomocity, Consistency, Isolation and Durability here


MySQL: Replication and local variables Can I use local variables in a trigger if I expect it to replicate on a slave ?

I have a trigger that uses a local variable @userId that is set prior to the triggering query. So every time a certain query is run the variable @userId is set and the trigger can make use of it.

My question is, can I expect the bin log to work correctly if my binlog_format is STATEMENT ?

I know from the documentation that session variables will not work, but they do not mention local variables.

  in statement-based replication, session variables are not replicated properly when used in statements that update tables.

I have a trigger that uses a local variable @userId that is set prior to the triggering query.

Local variables are variables defined on a compound-statement level with this compound statement scope using DECLARE variable statement explicitly. Their name's first symbol cannot be @.

User-defined variables are session scope variables defined implicitly (in SET or INSERT INTO statement) by the first 2 symbols of their names (first is @ and second is not).

So you use user-devined, not local, variables.

User-defined variables are ignored by the replication at all.

  Can I use local variables in a trigger if I expect it to replicate on a slave?

The answer is "No". Use custom service table.

  I know from the documentation that session variables will not work, but they do not mention local variables.

Session variables are system variables duplicates with a session scope. They (when exists) override system variables values. They are not local or user-defined, they are a separate type of variables. As for user-defined variables their first two symbols are @ and not @ - it shows that their values may be set by user with a session scope (like user-defined variables).


Query for which my.ini in use Is it possible to query mysql server to know which configuration file is in use? Sometimes, my.cnf, my.ini.

SHOW VARIABLES; -- does not have enough information here for my.ini path

It is not possible to do that, because once the config is loaded on startup, it is no longer used.

MySQL uses a deterministic algorithm for searching for a config file. You can see it on the MySQL Manual (the first table is for Windows).

Two things you can do is: check which options are loaded by default: use --print-defaults:

mysqld --print-defaults
mysqld would have been started with the following arguments:
--log_bin

(in this case, I have log_bin in my configuration file).

Force manually a configuration file to be loaded with the parameter --defaults-file:

mysqld --defaults-file=C:\my\path\to\my.ini


Model for Distributed Database I want to design a model for a distributed database solution.

Are there appropriate tools and approaches?
How to include stereotypes for (horizontal) fragments?

If you are looking for data models to start with, I'd recommend you take a look at: http://www.databaseanswers.org/data_models/index.htm

This is the most comprehensive list of data model examples that I found so far. In fact I have used some samples as starting point for some of my projects. These models won't show you how to take horizontal fragmentation into consideration, though.

Update:

You may consider these basic techniques for horizontal fragmentation*:

Range Partitioning: Each fragment is assigned a different value range
Value List: For each fragment a list of attribute values is given
Hash Values: Applying a hash function to tuples, each fragment is
responsible for a given hash range

*Source - Distributed Database Systems: Fragmentation and Allocation by Katja Hose, Ralf Schenkel


Streaming Replication Log is puzzling me I have a streaming replication hot-standby setup, which seems to be running all good. I can login, execute selects and the result seems to be up-to-date with the master. However in the log of the standby, I see a lot of these:

cp: cannot stat `/mnt/wal_drive/wals/0000000100004BA800000070': No such file or directory
2014-02-28 16:00:37 CET LOG:  record with zero length at 4BA8/70DD79D0
2014-02-28 16:00:37 CET LOG:  started streaming WAL from primary at 4BA8/70000000 on timeline 1
2014-02-28 16:02:59 CET FATAL:  could not receive data from WAL stream: SSL error: sslv3 alert unexpected message

What does each of these mean, and should I worry about any of them?

PS. In the master I see some of these, which I expect to be related to the SSL thing:

2014-02-28 16:10:33 CET LOG:  SSL renegotiation failure
2014-02-28 16:10:33 CET LOG:  SSL error: unexpected record

I'm running postgresql 9.3 on ubuntu 12.04

EDIT

Regarding the WAL logs I have the following setup:

On both my master and my slave I have mounted a network storage in fstab using a cifs command. The directory is chowned to be owned by the postgres user.

On my master I have the following settings in postgresql.conf:

archive_mode = on
archive_command = 'rsync -a %p /mnt/wal_drive/wals/%f </dev/null'

On the slave I have hot_standby = on and the following recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=localhost port=5433 user=replicator password=some_pass'
trigger_file = '/tmp/pgsql.trigger'
restore_command = 'cp -f /mnt/wal_drive/wals/%f %p </dev/null'
archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup /mnt/wal_drive/wals/ %r'

I have an ssh-tunnel forwarding port 5433 of the slave to port 5432 on the master, so connecting on localhost:5433 is actually the master db.

You appear to have two separate problems an issue with OpenSSL renegotiation.

First - nope, I was wrong, see Daniel's answer.

Second, you're being bitten by an OpenSSL issue with renegotiations. Searching for the error message would've told you this. As noted in that bug report:

  Workaround is to set 'ssl_renegotiation_limit=0' in postgresql.conf

which asks PostgreSQL to tell OpenSSL to stop attempting renegotiations, since that version of OpenSSL has broken renegotiation support.


Database design for an exam system I am working on creating a simple exam system, in which you are going to be basically able to store students, classes, exams, questions and scores(and some other stuff that those depend on). I am also using Entity Framework code-first.
I am using inheritance for the Student and Instructor classes, which derive from the Person class. In SQL that it represented by a single People table containing a Discriminator column, as well as columns, representing each of the fields of both classes.
Here is a picture of my database diagram:

It is my first time designing a database this complicated and am really not sure if I have done everything right (specifically worried about circular references). I would really appreciate it if you could take a quick look and tell me if you see anything wrong.

Thanks a lot for the answer, mgw854.
The inconsistent naming is because of Entity Framework. I am using code first so it names the columns that it auto-generates(the foreign keys) as it pleases. If you could tell me a how to fix it, I will.
About the Student to Class relationship, it is kinda weird why EF generated it that way. It is probably because I forgot to include a collection of students inside the Class class :D
Also yes, now that I think about it, it definitely would be better if I added a Value column to each question which indicates how many points it gives you and then just use that to calculate the score on runtime.
Thanks for the help. I will fix those ASAP.
PS: I am not going to mark the question as answered, because I want to see some more suggestions, if that's ok with you :)

So I have made the changes and updated the diagram image. Looking for a way to make EF generate column names without underscores without having to write each name manually.

Just found how to fix the naming conventions in a nice way (EF custom naming convention).
Diagram updated again.

At first glance, I see lots of inconsistent naming--Person_ID, but PasswordHash (or worse, SchoolYear_ID). Pick a naming convention, and stick with it. I promise you that your future self with thank you!

On your Classes table, you have a Student_ID column. As far as school goes, many students take one class, so class should have a 1:n relationship with students, not a 1:1 relationship. I'd make a Class_Registration table that contains a class id and a student id.

Also, unless your score can be different that what it ought to be based on your performance (say, a professor giving you a boost), you shouldn't have a StudentScores table. That should be derived from the number of questions you answered correctly.

I don't see any worrisome circular references, so I think you're good on that front.


Is there a way to ensure that a SQL Server trigger will be executed? My supervisor is hesitant to use a trigger in a process because if there is a network interruption at just the right time, the process would not complete.  Does SQL Server include the trigger in the calling procedure's transaction?  If not, what is the best way to implement this?

Yes.  Triggers occur as an implicit nested transaction within the scope of the caller's transaction.  The trigger will commit or roll back as part of this transaction.

See:

Nesting Transactions
Rollbacks and Commits in Stored Procedures and Triggers
ROLLBACK TRANSACTION (Transact-SQL)


How can I append a column to an inner SQL statement? I have two tables that have the same structure except one has an additional column that should be auto-incremented.  Sequence and trigger are defined.

I want to copy all data from one table to another using this SQL statement:

INSERT INTO A_WITH_ADDITIONAL_COLUMN
SELECT * FROM A;

But I'm getting error:

SQL Error: ORA-00947: not enough values

Which is fine because there is one more column in the target table.

How do I append an additional NULL at the beginning of every row of the source table in order to be able to fill the target WITHOUT enumerating all columns of the source table in the query?

INSERT INTO A_WITH_ADDITIONAL_COLUMN (newcol, col1, col2, col3,...)
    SELECT NULL, A.col1, A.col2,a.col3,..... FROM A;

Should do what you're asking.


Is it possible to generate DB diagram for all SQL databases opened in SSMS At the present we have to go to each database and manually generate database diagrams, so I want a way to automate this.

Question: Is it possible to generate DB diagram for all SQL databases opened in SSMS ?

No it is not possible

You have to go into each database and create database diagrams there


Query with two LEFT JOIN's is not working as expected I am using PostgreSQL as my database and I need to write the query that returns:

a user_id from 1st table
sum of every row's amount from 2nd table (where user_id is the same as in the 1st table)
sum of every row's amount from 3nd table (where user_id is the same as in the 1st table and the type is defined).

So I wrote this query with two LEFT JOINs, but it doesn't work as expected:

SELECT aff.user_id, COALESCE(SUM(t.amount), 0) as total_bet, COALESCE(SUM(d.amount), 0) as total_deposit
FROM affiliate_guests aff
LEFT JOIN transfers t ON aff.user_id = t.user_id
LEFT JOIN deposits d ON aff.user_id = d.user_id AND d.type = 'deposit'
WHERE aff.code = 'code'
GROUP BY aff.user_id;

This query returns the wrong numbers for total_bet and total_deposit columns.

If I omit the second LEFT JOIN, like this:

SELECT aff.user_id, COALESCE(SUM(t.amount), 0) as total_bet
FROM affiliate_guests aff
LEFT JOIN transfers t ON aff.user_id = t.user_id
WHERE aff.code = 'code'
GROUP BY aff.user_id;

everything works fine.

What can be the problem and how can I solve it?

Let's assume your tables are defined:

CREATE TABLE affiliate_guests
(
    user_id serial not null primary key,
    code text
    -- etc.
) ;

CREATE TABLE transfers
(
    transfer_id serial not null primary key,
    user_id integer not null references affiliate_guests(user_id),
    amount numeric not null
    -- etc.
) ;

CREATE TABLE deposits
(
    deposit_id serial not null primary key,
    user_id integer not null references affiliate_guests(user_id),
    amount numeric not null,
    type text
    -- etc.
) ;

... and we populate them with some sample data:

INSERT INTO
    affiliate_guests (user_id, code)
VALUES
    (1, 'code') ;
INSERT INTO
    transfers (user_id, amount)
VALUES
    (1, 1000.00),
    (1, 2000.00),
    (1, 1234.00) ;

INSERT INTO deposits (user_id, amount, type)
VALUES
    (1, 1000.00, 'deposit'),
    (1, 2345.00, 'ignoreit'),
    (1, 3456.00, 'ignoreit as well'),
    (1, 9999.00, 'deposit') ;

In order to understand why your query is not working as you expect, check first the result of executing your same query, but without GROUP BY, and changing your aggregates by just values (I've also added some extra ids for ease of interpretation):

SELECT
    aff.user_id, transfer_id, t.amount AS t_amount, deposit_id, d.amount AS d_amount
FROM
    affiliate_guests aff
    LEFT JOIN transfers t ON aff.user_id = t.user_id
    LEFT JOIN deposits d ON aff.user_id = d.user_id AND d.type = 'deposit'
WHERE
    aff.code = 'code' ;

This is what you'll get:

|---------+-------------+----------+------------+----------|
| user_id | transfer_id | t_amount | deposit_id | d_amount |
|---------+-------------+----------+------------+----------|
|    1    |      1      | 1000.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      1      | 1000.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|
|    1    |      2      | 2000.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      2      | 2000.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|
|    1    |      3      | 1234.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      3      | 1234.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|

As there are not any relationship between your tables t and d, what you actually get is the cartesian product of both of them. You clearly see it because you have all possible combinations of transfer_id and deposit_id.

You need to make this by using subquerys, instead:

SELECT
    aff.user_id,
    coalesce( (SELECT sum(amount)
                 FROM transfers t
                WHERE t.user_id = aff.user_id), 0) AS total_bet,
    coalesce( (SELECT sum(amount)
                 FROM deposits d
                WHERE d.user_id = aff.user_id AND d.type='deposit'), 0) AS total_deposit
FROM
    affiliate_guests aff
WHERE
    aff.code = 'code' ;

That will get you what you expect:

|---------+-----------+-----------|
| user_id | total_bet | total_bet |
|---------+-----------+-----------|
|    1    |  4234.00  | 10999.00  |
|---------+-----------+-----------|

See Identifying and Eliminating the Dreaded Cartesian Product


Cassandra query response time degradation I am experimenting with Cassandra on 4 sites with 2 nodes on each one. Have small network latency between them. I always query the data in DC where I inserted it. I see very strange behavior: when I increase the nodes number the query response time slowly increasing, but insert time is improving. I am expecting that query time should improve as well. Any suggestion regarding configurations check?

Response time heavily dependent on the query that you're executing & how you're executing them. Linear scalability is achieved only if you're performing reads by primary or at least partition key. If you're doing something like SELECT * FROM table WHERE non_pk_column = 'something' ALLOW FILTERING or SELECT * FROM table, then Cassandra will need to get data from all your nodes, and as you increase the number of nodes, the latency is increasing.

Also, when executing query you need to make sure that you're using token aware load balancing policy that will send query request to the coordinator node that has one of the replicas.

But it's a really broad question, please provide more information about your queries, etc.


Teradata SQL Assistant - List tables under DBC in the Database Explorer I am able to query, but under DBC, in the Database Explorer, I do not see the list of tables. That also makes it impossible for me to qualify name and get any kind of ide support while writing my queries.

By default, Teradata SQL Assistant will attempt to query the views DBC.Databses, DBC.Tables, DBC.Columns to populate the Database Explorer. It is possible in your environment that those objects are not accessible to developers or end users via the PUBLIC user. Instead, you may need to modify your connection settings ODBC or .Net Provider to use the X-Views in DBC. These are a collection of views which restrict the rows returned based on the privileges your user account has been granted to access or which you have created.

The ODBC DSN, .Net Provider, and JDBC drivers for Teradata have a means to use the X-Views by default to enable database tools such as SQL Assistant or Teradata Studio/Studio Express to populate the database explorer controls "transparently". Try this and see if it works.


Dynamically convert hstore keys into columns for an unknown set of keys I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns.

Users can add new custom fields and so I can't rely on knowledge of the keys ahead of time. Which makes the answer at "Attributes from an hstore-column as separate columns in a view?" not applicable for my problem.

Where a record doesn't have a key present in other records, it should get the same column with a null value.

How do I do this?

This can be done, very efficiently, too. Not in a single statement, though, since SQL demands to know the return type at call time. So you need two steps. The solution involves a number of advanced techniques ...

Assuming the same table as @Denver in his answer:

CREATE TABLE hstore_test (
  id serial PRIMARY KEY
, hstore_col hstore
);

Solution 1: Simple SELECT

After I wrote the crosstab solution below it struck me that a simple "brute force" solution is probably faster. Basically, the query @Denver already posted, built dynamically:

Step 1a: Generate query

SELECT format(
     'SELECT id, h->%s
      FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;'
    , string_agg(quote_literal(key) || ' AS ' || quote_ident(key), ', h->')
   ) AS sql
FROM  (
   SELECT DISTINCT key
   FROM   hstore_test, skeys(hstore_col) key
   ORDER  BY 1
   ) sub;

The subquery (SELECT id, hstore_col AS h FROM hstore_test) is just to get in the column alias h for your hstore column.

Step 1b: Execute query

This generates a query of the form:

SELECT id, h->'key1' AS key1, h->'key2' AS key2, h->'key3' AS key3
FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;

Result:

 id | key1  | key2  | key3
----+-------+-------+-------
  1 | val11 | val12 | val13
  2 | val21 | val22 |
  3 |       |       |        -- for a row where hstore_col IS NULL

Solution 2: crosstab()

For lots of keys this may perform better. Probably not. You'll have to test. Result is the same as for solution 1.

You need the additional extension tablefunc which provides the crosstab() function. Read this first if you are not familiar:

PostgreSQL Crosstab Query

Step 2a: Generate query

SELECT format(
   $s$SELECT * FROM crosstab(
     $$SELECT h.id, kv.*
       FROM   hstore_test h, each(hstore_col) kv
       ORDER  BY 1, 2$$
   , $$SELECT unnest(%L::text[])$$
   ) AS t(id int, %s text);
   $s$
 , array_agg(key)  -- escapes strings automatically
 , string_agg(quote_ident(key), ' text, ')  -- needs escaping!
   ) AS sql
FROM  (
   SELECT DISTINCT key
   FROM   hstore_test, skeys(hstore_col) key
   ORDER  BY 1
   ) sub;

Note the nested levels of dollar-quoting.

I use this explicit form in the main query instead of the short CROSS JOIN in the auxiliary query to preserve rows with empty or NULL hstore values:

LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE

What is the difference between LATERAL and a subquery in PostgreSQL?

Related:

Dynamic alternative to pivot with CASE and GROUP BY

Step 2b: Execute query

This generates a query of the form:

SELECT * FROM crosstab(
     $$SELECT h.id, kv.*
       FROM   hstore_test h
       LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE
       ORDER  BY 1, 2$$
   , $$SELECT unnest('{key1,key2,key3}'::text[])$$
   ) AS t(id int, key1 text, key2 text, key3 text);

You may want to inspect it for plausibility before running the first time. This should deliver optimized performance.

Notes

Both solutions work for any number of keys up to the physical limit of ~ 1600 columns in a Postgres table.
Both also work for keys of any shape or form up to the maximum length for identifiers, which is 63 bytes per default.
Besides the hstore function each() that was already mentioned by s.m., I also use the related function skeys() to identify keys.
Be sure to quote column names correctly to avoid possible SQL injection attacks by way of maliciously formed key names. I take care of that with quote_literal() and quote_ident().


performance of multi field primary key or 'contrived' 'semi artificial' key This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can easily be found and decisions made by whoever would like to search for them.

This question is more about performance of the keys (or lack thereof)

I work as a DB manager, and when I create my tables I attempt to use a 'natural' key for the table. Often times this comes out as being a set of 2,3, or sometimes 4 fields that act as the primary key for the given table. More often than not these fields are Varchar in nature, but short (10 or 15 characters in length at most). Personally I try to keep them shorter!

My question is this.

Imagine I have a table that holds demographic data.
The only way I can ensure that I have uniqueness in each row is to use the fields for
FirstName FamilyName DateOfBirth PlaceOfBirth

(You may wonder why I include 'place of birth', I am aware of another individual (who used to live nearby - same phone number, different dialing code) with whom I shared all these details (I assume that the PlaceOfBirth was different, but I guess I could have used MothersMaidenName ;) )

so now I have an interesting problem.

I could use a much shorter field that is created from concatenating the info in the 4 main fields
example: DateOfBirth first 2 characters of FirstName first 2 characters of FamilyName first 2 character of PlaceOfBirth

My question is this.

At what point would the concatenation of field provide a performance improvement over using the fields directly, ie how many columns.

I know from searching that most DBMS's have a 'thoretical maximum size limit' dependent on the B-Tree that is created. I am assuming that I don't hit this limit in terms of the length/size of the primary key.

My reason for considering using this type of 'contrived' key is:
The information in the concatenated column is most likely sufficient to be able to identify the record without the need to extract all the primary key fields (would this be better for performance or no different compared to using all the 4 primary key fields?)

This is obviously a fairly 'theoretical' question, but I have considered doing this concatenation on a table that end up with 4 varchar fields, and it was obvious that uniqueness would be described by using just a shortened version. Obviously there is an effort to create this field in the first place, but in others opions would this effort be worthwhile, and at what point would it become more interesting.

I have searched for this but I have never found this question asked directly, it allays comes out as a 'natural' or 'artificial' primary key discussion.

Of course if this feels like a 'natural' or 'artificial' key discussion feel free to say so. My feeling is that this 'contrived' key would proffer the advantages of a both. Has anyone used this idea in a real world solution?

Thanks in advance for your thoughts.

David

Edit.
I just found this thread

https://stackoverflow.com/questions/3735390/best-primary-key-for-storing-urls

It seems to cover similar ground, I must admit I hadn't thought of 'hashing' my columns together (mainly because they are short by nature), but I do like the idea. I guess you could do that and hash the whole row !

Edit2.

I've returned to this question just to see if there has been any change to the answers, or extra comments. I've decided to accept a response, but would like to note that I have found all the responses helpfull in the terms of the discussion.

I'll answer obliquely...

The natural key is always the natural key and should be enforced with a unique constraint or index. This is the "primary key" that flows from your modelling phase.

The choice of an auto-number/identity surrogate key matters at implementation phase because there are good and bad choices for your clustered index (example: SQL Server, Sybase, MySQL InnoDB, Oracle IOT).

That is, primary key is orthogonal to your clustered index: don't confuse the two issues

I'd suggest using a contrived key adds no value over using an auto-number/identity column in this respect. You lose data from the natural key, probably won't be unique, is just as opaque.

FWIW, I use surrogate keys and composite keys when I need too:

Some natural keys are useful in their own right: ISO currency and country codes
A table with no secondary (non-clustered) indexes and no child table doesn't benefit from a surrogate key
If you have parent-child-grandchild, then I usually need to join parent-grandchild: with composite keys I can do so directly. Simpler JOINs, simpler indexes

Note: this assumes that every table requires a clustered index

Related on dba.se: SQL Server Primary key / clustered index design decision


Should I drop a foreign key if I use a soft delete? "ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have suffered deadlock issue when application is under high concurrent workload.

BTW: My application also validates existence of parent resource if child resource has a reference(foreign key) to it when creating child resource

Keep it.  A foreign key exists primarily to ensure that the value given in the child table really does exist in the parent (foreign) table.  Cascading deletes or updates are, generally, a distant second.

That's the general case; your specific business rules may be different.


Will creating partitions reduce locking and how do we implement this in sql-server? A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.

This application generates quite a lot of data that is used for reporting. Before each run the old data for that item is deleted, the calculations are performed and then the new data inserted. In busy periods the users queue up hundreds of these generation tasks and we run upto 30 of them concurrently. Each run might create 60K rows.

The dba has suggested we could change the application to use 30 partitions (eg. one per thread) to reduce locking between threads during insert and delete. They suggested that in standard sql we could do something like

INSERT INTO schema.table.partition (...) VALUES (...)

I do not see this syntax in the msdn docs and this will mean changing this application which is a pain but is it even possible to do this? As I understand we would instead partition based on columns of the tables using partition functions?

I've read the create partition function docs but am not completely sure how to create a function to meet our needs. To make matters worse I don't yet have enterprise edition to try this out on so my apologies for incorrect syntax.

I am thinking that for example if we have an items table and an itemdata table with data for that item we might partition itemdata table by splitting the data based on a function like itemid mod 30. This would put item 1 in partition 1, item 2 in partition 2, etc. I'm not sure if we could do this in the partition function, in the scheme, table declaration or would we need to create a calculated column and use a values clause? Also not sure if we are going to see any performance improvement?

This is how I think we could implement this:

CREATE PARTITION FUNCTION SplittingItemIds_PFunc(decimal(18,0)) AS
RANGE LEFT FOR VALUES
(0,1,2,3, ... ,29)

CREATE PARTITION SCHEME SplittingItemIds_Scheme
AS PARTITION SplittingItemIds_PFunc
ALL TO ([PRIMARY]);

CREATE TABLE ItemData
(
    Id decimal(18,0),
    ItemId decimal(18,0),
    ...
)
ON PartitionSplittingItemIds_Scheme(ItemId % 30)

CREATE INDEX ItemData_ItemId_Idx ON ItemData(ItemId);

Is this kindof right?
From what I've read the index will be automatically partitioned - is that correct?

Software can't fix this if the underlying IO/disks are opaque to you.

If you add partitions, you won't spawn 30 threads
If all your partitions are on the same volume you'll decrease throughput

I've worked on similar systems where we had

a staging DB
SIMPLE recovery on staging
deferred deletes (eg a new or updated run is an INSERT) that run out of hours
each run has a header row to track this status
flush to real DB when client is ready

We've also

removed FKs and other tweaks to the main table
put the staging DB onto separate volumes.


MS SQL Server sys.indexes vs sys.sysindexes system table difference For indexes there are 2 system tables: sys.indexes and sys.sysindexes.
I'd like to know what are their differences. Both contain information about indexes then why they are separate?

Thanks in advance.

sys.sysindexes - is old and deprecated.

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead.

you should use sys.indexes.


How can we handle out-of-range errors when aggregating data? For example we have some data in int8 fields and want to build aggregations: e.g. the sum of squares. The result should also be int8 (not a float).

So obviously calculating the sum of squares for int8-values can overflow the int8 range and we could get bigint out of range errors.

How can we handle that case?

e.g. is there a way to "catch" this error and return a default (e.g. max-int8)?
or other ideas, how to avoid this

Example:

create table test
( val int8);

insert into test VALUES (9223372036854775807), (9223372036854775807);

This query will fail with bigint out of range:

select sum(val*val) from test;

To make the sum work, we could cast to numeric:

select sum(val::numeric*val) from test;

but our application needs a result of type int8, so we could use least and greatest

select greatest(+9223372036854775807, least(-9223372036854775808, sum(val::numeric*val))) from test;

Is there some easy built-in way to "cast"/"convert"/"clamp" the numeric back to the int8 range?

link to sqlfiddle example

If you create a user function (or 2), some parts of your 'problem' can be solved:

DROP FUNCTION IF EXISTS clamp(val numeric);
CREATE FUNCTION clamp(val numeric) RETURNS bigint AS $$
BEGIN
RETURN CASE WHEN val>+9223372036854775807 THEN +9223372036854775807 WHEN val<-9223372036854775808 THEN -9223372036854775808 ELSE val::bigint END;
END; $$
LANGUAGE PLPGSQL;

DROP FUNCTION IF EXISTS clamp(val double precision);
CREATE FUNCTION clamp(val double precision) RETURNS bigint AS $$
BEGIN
RETURN CASE WHEN val>+9223372036854775807 THEN +9223372036854775807 WHEN val<-9223372036854775808 THEN -9223372036854775808 ELSE val::bigint END;
END; $$
LANGUAGE PLPGSQL;

After this, you can do:

select clamp(9223372036854775807 * 9223372036854775807::float);

This will return the bigint 9223372036854775807, which is 'Infinity'


Software for backing up SQL Server databases Since using the new version of Symantec, I've experienced lots of problems doing incremental backups of clients' SQL databases.

Is Windows Server backup a good alternative to Symantec Backup Exec 2012 for backing up SQL databases?

In my opinion, the best product for performing SQL backups is SQL Server itself.  When space permits, I backup to local disk (full + differential, tx log backups every 10 minutes).  These files can then be backed up by your third party software (to tape, NAS, etc.).

If I need to do an emergency restore (assuming a non-drive related issue), I already have the files local to the server, no need to mess around with restoring from tape or copying across a network.

One client of mine had used a third party software to backup files directly to tape (a proprietary format).  Two years later they needed to do a restore (audit).  They had a great deal of difficulty performing the restore because in the meantime they had upgraded the OS and the backup software, which no longer was compatible with the tapes they had from before.


Efficient query to get greatest value per group from big table Given the table:

    Column    |            Type
 id           | integer
 latitude     | numeric(9,6)
 longitude    | numeric(9,6)
 speed        | integer
 equipment_id | integer
 created_at   | timestamp without time zone
Indexes:
    "geoposition_records_pkey" PRIMARY KEY, btree (id)

The table has 20 million records that is not, relatively speaking, a large number. But it makes sequential scans slow.

How can I get the last record (max(created_at))  of each equipment_id?

I've tried both the following queries, with several variants that I've read through many answers of this topic:

select max(created_at),equipment_id from geoposition_records group by equipment_id;

select distinct on (equipment_id) equipment_id,created_at
  from geoposition_records order by equipment_id, created_at desc;

I have also tried creating btree indexes for equipment_id,created_at but Postgres finds that using a seqscan is faster. Forcing enable_seqscan = off is of no use either since reading the index is as slow as the seq scan, probably worse.

The query must run periodically returning always the last.

Using Postgres 9.3.

Explain/analyze (with 1.7 million records):

set enable_seqscan=true;
explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id;
"HashAggregate  (cost=47803.77..47804.34 rows=57 width=12) (actual time=1935.536..1935.556 rows=58 loops=1)"
"  ->  Seq Scan on geoposition_records  (cost=0.00..39544.51 rows=1651851 width=12) (actual time=0.029..494.296 rows=1651851 loops=1)"
"Total runtime: 1935.632 ms"

set enable_seqscan=false;
explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id;
"GroupAggregate  (cost=0.00..2995933.57 rows=57 width=12) (actual time=222.034..11305.073 rows=58 loops=1)"
"  ->  Index Scan using geoposition_records_equipment_id_created_at_idx on geoposition_records  (cost=0.00..2987673.75 rows=1651851 width=12) (actual time=0.062..10248.703 rows=1651851 loops=1)"
"Total runtime: 11305.161 ms"

Index
A plain multicolumn B-tree index should work after all:
CREATE INDEX foo_idx
ON geoposition_records (equipment_id, created_at DESC NULLS LAST);

Why DESC NULLS LAST?

Unused index in range of dates query

It's safe to assume you have an equipment table? Then performance won't be a problem:
Correlated subquery
Based on this equipment table, run a lowly correlated subquery to great effect:
SELECT equipment_id
     ,(SELECT created_at
       FROM   geoposition_records
       WHERE  equipment_id = eq.equipment_id
       ORDER  BY created_at DESC NULLS LAST
       LIMIT  1) AS latest
FROM   equipment eq;

For a small number of rows in the equipment table (57 judging from your EXPLAIN ANALYZE output), that's very fast.
LATERAL join in Postgres 9.3+
SELECT eq.equipment_id, r.latest
FROM   equipment eq
LEFT   JOIN LATERAL (
   SELECT created_at
   FROM   geoposition_records
   WHERE  equipment_id = eq.equipment_id
   ORDER  BY created_at DESC NULLS LAST
   LIMIT  1
   ) r(latest) ON true;

Detailed explanation:

Optimize GROUP BY query to retrieve latest record per user

Performance similar to the correlated subquery.
Function
If you can't talk sense into the query planner (which shouldn't occur), a function looping through the equipment table is certain to do the trick. Looking up one equipment_id at a time uses the index.
CREATE OR REPLACE FUNCTION f_latest_equip()
  RETURNS TABLE (equipment_id int, latest timestamp)
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN
   FOR equipment_id IN
      SELECT e.equipment_id FROM equipment e ORDER BY 1
   LOOP
      SELECT g.created_at
      FROM   geoposition_records g
      WHERE  g.equipment_id = f_latest_equip.equipment_id
                           -- prepend function name to disambiguate
      ORDER  BY g.created_at DESC NULLS LAST
      LIMIT  1
      INTO   latest;

      RETURN NEXT;
   END LOOP;
END
$func$;

Makes for a nice call, too:
SELECT * FROM f_latest_equip();

Performance comparison:
db<>fiddle here
OLD sqlfiddle


Database schema restores by Sql job I'm planing to do a database backup and restore by using SQL Job. My database size is to large and I want to backup and restore my schema only.

By using this code I can set up SQL Job. Can anyone help me to backup and restore my schema only?

BACKUP DATABASE [pubs]
TO DISK = N'c:\temp\backups\pubs_full.bak'
WITH INIT, SKIP, CHECKSUM
GO

RESTORE DATABASE pubs_RestoreTest
FROM DISK = N'c:\temp\backups\pubs_full.bak'
WITH MOVE 'pubs' TO 'c:\temp\backups\pubs_RestoreTest.mdf',
MOVE 'pubs_log' TO 'C:\temp\backups\pubs_RestoreTest_log.ldf'

SQL Server contains no facility to only backup the schema of a database.  Backup-and-restore can only be used to restore the entire contents of a database or, in the case of piecemeal restore, individual filegroups of a database, assuming there are multiple filegroups.

However, there are multiple tools available that can be used to export and synchronize schemas between versions of databases.

Just a couple of options, listed alphabetically:

ApexSQL SQL Diff
dbForge Schema Compare
Idera SQL Comparison Toolset
SQL Server Data Tools - MSBuild Support for schema comparison


Get the Record where only Two Records exists in SQL I want to get the parentID where user1 and user2 are using and only the two of them are using.
for example,
ParentTbl
| ID   |           Remarks              |
|------|--------------------------------|
| 1    | only one use this              |
| 2    | two user, _this is the ouput_  |
| 3    | three users use this           |

UserTbl
| ID | UserID | ParentID   |
|----|--------|------------|
| 1  |    1   |      1     |
|_2_ | ___1___|   ___2___  |
|_3_ | ___2___|   ___2___  |
| 4  |    1   |      3     |
| 5  |    2   |      3     |
| 6  |    3   |      3     |

It should be Parent 2
I already have :
select ParentID from UserTbl
where UserID IN (1,2)
group by ParentID
having COUNT(*) = 2

But the output is wrong.

If I understand you correctly, what you want is
SELECT ParentID
FROM   UserTbl
GROUP BY ParentID
HAVING SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2
AND    COUNT(*) = 2

The first condition SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2 is to check User 1 and 2 are in
The second condition COUNT(*) = 2 users count = 2 (only user 1 and 2)


Does updating a row with the same value actually update the row? I have a performance-related question. Let's say I have a user with first name Michael. Take the following query:

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123

Will the query actually execute the update, even though it is being updated to the same value? If so, how do I prevent it from happening?

Due to the MVCC model of Postgres, and according to the rules of SQL, an UPDATE writes a new row version for every row that is not excluded in the WHERE clause.
This does have a more or less substantial impact on performance, directly and indirectly. "Empty updates" have the same cost per row as any other update. They fire triggers (if present) like any other update, they have to be WAL-logged and they produce dead rows bloating the table and causing more work for VACUUM later like any other update.
Index entries and TOASTed columns where none of the involved columns are changed can stay the same, but that is true for any updated row. Related:

PostgreSQL Initial Database Size
Redundant data in update statements

It's almost always a good idea to exclude such empty updates (when there is an actual chance it may happen). You did not provide a table definition in your question. We have to assume first_name can be NULL (which wouldn't be surprising for a "first name"), hence the query has to use NULL-safe comparison:
UPDATE users
SET    first_name = 'Michael'
WHERE  id = 123
AND    first_name IS DISTINCT FROM 'Michael';

If first_name IS NULL before the update, a test with just first_name <> 'Michael' would evaluate to NULL and as such exclude the row from the update. Sneaky error. If the column is defined NOT NULL, use the simple equality check, though, that's a bit cheaper.
Related:

How do I (or can I) SELECT DISTINCT on multiple columns?
Update column with data from another table


Data and index file compatibility between patch/revision releases of MySQL Let's say a server is on MySQL 5.6.1234 (yes I know that number's made up) and gets updated to 5.6.1235 (so an increment in revision / patch number).  How safe would it be roll back MySQL to 5.6.1234 - would the data / index / log / table definition / etc. files still work on the prior release?

The scenario I'm envisioning is a backwards compatible change is introduced and we need to buy time to fix all the websites affected by it.

This happened before with MySQL 5.0.x on CentOS 5 due to  some change which MySQL didn't even officially label as a backwards incompatibility in the changelog. In MySQL 5.0.84 they changed max_allowed_packet from being able to be set as a SESSION variable but it having no effect to being readonly and we got a nice surprise when Red Hat bumped MySQL up from 5.0.77 to 5.0.95 (see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet vs. the "oh by the way" at the end of http://dev.mysql.com/doc/relnotes/mysql/5.0/en/news-5-0-84.html).  That case was easy enough to fix, but I'm trying to plan for a more difficult situation and want to know my options.

As long as you are dealing with two GA releases in the same series (5.6.y to 5.6.x), you should be okay, because that is not an unsupported operation.

  To downgrade between General Availability (GA) status versions within the same release series, typically you just install the new binaries on top of the old ones and do not make any changes to the databases.

  https://dev.mysql.com/doc/refman/5.6/en/downgrading.html

Or, better (I think) than just copying the new (old version) binaries over the old (new version), use the mechanism I use, where /usr/local/mysql is a symlink to the directory where the live version binaries are extracted, and data, inside that directory, is a symlink to the datadir.

This is a perfectly valid configuration and it allows both sets of binaries coexist on the same machine.  I use the "Linux generic" binary tarball, which extracts the files into a directory named after the release and OS... as a DBA, I don't let the distro's package manager anywhere near my MySQL.  Nobody decides when we upgrade or what version we run, other than me, so upgrades occur when planned and after testing.

With this setup, it's a quick operation to stop the service, move one symlink, and start the service, and I'm running the new version... and similarly quick to switch it back, if you have to.  I still do it exactly this way, though I am a lot less edgy about the possibility of needing to roll back.

You also need to run mysql_upgrade to finalize the upgrade process, but you don't typically have to do it immediately, during a GA to GA release upgrade.  You can do it after the server is back up, and, in fact, the server has to be running before you can do that part.

Of course, during any upgrade, it's critical to have a mysqldump of your data ready to reload in an emergency... but within the same release series, with GA versions, you should be able to pull this off... but also, you shouldn't need to.  The biggest jump I recall  doing in one shot was 5.1.34 to 5.1.69, with no ill effects, and of the dozens of machines I manage, I don't recall a 5.5 or 5.6 upgrade that I had any reason to roll back.

Another suggestion for before you upgrade is to sweep the entire server for cobwebs with mysqlcheck --check-upgrade --all-databases which confirms that the tables are fully structurally compatible with the current version you're running... and then mysqlcheck --optimize --all-databases.  The latter is recommended (by me) not because of what it does, but because it's likely to find sketchy structural issues that could cause problems that appear to be upgrade-related, but were in fact latent corruption just waiting to surface.  Optimize is time-consuming and will lock each table while running, then unlock it and move to the next, so it's potentially disruptive, but with InnoDB, it also defragments each table, so you might reclaim some disk space in the process.  Skip the optimize if you can't tolerate the potentially long locks it needs while it's running.


Can we use FILESTREAM in case of SQL Server cluster I saw this point on Technet article about RBS.

  The local FILESTREAM provider is supported only when it is used on
  local hard disk drives or an attached Internet Small Computer System
  Interface (iSCSI) device. You cannot use the local RBS FILESTREAM
  provider on remote storage devices such as network attached storage
  (NAS).

Does it mean we cannot use FILESTREAM in case of SQL Failover Cluster because Failover Cluster only works on NAS? But the thing is NAS is made available locally when setting Failover Cluster so may be FILESTREAM will work but I am not sure about it.

Yes, you can.  See: Set Up FILESTREAM on a Failover Cluster, https://msdn.microsoft.com/en-us/library/cc645886(v=sql.110).aspx


Best way to handle column that doesn't conform to one particular type I have a Settings table:

CREATE TABLE `Settings` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Code` varchar(100) NOT NULL,
  `Name` varchar(250) NOT NULL,
  `Description` text NOT NULL,
  `Default` varchar(30) NOT NULL, <<<<<<<
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The Default value may however be integers, booleans, text, enums etc. etc.
I have set it to varchar right now, and when retrieving the setting, I will know what setting it is, e.g Send me daily emails - True/False, and do a case at the application level given that I know the type the value should be for that particular setting.

Is there a better way of doing this?

The approach you are proposing is workable but informal.  A more formal approach can be found in my answer to this question.

The basic pattern is called a Property Bag.


How to replicate and sync MongoDB localhost to a remote server using replica set Initially I started two node(primary and secondary) on same host machine on two different port , secondary was secondary was correctly replicating primary , but my actual requirement is on two different machine with two diff IP address , one should act as Primary other one should act as secondary ,To do that I remove secondary in my local host , and see status below , then tried to add remote IP on same replica , But showing error described below ,

"members" : [
            {
                "_id" : 0,
                "name" : "localhost:27017",
                "health" : 1,
                "state" : 1,
                "stateStr" : "PRIMARY",
                "uptime" : 5359,
                "optime" : {
                    "ts" : Timestamp(1502370582, 1),
                    "t" : NumberLong(1)
                },
                "optimeDate" : ISODate("2017-08-10T13:09:42Z"),
                "electionTime" : Timestamp(1502366671, 1),
                "electionDate" : ISODate("2017-08-10T12:04:31Z"),
                "configVersion" : 4,
                "self" : true
            }
        ],
        "ok" : 1
    }

myreplset:PRIMARY> config = {_id:"myreplset", members:[          {_id:0, host:"localhost:27017"},          {_id:1, host:"192.168.205.59:27017"} ]          };
    {
        "_id" : "myreplset",
        "members" : [
            {
                "_id" : 0,
                "host" : "localhost:27017"
            },
            {
                "_id" : 1,
                "host" : "192.168.205.59:27017"
            }
        ]
    }
    myreplset:PRIMARY> rs.initiate(config);
    {
        "info" : "try querying local.system.replset to see current configuration",
        "ok" : 0,
        "errmsg" : "already initialized",
        "code" : 23,
        "codeName" : "AlreadyInitialized"
    }

myreplset:PRIMARY> rs.add("192.168.205.59:27017");
{ "ok" : 0,
 "errmsg" : "Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2",
"code" : 103, "codeName" : "NewReplicaSetConfigurationIncompatible"
 }

Please use rs.add("host:port") command to add new node to replica set.

The main problem is that you can NOT mix localhost and IP addresses. If one member is on localhost, every member must be on localhost

You can of course use procedure:

var cfg=rs.conf()
cfg.members
<copy that to text editor and make changes>
cfg.members=<paste here edited members section>
rs.reconfig(cfg)


How to normalize a table with multiple cells that have multiple values? So I'm doing a task where I take a massive list (30,000+) of movies on Wikipedia that have multiple columns (such as the films name, the genre, the cast, the plot etc.) and upload it into Elasticsearch. However, after doing that I now want to make it so the table is in at least 1NF. I'm not really experienced in database design and the last time I did anything with Normal Form was a few years ago. So I'm looking at this table and thinking, how could I put this into 1NF. It's easy if for example there's only 1 column that has multiple values, but what do you do when there are multiple columns with multiple values as seen below.

Film Name
Director
Cast
Genre
Wiki Page
Plot

Chimmie Fadden Out West
Cecil B. DeMile
Victor Moore
Comedy, Western
https://en.wikipedia.org/wiki/Chimme_Fadden_Out_West
Chimmie is sent out west...

20,000 Leagues Under the Sea
Stuart Paton
Lois Alexander, Curtis Benton, Wallace Clarke, Allen Holubar
Action, Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

The Cat and the Canary
Paul Leni
Laura La Plante, Forrest Stanley, Creighton Hale
Comedy, Horror, Mystery
https://en.wikipedia.org/wiki/The_Cat_and_the_Canary_(1927_film)| In a...

Would you just have to do something like this...

Film Name
Director
Cast
Genre
Wiki Page
Plot

Chimmie Fadden Out West
Cecil B. DeMile
Victor Moore
Comedy
https://en.wikipedia.org/wiki/Chimme_Fadden_Out_West
Chimmie is sent out west...

Chimmie Fadden Out West
Cecil B. DeMile
Victor Moore
Western
https://en.wikipedia.org/wiki/Chimme_Fadden_Out_West
Chimmie is sent out west...

20,000 Leagues Under the Sea
Stuart Paton
Lois Alexander
Action
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Lois Alexander
Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Curtis Benton
Action
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Curtis Benton
Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Wallace Clarke
Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Wallace Clarke
Action
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

etc? I'm surely missing something extremely simple when it comes to converting a table with multiple cells with multiple values into 1NF, but I'm not sure what.
Thanks.

Normalisation is to remove information from tables, that are repe4ated by many times and ids as int are smaller than any text.
The bridge Tables you need, because you have a m:n relationship between film and users(cast, director, musician...)
Occupation is in my opionion a attributs of the relationship between film and user
Film (idfilm,Titel, plot,Wiki_Page, year,... )

Film2user (idfilm,iduser,idtype)

type  (idtype,occupation)

user (iduser, Name, Birth,...)

genre (idgenre,name)

Film2genre(idfilm, idgenre)

as you develop further, you can add more attributes or tables if you find more such redundant information


In the Vertica database, what is a namespace? In the Vertica database, what does the term "namespace" mean?

I have reviewed the entire Vertica documentation and cannot find what this means.

The namespace refers to a schema (select * from [schema].[tablename]). If you search the "SQL Reference guide" you will find the term used interchangeably, although, schema is used a lot more since its standard SQL.


New Server Query AD is giving permission error We replaced a physical server with virtual, I copied all the logins, and linked servers from the old server and restored all the databases from the old server to the new.  Everything works fine except for a query that uses a linked server to query Active directory.  I get a permission error:

The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

If I go to the linked server and test connection it succeeds.  The linked server is set to connect to AD using SQLService.  The only think that changed is the physical box we were running on, so it has to be something on the sql side that is not configured correctly?

I would presume the server Adam service GUID changed and or need to create a new computer object for your (virtual) as it's associated with the physical (adsi).

Presuming you've done all the user, machine creation. Perhaps run a com lime logo to confirm the ticket is valid database service and machine etc.?

Objects in ADS is what I'd audit and confirm.
Command kinit


Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files Trying to migrate individual tablespaces back into the shared ibdata tablespace.
MariaDB version 10.0.16

SET innodb_file_per_table to off in MySQL, restated mysql and new tables are in fact going into shared space.

Attempted to run alter table statement to recreate move the table back into the shared tablespace but the tables are not moving.

SET SQL_LOG_BIN = 0; ALTER TABLE menu_router ENGINE=InnoDB;

But the file still exists

-rw-rw----    1 mysql mysql  3.0M Apr 22 14:50 menu_router.ibd

in information schema

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE %db4472%';

+---------+---------------------------------+
| SPACE   | PATH                            |
+---------+---------------------------------+
| 2431817 | ./db4472/menu_router.ibd |
+---------+---------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]>

Found a solution that works

MariaDB [(none)]>  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '%5377%';
+---------+---------------------------------+
| SPACE   | PATH                            |
+---------+---------------------------------+
| 2432544 | ./db5377/menu_router.ibd |
+---------+---------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]> SET SESSION old_alter_table=1
-> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> ALTER TABLE db5377.menu_router FORCE;
Query OK, 2331 rows affected, 1 warning (0.10 sec)
Records: 2331  Duplicates: 0  Warnings: 1

MariaDB [(none)]>  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES  WHERE PATH LIKE '%5377%';
Empty set (0.05 sec)

To convert all the separate table spaces into the shared space.
Credit to: Migrate from innodb_file_per_table to off in MySQL

echo "SET SESSION old_alter_table=1;" > ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-uroot -p"
SQL="SELECT CONCAT('ALTER TABLE ',REPLACE(REPLACE(REPLACE(PATH,'./',''),'.ibd',''),'/','.'),' FORCE;')"
SQL="${SQL} FROM  INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH NOT LIKE '%mysql%'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> ConvertInnoDBToInnoDB.sql

mysql> source /root/ConvertInnoDBToInnoDB.sql


CHECKDB fails on MSDB and Master system databases I get the following error within my maintenance plans:

  Executing the query "DBCC CHECKDB(N'master')  WITH NO_INFOMSGS  "
  failed with the following error: "The database could not be
  exclusively locked to perform the operation.  Check statement aborted.
  The database could not be checked as a database snapshot could not be
  created and the database or table could not be locked. See Books
  Online for details of when this behavior is expected and what
  workarounds exist. Also see previous errors for more details.".
  Possible failure reasons: Problems with the query, "ResultSet"
  property not set correctly, parameters not set correctly, or
  connection not established correctly.

I get the following when I run the code from the query analyzer:

USE [master]
GO
DBCC CHECKDB(N'master')  WITH NO_INFOMSGS,  PHYSICAL_ONLY

  Msg 5030, Level 16, State 12, Line 1 The database could not be
  exclusively locked to perform the operation. Msg 7926, Level 16, State
  1, Line 1 Check statement aborted. The database could not be checked
  as a database snapshot could not be created and the database or table
  could not be locked. See Books Online for details of when this
  behavior is expected and what workarounds exist. Also see previous
  errors for more details.

I've searched high and low trying to find a fix, but no dice.

FACTS:

SQL Server 2008 R2 Standard 64-bit (10.50.4000.0)
Windows Server 2008 R2 64-bit w/SP1
VMWare Virtual Machine
Production server, so I can't reboot server/instance without internal change management process
SQL Server Agent & Database Engine service accounts have full access to the folders and files where the MDFs and LDFs are housed
Restarted SQL Server Agent via Config Mgr.
SQL Server Agent has read permissions on the entire volume
There are no read-only FGs
DBs are not in Single-user mode, read-only, or in emergency mode
Not running TABLOCK with DBCC command
The DBs are on NTFS

The following are a few places I've read out of many, but these are the most applicable to my matter as I see it.  I've performed the suggested "fixes" with no change in result or error message output from the SQL Server Error Logs.

Paul Randal's Input

Microsoft

There is an internal snapshot that SQL Server will try to create when you run certain DBCC commands.  There is really good information about this on the BOL reference for DBCC.

  When you execute one of these DBCC commands, the Database Engine
  creates a database snapshot and brings it to a transactionally
  consistent state. The DBCC command then runs the checks against this
  snapshot. After the DBCC command is completed, this snapshot is
  dropped.

  Sometimes an internal database snapshot is not required or cannot be
  created. When this occurs, the DBCC command executes against the
  actual database. If the database is online, the DBCC command uses
  table-locking to ensure the consistency of the objects that it is
  checking. This behavior is the same as if the WITH TABLOCK option were
  specified.

The master database is particularly interesting with this, as if the internal snapshot can't be created then DBCC CHECKDB will fail against master.  As per the above reference:

  DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created.

So, what can cause the internal snapshot creation to fail?  The above reference has a nice list:

  An internal database snapshot is not created when a DBCC command is
  executed:

  Against master, and the instance of SQL Server is running in single-user mode.
  Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
  Against a read-only database.
  Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
  Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
  Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.

  The DBCC commands use table locks instead of the internal database
  snapshots when the command is executed against the following:

  A read-only filegroup
  An FAT file system
  A volume that does not support 'named streams'
  A volume that does not support 'alternate streams'

My recommendation is to look at that information and find which one is applying to your situation.  That should answer your question and move towards the resolution.


Very large MySQL backup At our company we have a very large MySQL server with almost 1.000 databases with a total size of 500 GB. Every customer has is own database. And each database has about 120-125 tables!

In the past we made backups with mysqldump. The server is very slow while creating backups and it almost takes 5 hours to take backups. The databases have for 99% Innodb tables.

I understand taking backups of a lot of data takes a lot of time. But what is the best solution to take backups? The problem is that our customers are working 24/7 with our webapplications. The system is very slow while taking backups.

The conventional approach is to set up multiple replicated slaves.

You then backup from these slaves periodically (mysqldump is probably fine for this part still).

The master need not be blocked while the slaves are being backed-up; it will simply continue to replicate once those backups are done. Stagger the backups so you always have an up-to-date slave.

Replication is not CPU-expensive (unless you have a crazy level of database activity), and the slaves' backup process won't impact the master at all.

And everything should have redundant storage.

Anecdote: At a previous job we had a 600GB database that was backed up in the same way that you're doing it, but over a 10Mbit pipe to the target, and it would block the entire database for 11 hours every night! Eesh. Never did get replication in place there (lol) but upgrading the networking to 100Mbit meant it could be done in 2 hours or so instead, which was "fine". Of course, with the database locked during the backup (which it should be!), and thus the actual service unavailable for that duration, a performance hit from the backup process was not even observable from the outside. In your case you have multiple distinct databases so the situation isn't quite the same — you don't have to lock everything for the whole time.


SQL Server 2005 Row update problem After I pulled these datas what I wanted from a SQL Server Database,I can't update on row.I'm receiving the following the error.

I suppose that you have used the "Edit top x Rows" feature of Management Studio and wanted to update one row. In case that specific row was already deleted by another user/session then you'll receive the upper error.

I think it's better not use the Management Studio's results grid to update data, but use SQL commands. This way you'll work on last fresh data.


Why does pg_basebackup use so much less resources than pg_dump? When I run pg_dump the main application starts to lag out and take 1 to 2 seconds for even the simplest queries on primary keys of small tables. When I run pg_basebackup it's unnoticable. Why is this?

I know there are many questions on stackexchange about why pg_dump is slow, that's not what I'm asking; I'm asking what pg_basebackup does differently to make it unnoticable. And ideally is there any way to make pg_dump run "in the same way" so that I can use it without messing up the application?

pg_dump doesn't use more I/O that pg_basebackup. If anything, then fewer, since it doesn't have to read indexes. What could be is that you have a lot of TOASTed overlength values, and reading from the TOAST table causes random and maybe more I/O.

Typically, what is more used by pg_dump is CPU, because it has to parse all the table data and format them for output. So I'd guess that CPU is the resource which is scarce on your system.

As a first measure, you should identify the bottleneck, and then optimize your workload. If you have reached the limit there, consider adding more of the scarce resource.

Another, quite different cause for what you observe would be if your application misguidedly takes ACCESS EXCLUSIVE locks on tables. Such locks would be blocked until pg_dump is done and are a bad idea for other reasons as well (autovacuum). Check pg_locks for entries where granted is FALSE.


Forgotten PostgreSQL Windows password This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop.

The default value is ‘postgres’, but sure enough I forgot what password I used when I originally installed it.

I have googled and found a post related to resetting your password. I followed the steps, but the end result is a bit different then mentioned in the post. I used—

net user postgres postgres

to reset the password for my database but instead of a success message I am getting:

  "System error 5 has occurred. Access is denied."

system error. How do I avoid this error and reset the password?

(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE, though you can still configure other accounts).

  I have used net user postgres postgres
  to reset the password for my database but instead of a success message I am getting "System error 5 has occurred. Access is denied."

You've reset (or tried to reset) the service account password. PostgreSQL won't run as Administrator for security reasons and the installer generally sets it up with a "postgres" user account in PostgreSQL 9.1 and older1. On Windows you can't start a service as a user without saving the password of the user in the registry, so that's what the installer does.

If you change the password for the Windows user account postgres, the PostgreSQL service can no longer start. So don't do that, you'll have to fix the service configuration to store the updated password.

Thankfully I think another mistake prevented you from doing that. It looks like you're probably running your command prompt without using "Run as Administrator" on an unprivileged Windows user account or a machine with UAC, so it isn't running with the access permissions required to change the password for the postgres user.

Before you try to change that password, make sure it's really what you want to do. What's the problem you're trying to solve here? Are you attempting to install a database update or something else that's asking for the password for the postgres Windows user?

Most likely you're just trying to log in to the database. For that, you use the (unfortunately completely unrelated) password stored in the database its self. Since you've lost/forgotten it you'll have to reset it:

Find your pg_hba.conf, usually in C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf
If necessary, set the permissions on it so that you can modify it; your user account might not be able to do so until you use the security tab in the properties dialog to give yourself that right by using an admin override. Alternately, find notepad / notepad++ in your start menu, right click, choose "Run as administrator", then use File->Open to open pg_hba.conf that way.
Edit it to set the "host" line for user "postgres" on host "127.0.0.1/32" to "trust". You can add the line if it isn't there; just insert:

host all postgres 127.0.0.1/32 trust
host all postgres ::1/128      trust # if IPv6 is in use

before any other lines. (You can ignore comments, lines beginning with #).
Restart the PostgreSQL service from the Services control panel (start->run->services.msc)
connect using psql or PgAdmin-III or whatever you prefer
ALTER USER postgres PASSWORD 'postgres'
remove the line you added to pg_hba.conf or change it back
restart PostgreSQL again.

See: How do I reset the postgres password for PostgreSQL on Windows?

1. 9.2 now uses the NETWORKSERVICE account, which doesn't require a password, so this problem goes away.


How I can find out the foreign wrapper that a foreign table belongs to? I have found that the following query lists all my foreign tables in my PostgreSQL database:

select * from information_schema."tables" where table_type='FOREIGN TABLE';

Now I want to find out the foreign wrapper connection that a database is referred into. In this answer the following query lists all my foreign wrappers:

SELECT fdw.fdwname AS "Name",
  pg_catalog.pg_get_userbyid(fdw.fdwowner) AS "Owner",
  fdw.fdwhandler::pg_catalog.regproc AS "Handler",
  fdw.fdwvalidator::pg_catalog.regproc AS "Validator"
FROM pg_catalog.pg_foreign_data_wrapper fdw
ORDER BY 1;

Now what I want is to find out how to combine this information in order to find out in which foreign wrapper a foreign table belongs into. Do you have any idea how to query that?

This should do the trick:

SELECT
  foreign_table_name,
  foreign_server_name

FROM
  information_schema.foreign_tables

You may find more information on information_schema.foreign_tables in the documentation.


Is there a way to check connection properties of an active SQL Server connection? The context:

I have a Java application that's using HikariCP for its connection pooling solution. While lightning-fast, HikariCP doesn't have a lot of configuration options, by design, and that's all fine and dandy except I need to set a connection property, and while a DataSource like Tomcat's gives me the ability to do something like:

tomcatDataSource.setConnectionProperties("someProperty=true");

Hikari's DataSource object doesn't have such a method.

My options, as far as I've been able to tell are to either create a DataSource object manually and pass it to Hikari to wrap it in it's own class, or to include the connection property as part of the connection URL.

(Just to make sure we're on the same page, I'm talking about the "sendStringParametersAsUnicode" flag as described in this document: Setting the connection properties - SQL Server)

The question (also known as "so far this question seems to belong in StackOverflow but hopefully you kept reading"):

Is there a way, on the database side, to see the connection properties of an active connection? In SSMS or some other tool? I tried SSMS's activity monitor on the target database, and right clicking on anything that seemed right-clickable, but nothing mentioned the properties of the open connections. Is this even possible?

I'd like to see the properties so I can double-check if my two options described above for setting this flag actually work.

That property is a setting for the client-side of the connection.  Not the server-side, so you won't be able to directly observe this setting from SQL Server.

You can observe the effect of that setting using SQL Server Profiler or Extended Events, looking for the Batch and RPC events.  They will show you the parameter data types sent by the client.


On not public server: Host name example.com could not be resolved: Name or service not known This is not a duplicate (please read carefully)

Digging in mysql logs of a production server I found a few entries with the error above, all pointing to the same host name. Each entry appears ~30 seconds right after mysql starts. There is also no record of the hostname in mysql.user table

I would not be surprised if mysql was exposed to public internet but it is not. The port is blocked by lfd/iptables and explicitly allowed for a few public/private IP addresses.

MySQL's skip_name_resolve is not enabled, since I need hostname resolution for a few VPN servers.

Where these entries may be coming from? The server is WHM/CPanel, MySQL 5.6.21

When --skip-name-resolve isn't in specified, the IP address of incoming connections is resolved into a name via a DNS query...

However, there isn't just one DNS query, because the existence of a reverse DNS entry (e.g. a PTR record 203.0.113.113 → foo.example.com) is not sufficient for hostname-based authentication -- anybody could change a reverse DNS entry to claim to be any hostname they wanted.

So there's a second query that occurs.

Once the hostname is resolved, there's an additional DNS lookup in the opposite direction, to verify that the hostname discovered in the first DNS query, foo.example.com, does indeed map to an A record that resolves back to the original IP address (e.g an A record foo.example.com → 203.0.113.113).

In this case, the IP address mapped to a hostname, but the hostname did not map back to the original IP address.

http://dev.mysql.com/doc/refman/5.6/en/host-cache.html


Mysql Get Column Name after Case Max i have this scenario with this query that trasform multiple rows key/values in a columns.

SELECT
  t1.*, t2.*, u.name, u.email, u.username,
  MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) AS latitude,
  MAX(CASE WHEN t2.datakey = 'longitude' THEN t2.data END)AS longitude
FROM `#_social_fields_data` AS t1
INNER JOIN `#_social_fields_data` AS t2 ON t1.uid  = t2.uid
LEFT JOIN `f9leh_users` AS u ON t1.uid = u.id
GROUP BY t1.uid

and i need to add a where clause to call the latitude value
but adding clause for example

where latitude > 0

and after i need to call the colum to replace the new a.lat with latitude column name

 $distance_col_expression = "(((acos(sin((".$post['socialmembers']['location_lat']."*pi()/180)) *
                            sin((a.lat * pi()/180))+cos((".$post['socialmembers']['location_lat']." * pi()/180)) *
                            cos((a.lat * pi()/180)) * cos(((".$post['socialmembers']['location_lng']."- a.lng)
                            *pi()/180))))*180/pi())*60*1.1515)
                            ";
                        $query .= ' AND '.$distance_col_expression .' <= '.$post['socialmembers']['search_radius'];

it not work. Says invalid column name. I have tried with other names but seems that this name generated is temporary.

Please can suggest me, How can i do?
Thanks in advance

Thanks for the useful suggestion.

Now the query work as in this way:

select DISTINCT t3.uid,latitude,longitude
from (
  SELECT DISTINCT
  t2.*,
  MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) AS latitude,
  MAX(CASE WHEN t2.datakey = 'longitude' THEN t2.data END)AS longitude
FROM `#_social_fields_data` AS t1
INNER JOIN `#_social_fields_data` AS t2 ON t1.uid  = t2.uid
GROUP BY t1.uid
     ) as t3
WHERE latitude > 0

Thanks, as suggested i have optimized the query removing self_join:

select DISTINCT t2.uid,latitude,longitude
from (
  SELECT DISTINCT
  t1.*,
  MAX(CASE WHEN t1.datakey = 'latitude' THEN t1.data END) AS latitude,
  MAX(CASE WHEN t1.datakey = 'longitude' THEN t1.data END)AS longitude
FROM `#_social_fields_data` AS t1
GROUP BY t1.uid
     ) as t2
WHERE latitude > 0


How to use full text search in Persian using Postgresql? I'm using Postgresql 9.6 and I'd like to run a query like this:

select post_id
from comments
where to_tsvector('pg_catalog.persian', comments.body) @@ to_tsquery('pg_catalog.persian', 'کتاب') ;

But Persian is not among the languages supported in postgresql dictionary:

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
(16 rows)

So I get error:

  ERROR:  text search configuration "pg_catalog.persian" does not exist

My default DB config is for English:

postgres=# show default_text_search_config;
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

I've tried pg_catalog.simple but it returns anything, while there are  results for کتاب.

So I'm wondering how can I make a Full text Search in this situation to get relevant Persian results?

use this.

select post_id
from comments
where to_tsvector('simple', comments.body) @@ to_tsquery('simple', 'کتاب') ;

OR

select post_id
from comments
where to_tsvector('simple', comments.body) @@ to_tsquery('کتاب') ;

Or use this extension:

https://pgroonga.github.io/tutorial/


Is there any way to Restore a Database from newer version to an Older Version of SQL Server? I am having a database backup file from SQL SERVER 2012 and i wanted to restore it on SQL SERVER 2008R2 .
If we can’t restore a SQL Server 2012 database to earlier version what is the next best option?
So Is their any way to restore by any other way ??

The only way this would be possible would be to restore the Back up to a SQL 2012 instance and then try importing it to a SQL 2008-R2 instance. You may have to create some sort of ETL script to do the importing.


Set priority for an SQL Server role Is there any way to set priority for a role if a user falls in two roles?

EXAMPLE : If the user is a member of the manager and staff roles, I want to get the features of the manager on conflicting issues.

Delete is denied in staff while granted in manager and user1 is a member of both, then user1 could delete the data.

Unfortunately for you, it does not work that way. Users in multiple roles are subject to the complete set of permissions from all of their roles, and deny permissions always trump grant permissions. You have to keep that in mind as you design your user and role assignments.

In this case, you might find that you don't need the DENY rule at all. A user can't do anything without explicit grant permissions, so if you remove the deny rule from your staff role they likely still won't be able to delete anything: the rule is redundant for them.


Can I upgrade MariaDB replication infrastructure gradually? I have 2 masters and 1 slave (replicating form both masters).
M1- MariaDB 10.4.11
M2- MariaDB 10.4.16
S1- MariaDB 10.4.12
I want to upgrade this LIVE system to 10.5 with possibly 0 downtime. The idea is this:

upgrade slave S1 (this is used mainly for backups, not queried at all)
switch all traffic to M2, stop M1 and upgrade M1
turn on M1 and sync it from M2 - at this stage I will have active master M2 running old 10.4 MariaDB, but both standby master M1 and slave S1 are already on 10.5
switch all traffic to M1, stop M2 and upgrade M2
sync M2 from M1 and keep it as standby master

I guess my main question is - Is MariaDB 10.5 <-> 10.4 replication compatible? Any particular issues I should look for?
Thanks!

A MariaDB replica can replicate from an older master. The other way around won't work.
You should add a step 2.5: stop replicating from M1 to M2 (even if it's just a standby master). After step 4, M1 can replicate from M2 again.


Simple SQL CTE update I am a little stumped with this CTE update stmt:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

WITH cte AS
(
    SELECT * FROM @a
)
UPDATE cte
SET    Value = b.Value
FROM   cte AS a
INNER JOIN @b AS b
ON     b.ID = a.ID

SELECT * FROM @a
GO

Why does this result in table @a having 100 for both rows? I thought it should be 100 for ID 1 and 200 for ID 2.

I get expected results if I use a table instead of a common table expression to do the updates:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

SELECT  *
FROM    @a

UPDATE @a
SET Value = b.Value
FROM @a AS XX
INNER JOIN @b AS b ON b.ID = xx.ID

SELECT  *
FROM    @a

This results in table @a with 100 and 200. But aren't we supposed to get both the same values? based on the previous explanation of referencing issue? -- Update being done to @a table and not the referenced XX.

To expand on MguerraTorres' answer:

(Updated with the info from your secondary query)

In your first query UPDATE cte says to update the table from the CTE.

FROM cte as a says to refer to the table from the CTE as a.

So, we've referred to our CTE in two places.

What you may not realize is that a CTE is re-evaluated for each time it appears in your query, just as if you replaced the reference with a subquery. Since you've referenced the CTE two separate times, you've generated two separate resultsets for the DB engine to work with.

When you say to use b.Value where a.ID = b.ID, we've got two rows - one where b.Value is 100, and one where it's 200 - from table b and from our second CTE resultset.

However, we're updating the first CTE resultset based on these two rows. Therefore, it updates each row in that first resultset from the two rows returned. There's no relationship between the two resultsets, even though they represent the same underlying data. The engine is doing a CROSS JOIN between the results of your join, and the first resultset, to perform the update.

Your UPDATE statement updates both of your rows to to 200, then to 100 (because the engine decides the quickest way to apply the cross-joined rows, they may not go in the order in which they were entered). Both rows are updated to the same value because they're being updated from the same multiple rows.

Your first query is functionally identical to:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

WITH cte AS
(
    SELECT * FROM @a
)
UPDATE cte
SET    Value = b.Value
FROM   (SELECT * FROM @a) AS a
INNER JOIN @b AS b
ON     b.ID = a.ID

SELECT * FROM @a
GO

In your second query, the DB engine knows that both a and @a reference a table outside the query, and it knows that a and @a mean the same thing, so it correctly ties the rows from @b to @a when performing the update.

In the comments, you asked:

  Would the result be always be 100 for both? or can it be 200 for both sometimes -- As I see there is no clear rule here?

Whether it's 100 or 200 can vary.

I would say that it's likely that, given the same statements shown in your first query, executed in the same way, you would almost certainly get the same outcome.

However, in the real world, with tables seeing other activity, you couldn't really on one outcome or the other, especially over time. It would depend on how the DB engine matched the tables in the join, and then processed the rows in applying the update.


postgres database disk issue I have a postgres database running on my application, and the postgres partition is 100% full. Upon checking the files in pgdata directory is see below files occupying lot of space.

smsstoregt1 ins /postgres/pgdata/base/16384> du -skh * | grep -i M
589M    16400
244M    16405

Please can anyone suggest how to cleanup the space and if we can move the files 16400 and 16405 to another path or delete them, or if we can purge any data from postgres database from DB login.

Below is my postgres partition

/dev/mapper/mpath0p1  939M  900M     0 100% /postgres

You can create a new tablespace on a different disk and then move those tables to that new tablespace.

First create a new table space on a disk that has enough space:

create tablespace large_disk
   location '/path/to/directory/with/more/space'
   owner your_postgres_user;

By making your_postgres_user the owner of that tablespace, the user automatically gets all privileges on that tablespace.

Then you need to find out to which tables those files belong to:

select 16400::regclass, 16405::regclass;

Then move the tables:

alter table public.table_one
    set tablespace large_disk;

alter table public.table_two
    set tablespace large_disk;