simulate sequence on mysql in preparation for move to oracle

The 'gotcha' is that in nearly every case you really don't want to take the performance and maintenance hit for triggers just to hide the pk insert.

You really should write the queries to insert sequence.nextval with the INSERT.  Do it during the migration before you regret it a few years from now.


Retrieving latest record using DISTINCT ON is slow

Why must it sort if I have an index on both carid and timestamp?
  Is there a better method to use here?

Because you don't have an index on both carid and timestamp!  You have an index on carid, and a different index on timestamp.  If you want the query to use both, you need an index on (carid, "timestamp") as ypercube mentioned.

Likewise, I'd try switching from the DISTINCT ORDER BY to a GROUP BY, and benchmarking each.

With every tuning question, try writing the query in a few different ways, and compare them against each other - how fast are they, and how much resources do they use?


SSMS connection to Availability group listener doesn't failover

What you describe is normal behavior for SSMS, at least all the times I've done failover testing.
But Management Studio is a poor tool to test this type of thing.  Best practice for an application is to have retry logic built into it that will upon error dispose of the current connection and re-establish, which will connect to the now primary side.  And SSMS does not do that.
I've never seen or caught SSMS auto-magically re-connecting to the primary side once it's connection is established.


Simple MySQL table copy from one server to another?

It sounds like your old programmer had a master -> slave setup which is a really good way to back up your whole database; any command run on the master is then replicated over to the slave. This seems to fit what you need, not sure if you can specify it for just one table. However there is a viable argument for keeping your whole DB backed up.


Dropping a group of schemas with similar name patterns

This query on the system catalog creates the necessary DDL script:
SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n')
FROM   pg_namespace
WHERE  nspname LIKE 'ceu_shard_test_merge_%';

Note the use of format() to escape identifiers if necessary.
format() requires PostgreSQL 9.1+.
Replace with quote_ident() in older versions.
string_agg() requires PostgreSQL 9.0+.
Replace with array_to_string(array_agg(...), E'\n') in older versions.
For Postgres 8.4, that would be:
SELECT array_to_string(
          array_agg('DROP SCHEMA ' || quote_ident(nspname) || ' CASCADE;')
        , E'\n')
FROM ...

Returns:
DROP SCHEMA ceu_shard_test_merge_1 CASCADE;
DROP SCHEMA ceu_shard_test_merge_2 CASCADE;
...

Which you can inspect before executing.
You can put it all into a DO command for automatic execution or create a function for repeated use. Consider this closely related answer for a complete code example:

Bug in PL/pgSQL function creation

Or this related answer on SO.
Output in psql
In response to @Stew's comment: To display unadorned text as result in psql you can use the \pset meta-command:
\pset format unaligned
\pset tuples_only

Or use the short commands \x \a


Does using the in operator affect the use of indexes?

If that happens in MySQL then it's a quirk to MySQL.  Since you asked about other databases as well I decided to test it out on my postgres database.  On a table with a little over 70 million rows it produces exactly the same explain for the following two queries (with the index):

explain select * from myschema.my_list where my_column in (232);
explain select * from myschema.my_list where my_column = 232;

which is:

'Bitmap Heap Scan on my_list  (cost=68093.11..4109456.90 rows=1656844 width=994)'
'  Recheck Cond: (my_column = 232)'
'  ->  Bitmap Index Scan on my_list_2  (cost=0.00..67678.90 rows=1656844 width=0)'
'        Index Cond: (my_column = 232)'

It doesn't diverge until I add in multiple elements in the list:

 explain select * from myschema.my_list where my_column in (232,79);
 explain select * from myschema.my_list where my_column = 232 or my_column = 79;

which is (respectively):

'Bitmap Heap Scan on my_list  (cost=172208.28..7050980.15 rows=4290987 width=994)'
'  Recheck Cond: (my_column = ANY ('{232,79}'::integer[]))'
'  ->  Bitmap Index Scan on my_list_2  (cost=0.00..171135.53 rows=4290987 width=0)'
'        Index Cond: (my_column = ANY ('{232,79}'::integer[]))'

'Bitmap Heap Scan on my_list  (cost=177390.73..7066890.07 rows=4230402 width=994)'
'  Recheck Cond: ((my_column = 232) OR (my_column = 79))'
'  ->  BitmapOr  (cost=177390.73..177390.73 rows=4290987 width=0)'
'        ->  Bitmap Index Scan on my_list_2  (cost=0.00..67678.90 rows=1656844 width=0)'
'              Index Cond: (my_column = 232)'
'        ->  Bitmap Index Scan on my_list_2  (cost=0.00..107596.63 rows=2634142 width=0)'
'              Index Cond: (my_column = 79)'

I would take his answer with a grain of salt.


Should I deploy Mongodb sharding for 50 collections?

You could use a sharded cluster to distribute the collections more evenly across your available hosts.  In general I would recommend not going beyond 5 shards (with at least 2 data bearing nodes for redundancy, plus an arbiter to break ties).

Once you have your 5 shards (or however many you end up with), you can then "pin" the collections to a particular shard with tagging.  I could explain that process in detail, but as usual, Kristina Chodorow has beaten me to it:

http://www.kchodorow.com/blog/2012/07/25/controlling-collection-distribution/

Although it is not required, and you can have a single database if you wish, I would suggest having more than a single database in order to benefit as much as possible.  Locking is at the database level, so concurrent writes will still be competing if you have more than one collection on a shard in the same database.

In fact, there is a simpler solution to this than tagging (though tags are the most scalable solution).  If you create separate databases for all of your collections (or at least a subset), you can have each database reside on a different shard and not shard the collections at all.

To explain: without enabling sharded collections, all data for a given database will reside on the "primary" shard for that database (primary shards are designated in a round-robin fashion).  Hence if you create 5 databases on a 5 shard cluster (for example) each will have  a different shard for its primary and you will have achieved a crude (but effective) distribution of load by using separate databases.  I added more detail regarding this in a previous answer.


Ideal database to store/query data submitted through dynamic user created forms

Storing the submissions is easy. Any persistence software with sufficient bandwidth will do. Heck, even the OS's native file system will do. From this perspective JSON in MySQL is excessive as you have the DBMS overhead.

Similarly with an update. The system need only overwrite the current submission with the new one.

The real constraint on your design will be on retrieval. You use the word "filter." I presume you want to see all submissions with a given value in a particular form field. To do that efficiently will require indexes. Capability in this area varies widely across the various NoSQL and relational products. You must learn what each can provide, gain a good understanding of your requirements, and choose.

If you adopt a relational store you will likely gain a lot of advantage by shredding the JSON into table(s). This is how an RDBMS is meant to work. It may be difficult to settle on a schema, however, if there is great variabilty in your forms.

There may be some advantage to holding, in addition to the submissions, a separate list (document / table) for each answer and pointing to all submissions which contain that value. This may become unworkable at scale because there will be a document keyed by answer holding an array of submission IDs. When any of those submissions is updated this document will also have to be updated. This will be a bottleneck when there are a large number of updates per second. It will require careful programming to keep the submission document and answer cross-reference document in sync during updates since Mongo does not have transactions that span documents.


MySQL syntax error 1064 near IN stored procedure

MemSQL is not MySQL - see e.g. their page MySQL Features Unsupported in MemSQL. For details on their stored procedure implementation, please refer to MemSQL's documentation on stored procedures:

[USING database_name] CREATE [OR REPLACE] PROCEDURE procedure_name ( [parameter_list] )
  [RETURNS { data_type [data_type_modifier] } ] AS
  [DECLARE variable_list] [ ...n ]
  BEGIN
    procedure_body
  END ;

parameter_list:
  parameter_name data_type [data_type_modifier [ ...n ] ] [, ...]

data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

variable_list:
  variable_name type_expression [= value_expression ] ; [... ;]

As you can see, there is apparently no support for declaring parameters as IN, OUT, or INOUT. (They do however support default values for parameters, something which neither MySQL nor MariaDB support!)


Function that returns table with an additional column

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too.

The form RETURNS SETOFrettype relies on the used type to be stored in the system catalogs. The manual:

  The return type can be a base, composite, or domain type, or can
  reference the type of a table column.

Therefore, just register the row type you desire in the system (once). You can explicitly create a composite type with CREATE TYPE. Or you can do it implicitly by creating another table, view or materialized view. Even just a temporary view or table for a temporary function (dying at the end of the session). Like @Abelisto commented:

CREATE VIEW user_plus AS
SELECT *, null::bigint AS vids_since
FROM   "user"
WHERE  false;

However, SELECT * is resolved to the list of columns at creation time of the view ("early binding"). If you later add a column to the underlying table, the view and its row type are not updated and you get another type mismatch when executing the function. Trying to delete a column form the underlying table will complain about the column in the view that depends on it, though. And you have to change the view - and the function.

Your function can be simplified and faster, btw:

CREATE FUNCTION get_users_with_most_videos_since_time(_ts timestamptz)
  RETURNS SETOF user_plus AS
$func$
   SELECT *   -- effectively the same as: u.*, v.vids_since
   FROM   "user" AS u
   JOIN  (    -- aggregate *before* you join
      SELECT creator_id AS id, count(*) AS vids_since  -- note the column alias
      FROM   video v
      WHERE  created_at > _ts
      GROUP  BY 1
      ) v USING (id)  -- USING only retains one id column
   ORDER  BY v.vids_since DESC;
$func$  LANGUAGE SQL;

It actually makes sense to have this function. You want to filter by a column that's not in the result. Not possible with a plain VIEW.

Aside: I would discourage using reserved words like user as identifiers. That's a loaded footgun.


Yet another "ERROR 1005 can't create table" question

You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values.

Ref:- http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/


A Rows file stores data and is where your data and meta-data lives.
A Log file stores changes to that data.

