How can I increase the length of the string returned by object_definition in sqlcmd?

Specify the -y SQLCMD option to avoid truncating the value to the 256 character default. -y0 will not truncate the value but should be used with caution as mentioned in the doc.

Example:

sqlcmd -i extract.sql -y0 -o %oudir%\extracted.dat


MySQL is using an Index not listed in possible_keys

The documentation for MySQL has the following bit of information regarding the EXPLAIN Output Format:

  It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

So while an index seek is not expected, MySQL will still retrieve the data via the index rather than via a normal data row scan.

Addendum regarding primary key indexes

On the assumption that you are using the InnoDB engine and that the PK on the passengerdetails tables is based on the passenger_id, then all other secondary indexes will be based on this primary key as stated in the article 14.11.9 Clustered and Secondary Indexes in the official documentation.

Even if you didn't create a primary clustered index yourself, MySQL will do it for you to ensure speedy queries. This is documented as:

  If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

and:

  If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.


Master to Slave to Slave Configuration in MySQL

Based on our chat conversation, here is what was discussed

Server1 is Stand Alone
Server2 is a Master
Server3 is a Slave to Server2

This implies that binary logging is enabled in Server2.
To Make Server1 a Master of Server2, perform the following:
STEP 01 : On Server2, add this to /etc/my.cnf
[mysqld]
log-slave-updates

STEP 02 : On Server3, run STOP SLAVE;
STEP 03 : On Server2, run service mysql restart
STEP 04 : On Server3, run START SLAVE;
STEP 06 : On Server1, add this to /etc/my.cnf
[mysqld]
log-bin=mysql-bin

STEP 07 : On Server1, run service mysql restart
STEP 08 : Set Replication From Server1 to Server2
See Clarification about master slave configuration in mysql
OPTIONAL
Once you have MySQL Replication Going From Server1 to Server2 to Server3, your can properly load all data into all three MySQL Instances by doing the following on Server1:
mysqldump -u... -p... --all-databases --routines --triggers > mysqldata.sql
mysql -u... -p... < mysqldata.sql

This will do three(3) things

Repopulate everything into Server1
MySQL Replication will handle populating Server2 from Server1
MySQL Replication will handle populating Server3 from Server2

Since your data is 50MB in total, this should be execute very quickly.


VACUUM VERBOSE outputs, nonremovable "dead row versions cannot be removed yet"?

The oldest transactions are some minutes old and haven't modified
  anything on the table yet.

That's not sufficient. I think what is required to mark these rows as dead is that, when these transactions were started, there was no other transaction that had touched these rows (doing an UPDATE or DELETE on them).

Updating or deleting a row will keep the previous version of the row physically where it was, and set its xmax field to the TXID of the current transaction.
From the point of view of other transactions, this old version of the row is still visible if it is part of their snapshot. Each snapshot has an xmin and xmax to which the xmin and xmax of the row versions can be compared.
The point is that VACUUM must compare row versions against the combined visibility of all live snapshots, as opposed to simply checking if a row change is definitely committed. The latter is necessary but not sufficient to recycle the space used by the old version.

For example, here's a sequence of events such that VACUUM can't clean up dead rows even though the transaction that modified them has finished:

t0: Long running transaction TX1 starts
t0+30mn: TX2 starts and sets itself in REPEATABLE READ mode.
t0+35mn: TX1 finishes.
t0+40mn: pg_stat_activity shows only the 10-mn old TX2
t0+45mn: VACUUM runs but won't eliminate the old versions of the rows modified by TX1 (because TX2 might need them).


Why would you index text_pattern_ops on a text column?

The documentation often gives you an answer to such questions.  Like in this case, too:

  The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar,
  and char respectively. The difference from the default operator
  classes is that the values are compared strictly character by
  character rather than according to the locale-specific collation
  rules. This makes these operator classes suitable for use by queries
  involving pattern matching expressions (LIKE or POSIX regular
  expressions) when the database does not use the standard "C" locale.
  As an example, you might index a varchar column like this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

  Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >=
  comparisons to use an index. Such queries cannot use the
  xxx_pattern_ops operator classes. (Ordinary equality comparisons can
  use these operator classes, however.) It is possible to create
  multiple indexes on the same column with different operator classes.

The documentation goes on to say:

  If you do use the C locale, you do not need the xxx_pattern_ops
  operator classes, because an index with the default operator class is
  usable for pattern-matching queries in the C locale.

You can check your locale as follows (it is likely to be UTF8 rather than "C"):

postgres=> show lc_collate;
 lc_collate
-------------
 en_GB.UTF-8


Can comments in stored procedures cause problems

I've never heard of comments causing a problem.

The code you captured from the running server and the posted source are for different SPs.  It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code.

For testing you could add another INSERT to the SP, writing as single value to a dummy table.  Then if the commented-out lines run there will be more rows in the dummy table than expected.


How to design a table with relationships to a group of users or individual users?

you could use something like this:

create table users (
    userid int not null primary key
  )

create table groups (
    groupid int not null primary key
  )

create table permissions (
    permissionid int not null primary key
  )

create table users_groups (
    userid int not null
  , groupid int not null
  , constraint pk_users_groups primary key clustered (userid, groupid)
  , constraint fk_users_groups_userid foreign key references users(userid)
  , constraint fk_users_groups_groupid foreign key references groups(groupid)
  )

create table users_permissions (
    userid int not null
  , permissionid int not null
  , constraint pk_users_permissions primary key clustered (userid, permissionid)
  , constraint fk_users_permissions_userid foreign key references users(userid)
  , constraint fk_users_permissions_permissionid foreign key references permissions(permissionid)
  )

create table groups_permissions (
    groupid int not null
  , permissionid int not null
  , constraint pk_groups_permissions primary key clustered (groupid, permissionid)
  , constraint fk_groups_permissions_groupid foreign key references groups(groupid)
  , constraint fk_groups_permissions_permissionid foreign key references permissions(permissionid)
  )

and query it with something like this:

select p.permissionid --,p.other_permissions_columns
  from users_permissions up
    inner join persmissions p on p.permissionid = up.permissionid
  where up.userid = @userid
union
select p.permissionid --,p.other_permissions_columns
    from users_groups ug
      inner join groups_permissions gp on gp.groupid = ug.groupid
        and ug.userid = @userid
      inner join persmissions p on p.permissionid = gp.permissionid


How to create a user with a particular user_id?

Not in a supported fashion, no.

Why would you care what the USER_ID of a new user was?  That would seem to raise a pile of red flags in my mind.


How to handle "or" possibilities in MongoDB

In cases where there is a legitimate option to use a substitute ingredient you can do one of several things, all involving storing the option in the ingredients array.  One way could be to add a field to an array element as "allowedSubstitutions": "different liquor" another way would be to turn "ingredient" into an array which lists possible different acceptable ingredients.

However, in case of a tricky martini drink, I would strongly encourage you to store two separate drinks, one called "Martini" and the other called "Vodka Martini" otherwise known to purists as "not really a martini" :)

The real key question is how will you be querying this collection - with flexible schema the way you use the data will usually drive the way you structure its storage, not the other way around.


os_username vs username in Oracle audits

The os_username is the name of the user that runs the application. The username is the name of the user defined in the database.

Suppose that UserA is logged on his desktop as UserA and runs an application to modify the data. When he is asked to connect he enters the credentials of UserB. In the audit you will find UserA as os_username and UserB as username.

This explains while you find oracle as os_username since this is the user that is used to run Oracle itself.


Totals on multple case conditions - mySql

In the latest iteration (edit #5), the problem would be that you can't use column aliases within the query where they're declared. You can either go back to the original query (get the total for each category in a sub-query, and add them together using the aliases in the main query), or do this:

SELECT
  tourney_info.school_num,
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end)
  + SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end)
  + SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Entries',
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE tourney_info.school_num = '546' AND tourney_info.tourn_date > '2018-04-20' AND tourney_info.tourn_date < '2018-04-22'


Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions

Firstly, I agree with John M's comment to your question. You should do some reading about the concepts of database.

You probably misinterpret between accessing your data and how your data organized in the database. When you need to find names whose last_name equals to 'Bluthe', just do it:

SELECT last_name FROM names WHERE last_name = 'Bluthe'

That's it. You do not need to worry about the primary key. The thing you should worry of is whether the last_name is indexed.

When we talk about index, then the primary key comes in. Primary key is the way to address a row in a table. Hence, PK should be unique. The way you define primary key simply won't work. Index will be build on top of PK.
Let's say you have names table as follows, assuming using integer as pk

names
--------------------------------------------------
pk_field  first_name        last_name
--------------------------------------------------
1         John              Doe
2         Will              Smith
3         James             Bluthe
4         Nick              Smith

Then we create index on last_name. The index created will be like this:

--------------------------------------------------
last_name     pk_field
--------------------------------------------------
Bluthe        3
Doe           1
Smith         2
Smith         4

Upon searching last_name, database will utilize this index to locate the actual record. Searching 'Bluthe' will come up with pk_field = 3, which is then used to fetch the actual record from names table.

For me, the best practice for primary key is cheap to compare (using integer based data types), clustered with auto-increment values. With auto-increment value we do not have to worry when more than one transaction inserting to the same table at the same time. Clustered primary key will arrange the physical row in the table according to its primary key value. Thus searching based on primary key will be very fast.

HTH


Calculating the value of a column based on consecutive values of rows in another column

The main thing you need is an ordering of the rows, since rows are unordered per se. Once you have an ordering, you can use the window function lag to access the previous row in that ordering.

Assuming that your rows are ordered by tstamp, that would look like

