What is wrong with this a MySQL 5.6 procedure definition? Use a variable for table name

Typos, always typos.

Here is the final working code:

drop procedure if exists dividir_tabla_por_usuario;

delimiter //
create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100))
begin
  set @i = 0;
  set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c');
  prepare p1 from @q1;
  execute p1;
while @i <= @c do
  set @r = 0;
  set @q2 = concat('(select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t2)');
  set @q3 = concat('(select @r:=@r +1 as ord, ', columna_usuario ,' from ',@q2, ' as t3 where ord = ', @i ,' )');
  set @q4 = concat('(select ', columna_usuario ,' from ', @q3);
  set @q5 = concat('select * from ', tabla , ' where ', columna_usuario ,' = ', @q4 ,' order by ', columna_fecha ,' asc limit 30000');
  prepare p2 from @q5;
  execute p2;
  set @i := @i + 1;
end while;
end//
delimiter ;

call dividir_tabla_por_usuario('mytable', 'mycolumn1', 'mycolumn2');


Does it matter which database you connect to when querying across multiple databases?

In your example there is no difference.
But there can be difference if your query is more complicated, has parameters and the databases have different compatibility level/parameterization options.
Compatibility level of the database where the query is executed will affect cardinality estimations, so if your predicates contain AND OR conditions the estimated number of rows will be calculated according to different optimizator's rules, there are cases when someone migrates to a new server (say 2014) but leaves user databases compatibility level 100, in this case in he executes the query in the master's context new 2014 optimizator will do calculations, but if he executes the same query in user database the legacy optimizator will do estimations and they will differ.
Also forced parameterization can affect the plan.
If your query contain joins and constants in where filter, these constants will be preserved when estimation is done if database has simple parameterization, so the estimation will use statistics for filtering field.
But if the same query is executed in the context of database with forced parameterization the query will be parameterized and the plan will be built by using unknown value for the filter.


But i can't figure out how to create a table for each entity and pull it into one centralized table

It sounds like you want to read up on Joins: http://en.wikipedia.org/wiki/Join_%28SQL%29

A "join" basically lets you have a query that returns data from multiple tables, based on some criteria, such as records in two tables having a certain matching value. If you want to give the appearance of having this combined data all in one table, you could create a View ( http://en.wikipedia.org/wiki/View_%28database%29 ) of the query with all the joins. MS Access lets you use views to drive reports as easily as it lets you use tables for that purpose.


Is there a way to find the transaction count on a sql table?

You want to use the sys.dm_db_index_operational_stats DMF.

SELECT *
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'schema.table'), NULL, NULL);

There is a lot of very interesting and useful info in that DMF, but with respect to this particular question, the following columns should have what you are looking for:

  leaf_insert_count -- bigint -- Cumulative count of leaf-level inserts.

  leaf_delete_count -- bigint -- Cumulative count of leaf-level deletes. leaf_delete_count is only incremented for deleted records that are not marked as ghost first. For deleted records that are ghosted first, leaf_ghost_count is incremented instead.

  leaf_update_count -- bigint -- Cumulative count of leaf-level updates.

It won't show history, so take a sample once per day. And like all DMVs and DMFs, it probably gets recent upon service restart.


Nope.

The one query is a SELECT that fetches everything, then your application code does all the formatting work.

Or you make a messy Stored Procedure to do all that work.  And you spend longer than you would like debugging it.


Is it OK to keep a value which updates in a table?

Yes, that's non-normalized, but occasionally non-normalized designs win out for performance reasons.

However, I would probably approach it a little differently, for safety reasons. (Disclaimer: I don't currently, nor have I ever worked in the financial sector. I'm just throwing this out there.)

Have a table for posted balances on the cards. This would have a row inserted for each account, indicating the posted balance at the close of each period (day, week, month, or whatever is appropriate). Index this table by account number and date.

Use another table for holding pending transactions, which are inserted on the fly. At the close of each period, run a routine that adds the unposted transactions to the last closing balance of the account to calculate the new balance. Either mark the pending transactions as posted, or look at the dates to determine what's still pending.

This way, you have a means of calculating a card balance on demand, without having to sum up all the account history, and by putting the balance recalculation in a dedicated posting routine, you can ensure that the transaction safety of this recalculation is limited to a single place (and also limit security on the balance table so only the posting routine can write to it).

Then just keep as much historic data as necessitated by auditing, customer service, and performance requirements.


PostgreSQL what happens if TimeZone is not set?

It seems like PostgreSQL 10 still

Looks at the environmental variable TZ first
Proceeds to use the localtime library function.
Looks under the shared directory or the system directory for matching timezone information.

So it seems like the 9.1 docs are still accurate as of PostgreSQL 10.

Internally, you can see the list of timezone information by using SELECT * FROM pg_timezone_names(); (here in the source) which calls pg_tzenumerate_start which the reads the shared directory or system directory above.


Difference between TDE/VPD/Data Masking/OLS - Oralce

Actually they don't do the same.

TDE encrypts the data in the database, you need to configure the wallet and have it open before you can see/use the data after that.

VPD is an Access Control Mechanism, it allows you to define sub-datasets that will be owned by different users, allowing them to see/use only the data that they actually own (even if the same set of tables are shared among different VPDs)

To have more detailed information about each tool, please review the documentation on https://docs.oracle.com/en/database/


This is called a left anti-join. You want all users who don't have a verified address. So you do a left join to find all users and whether they do have a verified address, then discard all the ones that have a verified address.

Something like:

SELECT *
FROM users
LEFT OUTER JOIN addresses a
ON (users.id = a.user_id AND a.type = 'VerifiedAddress')
WHERE a.user_id IS NULL;

This may be easier to understand when expressed with NOT EXISTS as:

SELECT *
FROM users
WHERE NOT EXISTS (
  SELECT 1
  FROM addresses a
  WHERE users.id = a.user_id
  AND a.type = 'VerifiedAddress'
)

both of which will optimize to the same query plan, in general.


Where does SSMS Last database backup date come from?

Where does SQL Server get this date?

SSMS behind the scenes uses msdb.dbo.backupset to populate the data in the Properties page Last Database Backup. Note that the SSMS properties page does not give info about filegroup backup. It only gives info about full, diff or log backup.

You can use this script to retrieve SQL Server database backup history and no backups details


trying to log into correct database without password

When you use CREATE USER, all it does is add a row to mysql.user with host set to localhost, user set to jeffrey or peter, and the password column is filled in. All the DB privileges are defaulted to 'N'.

When you connect, run this:

SHOW GRANTS;

It will simply say GRANT USAGE. That means you can connect, AND NOTHING ELSE !!!

When you login as root, you must now explicitly tell mysqld to give grant privileges at various levels.

EXAMPLES

This will let jeffrey do everything except give away his privileges

GRANT ALL PRIVILEGES ON *.* to 'jeffrey'@'localhost';

This will let jeffrey do everything, including give away his privileges

GRANT ALL PRIVILEGES ON *.* to 'jeffrey'@'localhost' WITH GRANT OPTION;

This will let jeffrey do everything inside a database called jeff

GRANT ALL PRIVILEGES ON jeff.* to 'jeffrey'@'localhost';

Next time you login as root, run the following

DESC mysql.user;
DESC mysql.db;

This will show you what global (mysql.user) and database (mysql.db) privileges look like.

Please read MySQL Documentation on all the grants you can give out to users.


How to determine SQL server version install history? I.E. When it was updated last

Did you try this folder?

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache

I once needed it. And found all the previous setups there. You can check their last modified dates. Maybe it helps.


UPDATE on big table in PostgreSQL randomly takes too long

GIN indexes have a "fastupdate" mechanism where new data gets written to a specific section in a linear fashion.  Once that exceeds some set size (gin_pending_list_limit, which is set globally but can be overridden per index) the next process which happens along to write to the index gets assigned the task of consolidating those entries into the main part of the index, which can lead to long freezes for that process.  For the length of freezing you are seeing, you must have a pretty high setting for gin_pending_list_limit.
If consistent latency is more important to you than overall insert/update performance, you can disable fastupdate.  Or you could lower the value of gin_pending_list_limit to keep some of the benefit while lowering the freeze-up time.
If you need both consistency and overall efficiency, you could write a manual process/thread which will open its own connection and run select gin_clean_pending_list(...) on the index in a loop while the main update operation is ongoing.  That way you get the benefit of the pending list, while the latency of cleaning it up is all loaded onto a background process where it doesn't matter.  (Vacuum and autovacuum will also clean it up, but you can't really get them to run often enough to do a sufficient job of it, because of all the other work they also need to do.  Hence gin_clean_pending_list)


How do I add a partition to an existing table in mariadb / mysql?

First I need to deal with the error

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

This makes sense and partitioning means putting data into different files based on some condition. If the partition condition is not part of the tables primary key index this would not know how where to put the data.

alter table app_log_Test drop PRIMARY KEY, add primary key (`id`, `dateCreated`);

Next, I can re-run my alter table to add the partitions I care about.

ALTER TABLE app_log_Test
PARTITION BY RANGE (TO_DAYS(dateCreated))
    (PARTITION p_invalid_date VALUES LESS THAN (0) ENGINE = TokuDB,
    PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')),
    PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')),
    PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE);

If I need to add more partitions after that. I don't have to specify the partition scheme again I can just add the partition and its constraints.

ALTER TABLE app_log_Test
    REORGANIZE PARTITION p_max_future_dates INTO (
        PARTITION p_201811 VALUES LESS THAN (TO_DAYS('2018-11-01 00:00:00')),
        PARTITION p_201812 VALUES LESS THAN (TO_DAYS('2018-12-01 00:00:00')),
        PARTITION p_max_future_dates  VALUES LESS THAN MAXVALUE);