You can't change one to the other.


Why does MongoDB give me the cannot use 'w' > 1 when a host is not replicated Error message?

You shouldn't specify w: 2 in your query. You're asking to write to two nodes. See the docs here about write concern


Setting up Oracle publishers in SQL Server replication

Well after a call to microsoft I got the problem resolved.  Turns out it was an easy fix, SQL Server had to be restarted.  It looks like sql server was not picking up the updated path variable, so it wasn't finding the oracle binaries.  Luckily, while a production server, I was able to schedule a quick restart to resolve the problem.  After that sql was able to connect to the oracle database, for the rest I was able to follow microsoft's documentation pretty closely and get the replication working.

I wanted to make sure I update this, because I didn't see anything in the documentation saying sql had to be restarted after installing the oracle client, and hopefully it will be helpful to someone else.


MySQL : "FLUSH TABLES WITH READ LOCK" started automatically

Some people use FLUSH TABLES WITH READ LOCK to lock a DB Server and perform mysqldumps. Doing this will trigger a full flush of any InnoDB table that has dirty pages in the InnoDB Buffer Pool.

Over the years, I have scripted some backups using mysqldump prefaced with FLUSH TABLES WITH READ LOCK. Within one DB thread, I would run FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);. I would then capture the MySQL ProcessID running the SLEEP function. When the mysqldump was finished, I would kill the MySQL ProcessID running the SLEEP function.

I knew some clients that manually ran the FLUSH TABLES WITH READ LOCK by hand in one session and ran the mysqldump in a separate ssh session.

Either way, FLUSH TABLES WITH READ LOCK was issued with this purpose in mind. Please look over the slow query logs for SLEEP function calls. Also, check coworkers for any crontab jobs doing backups with it or any adhoc backups requiring FLUSH TABLES WITH READ LOCK. The mysqld process would not issue that command on its own by design.


Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset

After many attempts to fix this problem, this command finally worked for me:

export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8


As you have noticed, right now this kind of "validation" is not possible in the mongodb. However, there is feature request open for such feature. If you give your 'vote' for it, maybe they make this feature. ;-)

Currently, the easiest way to archive that is do check (like that) in your application, just before inserting (or updating) document.


The issue that occurred is quorum was lost - This is a good primer on how the cluster voting works and should give you some insight into how to design a cluster to meet certain disaster scenarios.

Since you have an even number of nodes on each "side" and connectivity was lost between the "sides", the cluster didn't know who should actually own and run the resources - this is something called split brain.

Generally, we have some sort of tie breaker involved. This could be as simple as a fileshare or disk. It could be as convoluted as cluster property settings or even sites with preferences. Generally, though, for most scenarios it can be pretty basic and is discussed in the post I linked above.

If you're looking to not have this happen in the future then I'd suggest, depending on version of Windows Server, think about the scenarios you're wanting to defend against and setting the cluster to have the resources or cluster level settings needed to fend those potential issues off. A cluster does not insulate you from every possible issue but does lend a great deal of high availability to most scenarios.


You have two options:

you change the empty strings in the original data to NULLs and then change the type
you handle them while changing the type

The first one would be a simple

UPDATE table SET col=NULL where col='';

but this naturally takes time and is a bit unnecessary since you're changing the type after that.

Second way is to handle the empty strings while converting

ALTER TABLE mapdata
  ALTER COLUMN lat TYPE double precision
    USING (NULLIF(lat, '')::double precision);

The NULLIF will return NULL if the lat column is an empty string, otherwise it'll return the lat column value. This will be a lot more efficient than to first change the source data.


Here are the grants you just mentioned

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%'

Based on this, you should not be able to write to the Slave because SUPER is a system level privilege (SUPER only appears in mysql.user as Super_priv) and not a DB level privilege (SUPER does not appear in mysql.db). The first GRANT lacks SUPER privilege. The second GRANT does not have a SUPER privilege context at all.

Having SUPER privilege has a lot of firepower because you can run the following

CHANGE MASTER TO
KILL
SET GLOBAL
PURGE BINARY LOGS

Not every user needs this. Giving the SUPER privilege to just anyone can actually hamper a DBA from logging into mysql if max_connections is reached.


How to test SQL Server Standard Basic Availability Group?

Unfortunately, there is no magic switch to make Developer / Enterprise / Evaluation Editions behave like Standard (though we've been asking for that for years; this Connect item is from 2009, and it was only the first one I found). So you have at least two alternatives:

Get an MSDN subscription. Ask around; your employer (or a fellow employee) may already have a suitable subscription. To get SQL Server you only need VS Professional ($1,199 for the first year; $799 per year afterward). With this edition, you can install any number of instances of any edition of SQL Server, as long as it is only used in a dev and/or test scenario.
Assuming you already have the Standard Edition licenses for production (or can get them soon), as long as they aren't installed at the same time, you should be able to use that licensing in dev/test and then uninstall it completely before deploying to production. If you have MSDN (or additional licenses you're not using yet), you could go on this way until you use them; if not, you'd be in violation of the licensing agreement. Honestly, MSDN is a cheap and effective way to avoid this (but I know that it can bring about the temptation to use those cheaper licenses for production machines).


Does MySQL have a more private and complete catalog other then INFORMATION_SCHEMA?

It funny you asked this. Over 7 years ago (Jul 11, 2011), I answered the post What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?.

All I mention there is MySQL trying to be SQL-92 compatible, while PostgreSQL does make actual use of the information_schema (it being localized to whatever database you connected, or as I say in the post "The collapse is automatic").

Due to its open storage engine infrastructure, MySQL's exposure of functions allows for installing your own. For example, in the docs you have this:

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION reverse_lookup
    ->        RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE AGGREGATE FUNCTION avgcost
    ->        RETURNS REAL SONAME 'udf_example.so';

I have seen statements like these when installing Percona Server, and Percona supplies three commands to run (like these) to expose parts of Percona Toolkit.

Anyway, going back to your actual question: INFORMATION_SCHEMA gives you nothing than what you already know. Everything else is pluggable.


How to log multiple invoke-sqlcmd verbose outputs into single log file along with showing/not showing them through flag into prowershell prompt

For 1, you can use PowerShell redirection operators. As per Redirectable output streams, steam 4 is Verbose Stream which is what you want.

invoke-sqlcmd .... -verbose 4>> "D:\temp\yourlogfile.log"

Above will append all your invoke-sqlcmd into just one log file "D:\temp\yourlogfile.log"
your question part 2 is unclear to me.


The sys.servers data dictionary view will give you the date that the Linked Server entry was modified/created (modify_date column), but not who created it.


Is it possible to pass parameters to pt-table-checksum using an options file?

Yes, you can do this.

pt-table-checksum --config FILENAME

Where FILENAME looks like

tables                  = table1,table2,table3
ignore-tables-regex     = ^tmp
chunk-time              = .2
max-load                = Threads_running=10
chunk-size              = 100000
user                    = myuser
pass                    = mypass

If --config is not specified, pt-table-checksum will read from /etc/percona-toolkit/pt-table-checksum.conf by default.  Nearly all Percona Toolkit tools have this ability.

Details on how the config files work is available here


Potential risks of users connecting to SQL Server, through Excel, as sysadmin?

Pretty much everything.

I'd start with their potential ability to use xp_cmdshell (and sp_configure if they can't, so then they can ... and whatever the account returned by xp_cmdshell 'whoami.exe' can do....), then move onto their ability to do drop database.

Further risks include not just finance users being able to do these things, but any program on a finance machine gaining access to your sysadmin connection credentials...

(Other potential risks include the risk of discovering that one of TPTB set it up this way)


How to exclude weekends and holidays in a query

You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any.Here Sunday is set as a firstday of week, For example check below query.

Declare @startdatee as DateTime
Declare @enddatee as DateTime
Set @startdatee = GETDATE();
set @enddatee = GETDATE()+1;
select @startdatee,@enddatee,(datediff(day,@startdatee,@enddatee+1)-(2)*datediff(week,@startdatee,@enddatee))

And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.
i gave you example here.
You can use your holiday's table,here i am just using temp table for example.

Declare @holidays Table (ID int,Name Varchar(50),[Date] date)
Insert into @holidays VALUES
(1,'holiday1','2018-07-07'),
(2,'holiday2','2018-07-12')

Declare
    @startdate datetime = '2018-07-02',
    @enddate datetime =  '2018-07-30',
    @holiday int = 0

    Set @holiday = (Select COUNT(*) FROM @holidays WHERE (Date BETWEEN @startdate AND @enddate)
                    AND DATEPART(Weekday,Date) not in (1,7)
                    )

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(case datepart(dw, @StartDate) when 1 then 1 else 0 end)
  -(case datepart(dw, @EndDate) when 7 then 1 else 0 end)
  -@holiday


Check against multiple duplicate records

This would depend on the table's layout.

Suppose you have the following table

CREATE TABLE `mydata` (
    `A` varchar(19) NOT NULL,
    `B` varchar(9) NOT NULL,
    KEY `A` (`A`),
    KEY `B` (`B`)
) ENGINE=InnoDB;

Before you insert 1000 rows into mydata, you could do preload them into another table called mynewdata like this:

CREATE TABLE mynewdata LIKE mydata;
CREATE TABLE mynewdups LIKE mydata;
INSERT INTO mynewdata ... ;
INSERT INTO mynewdups SELECT * FROM mynewdata;

Next delete all rows in mynewdata that matches A or B in mydata

DELETE T1.* FROM mynewdata T1 INNER JOIN mydata T2 ON T1.A=T2.A OR T1.B=T2.B;

What's left in mydata are rows that do not have A or B matching

What about the rows that matched? Run this

DELETE T1.* FROM mynewdups T1 LEFT JOIN mydata T2
ON T1.A=T2.A OR T1.B=T2.B
WHERE T2.A IS NOT NULL;