SELECT lat,
       lon,
       lag(lat) OVER (ORDER BY tstamp) AS prev_lat,
       lag(lon) OVER (ORDER BY tstamp) AS prev_lon
FROM atable;

Applying the formula of your choice is left as an exercise for the reader.


Always On - Does the read replica need the same SQL license as the primary?

Enterprise Edition is required on the readable replica, but it doesn't have to have the same hardware. You could go with a server with less cores and thus need less licenses.


MySQL usage suddenly hitting through the roof

This is a common issue as you scale to some point.  There could be many causes; let's first search for the cause; then the solution.

long_query_time=0
turn on the slowlog
run for a few hours
turn off the slowlog (or raise the cutoff to 1)
run pt-query-digest to find the 'worst' couple of queries.

Those queries may be zillions of 10ms queries, or it may be a few 1-minute queries.  In either case, look carefully at whether

you need them that often,
they have optimal indexes,
they can be combined in some more efficient way
they can be rewritten
etc.

Check some tunables

How much RAM do you have?
Are you using InnoDB?
What is the value of innodb_buffer_pool_size?
If you provide me with SHOW GLOBAL STATUS; and SHOW VARIABLES;, I will search for other issues.

Other likely causes

UUIDs are terrible as keys; as soon as the data exceeds cache, you are in deep dodo.
EAV schema has problems
How many cores?  What is max_connections, Max_used_connections?  What version?  (You could have the "thundering herd" or "too many people in the grocery store" syndrone.)

Tuning Analysis

Observations:

Version: 5.6.27
48 GB of RAM
Uptime = 03:06:22; Please rerun SHOW GLOBAL STATUS after several hours.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

Since you are not(?) using MyISAM, decrease key_buffer_size to only 10M.

innodb_log_buffer_size is over 7G, 15% of RAM -- far too big; decrease to 100M.

innodb_additional_mem_pool_size is no longer used; remove it from my.cnf

It is terribly dangerous to set tmp_table_size and max_heap_table_size too high.  Recommend 1% of RAM size, not 12%.

query_cache_type = ON and query_cache_size = 2G -- This may be the worst performance hit.  No more than 100M for the size.  When it is 2G, each write spends a lot of time going through Query Cache to purge entries for the modified table.  Furthermore, the metrics on the QC say it is not very effective.  So even consider turning it OFF.

Several STATUS metrics indicate lack of indexes and/or poorly written queries.  Let's take a look at a couple of them.

innodb_doublewrite = OFF -- this may (rarely) lead to "torn pages" after a crash.

There seems to be no MyISAM activity; this is unexpected because some system tables are MyISAM.  What gives?  Also puzzling:  default-storage-engine=MyISAM.

Details and other observations

Memory allocation

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (8589934592 - 1.2 * 0 * 1024) / 49152M = 16.7% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 8589934592 = 0 -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( innodb_log_buffer_size ) = 7G -- far too big; decrease to 100M

( join_buffer_size ) = 32M -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- Use the default.

( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 6442450944, 6442450944 ) / 49152M = 12.5% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease tmp_table_size and max_heap_table_size to, say, 1% of ram.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 5,245 / (5245 + 19126) = 21.5% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( tmp_table_size ) = 6,442,450,944 = 6144MB -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Handler_read_rnd_next ) = 23,608,688,071 / 11182 = 2111311 /sec -- High if lots of table scans
-- possibly inadequate keys

( Handler_read_rnd_next / Com_select ) = 23,608,688,071 / 116779 = 202,165 -- Avg rows scanned per SELECT. (approx)
-- Consider raising read_buffer_size

( Select_scan ) = 33,170 / 11182 = 3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 33,170 / 116779 = 28.4% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log / max_connections ) = 70 / 100 = 70.0%

( Connections ) = 28,080 / 11182 = 2.5 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 64 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- 0 is good for Windows
0 is inefficient for non-Windows; 10 is probably fine

( thread_cache_size / max_connections ) = 64 / 100 = 64.0%
-- (0 for Windows)

( Com_show_collations ) = 30/minute.  What's up?


Is there a way to load a sql script with a specified encoding into a running SQL Server Management Studio (SSMS)?

this might not be the answer you expected/hoped for but take a look at DbVisualizer. This is a nice tool that organizes sql scripts as bookmarks and we can choose in what encoding they are written to disk. The tool is multiplatform and multidatabase.
Using this I - as an oracle dba - can use a SQL server database without having any dedicated SQL server tool installed, from my macbook.
Choosing a smart encoding if you work multiplatform can help.

I hope this helps.


Query to retrieve SQL Server login password length

I think, your corporate security meant by week passwords. One way of doing that (without any third-party tools) with help of in-built function PWDCOMPARE, but for this you need to have real week-passwords list available perhaps in database table from where every password looked-up and compared with SQL Login hashes one-by-one. following is the example:
Declare @pwText nvarchar(128);
Declare @WeekPassword_List table (pw nvarchar(128));
Declare @WeekPasswords_Active table (login_name sysname, pw nvarchar(128));

insert into @WeekPassword_List
values
('password'),
('passwordpassword'),
('password123'),
('P@ssw0rd'),
('Pa55word'),
('Pa55w0rd'),
('Temp@password'),
('Temp123'),
('temp123'),
('temp@123'),
('Temp@123'),
('123'),
('1234'),
('123456'),
('12345678'),
('');

while exists (select 1 from @WeekPassword_List)
begin
    SET @pwText = (select top 1 pw from @WeekPassword_List);

    INSERT INTO @WeekPasswords_Active
    SELECT name, @pwText
    FROM   sys.sql_logins
    WHERE  Pwdcompare(@pwText, password_hash) = 1;

    DELETE FROM @WeekPassword_List where pw = @pwText;
end

select * from @WeekPasswords_Active;


This is same as 'Startup Priority' introduced in Windows Server 2012. You can set it from two places if you are using GUI. One what you have shown in the question and other one is below.  If you right click on any role name and click Change startup priority you will get to same options.

You can also use PowerShell to change the same setting. Example here.

Medium is the default priority. In the case of node failure, this priority setting determines the order in which roles are failed over and started on another node. A higher priority role both fails over and starts before the role of the next highest priority. If you assign the No Auto Start priority to a role, the role is failed over after the other roles but is not started on the new node. The purpose of startup priority is to ensure that the most critical roles have prioritized access to resources when they fail over to another node.

Ref:

Assigning role startup priorities


Issue patching SQL Server 2016 to SP2 CU 15

Thanks to SqlWorldWide:

You will need to install SP2 first in order to apply any CU that was released after SP2.

Applying the base SP2 patch allowed me to then apply the CU 15 patch.
Being forced into the DBA role, I tried this CU on our test server which was at the same patch level and CU 15 worked fine, hence my confusion.


get query execution time in command prompt

In all probability, your query takes half a second to run, but it takes a lot more time to display all the results.

The latter depends on the speed of your network and that of your computer and is not really relevant from a performance tuning perspective.

To answer your question, the query execution time (0.5 seconds) is the execution time.


Mysql: Tell InnoDB to keep a specific table or database out of pool/cache?

Any SELECT, INSERT, UPDATE, or DELETE performed against InnoDB tables will do all the following:

Add new pages to the InnoDB Buffer Pool
Generate dirty pages (via UPDATE) within the InnoDB Buffer Pool
Remove old pages from the InnoDB Buffer Pool that you may still need

You cannot dictate what tables will and will not affect the InnoDB Buffer Pool. However, you can stagnate the rotation of Buffer Pool pages at MySQL startup with these two options:

innodb_old_blocks_pct

  Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool).

innodb_old_blocks_time

  Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

For further information, please see The InnoDB Buffer Pool section of MySQL Documentation

If you are not sure of the effects, it is best to leave it as is. Perhaps more server memory and a bigger InnoDB Buffer Pool may be the answer. Tweaking these settings may be overkill if you could just upgrade RAM.


SQL Server 2008 R2 Management Studio : "Evaluation period has expired"

You've already I assume, applied the registry fix suggested by Microsoft.

Since they are saying that this has been resolved since SP1, have you considered installing a newer version of SQL Server 2008 R2 Express?

http://www.microsoft.com/en-us/download/details.aspx?id=26729

Update 12:27

Based on what you're saying and without having your system in front of me, I'd try to find out if the evaluation edition you think you removed might actually still be there.

You can run the SQL Server features discovery report to find out what components are actually installed on your laptop.

Start -> All Programs ->  SQL Server 2008 -> Configuration Tools -> SQL Server Installation Center.  Then on the left you select Tools, and then click on the Installed SQL Server features discovery report.

Installation Center - Tools

Features Report

I would also go Control Panel, Uninstall Programs, and make sure there wasn't anything left over from the evaluation edition.  Once you're absolutely sure that evaluation is gone, reboot the system just to make sure that the registry is reloaded correctly.


Is there a difference between RANGE and LIST method for partitioning by consecutive integer values?

The 2 will be equivalently impossible ;-). If you try that, you will get the following error:

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Which is one of the biggest limitations of partitioning. Any unique key must contain the columns of the partitioning function. So either:

Drop the primary key (not usually a good idea)
Add the year to the primary key (so you can have duplicated ids)
Create your own partitioning (forcing you to maintain yourself the constraints and making the queries a bit more difficult)

Independently of that, I can see at least one big difference (and the reason why I would recommend you to use LIST) which is that even if your application cannot insert lower values, it is not restricted at database level using the range. Not only that could potentially break our data consistency in case of an application error, it would be advantageous if you had a query like this:

SELECT * FROM foo WHERE `year` = 1999;