My table now looks like this.

show create table app_log_Test;
Create Table: CREATE TABLE `app_log_Test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `version` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`dateCreated`),
  KEY `app_log_dateCreated` (`dateCreated`),
  KEY `app_log_label` (`label`),
  KEY `app_log_event` (`event`),
  KEY `app_log_level` (`level`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `COMPRESSION`=tokudb_zlib

/*!50100 PARTITION BY RANGE (TO_DAYS(dateCreated))
(PARTITION p_invalid_date VALUES LESS THAN (0) ENGINE = TokuDB,
PARTITION p_201901 VALUES LESS THAN (737425) ENGINE = TokuDB,
PARTITION p_201810 VALUES LESS THAN (737333) ENGINE = TokuDB,
PARTITION p_201811 VALUES LESS THAN (737364) ENGINE = TokuDB,
PARTITION p_201812 VALUES LESS THAN (737394) ENGINE = TokuDB,
PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE ENGINE = TokuDB) */


Is there a way to use my Access accdb or accde file (Created on the desktop) on an ipad?

As far as I know - NO.

On iMac you can use emulator software, but on iPad there is no such possibility.


How to Update same table on deletion in MYSQL?

The easiest way to delete D is to do the following:

Have B inherit G and E, putting them on the same level as D.
Delete D

First, I'll load your sample data

mysql> DROP DATABASE IF EXISTS grijesh;
Query OK, 1 row affected (0.06 sec)

mysql> CREATE DATABASE grijesh;
Query OK, 1 row affected (0.00 sec)

mysql> USE grijesh
Database changed
mysql> CREATE TABLE IF NOT EXISTS `Employee` (
    ->   `SSN` varchar(64) NOT NULL,
    ->   `name` varchar(64) DEFAULT NULL,
    ->   `designation` varchar(128) NOT NULL,
    ->   `MSSN` varchar(64) DEFAULT NULL,
    ->   PRIMARY KEY (`SSN`),
    ->   CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO Employee VALUES
    -> ("1", "A", "OWNER",  NULL),
    -> ("2", "B", "BOSS",   "1"),
    -> ("3", "C", "WORKER", "2"),
    -> ("4", "D", "BOSS",   "2"),
    -> ("5", "E", "WORKER", "4"),
    -> ("6", "F", "WORKER", "1"),
    -> ("7", "G", "WORKER", "4");
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
+-----+------+-------------+------+
7 rows in set (0.00 sec)

mysql>

View all current relationships

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| D    | BOSS        | E    | WORKER      |
| D    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql>

With this UPDATE JOIN query

UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
SET B.MSSN = A.MSSN WHERE A.SSN = '4';

let's elevate G and E to the same level as D and show relationships

mysql> UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> SET B.MSSN = A.MSSN WHERE A.SSN = '4';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql>

At this point, D is nobody's boss.

Now, delete it and show relationships:

mysql> DELETE FROM `Employee` WHERE SSN = '4';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Please notice that you do not need a Trigger


Hash join spills to disk even though there is plenty of memory granted for the query

The 17GB memory grant is across the entire query. That 17GB isn't given in its entirety to a single operator. Each operator gets a fraction of the overall grant, and that amount is not dynamically reallocated during query execution.

If you go into the query's XML and search for MemoryFractions (or hover your mouse over operators in Plan Explorer), you'll see more information about the fractions assigned to each operator.

It shows the input side of the join gets 52.48%, but not the output side. If you have more memory required for the data coming out of the operator, you'll spill to disk.

To avoid the spill, tune indexes, tune the query, use the new min memory grant hint in 2016, etc.


general design pattern for smushing rows into one column

As others have mentioned, your goal is to denormalize the data and is not a usual use case for a relational database system, but it is certainly achievable. So if you really want to accomplish that, what you'd want to Google is something along the lines of "PostgreSQL concatenate multiple rows". The goal usually is to create a comma separated values list (CSV or something of a similar structure).
Doing so actually led me to this StackOverflow answer on using the string_agg() function in PostgreSQL.
For historical reference, here is Erwin's example from the aforementioned StackOverflow answer, leveraging string_agg() to smush multiple Actor values into a single field per Movie row in a table of Movies:
SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP BY 1;

In your case, you'd want to JOIN your people to your tags table (via your junction table). From the above example movie would be equivalent to your person_name field, and actor would be equivalent to your tag_name field. A query likely similar to this is what you're looking for:
SELECT P.person_name, string_agg(T.tag_name, ', ') AS tag_list
FROM people P
LEFT JOIN peopleTagJunctionTable JT
    ON P.id = JT.person_id
LEFT JOIN tags T
    ON JT.tag_id = T.id
GROUP BY P.person_name;


What's the best solution for distribution of one database over two servers?


Maximum possible memory usage: 16.2G (874% of installed RAM)

Staring at the mysqltuner output, I see something disturbing
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)

The 800 is the max_connections.
The 18M is a sum that includes read_buffer_size + sort_buffer_size + join_buffer_size

What is 800 * 18M ?
mysql> select 18 * 1024 * 1024 * 800 / power(1024,3);
+----------------------------------------+
| 18 * 1024 * 1024 * 800 / power(1024,3) |
+----------------------------------------+
|                                14.0625 |
+----------------------------------------+
1 row in set (0.00 sec)

That's 14G your DB Server cannot handle.
RECOMMENDATION
Add this to my.cnf
[mysqld]
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
max_connections = 100

then restart mysql and run mysqltuner.pl again. You will see lower numbers.
Give it a Try !!!


Unable to replicate schema changes in transactional replication

You cannot change the table name or change the primary key of a table that is involved in replication.

you can see the scripts here

You can replicate the schema changes if your publication has @replicate_ddl on.
However

  The ALTER TABLE ... DROP COLUMN statement is always replicated to all
  Subscribers whose subscription contains the columns being dropped,
  even if you disable the replication of schema changes.

You want to avoid running a full snapshot - you want a snapshot just with the affected object(s).

As Brandon Williams said on the comments:
First you check your publication and make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.

SELECT immediate_sync, allow_anonymous
FROM Your_Published_DBName.dbo.syspublications

Then you can change these properties as described here.

  when i change column name , what will be the effect on subscriber, do
  i have to change the name there as well? while adding subscription or
  will it be able to use the previous name as well

You will need to remove the table from the replication first. Then do your changes - like renaming a column or changing the primary key.
then you need to re-add the table to the publication. Generate a new snapshot - will only have the new version of the table. When you apply this snapshot to the subscriber database - the table there will be overwritten.

You can specify the destination table on the subscriber.

Therefore if you need different permissions, triggers, indexes, stats, or anything else to this table, you need to script all these and re-apply them on the subscription after the snapshot has been applied.

  how the changing table name works, if tableA was replicated and it
  resides at the subscriber, how will changes apply to it, after I
  change the name to tableB at the the publisher. will I have to remove
  it tableA from subscriber or will it be overwritten? or will new
  tableB will be crated at the subscriber?

tableA was part of publication1
you remove tableA from publication1
you rename tableA to tableB
you add tableB to publication1
you generate a snapshot of publication1 (hopefully with only tableB in it)
you add subscriber1 to publication1
you apply the publication1 snapshot on subscriber1
tableB is created on subscriber1


While taking a full backup of the database, SQL Server is unable to reuse the transaction log for the duration of the backup, even if the database is in SIMPLE recovery. If your database is very busy, or backups take a long period of time, this can require a large transaction log,  even if the database is in SIMPLE recovery.

 Why does SQL Server need to do this?

Think of the database as a bookshelf containing a set of encyclopedias, and each table is a book in that set. SQL Server is the bibrarian. The librarian needs to make a copy of every book in the set to get a snapshot of the set.

The librarian (SQL Server) starts copying books (tables) at one end of the shelf (database), and makes it's way to the far end. This takes time. If the data within the books is changing (ie, database transactions), getting a consistent (that's the "C" in ACID) snapshot becomes a challenge.

Consider this timeline:

The librarian copies book A.
The librarian copies book B.
Books A & Z are updated.
The librarian copies book C.
The librarian copies book D.
Books C, X, Y, Z are updated.
The librarian copies books E-Z. The backup is complete.

The librarian's backup is not consistent. It has some of the old data (Books A & C, and some of the updated data (Books X,Y,Z). In order for the data to be consistent, the librarian could keep track of what changed in books A & C, then roll forward those changes on her backup copy.

It might be OK for the librarian to have an inconsistent backup, but it's not ok for SQL Server to have an inconsistent backup. SQL Server's backups must be consistent to a single point in time.

How does SQL Server do this?

When SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. Upon restore, it will then roll those transactions forward or backward, as necessary, to ensure a consistent image is restored.

Backing up busy databases in Simple Recovery

Based on OP's comments, the database in question is 7TB, and takes 20 hours to back up. That means that the transaction log for this database must be large enough to accomodate 20 hours worth of transactions. Depending on the amount and type of activity, the amount of transaction log needed could very quite a bit.

To reduce the amount of transaction log needed, you can either try to make your backup go faster, or take your backups during a time of reduced write activity, or both.


LOAD DATA INFILE fatal error encountered

You don't use LOAD DATA INFILE for what you are trying to do.

LOAD DATA INFILE loads a delimited text file (such as CSV) full of raw data, that is sitting on the server, into a table.

What you have is a dump file, which is full of queries -- not raw data.

You have three basic options:

use phpmyadmin to apply the file
use the mysql command line client to load the file
open the file yourself in your code (however you'd open a normal text file for reading), read each line in, and execute each line as a query from your code.

A dump file contains the queries to recreate data structures, so its contents are simply executed as queries.  That's what phymyadmin is doing when you "load" it that way.

If the file contains DDL that drops and creates tables, you'll have to do a little more work, because you'll need to buffer what you read from the file, and only execute what you've collected so far each time you find a line ending with the ; delimiter -- or any other delimiter that may be specified in the dump file (a line beginning with DELIMITER changes the expected delimiter for subsequent statements until a subsequent DELIMITER statement is found).

But using the mysql.exe command line client on your local machine would probably be the easiest.

C:\>mysql [options] < "E:\Macdata\20131228\atelier.sql"

With a file structured like this, you can't specify what table it goes into.  If the file contains USE statements to change to the appropriate database, that will happen when those statements are encountered.  If it doesn't, you'll need to add the name of your schema right before the < in the command line.


MYSQL dump and problems character encoding

Assuming your source data contained only English and Greek characters, and was mis-exported with an ISO-8859-1 to UTF-8 conversion (rather than an ISO-8859-7 to UTF-8 conversion), you can get your data back by first repeating the missed conversion the other way around, and then doing the right one.

You could use iconv for this (available on pretty much all Linux distributions, and on Windows via gnuwin32).

You'd do something like:

$ iconv -f UTF-8 -t ISO-8859-1 < bad_dump_file.sql | \
  iconv -f ISO-8859-7 -t UTF-8 > good_dump_file.sql

As an example:

$ echo -n $'\xC3'$'\x90' | iconv -f UTF-8 -t ISO-8859-1 | \
   iconv -f ISO-8859-7 -t UTF-8 | hexdump -C
00000000  ce a0                                             |Î |
00000002

And 0xCE 0xA0 is the right UTF-8 encoding for Π.


Why do different accounts connecting to the same database see different data?

I see two possibilities, and they are not mutually exclusive.

First is that you are not really looking at the same table.  If you connect as SCOTT and select from EMP without qualifying the table name, you are selecting from SCOTT.EMP.  If you connect as FRED and do the same, you will be selecting from FRED.EMP.  Two different schemas with the same table name.  The two tables are totally unrelated and could very well have different structures in addition to certainly having different data.

The other is Virtual Private Database (VPD).  Two users actually selecting from the same table, but only seeing the rows they are allowed to see, based on the VPD policies.

With SQLplus or SQL Developer, connect with one account and SELECT TABLE_NAME FROM USER_TABLES;  Do the same with the other account.  Be sure to select from USER_TABLES, not ALL_TABLES or DBA_tables.  That will show you just the tables owned by the connected user.


How to change the display of an empty string in mysql command line tool?

Bug #20067 empty string returns NULL as dispalyed value

This bug is possibly fixed 5.0.23. Look at the document referenced by the above page.


How to backup MS SQL Server databases off-site?

I'm not sure I would advocate it as a good solution, neither performance wise nor as a best practice, but there actually is a possibility.

In short, the steps would  be

Make a backup on a swap file on the server
Create a temporary table with a varbinary field
Insert the backup into the table
Fetch column data using ADO.NET
Save the stream in a file

The full source code is available on codeplex Transferring backup files from a remote SQL Server instance to a local machine without network shares, FTP, HTTP

The gist of the code is:

Step 1:

_sql = String.Format("BACKUP DATABASE {0} TO DISK " +
       "= N'{1}\\{0}.bak' WITH FORMAT, COPY_ONLY, INIT, " +
       "NAME = N'{0} - Full Database Backup', SKIP ",
       _dbname, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 2:

_sql = String.Format("IF OBJECT_ID('tempdb..##{0}') IS " +
                     "NOT NULL DROP TABLE ##{0}", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))",
                     temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 3:

_sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM OPENROWSET" +
       "(BULK '{1}\\{2}.bak',SINGLE_BLOB) bck",
       temporaryTableName, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 4 & 5:

_sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);
SqlDataAdapter da = new SqlDataAdapter(_sql, _conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables[0].Rows[0];
byte[] backupFromServer = new byte[0];
backupFromServer = (byte[])dr["bck"];
int aSize = new int();
aSize = backupFromServer.GetUpperBound(0) + 1;

FileStream fs = new FileStream(String.Format("{0}\\{1}",
                AlocalPath, fileName), FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(backupFromServer, 0, aSize);
fs.Close();

The original code was written by Adrian Pasik under the bsd license and published on codeplex.


How can I handle multiple users for an online CRM?

From a database design perspective, it's possible to design a database where an "unlimited" number of different organizations could share the same database, but would not be able to access any data except their own.

However, that's a step that one would need to take very early on in the process.

NOTE: If the individual companies require access to the database itself for direct querying, then separate databases for each company are the best practical answer. It would be difficult (if not impossible) to allow true ad hoc querying of the underlying data while preventing Company A from viewing any other company's data in the database.

To implement a multi-organization database, you'd start with what we'll call the [Organization] table. This table would uniquely identify every organization using the database. Let's call the primary key on this table Org_ID.

All other database tables (except very basic tables that would hold values common to all organizations) would include an Org_ID column, and all queries would reference the column (in fact, if possible, I would make this column a part of each table's primary key). Users who could log into the application would be tied to one (and only one) organization, and would only be able to access the data for that organization. NOTE: in practice, few if any tables are likely to be common to all organizations. Even tables like US state postal abbreviations might not truly be safe to apply to all organizations, as some might only want the "50 states plus District of Columbia", others might want to include abbreviations for US territories, and still others might want to include Canadian province abbreviations.

Since this would impact almost every table in the database, and every query in the application, it's unlikely to be a practical change now, if you're "almost done with developing the platform."

If the organizations using the application are fairly large, or the server involved is not terribly large, then the solution may simply be to spin up additional servers as you get additional organizations, with (say) 1-10 organizations per server.

Background: I was peripherally involved in work on a time entry system much like you've described, that was implemented as a multi-organization system much like I've described.

Limiting to one organization per database is more secure, as it avoids the possibility of a bug in the application allowing one organization to see another organization's data (especially if any of the organizations in question are competitors; getting access to a competitor's customer data would be a very bad glitch, and would possibly result in lawsuits), and allows each organization to access their database directly for ad hoc querying or reporting.

A final note: I'm providing this from a general database design perspective. I haven't used MySQL in particular enough to know (for example) how many separate databases a server can support, either due to explicit limits on MySQL itself, or to practical limits.


You need to use NCHAR(1 - 4000) or NVARCHAR, either as NVARCHAR(1 - 4000) or NVARCHAR(MAX) for storing anywhere from 4001 to just over 1,073,741,822 characters (or possibly less if storing any supplementary characters as described below).

Technically, you can store Japanese characters in VARCHAR fields if you use a Japanese_* Collation that is associated with Code Page 932. However, that is considered a "legacy" approach and would still leave you with some issues. The appropriate way to handle this is to use a Unicode datatype as mentioned above. Please see the UPDATE section at the end for details about VARCHAR.

You will also want to specify a Japanese collation so that the data compares and sorts as expected. You can find the available Japanese collations using:

SELECT * FROM fn_helpcollations() WHERE name LIKE N'Japanese%';

And you use that value in the field specification like:

CREATE TABLE dbo.test
(
  JapaneseText NVARCHAR(3000) COLLATE Japanese_CI_AS_KS_WS
);

Please see the following section of MSDN pages for more info on using collation and what each of the CI/CS, AS/AI, KS, and WS mean, as well as BIN/BIN2 and SC (not shown above): Collation.

And depending on which characters you need to store, you might need to pay close attention to the collations ending with SC (i.e. "Supplementary Characters"). By default, NCHAR / NVARCHAR data is stored as UCS-2, which is very similar to UTF-16, but UCS-2 is always 2-bytes per character. On the other hand, UTF-16, in order to support more than 65,536 characters (max size of 2 bytes, or UInt16.MaxValue + 1) can store characters that are 4 bytes (known as "surrogate pairs"). Please see the following MSDN page on Collation and Unicode Support ("Supplementary Characters" section) for more details.

Absolutely do not use NTEXT. That has been deprecated since SQL Server 2005 came out! There is no benefit / reason for using it and, in fact, there are several drawbacks.

UPDATE

While not ideal, it is possible to store Japanese characters in CHAR / VARCHAR fields and variables. Doing so requires that the Database's default Collation be set to one that is associated with Code Page 932 (Shift-JIS). You can find that list of Collations by running the following query:

SELECT col.name
FROM   sys.fn_helpcollations() col
WHERE  COLLATIONPROPERTY(col.name, 'CodePage') = 932;

I did a simple test by creating a database with an entry from that list and ran the following statements:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'); -- Japanese_Unicode_CI_AS
SELECT COLLATIONPROPERTY(N'Japanese_Unicode_CI_AS', 'CodePage'); -- 932
SELECT CONVERT(VARCHAR(50), 0x944094B294CD985198EE9AD79AA0); -- 如抜範浪偃壅國
SELECT LEN('如抜範浪偃壅國'), DATALENGTH('如抜範浪偃壅國'); -- 7, 14

This works because Code Page 932 is a Double-Byte Character Set (DBCS), which is different than UCS-2 / UTF-16 which is also double-byte. A DBCS character set is one that is double-byte within an 8-bit encoding (like the Extended ASCII Code Pages). You can see in that last query that the DATALENGTH is twice the the character LENgth and that the data is in a VARCHAR type since there is no N prefix on the string literals and the CONVERT was to VARCHAR, not NVARCHAR. There are 4 DBCS Code Pages supported in Windows / SQL Server:

932 = Japanese (Shift-JIS)
936 = Chinese Simplified (GB2312)
949 = Korean
950 = Chinese Traditional (Big5)

Only use these if you absolutely need to, such as supporting interaction with a legacy system. Of course, the Collations are still fine to use, but store the data in NVARCHAR instead of VARCHAR.


pg_basebackup: could not connect to server: fe_sendauth: no password supplied

Your issue is that your .pgpass file is missing the database field, and all fields are required (although you can use it * for any match). As you are using pg_basebackup (that uses the replication protocol), you need supply that as "replication":

cat > ~/.pgpass <<EOS
${MASTER_PORT_5432_TCP_ADDR}:5432:replication:${REP_USER}:${REP_PASS}
EOS
...

Using * for the database field would work too, but replication is more strict.


Is it safe to run parallel innodb single-transaction dumps of individual tables?

While parallel --single-transaction dumps on tables are possible, there are many things you must take under consideration:
InnoDB Buffer Pool
When you mysqldump a single table, the connection launches a START TRANSACTION followed by SELECT /*!40001 SQL_NO_CACHE */ ..... Naturally, this will push all data pages for the dumped table through the InnoDB Buffer Pool. Launching parallel dumps would just make the Buffer Pool become a funnel because of passing multiple tables through it. The combined time of each table dump may not necessarily be be less that dumping all the table sequentially if the combined size of the table is greater than innodb_buffer_pool_size.
Binary Log Position
When doing parallel dumps with --master-data on a DB Server with binary logging enabled, you will quickly realize that the dump of each table is not perfectly consistent. Why ? When launching a mysqldump of everything, lines 23-25 will contain a CHANGE MASTER TO command before the dump data/schema. Each dump will have a different master_log_pos, especially coming from a live system.
Locking
You had made for following statement

I tested this on a development server and works well but without knowing the internal locking methods I am worried to use it in production

Performing a single transaction dump does not trigger any kind of locking. According to the MySQL Documentation on --single-transaction:

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

SUGGESTION
I have recommended doing parallel dumps in the past (See my old post How can I optimize a mysqldump of a large database? under the heading Option 3 : mysqldump separate tables into separate data files)
If you want to launch parallel dumps of tables in Production, it is best to setup MySQL Replication. Then on the Backup Slave, you can do the following:

STOP SLAVE IO_THREAD;
Wait until the SQL Thread stops processing (SHOW SLAVE STATUS\G shows that Exec_Master_Log_Pos stops changing)
Perform parallel dumps
START SLAVE;

GIVE IT A TRY !!!


Having issues in assigning mysql datatypes

To get rid of " GMT" in the fly, use @variables and SET.  Something like

LOAD ...
    ... ,
    ( cell, id, too, @issue, ... )
    SET issue = LEFT(@issue, 19)
    ...;


This is rather subjective based on the nature of the data, potential for change, deadlines, longevity of the project, storage space, performance requirements, etc.

However, the determining factor will likely be the probability that you will ever need multiple groupings for pages.   If you will, then implement a join table now.  If not, putting Group_Id inside the pages table is faster to build, less complex, will perform better, and uses less space.


Equivalents to `ora_rowscn` in other databases?

As I understand the question, the scenario is one where users read values from the database outside a transaction, and later attempt updates. The requirement is that the database detect when a lost update might occur.
SQL Server does not provide a direct equivalent for the Oracle pseudo-column ora_rowscn because it does not exclusively use row versioning to implement transaction isolation levels (so row versioning information is not always present). Nevertheless, there are a number of ways to implement optimistic concurrency control with SQL Server.
Using Row Version
One option is to add a rowversion column to the table. Often, this column will be given the same name in all tables (e.g. RV). The general strategy is to read the rowversion value when reading data, and to check that the value is unchanged when updating the row later on:
CREATE TABLE dbo.Example
(
    SomeKey     integer PRIMARY KEY,
    SomeValue   integer NOT NULL,
    OtherValue  integer NOT NULL,
    RV          rowversion NOT NULL
);

INSERT dbo.Example
    (SomeKey, SomeValue, OtherValue)
VALUES
    (1, 100, 1000);

-- Read data
SELECT SomeValue, RV
FROM dbo.Example AS E
WHERE SomeKey = 1;

-- Time passes

-- Attempt update
UPDATE dbo.Example
SET SomeValue = 123
WHERE SomeKey = 1
AND RV = 0x0000000000273711;

If the row has been modified, the UPDATE statement makes no changes and no error or warning is returned. It is up to the developer to check the number of rows affected by the statement, and take appropriate action (such as re-reading the source row, and alerting the user to the conflict).
Using an optimistic cursor
Server-side cursors are often used in the scenario we are discussing. SQL Server contains native support for OPTIMISTIC server-side cursors via a number of interfaces (e.g. API, ODBC, Native Client). If a rowversion column is present, SQL Server uses that to implement optimistic concurrency, without the developer needing to retrieve and check the rowversion value explicitly. Using a Transact-SQL cursor to demonstrate:
DECLARE UIcursor
    CURSOR GLOBAL
    SCROLL DYNAMIC
    OPTIMISTIC
FOR
    SELECT SomeValue
    FROM dbo.Example
    WHERE SomeKey = 1
    FOR UPDATE OF SomeValue;

OPEN UIcursor;

-- Read data
FETCH FIRST FROM UIcursor;

-- Time passes

-- Attempt update (note no rowversion reference needed)
UPDATE dbo.Example
SET SomeValue = 321
WHERE CURRENT OF UIcursor;

If the row has been changed since data was read, a warning and an error is returned:
Optimistic concurrency check failed. The row was modified outside of this cursor.
Msg 16947, Level 16, State 1, Line 1
No rows were updated or deleted.

If the table does not contain a rowversion, SQL Server transparently uses a reliable checksum to detect row changes. The same warning and error is generated if the checksum comparison fails. Using rowversion is more efficient than using the internal checksums, but both are equally reliable.
Note that a real implementation would be quite unlikely to use a global Transact-SQL cursor as shown above, but the concepts are the same when using whatever server-side cursor support is available within your development framework.


How to Add or subtract rows of one columns this example in sql 2014?

OK, so let's rephrase:

  for each id, if N=1, return price, else, return price for N=1 for the
  same id minus current price.

You can try this ugly:

select *, iif(tt.N = 1, t.price, isnull(tt.price, 0) - t.price)
from my_table t
left join (select * from my_table) tt on t.id = tt.id
where t.N = 1

Or a trendier version using first_value statement (starting from version 2012) and assuming we have, for all id, a row with N=1:

select *, iif(N=1,price, price - first_value(price) over(partition by id order by N) )
from my_table


How to change the login for a database's dbo user?

The group or login that is member of the sysadmin role is indepentent of who owns the database. The login that is mapped to the dbo of the database is basically the owner. You can change this via SSMS or with the query below:

ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[ReportServerTempDB] TO [sa];

You can find more information on the specifics about the database owner (dbo) from this question: What is the purpose of the database 'owner'?


Why do my Batch Requests increase when doing a Differential Backup

After monitoring the active sessions and queries during the high activity period, I have come to the conclusion that the third party software we use (LiteSpeed) to perform backups is the culprit.

There seems to be a lot of queries to get information about the maintenance plan, updating backup statistics, getting information about the server and the database, etc. This server is not only the one being backed up at this time but it is also the "Central Repository" for all our servers.

It appears I jumped the gun by posting here but at least this might prevent others from spinning their wheels on something that does not appear to be a real issue or one you can do anything about, other than not using the third party software. If our environment becomes bogged because of this, I would bet moving the "Central Repository" to another server would reduce the load on this one.


Self-answer from Kevin Struckhoff originally left as a comment:

Finally got back to this task and ran the upgrade successfully.

Turns out, since I use tablespaces in specific directories, the new 9.5 are right next to the old 9.3 dirs:

drwx------ 4 postgres postgres  4096 Oct  4  2014 PG_9.3_201306121
drwx------ 3 postgres postgres  4096 Apr 18 11:49 PG_9.5_201510051


What's better - setting up Always On fresh and migrating data, or using existing server with data and adding Always On?

To migrate or not to migrate, that is the question: Whether 'tis nobler in the mind to suffer the slings and arrows of past setup sins and mistakes, or to take arms against that sea of troubles and by migrating end them.

Or not. If you were asking "Should I upgrade in place or migrate?" I'd have directed you to an older (and longer) answer I gave which, in short, says MIGRATE - ALWAYS MIGRATE!

But you didn't ask about an upgrade. You asked about an Availability Group. I commend you to that post on in-place vs migration upgrade decisions and contemplate where you are. If you are on a virtual and like the version of SQL you are on and only have to reconfigure "stuff" - I see no pressing reason to do a migration. You simply can add windows clustering, setup the AG feature, and start building your AG. You can use this as an excuse to cover for past sins because most config changes, etc. are "online" settings you can make as you go. On a VM you can play with your CPU config and RAM, etc.

If you need a new version of SQL Server or made some crucial installation mistake (hard to think of any, perhaps 64kB drive allocation size, but that's not the end of the world and you need new drives anyway), then sure install afresh and migrate. Or if you are on a physical, have the licensing budget, and can demonstrate the need for more cores, then you have no choice but to build new and migrate.

BUT! You are building an AG. You now have a magical THIRD OPTION. You could build your new server just the way you want it. You could add that as a node into your AG. You could build the AG with this instance participating as a replica in the AG with two instances you like the config and sizing of better. And then just failover to one of those and evict your current node and kick it goodbye. So you could use your AG to sail on over to the new hardware with minimal user frustration and look like a hero.


How to collect NTFS file properties and insert into SQL Server table

SSIS is well equipped to handle CSV files and load them into SQL Server.

You can have a very simple package using the Flat File Source.

The dialogue and setup is a familiar windows "wizard" like process, and most of it is automated... what you need to pay attention to is that it has correctly guessed your file for lengths and data types. You can either adjust the settings in the connection manager or you can later change data types with SSIS tasks. Note that if you have say 10,000 rows of integers and then start getting characters the flat file source may easily assign an integer data type to that column, then fail when it encounters the characters. Thus with large files that may not be well structured you have to pay more attention to these settings. The Suggest Types... button allows you to increase the number of inspected rows, but I have found that even this can still recommend the wrong data types.

SSIS is a huge tool and you can perform data clean-up tasks or even split data into different tables from the single CSV. If you have different tables use tasks like Multicast or Conditional Split. You may also find that Data Conversion and Derived Column can help you efficiently produce the data you need as it moves through your package.

I wouldn't do much more than clean, split, modify, and load the data into SQL Server with SSIS though. SQL Server is highly optimized to produce aggregates, sorts, etc., while SSIS is less capable for such tasks. Tasks like Aggregate are blocking transforms which essentially means it can stall your SSIS package and consume a lot of memory.

As an example the below SSIS dataflow performs the following tasks:

Reads a CSV file
Creates derived columns which are just trimmed versions of the originals
Performs a look-up to see if the record already exists in the destination
If the record was not found then it is inserted in the destination


script for restore of postgres database localy from dump given on stdin - ambiguous redirect

I suggest this :

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -U foo some_db" | psql -U foo -d new_db

This will pipe ssh standard output into psql standard input.


Does it ever make sense to have a clustered and non clustered index for the same column

Before addressing the two questions, let me briefly define the difference between clustered & non-clustered indexes.

The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, it has the entire row).

The nonclustered index is also organized by the key columns. It implicitly includes the clustering key columns (if the table is clustered), or a pointer to the row (if the table's a heap). If any INCLUDE columns are explicitly specified, they will also be included in the index structure.

  Does it ever make sense to keep both of these?

There is a corner case where it makes sense to have a non-clustered index "duplicating" the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index. The non-clustered index does not contain the full row data, and thus it will take up less physical space. Because it takes up less physical space, SQL Server can scan the table with fewer IOs, and will make use of it for performance reasons.

You can query sys.dm_db_index_usage_stats, you can see the number of user_seeks and user_scans on the two indexes. This will help you see how the two indexes are being used, and determine the usefulness of the two indexes.

  If I delete one which one should I delete?

As a general rule, most tables should have a clustered index. (That's a whole other topic). Not knowing any details about your index usage or data access, I'd guess that if you were to drop one, you would drop the nonclustered index (but, that really is a guess based on what I know).

Depending on the clustering key, the data, the data access patterns, etc., the most correct answer might be to drop the clustered index and create a different clustered index. You may want to read more on Effective Clustered Indexes.


How to prevent asking for password when creating new database in PostgreSQL 10?

use the PGPASSWORD environment variable,

@SET PGPASSWORD=something_secret
psql -c "CREATE DATABASE mydb" -U postgres postgres

or just use a connection string

psql -c "CREATE DATABASE mydb" "user=postgres dbname=postgres password=something_secret"


You are missing a BEGIN and END around your body:

REPLACE FUNCTION create_category_training_job(category_id integer)
  RETURNS integer
AS
$BODY$
-- check if a job for this category is already queued and not started
DECLARE
  job_id Integer := (SELECT id FROM category_training_jobs WHERE startdate IS NULL);
BEGIN --<<<< HERE

  -- if a not started job is present return the id it
  IF job_id IS NOT NULL THEN
      RETURN job_id;
  -- if id is not present create a new job and return its id
  ELSE
      INSERT INTO
          category_training_jobs (category_id, submissiondate, startdate, enddate)
       VALUES
          (category_id, NOW(), NULL, NULL)
       RETURNING id;
  END IF;

END; --<<<< AND HERE
$BODY$
LANGUAGE plpgsql;

The parentheses around your insert are useless noise as well, you should remove them.


DTA helps for adding indexes but not removing indexes.  If you're faced with a database that already has more than, say, 5 indexes per table, the DTA isn't the answer.  You'll want to use the DMVs to evaluate usage of existing indexes.

Here's a script with a video tutorial I did a while back:

http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx


Why does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column?

The key is that you don't compare the same types.  When comparing a bigint to a numeric, the easier way is to 'expand' the bigint with the decimal places being 0 (like 1 -> 1.0), while the other way around it would mean rounding/truncation.  (In this specific case it is easy to see that the both lead to the same result, but what if the values are negative?)

So, what you get in your comparison, is a numeric to numeric comparison, which is not something a bigint index could serve.

It's worth a look which casts are possible and which is performed in these cases.  For this, here are the two rows from pg_cast:

SELECT castsource::regtype, casttarget::regtype, castcontext
  FROM pg_cast
 WHERE castsource::regtype = 'bigint'::regtype
   AND casttarget::regtype = 'numeric'::regtype;

 castsource │ casttarget │ castcontext
────────────┼────────────┼─────────────
 bigint     │ numeric    │ i

SELECT castsource::regtype, casttarget::regtype, castcontext
  FROM pg_cast
 WHERE castsource::regtype = 'numeric'::regtype
   AND casttarget::regtype = 'bigint'::regtype;

 castsource │ casttarget │ castcontext
────────────┼────────────┼─────────────
 numeric    │ bigint     │ a

According to the linked documentation page, castcontext

  Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.

So this means that the numeric -> bigint direction is happening 'by itself' (i. e. you not calling one of the casting operators explicitly) only if you assign the former to the latter.  In expressions like your comparison, this is not the case, so the parser will only consider the other way (marked with an i above).  That means, you get a numeric to numeric comparison, unless you force it otherwise.

Notes:

psql is a command line client to PostgreSQL, it does not do any sort of these things itself (I've edited the title accordingly)
using keywords as column names (like timestamp) is never a good idea - you'll possibly get unexpected parsing errors here and there, unless you are careful enough to double-quote them everywhere
using the Unix epoch as a timestamp might be cumbersome.  There is a rich functionality for 'real' timestamps in PostgreSQL - in most cases it is much easier to use it.


look for missing datetime records in database

You can do something similar.  First generate the minutes (if you do this often, probably you would like to store them in a permanent, indexed table), then pick the ones not in your table.  The query would look like (no looping!):

WITH minutes(m) AS (
    SELECT i::timestamp(0)
    FROM generate_series('2013-04-17 12:00:00', '2013-04-17 13:00:00', interval '1 minute') t(i)
)
SELECT m
FROM
    minutes
    LEFT JOIN your_table ON m = your_timestamp
WHERE your_table_id IS NULL;

You may have to transform your timestamp stored if the seconds part is non-zero.  And, with big amounts of data at least, you will need an index defined on the timestamp (and possibly your_table_id, it would help a lot if you use PostgreSQL 9.2).


Please Do the below written instructions:

Set the class path of latest version of Java in your system  .
set the path as JAVA_HOME in Environment variable too.

Hope this would help you.


SQL SERVER 2016 side by side with previous versions

Each instance is a separate service with its own set of folders, system databases, agent and binaries so they do not directly impact each other.  All of the instances, however, will share the management tools, and older versions of the management tools cannot manage newer instances.   You can avoid trouble by installing the oldest version instances first and install the newest version last.  This way SSMS, SSCM, Profiler, etc will be the 2016 version and capable of managing all previous versions.

Another thing to consider is your Max Server Memory setting.  You want to make sure that the combined total of all your max server memory settings minus the total memory in the box, will still leave enough memory for the operating system and other applications.

The last thing I would consider is your CPU.  It is possible for instances of SQL to so consume resources that another instance could effectively be 'starved'.  A work around for this is to set affinity mask so that each instance is assigned a set number of CPU.


Create a key-preserved view across Oracle tables with descending indexes

if you are not required to create view, but just to update using join, then we can use a workaround with a MERGE statement.

merge into TEST_KEY_PRES_A pa
using TEST_KEY_PRES_B pb
  on (pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2 and pa.VAL1 > 3 and pa.KEY1 = 'K15')
 when matched then update
   set VAL2 = 'V2-5-X';


Perhaps you've got a maintenance job - such as reindexing - that occurs once a week - that would cause a massive jump in diff backup file size. Or perhaps it's a once-a-week application job that imports or changes data?

If you're also taking regular transaction log backups (say once an hour), if you look at the file size of those backup files it may give you a clue as well as to when the change activity is.


The following is just insane ranting and raving...
If you leave all data in one table (no partitioning), you will have O(log n) search times using a key. Let's take the worst index in the world, the binary tree. Each tree node has exactly one key. A perfectly balanced binary tree with 268,435,455 (2^28 - 1) tree nodes would be a height of 28. If you split up this binary tree into 16 separate trees, you get 16 binary trees each with 16,777,215 (2^24 - 1) tree nodes for a height of 24. The search path is reduced by 4 nodes, a 14.2857 % height reduction. If the search time is in microseconds, a 14.2857 % reduction in search time is nil-to-negligible.
Now in the real world, a BTREE index would have treenodes with multiple keys. Each BTREE search would perform binary searching within the page with a possible descent into another page. For example, if each BTREE page contained 1024 keys, a tree height of 3 or 4 would be the norm, a short tree height indeed.
Notice that a partitioning of a table does not reduce the height of the BTREE which is already small. Given a partitioning of 260 milliion rows, there is even the strong likelihood of having multiple BTREEs with the same height. Searching for a key may pass through all root BTREE pages every time. Only one will fulfill the path of the needed search range.
Now expand on this. All the partitions exist on the same machine. If you do not have separate disks for each partition, you will have disk I/O and spindle rotations as an automatic bottleneck outside of partition search performance.
In this case, partitioning by database does not buy you anything either if id is the only search key being utitlized.
Partitioning of data should serve to group data that are logically and cohesively in the same class. Performance of searching each partition need not be the main consideration as long as the data is correctly grouped. Once you have achieved the logical partitioning, then concentrate on search time. If you are just separating data by id only, it is possible that many rows of data may never be accessed for reads or writes. Now, that should be a major consideration: Locate all ids most frequently accessed and partition by that. All less frequently accessed ids should reside in one big archive table that is still accessible by index lookup for that 'once in a blue moon' query.
The overall impact should be to have at least two partitions: One partition for frequently accessed ids, and the other partition for the rest of the ids. If the number of frequently accessed ids is fairly large, you could optionally partition that.


Simple SSIS Package Failed User does not exist or has not permission

The solution was that job needed to has an access to the default folder (on the server)

C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo

My colleage solve it and according to him it is some kind of bug which is patched in newer version of SQL Server.


(1) is referring to the service itself. If I go stop the SQL Server Agent service this value specifies how long it will wait for any active job to complete before forcible stopping it (the service) so it can shutdown.

With regards to (2) and (3), you would have to setup some monitoring script/job that tracks that information and performs an action based on your criteria.


Filtered index error when using two column condition

Where a filtered index on a computed column is too limited, you have the option of creating an indexed view.

The indexed view is maintained automatically by the database, so you do not need to worry about getting trigger logic correct for all possible DML operations. You also do not have to worry about complicated correctness problems under high concurrency.

The overhead of maintaining the view indexes is also typically less than for triggers.

For example, given a table:

CREATE TABLE dbo.MyTable
(
    PlantCD varchar(10) NOT NULL,
    InboundQuantity integer NOT NULL,
    OutboundQuantity integer NOT NULL
);
GO
-- Pretend the table has rows
UPDATE STATISTICS dbo.MyTable
WITH ROWCOUNT = 1000000, PAGECOUNT = 50000;

A suitable indexed view might be:

CREATE VIEW dbo.MyTableQtyDiff
WITH SCHEMABINDING
AS
SELECT
    MT.PlantCD,
    QtyDiff = SUM(MT.InboundQuantity - MT.OutboundQuantity),
    NumRows = COUNT_BIG(*)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity
GROUP BY
    MT.PlantCD;
GO
CREATE UNIQUE CLUSTERED INDEX
    CUQ_dbo_MyTableQtyDiff__PlantCD
ON dbo.MyTableQtyDiff
    (PlantCD);
GO
-- Pretend the view has some rows
UPDATE STATISTICS dbo.MyTableQtyDiff
WITH ROWCOUNT = 10000, PAGECOUNT = 50;

If you are running Enterprise Edition, automatic indexed view matching means a query written without referencing the view directly can still use it:

-- Enterprise Edition required
SELECT
    SUM(MT.InboundQuantity - MT.OutboundQuantity)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity;

The execution plan shows the indexed view being accessed:

For other editions, you would need to reference the view directly, and use the NOEXPAND hint:

-- Any edition
SELECT
    SUM(MTQD.QtyDiff)
FROM dbo.MyTableQtyDiff AS MTQD WITH (NOEXPAND);

Execution plan:

The same connection setting restrictions apply to indexed views as for indexed computed columns. You may choose to use the NOEXPAND version even in Enterprise Edition, since use of the view is guaranteed (not decided by the optimizer), and cardinality estimates will usually be better as well.

The effect of adding an indexed view depends on many factors, so you need to test it. Any safe solution has the potential to increase contention, but where an indexed computed column doesn't fit, an indexed view is often the next best option. If done well, the effect can be minimal. The overhead of an indexed view over a single table can be no more than that of adding a new nonclustered index to the base table.


How to get the best time from submited values?

You should be able to use CTE (common table expression) along with the ROW_NUMBER() function to select the top 1 for each email.

See here for a short primer on ROW_NUMBER().
And here for CTE.

Something like:

WITH CTE AS (
SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email, datesubmission) R, *
FROM MyTable
)
SELECT * FROM CTE
WHERE R = 1
ORDER BY datesubmission, email


How to write this SQL query for a XML type?

What you want here is not entirely clear from your question. My guess is that you want the value from the <Value> node where the <Name> node is Tid.

Shred the XML using the nodes() function on /Information/Groups/Group/Items/Item and add a predicate [Name/text()="Tid"] to check the value for <Name>.

declare @T table(X xml not null);
insert into @T(X) values
('<Information>
  <Groups>
    <Group Name="Monitor">
      <Items>
        <Item>
          <Name>Cid</Name>
          <Value>2323232323</Value>
        </Item>
        <Item>
          <Name>Tid</Name>
          <Value>6656565656</Value>
        </Item>
      </Items>
    </Group>
  </Groups>
</Information>');

select I.X.value('(Value/text())[1]', 'bigint') as Value
from @T as T
  cross apply T.X.nodes('/Information/Groups/Group/Items/Item[Name/text()="Tid"]') as I(X);


How to get YAML Python Library in PostgreSQL

This is a problem specific to EnterpriseDB. PostgreSQL does not ship Python, it merely ships a contrib module which your distribution is set to build against the system's Python. In the case of Windows, there is no Python for them to build against because there is no distribution. You can get any version of Python when you download "python" (more properly cpython). So instead, EnterpriseDB bundles a version of Python they provide as a distribution of PostgreSQL. Previously, that were bundling a statically linked Active State Python, now they're bundling cPython 3.3 (which they can dynamically link in). This is all from the docs here.

To configure your system to use EnterpriseDB Python though, you need to first follow a few steps. From the docs on configuration

  After installing Language Pack, you must set the following variables:
  set PYTHONHOME=C:\edb\languagepack-9.6\x64\Python-3.3

  Use the following commands to add Python, Perl and Tcl to your search path:

set PATH= C:\edb\LanguagePack-9.6\x64\Python-3.3\bin: C:\edb\LanguagePack-9.6\x64\Perl-5.20\bin: C:\edb\LanguagePack-9.6\x64\Tcl-8.5\bin:%PATH%

  After setting the system-specific steps required to configure Language Pack on Windows, restart the Advanced Server database server; see Section 5 for detailed information about restarting the server.

There are similar instructions provided for Linux and OSX

After you configure that pip and easy_install should work fine.


Every time you scan a big index (or a big table, if you prefer) that data has to be brought from disk to memory. When this happens, if the buffer pool is holding data from other objects and does not have enough free space to accomodate the pages you are reading from disk, something has to be discarded.

Low PLE means that this process happens too often, thrashing your buffer pool and overwhelming your I/O subsystem.

Some ways to improve it:

Add more RAM. Easy and not very expensive nowadays. Doesn't really solve the problem, but can be an acceptable short-term solution. How much RAM? Enough to hold the active portion of your databases. Only you can tell how much that is. Not all versions/editions of SQL Server and Windows can use the same amount of RAM, so make sure you're not limited by that.
Purge some data. Are you sure you need all the data you have in
there?
Create smaller indexes. That means including in your indexes the
minimum set of columns that cover your queries. Big scans work
faster on smaller indexes and require less RAM. The tradeoff is more
space used on disk and more operations to perform when updating the
table.
Create filtered indexes. If you are often filtering on a common
non-selective condition (eg: active=true or something similar) a
filtered index might help reducing the size of the indexes. This has
some impact on the application as well (some SET options
restrictions to make it work).
Use compression. Data is compressed both on disk and in memory, so
ROW/PAGE compression can be a way to reduce memory comsumption.
You will need Enterprise Edition and you have to give up some CPU,
but it's often worth it for huge objects.
Use the correct data types: using (n)char over (n)varchar or int when tinyint is enough will waste not only disk space, but also buffer pool space. This includes character and numeric data types: make sure you're using the right type/size/precision/scale for your data columns.


How to access an Oracle database from linux

Since you have root access to the server, you can su to the Oracle instance owner (typically oracle), connect to the database locally using OS authentication (e.g. sqlplus / as sysdba), and create new (or modify existing) Oracle users or roles as needed.

In case you're not familiar how su and login shells work on Linux, you will need to run su - oracle to ensure the Oracle user profile gets executed, setting up the shell environment properly. If the database was installed by an experienced person, that would include configuring all the necessary Oracle environment variables, allowing you to call its utilities, such as sqlplus.
If an attempt to run sqlplus fails with "command not found" or something similar, try to set the Oracle environment yourself by running, as the oracle user, source /usr/local/bin/oraenv. Note that oraenv may be elsewhere, not necessarily in /usr/local/bin, depending on your Oracle version, Linux flavour, and on how Oracle was actually installed.

Thanks to Michael Kutz  and EdStevens for valuable critique.


Best way to enter data in DB that is spread out date wise

Am I understanding that you pretty much have a work order, and that work order can be worked on for a number of hours on any day, and you want to store how many hours someone worked it each day? So if I work on a project for 2 hours on the 3rd, and 5 hours on the 10th, then it would store 12 hours in the total column?

If that's the case, I would approach it in a more event based method. Have a table for the activity (ActivityID, ActivityName...). Then Have a table for work entries for the activity (ActivityID, PersonWorking, DateWorked, PersonWorked...). The hours will each only be entered into the Work table, and have a view that would sum up each activity and show the days worked along with the total. And as a note, that database systems can usually handle a lot of rows listing 4-5 fields a bit easier than trying to just have 10 years worth of dates in the column headers and just spread everything out.

In my experience it is almost never a good idea to 1)store a computed value based on data in the same record row, and 2)store date data as field names. Unless you have a VERY SPECIFIC set of dates that will apply to all projects/activities, don't use them as field headers.


PL/SQL : query numeric data type by scale?

If I understand the question correctly you want to find numbers that have, for example, more than 4 digits of precision after the decimal? E.g., you want 3.0671, 3.06713, etc. but not 3.067 or 3.06 or 3. If that is the case then you could do something like:

select number from table where number - trunc(number,4) <> 0;

trunc here will simply remove everything ater the fourth decimal digi

(I'm not taking into consideration what Nick said regarding 2 and 2.00.)


You're invoking two different log functions here: log(numeric,numeric) and log(double precision), and the first is much slower than the second.

Notice how the functions call differ in the EXPLAIN (ANALYZE, VERBOSE) below,
run with PostgreSQL 11.5 (Linux Ubuntu):

Slow version:

explain (analyze, verbose) select count(value)
from (
         SELECT  log(greatest(1e-9, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.02..25.03 rows=1 width=8) (actual time=1174.349..1174.349 rows=1 loops=1)
   Output: count(log('10'::numeric, GREATEST(0.000000001, ((generate_series(1, 20000, 1)))::numeric)))
   ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.004..1.310 rows=20000 loops=1)
         Output: generate_series(1, 20000, 1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.123 ms
 Execution Time: 1174.385 ms

Fast version:

explain (analyze, verbose) select count(value)
from (
         SELECT  log(greatest(1e-9::float, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.02..25.03 rows=1 width=8) (actual time=6.693..6.693 rows=1 loops=1)
   Output: count(log(GREATEST('1e-09'::double precision, ((generate_series(1, 20000, 1)))::double precision)))
   ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.004..2.561 rows=20000 loops=1)
         Output: generate_series(1, 20000, 1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.096 ms
 Execution Time: 6.731 ms

greatest() is not responsible: considering the query with just log(x), if you cast x to numeric it will be as slow with or without greatest().


3 additional varchar(32) keys and also updating them -- too bad for performance?

Maintaining those indexes will have a cost.  It is not possible to say if that cost is "too bad" or if it is significant to you.  The cost will depend on your RAM and memory settings, on your data size, and on your storage system.  Do some benchmarking on your own system (or a clone of it) and see.

If 100,000 rows are arriving evenly throughout the day, any reasonably sized server should be able to cope with this load.  You might not want to use a AWS t2.nano.  But you didn't say how many rows are updated per day, but "not too often" doesn't seem hard to deal with.

Assuming all index pages other than the leaf pages are already cached, then an update of one random unique token to another random unique token would need to read one index leaf page (for the old value), read and dirty one table page, read and dirty one index leaf page (for the new value), and possibly read and dirty a 2nd table page if the new record can't fit on the same page as the old one, or if there is an index entry for the new value already and it needs to verify that entry is not "live" in violation of the unique constraint.  The WAL records covering all of this will need to be written (most likely fitting into one write request) and flushed.  This shouldn't take more than 100ms in the typical case if you have 5400 RPM hard disk, substantially less if many of those random reads are satisfied by the caches.  In the worst case could be a lot longer if someone else is syncing large amounts of data to disk at the same time and you get contention on that.


Running sys.dm_db_index_physical_stats in parallel

sys.dm_db_index_physical_stats

is a system table valued function.
Internally this will perform an openrowset call to an internal INDEXANALYSIS system data source.
create function [sys].[dm_db_index_physical_stats]
(
    @DatabaseId         SMALLINT        = 0,
    @ObjectId           INT             = 0,
    @IndexId            INT             = -1,
    @PartitionNumber    INT             = 0,
    @Mode               nvarchar(20)    = NULL
)
returns table
as
    return select *
        from OpenRowset
        (   TABLE
            INDEXANALYSIS,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )
GO

More info on these internal data sources here.
You can find the same INDEXANALYSIS TVF call in the execution plan:

And you can also see that it is a multi statement table valued function.

So the query will not be able to use parallelism due to reason #1: system table access

The list changes from version to version, but for example these things
make the whole plan serial on SQL Server 2012:

...

System table access (e.g. reading from sys.tables)

The TVF call will also be serial due to reason #2: Multi statement TVF

These query features are examples that require a serial zone in the

...

Multi-statement T-SQL table-valued functions

Source


Optimize a UNIQUE CONSTRAINT to make SELECT faster

For the best performance of your query the table should have an index on (AggregationLevelId, Serial). One index on two columns. The order of columns in the index is important. Since Serial is unique, this two-column index can and should be declared unique as well.

CREATE UNIQUE NONCLUSTERED INDEX [IX_AggregationLevelId_Serial] ON [dbo].[Code]
(
    [AggregationLevelId] ASC,
    [Serial] ASC
)

You can create such index in addition to existing indexes and constraints. There is no limitation. It will affect the performance of inserts and updates as any other extra index.

Most likely the optimiser would use a single seek in such index for your query, but occasionally I came across a situation when the query with MAX didn't perform well even with the proper supporting index. (I vaguely remember that it happened when the table had no rows for the given AggregationLevelId).

So, I personally use the equivalent variant of the query:

SET @maxCode =
(
    SELECT TOP(1) Serial
    FROM Code
    WHERE AggregationLevelId = @codeLevel
    ORDER BY Serial DESC
);

Optimiser should be smart enough to use the index with Serial ASC, even though the query orders by Serial DESC.


I think you will have an easier time to simply cast whatever the user throws at you to a date format that you want, to keep it all uniform. E.g. in PHP like this: date('Y-m-d', strtotime($userdate))

It can also be done in sql with convert(date, '28/09/2016'), however I think you need to specify the format. Link


From a quick test this end none of the built in collations can represent all characters in that string in a single byte code page so you would need to use a unicode datatype.

SET NOCOUNT ON;

DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ'

--The below without the problematic character returns 7 results
--DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَنِ الرَّحِيمِ'

CREATE TABLE #Results
(
name sysname
)

CREATE TABLE #Chars(
C NCHAR(1)
)
INSERT INTO #Chars
SELECT DISTINCT SUBSTRING(@StringToTest,number,1)
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND LEN(@StringToTest)

CREATE TABLE #Collations
(
     code TINYINT PRIMARY KEY
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM E08

DECLARE @AlterScript NVARCHAR(MAX) = ''

SELECT @AlterScript = @AlterScript + '
RAISERROR(''Processing' + name + ''',0,1) WITH NOWAIT;
ALTER TABLE #Collations ADD ' + name + ' CHAR(1) COLLATE ' + name + ';
EXEC(''UPDATE #Collations SET ' + name + '=CAST(code AS BINARY(1))'');
EXEC(''UPDATE #Collations SET ' + name + '=NULL WHERE ASCII(' + name + ') <> code'');
EXEC(''
IF NOT EXISTS(
SELECT *
FROM #Chars
WHERE NOT EXISTS(SELECT * FROM #Collations WHERE ' + name + ' = C COLLATE ' + name + '))
INSERT INTO #Results
SELECT ''''' + name + '''''
'');
'
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%CS_AS'
       AND name NOT IN    /*Unicode Only Collations*/
                        ( 'Assamese_100_CS_AS', 'Bengali_100_CS_AS',
                         'Divehi_90_CS_AS', 'Divehi_100_CS_AS' ,
                         'Indic_General_90_CS_AS', 'Indic_General_100_CS_AS',
                             'Khmer_100_CS_AS', 'Lao_100_CS_AS',
                         'Maltese_100_CS_AS', 'Maori_100_CS_AS',
                         'Nepali_100_CS_AS', 'Pashto_100_CS_AS',
                         'Syriac_90_CS_AS', 'Syriac_100_CS_AS',
                         'Tibetan_100_CS_AS' )

EXEC (@AlterScript)

SELECT * FROM #Results

DROP TABLE #Collations
DROP TABLE #Results
DROP TABLE #Chars


How can I authenticate mongodb server identity via mongodb shell?

I am trying to deploy a mongodb instance in my environment. I have
  settled a ssl connection so it is possible to authenticate clients
  (using a certificate they enter and agreed root ca by both the server
  and the client). Is there a way to authenticate the server identity
  too? (I could not find any reference to this issue) . I would like to
  use the mongo shell (without any side programming).

As per MongoDB documentation here MongoDB supports the following authentication mechanisms:

SCRAM
(Default)
MongoDB
Challenge and Response (MONGODB-CR) (Deprecated as of MongoDB
3.6)
x.509
Certificate Authentication.

New in version 2.6.

MongoDB supports x.509 certificate authentication for client authentication and internal authentication of the members of replica sets and sharded clusters.

x.509 certificate authentication requires a secure TLS/SSL connection.

Certificate Authority

For production use, your MongoDB deployment should use valid certificates generated and signed by a single certificate authority. You or your organization can generate and maintain an independent certificate authority, or use certificates generated by a third-party TLS/SSL vendor.

Client x.509 Certificates

To authenticate to servers, clients can use x.509 certificates instead of usernames and passwords.

Client Certificate Requirements

The client certificate must have the following properties:

A single Certificate Authority (CA) must issue the certificates for
both the client and the server.
Client certificates must contain the following fields:

keyUsage = digitalSignature
extendedKeyUsage = clientAuth
Each unique MongoDB user must have a unique certificate.
A client x.509 certificate’s subject, which contains the
Distinguished Name (DN), must differ from that of a <a
href="https://docs.mongodb.com/manual/tutorial/configure-x509-member-authentication/#x509-member-certificate" rel="nofollow noreferrer">Member
x.509 Certificate. Specifically, the subjects must differ with
regards to at least one of the following attributes: Organization
(O), the Organizational Unit (OU) or the Domain Component (DC).

  WARNING
  If a client x.509 certificate’s subject has the same O, OU, and DC
  combination as the Member
  x.509 Certificate, the client will be identified as a cluster
  member and granted full permission on the system.

For your further ref here


Auto update a column every 24 hours in Oracle database

Sure, just create a job. For example, update column every midnight:

begin
  dbms_scheduler.create_job
  (
   job_name             => 'UPDATE_TABLE',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'begin update table set column = value; commit; end;',
   start_date           => trunc(sysdate+1),
   repeat_interval      => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0',
   enabled              => TRUE,
   auto_drop            => FALSE
  );
end;
/


There's a couple of ways I can think of to do it:

You could have a "Deleted" indicator, which is set to "Y" when a record is deleted. Very simple, but it leaves deleted and active data in the same tables, which could cause performance problems if there's a lot of activity. Restoring a record is as simple as changing the indicator.
You could have a table that mirrors the structure of the one you want to delete from, and when you delete a record from the main table, insert that record into the "deleted_data" table. This allows you to move deleted data into separate table to give the main table better potential performance, but searching for data that is in either table could make things more complicated, and keeping the data structures in sync  (when they change) is also a bit more work that has to be done. In this case, restoring the data would involve removing the record from the "deleted" table and inserting it back into the main table.

In both cases, you may want to find any child records and mark them as deleted as well. I suppose you could do this with triggers or with code.


Actually there is but not where you'd intuitively think of looking for it. Have a look at SORT - this should do what you need:

SORT myset BY nosort STORE mylist


How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8

You are suppose to remove utf8mb4 character set & collation, and add relevant character set in your backup file & then try to restore.

Like :

:%s/SET character_set_client  = utf8mb4/SET character_set_client  = latin1/
:%s/SET character_set_results = utf8mb4/SET character_set_results = latin1/
:%s/SET collation_connection  = utf8mb4_general_ci/SET collation_connection  = latin1_swedish_ci/

:wq

I think still you will face issue for mysql schema restoration as its architecture is changed in MySQL 8.0 than MySQL 5.7


Implementing a Record Keeper/Rollback System for a MySQL Database

The idea of being able to roll back individual changes to a database is problematic.  Records are related to other records in RDBMS, so rolling back one change may cause referential integrity problems.  This means that any kind of rollback mechanism is going to have to be smart enough to know what the referential integrity constraints are and either cascade rollbacks or prevent rollbacks that would cascade (depending on your business rules).

Another approach which you might want to consider instead is to put all changes into a work queue and let the administrator approve them before they are written into the database.  In this way the contents of your database would be "moderated".  If there is a very high risk of data changes being rejected then the pre-approval approach may be a good one for you.

On the other hand, if it would be rare for a change to be rejected, then really what you want is an auditing system that records changes.  Rolling back a change is difficult, so many it just needs to be done manually.  The important thing is knowing what changed and how - and perhaps who changed it so you can avoid undesirable changes in the future.

A common approach for auditing changes to data is to have a history/audit table for each table that you are tracking.  The structure of the history/audit table is the same as the main table, but it adds auditing columns, typically:

Date/Time of the change
User who made the change
Change action (insert/update/delete)
Source of the change (if multiple systems/sources can change the data)

Essentially every time there is a write into the main table, the new main table values are also recorded in the audit table, with the extra columns filled out.  In the case of a delete, use the last known values.  Sometimes the audit record is written automatically by a trigger, sometimes people write application logic to record this information.


Alter table statement waiting (for hours, on dev machine), but no locks shown

Per the notes given on the wiki for that query, it only shows row level locks.

ALTER TABLE takes a table level ACCESS EXCLUSIVE lock. One or more transactions will be holding weaker locks on the table that prevent ALTER TABLE from acquiring its lock.

You can identify the lock being waited on with something like:

select *
from pg_stat_activity sa
inner join pg_locks lwait on (sa.pid = lwait.pid)
where sa.waiting = 't'
  and sa.query like '%ALTER%'
  and not lwait.granted;

(change "sa.pid" to "sa.procpid" on 9.1 and older).


What happens when I reach the last id with MySQL InnoDB?

Do not use GUIDs -- they have a serious performance problem at scale.  (This is because of their randomness.)

Within 5 years (probably within 1 year), you will decide on some significant change and this 'last id' question will be moot.

You are "removing" regularly?  Why?  If you are doing a REPLACE or DELETE, you probably have some other unique key on which you are finding what to remove.  Consider making that the PRIMARY KEY and eliminate the AUTO_INCREMENT.

I have a PRIMARY KEY on virtually every table I make, yet only about 1/3 are AUTO_INCREMENT.

Beware of INSERT IGNORE and REPLACE (and others) -- they pre-allocate AUTO_INCREMENT ids, but may not use them.


SQL-SERVER: how to get the maximum length of a variable of VARCHAR help

You are comparing integer to string. You need to compare length of customer name with max of length and not with direct customer name.

You could modify your code as below:

SELECT
CustomerName AS LongestName,
LEN(CustomerName) AS LengthOfName,
FROM
MsCustomer
where
len(CustomerName) = (SELECT MAX(LEN(CustomerName)) from MsCustomer)

I am not really sure what are you trying to do with STATUS = 'Customer' code.

There are tonnes of article on web for finding the record for max of certain things.

Hope this answers your concern.


It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently):

1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately updating them. Perhaps you need to look into insert using select statements to dynamically derive the full value, and then insert the rows in their final state.

2) By inserting 30 million rows, I suspect that you're altering your tables by a value > 10%. Oracle's automated jobs will reanalyze stats on any table with a change of 10% or greater; I make it a practice to do the same thing when I'm inserting/updating/deleting data. This is why you're seeing this behavior when you stop the 2nd procedure and analyze the table.

3) For the update statement, have you properly indexed the columns?

4) What is your expectation for the second procedure's run time? Does 1-2 hours to update 30 million rows sound too long? Only you can know that. For example, in the environment I work in, we can run our databases at 17,000 IOPS, but we force logging on everything because we run a data guard to a DR site, which adds some overhead on operations. So for me, 1-2 hours for a 30 million row update is right about where I normally am. Of course, this also depends on factors like row size (are you updating a single character, or a blob?), the where clause of the update (are you updating based on primary key, a foreign key, or based on an unindexed field?), and the overall load on your system at run time.

HTH.


If you just want a single value then you can just use the value method of the XML datatype with a little bit of XPath that translates to:

  Get me the first value of ...key/string which has a sibling element of
  ...value/string under the same item element with the value "football"

Something like this:

DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @yourTable ( yourXML )
SELECT
'<document>
      <item>
        <key>
          <string>Michael Burry</string>
        </key>
        <value>
          <string>CDO</string>
        </value>
      </item>
      <item>
        <key>
          <string>Adam Smith</string>
        </key>
        <value>
          <string>£20</string>
        </value>
      </item>
      <item>
        <key>
          <string>World Cup 2018</string>
        </key>
        <value>
          <string>football</string>
        </value>
      </item>......
</document>'

SELECT *, yt.yourXML.value('(document/item[key/string[.="World Cup 2018"]]/value/string/text())[1]', 'VARCHAR(50)') result
FROM @yourTable yt


Mongodb crash after WiredTiger Cannot allocate memory error

Potentially the unix box is forking the process from your application and causing a duplicate process to try to allocate the same amount of memory but crashing when it does so.


Failed to create appdomain "master.sys[runtime].2"

I finally understand why your server uses only 3Gb of RAM, you have 32-bit version of SQL Server: SQL 2012 (SP1) - 11.0.3128.0 Intel(x86) and it just cannot use more than 4Gb.

You should use 64-bit version of the Server to resolve your problem.

In previous versions (until 2012) it seems -g parameter could be useful:
SQL Server 2012 Memory

  -g startup parameter

  We used the -g startup option to change the default value of a region
  in SQL Server user address space known as "Memory-To-Reserve". This
  region was also known as "memory-to-leave or MTL.  The
  "Memory-To-Reserve" (or) -g configuration option are relevant only for
  a 32-bit instance of SQL Server.

  Multi pages allocation and CLR was part of Mem-to-reserve (-g)  in In
  previous SQL Server versions until SQL Server 2008 R2 , From Denali
  they are part of BPOOL (Controlled by Max server memory)  So you may
  have to remove –g if you have set it to give space for multipage
  allocator or CLR in earlier versions and migrating to Denali now.

But since you are on 2012 it seems the only way is to install SQL Server x64


Does the partition key also have to be part of the primary key?

Not at all.

One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK.

For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate.

When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.

Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries.  If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).

EDIT

For part 2, I think the answer is no as well.  The partition key is used to determine which partition to put the row in, but I don't think an index is maintained.  There may be stats in the back end on it though.