What's left in mynewdata is data to import

What's left in mynewdups is data that had a dup key in mydata

Give it a Try !!!


Recalculate and replace large table without interrupting table reads

Two options, depending on the engine and/or the permissions you have - populate a second table, then rename that table (e.g. sp_rename 'thetable', 'deadtable'; sp_rename 'newtable', 'thetable'; drop table deadtable;), or populate a second table then have your application choose to use that table instead.


How does STRICT_ALL_TABLES work with multi-row statements and partial updates?

I agree that the split between _ALL_ and _TRANS_ needs clarification.

Earlier on that page it says

  With TRADITIONAL mode enabled, an INSERT or UPDATE aborts as soon as an error occurs. If you are using a nontransactional storage engine, this may not be what you want because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.

I think the meat of the issue is InnoDB versus MyISAM.

MyISAM is covered by STRICT_ALL_TABLES, but not by STRICT_TRANS_TABLES.  If an UPDATE or DELETE is aborted for any reason mid-stream, some rows will have been updated or deleted.  You have no practical way to clean up the mess.  (This is a major reason never to use MyISAM.)

InnoDB is covered by both settings, but STRICT_TRANS_TABLES is more relevant, and overrides the statement about "earlier rows".  UPDATE and DELETE are atomic.

Also involved is the question of aborting with an error versus doing something so you can keep going.  For example, bad dates and defaults without an explicit DEFAULT, etc.  These are cases where MySQL (unlike some other engines) defines that a non-abort action will be taken.

If your Question is intended to point out poor phrasing in the documentation, go for it.  But do it at bugs.mysql.com , where there is a chance of it getting fixed.

MySQL 8.0 moves toward more robust default settings by changing the defaults.


xp_delete_file - error message when no files for deleting

I managed to get the same error when executing the query on a random chosen folder (that was empty).

Message
Executed as user: DOMAIN\ServiceAccount. xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000] (Error 22049).  The step failed.

At first, I wanted to see if we could use aTRY CATCH Block to 'muffle' the sound of the error screams our SQL Server is throwing at us.

It still fails with the same error message, so we cannot use that to get a 'false positive'.

I do think that the error is happening because either

The folder does not exist
The service account does not have permissions on the folder

When executing on a folder where i knew that my service account did not have permissions on, i got the same error.
After changing the folder, and adding my service account as 'full control' on the folder, it worked, even if it was empty.

Permissions on the folder for my service account

The folder is empty

Checking the job T-SQL

Looks good

Message

  Executed as user: DOMAIN\ServiceAccount. The step succeeded.

This was tested on SQL Server 2014 (SP2-CU12)

There could be better alternatives for the proc, or other
 workarounds.


MS SQL server set variable to where clause

You can use table variables

DECLARE @sqlq TABLE
(
    adc VARCHAR(100)
)

INSERT INTO @sqlq VALUES (21);
INSERT INTO @sqlq VALUES (23);

SELECT * FROM TBL1 WHERE adc IN (SELECT adc FROM @sqlsq)


Unfortunately, you cannot force the ordering of columns in a mysqldump. You can, however, use the table INFORMATION_SCHEMA.COLUMNS. You will need the following:

MySQL Instance with the old data loaded
MySQL Instance with the new data loaded
Some Patience and Scripting

With this setup in mind, here is what you need to do:

STEP 01) Use Query to Present Columns Ordered Per Database/Table

Here is the Query:

SELECT CONCAT(table_schema,'.',table_name),
GROUP_CONCAT(column_name ORDER BY column_name)
FROM information_schema.columns WHERE table_schema NOT IN
('information_schema','performance_schema')
GROUP BY table_schema,table_name;

This will give you every table followed by a CSV list of alphabetically-sorted columns

STEP 02) Launch this Column Query From Both MySQL Instances

IPADDR_OLD=10.1.20.30
IPADDR_NEW=10.1.20.40
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name),"
SQLSTMT="${SQLSTMT} GROUP_CONCAT(column_name ORDER BY column_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema')"
SQLSTMT="${SQLSTMT} GROUP BY table_schema,table_name"
mysql -u... -p... -h${IPADDR_OLD} -ANe"${SQLSTMT}"  > column_listing1.txt
mysql -u... -p... -h${IPADDR_NEW} -ANe"${SQLSTMT}" >> column_listing2.txt
cat column_listing1.txt  > column_listing.txt
cat column_listing2.txt >> column_listing.txt

Run the Column Query and Collect the Results in column_listing.txt

STEP 03) Perform Unique Sort ; Check For Duplicate Table Names

sort -u < column_listing.txt > column_listing.unq
UNQ_LINES=`wc -l < column_listing.unq`
TXT_LINES=`wc -l < column_listing.txt`
(( DIF_LINES = (UNQ_LINES + UNQ_LINES - TXT_LINES) / 2))
echo ${DIF_LINES}

If everything is identical, DIF_LINES should 0. Otherwise, there are duplicates present.

STEP 04) If There Are Duplicates, Find Them

diff column_listing1.txt column_listing2.txt

Give it a Try !!!

UPDATE 2013-03-22 13:04 EDT

You can also check other metadata tables such as

TABLE_CONSTRAINTS

mysql> desc TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

STATISTICS

mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

REFERENTIAL_CONSTRAINTS

mysql> desc REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql>

Setup queries that perform the same styled comparison


I thought that Jeffrey Yao did a fantastic job answering this question here. I've edited it a little bit and sometimes include my own mission statement on my resume, but here's my "slightly" modified version of Jeffrey Yao. This is not specific to mariadb, but I don't think it has to be.

  "Mission: I am committed to providing a robust and secure database
  system for my customers and as such, I proactively maintain and
  monitor the database system to prevent any potential issues;
  accurately diagnose and forecast the database system health and
  capacity; quickly respond to support request(s) and persistently seek
  innovative ways to improve DBA work efficiency and quality."


The user specified as a definer ('root'@'%') does not exist

I met this problem when update a row in mysql.

I connect mysql use a diffrent user and password rather than root.

When I type "show triggers" in mysql, I found the triger use "root@%", this will cause this exception.


What is the impact of MAX_DISPATCH_LATENCY while configuring EXTENDED EVENT?

From my research it doesn't sound like anything terrible can really happen from adjusting that property, but it also generally doesn't need to really be adjusted because 30 seconds is typically a good value for an Extended Events session.
You can read more about the property here but basically its purpose is to set a threshold for the max amount of time the captured events stay in the buffer in memory. Often times the captured events are written to their destination before hitting that threshold, so it's more of a catch all limit. Aside from that threshold, the buffer also gets flushed to the destination once the buffer is full.
Ultimately the reasoning behind having the buffer work this way rather than instantly writing to the destination every time is because I/O is less performant to write to then memory.  Constant writes from the buffer to disk (e.g. a file in your case) could be bottlenecked by and/or cause bottlenecks to the disk. Doing batch writes to disk is most performant when there's a lot of data, and when there's not a lot of data then the MAX_DISPATCH_LATENCY threshold will handle writing your events to the file.


MySQL [Bad handshake] error after update

MySQL 5.7.28 dropped support for yaSSL. On some platforms, such as RHEL and presumably Ubuntu, that resulted in a change from yaSSL to OpenSSL. There are a variety of things that would have worked under yaSSL but no longer work under OpenSSL. For example, yaSSL could not handle certificate chains, but OpenSSL both sends and verifies full chains.
Among other troubleshooting techniques, try taking a packet capture of the TLS handshake with tcpdump and decoding it in Wireshark.


List full text indexes in Oracle database

There are 4 types of Text indexes in Oracle, owned by the CTXSYS user (installed optionally), so you can simply query [DBA|ALL|USER]_INDEXES and filter rows by the ITYP_OWNER and ITYP_NAME columns:

select
  *
from
  dba_indexes
where
  ityp_owner = 'CTXSYS'
  and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH')
;

Index types can be found in the DBA_INDEXTYPES view.


change mysql datatype column from string to json

Whilst a JSON field may do what you want, consider proper Data Normalisation rules first.

As soon as you want "more than one" of something, you should be thinking about a new table that contains those multiple instances, with a foreign key back to the table that contained "the one" thing previously.

select * from vendor_horses ;

+----+---------+
| id | name    |
+----+---------+
|  1 | Red Rum |
+----+---------+

select * from vendor_horse_images ;

+----+-------+-----------------------+
| id | vh_id | path                  |
+----+-------+-----------------------+
| 22 |     1 | /users/57/user-11.png |
| 33 |     1 | /users/57/user-73.png |
+----+-------+-----------------------+


How to get SQL for DDL commands in Sql developer?

Oracle knew you wanted this feature. I'm using SQL Developer 4.0.0.13.80 and it works like you want it to.  You were probably using an older version when you posted your question.  I go to Edit Table, add a column in the GUI, and then pick the item called DDL in the dialog.  There is a radio button to show either the full CREATE statement DDL, or just the DDL update for the current edit.


Most probably this issue due to network latency or long running transaction on primary replica. Thus, in production environment it's always better to have dedicated NIC in each replica that only handles Database Mirroring end-points communication.

However, in your case following steps may help:

Try Suspend data movement and Resume for particular database
Perform LOG backup, check if log_reuse_wait_desc changed

If the AVAILABILITY_REPLICA not changed

Probably one of the replica still got the redo_block which you can identity from effected Replica's Extended Events -> Sessions -> "AlwaysOn_health"

To confirm the blocking is the cause, you can execute following query, look at last_redone_time this would indicate since when the replica got the backlog.

select  db.name, db.database_id,
        ag.name as GroupName,
        state_desc,
        recovery_model_desc, log_reuse_wait_desc,
        Rep.replica_server_name,
        rep.endpoint_url,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        (redo_queue_size / 1024.0) as redo_queue_size_MB,
        last_redone_time,
        last_redone_lsn