That also has a potential impact on performance, if you run EXPLAIN PARTITIONS on that query with the RANGE partitioning, the full first partition would be scanned (using an index or not); in the second case, partition pruning can automatically return the empty set. In practice you may not see a big difference if you do things correctly (the partitions will be physically equivalent), but I cannot see any case where RANGE is better, while I can for LIST.

Addendum: If you are thinking about changing the physical structure of the table, and you are running a recent version of MySQL (5.5, 5.6), you should think about changing to InnoDB. I am not saying that you should, there are reasons to maintain the format, but it is something that you may want to ask yourself, as the table will have to be recreated anyway for partitioning.


Column 'Comments.Text' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Your question isn't really clear, but I think you want something like this:

select group_id,
       item_id,
       comment
from (
   select group_id,
          item_id,
          comment,
          row_number() over (partition by group_id order by item_id) as rn
   from the_unknown_table
) t
where rn = 1;

(you didn't state your DBMS, so this is ANSI SQL)

SQL Fiddle demo: http://sqlfiddle.com/#!12/a8471/1


How can I prove / disprove If A ↠ BC and A → B then A → C

A,B and C are sets of attributes.

  All examples I've seen in books are like Employee ↠ Project |
  Dependent, where Employee simultaneously determines two attributes.

A ↠ B | C is a special notation different from A ↠ BC. It's useful because MVDs always come in pairs. It says that both A ↠ B and A ↠ C hold. This implies that the relation equals AB JOIN AC. Ie the relation satisfies join dependency *{AB,AC}.

  What does it means to have a multi valued dependency on two attributes like A ↠ BC?

  Please give an example of such a dependency.

Just think of values for subrows the way you would think of values for one attribute. Suppose {W} ↠ {X}. Then whenever W appears it's with all of some set of X values. Suppose each X has a numerator N and denominator D. Then we could have a relation {W} ↠ {N,D}. Whenever W appears it's with all of some set of < N, D > subrow values.

  Please help me prove/disprove option C.

  C) If A ↠ BC and A → B then A → C. Here using Coalescence rule I'm getting A->B back. I'm not able to prove/disprove A → C.

Hint: In my last paragraph if {W} → {N} then the variation must come from D.

Hint: If you are permitted to prove by counterexample then it's good to try to disprove things via guessing at one or more trivial examples. Eg with {W} ↠ {N,D} and {W} → {N} (A={W}, B={N}, C={D}):

  V   W   N   D
=================
| v | w | n | 1 |
-----------------
| v | w | n | 2 |
-----------------

(
How do I apply this definition of multivalued dependency (MVD) to this example relation?
)


removal of GUIDS used for all PK's, FKs & Clustered indexes

(Moving discussion to answer)

First of all, you have to ask yourself: Why go after GUID first (if at all)? You could end up spending a lot of time rearranging tables and gain no benefit at all.

What do we know:

You state that the queries are generated by NHibernate. This indicates that you are an OLTP system.
Your major waits are CXPACKET and PAGEIOLATCH
Query plans are not doing what you want them to do

None of the above indicate that changing away from GUID is the best thing to do here. Here is why:

First, while moving away from GUID might reduce fragmentation - this has no effect on your query plans (Statistics has). As a matter of fact, it may even make things worse since you will now have to deal with the ascending key problem described here: http://blog.kejser.org/2011/07/01/the-ascending-key-problem-in-fact-tables-part-one-pain/ which will cause your parallel queries to run single threaded.

Second, the presence of CXPACKET as a high wait indicates that you are running a lot of parallel queries. This indicates that you are getting wrong estimates or are lacking indexes - GUID's wont help with that either (and lowering DOP is not the answer here)

Third: Even if you did manage to switch to GUID and using your 20M row example, how much would you actually save? Every row would be 12B bigger (unless you use a heap of course, but that has to be understood well first). There are 20M row them - this is 240MB of RAM (assuming of course, that the pages are full, which they only will be on the keys, not the secondary keys). 10 times that is just over 2GB, which today costs less than one hour of quality DBA consulting. In other words: trying to squeeze more space out of the pages is throwing money and your precious time out the window.

In your situation, it sounds like you need to collect some data from sys.dm_exec_query_stats and try to find out where the poor performance is really coming from. Since this is NHibernate, I would look out for:

Poorly parameterised queries (can be fixed with simple hints or better stats)
Over indexing or lacking good covering indexes
Lacking join conditions
Optimiser chosing hash join when loop would be better (you can use OPTION (LOOP JOIN) temporarily to see if a good loop join plan is even possible)

The best way to remove PAGEIOLATCH is not to squeeze more into memory (unless the database is very small or you can just buy cheap RAM) - it is to avoid doing bad table scans in the first place.


trying to connect to MSSQL server using named pipe in HeidiSQL

Try following:

Enable Named pipes visa configuration manager

Restart particular SQL Service
Have a look at SQL log (recent entries after restart), it must state the named pipe is ready without any error (as showed screenshot below)

If SQL error log looks good, try again with named pipe, make sure the hostname match with named pipe (configured in step:1)

If still no luck, you may proceed with TCP/IP protocol, just make sure you are able to do telnet localhost 1433 to be sure the TCP/IP is enable and ready for connections. If any issue, you can follow Step 1 and 2 for enabling TCP/IP, and add a New Rule at Windows Firewall to accept incoming connections on SQL Port# (default: 1433).

Additional steps for custom port configuration


Indexes Size 20 times bigger then the table itself and very slow queries

Pre-9.0 VACUUM FULL

You're on PostgreSQL 8.4 or older, where VACUUM FULL tends to bloat indexes. See this wiki page for details.

Don't run VACUUM FULL as a periodic maintenance task. It's unnecessary and inefficient. This remains true on current versions, it's just not as bad on 9.0 and above. If you feel the need to run VACUUM FULL regularly then you probably don't have autovacuum turned up far enough and are having table bloat issues. In fact, unless you've changed the FILLFACTOR on the table from its default 100 a VACUUM FULL is quite counter-productive; it'll compact away all the free space in the table, so following UPDATEs will have to extend the table.

Table extensions are currently one of the poorer performing operations in PostgreSQL, as they're controlled by a single global lock. So if you have tables that fluctuate in size, you really want to avoid constantly compacting and truncating them only to extend them again.

On some unusual workloads it can be worth running a periodic CLUSTER, which orders the table based on an index and effectively REINDEXes it. If you do many UPDATEs on the table should set a lower FILLFACTOR for efficiency.

If this table is being emptied and re-populated regularly, you should generally using TRUNCATE  followed by COPY to fill it back up. If it's big, drop the indexes before the COPY then re-create them afterwards to produce indexes that are more compact and faster and to speed up the data load.

For one-off mitigation, CLUSTER the table or REINDEX it.

8.1?!?!

After edit added version: Holy bleepazoids, batman. 8.1.18? Forget what I said about autovacuum, autovacuum in 8.1 was way too ineffective. Upgrade to a sane version ASAP. You're not even on the current point release of 8.1, 8.1.23, from December 2010. 8.1.18 was released in September 2009! You need to begin your upgrade planning ... well, about two years ago, preferably. Read the release notes for every .0 version between 8.1 and the current release, focusing on the upgrade notes and compatibility notes. Then plan and execute your upgrade. If you don't feel up managing that on your own there are people who'll help you with it (I work for one of them) but honestly, the release notes and docs are quite sufficient for most people to do an upgrade themselves without undue pain.

Moving from 8.1 to 8.3 or newer will be your biggest pain point, as PostgreSQL 8.3 dropped a whole bunch of implicit casts that lots of potentially buggy SQL relied on. You'll need to test your application carefully on the newer version. Other changes to be aware of are:

The removal of implicit FROM and in later versions removal of the backwards compatibility parameter for it;
UTF-8 validation improvements in newer versions that can cause older dumps to fail to load until the data is corrected;
The change to standard_conforming_strings by default;
The change of bytea_output to hex


Clustered columnstore index on small tables

Columnstore index has a big advantage on compress data size. The general aim of the Columnstore index is quickly read a bunch of data due to its compression.

CCI is Columnstore Clustered Index, Clustered is Clustered Index
Columnstore Index compresses data size from 4MB to 2MB.
We can look at the performance in two tables and three parts.
The first one is the minimal SELECT operation:
SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333

SELECT * FROM Users_Clustered WHERE Id=12333

Result: There is a Columnstore Scan here and wrong estimated. Further, logical read difference. You can say it doesn't matter for you, but, If you use a minimal SELECT query, probably use thousands of times. And it will affect total performance.
The second one is the minimal UPDATE operation:
UPDATE Users_CCI SET Age=10 WHERE  Id=2

UPDATE Users_Clustered SET Age=10 WHERE  Id=2

Result: There are read, CPU, and time differences as we can see.
The third one is the REBUILD operation:
USE [StackOverflow2013]
GO
ALTER INDEX [CCI_Users] ON [dbo].[Users_CCI] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
USE [StackOverflow2013]
GO
ALTER INDEX [PK_Users_Clustered_Id] ON [dbo].[Users_Clustered] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE Users_CCI SET Age=10
UPDATE Users_Clustered SET Age=10

If I rebuild and update all rows for index fragmentation, I will see a more fragmented Clustered Columnstore Index than the Clustered Index. And I didn't show, but the Clustered Columnstore Index Rebuild process produces transaction log more than Clustered Index.

Like the document says

More than 10% of the operations on the table are updates and deletes. Large numbers of updates and deletes cause fragmentation.The fragmentation affects compression rates and query performance until you run an operation called reorganize that forces all data into the columnstore and removes fragmentation. For more information, see Minimizing index fragmentation in columnstore index.

If you have a small table you don't need to Columnstore index.


Is MySql locking table during backup and how to avoid?