from sys.databases as db
    left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
    left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
    left outer join sys.availability_replicas as Rep on DBRepStats.group_id = Rep.group_id and DBRepStats.replica_id = Rep.replica_id
    left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4

Query Result  - Replica got more than a hour back-log

By this time, if you look blocking within secondary replica, you may find some active process which causing the block. Most probably that would be read operation trying to block the things to make sure the schema (SCH_M) of the object can’t be changed by DDL activity that been pushed by Primary replica.
Once the blocking process killed, or completed. You can notice that the last_redone_time and redo_queue_size in sync with primary replica. Also, AVAILABILITY_REPLICA should disappeared when you query

select name, log_reuse_wait_desc from sys.databases`

All Databases are in-sync (arrow marked was the one back-logged):

If nothing works, you already got the way to do force fix:

  Remove Back-logged Replica from AG and Re-join, but there is impact on read-only routing of Availability Group


SQL Server Memory allocation and max server memory setting

Your configuration is fine. 24GB total RAM, 19GB for SQL Server leaving 5GB for the OS.

SQL Server is memory hungry and will consume all the 19GB available, that is perfectly normal.

You could lower the max server memory from 19GB. I wouldn't do that however, if you were experiencing memory pressure with 12GB of memory.

The question would be what other roles is that server performing that would use the memory you would potentially deallocate from SQL Server? Can those roles (maybe SSIS, SSRS, SSAS etc) be moved to an alternative server?


Your SQL statement specifies the behavior you are seeing with ON DUPLICATE KEY UPDATE. This syntax is shorthand for logic that causes an insert only if the key does not exist, and an update of the row identified by that key if it does exist. See the MySQL documentation for more info.

Since ON DUPLICATE KEY UPDATE only operates on primary keys or unique indexes, you cannot insert another row with the same key anyway. If multiple records with the same identifier are required in the table, you must make that identifier something other than the primary key or remove the unique constraint on the index.


Literature

I gathered you some links you should read:

The manual on reliability
The wiki pretty much sums up what's written in the manual, but maybe it helps you too
You can read a more detailed list of accessible sync methods on this page
Here are some more or less practical tips in what to choose

Possible Workflow

Basically your workflow might be the following, which is extracted as a combination of all tips of the above links:

Turn on postgres setting fsync if it is not.
Set synchronous_commit to "on" (or "local") if it is set to "off".
Run diskchecker.pl just for sanity to see if you bought something good and it is configured correctly.
See what sync method postgres selected automatically with show wal_sync_method;
Run pg_test_fsync
and use it's output to

check if you are affected by FLUSH CACHE EXT/SYNCHRONIZE CACHE problem.
decide on the basis of the additional information provided here (in paragraph wal_sync_method) and here (in paragraph test_fsync and below) what value to set wal_sync_method to.

Check if your BBU and file-system prohibit partial writes of 8kb pages, if yes you may disable full_page_writes (see last paragraph here).

Remark (why this might not be enough)

Note that this seems very reliable, but quoted from one of the links above:

  Critics of the battery backup approach suggest that if you run such a system under load, eventually you’ll have a failure in this relatively complicated cache method that will corrupt your database in a way that’s nearly impossible to detect or recover from.

So even having a BBU and a correctly configured server instance, this does not remove the burden to backup everything regularly, and does not remove the possibility to loose some data.

Legal Note

I am not responsible for any lost data ;) neither due to lack of advice here or a wrong tip, nor through anything else.


Load flat file into a predetermined table

Why do you think address is being split into three columns? Based on your screenshot column 5 is being mapped to address and 6 and 7 aren’t being mapped to anything.

Can you please share the full error messages? Also does your file have headers, I presume not based on your screenshot. Try adding headers to the flat file and letting the wizard create the destination table for you.


Since the link that I created in the comments did not contain this particular method, I figure I would throw it in here.   Besides the approaches mentioned here I went ahead and tried this approach as another way that would take very little change to a package that is going through a ForEach Loop (since that is how I did it in mine).   What you would do, is before running the ForEach Loop (and if you are doing the ForEach through an ADO recordset then you would want to do it before that) you could have a file kept in some other location that is nothing more than the file with the headers you want for this file and you would create a task (either Script task or a File System Task) to copy that file that you saved off to the location of the file you are generating via your ForEach Loop.

After you have done that, be certain that within your flat file connection that you have the "Column names in the first data row" unchecked.

If you do that, it will copy the file with the headers and then all of the rows added within your foreach loop will contain data only and no headers.  It is an alternative if those others did not work for you.

Obviously it requires you to keep a file that exists only to be used for headers out on your server or some network drive but it is a solution that will get this done.


You're not alone on seeing sync latency with AGs when doing index rebuilds.  From Sean Gallardy's blog post SQL Server Index Maintenance – You’re Doing It Wrong (emphasis mine):

In the worst of the cases, which does seem to happen quite often, it can bring the entire server to its knees, causing long blocking waits, filling up the log, causing remote AG replicas to fall behind, among a large list of other items. This does have real and painful consequences.

Note that an important caveat mentioned in the comments of that post is that fragmentation can affect the performance of SQL Server's readahead mechanism, which is used heavily during large table scans (ETL loads, big reporting queries).
I honestly like your suggestion of disabling maintenance and seeing what happens.  You could disable it on just the very large tables, and see if there is any negative impact vs the gains you get in terms of shorter maintenance windows.
To comment on this specific statement in your question:

Once index maintenance has been performed we then update statistics etc.

FYI, rebuilding an index will result in statistics being updated for that index with FULLSCAN.  So make sure you're not updating stats twice on these tables that are getting rebuilt.
You should also take a look at Erik Darling's blog post Because Your Index Maintenance Script Is Measuring The Wrong Thing.  You could check avg_page_space_used_in_percent to determine if there are indexes where you can reclaim a lot of space (on disk and in memory) by rebuilding.  This would probably be the biggest "win" from rebuilds.
On a related note, another of Erik's posts (What Metrics Does Rebuilding Indexes Improve?) points out some specific situations that might warrant an ad-hoc rebuild:

It’s my opinion, and you can take it or leave it, that index rebuilds
should be reserved for special circumstances.

You deleted a lot of data
You need to change something about the index
You have a Heap with a lot of forwarded fetches

Unfortunately, there isn't an easy button here.  Hopefully the links help inform your decision process though.


Your best bet is to test it out. You did not told us what would you do to the transaction log backup job on primary will it keep on happening as per schedule?, if so do you have enough space on drive holding transaction log backups ? Also for this exercise it would be better to have secondary database in standby mode before you recover it

If you do not disable the backup job then you have to make sure you keep all the log files intact and after you add the secondary back the mammoth task of restoring all those files would be on restore job and it will sure fire some alerts that log shipping is out of sync but since log chain is not disturbed it should eventually catch up.

Now other scenario can be you disable all the log shipping jobs and is ready to run primary database exposed (which would not be correct thing, its your call) and now you go ahead and remove secondary from log shipping for 24 hours. When you will add it back, subject to condition you can do it successfully, the log shipping should work. The reason I say it should work is I do not see any log chain which is disturbed or broken and after adding the secondary server. The challenge here also is whether you are successfully able to restore the snapshot, please note I have seen many a times this thing failing that is why i stressed on testing it.


Joining two inline functions, slows considerably when filtering on second function

Workaround suggestion:

If you can change the functions, try making them multi-statement table value function instead of inline functions. When doing so, you define an output table in the RETURNS part. In the DDL of the output table, declare a good clustered primary key.

Example:

CREATE FUNCTION dbo.fn_test1(@i int)
RETURNS @out TABLE (
    a    char(5) NOT NULL,
    b    date NOT NULL,
    c    int NOT NULL,
    PRIMARY KEY CLUSTERED (a, b)
)
AS

BEGIN;
    INSERT INTO @out (a, b, c)
    SELECT x, y, z
    FROM dbo.someTable
    WHERE someCriteria>@i;

    RETURN;
END;

Even though swapping an inline function for a multi-statement function comes with its own set of advantages and drawbacks, an important difference in your case may be that the output is stored in a temp table with a clustered index before the join happens. If you've properly aligned the clustered indexes on the two functions, joining their output tables should be a speedy affair.

Here's an idea of what kind of query plan you could expect (excluding the inner workings of the functions, obviously).


What Query Browser tools are out there available for Sybase Database?

As Sybase also has a JDBC driver any Java/JDBC tool should work too (at least the basic stuff).

These are the free JDBC based tools that I know of:

DbVisualizer  (not open source, but has a free version)
ExecuteQuery
SQL Developer That's not the one from Oracle! It's not open source but free as well
SQL Workbench/J
Squirrel has already been mentioned.

Another commercial tool that comes to my mind is: DB Solo

Edit, some more suggestions here:
https://stackoverflow.com/questions/5001722/sybase-gui-client
https://stackoverflow.com/questions/2481071/any-alternative-to-sybase-sql-advantage
https://stackoverflow.com/questions/428387/whats-a-good-front-end-query-tool-for-sybase/


To enforce this declaratively in 2005 you can use an indexed view.

CREATE VIEW dbo.ActiveNames
WITH SCHEMABINDING
AS
SELECT Name
FROM  dbo.YourTable
WHERE Active = 1

GO

CREATE UNIQUE CLUSTERED INDEX UQ_ActiveNames ON dbo.ActiveNames(Name)


Incomplete MysqlDump Files - Mysqldump Inside For Loop

I have created a batchfile which runs under Windows.
It will create the .sql files in the current directory.
I left out Zipping the output.

The parameter '-N' will delete the header in the output from "show databases".

@echo off

set mysql=C:\Program Files\MySQL\MySQL Server 8.0\bin\
set mysql=c:\progra~1\mysql\mysqls~2.0\bin\
set password=********

FOR /F "usebackq" %%F IN (`%mysql%mysql -N -uroot -p%password% -e "show databases"`) do (
    ECHO %%F
    "%mysql%mysqldump" -u root -p%password% %%F >%%F.sql
)

I only got 1 error running this script (besides the warnings... ):

performance_schema
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES


Delete _new table from pt-online-schema-change

Like many other solutions designed for an almost lockless schema change, pt-online-schema-change works by creating triggers on your table and creating a _new table. As stated in the documentation:

  Any modifications to data in the original tables during the copy will
  be reflected in the new table, because the tool creates triggers on
  the original table to update the corresponding rows in the new table

If this utility crashed, you might have to do the cleanup yourself. This might include:

Removing the triggers: have a look at the SHOW TRIGGERS  syntax to see the ones pt-online-schema-change has just created;
Delete the _new table.


I don't think I understand your question, but you may benefit from a rollup. MySQL have there own version of grouping sets that look like:

SELECT country_id
     , province_id
     , user_id
     , SUM(score) AS total_score
FROM activities
GROUP BY country_id, province_id, user_id
WITH ROLLUP;

null in user_id column means an aggregation, dito för province_id. I.e.

1   null    null    64390

is the total for country_id = 1


sp_BlitzFirst - Wait Stats Interpretation

So let's do a quick rundown of your top two waits:

IO_COMPLETION - waiting to write to storage
PAGEIOLATCH* - waiting to read from storage

In 30 seconds, your queries spent 1,655 seconds waiting on storage.

Your storage is probably slow - if you skip down to the next section in sp_BlitzFirst's output, it will show which data and log files SQL Server was waiting on, and for how long. However, before you leap to playing Pin The Blame On The SAN Administrator, consider:

Tuning indexes & queries to reduce the amount of data read from disk (I'd suggest using sp_BlitzIndex and sp_BlitzCache for that, although I'm biased (disclaimer: I'm one of the coauthors))
Adding memory to cache data, which will reduce PAGEIOLATCH, thereby easing pressure on the storage, and will likely reduce the IO_COMPLETION waits too
Spending a whole lot of money on storage (you might guess that the above two fixes are above this one for a reason)


The last successful restore was the RESTORE DATABASE. Since you specified NORECOVERY, the database is, as expected, in restoring state. If you don't have any more backups to restore and just want to get out of the restoring state, you just do:

RESTORE DATABASE mydb WITH RECOVERY

That's it.  You find the database as it were when that full backup you restored was produced.

As for log backups, you need an unbroken chain of log backups. You can't skip a log backup, like you most probably tried to do. You likely got an error message saying that the log backup was too recent to restore, citing LSN numbers and that it requires some earlier log backup with some earlier LSN number. There's no way around the "unbroken chain of log backups" requirement. If you can't find those log backups, or a more recent full backup, then you are probably stuck with a 4 month old database.


how to change wsrep_cluster_size in Percona XtraDB Cluste?

You have a couple issues here, I believe it is a misunderstanding of how to deploy PXC. Firstly, wsrep_cluster_size is telling you how many nodes you have in your cluster. If both nodes are saying 1, you have 2 clusters of 1 node, not 1 with 2 nodes.

Secondly, follow the rule of 3's. You should always have an odd number to avoid split brain.

Recommended step here is to shut down node 2. Bootstrap node 1

http://severalnines.com/blog/how-bootstrap-mysqlmariadb-galera-cluster

https://www.percona.com/doc/percona-xtradb-cluster/5.6/manual/bootstrap.html

Then bring node 2 up with the correct cluster ips set in your my.cnf.

Follow configuration options here: https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation.html


Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

The problem seems to have been Postgres's "FASTUPDATE" mechanism.

FASTUPDATE is a setting available on GIN indexes which, when enabled, causes changes to the index (caused by UPDATEs and presumably INSERTs as well) to be "queued up". Then, once this "queue" becomes too large, the pending entries are properly integrated into the GIN index.

The aim of FASTUPDATE is (no surprise) to speed up index updates, but it unfortunately leads to an occasional UPDATE query being exceptionally slow. In my case, I found it preferable to take the hit up-front (mainly to avoid warnings of a "slow query" in my logs).

FASTUPDATE is apparently enabled by default and available since PostgreSQL 8.4. I was able to disable it like this:

ALTER INDEX items_search_vector_idx SET (FASTUPDATE=OFF);

At the time of writing, I have been running as such for almost a week with almost no slow queries. (Aside one query that I expect to take a long time, I've noticed little else.)

You may also find more pertinent information in a related thread from the Postgres mailing list. Interestingly, one of the Postgres devs (Tom Lane) suggests processing of the FASTUPDATE pending items "was not supposed to block concurrent insertions", but I'm not sure if that's correct; in my case I would see several queries get "backed up" and then finish all at once.


Data Pump uses directory objects. Read this:

Default Locations for Dump, Log, and SQL Files

First create the directory object as:

create directory dump_dir as '/home/oracle/rmanscripts/dumbbackup';

Then you can use that in your impdp command:

impdp full=y direcotry=dump_dir dumpfile=sysfull.dmp logfile=full_db.log

Also:

  while database is not mounted

By this I hope you mean it is open. Data Pump needs a working database, even if it is empty.


What you need can be achieved via a simple combination of two joins, Let's have a look at a sample query

Select < Fields as per your requirements >
From items i
Inner join item_categories ic On i.item_id=ic.item_id
Right outer join categories c On c.category_id=ic.category_id
Where <Any conditions you may have>

I hope this is what you were looking for.


Does pg_export_snapshot work across replicas?

I just tried it, and it is not possible, because exported snapshots are not replicated.

Snapshots are persisted in the pg_snapshots subdirectory of the data directory, and you will see that they don't show up on the standby.


Transactional Replication: Maintaining schema changes

With transactional replication you must ensure that your schema changes are compatible or risk errors on the subscriber.  In practice this means reviewing the schema change scripts and testing their compatibility with your replication topology.  Adding articles requires a new snapshot, but you can create a second publication to hold the added tables if you want to reduce the snapshot size.

If you don't want to do that, then a log-based replica is probably a better design.  See, eg Read Scale Availability Groups


Get SQL server instance installation date

You could use the sys.server_principals view to get the date:

SELECT  create_date
FROM    sys.server_principals
WHERE   name = N'NT AUTHORITY\SYSTEM';

Check out server_principals on MSDN.


15K databases on one instance of sql server - depending on the activity, overhead of maintenance, backup time, etc sounds to be an overkill.

You should instead spin up VMs on one powerful host machine and then balance out your databases on multiple VMs.

You are looking at a wrong place for optimization - Auto-Close option and you know that what you are trying to do is not a good practice either !

Remember: Auto-close is deprecated, so one more reason to avoid it.

Seems like you should invest in reading - Multi-Tenant Data Architecture


First, 4 hours seems like a long time for this.  I have done big updates and would not expect it to take this long were an inner join is involved (antijoins are more troublesome).

First to your questions:

If I removed all of the columns on the update_assets table, except for the pertinent columns for this transaction, will that speed up the transaction?   Maybe, if you rewrote the table after (using cluster, or select into).
Does postgresql always change data to text data for these types of joins?  Nope.  So turning everything to text will not help.

So what you can do is to look and see what is happening.  Initial troubleshooting steps need to be:

Run top and watch the query.  Is it I/O bound or CPU bound?   What is going on memory-usage-wise?
Does your db fit comfortably in RAM?  If not consider allocating more RAM to this vm.
Check the data types for the join columns.  Make sure these are the same.  Bad things can happen when it is casting to text for a join like this.
Check for locks.  Maybe the process is continuing slowly during a transactional period and waiting for locks periodically?


Function returning "No data - zero rows fetched, selected, or processed" in MySQL 5.5

You are using 'NOT FOUND' and SQLSTATE '02000'. There were bugs reports that addressed this:

http://bugs.mysql.com/bug.php?id=42834 (March 5, 2009 : STATUS Closed)
http://bugs.mysql.com/bug.php?id=55843 (August 9, 2010 : STATUS Closed)
http://bugs.mysql.com/bug.php?id=59169 (December 25, 2010 : STATUS Duplicate)

Here is something enlightening about the whay MySQL does error handling.

The book MySQL Stored Procedure Programming has a whole chapter on this subject. On your particular problem, you need what's on pages 132,133 under the subheading Handler Conditions.

It states on page 132 that there are three ways to define an error:

MySQL error code
ANSI-standard SQLSTATE code
Named Condition

You must change the code to

Declare continue handler for 1329
Begin
  set Done = 1;
End ;

I found that error code here : http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

It says as follows:

  Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

  Message: No data - zero rows fetched, selected, or processed

The book I mentioned recommends getting away from ANSI error codes and using the direct MySQL error codes. The reason? Page 133 has a box "SQLSTATE or MySQL Error Code?", which says the following:

  In theory, using the SQLSTATE codes will make your code more portable
  to other database platforms and might therefore seem to be the best
  choice. Hoewver, there are a number of reasons to use MySQL error
  codes rather than the SQLSTATE codes when writing MySQL stored
  programs:

  In reality, it is unlikely that you will move your stored programs to
  another RDBMS. The Oracle and SQL Server stored program languages are
  totally incompatible with MySQL. The DB2 stored program language is
  somewhat compatible (both are based on the SQL:2003 standard). It is
  very likely, however, that you will use MySQL-specific syntax as you
  write your application, which will prevent your stored code from being
  portable.

  Not all MySQL error codes have SQLSTATE equivalents. Although every
  MySQL error code is associated with some SQLSTATE error code, often it
  will be a general-purpose SQLSTATE that is not specfic (such as
  HY000). Therefore, you will almost certainly have to code some
  handlers that refer directly to MySQL error codes. You'll probably
  find that the advantages of using a consistent handler format will
  outweigh the theorectical portability advantage of SQLSTATE error
  codes.


How does OPTION (FAST 1) actually interact with a client?

If it were a query that returned more than 1 row I'd speculate that someone at the vendor (way back when, given the version of SQL Server) stumbled on the query optimiser producing a preferable plan when FAST was specified as a hint.

As it's returning just 1 row, the explanation probably has more in common with the infinite monkey theorem than reasoned judgement. A junior saw a hint for FAST and decided that was preferable to his query not being fast.

  This makes me wonder if the client has somehow broken communication,
  would the server keep the transaction open, processing the full result
  set after the first n rows are returned and leave the transaction
  open?

The query returns 1 row so there is no further result set to process. I'm inclined to suspect there is other code further up the stack that's causing the problem.


Here's the canonical design for a Many-to-Many linking table.  You generally want an index to support traversal in each direction, and (at least for SQL Server) it doesn't matter whether you use a unique constraint, unique index or non-unique non-clustered index for the reverse key index: you end up with the same data structure.

CREATE TABLE CodeGroupMappings
(
    CodeID int NOT NULL,
    GroupID int NOT NULL,
    CONSTRAINT PK_CodeGroupMappings PRIMARY KEY CLUSTERED (CodeID, GroupID),
    CONSTRAINT AK_CodeGroupMappings UNIQUE (GroupID, CodeID),
    CONSTRAINT FK_CodeGroupMappings_Code FOREIGN KEY (CodeID) REFERENCES Code(CodeID),
    CONSTRAINT FK_CodeGroupMappings_Group FOREIGN KEY (GroupID) REFERENCES [Group](GroupID),
)


Very slow insert performance on large table (EF)

It appears that the dates you are clustered on are not being inserted sequentially.  If your date field was recent purchases for example they would be inserted at the 'end of the table'.  Because it is taking so long to do the insert it seems that SQL is having to reorder your data with each insert instead of tacking it on the end.

Determine if your clustered index is important to reads.  It is always a balance on input / output when looking at indexes.  It may be that a different column is best for clustered index.  I almost always use an integer or long primary key with no business value for the clustered index and then index dates, maybe with included columns, for read performance.

https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/


PostgreSQL: PreparedStatement to execute set schema command

You can't pass identifiers as parameters. It's similar to select * from ?

However, you can use a function to set the search path:

public void executeSetSchema(Connection con, String schema) {
  try (
    PreparedStatement pstmt = con.prepareStatement("select set_config('search_path', ?, false)");
  ) {
     pstmt.setString(1, schema);
     stmt.execute();
  } catch(Exception) {
  }
}

I moved the prepareStatement() call into a try-with-resources so that the statement is closed properly.


Oracle shrink space release space to operational system in one step?

Table Shrinking is not about freeing space for OS, but replacing the blocks freed due to UPDATE and DELETE operations, with the used blocks. Thus, reducing the load on the database during table scans. Moving HWM does not 'give back' the free blocks to OS, it just indicates where the used blocks end, therefore Oracle knows there is no data after that.
Managing OS space for a database is done in OS layer, if you want to free up some space in OS, and you know that there are some free blocks inside Oracle datafiles, you have to take a dump using Oracle Datapump (expdp and impdp), drop the datafile, re-create the datafile with AUTOEXTEND option and then import your dump.


Improve the poor performance of the sys.dm_db_stats_properties DMV

You can get everything except modification_counter from DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
WITH STAT_HEADER;

You'd need to capture its output into a temporary table using something like:

CREATE TABLE #StatHeader
(
    [Name] sysname NULL,
    Updated datetime NULL,
    [Rows] bigint NULL,
    [Rows Sampled] bigint NULL,
    Steps integer NULL,
    Density float NULL,
    [Average key length] integer NULL,
    [String Index] varchar(3) NULL,
    [Filter Expression] nvarchar(max) NULL,
    [Unfiltered Rows] bigint NULL,
    [Persisted Sample Percent] integer NULL
);

INSERT #StatHeader
EXECUTE('DBCC SHOW_STATISTICS (table, stat) WITH STAT_HEADER;');

A substitute modification counter is provided by rowmodctr on sys.sysindexes.

This is a workaround, and what we would have done before the new DMV was provided. It's not ideal, and you should report the unexpectedly slow performance you see to Microsoft.


MongoDB Stuck with Jumbo chunks while exhausting a shard

I was able to complete the exhaust by increasing the chunk size and removing the jumbo tag from the problematic chunks.
After getting the results from AdamC function, I found out the biggest chunk of that shard was ~148 MB of size.
After figuring this one out, the first step is connect to the config database and change the max chunk size:

use config
db.settings.save( { _id:"chunksize", value: 150 } )

The second step was to clear the jumbo flag from the relevant chunks.
This can be found in the MongoDB Documentation: Clear jumbo flag

Once the chunks were no longer marked as 'jumbo' the balancer had no problem migrating them to another shard.
When all of this was done, I restored the chunkSize back to 64

db.settings.save( { _id:"chunksize", value: 64 } )

As I do not want that all of my chunks will be as big. when possible, I'll correct the hashed shard key on the problematic collections to create a more divisible shard keys.

Thank you AdamC, your initial guidance set me on the correct path to solve this problem.


Assuming you are using SQL Server, you could adapt this to work:

CREATE TABLE [dbo].[IP2Location](
    [ID] [int] NOT NULL CONSTRAINT PK_IP2Location PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
    [ip] [bigint] NULL,
    [country_code] [varchar](2) NULL,
    [country_name] [varchar](255) NULL,
    [region_name] [varchar](255) NULL,
    [city_name] [varchar](255) NULL,
    [latitude] [float] NULL,
    [longitude] [float] NULL,
    [zip_code] [varchar](255) NULL,
    [time_zone] [varchar](255) NULL,
    [isp] [varchar](255) NULL,
    [domain] [varchar](255) NULL,
    [net_speed] [varchar](255) NULL,
    [idd_code] [varchar](255) NULL,
    [area_code] [varchar](255) NULL,
    [IP2LocationTimeStamp] [timestamp] NOT NULL,
);

/*
    Create a non-clustered index on the numeric IP address to speed lookups
    I indexed the country_code field here as well since our lookups use it.
    I INCLUDED country_name, region_name, and time_zone since that is what we
    typically request in a SELECT statement from this table.
*/
CREATE NONCLUSTERED INDEX [IX_IP2Location_IP2LocationServiceQuery] ON [dbo].[IP2Location]
(
    [ip] ASC,
    [country_code] ASC
)
INCLUDE (   [country_name],
    [region_name],
    [time_zone])
GO

/*
    Create BIGINT versions of the dotted-ip field ip_address
*/
UPDATE IP2Location SET IP =
            (CAST(substring(ip_address,1,charindex('.',ip_address)-1) AS BIGINT) * 0x01000000)
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address))+1, (charindex('.', ip_address, charindex('.', ip_address, charindex('.',ip_address))+1)-charindex('.',ip_address,charindex('.',ip_address)))-1) AS BIGINT) * 0x00010000)
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1, charindex('.', ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1)) as BIGINT) * 0x00000100)
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1)+1, len(ip_address) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1))) as BIGINT)
            );

/*
    Function to return a TVF containing location info about the given numeric IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone
        FROM GetLocationFromIPv4(16777215);
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4]
(
    @IP bigint
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    INSERT INTO @IPLoc
    SELECT country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code
    FROM IP2Location
    WHERE ip = @IP;
    RETURN
END
GO

/*
    Function to return a TVF containing location info about the given dotted-decimal IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone
        FROM GetLocationFromIPv4('192.168.0.1');
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4String]
(
    @IP nvarchar(15)
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    DECLARE @IPInt64 bigint;
    DECLARE @n1 bigint;
    DECLARE @n2 bigint;
    DECLARE @n3 bigint;
    DECLARE @n4 bigint;

    set @n1 = substring(@IP
        ,1
        ,charindex('.',@IP)-1
        );

    set @n2 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP))+1
            , (charindex('.', @IP, charindex('.', @IP, charindex('.',@IP))+1)-charindex('.',@IP,charindex('.',@IP)))-1
        );

    set @n3 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1
            , charindex('.', @IP, charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1)
        );

    set @n4 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1)+1
            , len(@IP) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1))
        );

    SET @IPInt64 = (@n1 * 0x01000000) + (@n2 * 0x00010000) + (@n3 * 0x00000100) + @n4;

    INSERT INTO @IPLoc
    SELECT TOP(1)country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code
    FROM IP2Location
    WHERE ip = @IPInt64;
    RETURN
END
GO


Where are user defined functions saved in PostgreSQL?

You can get the definition of a function from the pg_proc system catalog.
If prorettype is contains the special record type, the actual return types are the elements in proallargtypes where the corresponding proargmodes entry is t, o or b.
This query should give you all functions maned test together with their object ID, the input types and the result types:
SELECT p.oid,
       p.proname,
       p.proargtypes::regtype[] AS input_types,
       /* only list output arguments */
       array_agg(a.type::regtype ORDER BY a.n)
          FILTER (WHERE a.mode IN ('t', 'o', 'b'))
          AS result_types
FROM pg_proc AS p
   CROSS JOIN LATERAL (/* get all arguments for table functions */
                       SELECT *
                       FROM unnest(p.proallargtypes, p.proargmodes) WITH ORDINALITY
                       UNION
                       /* get the return type for other functions */
                       SELECT p.prorettype, 'o', 1
                       WHERE p.proallargtypes IS NULL
                      ) AS a(type,mode,n)
WHERE p.proname = 'test'
GROUP BY p.oid, p.proname, p.proargtypes;


Temporal Validity and Primary/Foreign Key Relationships

Unfortunately present SQL or its implementations in all RDBMS products does not fully support temporal relations.

Only Teradata and DB2 have some features implementing constraints having time in the two or more of columns.

My example:

SET SCHEMA TEST;

CREATE TABLE PRODUCT (
product_ID INT NOT NULL,
suplier NVARCHAR(50),
PRIMARY KEY (product_ID)
)
;

CREATE TABLE CAMPAIGN  (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID)
)
;

ALTER TABLE CAMPAIGN
ADD CONSTRAINT XFK_campaign_productid
FOREIGN KEY (product_ID) REFERENCES PRODUCT (product_id)
ON DELETE NO ACTION;

If you add temporal columns business_startdate and business_enddate and optionally
transaction_starttime and transaction_endtime, you cannot enforce referential integrity anymore.

You might have to implement triggers or stored procedures or application level coding if you want to implement a constraint like this:

CREATE TABLE CAMPAIGN  (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
transaction_starttime TIMESTAMP NOT NULL,
transaction_endtime TIMESTAMP NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID,business_startdate,transaction_starttime)
)
;

For DB2 there exist a temporal primary key constaint in the following syntax:

CREATE TABLE CAMPAIGN  (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
price DECIMAL,
PERIOD BUSINESS_TIME (business_startdate,business_enddate),
PRIMARY KEY (campaign_ID,BUSINESS_TIME WITHOUT OVERLAPS)
)
;


How to avoid constantly querying a server

Note you should include which SQL Server version you're using, as this will impact what answers are applicable to your use case.
If you're trying to alleviate pulling data across remote servers, then you should look into a data synchronization feature like Replication, AlwaysOn Availability Groups, or SISS
These can be used to synchronize the data from a remote server to a local server, so your querying can all be contained to one server.


How to get names form 3 different tables

Finally as I did not see any hope I inserted a column "prodname" and with this sql I got the result. But any help will be welcome this add of prodname makes my TB_A table a little bulky.

Here is the SQL

SELECT sd.prodname, sd.date,
SUM(IF((sd.status=0 AND (sd.prodid=pb.id OR sd.prod.id=pm.id OR sd.prodid =pg.id)), sd.qty,0)) AS tot,
SUM(IF((sd.status >0 AND (sd.prodid=pb.id OR sd.prodid=pm.id OR sd.prodid=pg.id)), sd.qty, 0)) AS rq
FROM TB_A AS sd
LEFT JOIN TB_B AS pb ON pb.id=sd.prodid
LEFT JOIN TB_C AS pm ON pm.id=sd.prodid
LEFT JOIN TB_D AS pg ON pg.id=sd.prodid
WHERE sd.stat>1
GROUP BY pm.id,pm.id,pg.id,sd.prodname
ORDER BY sd.date DESC

PS:In my display of my program I don't display rq I display tot-rq within a program. Any suggestion for getting that in this query is welcome.


What real-world problems have you solved with Extended Events?

There are TONS of theoritical ones but I'll stick to 1 that I used by using extended events, then another that a tool I demo'd found with extended events:

Had to build a system that met the SISR and PCI tier 1 requirements for a tier 1 telco.  They required us to log every SA log in.  Using extended events was a lot more lightweight and easy to manage than having to worry about profiler always being on.

2
-TempDB Contention.  A query would run 1 second, half a second, 30 seconds, it was all over the place.  The tool used extended events to break down the wait types and I saw PAGELATCH_SH was really high.  Turns out tempdb did not have enough data files to optimize all the CPUS (32 cores) that the server had running and we had tempdb contention.  Using the wait stats per query really helped figure that issue out fast.

-Was migrating a back office SQL cluster to a different data center, trying to keep downtime to 1 minute or less.  While testing the new data center I would get major timeouts.  Timeouts were usually caused by sql server however looking at the wait type of NETWORK_IO we were able to trace it to a firewall between the DCs that was pegged at 100% CPU dropping packets faster than monica lewinsky.  Yea that's right, my last example had Monica Lewinski lol.


Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables

The main reasons not to enable TF 2453 globally are:

You have queries that deliberately used table variables for the one-row guess.
You absolutely must avoid recompilations as much as possible.

In many cases, you will still find that using other solutions (temporary tables, recompile hints) are better overall. Trace flag 2453 benefits a relatively small class of queries for which a table variable is the absolute best choice, but table cardinality is important without needing full statistics.

Somewhat related reading:

Interleaved execution for multi-statement table valued functions
Public Preview of Table Variable Deferred Compilation in Azure SQL Database
What's New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)


This is the proedure.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/hide-an-instance-of-sql-server-database-engine?view=sql-server-ver15
Is the SQL Server Browser running?
The information to hide/show instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide/show instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :
EXEC master..xp_instance_regwrite
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key =
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
  @value_name = N'HideInstance',
  @type = N'REG_DWORD',
  @value = 1
  — 0 = No, 1 = Yes

To check if an instance is hidden you can use xp_instance_regread to check registry values:
DECLARE @getValue INT
EXEC master..xp_instance_regread
  @rootkey = N'HKEY_LOCAL_MACHINE',
  @key=
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
  @value_name = N'HideInstance',
  @value = @getValue OUTPUT
SELECT @getValue

This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.


You should be able to run the installer and specify the /SKUUPGRADE switch which will allow you to upgrade the existing instance.  There's a couple of reg keys that you'll need to blow away to get SSMS up and running on the machine, but those are documented in the MSKB somewhere.


SSIS Expressions Date Format get just YY for year

Use the modulo operator %: http://technet.microsoft.com/en-us/library/ms141815(v=sql.100).aspx

DATEPART("yyyy",GETDATE()) % 100

Your next problem may be that this will still be be a number and thus will not have leading zeros.

What is your underlying problem?  What are you trying to format a date as?

You would then just use (DT_WSTR, 2) DATEPART( "YY", GETDATE()) % 100 as part of your expression.


Import from Access .accdb file to SQL Server

I've had to import Access files into SQL Server before and haven't found the Import and Export wizard to be a very good tool for the job. What I ended up doing was opening up the Access file, finding the table, right clicking and going to Export -> ODBC Database. This does require you to have a User or System DSN setup for the target SQL Server database, and it is a bit slow, but it was the only thing that I found that worked all that well. (It will create the target table for you.)


IMHO

The original layout looks like it is a self-built implementation of Row Level Security (RLS).  RLS allows the developer to limit the available rows based on who is logged in (and other pieces of information).

The reason for the original design could have been done because:

The designer was unaware of RLS
The RDBMS did not support RLS
DBA Stack Exchange wasn't available to the developer at the time of implementation
The developer copy+pasted code unaware of the potential problems

constructing the SQL statements for contacts_201 screams "Possible SQL Injection"

The solution for (2) would be based on these words of wisdom:

  use the right tool for the job.

In other words, the RDBMS (that doesn't support RLS) should have been changed to an RDBS that supports the Business Requirement (ie one that does support RLS).  This could be a simple as doing an upgrade.

note "Database Agnostic Code is a Myth" could have been the major reason to not fix (2).


Override database option in a user-specific .my.cnf config file

In fact I am realizing that setting database option in a [client] section is may be nonsense as many client tools won't be compatible.

mysqldump and mysql_upgrade for example are complaining about this option. So I may specify it in a more specific [mysql] section instead:

[client]
user=myuser
password=mySecretPassword

[mysql]
database=mydb

Even if it meets my needs, It would still be great to find a way to override database option though...


If you want to use reserved words as table or column names, you have 2 options:

use brackets (the SQL-Server's way): SELECT [Kill]

or double-quotes* (the ANSI/ISO standard): SELECT "Kill"

Your whole statement would become:

SELECT [serial], [Kill]
FROM tbl_pvporderview
WHERE [Kill] > (?)
ORDER BY [Kill] DESC ;

*: Of course, using double quotes (SELECT "Kill") would necessitate that QUOTED_IDENTIFIER is ON. You may encounter databases that the setting is still SET OFF.


Information about a column and the data it holds?

The logical built in place to put this kind of metadata about columns might be in the description box when the column is selected in the table designer.

Behind the scenes this gets saved as an extended property with MS_Description as the property name.

It would be extremely pushing it to claim that this is "standard" or common practice though.


Find rows where any column matches a search pattern

Well, there is the plain and simple "brute force" way:

SELECT *
FROM   "Test"
WHERE (col1 LIKE '%ABC%' OR
       col2 LIKE '%ABC%' OR
       col3 LIKE '%ABC%' OR
       ...
       col15 LIKE '%ABC%');

Parentheses won't be necessary if you don't have additional WHERE expressions.
Probably faster (and more robust in any case) than concatenating all columns for a single LIKE test.


How to get the customer payment count for each row in MYSQL

You can use a CASE expression that checks for the existence of another payment of the same customer for each payment. If such a payment exists get the count of previous payments plus 1 and use that in the string indicating and "old" payment. Otherwise return the string indicating a "new" payment.

SELECT c1.customer_name,
       p1.payment_datetime,
       p1.amount,
       CASE
         WHEN EXISTS (SELECT *
                             FROM tbl_payments p2
                             WHERE p2.customer_id = p1.customer_id
                                   p2.payment_id <> p1.payment_id) THEN
           concat('Old(',
                  (SELECT count(*) + 1
                          FROM tbl_payments p2
                               WHERE p2.customer_id = p1.customer_id
                                     AND (p2.payment_datetime < p1.payment_datetime
                                           OR p2.payment_datetime = p1.payment_datetime
                                              AND p2.payment_id < p1.payment_id)),
                  ')')
         ELSE
           'New'
       END status
       FROM tbl_payments p1
            LEFT JOIN tbl_customers c1
                      ON p1.customer_id = c1.customer_id
       WHERE p.payment_datetime > '2015'
             AND p.payment_datetime < '2019';


Duplicate check constraint on multiple tables?

I would create another table.

This allows for several possibilities:

that one day some other part number such as 9 would be acceptable. You can quickly and transparently add it to the table, rather than changing a bunch of constraints on different tables.
that you might want to store some extra data alongside each part number, for example the name of the part. You can add a column to the table which you already have.

The only situation where I would not do it this way, was if 1 to 8 is something which could never ever change. For example, you might be sure that there will only ever by 7 days of the week (but who knows?).

In this case I would create a TYPE with this constraint. Then you can use the type in multiple tables. If the days of the week (or whatever) DOES change, you can change the TYPE.


How to recover Postgres replication slave after fatal error

If you set up an archive_command and have your WAL segments saved elsewhere, you can just point a restore_command in the recovery.conf on each of the secondaries at your WAL archive, and they should grab the next needed segment and carry on happily.

If you didn't set up an archive_command, you'll need to take a pg_basebackup on each of your secondaries, because without that WAL segment, they can never catch up. Since you're on 9.4, I would recommend also setting up a replication slot, which will prevent the primary from recycling a WAL segment that is required for the streaming replicas.

You can find more out about this with the replication slot documentation.

Otherwise, you need to have an archive_command set that saves the WAL elsewhere to catch up, or you need to adjust wal_keep_segments high enough on the primary that it doesn't recycle files under heavy load on your system.

Setting up the archive_command in your postgresql.conf is covered in the Continuous Archiving and Point In Time Recovery documentation.

An example of a similar situation to the one you are in, and one potential solution to prevent it, is covered in Offsite Replication Problems and How to Solve Them.

Hopefully this is enough information for you to get your replicas back to a working state. =)


Postgres Installed in Linux (RHEL) and trying to access / connect using pgadmin (Windows)

First try to be more specific:

Which version of RHEL are you using?
Which version of Windows are you using?
How are these computers connected?

Are they in the same network segment?
Are there any firewalls in between

Next, try the following:

Check firewall configurations

Windows firewall configuration (or any Antivirus/Firewall software that may be blocking the pgAdmin)
SELinux configuration and/or any antivirus/firewall that may be preventing connections to the linux host
If the host is a 'hostname' then check that the network services is translating the name correctly. Try a ping to the IP and then ping to the hostname. Or issue a dnslookup.

In the linux system try netstat -a | grep LISTEN |grep "5432" to verify that there is a process actively LISTENING on port 5432

Hope this helps you to start solving your problem.


Online Hostel Management System ER Diagram

You should probably have a table "StaffHostel" which has StaffID/HostelID FKs. It should also have another field called "IsManager", which can be True/False. That is a better way to represent the Staff - Hostel relationship. Table would look like:

HostelID (FK)
StaffID (FK)
IsManager (True/False)

If this is for a Hostel, note that people often don't book Rooms, but book beds! There can be many beds in a single room (usually up to 8) with customers booking a bed in a particular room. That will complicate your design significantly.

You may want to put RoomPrice into the Room table and have a "DefaultRoomTypePrice" in the RoomType table, but that is getting into semantics. I just know from experience sometimes room can be the same type but different prices for certain reasons.

It is unclear how your Catalog table is linked to the Hostel, explain a bit more? What is a Catalog?

Otherwise looks pretty good, the only thing I can imagine you might want to do differently is to hand off the payment to another provider, storing Credit Card information is a very dangerous thing to do, leave it to the big boys who implement stringent security practices.


strategy for enabling a general query log on MySQL 5.0.95 with no restart?

I do not think for v5.0.95 is it possible to enable general log without server restart, but
as of MySQL 5.1.12, you can by executing this query,
You can try this on v5.0.95 as well and see whether it works or not,

  SET GLOBAL general_log = 'ON';

This method works on any platform and does not require a server restart.

Baron has written a script to enable and disable log without restart of server for v5.0 as mentioned below:

http://www.mysqlperformanceblog.com/2007/09/13/enablingdisabling-query-log-without-server-restart-in-mysql-50/


Ordering a parent table by SUM() of colums of two of its child tables

SELECT s.name as storename, i.turnover + r.turnover as turnover
FROM companies c
INNER JOIN stores s on s.company_id = c.id
INNER JOIN ( SELECT store_id, SUM(total) as turnover
             FROM invoices
             WHERE QUARTER(date_time) = 4
               AND YEAR(date_time) = 2019
             GROUP BY store_id ) i ON i.store_id = s.id
INNER JOIN ( SELECT store_id, SUM(total) as turnover
             FROM receipts
             WHERE QUARTER(date_time) = 4
               AND YEAR(date_time) = 2019
             GROUP BY store_id ) r ON r.store_id = s.id
WHERE c.name = 'Com1'
ORDER BY turnover DESC

?


Excluding particular words from a string on a WHERE clause

Its not too difficult to build a query that does it. First create a query that joins on a table using LIKE with wildcards. Then exclude everything from this query.

To see what I mean, see this simple example: http://sqlfiddle.com/#!6/619fb/2

Or alternately I have reproduced an example here:

--Create tables for comparing data
CREATE TABLE Invoice
(
  InvoiceID INT NOT NULL IDENTITY,
  InvoiceData varchar(200) NOT NULL,
);

CREATE TABLE BadWords
(
  BadWordID INT NOT NULL IDENTITY,
  BadWord varchar(10) NOT NULL
);

--Insert data
INSERT INTO Invoice (InvoiceData)
VALUES ('This is some invoice data'), ('it is about'), ('something interesting that'),
('you should look at'), ('because its got invoice information');

INSERT INTO BadWords (BadWord)
VALUES ('this'),('invoice');

--Test query:
SELECT *
FROM Invoice
WHERE InvoiceID NOT IN (
  SELECT InvoiceID
  FROM Invoice i
  INNER JOIN BadWords b ON i.InvoiceData LIKE '%' + b.BadWord + '%'
)

Performance may be an issue with this if you have hundreds of thousands of rows. Without more information though it would be difficult to give you a high performing query (for example using a NOT CONTAINS free text query may give better performance).


Is this an appropriate development environment MySQL backup strategy?

I would also recommend MySQL dump with an additional twist, place the dump file under version control, at regular intervals, I would recommend when a change is complete. That way you can also track the changes to the database and make reconstruction easier


Postgres: Purpose of archiving in master?

WAL archiving is useful when you're running streaming replication, because there's a limit to how much WAL the master will retain.

If you don't archive WAL, and the replica gets s far behind that the master has discarded WAL it still needs, it cannot recover and must be replaced with a fresh base backup from the master.

It's also useful for PITR for disaster recovery purposes.


Adding a Group ID to a query where the sort by is not based on the Column your grouping by

The easiest way in my opinion would be to just add the randomly ordered sequential integer in the SELECT from Product before the join.

WITH Prod
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (ORDER BY CRYPT_GEN_RANDOM(4)) AS ProductGroupID
         FROM   dbo.Products
         WHERE  StudyID = @StudyID
         )
SELECT ProductID,
       QuestionID,
       ProductGroupID
FROM   Prod p
       JOIN dbo.Questions q
         ON p.StudyID = q.StudyID
ORDER  BY ProductGroupID,
          QuestionNumber


Here is what resolved the error, copied lovingly from a Microsoft forum

Here is what I'll be trying in a bit (from here)

1) Execute Diff backup in SQL Safe
2) Create Snapshot in VCD
3) Perform Upgrade
4) When fails add -T902 flag to startup parameters
5) Start SQL Server
6) Install CU 11
7) Remove -T902
8) Start SQL Server

The key here was to start up sql server with the -T902 parameter and then install the latest CU (which is 11 right now)


Postgresql 9.2.6 ignoring archive_command

Streaming replicas don't run archive_command because they do not generate WAL. They just replay the master server's WAL.

You can cascade streaming replicas, where one replica acts as a master for the next. But for fallback WAL archiving they both use the master's WAL stream directly.

So just configure the second replica to use the master's WAL stream.


AdonisJS: is something wrong with belongsTo() relationship design?

I think that the only problem with the linked documentation are the typos in the model image:

The users column id (FK) was meant to say id (PK).
The profiles column user_id (PK) was meant to say user_id (FK).
The link should have reverse direction (in all other diagrams, links start from the PK/referenced table and point to the Fk/referencing table).

The diagram under the belongsTo() example seems to be the correct one (and should be used in hasOne(), too).

The design shows a 1-1 relationship, or more accurately a 1 - 0..1 relationship, between users and profiles.

A user can have one (hasOne) profile. A profile belongs to (belongsTo) one user.

What seems to be missing from the diagrams is that profiles (user_id) should have a UNIQUE constraint, in order for the relationship to be true 1-1 and not 1-to-many. Perhaps the ORM creates such a constraint and it was missed when drawing the diagrams.


I will assume your table is named baz and is in schema public

create table baz (
  foo text primary key
);

insert into baz (foo) values ('hello');

select foo from baz;

Now you want to rename that column:

alter table baz rename column foo to bar;

But now your scripts are broken:

select foo from baz; -- nope!

So create a new schema, put a view in it, and alter your search_path to hide the table "behind" the view.

create schema qux;

create or replace view qux.baz as
    select
      bar as foo
    from public.baz;

set search_path to qux, public;

select foo from baz; -- works!

This is kind of the postgres way to do create synonym


Database Mail does not send the mail through SMTP when you call sp_send_dbmail.  The message is put on a service broker queue, and sent by a background process.

So if the transaction in which the trigger executes is rolled back, then the message will be rolled back from the queue, and the mail will never be sent.  So that's one reason why your mail may not be sent.

Otherwise you can check the log table:

SELECT * FROM msdb.dbo.sysmail_log