Have you checked Run MySQLDump without Locking Tables question?

Everything boils down to the way you're doing data backups.


Completion of ERD, uncertainties about generalization/specialization and primary keys

I think you're creating more tables than necessary.

First, why do you need a table for "bridal couple"? Why not just include the bride and groom fields in wedding_booking table? The only reason to break this out into a separate table would be if you expected to book multiple weddings for the same couple, which seems rather unlikely. If they're getting re-married, it's probably not a second wedding to each other.

Why have tables for "bride" and "groom"? All these do is serve as an extra stepping stone to get from bride and groom id's to their names. Why not just have wedding_booking have fields for bride_name_id and groom_name_id?

Why is name separate from client? Why not just put the name fields in the client table? Okay, you also use name for the employee who does the booking. Really you need an employee table and a client table, with each including a name. With just two fields making up the name, there's little gained by breaking this out into a separate table.

I don't see any reason for room_booking to be a separate table. Booking should simply point to a room.

"Plaza" and "Ballroom" are not fields in suite. They are two different records. Maybe there's "description" that can have the value of "plaza" or "ballroom".

If I understand your intent, every wedding_booking must be linked to a booking. So there's no need to have a client_id in wedding_booking as it's already in booking.

Likewise staff_member in booking appears to be the same thing as booker_id in wedding_booking. It belongs just once, in booking.

Participants, delegates, and guests all appear to be the same idea: how many people are attending this event. I'd combine these into a single field in event_booking.

If a conference can use either a meeting room or a suite, and possibly multiples, it gets awkward to break these into two tables. I'd combine meeting_room and suite into a single table. If weddings and theme nights can book meeting rooms, fine, add a flag in there that says which it is, and then suites are available to all three types of events while meeting rooms are available only to conferences.

Many database designers would challenge me on this, but: I'd seriously consider combining theme night, conference, and wedding into a single table and having a type flag that indicates which it is. Most of what you need to record for these events is the same: how many people are coming, what room or rooms are they using, who's name is it under. A few fields only apply to one or the other, like conferences probably don't have flowers. But if somebody organizing a conference said they wanted to have flowers, would you tell them they can't because it's not a wedding? A conference or a theme night could have an open bar, I would think. Etc. My point is, there's no point limiting the services your hotel can offer to just certain types of events because you think they are unlikely to be wanted at other types of events -- unless there's really some reason why you just can't do it, legal restrictions or something. Also, if you later come up with another type of event you might book -- a birthday party or a bar mitzvah or a politician's victory party or whatever -- you just add another type, you don't have to struggle with creating a whole new table. You can even toss in an "other" type to allow for it so you don't have to scramble to reprogram the first time it happens.

Well, that's probably enough comments for the first round. :-)


Does SQL Server full restore set the differential base?

The differential backup is always associated with the last full backup. It is that simple.

So any differential restore is looking for a certain full restore.

So yes, the base is reset. You can't mix and match.

Personally, I wouldn't have monthly full backups and hourly diffs. The frequencies don't match (example: weekly full, daily diff, hourly log). And I wouldn't use any binary file diff rubbish for my backups because I expect to be able to restore them.


Since database server was rebooted while restore process was still going on so the restore process did not completed successfully.Restoring database with recovery is not going to work it will flash an error. What you have to do is

Open new query window, connect to master database and drop the database which is in restoring state by drop database db_name;
Now start the restore process again.

You should have waited to restart the machine after it prompted for reboot. I am sure it would have prompted but you would have selected for reboot then only it went ahead and rebooted. Avoid rebooting SQL servers when restore process is going on this would disrupt restore process and you would have to start all over again, which is waste of time.


What is meant by "Stand-Alone Instance of SQL Server"

Stand Alone:

  Operating, functioning, or existing without additions or assistance; independent; able to be separate or separated.

And that's precisely what is referring to on that article, to a self-sustained SQL Server instance, not participating (as Aaron commented) on a Failover Cluster or a WSFC. That's why the article refers to some scenarios where renaming there computer is directly forbidden or it could wreak havoc as it's not a "stand alone" but part or some other process.


Poor SSRS Rendering performance in SharePoint 2010

This was related to two things:

The web servers of the SharePoint farm had no external web access, so the root certificate revocation was failing (adding a delay of 15-20 seconds to paging and activity) - This was resolved by giving them access to the external web via a proxy.

The issue with extra overheads when using SSRS integrated into Sharepoint is pretty well documented and still seems to be a problem - I guess that's part of why Microsoft are disabling the Add-In in the future.


MariaDB/mySQL is there a way to limit a user to SELECT and SHOW queries?

Use GRANT to give them the exact authority that you would like them to have. It sounds like they may only need SELECT. For global authority use:

GRANT SELECT ON *.* TO 'supportmember'@'localhost';

Or limit the authority to a specific database using:

GRANT SELECT ON specificdatabase.* TO 'supportmember'@'localhost';

Or to a specific table in a specific database:

GRANT SELECT ON specificdatabase.specifictable TO 'supportmember'@'localhost';


How to determine which SQL Server user or database overuses server resources

There's a ton of dynamic management views (DMVs) you can use to get the data, but the easiest way is to use Adam Machanic's sp_WhoIsActive stored proc.  Here's a video on how to use it:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

And you can download it from here:

http://whoisactive.com/

The output includes columns for the database name, CPU cycles used, query duration, and more.


SQL Server 2005 Transactional Replication

SQL Server Replication isn't a good way to handle a database upgrade.  A much better approach would be to take a full backup and restore then do your testing.  After completing your testing you do a full backup and restore, then perform log restores until you are ready to cut over the database.  After you take the production system offline you finish the log restores then bring the database online.


repmgr 3.0.4 and postgres 9.4 on ubuntu (ERROR: replication slot "repmgr_slot_2" does not exist)

OK, I found the issue. I hadn't properly configured passwordless ssh login between my master and slave. So despite being able to login into my master from my slave the issue was it couldn't copy files because ssh wold require a password.

My only issue is error reporting is horrible for repmgr.

Following this guide I missed a very small step. Always make sure you test the login of ssh between both master and slave for passwordless login to ensure it was setup correctly.


This is mostly a preference due to security and consistency, and has nothing to do with performance (though that may have been more of a concern in ancient versions of SQL Server).

Why use EXEC() some of the time when you should be using sp_executesql whenever you have parameters? EXEC() forces you to concatenate all of your variables into a single string, and this makes it ripe for abuse.

I wrote about this in more detail here:

Bad Habits to Kick : Using EXEC() instead of sp_executesql

I also wrote about protecting yourself from SQL injection here:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2

SQL injection is a pretty big deal, and plenty of other people have written about it too.

Finally, be sure when you call system procedures that you use the proper casing to match what's stored in sys.all_objects - it should be all lower case. Otherwise, if your code gets deployed to a case sensitive instance, it will all start failing.


Latin1_General_BIN performance impact when changing the database default collation

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require.

Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the best performance (especially the pure code-point BIN2 versions) most implementations do not use them.

Next in raw performance terms (but only for non-Unicode strings) are the backward-compatibility SQL collations. When working with Unicode data, these collations use a Windows collation instead, with the same performance characteristics. There are subtle traps here, so you need to have good reasons to choose a SQL collation these days (unless working on a US system, where it is still the default).

Windows collations are the slowest, in general, because of the complex Unicode comparison and sorting rules. Nevertheless, these offer complete compatibility with Windows in SQL Server, and are regularly maintained to keep up with changes in the Unicode standard. For modern use that includes Unicode data, a Windows collation is generally recommended.

TL;DR

If all you want is case-sensitive comparison and sorting semantics, you should choose the _CS_ (for Case Sensitive) variation of whichever base collation provides the expected behaviour for your users' language and culture. For example, both these are case-sensitive collations:

-- Latin1-General, case-sensitive, accent-sensitive
Latin1_General_CS_AS

-- Latin1-General, case-sensitive, accent-sensitive for Unicode Data,
-- SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data
SQL_Latin1_General_CP1_CS_AS

You can see these definitions using sys.fn_helpcollations

Examples

Four tables that are exactly the same except for the collation; one binary, one case-sensitive, one case-insensitive, and one SQL case-sensitive:

CREATE TABLE #Example_BIN
(
    string nvarchar(50)
        COLLATE Latin1_General_BIN
        NOT NULL
);

CREATE TABLE #Example_CS
(
    string nvarchar(50)
        COLLATE Latin1_General_CS_AI
        NOT NULL
);

CREATE TABLE #Example_CI
(
    string nvarchar(50)
        COLLATE Latin1_General_CI_AI
        NOT NULL
);

CREATE TABLE #Example_SQL
(
    string varchar(50) -- Note varchar
        COLLATE SQL_Latin1_General_CP1_CS_AS
        NOT NULL
);

Same sample data for each table:

INSERT #Example_BIN
    (string)
VALUES
    (N'A'),
    (N'a'),
    (N'B'),
    (N'b'),
    (N'C'),
    (N'c');

INSERT #Example_CS
SELECT EB.string
FROM #Example_BIN AS EB;

INSERT #Example_CI
SELECT EB.string
FROM #Example_BIN AS EB;

INSERT #Example_SQL
SELECT EB.string
FROM #Example_BIN AS EB;

Now we want to find strings greater than 'a':

SELECT EB.string AS BIN
FROM #Example_BIN AS EB
WHERE EB.string > N'a'
ORDER BY EB.string;

SELECT EC.string AS CS
FROM #Example_CS AS EC
WHERE EC.string > N'a'
ORDER BY EC.string;

SELECT EC2.string AS CI
FROM #Example_CI AS EC2
WHERE EC2.string > N'a'
ORDER BY EC2.string;

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > 'a' -- not Unicode
ORDER BY ES.string;

Results:

╔═════╗
║ BIN ║
╠═════╣
║ b   ║
║ c   ║
╚═════╝

╔════╗
║ CS ║
╠════╣
║ A  ║
║ b  ║
║ B  ║
║ c  ║
║ C  ║
╚════╝

╔════╗
║ CI ║
╠════╣
║ B  ║
║ b  ║
║ C  ║
║ c  ║
╚════╝

╔═════╗
║ SQL ║
╠═════╣
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝

Finally...

Note though, if we use a Unicode literal with the SQL collation, the implicit conversion rules result in a Windows collation comparison:

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > N'a'
ORDER BY ES.string;

...and the SQL collation results change:

╔═════╗
║ SQL ║
╠═════╣
║ A   ║
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝


MySQL - Many to Many relationship, or one to many with additional columns

Basically you can only use
Question - TagsQuestion - Tags

With the bridge table, if one tags is set for many questions.
It wuld also save some space, if you have a lot of questions.
The sencond option has the problem, that if you wqant all tags t be displayed you need five INNER JOINs, for every Tag one, while you can make the same query with two joins, with the first option..
Overall, the first option with bridge table, is the more flexible and space consuming option.


Auto logging / tracking records changes at MS SQL

There's a few different options available to do this, and also depend on which version of SQL Server you're running. Here they are in order of least complicated to implement and most standard, in my opinion:

Temporal Tables - These generate a log table for each table you want to track changes on, with who, when, and what the change was.

Change Data Capture - Similar to Temporal Tables, but on a more global level. System tables of the same database are used to log the changes of your tables within that database.

Database DML Triggers - You can create an AFTER TRIGGER that manually logs the changes into a separate table. This is more of a homebrew solution and a little more work than using either of the first two features.

Audit - This globally tracks multiple events, a subset of which would be DML changes such as UPDATES and INSERTS.


For alignment optimized table is bigger than original table - why?

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table.

That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle room for UPDATEs to place updated row versions on the same data page, which allows for better performance. There is also a cost to shrinking and growing the physical table of a relation. Plus, VACUUM FULL takes out an exclusive lock on the table.
That's why autovacuum only runs VACUUM (and ANALYZE) not VACUUM FULL.

Read-only (or mostly-read) tables are best kept to their minimum size, though. And excessive bloat after changes to the table definition (or for some other reason) is best removed immediately, too.

Try VACUUM FULL on both versions of the table and measure again. The difference should show.

You can also try my query with multiple tests for more information on row / table sizes.


What is the privilege for account unlock in Oracle

you have not shown the roles TEST_USER is granted.  Likely they have been granted the DBA role which allows ALTER USER.

see the documentation for details.  This query will show the roles a user has

SELECT * FROM DBA_ROLE_PRIVS where grantee = 'TEST_USER'

of course this requires privileges too, unless you have the CATALOG role, if you select from this table you can alter a user


Generate random timestamp taking into account shift to daylight saving time

You can create timestamp in SYSTEM timezone with:

FROM_UNIXTIME(timestamp_in_seconds) -- This is a number, not a datetime

To convert back to a timestamp you can use UNIX_TIMESTAMP function.

This sample demonstrate how to generate random datetime based on starting point. It give you only correct timestamps:

select FROM_UNIXTIME(UNIX_TIMESTAMP('2016-03-27 00:30:00') + rand() * 10000);


postgres user cannot access location of new data directory

I figured it out. So the permissions at one level of the directory hierarchy did not have any setting for letting the other group read that level. Thus that blocked the postgres user from being able to see a drive mounted by any other user. Once that was changed with

chmod -R 777 /media/$USER

Then this created permissions for the other group to see the directory. This fixed the issue.


I've done this for attorneys in the past. I used an ISO-style date format (yyyy-mm-dd) stored as char(10). Any missing part used question marks.

2002-01-?? -- some day in January 2002
199?-??-?? -- some day in the 1990s
????-01-08 -- January 8th in an unknown year

Values like these are intuitive to people doing data entry, they sort fairly sensibly, and the format can be controlled with CHECK constraints. You lose date and time arithmetic, but when you have unknown dates that usually doesn't matter too much.

  I might know the date lies in an open or closed interval.

That's actually a different kind of information than, say, knowing that something happened on April 1, but not knowing which year. Off the top of my head, you could store four columns.

starting date
ending date
whether the starting date is or is not in the interval
whether the ending date is or is not in the interval

I'd expect to have to store that kind of information separately from the kind of data I mentioned first. "January 8th in an unknown year" would be particularly troublesome to represent in either an open or closed interval.

The latest release of PostgreSQL (9.2) includes support for range types.


At almost 3 million rows in the postmeta table and almost 250,000 rows in the posts table, it sounds like you have long-ago outgrown WordPress.

The custom post types and custom taxonomies in WordPress were developed as an after-thought so the implementation won't be that efficient. (A lot of WordPress isn't very efficient, but that's another thing altogether.)

Rather than building a "hack" to work around the WordPress "hack" that is custom post types and custom taxonomies, I would suggest moving the client and meal data out of the posts and postmeta table and moving them into a custom table(s). You can still use WordPress as the CMS for the site (and even use their database classes and helpers), but you'll gain a lot of efficiency by creating your own tables and PHP code to manage this information instead of trying to make it work in tables that weren't designed to hold that data...

One example that comes to mind is the GigPress plugin, which creates its own tables to store event/artist/venue information, not the posts and postmeta table.

The biggest problem I see with trying to modify or partition the native WordPress tables is that whenever WordPress is updated, it could/would break your changes. You don't want to put yourself in a position where you have to spend lots of time just to update to a newer version of WordPress.


Why does the following query not use b.a's index?

Because the index only stores the UUID value of a, not the result of the expression a::text
To make the index eligible for use, you would need to cast the other column to the data type stored in the index, e.g.
select *
from b
   join c on b.a = c.a::uuid;

Note that this doesn't guarantee that the index is used, it just makes it possible.
But as the query retrieves all rows from both tables, it's highly unlikely that the index is used anyway.


I think this might work, assuming that by not present you mean is NULL:

select * from subscription where
coalesce ( subscription.renewed_at, subscription.created_at )
between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999')

The coalesce() function returns its first non-NULL argument, so if renewed_at is present it will be returned, otherwise created_at will be the result.

Alternatively, you could use this, which will work in case not present means something else in your table, e.g. the value 9999-01-01-00-00-00:

select * from subscription where
(subscription.renewed_at
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999')
or (
subscription.renewed_at = '9999-01-01-00-00-00'
and
subscription.created_at
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999'
)


Yes, this conforms to 3NF as far as I can see.

Basically 3NF is met when all non-key values (assuming natural keys here) in relations are non-transitively functionally dependent on the key.  Surrogate keys don't count for this analysis since category.name is not functionally dependent on category.category_id.  In fact, category.name is probably your natural key and category.category_id is functionally dependent on it for purposes of this analysis (because it is in essence a join dependency stand-in or surrogate for your natural key).  Going through your tables here I do not spot any cases where this is violated.

The one obvious change that this suggests is what was mentioned in the comments by others, to move customer_id into hire and out of hire_equipment (if third party loans of equipment are supported you probably want customer_id in both places though).


commitlog_directory and data_file_directories (use this link if you are on 3.0+) will tell Cassandra where to look for commitlog and data files (there are a few other directories you might be interested in, search through the documentation page for 'director'). However, your volumes have to be mounted first to be able to put any directories onto them.

How to mount them is not really a DB question, though.  It is described in any case in the EBS documentation.


When an application opens a file, it specifies what type of access is allowed by concurrent processes (none, read, read/write, etc.). SSIS cannot change that. The file must either be closed or the other process terminated.

Although you could programmatically identify the terminate local processes owning a file handle, that can be challenging with remote processes and/or UNC paths. Personally, I wouldn't go there. A better solution is to have users copy files to a staging location once they are ready and have the ETL process them up from there.


How to Create Insert Statement from Table Data using sp_export_data without Id?

If you are OK with using a slightly different approach (still T-SQL) go to this link and scroll down to the answer by Neeraj Prasad Sharma where he creates the stored procedure named INS.

Within that procedure you can add a where clause of something like table where id <> 1.  Here is some example code.

First create your table and test records.

CREATE TABLE testNegative
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,Col1 VARCHAR(200)
);
GO
INSERT INTO dbo.testNegative
        ( Col1 )
VALUES  ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test5' ) ;
GO

This will insert ID's 1-5 into the table.  Now say that I want to script out every row other than ID 1.   I would then run this:

EXEC INS ' dbo.testNegative where id <> 1';

This procedure has worked well for me in a number of scenarios.  I think it will do what you are looking for here.


How to know if logreader agent already exists?

Below T-SQL will give you the log reader agent. You can use the agent name or job_id to drop/delete the log reader agent.

    SELECT SERVER
    ,[command]
    ,sj.job_id
    ,[NAME]
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs ON sjs.job_id = sj.job_id
    AND subsystem = 'logreader'


ElasticSearch: is this problem suitable for ElasticSearch DB?

For your use case of key-value data, where the value is simple, MongoDB is more than you need.  It is a document-oriented data store for complex value types.  A specific key-value store would suit your needs.  RIAK is the one I've looked at though several others exist.

Since your retrieval will be by time range i.e. key range, Elastic's full-text capabilities will, again, be overhead for which you have no payback.

You don't say what your scale out requirement is, whether local write and global read or full replication of the whole data set etc. Several relational engines offer distributed features.  MariaDB has Galera, for example.  SQL Server has distributed, partitioned views. Other popular systems can afford similar capabilities.  NeoDB is relational and multi-host by design.  The relational model is based on theoretically sound principles and has decades of industry development. You would be doing yourself a disservice by discounting it out of hand.

And your data does have structure, even if it is a simple structure. You have a primary key (device & time) and a functionally dependent attribte.  Whether you define this declaratively in a schema or implicitly in your program's data structures, it still exists.


Master slave replication in mysql seconds behind master goes on increasing

Just check if you have same time and timezones on both the servers, i.e., Master as well as Slave.

++++++++
Here is an idea

In order for you to know that MySQL is fully processing the SQL from the relay logs. Try the following:

STOP SLAVE IO_THREAD;
This will stop replication from downloading new entries from the master into its relay logs.

The other thread, known as the SQL thread, will continue processing the SQL statements it downloaded from the master.

When you run SHOW SLAVE STATUS\G, keep your eye on Exec_Master_Log_Pos. Run SHOW SLAVE STATUS\G again. If Exec_Master_Log_Pos does not move after a minute, you can go ahead run START SLAVE IO_THREAD;. This may reduce the number of Seconds_Behind_Master.

Other than that, there is really nothing you can do except to:

Trust Replication
Monitor Seconds_Behind_Master
Monitor Exec_Master_Log_Pos
Run SHOW PROCESSLIST;, take note of the SQL thread to see if it is processing long running queries.
BTW Keep in mind that when you run SHOW PROCESSLIST; with replication running, there should be two DB Connections whose user name is system user. One of those DB Connections will have the current SQL statement being processed by replication. As long as a different SQL statement is visible each time you run SHOW PROCESSLIST;, you can trust mysql is still replicating properly.


how to grant select access my sql server

It does not make sense to be changing your own rights.   If you already have rights you do not need to grant them. If you do not have the desired rights, you should not be able to grant them to yourself.  (Nor should you be able to deny yourself rights.)

However, it is possible that you apparently should have rights to some object, but you do not.  In that case, check to see if your account was directly denied access to that object or if you are in a group or role that has been denied access.  DENY always overrides a GRANT.

Just to flesh out the limits to changing permissions, there are other rights that cannot be changed, such as:

sa - operates outside the security system, can do everything on the server
dbo - can do everything in the database
entity owner - owns an object or objects and therefore has full rights to them
information_schema - system defined ownership
sys - system defined ownership

More:  If you are an administrator and a group membership is denying you rights, you may be able to modify the group's rights or its membership.  Otherwise, you need someone with GRANT rights to assist you.


The called SP in the remote server should have "set nocount on" at the top. This will solve the issue; provided you have all the permissions required set.


I've seen similar problems addressed by decoupling the app from the central server:

Remote sites install a local SQL Server Express along with the app
Apps talk to the local Express instance. Low latency, good availability.
SQL Server Express uses Service Broker to deliver the updates to the central server
Service Broker handles the network availability, latency etc.
The central server uses Service Broker activation to modify the destination tables based on the updates received from the remote sites.

This way you can handle transparently pretty big backlogs when the sites experience poor network or no network. SSB will take care of the delivery (retry etc) while the remote apps continue to experience local availability times.

SSB isolates your central server data (transactions) from a slow network. There won't be any data locks while the SSB messages are being exchanged, no matter how slow the transfer.

In one incident I know a remote site that was disconnected for 42 days, kept working, and then resumed delivery and pushed all the updated in about 30 minutes once it was reconnected.

See also this presentation for a similar case.


Rebuild Indexes on SQL Server Standard edition

Depending on your version of SQL Server, you can do index rebuilds as an ONLINE operation. This is an Enterprise feature.

Failing that (ie Standard Edition etc), look at re-organizing indexes - these will keep them online. Microsoft best practice says that you should generally not do a rebuild operation unless fragmentation is over 30%. You should always do some form of intelligent rebuilding - i.e check fragmentation and take an appropriate action. I can't recommend Ola Hallengren's maintenance solution enough for dealing with this.

https://ola.hallengren.com/

If you are worried about the blocking effect of index rebuilds, I would recommend this sort of approach.


Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

Casts from text to timestamp without time zone are handled by calling the type input function timestamp_in, which is STABLE.

The reason is that timestamp_in supports some other formats too:

SELECT 'now'::timestamp;

         timestamp
----------------------------
 2019-10-09 09:53:32.026673
(1 row)

If you know that your input is always an ISO timestamp and this cannot happen, define your own conversion function:

CREATE FUNCTION text2ts(text) RETURNS timestamp without time zone
   LANGUAGE sql IMMUTABLE AS
$$SELECT CASE WHEN $1 ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d+)?$'
            THEN CAST($1 AS timestamp without time zone)
       END$$;

You can use that in your queries and indexes.

If you lack paranoia and are sure of your data quality, you can omit the test. That will improve performance, but you risk index corruption if somebody sneaks something like now into the data.


Mysql Cluster Ndb Restore Foreign key constraint violated

With any more complex data it is almost impossible to restore data in MySQL Cluster in one step. Usually one needs two steps:

ndb_restore <other_options> --disable-indexes

ndb_restore <other_options> --rebuild-indexes

First step has to be run on all nodes of the cluster. Second step though - just on one node which will rebuild all the indexes.

For foreign keys you can try to split first step into two:

ndb_restore <other_options> --restore_meta

ndb_restore <other_options> --restore_data --disable-indexes

--restore_meta has to be run on one node. --restore_data on all the nodes in cluster.


How to make a column mandatory in every Insert / Update (not meaning default null)

Your check NEW.last_change_user is null in the trigger will only be true if the UPDATE statement explicitly contains a set last_change_user = NULL, not if the value did not change (then new and old are simply identical).

You can check if the value changed using if new.last_change_user is distinct from old.last_change_user, but you can not check if the value was specified at all, possibly supplying the same value that is already in the column.

Something like Oracle's updating(column_name) function would be needed here, but Postgres does not have that.

So I think the answer to your question is: no, that's not possible.


MongoDB service not starting on RedHat - missing 'service_uri'

That sounds more like a problem with the service command than with MongoDB or the install.  Try starting it from init.d instead:

sudo /etc/init.d/mongod start


Trigger to apply user mapping to logins on database creation

Here's a very quick example that will create a user blat given an existing login blat in all new databases created (this won't work for attach/restore). You could extend this to adding them to roles, granting permissions, even building the list based off of some query from sys.server_principals in master, sys.database_principals in another database, or your own users table. Note that it doesn't have any error handling.

CREATE TRIGGER DBCreate_MapUsers
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
  DECLARE @db NVARCHAR(510);
  SET @db = CONVERT(SYSNAME, EVENTDATA().query('(/EVENT_INSTANCE/DatabaseName[1]/text())'));
  SET @db = QUOTENAME(@db) + N'.sys.sp_executesql ';
  EXEC @db N'CREATE USER blat FROM LOGIN blat;';
END


I would recommend starting with a methodology like Waits and Queues. But at 10k queries per second you should expect some CPU to burn. Whether it's more than it should be is really hard to tell just from the info you gave. The methodology linked will at least help you find bottlenecks.

The biggest bang for the buck would probably be... not to send 10k queries a second. Cache in the client. Look at How StackOverflow Scales with SQL Server and see how SE caches as much as possible in the web tier layer before hitting the server.

Just in case, do a couple of DBCC SQLPERF ('SPINLOCKSTATS') and see if anything stands out.


ORA-00904 error while querying the Oracle database table

When ORA-00904: invalid identifier occurs, we must enter a valid column name as it is either missing or the one entered is invalid. This error most commonly happens when we are referencing an invalid alias in a select statement.  Oracle's voice on ORA-00904 error:

  ORA-00904 string: invalid identifier

  Cause: The column name entered is either missing or invalid.

  Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #.
  If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

To avoid ORA-00904, column names must
begin with a letter.
consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them.
be less than or equal to thirty characters.


How can I group the null results in this FULL OUTER JOIN with non-null responses?

All this approach does in to make your original query a derived table then group by pub_id.

SET hive.auto.convert.join = TRUE;

SELECT max(DEAL_ID) as DEAL_ID
     , EXCHANGE_ID
     , PUB_ID
     , sum(INCREMENTAL) as INCREMENTAL
     , sum(FIRST_IMPS) as FIRST_IMPS
     , sum(SECOND_IMPS) as SECOND_IMPS
  FROM (

    SELECT First.deal_id
         , COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
         , COALESCE( First.pub_id, Second.pub_id ) as pub_id
         , COUNT(DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
        , SUM(First.imps) AS First_imps
        , SUM(Second.imps) AS Second_imps
    FROM (SELECT a.deal_id
               , a.exchange_id
               , a.pub_id
               , a.user_id
               , 1 AS imps
            FROM logs a
           WHERE a.deal_id >= 1
            AND a.event_type = 'TRUE'
         ) First
FULL JOIN (SELECT a.exchange_id
                , a.pub_id
                , a.user_id
               , 1 AS imps
            FROM logs a
           WHERE a.deal_id = 0
             AND a.event_type = 'TRUE'
          ) Second
      ON (   First.exchange_id = Second.exchange_id
         AND First.pub_id = Second.pub_id
         AND First.user_id = Second.user_id
         )
   GROUP BY First.deal_id
          , COALESCE( First.exchange_id, Second.exchange_id )
          , COALESCE( First.pub_id, Second.pub_id )

  ) group by pub_id, exchange_id

;


postgresql set variable not working with command option

It's not a bug, as the psql documentation warns about this:

  -c command
  ...

  command must be either a command string that is completely parsable
             by the server (i.e., it contains no psql-specific features), or a
             single backslash command.

The expansion of :'env' in -c "SELECT :'env';" is a psql-specific feature.

I would suggest that the solution is to use heredoc rather than -c:

$ psql -v env=test <<END_OF_SQL
select :'env';
END_OF_SQL

Output:

?column?
----------
 test
(1 row)


After uninstall oracle, there is remaining directory. 'tfa'

TFA is a seperate installation. It is not part of the database home. Uninstalling the database home does not automatically uninstall TFA.
It is installed while executing root.sh, but it is a seperate installation.
No. Uninstall it with $ORACLE_BASE/tfa/$(hostname -s)/tfa_home/bin/uninstalltfa.sh, as root user.
None.


I need to insert data from a column from another table with different data type and the value is based on a lookup table

You are not explaining very well what you mean by import (whether it is an insert or an update or a little bit of both), so it is hard to suggest a close enough prototype query for you to use directly or with minimal tweaking. However, the key issue, as I understand it, is to convert each nvarchar(10) value of BOND.MOODYS_RATING to a corresponding int key, looking up each string in ValidCreditRatings.CreditRating and taking the key from ValidCreditRatings.KeyValidCreditRatings.

That operation takes a simple inner join:

SELECT
  b.*,
  vcr.KeyValidCreditRatings
FROM
  BOND AS b
  INNER JOIN Lookup..ValidCreditRatings AS vcr
    ON b.MOODYS_RATING = vcr.CreditRating
WHERE
  vcr.KeyInstnRatingAgency = 108462
  AND vcr.KeyValidCreditRatings <= 21
;

The KeyValidCreditRatings column in the output of this query will contain the values that you can write to LCDOffering.KeyValidCreditRatingsMoody.

A join can be used both in an UPDATE statement and in an INSERT statement (the INSERT ... SELECT variation). Use the join in the above example query as a basis for the actual query that will be importing data, adding other filters and/or joins as necessary and putting the vcr.KeyValidCreditRatings reference where the source for LCDOffering.KeyValidCreditRatingsMoody should go. By the latter I mean, if for instance, yours is an UPDATE statement, then you will probably have in the SET clause an assignment like this:

UPDATE
  LCDOffering
SET
  ...
  KeyValidCreditRatingsMoody = vcr.KeyValidCreditRatings,
  ...
FROM
  ...

And if it is an INSERT ... SELECT statement, then you will have vcr.KeyValidCreditRatings in the select list at the position corresponding to the KeyValidCreditRatingsMoody column's position in the target column list:

INSERT INTO
  LCDOffering (..., KeyValidCreditRatingsMoody, ...)
SELECT
  ..., vcr.KeyValidCreditRatings, ...
FROM
  ...


@Ypercube's comment basically answers your core question.

But your function is also much slower and more complicated than necessary:

CREATE OR REPLACE FUNCTION find_days_business(_reception_duration int
                                            , _department_id bigint
                                            , _service_id bigint)
  RETURNS TABLE (avail_records numeric
               , busy_records bigint
               , percentage numeric
               , day_date date) AS
$func$
BEGIN
 RETURN QUERY
 SELECT s.sum_diff, n.ct, ((n.ct * 100) / s.sum_diff ), s.date
 FROM  (
    SELECT p.date, sum(p.diff) AS sum_diff
    FROM  (
       SELECT w.date
            , ceil(((max(w.time_to) - min(w.time_from)) * 60) / _reception_duration) AS diff
       FROM   workingtimetable w
       WHERE  w.date >= now()::date
       AND    w.department_id = _department_id
       -- another restriction IF _service_id is not null:
       AND   (_service_id IS NULL OR <another restriction>)
       GROUP  BY w.user_id, w.date
       ) p          -- perday
    GROUP  BY p.date
 ) s                -- sum_of_avail_records
 LEFT   JOIN (
    SELECT d.date, count(*) AS ct
    FROM   appointments_book_records r
    JOIN   appointments_book_days    d ON r.appointment_day_id = d.id
    WHERE  d.date >= now()::date
    GROUP  BY d.date
 ) n USING (date)   -- num_of_busy_records
 ORDER  BY s.date;
END
$func$  LANGUAGE plpgsql;

Major points

Temporary tables are more expensive and not necessary here. Simple subqueries do the job.
Don't include columns in the SELECT list that you don't need for anything. Dead weight. (You also don't need to include GROUP BY expressions in the SELECT list.)
Don't ORDER BY in subqueries. While Postgres typically keeps the order in the outer SELECT for simple queries, this is completely arbitrary and not reliable. There is no provision in the SQL standard that would require propagation of ORDER BY in subqueries to the outer query.

If your are looking for fully dynamic query predicates or arbitrary dynamic query elements with optimized query plans, you'll have to use dynamic SQL and EXECUTE:

PostgresSQL dynamic execute with argument values in array
Robust approach for building SQL queries programmatically


GROUP_CONCAT with ORDER BY , but results are not ordered

How to Order GROUP_CONCAT() Values in MySQL

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' ORDER BY priority)
FROM project_photos
WHERE project_id = projects.id
) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5;


psql with '-c' option or '-f' option is not terminating properly

I need to press the q key as required by less or man for the execution to finish and return to the command prompt?

Sounds like your terminal's pager is taking over the output: you can tell psql not to send its output to the pager when you're using the -c 'select... ' invocation via:

psql --pset pager=off -d database -c 'SELECT * from TABLE'

You may also put the line pset pager OFF in the file ~/.psqlrc, however it seems ~/.psqlrc directives are honored only in interactive mode, not for commands passed in via -c '...'. More great tips for non-interactive psql usage from Peter Eisentraut.


Same record shows up multiple times in ordered paginated query

I have some bad news for you: According to MySQL, IT'S NOT A BUG !!!

There are two bug reports on this behavior and is considered non-critical

MySQL 5.6 (Bug #69732 : LIMIT clause results in duplicate data across pages)
MySQL 5.5 (Bug #65307 : Problem with order by and limit offset)

Here is the rationale expressed in Bug #69732

  Without a distinct ORDER BY the result order is undefined. Period.

  Even if the same query, executed twice, results results in different order there is nothing wrong with that. You may be used to what looks like deterministic results but that is actually an illusion. It may be what you see in the usual case but there is nothing that guarantees this. Result order can change as statistics get updated, as index trees are reshuffled, as data is partitioned across different physical machines so that result order depends on network latencies ...

  So if you want pagination then make sure you ORDER BY on something guaranteed to be UNIQUE, or live with the fact that the sort order of identical values in a non-unique sequence is not deterministic and can change at any time without prior notice.

In your particular case, the online field is probably not distinct enough. Even if you index bbr_bar by (country_id,online), this may still be not distinct enough. You may have to involve another column to drive the sort order (perhaps an auto increment field or a timestamp field).

Give it a try!!!!


What is the HY number (HY000) in MySQL Error messages?

MySQL reports with ODBC error numbers which include a SQLSTATE. HY000 is a MySQL ODBC extension for there being no ODBC equivalent error number from the docs on Server Error Codes and Messages

  A five-character SQLSTATE value ('42S02'). The values are taken from ANSI SQL and ODBC and are more standardized. Not all MySQL error numbers have corresponding SQLSTATE values. In these cases, 'HY000' (general error) is used.

You can find the HY000 Error Codes in error_code.h, and the full list of authoritative ODBC error codes from the Microsoft ODBC Appendix

The 2 "No such file or directory" is operating system specific.


I think you want something like this:

SELECT
  v.visit_id,
  v.tvp_id,
  LEFT(date_range, 4)::INT,
  RIGHT(date_range, 4)::INT
FROM tvp.visit v
WHERE  date_format = 'DMY' AND date_range NOT LIKE 'Pre%';

To answer your question I did the following:

Created a table:

CREATE TABLE my_range(the_range VARCHAR(9)); -- just the field of interest!

Some sample values:

INSERT INTO my_range VALUES ('1969-1986');
INSERT INTO my_range VALUES ('1932-1987');
INSERT INTO my_range VALUES ('1956-1999');

Query:

SELECT LEFT(the_range, 4)::INT, RIGHT(the_range, 4)::INT FROM my_range;

Result:

Left Right
1969 1986
1932 1987
1956 1999

See the dbfiddle here.

You can check that they are now integers by running:

SELECT LEFT(the_range, 4)::INT + 20, RIGHT(the_range, 4)::INT - 20 FROM my_range;

And you can see that addition and subtraction are indeed performed on the new values in the fiddle.


Splitting a string with a recursive CTE is usually a bad choice but this is a case where I at least would consider a solution using that.

If you used a faster string split technique you would have to preserve the order of items and rebuild parent_category using for xml. That might still be faster than using a recursive CTE but here is the recursive stuff anyway.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  category_path nvarchar(max)
);

insert into YourTable values
('RootCategory\Middle Category\Child Category\Child of child category'),
('RootCategory\Middle Category\Other Child Category\');

Query 1:

with C as
(
  select left(category_path, charindex('\', category_path+'\') - 1) as category_name,
         stuff(category_path, 1, charindex('\', category_path+'\'), '') as category_path,
         cast(null as nvarchar(max)) as parent_path
  from YourTable
  union all
  select left(category_path, charindex('\', category_path+'\') - 1),
         stuff(category_path, 1, charindex('\', category_path+'\'), ''),
         coalesce(parent_path+'\', '')+category_name
  from C
  where category_path <> ''
)
select distinct
       category_name,
       parent_path
from C;

Results:

|           CATEGORY_NAME |                                 PARENT_PATH |
-------------------------------------------------------------------------
|          Child Category |                RootCategory\Middle Category |
| Child of child category | RootCategory\Middle Category\Child Category |
|         Middle Category |                                RootCategory |
|    Other Child Category |                RootCategory\Middle Category |
|            RootCategory |                                      (null) |


I had a similar issue, though I was using DMS to migrate data from S3 to an (RDS) SQL Server 2008. I figured out what my issue was, I didn't require SSL on the SQL Server endpoint. I additionally had to make sure my IAM policy had the right level of access to the services. Hope this helps.

Additionaly:

  AWS DMS currently does not support SQL Server Express as a source or target.

Reference: Troubleshooting Migration Tasks in AWS Database Migration Service (AWS Database Migration Service | User Guide)


Keyboard shortcut to execute statement at cursor in pgAdmin

Since there seems to be no way to define such a keyboard shortcut, I filed a feature request: https://redmine.postgresql.org/issues/1439 , which got rejected:

Issue #1439 has been updated by Dave Page.
Status changed from New to Rejected
We follow the pgAdmin 3 behaviour in this regard - when you execute a query (or explain etc), if any text is selected, only that text is executed. If nothing is selected, the entire query (or set of queries) are executed.

Since amazingly with pgAdmin one has to login to view issues / feature request, here is a screenshot:


Help me about syntax STUFF (SELECT... for XML PATH)

Based off of your examples queries, this should work out for you (Comments Included):

-- works
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT
                            ', ' + p.pname
                FROM        dbo.products AS p
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid --Join the products to the Order Details...
                WHERE od1.oid = o1.oid  -- Only for the "main" orders
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name], -- remove the first comma and space.
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid
GROUP BY    o1.oname, o1.oid;

However, your expected output doesn't have any Product C so maybe you don't want to show them. In which case, you would need to join back and restrict it away:

-- without Product C?
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT
                            ', ' + p.pname
                FROM        dbo.products AS p
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid
                WHERE od1.oid = o1.oid
                AND p.pid <> 'p3'
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name],
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid

-- Add this join to remove Product C.
INNER JOIN  dbo.products AS [p1] ON p1.pid =o2.pid
                AND p1.pid <> 'p3'

GROUP BY    o1.oname, o1.oid;

But then you're expected output doesn't have Order 3, so you'd need to restrict that away as well. However I'm not going to do that as you should have the info you need to do that yourself.

Best of luck!


How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

If you intend to use PostgreSQL's built-in streaming replication, then you seed the replica with a base backup - a file-system level copy of the original database. So it already has all the tables and data from the master. pg_basebackup is usually used for this.

Streaming replication reads changes at the block level from the write-ahead logs, so the databases are kept physically the same. Since DDL is, just like DML, recorded in the WAL, DDL and DML are both transparently replicated.

See the documentation on HA and replication in PostgreSQL for more information.


You can use CONCAT function to add this two strings on this way:

create table video(link varchar(200), title varchar(200));

insert into video values ('y_LjhCEhWiKXk', 'Bruno Mars - Just The Way You Are [OFFICIAL VIDEO]');

select title,
       if(substr(link, 1, 2)='y_',
          concat('https://www.youtube.com/watch?v=', substr(link,3)),
          concat('https://vimeo.com/', substr(link,3))) as link
from   video;

title                                              | link
:------------------------------------------------- | :------------------------------------------
Bruno Mars - Just The Way You Are [OFFICIAL VIDEO] | https://www.youtube.com/watch?v=LjhCEhWiKXk

dbfiddle here


The error message is quite straightforward. You are trying to shrink a permanent tablespace using a command to shrink temporary tablespace.

12916, 00000, "cannot shrink permanent or dictionary managed tablespace"
// *Cause: An attempt was made to shrink a permanent tablespace or a
//         dictionary managed tablespace.
// *Action: Check the tablespace type and issue the statement only on
//          locally managed temporary tablespaces.

Example to resize BIGFILE Tablespace.

ALTER TABLESPACE bigtbs RESIZE 80G;

Refer to Altering and Maintaining Tablespaces


How to determine/set the Java classpath for an Oracle DB?

The JVM embedded in your Oracle database only allows you to access java classes loaded inside that database: accessing external classes (from OS files) is not allowed.

First you need to find out what java classes are already loaded in your database by reading dictionary view ALL_JAVA_CLASSES. You will find that it has already 10's of thousands of classes loaded.

SQL> select owner, count(*) from all_java_classes group by owner order by owner;

OWNER        COUNT(*)
---------- ----------
MDSYS            1134
ORDSYS           1944
SYS             34358

3 rows selected.

But it is unlikely that the EBS-specific classes you want to invoke are there. So you then need to load those classes in your database.

You can do that using the loadjava command line tool, which lets you load entire jar files into your database. Or you can also use the DBMS_JAVA package, specifically the LOADJAVA() method.

Of course those EBS classes may have dependencies on other EBS or third-party classes. You will then also need to load those.

Read about it in the Java Developer's Guide (https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdev)


Install SQL Server Express without administration rights

Unfortunately, no. You are installing a program and making service and registry changes, etc. You need to run setup as an account that has local administrative rights. No workaround to this that wouldn't involve some back door into administrative rights.

Best bet is to ask for someone to temporarily give you the rights or install it for you.

Source: The Microsoft SQL Server books online article for how to install SQL Server 2008 R2Express


Upgrading from 10.2g to 11g by incrementals

Once you upgrade the destination database to 11.2, you won't be able to apply archived logs (or incremental backups) from a 10.2 database.  So no, this won't work.

It is, on the other hand, often possible to use an actual standby database to perform a rolling upgrade.  You can create the 11.2 database as a logical standby of the 10.2 database, let the SQL apply process keep the databases in sync, and then switch over to the 11.2 database when you are ready to migrate.  In a similar vein, you could also use Streams between the 10.2 database and the 11.2 database to keep the data synchronized and then switch over when you're ready to migrate.


Postgresql sql import Permission denied!

Try to move the file into /tmp folder, I hope this will work.

In PostgreSQL you run into error with having permission on the the file but not all the folders/directories before it.


Oracle: Extract Graphical Database Representation

You might try SQL Developer Data Modeller which does do graphical data models and is free - I don't know if it does reverse engineering though. http://www.oracle.com/technetwork/developer-tools/datamodeler/sqldevdm31ea-download-515132.html


SQL Server - materialised (indexed) view not working as expected

Like @sp_BlitzErik noted in the comments, you need to use the WITH (NOEXPAND) hint, like so:

SELECT something, something
FROM the.indexed_view WITH (NOEXPAND);

When SQL Server reads your query and replaces the name of the view with the definition of the view, that's what's known as "expanding" the view, so with NOEXPAND, you specifically tell SQL Server to not expand the view, but use the index that's there.

On the flip side, if there's no index (like if someone does ALTER VIEW!) the query will fail if you use WITH NOEXPAND.


Here are the answers to each of your question

  1 How the copying of binary log works?

I wrote a detailed list of the steps taken by MySQL Replication in my answer to  Is MySQL Replication Affected by a High-Latency Interconnect?.

Someone else answered that same post, but from the point of view of DB Client

  2 Is it first writes to master binary log and copied to destination log?

There are many middle steps

Master completes a SQL update (single update or group of updates (AKA group commit))
Master writes one of the following to its binary log

the SQL command if binlog_format=STATEMENT
the row changes if binlog_format=ROW

IO Thread from the Slave copies the SQL Update from Master's binary log to its relay logs
SQL Thread on slave reads updates from its relay log and processes. If log_slave_updates is enabled, then the Slave writes that SQL command to its local binary log.

  3 Is it writing on both the source and destination binary log simultaneously?

Never. Not with semisync replication, not with GTID replication either. Just look at the above steps

  4 What will be impact of MySQL performance and disk IO performance?

That depends where binary logs and relay logs are stored. If data and logs are on the same disk mount, you will both random writes for data and sequential writes for logs. Both will be competing for the same disk space. You should separate them (See my post from last year Should the Redo log on a production MySQL server be configured to another location?)


You need three changes

CHANGE #1

Add // after end

CHANGE #2

Just insert NEW.SM_NO directly into sync_log

CHANGE #3

Make it an AFTER INSERT trigger, to be sure the new SM_MO lands in student_master

--
-- Triggers `sync_log`
--
DROP TRIGGER IF EXISTS `tg_sync_id_insertbgnn`;
DELIMITER //
CREATE TRIGGER `tg_sync_id_insertbgnn` AFTER INSERT ON `student_master`
FOR EACH ROW BEGIN
INSERT INTO `sync_log`(`Sync_query`) VALUES (NEW.`SM_NO`);
END //
DELIMITER ;

Give it a Try !!!


This query is run by Management Studio. I have checked this Microsoft SQL Server Management Studio(13.0.11000.78).

In recent releases ability to connect to Azure SQL Database been added to SSMS and looks like SSMS executes this query when ever we change focus to any tab or click in SSMS tab.

So now when we switch table from session 1 (which creates blocking) to session 2, below query is getting executed for the session 1. You can check this by running DBCC InputBudder(<>).

  DECLARE @edition sysname; SET @edition =
  cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition
  = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

And hence when blocked process report captures event it get this query from Session 1.

Now we may think that why this is not the case for Session 2. But this is not case as while query in session 2 is executing, SSMS not fire above query.


Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

The limitations are listed here and do not mention VARBINARY(MAX).

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

The way to do this when the feature first came out was:

https://justdaveinfo.wordpress.com/category/always-encrypted/

  Disclosure
  I am the author of the above blog

It is easier now though either via:

SSMS https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio
Powershell https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-powershell


Oracle user locks (dbms_lock): guaranteed order of requests granted?

Yes!

There is a wait queue and the first requester is the first served.
Beware of deadlocks...