texts: Is there a portable profile/well defined subset of SQL/PSM?

texts:
https://en.wikipedia.org/wiki/SQL/PSM says that DB2's SQL PL and Mimer SQL's PSM implement the SQL/PSM standard and that the MySQL/MariaDB is close while HSQLDB stored procedures, OpenLink Virtuoso SQL Procedures, and PostgresSQL PL/pgSQL, Oracle PL/SQL, Microsoft/Sybase TransactSQL and Invantive Procedural SQL adopt the standard but are not 100% compatible.
Is there a  profile/well-defined subset of SQL/PSM that can be used to write stored procedures that are portable in practice among some reasonable subset of these databases (lets say Oracle, DB2, Postgres, MySQL, MariaDB, SQLServer)?  If so is this subset documented somewhere?


texts: Why do some rows returned by sys.dm_exec_query_profiles have "???" for the physical operator name?

texts:
SQL Server 2014 introduced the sys.dm_exec_query_profiles DMV which provides a way to monitor real time progress of executing queries. Recently I was looking at a somewhat complex query in SQL Server 2016 SP1 and noticed that sys.dm_exec_query_profiles contained extra nodes that weren't present in the query plan. These nodes had ??? for the physical_operator_name:

For the parallel version of the query there was just one hidden node. The node had an object_id associated with the inner table of a nested loop join. According to the node_id here is where the node should have appeared:

Running the query with MAXDOP 1 resulted in a few more hidden nodes. The ??? node showed up in the same place as before:

There was also a new one not present in the parallel plan:

These only seem to show up around some nested loop joins. I cannot recall seeing this behavior on SQL Server 2014. Unfortunately, the query is complex and I cannot upload an anonymized plan.

What's going on here? Why does sys.dm_exec_query_profiles report extra query plan nodes that don't show up in the graphical plan?


texts: Can I get top 10 remote clients by average query count per minute?

texts:
Is there any way for me to see the top clients of my sql server?
I want to figure who is executing so many queries on my server. Ultimately I would love to see the application name(like in sql profiler) and IP/name of the server, but even knowing which web server the queries come from would help alot.

Remote Client | Database         | Executions/min
--------------------------------------------------
    XXXX      | FooDb            | 88.00
--------------------------------------------------
    XXXX      | BarDb            | 43.00
--------------------------------------------------

Instead of XXXX it could be - remote server name, IP, ApplicationName (like in SQL Profiler) - any of these.
I think something like this is possible in SQL Profiler, but I cannot run it because this is production environment, and the performance is already bad


texts: User privileges error on server maria db Create trigger Drop trigger

texts:
User privileges error on server maria db

User : user1
DB : db1

Error

  15:00:46 DROP TRIGGER IF EXISTS audit_trigger Error Code:
  1419. You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe
  log_bin_trust_function_creators variable)
  0.266 sec

Script

DELIMITER $$
DROP TRIGGER IF EXISTS audit_trigger$$
CREATE TRIGGER `audit_trigger` BEFORE UPDATE ON `table` FOR EACH ROW
BEGIN
    IF OLD.dateModify <> NEW.dateModify THEN
        INSERT INTO  table SELECT * FROM thistable where id = NEW.id ;
    END IF ;
END $$
DELIMITER ;

Is it possible to give super privileges for user1 to only db1
i dont want this user to have access to other tables on the server


texts: Redesigning a table that has a lot of columns (not always related)

texts:
I'm new to database stuff so I apologize if I say anything ridiculous..

I work for a company that runs a website for a certain industry. We're not very large at the moment but our parent company wants to push our product out to more places. I'm worried about this because our database was never designed with ANY plan in mind (e.g, normalization doesn't exist, there are maybe 70 unused tables out of about 400- plenty of more unused columns on the tables we do use, etc.,) and we're running on a really shitty database program (pervasive SQL).

The company has at least recognized the need to move away from pervasive. We're planning to go to mariadb. To go along with this, there is a rare opportunity to potentially redesign some parts (or all of) the database but no one really cared to look into it- and that's how I ended up here.

SO, all that being said I just want to know if I'm on the right track here with my redesign so far.

Let's say we have a table that holds basic settings for companies on our system. Some of these settings are relevant to notifications (i.e what events will trigger e-mails for them), some are related to billing, some to how they create orders / what info they see on their orders, etc., This table has 334 columns and a LARGE amount of them only store a CHAR(1) which can be Y/N. (Side note: am I correct in assuming BIT will take up less space?)

What I've been doing is separating the parts (i.e, settings for orders go in their own company_order_settings table which has the company's id in it). The only thing is at this point I already have ~8 tables just for these different settings...is this normal? In general these things will (usually) be accessed separately so I'm not concerned about large joins. This large number of purely settings related tables will also happen when I refactor the user tables (which hold info on the users at said companies- in a similar number of columns).

If anyone wants more info let me know.


texts: Source Database of a restore stuck Restoring

texts:
I have copied a database vn_Budget_2021_BofDOct29 to the database vn_Budget_2021_Rig2. I did this through Tasks->Restore Database shown in the second image.
I saw this similar issue except the database I've restored isn't the one hanging. Its my source DB that doesn't require any restoring in iteslf.
SQL server databases stuck in restoring state
How do I get it stop hanging? I don't need to restore this database to the same name. The last backup is a few days old and theres nothing wrong with the data.

Method of restoring. No logs were taken which may be the issue?


texts: MySQL 5.5 to 5.7 doesn't work, apparently this is a feature?

texts:
Has anyone found a way yet of circumventing MySQL 5.7s insistence that it's Master must have a server_UUID.

I currently have multiple instances of MySQL 5.5 over various sites, and want to look at upgrading to MySQL 5.7.
To do this I was planning to set up a test-site using some MySQL 5.7 databases running as a set of master > slaves from my main site.

But this failed at the first hurdle, as MySQL 5.7 insists that the Master must have a SERVER_UUID !! Depsite the official documentation telling us we should upgrade our slave databases before we upgrade the master.

  In other words, when upgrading to MySQL 5.7, the slaves must be MySQL
  5.7 before you can upgrade the master to 5.7.

http://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html

As it stands the only option appears to be to install, configure, and test a copy of MySQL 5.6 (which I'll never actually use) to act as an itermediary between my live system and test system. But that just introduces a whole load of additional complexity when trying to run like for like testing between 5.5. and 5.7 slaves (e.g. is it MySQL 5.7 that caused problem X, or was it MySQL 5.6 doing something on the way through etc).

I see that there is a BUG report for this, which they have since decided is Not actually a BUG, but a feature!!!


texts: Logging to a file in PostgreSQL windows service

texts:
I need an alternative for running the following command:

C:\xxx\pgsql\bin\pg_ctl" -D "C:\xxx\pgsql\data" -l "C:\yyy\log\pgsql.log" start.

This way the server starts and logs to C:\yyy\log\pgsql.log.
When I try to register the server as a service though - no logging options are available and the server logs to Event Viewer.
From pg_ctl documentation:

  pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options]

How can I force the server to log into a file?
Note: I don't want to use one-click installer, I just want to work with unzipped binaries.


texts: In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;


texts: databases/tables with nonconventional names

texts:
Disclaimer: not an administrator

How does one deal with non-kosher names such as 'data?base' ?

I'm always one to ensure the cleanliness of my databases, but after finally installing phpmyadmin to aid in my mysql server (where before it was all manual). I seemed to have slipped up.

I logged in remotely through a limited access account and saw an option create a database. Thinking that I disabled create on that account, I went ahead and selected that option expecting and error. Nope, it actually worked, so I revoked access as well as double checked my permissions before I came to the quandry of removing the database.

The original database was named sig_db5 which phpmyadmin left as sig?db5 in the create database option (the default name in that context).

mysql> drop database 'sig?db5';

yeilds to

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sig?db5'' at line 1

I can assure you that the database does indeed exist, but I'm in disbelief that if I can't remove it due to that character, how the heck did it get created.

+---------------------+
| Database            |
+---------------------+
| ******************  |
| ******************  |
| sig?db5             |
| ******************  |
| ******************  |
| ******************  |
| ******************  |
| ******************  |
| ******************  |
+---------------------+

(censured other names)


texts:
Is it possible to create or simulate a partition-wise join in Mysql/Innodb??  Here is a good description of partition-wise joins....

https://blogs.oracle.com/datawarehousing/entry/partition_wise_joins
*A partition wise join is a join between (for simplicity) two tables that are partitioned on the same column with the same partitioning scheme.

If you now join the two tables on that partitioned column you can break up the join in smaller joins exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets, all values required to do the join live in the equivalent bucket on either sides. No need to talk to anyone else, no need to redistribute data to anyone else... in short, the optimal join method for parallel processing of two large data sets.*


texts: UTC date in DMV dm_broker_queue_monitors

texts:
It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so?

I am wondering how to get local time from this view? I can get current time offset, but that  is not valid for older dates (before daylight saving), so it is not really a correct way to do that.


texts: Convert rows into columns MySQL DataBase

texts:
I have two tables and I would like to generate a query relating the tables.
Until now I have this query:

SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID

but I return the 3 rows repeating the data in table 1.

I would like my query to return the data as follows (see image)

Any suggestions to see if it is possible to make my requirement?

Thank you!

===============================

UPDATE

I Tried with this query

SELECT  Table1.*,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ALTA') AS Name1,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ATENCION') AS Name2
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FK_ID
GROUP BY Table1.ID

but in my Name2 always is NULL


texts:
mysql> select  count(*) from num;
+----------+
| count(*) |
+----------+
|  9918530 |
+----------+
1 row in set (59.18 sec)

It is horrible, 59.18 sec for just a simple select command!
How to improve MySQL performance in my windows10?


texts: Restrict Postgres roles by IP address outside of the pg_hba.conf file

texts:
I am currently adding Postgres support to a deployment tool that currently only supports MySQL. This tool dynamically provisions and configures a database, among other things.

In MySQL, we can restrict users to a particular set of IP addresses, with SQL, as part of the grant command like so:

GRANT ALL PRIVILEGES ON my_database_name.* TO 'my_user_name'@'192.168.101.%' IDENTIFIED BY 'my_secret_password'
GRANT ALL PRIVILEGES ON my_database_name.* TO 'my_user_name'@'internal.example.com' IDENTIFIED BY 'my_secret_password'

In Postgres, it seems like the only way to set up a similar arrangement is to first create the user without restriction:

CREATE USER "my_user_name" WITH PASSWORD 'my_secret_password'

I've got it sorted out how to then programmatically restrict this role to a particular database, but it seems like the only way to restrict the Postgres user by host is to create entries in the pg_hba.conf file.

This is problematic, as there are many users/hosts being created programmatically by an agent that does not have file-system access.

It looks like our only three options around this are to:

Manipulate the pg_hba.conf without using SQL to restrict a role, after it has been created using SQL
Allow "all", instead of particular users, for an allowed host
Restrict access to localhost only, and connect using a ssh tunnel

This seems less than ideal, and makes me wonder if I am overlooking an obvious solution. Is it possible to restrict Postgres roles by IP address using only SQL commands?


texts: Mysql sequential numbering based on MAX of another column

texts:
I've a table lp_pictures which have these fields:

[id | plate_uid]
[1  | aaa]
[1  | aaa]
[1  | aaa]
[1  | bbb]
[1  | bbb]
[1  | bbb]

and i need to UPDATE the field id to becomes:

[id | plate_uid]
[1  | aaa]
[2  | aaa]
[3  | aaa]
[1  | bbb]
[2  | bbb]
[3  | bbb]

The table structure is:

CREATE TABLE `lp_pictures` (
  `id` int(5) NOT NULL,
  `plate_uid` varchar(128) NOT NULL,
  `uid` varchar(64) NOT NULL,
  `filename` varchar(128) NOT NULL,
  `extension` varchar(4) NOT NULL,
  `ip_submitted` varchar(15) NOT NULL,
  `date_submitted` datetime NOT NULL,
  `mode_submitted` varchar(15) NOT NULL,
  `ip_validated` varchar(15) NOT NULL,
  `date_validated` datetime NOT NULL,
  `user_validated` varchar(64) NOT NULL,
  `size` bigint(20) NOT NULL,
  `combination` varchar(9) NOT NULL,
  `type` varchar(15) NOT NULL,
  `oldtimer` tinyint(1) NOT NULL DEFAULT '0',
  `owner` tinyint(1) NOT NULL,
  `name` varchar(128) NOT NULL,
  `email` varchar(128) NOT NULL,
  `notify` tinyint(1) NOT NULL,
  `main` tinyint(1) NOT NULL,
  `source` varchar(512) NOT NULL,
  `comment` text NOT NULL,
  `meaning` text NOT NULL,
  `like` bigint(12) NOT NULL DEFAULT '0',
  `score` bigint(20) NOT NULL,
  `share` bigint(20) NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `blocked` varchar(1) NOT NULL DEFAULT '0',
  `fb_post_id` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`uid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

The order should be assigned based on date_validated field. (The lower it to the first date).

I need an mysql UPDATE instruction.

Thanks a lot !!


texts: What kind of a lock does IN keyword takes in SQL

texts:
If I have a query in which I have used IN keyword than what kind of lock will be taken by IN keyword.

Query is :

UPDATE table1 SET b_id where id in (SELECT id from table1 where id IS NULL )

I wanted to know the type of lock taken by the query when IN keyword is used.


texts: MySQL: Foreign key constraint on composite key with the referenced table a function of a constituent column of the key

texts:
I have two MySQL tables customFieldsText and customFieldsMultipleChoice. Both tables have an auto-incremental unsigned int primary key.

I'd like to create a new table called customFields with the following two columns:

customFields
 - id [int] [unsigned] [auto-increment]
 - type [string] [not nullable]
 - foreignId [int] [unsigned] [not nullable]

I'd like to enforce that [type, foreignId] is a unique key on this table. I would like to have "something like" a foreign key constraint that works this way:

if type == 'text' then
   enforce that foreignId can be found in customFieldsText table
if type == 'multiple_choice' then
   enforce that the foreignId can be found in the customFieldsMultipleChoice table

I'm not too experienced with SQL and I don't know if this is possible at all. I think it's probably clear what I want to achieve here: I want to be able to uniquely identify all my customFields by a single unsigned integer key. I inherited the two tables mentioned above so I'd be disinclined to change their schema. How would you solve this issue? Is there a way to create such a foreign key constraint?


texts: Search multiple values in multiple columns

texts:
I have a table results with this fields:

id
results_id
number_one (int)
number_two (int)
number_three (int)
number_four (int)
number_five (int)

Each number_* column can contain a number from 01 to 90.
I have a search form with 90 checkboxes. A user can tick up to 5 checkboxes.
Once a user select n checkbox, I need to build a query that look for a row that contains all those values, but a number can be in any of the five columns.
Example:
a user ticks checkbox 02, 12 and 20. I need to retrieve all the rows with all three number but I a number can be in any of the number_* column. The 20 can be in column number_one, 02 could be in column number_three and 12 in column number_four.
How can I build this query?
Also, would it be better to use one column instead of five columns?
results table:

id
result_id
numbers (string)

Column numbers contains 02-12-20-57-84 where - is the separator.
Example:
a user ticks checkbox 02, 12 and 20. so my query would be
SELECT *
FROM results
WHERE numbers LIKE "%02%"
    AND numbers LIKE "%12%"
    AND numbers LIKE "%20%";

UPDATE: I am using MySQL at the moment with this setup:

id
results_id
number_one (int)
number_two (int)
number_three (int)
number_four (int)
number_five (int)
date
city (foreign key)

I am planning to move look for optimisation and faster alternatives. I can migrate to any other database just for this specific table. I can also change table structure if needed.
There are around 150k records in that table doing a query similar to:
SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)

Will a migration to another DBMS type bring benefits?


texts: How to select two columns from two different tables using foreign keys from another third table?

texts:
I am using MYSQL and here is the schema that I have:

Here is my first table user

+-----+------------------+
| id  | name             |
+-----+------------------+
| 101 | Daniel Radcliffe |
| 102 | Elijah Wood      |
| 103 | Robert Downey Jr |
+-----+------------------+

Here is my second table movie

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Harry Potter 1      |
|  2 | Harry Potter 2      |
|  3 | Harry Potter 3      |
|  4 | Lord of the Rings 1 |
|  5 | Lord of the Rings 2 |
|  6 | Lord of the Rings 3 |
|  7 | Iron Man 1          |
|  8 | Iron Man 2          |
+----+---------------------+

Here is my last table list

+---------+----------+
| user_id | movie_id |
+---------+----------+
|     101 |        1 |
|     101 |        2 |
|     101 |        3 |
|     102 |        4 |
|     102 |        5 |
|     102 |        6 |
|     103 |        7 |
|     103 |        8 |
+---------+----------+

Now I want to display name and movie side by side something like this:

+------------------+--------------------+
| Daniel Radcliffe | Harry Potter 1     |
| Daniel Radcliffe | Harry Potter 2     |
| Daniel Radcliffe | Harry Potter 3     |
| Elijah Wood      | Lord of the Rings 1|
| Elijah Wood      | Lord of the Rings 2|
| Elijah Wood      | Lord of the Rings 3|
| Robert Downey Jr | Iron Man 1         |
| Robert Downey Jr | Iron Man 2         |
+------------------+--------------------+

The list table has foreign keys for user_id and movie_id which reference user.id and movie.id respectively. So how can I use JOINs to display the above data.


texts:
After Install Oracle 12.1.0.2.0 i can not access https://10.10.20.156:5500/em

 netstat -a
 not show port 5500

C:\Users\Administrator>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 03-AUG-2019 16:53:26

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.20.156)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                03-AUG-2019 16:39:23
Uptime                    0 days 0 hr. 14 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File
C:\app\oracle\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\oracle\diag\tnslsnr\oracle12c\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.20.156)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 3 16:58:09 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select dbms_xdb_config.gethttpsport() from  dual;
DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
5500
SQL> select dbms_xdb_config.gethttpport() from  dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
2200
SQL>

Thanks in advance.
Edit : here is "lsnrctl services"

C:\Users\Administrator>lsnrctl services

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 03-AUG-2019 18:23:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.20.156)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


texts: How to automatically drop and restore PostgreSQL database

texts: I am using dhis2 with PostgreSQL as database but I want to verify my backups from the cloud by restoring and checking whether the data are ok but I need it to be automatic.


texts: How to store a date without the year component

texts:
I have to store climate (temperature) data for a few cities. This would mean that each city will have 365 values  (ex: The value for Jan 1, is the average temperature for that city for all Jan 1 for 50 years (1955-2004).

Hence I am not at all concerned with year. How do I store the date in a date column? Do I store Jan 1 as Jan 1 2009 ( a non-leap year)? Or is there any better way out?


texts: Sql Azure: The session limit for the database is 8000 and has been reached

texts:
I am getting following exception in SQL Azure, my SQL Server database is a web version, and I am getting this error in SQL Server Management Studio (when connecting). Also this is happening when connecting through my web application interface.

  Resource ID : 2. The session limit for the database is 8000 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.
  Login failed for user '--'.
  This session has been assigned a tracing ID of 'f47cc110-a9ef-439b-b575-4240aba929eb'.  Provide this tracing ID to customer support when you need assistance.

This seems to be like Windows Azure limitation, but if anyone came across such situation please let me know.

Thanks.


texts: How to take mysqldump with Generated column?

texts:
I am getting below error while importing the dump,

  ERROR 3105 (HY000) at line 82: The value specified for generated
  column 'column_name' in table 'table_name' is not allowed.

When I describe the table with error, there are some generated columns in it.

  option_type         | varchar(45)  | YES  | MUL | NULL    | VIRTUAL
  GENERATED

What is the correct command to use when taking mysql dump for the database contains generated column?

MySQL server source and destination version:

  mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))

MariaDB mysqldump CLI version:

  mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)


texts:
I'll try my best to explain this.
I've got a spreadsheet (.xlsx) with 220 rows and 15 columns. I need to make it into 3rd normalized form eventually, but how do I get the data from the spreadsheet into a database so I can start changing it? I've tried looking on Google but I can't find anything.

(If this is not where this question please link me to correct direction and I'll close the question)


texts: Are CNAME aliases for connection strings a good idea?

texts:
To be very flexible and move database to different servers more easily, we came up with the idea of using an alias per database. So by simply changing a DNS entry, we can route all queries to a different database server without changing the connection string or re-deployment of the respective client application.

To clarify:

Per database we would have an alias. So if the database is moved to a different server, only its DNS alias has to be changed.

For example:

db1-alias.mydomain.com points to db1 on some random server.
db2-alias.mydomain.com points do db2 on some other random server.

So lets say db1 was moved from server1 to server2, there is no need to change the connection string. Only the DNS alias entry has to be changed, forwarding to server2 instead of server1.

Are there any known drawbacks to this?


texts: How can I verify I'm using SSL to connect to mysql?

texts:
I have configured my server to allow SSL, and have modified my client ~/.my.cnf so I use SSL:

[client]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=~/certs/ca-cert.pem

When I log in with my client and view the status, it lists a cipher on the SSL line:

mysql> \s
--------------
SSL:            Cipher in use is DHE-RSA-AES256-SHA

Without installing something like wireshark to verify that the connection is secure, can I assume that I'm connecting via SSL based on this information?


texts: Cannot see two tables with the same name

texts:
I have two non-public schemas in one database. A table named "foo" exists in both schemas and I can execute all necessary DDL & DML commands against both. However, when I execute \d, I cannot see both of them. What gives?

Repro

create schema a;
create schema b;

create table a.foo (i int);
create table b.foo (i int);

set search_path = "$user", public, a, b;

In psql

postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 a      | foo       | table | pvandivier
(1 row)

I \set ECHO_HIDDEN on to extract the underlying query run by \d and moved the relevant lines from the WHERE clause into the SELECT to examine the differences between the two table objects.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_table_is_visible(c.oid),
  n.nspname,
  c.relkind
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('a','b')
ORDER BY 1,2;

Running this query reveals...

 Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | t                   | a       | r
 b      | foo  | table | pvandivier | f                   | b       | r

The relevant difference appears to be the output of pg_table_is_visible(). But why should it return "False" when asking if b.foo is visible?

  Why is pg_table_is_visible() returning False for a table I have access to?


texts: Does the order of keys in compound Primary key make any difference?

texts:
Package

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)
and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

When I define PackageVersion (read PackageVariant) table, I mark VersionNumber (aka idPackageVariant) as Auto Increment and a part of Primary Key in the order defined above:

  PRIMARY KEY (idHolidayPackage, idPackageVariant)

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  **PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,**

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

However, due to this bug in mySql, I need to have AutoIncrement Key as the first part of the Primary Key in a compound key.

So, does the order of keys in compound Primary key make any difference ?

Just for reference, the above design comes from here.


texts: Is unique index better than unique constraint when an index with an operator class is required

texts:
Postgres docs say:

  The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Based on this, if I want an expression index on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a lower case index?

As @Colin'tHart pointed out, these 2 cases aren't the same. I should have posted this question without the use of lower() expression. In that case, my understanding is that a CREATE UNIQUE INDEX would be better than a unique constraint and a simple index.

Based on this, if I want an index with an operator class (e.g. text_pattern_ops) on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a different operator class?

Case 1:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (name text_pattern_ops);

Case 2:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);


texts: Could I control which protocol will ODBC uses to connect MSSQL on "integrated windows authentication"

texts:
I have a Windows machine that should connect to an MSSQL instance with an ODBC driver using "windows integrated authentication".
Now according to the specifications of the login message the server is using SSPI for that. And if I understood the sspi documentation correctly, then SSPI could be use by Kerberos, NTLM or some other authentication protocol.
Now let's say that I want to enforce ODBC to user Kerberos, is it possible to do that by configuring either one of the following components?

SQL Server
Active Directory
ODBC driver

(Hope that my question makes sense? I am really new to the world of Windows authentication and protocols and really confused by all the docs..)


texts:
I have two tables (simplified).

table1:
 - id (serial)

table2:
 - table1_id (integer)
 - value (text)
 - type (integer)

I want to join these two tables, but depending on the type, I want to join the "value" of table2 into different columns. Result should look like this:

id | table2value0 | table2value1 | table2othervalues

table2value0 is the value from table2 where type is 0.
table2value1 is the value from table2 where type is 1.
table2othervalues are the remaining values where type is something else, as an array.

If there are more than one value for table2value0 and table2value1, I only want the first. Order doesn't matter, so the order it was inserted.

If some entries in table1 do not have matches in table2, they should appear in the output as NULL.


texts: Does the EXIST clause only return distinct rows by its nature?

texts:
I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database.

The first query uses an EXISTS clause with a subquery:

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
       ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%')

The second query only uses JOINs:

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID

The first query returns 198 rows, while the second query returns 199.

I then ran both queries against each other with an EXCEPT clause to see which row was disappearing:

SELECT  p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID

EXCEPT

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
       ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%')

Surprisingly no rows were returned. But then I added a DISTINCT clause to my query with JOINs, and then both queries were finally returning the same result sets.

Am I correct in assuming that to have logically equivalent queries of this nature, I would have to add a distinct clause to my query that only uses JOINs?

What is the benefit of using subqueries like this? To me, JOINs make much more sense, and are easier to understand as to what is going on.


texts: Register dll to use in SSIS Script Component

texts:
I've been asked to register an existing dll file so that it can be referenced in an SSIS script component.

I have a dim and distant memory of doing this using gacutil.exe so that was my initial go to.

However, gacutil.exe is not present on the integration server, presumably because the full version Visual Studio is not installed there, only the Data Tools shell. I have also tried to copy the file into C:/windows/assembly as I read this would work, but nothing happens when I drop the file in it just cancels out.

How can I do this without gacutil.exe? Is there a way or will have to install full VS SDK to do it.


texts: MySQL Replication - Introduce new Slave to replication

texts:
I've recently taken on a sys admin position managing a group of 20 (or so) servers.  One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL replication farm.

Basically the replication is set up like so:

MS -> SL1 -> SL2 (backup)
|                     SL3 (reporting)
SB2                   SL4 (loadbalanced web slave)
                      SL5 (loadbalanced web slave)
                      SL6 (loadbalanced web slave)
                      SL7 (loadbalanced web slave)

But basically its a Master with two read slaves, (one purely for backup), one for the main read slave (master in waiting, if you will), with 6 slaves behind it, used for an additional backup, 4 load balanced web read slaves and a server used for reporting.

I've done much reading online, and assumed adding a new slave to the environment (from SL1), would be to:

Log onto SL2:

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
(copy mysql directory to new server)
upon completion, UNLOCK TABLES;, and then START SLAVE; (all fine up until this point, SL2 comes back online and catches up)
made sure db structure is correct on new server and master.info points at the right place (pointing correctly at SL1)
Start MySQL on the new server, checked that Slave SQL and I/O is running (yes, this is fine)

However, after a period, I get duplicate key insert errors from the binary log:

110122 17:01:25 [ERROR] Slave SQL: Error 'Duplicate entry '2011-01-22 17:00:01' for key 'PRIMARY'' on query. Default database: 'thelm_soft'. Query: 'INSERT INTO  thm_member_views  (   member_view_ts, logged_in_members, non_members ) VALUES ( now(), '27037', '132834' )', Error_code: 1062
110122 17:01:25 [Warning] Slave: Duplicate entry '2011-01-22 17:00:01' for key 'PRIMARY' Error_code: 1062

Is this an issue with the fact now() is being in a query, which common sense tells me would cause issues if the inserts on that table were frequent enough??!?!  Or have I got the process with replication wrong, i.e starting at an incorrect log position?


texts: Viewing the SQL of a view from a backup set

texts: I have a full backup of my database from 4 days ago. It is a standard SQL 2008 .bak file. What I'd like to know is if its possible to view the SQL to a View that is in the database I backed up without restoring the entire database?


texts:
MSSQL 2005 sp3 32 bit running on Windows 2003

I have a sql server user MyUser in a database called MyDatabase.  MyUser is able to access the MyDatabase database via SSMS.  He is also dbo for that database.

When using the EMS Database Comparer 2011 to compare this database, I am getting the following error.

  The SELECT permission was denied on the object 'syscolumns', database
  'mssqlsystemresource', schema 'sys'

MyUser is not in the db_denydatareader or db_denydatawriter database roles

Is this normal, if so, what can be done to resolve it.

I am able to run the comparison as sysadmin.

I ran a trace using profiler and replayed using the MyUser user

Here is the result

The Query that causes the error

SELECT
    p.action
,   p.protecttype
,   p.columns
,   p.grantor
,   MAX(c.colid) AS colnum
,   u.name
FROM
    sysprotects p
    INNER JOIN
        sysusers u
        ON u.uid = p.uid
    LEFT OUTER JOIN
        syscolumns c
        ON c.id = p.id
WHERE
    p.id = 1497772393
GROUP BY
    p.id
,   p.action
,   p.protecttype
,   p.columns
,   p.grantor
,   u.name

The error sent back by profiler

  [Microsoft][SQL Native Client][SQL Server]The SELECT permission was
  denied on the object 'syscolumns', database 'mssqlsystemresource',
  schema 'sys'. (State 42000) (Code 229)

Some additional Clues

Permissions error if the query is run from SSMS on the same machine as EMS
No error when if the query is run from SSMS on the MSSQL server itself.


texts: How to properly debug and inspect SQL Queries in PHP File?

texts:
My website AdminPanel has a PHP file which takes almost 30-40 seconds in loading.  I doubt the queries which are making the mess, but may be it is the code but the code structure seems perfect. I want to find and check the all the queries execution and running time i.e how much time it takes to execute? But i can't.

I tried the slow query logs option but it is not logging anything. And from the general log i just see queries but no execution time and details.

Thanks


texts: What's the difference between Object.bsonsize and avgObjSize returned by db.collection.stats?

texts:
In my collection, my documents seem to be relatively similar. I need to know how much storage I'll need in my production server to store my documents, that's why I queried :

db.collectionName.stats()

My field avgObjSize was around 6442 bytes.
I chose a random document in my collection, I typed :

Object.bsonsize(db.collectionName.find({"_id" :         ObjectId("5508497c51a990da07b07106")}))

And I obtained 810 bytes.

Why do I have this huge difference ?

I also noticed that more I store documents, more avgObjSize decreases... Is that normal ? (I checked, my documents are not empty.)

It's a development server, so I'm on Red Hat Enterprise Linux 5.8 and it's a 32bit version.


texts:
Goal:
*Same database to have 2 similiar table named test1 an test2.
*Table test2 is only used for query (read only) and table test1 is only used for command (delete, create and update).
Background:
*Test2 is a replication of Test1 (any changes in test1 should update in table test2 in realtime).
Problem:
Is it possibly to make a replication of a table in the same database in SQL server?
Thank you!


texts:
I need a Char Encoding Trick to Strip Hebrew Accent Marks.

Sample Before

  בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ

Sample After

  בראשית ברא אלהים את השמים ואת הארץ


texts: Best practice around sys.dm_db_missing_index_group_stats in SQL Server

texts: I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which of these indexes are important and should be created and should I just create them all or are there any other secrets? Anything I specifically should look for in the avg_user_impact or avg_total_user_cost columns?


texts: How to stop MySQL from replicating CREATE TABLE... on Slave for ignored databases

texts:
Mine is a simple MySQL Master-Slave setup, with --binlog-do-db option as discussed in this article.

As per the documentation, logs are written for DDL statements for all databases irrespective of '--binlog-do-db'.

Is there any way to stop this??


texts: SQL Server replication for off site copy

texts:
We have our Primary SQL Servers in-house (15 primary servers at the moment (500 databases approx overall), most of the servers have hex core processors).

These are mirrored to other backup servers in a near-by building (via a dedicated 0ms fibre link).

What we'd like to do is maintain a live copy of our data in an off-site data centre miles away (as part of a much bigger DR project - i.e. if there is a comms issue in our area).

Currently we use SQL Server 2008 R2 - Standard edition.

Obviously using the "Standard Edition" of SQL Server 2008 R2 limits us to Synchronous mirroring as asynchronous is an "Enterprise Edition" feature.

We've done testing with offsite synchronous mirroring but the latency just made it a no-go.

I'd love to upgrade to SQL Server 2012 Enterprise and implement AlwaysOn availability groups, but the company is not willing to spend the £100,000+ to upgrade the SQL licensing (2012's per core licencing + our hex-core processors = epic fail) - they're not even willing to spend the money to upgrade to 2008 Enterprise so that’s asynchronous mirroring out of the window too.

So, with my hands tied by these financial constraints i'm stuck with 2008 R2 Standard edition.

The only options left open to me are log shipping and replication (correct me if I’ve missed anything) - Log Shipping is crude - but when managed properly can be feasible - so we've got that on the back burner for now.

My Question(s):

Does SQL Server 2008 R2 Standard have all the replication features that we'd need to do off-site replication?
Would the replication of in-house servers to an off-site data centre (20-30ms latency) be feasible without impacting performance on the primary server in any way (would we be ok setting the Replication Distributor on the "Principle" server)?
Given that replication is not an instance level replication solution - would the management of this be more than 1 DBA could handle? (given that i'm already managing 15 Principle servers & their Mirror counterparts - admittedly with lots of automation)
Would replication be possible from our primary without impacting the existing mirroring configuration - it's my understanding that best practice is to mirror the Subscriber as opposed to the Publisher - is this correct?
Is there anything else Replication Virgins forget that you need to bring to my attention?


texts: PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

texts:
We are using a software that needs to request the max primary key column value to add a new row into the table that was requested.

I know that there are some alternatives like:

Sequences, and
GUIDs

in PostgreSQL, but is there something similiar like:

Identity-type in SQL Server or
any auto-increment columns of any other DB (MySQL)

in PostgreSQL, is there a way to achieve this with a (stored) procedure or a trigger and how?


texts: How do I set up Azure Sql Server to have multiple instances?

texts:
We are setting up a new SAAS application on Azure and while we have used Azure before, not at this level of multiple apps, etc.

We plan to have both a web app and a cloud app in both a US and EU data center. They need to hit a common database because requests will go to the closest data center via traffic manager and a lot of the data works off of the customer table. And a customer (company) can have users in both the U.S. and E.U.

Is there a way to set up Azure Sql Server so it has instances in both data centers, and Azure keeps them synchronized? If I understand sharding right, that is not what we need as someone hitting either data center could be requesting any of the data in the DB.

thanks - dave


texts: Searching Oracle tables for particular values

texts:
I want to search all tables, all rows in a particular schema for a particular value. This is so I try to understand where a value is getting persisted.

How do I do this in Oracle?


texts: Confused over encoding/locale in postgresql

texts:
I am a little confused over the difference between encoding and locale as it pertains to the postgresql database.

When initializing the database, you can specify both an encoding and a locale.  Correct me if I am wrong but I assume the encoding defines what the database is actually stored as on the computer.

So if I specify UTF8, all the characters in the UTF8 character set would be valid characters.  If I specified WIN1252, all those characters would be valid characters.  What I dont get is where the locale comes into play.  If I specify my encoding as UTF8 and then specify my locale as English_United States.1252, what does that exactly mean?

I think the WIN1252 character set is a subset of UTF8 so is the locale just specifying the subset of characters to be used from the UTF8 character set?

From what I have read is that UTF8 can be used with ANY locale so what is the point in specifying different encodings if UTF8 is so ubiquitous and the locale is really the one specifying the specific character set to be used?

Also, on Linux, the locale can be specified like so: en_us.utf8.  So the database encoding is specified in the locale?  If the encoding is specified in the locale, why even have a -encoding flag when initializing the database?


texts: Postgres Incremental Backup (Continuous Archiving WAL)

texts:
I need to take incremental backup of Postgres DB.
I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want.

As per Continuous Archiving, I am doing below steps:

I configured postgresql.conf, and added wal_level, archive_mode and archive_command
Restart server
Added some data
pg_start_backup('label');
Take backup of data dir
pg_stop_backup();
Added more data
Take backup of wal dir and pg_xlog dir
Replace backup data (from step 5) in data dir
Start server
Copy pg_xlog (from step 8) in current pg_xlog dir
Added recovery.conf and added restore_command
Restart DB

Now I want data which I added in step 7. (I had taken backup of WAL file and pg_xlog at that time). So I consider this data as a backup of a particuar day. And I want to restore my database with this date's data.

Now If I replace those files ie. WAL and pg_xlog and recreate recovery.conf and restart db, I do not get the data in step 7, still old one.

Please let me know if I am missing anything.


texts: Unable to get actual execution plan for SQL Server 2014 SP3

texts:
I was reading this blog post https://techcommunity.microsoft.com/t5/sql-server/using-xevents-to-capture-an-actual-execution-plan/ba-p/392136

and based on same created extended event

CREATE EVENT SESSION [PerfStats_Node] ON SERVER

ADD EVENT sqlserver.query_thread_profile(

ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text))

ADD TARGET package0.event_file(SET filename=N'C:\Temp\PerfStats_Node.xel',max_file_size=(50),max_rollover_files=(2))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

However when reviewing the target file i see the plan captured seems estimated even though some actual number shows up in target events but when i pass plan handle in sys.dm_exec_query_plan, the plan which comes up seems to have all estimates

Blog clearly says it will open actual plan, then why i am not able to see one?

I tried using SSMS 17.9 as well but still no luck.


texts: Does anyone have a good recommendation for a book which teaches sharding?

texts:
Does anyone have a good recommendation for a book which teaches db sharding (from scratch at best)

I have read like 40 different websites talking about sharding.

I'm not saying online sites/blogs are lousy, they are good titbits. However, I need the main meal not just a it of useful info here and there. Basically, I have an idea (I guess) of how to implement sharding but, since it's a terribly complicated concept, I believe there is much, much, much more to it that can be studied on.


texts:
I've got 3 dedicated non-virtual servers (srv1-3). On one of them (srv1) i've got MongoDB in production, that already have got really big amount of data. At the present time there is no sharding or replica processes, it's just a standalone server.

So, I want this server (srv1) to be as a shrad and config server at the same time. The second server (srv2) is going to be a second shard. And the third one (srv3) is going to be a replica server.

I guess, that I have to run the mongod process on srv1 in two instances: one is the regular in --fork, and the second is --configsrv localhost:27019 (and previously create a separate directory for the config server, like /data/confDB).

After that, I should add sh.addShard("srv1:27017") and sh.addShard("srv2:27017") in mongo console and then just sh.enableSharding(myDBname).

Is that correct? Or it's necessary to create an additional server, that will be the config server?


texts: How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

texts:
I want to set a Unicode string variable to particular character based on its Unicode code point.

I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of SQL_Latin1_General_CP1_CI_AS.

According to Microsoft's NCHAR documentation, the NCHAR function takes an integer as follows:

  integer_expression

  When the collation of the database does not contain the supplementary
  character (SC) flag, this is a positive whole number from 0 through
  65535 (0 through 0xFFFF). If a value outside this range is specified,
  NULL is returned. For more information about supplementary characters,
  see Collation and Unicode Support.

  When the collation of the database supports the supplementary
  character (SC) flag, this is a positive whole number from 0 through
  1114111 (0 through 0x10FFFF). If a value outside this range is
  specified, NULL is returned.

So this code:

SELECT NCHAR(128512);

Returns NULL in this database.

I would like it to return the same as this:

SELECT N'';

How can I set a Unicode string variable (e.g. nvarchar) to an emoji using code (without using the actual emoji character) in a database where the collation "does not contain the supplementary character (SC) flag"?

Full list of emoji Unicode code points

(Ultimately I want any character to work. I just chose emoji for ease of reference.)

(Although the server is SQL Server 2008 R2, I'm also curious about any solutions for later versions.)

Assuming that there is no way, could I reference an inline user-defined function in another database which had an appropriate collation?

How do I find a collation which has the "supplementary character" flag?

This returns no records on our server:

SELECT * FROM sys.fn_helpcollations()
WHERE name LIKE 'SQL%[_]SC';

It seems like SQL Server 2012 introduced Latin1_General_100_CI_AS_SC which would work. Can you install collations on older instances?

Collation References:

Answer to What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
Microsoft's Supplementary Characters Collation Information
Microsoft's SQL Server 2008 R2 Collation List

Is there an explanation for why, regardless of collation, SQL Server can understand and deal with the extended characters except from the perspective of NCHAR?


texts: How can you split multiple delimited strings of varying sizes into rows?

texts:
I have a situation where for each ID in my table there are multiple fields that each contain a comma-delimited string. The strings don't necessarily have the same number of elements as one another. The format of the data comes from another team and I can't change it.
How can I split these strings into rows with SQL Server 2016? I'd rather not add a function to the database if it can be avoided.
Here's a sample of what I'm starting with and what I'm looking to achieve:
DECLARE @SourceTable TABLE (ID INT, String1 VARCHAR(100),
  String2 VARCHAR(100), String3 VARCHAR(100))

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2')

--Source
SELECT *
FROM @SourceTable

Desired Output:
ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3
1   4                           b4
1   5                           b5
1   6                           b6
2   1           d1              e1              f1
2   2           d2                              f2

EDIT: Corrected output based on Aaron's comment.


texts:
My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's.

The system is relatively young and our biggest tables are just over a million rows, but we're taking a look at our indexing and want to be able to scale quickly as it may be needed in the near future.

So, my first inclination was to move the clustered index to the created field which is a bigint representation of a DateTime. However, the only way I can make the CX unique would be to include the GUID column in this CX but order by created first.

Would this make the clustering key too wide and would it boost performance for writes? Reads are important too, but writes are probably a bigger concern at this point.


texts: Reset the sequence of a serial PK with dynamic SQL

texts:
How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this?

My goal is to do this within a DO/BEGIN block for performance reasons plus it'll help me understand plpgsql by doing something useful so I'm not going to mix this in to PHP unless I really have to.

DO $$
 DECLARE PKEY VARCHAR;
BEGIN

SELECT pg_attribute.attname INTO PKEY
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = 'parts1'::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;

--SELECT setval('parts1_id_seq', (SELECT MAX(pkey) + 1 FROM parts));
SELECT setval('parts1_' || PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

  END;
$$ LANGUAGE plpgsql;


texts: Fencing a failed application node to isolate it from SQL Server

texts:
I have a multi-node server application that is backed by SQL Server 2012, often in a clustered or AlwaysOn AG/FCI HA configuration. My application monitors the health of the application nodes (not the database node(s)) and implements a kind of fencing if an application node is non-responsive or otherwise determined to be unhealthy. I am trying to figure out how to isolate the failed application node from accessing SQL Server since any writes from an unhealthy node could corrupt data.

The approach I'm considering is configuring all application nodes to connect to the database with distinct users. If a node was deemed unhealthy, the write privileges for its database user would be revoked by one of the healthy nodes. This seems pretty clean except for the fact that individual users have to be maintained for each node. I briefly considered having one of the healthy nodes kill all database connections from the unhealthy node, but I think that approach is less desirable because it requires all nodes to have the ALTER_ANY_CONNECTION privilege and it would not play nicely with the existing database connection retry logic. Are there any other approaches or best practices that I should consider?


texts: Abstract classes in SQL Server. Are they even possible?

texts:
I have a very specific question that I am surprised no one has yet asked. It involves a concrete class model and SQL Server 2016 DDL. The main point is:
In my model, some classes inherit from a base class, let's say "BaseInfo".

This class is abstract. In SQL terms it means I do not (or do I?) want to create a separate table with rows (eg. CREATE TABLE "BaseInfo"..... I do however want some of my tables (actually quite a lot of them) to have the same columns (plus some of their own). In OOP terms I want some of my classes to inherit from that table definition. Is there a way to define this in DDL, or do I have to implement this manually?

Notes:

I have read all the articles that are to be found on TPH, TPT, TPC. They are too simplistic, they all feature the same Person-Student-Educator Model and do not match my case. What I probably want to do is TPC, but I want changes made to BaseInfo table to automatically apply to all child classes.
I will not be querying BaseInfo. I'd rather do a (rare) complex query involving some (or all) of the children.
Changes made to BaseInfo DDL (ALTER TABLE BaseInfo...) have to be reflected to the children (preferably automatically).
PostgreSQL shines here with the INHERITS statement. Is there any equivalent in SQL Server (I have done research and found nothing yet)?
In case I get no answer or have some other breakthrough, I will go the manual approach. Save a code snippet with the BaseInfo DDL and add it to the CREATE TABLE statement of each child table.
The answer must respect the conceptual model definition!


texts: SQL Server - Extremely low avg_page_space_used_in_percent for heap table

texts:
While analyzing a SQL Server 2012 database bloat issue, I found that fragmentation might be a major contributor to how much space is being taken up by certain tables, so after some Googling, I'm looking into the sys.dm_db_index_physical_stats function to try to understand what's going on.  Here's my query:

SELECT
index_id,
index_type_desc,
alloc_unit_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
record_count,
page_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL , 'SAMPLED')
WHERE OBJECT_ID = 516196889
ORDER BY avg_fragmentation_in_percent DESC;

And my results:

There are 4 non-clustered indexes on the table, so I'm guessing those are what the top 4 result rows represent.  I haven't figured out why the IN_ROW_DATA portion of the heap has such low page usage (6%).  If I'm reading the results correctly, there are more pages for this part than there are rows, but max record size is less than 8kb, so I'm trying to understand how that's even possible.  How do I find out what's causing this low page usage, and what are some things I can do to fix it?

Any help greatly appreciated!


texts: I recently updated mysql from 5.1 to 5.6 however it's causing too many issues with the websites hosted on my machine. Does anyone know how to remove mysql 5.6 and reinstall version 5.1.


texts:
When I execute my query I have always the same problem :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|Total
------------------------------------------------------------
  2016       |    7        |  0     | 0.40   |  0      0.40
  2016       |    7        |  0.50  | 0      |  0      0.50
  2016       |    7        |  0     | 0      |  0.30   0.30

How I can combine to have a single line :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|
-------------------------------------------------------
  2016       |    7        |  0.50  | 0.40   |  0.30

This is my query :

SELECT
   MONTH(created_at) as month_created,
   YEAR(created_at) as year_created,
   1 - SUM(price)/SUM(order_total) as Total,
   CASE loc_id WHEN 1 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_1',
   CASE loc_id WHEN 2 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_2',
   CASE loc_id WHEN 3 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_3'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
  GROUP BY year_created, month_created
  ORDER BY year_created asc, month_created desc


texts: FDW-based Sharding with PostgreSQL 10: Multiple coordinator nodes?

texts:
Context

I'm currently looking for and comparing different options for sharding data from a Postgres database into multiple ones.

The end result may be something like having instances of a web application in different regions (one in Europe, one in North America, etc.) and, with the aim of avoiding a Europe instance talk to a Postgres DB in North America for example, likely DB instances located similarly.

Options like the Citus DB extension or doing sharding through the use of foreign data wrappers seem promising. My question is about the latter option.

These slides (specifically, Slide 13) describe an architecture using foreign data wrappers where clients may contact any one Postgres DB instance and each one of these instances may act as a coordinator node as well as a data node, where data is sharded between all those nodes. Yet, the slides mark this as being something that will come "in the future". The examples of the presentation seem to be based on Postgres 9.6.

Question

I have not been able to find out whether the scenario described, with multiple coordinator nodes, is possible in Postgres 10 or if that is something that may be possible only in Postgres 11 or later.

So my question is, is it possible with Postgres 10? If features are currently missing for achieving this, what are they?

My worry about implementing sharding with foreign data wrappers if only one coordinator node can be present is that it would put too much pressure on that one node and that it would not be optimally located for all instances of the web application that would make use of it.


texts: Howto specify my own id value when upload a stream into a gridfs file, using the GridFSBucket API?

texts:
I'm using TypeScript, MongoDb 3.6 and the mongodb 3.0 driver. Here is how I do it:

this.gfs = new GridFSBucket(this.media_db, {bucketName: 'original'});
const reader = fs.createReadStream(path);
const writer = this.gfs.openUploadStream(result.sha1);
reader.pipe(writer);
console.log("STORE ORIGINAL MongoDb/GridFs")
const waiter = new Promise((resolve, reject) => {
    reader.on('end', ()=>resolve(true));
    reader.on('error', reject);
});
await waiter;

There is a big problem with this setup. Here is my former question about creating indexes on gridfs collections:

GridFS: why can't I shard chunks with a hashed key?

I got an answer that explains how these should be indexed: the file id should have a uniform distribution (e.g. a hashed key). The default id value is not like that, so I must provide my own file ids if I want them to be distributed over multiple nodes from the beginning. The chunk ids are a different story - they are generated objectid values, and they should be left at peace. Using generated objectid values for chunks allows MongoDb to perform a range query on the chunks, resulting in a much more efficient query plan (e.g. when a data stream needs to be reconstructed for a file.)

The question is this: using the official API and the GridFSBucket class, how can I specify my own file id? It is how it should be done, but I don't see any way to do it. Here is the signature of that method:

openUploadStream(filename: string, options?: GridFSBucketOpenUploadStreamOptions): GridFSBucketWriteStream;

It only has a filename parameter and an options parameter. Options is like this:

export interface GridFSBucketOpenUploadStreamOptions {
    chunkSizeBytes?: number,
    metadata?: Object,
    contentType?: string,
    aliases?: Array<string>
}

If this can't be done, then there is no way of avoiding hot shards when mass ulpoading files to gridfs. (Then I probably need to submit an issue.)


texts: AD User does not inherit the permission from AD Group - for a LOGIN

texts:
I am having a very tough time figuring out the permissions in my database. My users gain access to the database through reports on SharePoint (via Impersonated authentication configured through Kerberos). Users, who are impersonated, are all added to AD Groups. And in my database, I am granting permissions to the AD GROUPS (as logins) and NOT to the individual users. I have 1000 users but 10 AD Groups. Each user is part of an AD Group.
The users currently cannot have access to the database – they are only able to see the database if I was to add them individually as logins (obviously not an option). If I add their AD Group, it doesn’t seem like it works. Again, they are authenticated through Kerberos as impersonated accounts. Here is a map of what I’m saying:

IF the AD GROUP has permission, why doesn't the user within has permission??
I reviewed this question, but I'm not sure where is the equivalent for SharePoint Integrated mode.

EDIT:
In case it makes a difference, AD Groups are assigned as "Security Group - Universal"


texts:
We are receiving emails from this SSIS package and I need to edit the text that is in The EMAIL and not the data exports themselves.  I have checked the stored procedures that produce the report code, I have checked the job but that just calls the SSIS package and I have looked everywhere I can think to look but I don't see where the text in the email is coming from.

Anyone have a similar setup where they set up an SSIS package and had the results as attachments to an email?  I'm not really sure where the code would be to change it.  The Email currently says something in the body like: "Here's the report for the  previous week".

Any help would be much appreciated as I just can't find where that is pulling from so I can change it.


texts:
I am trying to determine the duration of backup restore. I executed a few restore command to restore some backups located on network share drive. Here is command and summary,

RESTORE DATABASE [test]
FROM DISK = '\\network\test.bak'
WITH
    MOVE 'test_data' TO 'D:\test_data.mdf',
    MOVE 'test_log' TO 'E:\test_log.ldf',
    CHECKSUM, STATS;

Backup A
1st restore take about 5 min. Ok, after restore complete, I delete the database
2nd restore take about 1.8 min. Hm.. Let's delete the database and try again.
3rd restore take about 1.5 min. Hm..

Backup B (almost same size as backup A, at the same network location)
1st restore take about 1.8 min. I deleted it and try again
2nd restore take about 1.5 min. Ok..

The service account is enabled for instant file initialization.

My question is why the restore duration varied on different restore attempt on the same backup file to the same machine? Is it purely on network throughput (perhaps someone was doing something on the network share and stuff), or something else like cache or something in SQL internal?


texts: Best solution for cross-datacenter MySQL master-slave replication

texts:
We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service.

System Architecture:

Preconditions:

The HPC cluster has a lot of write operations
Website and API both read the data most of the time and write rarely
Ping from AWS to our cluster is ~35ms
In case of our local data-center failing, the HPC should be replicated on AWS, turning the MySQL Slave into the new Master

Question:

What is the best solution to replicate the MySQL database in such a setup?


texts: Replay trace file and filter on error>0?

texts:
I have captured a trace file using the replay template. It includes all columns available, of course including ERROR. When I replay this trace file, the "trace configuration" window pops up, I declare the target server, save the result to a new trace file and run the trace.
When I then load the new trace file, most of the columns are gone, including the error column. I was hoping to filter on ERROR > 0 in the resulted trace file of the replay action to find queries having problems on the replayed-against server.
Why are the columns not included anymore - and why does the new trace file contain not all events anymore but instead includes these "Replay Result Row Events" (e.g. Name = blabla...) ?

Edit:
Meanwhile I followed a suggestion to look at the trace file using a sql query:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)

to see ALL columns. Unfortunately here the columns are displayed but contain all a NULL value where I had expected to see values e.g. for ERROR.


texts: How do I generate a Loader_Generate_Script for all states and regions?

texts: I'm using PostGIS and I'm wondering how do I generate a loader script for all states that will load all of the census dataset.


texts: How to search multiple values using LIKE

texts:
I'm using MySQL and I have a table with these fields :

id
name
kode_fakultas
year

I want to create a form for searching based on user input. Example, if the user search is based on name, but the fields kode_fakultas and year are not filled with a value, then the form should retrieve the results based on name. And if the user wants to search based on kode_fakultas, but name and year are not filled  in, then the form would retrieve results based on kode_fakultas only.

My query seems wrong, because I don't get the results that I want.

select * from teacher
    where name like '%".$name."%' or year = '".$year."' or kode_fakultas = '".$fak."


texts: How do I grant PostgreSQL superuser access to vacuum tables?

texts:
My logs are constantly filling up with these messages for hundreds of tables:

WARNING:  skipping "some_table_name" --- only table or database owner can analyze it

How do I perform any the following and/or resolve this?

Determine in which database(s) these tables reside? Would be nice if the warning included that information.
Grant the PostgreSQL superuser access to vacuum these tables
Disable these warnings in the log file

Side note: none of the log file rotation, truncation, size/date limits settings are working either but I'll save that for a separate post.


texts:
I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available.

I get this message:

psql
-bash: psql: command not found

Do I have to install something more? Or how can I configure PostgreSQL so I can use it on my computer?


texts: Configure phpmyadmin to run on different hosts

texts:
Just created locally a new host pointing to a new Virtual Host.

And added through command line a new user at the new host.

So like 'user1'@'localhost' I now have 'user2'@'local2' .

The problem is I don't know how to access phpmyadmin for the new user.

Edit :  here is the phpmyadmin [config file] http://pastebin.com/XbWnHj1G


texts:
I have a MySQL RDS instance that's got 100+ queries all stuck waiting for a table metadata lock.

The good news

I have permission to kill all of the sessions, and have 0 concern for data loss as this database is completely reloaded as needed.

The bad news

It's a MySQL instance running as a hosted RDS instance in AWS AND while I have access to the "root" MySQL account, I don't have access to the AWS console that this RDS instance is hosted in.

What I've tried

When this has happened previously, I look through the connections and run CALL mysql.rds_kill(_each_and_every_process_id_one_at_a_time). However, this time, when I try run one of these kill statements, it simply hangs. (I'm doing this through MySQL workbench)

What options do I have? Is there someway to "escalate" my mysql.rds_kill call so that it's handled right away? Do I try executing this outside of workbench somehow?


texts: Design a db table where rows may have NULL in multiple columns

texts:
I am trying to design a database for tour packages. Tour packages contains the detailed itinerary.

Tour package duration may be between 3 and 30 days. I need to add the description of each day.
If  I make 30 columns like day_1, day_2, ... day_30 .
While creating the package for 5 days the rest of 25 Columns will be empty/NULL.

There will be constraints that all packages should have less than 30 days.

How can I improve this design ?


texts: Anyway to view original query on top of views using postgres

texts:
For example, I have a simple query:
SELECT * FROM information_schema.columns where table_schema = 'public';

Which turn out very complex, information_schema.columns is a complex view.
Right now, to understand the query, I need to read all the definitions of all the views involved.
Is there any way to "expand" query and make it easier to read side by side with query plan output?


texts:
I have one question that confusing me.

By logical query processing, the SELECT clause comes before the ORDER BY clause, and in theory, every step in a query creates a virtual table. So, how is it even possible to order result by a column that is not listed in the SELECT clause, when the SELECT clause generates a virtual table with the selected columns?

In case your query has a GROUP BY clause, then the behavior become normal, but in the absence of a GROUP BY clause, it seems a little tricky. Does anybody have an explanation?


texts: Why running queries one by one yields correct results, but running lots of them in one chunk doesn't?

texts:
Let's say I create a simple table and a stored function to populate it:

CREATE TABLE t
(
    id INT PRIMARY KEY,
    val VARCHAR(255)
)//

CREATE FUNCTION populateIfNotAlreadyIn (idArg INT, valArg VARCHAR(255))
RETURNS BOOLEAN NOT DETERMINISTIC
BEGIN
    DECLARE alreadyIn INT;
    SELECT COUNT(id) INTO alreadyIn FROM t WHERE id = idArg;
    IF alreadyIn <> 0 THEN
        RETURN FALSE;
    ELSE
        INSERT INTO t (id, val) VALUES (idArg, valArg);
        RETURN TRUE;
    END IF;
END//

Of course, the intended behaviour is that the function returns 0 if a row with the given ID already exists in the table, and 1 if it doesn't (in the latter case, the function also inserts such a row).

So now testing comes.

If I run testing queries in phpmyadmin one by one like that:

SELECT populateIfNotAlreadyIn(1, 'val1');

//Go//

SELECT populateIfNotAlreadyIn(2, 'val2');

//Go//

… etc

Then apparently, the results are correct: the function returns 1 and inserts a row if and only if there is no row with such an id in the table, otherwise it returns 0.

So far, so good. But instead of running queries one by one, let's feed phpmyadmin with all of them at once. To be more specific, let's insert into phpmyadmin sth like this:

SELECT populateIfNotAlreadyIn(1, 'val1');
SELECT populateIfNotAlreadyIn(2, 'val2');
SELECT populateIfNotAlreadyIn(3, 'val3');
SELECT populateIfNotAlreadyIn(4, 'val4');
SELECT populateIfNotAlreadyIn(5, 'val5');

And then click Go, to run all of them.

Result? The rows are inserted correctly into table t. But the stored function returns incorrect values. Each call except the last one returns a 0.

Why does this happen?


texts: how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER?

texts:
I could not script the following server side permissions:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]

but thanks to the question below I found a solution to that:

How to script out the grant view on login permission?

Now I have a similar problem:

I have a process that grant the following server side permission to a login:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [my_company\my_login]

that is working fine, however, I need to script all the permissions of the my_company\my_login but I cannot find a way to script this permission.

How can I do that?


texts: How to know if Instant File Initialization is enabled?

texts:
Besides creating a new database and timing it, is there a simple way to determine whether or not Instant File Initialization is enabled/functioning?

(on Windows Server 2008 Enterprise)


texts:
So I have a table, report_items with a structure as follows:
id (uuid) | date (date) | sheet (text) | report_item (text) | report_value (numeric) | stock_id (uuid) | period (text, either quarterly or yearly)

I'm trying to make a function that returns a table for every sheet where the report_items are turned into columns.
So one of the sheet types is Balance_Sheet, so I've hardcoded that in for now. Balance Sheet has about 54 distinct report_item rows for every stock_id, date where sheet = 'Balance_Sheet'.
I'd like to return a table with a structure as follows, so that instead of having 54 rows, each row is joined to form a super table having all those rows as columns, so that for each date, stock_id pair there would only be 1 row.
date | sheet | stock_id | report_item_1 (numeric where value is report_value where report_item=report_item_1 and sheet=sheet and date=date) | report_item_2 | ... report_item_n | period

Where report_item_1 ... report_item_n are named based on what the report_item value (text) with the value being what the report_value is for the row with that report_item.
create or replace function report_table()
RETURNS integer AS $$
declare
   index_items integer:= 0;
    sheet_type varchar:= 'Balance_Sheet';
    period text;
    items text[];
    item text;
    query table; -- not sure what the appropriate item type would be for a table

begin
    -- This gets all the different report_items
    items := array(select report_item from report_items ri2 where sheet = 'Balance_Sheet' group by report_item) ;

    FOREACH item IN ARRAY items LOOP
  -- Loop is working, but not sure what to put in the loop
      index_items := index_items + 1;
      if index_item = 1 then
         select date, stock_id, report_value as item, sheet from report_items ri1 where report_item = item and sheet=sheet and period=period;
      else
         -- Join current report_item column onto the previous table
         select report_value as item from report_items as ("ri" + index_item) where report_item=item and sheet=sheet and period='quarterly' join  ("ri" + index_item-1) on  ("ri" + index_item).stock_id= ("ri" + index_item - 1).stock_id and  ("ri" + index_item).date =  ("ri" + index_item-1).date; --pseudocode: This won't work, but trying to show the general idea of what I'm trying to do
      end if
    END LOOP ;
    return ("ri" + index_item); -- Should return the supertable

end $$ LANGUAGE plpgsql;


texts: SQL Server 2014 Triggers and where are they created

texts:
I am on SQL Server 2014 Standard edition. I am developing a Supply Chain DW and I am currently in the process of building a Landing schema which will be used to Land data coming out of the source system and then move that data into a Staging schema to begin the ETL process. I am truncating the landing tables after I have moved that days data into Staging. I am using Hashbytes in a Trigger which will add a hash value to each record for comparison while being moved upstream.

CREATE TRIGGER trgr_hashval_mara ON land.mara
FOR UPDATE, INSERT AS
UPDATE land.mara
SET HashVal = (SELECT hashbytes('md5', (SELECT material, MaterialDesc FOR xml raw)));

I can create these all day long, but I am unable to Drop them using conventional syntax as below. Executing the DROP statement produces an error.

 DROP TRIGGER trgr_hashval_mara;

"Can't find trigger or you don't have permission to drop trigger."

However the following statement comes back with a list of Triggers I have created.

select * from ibscm01.sys.triggers

I also can't seem to find them in the DB Tree. I see the Database Triggers child, but there are no Items/triggers shown. I am using Toad for SQL server 6.7 which should be the current release. also using Windows Authentication with AD and I have dbo privileges.

Thanks,
Pat


texts: Choosing shard key and friendly URL Ids for my MongoDB

texts:
I have decided to use MongoDB as my Database for a web application. However, I have some difficulties to get started and I hope that you can help me out with a few questions.

I am developing my application in ASP.NET and with MongoDB as the back-end.  I intend to start with a single server + 1 replication but wanted to built it right so I won't have problem sharding the database in the future if I have to.

One of my biggest problems is choosing the right shard key and friendly URLs for my website.

I have a folders collection and files as embedded collection inside the folders collection.  Each user can create any number of folders and add files to it. Each folder belongs to one user. I wanted to know what is the best shard key for this type of collection?
 Many queries will query by the user, getting the folder and its items by querying the folders collection by its unique id. I will also use the id in the URL to get the folder and its filers: ex. mywebsite.com/folder/[the_id_of_the_folder]

I will also will use paging in my application, so I need to query the data (also in a sharded environment) and get for example: the last 10 records, page 2 with 10 records - all ordered by the last time the were inserted/updated

So my first question is what is the best shard key to use for a single machine, but considering that I will have to shard in the future
Does the shard key has to be the primary unique id of the document in MongoDB?
How can I generate more user friendly URLs - I prefer a numerical value instead of GUID (is there option to convert it?)

Help will be very appreciated, as I am stuck and can continue until I solve this.


texts: Using RANK function, how can I return the rank of one record vs the entire table?

texts:
I have the following info in my database:

+-----------------------+-------------+
| USERNAME        TRUST |             |
+-----------------------+-------------+
| DeaDTerra             |         390 |
| OgNasty               |         322 |
| Blazed                |         303 |
| monbux                |         260 |
| Tomatocage            |         230 |
| Stunna                |         220 |
| philipma1957          |         216 |
| John (John K.)        |         180 |
| CanaryInTheMine       |         173 |
| Mitchell              |         165 |
+-----------------------+-------------+

I want to run a query that will return the following:

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          3    |
+----------------------+

I have tried the following query:

select top 25 username, trust, RANK() over (order by trust desc) AS Rank
from btcProfile
where profile_name='Blazed' order by trust

All I get is this:

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          1    |
+----------------------+

How do I only pull up the one record but calculate the RANK() based on all rows in the table?


texts: Point in time Restore failed - Error 3456 'Could not redo log record'

texts:
I have a database from which all tables got deleted by accident. Unfortunately we do not have a 'perfect' backup and now my problem comes in:

FULL Backup is from yesterday, 18:00
LOG Backup wasn't taken for several days. That backup was performed manually after the delete.
Both files are available to me to perform a point-in-time restore

To restore point-in-time, i restored the FULL-Backup WITH NORECOVERY. Then i tried to apply the Log-Backup. After it reaches 100% in GUI, i get the following error:

LSN-Information of FULL-Backup:

LSN-Information of LOG-Backup:

Am i right that the overlapping LSNs of FULL and LOG-Backup are the problem here? I think it is not accidential that PrevPageLSN from the error matches the FirstLSN of the FULL backup. Ideas how to get the database restored point-in-time?


texts: Generate scripts wizard hangs when loading database information

texts:
I am trying to script out a database from an instance of SQL Server 2017, so I can load it into an instance of SQL Server 2014. I right-clicked on the database, chose  Tasks -> Generate Scripts, and the "Generate and Publish Scripts" wizard popped up, showing the following...

The problem is that it just sits there, with the progress bar whizzing along, but apparently not doing anything. The Task Manager shows very little CPU or memory being used, but it never gets past this stage. I left it overnight, and it was still there about 14 hours later.

The database has 62 tables, and a backup file is about 750Mb, so it's not that big.

I tried clicking the "Set scripting options" link on the left and continuing from there, but when it tried to generate the script, it gave an error...

I presume this is because it hadn't loaded the database information.

Anyone any ideas? I need to get this database loaded, but as the target machine is a lower version, I can't restore a backup.


texts: Change logging level programmatically for multiple jobs

texts:
I have multiple Sql Agent jobs, with multiple steps each.  For a large subset of these jobs, I want to set the logging levels to PERFORMANCE instead of BASIC -
 the logs are taking a lot of log space, and, more importantly, are causing a slowdown for other processes.

I went into SysJobSteps, but there is no column that I can update.  I am considering changing hundreds jobs so doing this manually isn't an option for me.

How do I do this using TSQL?

Here is an image of the 4 options available:


texts: How to return the value from a query with SP_EXECUTESQL in a stored procedure

texts:
I have a stored procedure that generated a query string and then executes it with

EXECUTE SP_EXECUTESQL @Query

The query, e.g., is: Select Name, Age from tableA, so very simple.

But how to get the result set back from the SP_EXECUTESQL (into a variable?) and then return it as result set in my stored procedure?


texts: When is a full table scan better than index scan?

texts: What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?


texts:
I'm trying to create a sample database in first steps after installing db2 Express V10.5
I've looked at the answers provided below, but I'm not able to resolve my issue.
How to grant rights for a user in DB2 Express?
DB2 Express on Windows 7 - Disable security?

Here is the exact error message

Creating database "SAMPLE" on path "C:"...
  Attempt to create the database "SAMPLE" failed.
  SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User ID:
"I32129
  'db2sampl' processing complete.

I'm logging on to my computer with a username on a domain managed by my company. My username is actually i321290, not sure if that error is accurate or if its hitting some character limit. While I was installing db2 I created a username and password for db2admin
which is in the PROD_EXPC.rsp file.
The DB2ADMNS and DB2USERS groups are created in my local groups. I created local users db2admin, i321290, and I32129 and added each of them to the above groups. The user names aren't case sensitive.
DOMAIN\db2admin and DOMAIN\I321290 are also members of these groups.
I've run the following commands

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

but still receive the same error.


texts: Dealing with measure groups of different granularity

texts:
Let's say I have a simple data warehouse with the following fact tables:

FactPurchase: one row per property purchased, with purchase amount, value, etc.
FactMonthlyStatus: one row per property, per month; with rent paid, total maintenance expenses, etc.

I have some dimensions that apply to FactPurchase (such as property, property type, location) and some dimensions that apply to FactMonthlyStatus (such as occupancy status, condition, and also the month).

I'd like to be able to have a cube set up where I can see facts from both of these tables regardless of the dimensions I select. For example, if I am looking at total rent paid this month (by putting rent paid as a measure in the browser grid and month in the filter), I'd like to be able to see property type as a dimension as well. That is, so I can see rent paid by property type for month X.

In SQL, this would be easy. I join FactPurchase to FactMonthlyStatus on the property dimension key, and then I put month in the where clause, and I group by property type and sum up rent paid. However, I'm not sure how to properly model this in SSAS. It seems that I need to relate the property type dimension to both facts (as measure groups), but that dimension only directly maps to FactPurchase. There is no property type dimension on FactMonthlyStatus. I've tried many to many in dimension usage, and that appears to work at first, but if I had a dimension to my browser (e.g., condition) from the FactMonthlyStatus table, I end up getting, as expected, a many-to-many situation, with all of the used combinations of property type and condition, and not the combinations valid as of the month specified. Referenced also doesn't work because there's no intermediate dimension between my condition dimension and property type dimension. There's an intermediate fact, though.


texts: Mongodb what is the cost of array queries?

texts:
As far as I can tell, find queries in mongodb involving an array appear very expensive. Take as example the queries given in: https://docs.mongodb.com/manual/tutorial/query-array-of-documents/

db.inventory.insertMany( [
   { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
   { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

And find query:

db.inventory.find( { "instock": { warehouse: "A" } } )

The above example can be described as simple as "Find all records connected to warehouse A". But what is the cost of the find query (big o)? Are there any indexing optimizations going on here, or is it basically an exhaustive search with a cost of O(N*K), where N is the number of inventory records, and K is the number of elements in the instock array of each record? and if so, are there any ways of optimizing this to minimize cost, by say indexing?


texts:
Hi I installed the latest WAMP on Windows 7. I would like connect via SSH to MySQL database and create dump.

As SSH client I use PUTTY.

I tried using the root account and also other accounts, but I get this error:

  server unexpectedly closed network connection

Then I tried using another SSH client Private Shell http://www.privateshell.com/

I get this error:

  other side does not support SSH protocol on this port

For connection I use these settings:

IP: localhost
PORT: 3306

MySQL version is 5.5.16 I am not sure if support SSH connection.

Is it needed to allow SSH in phpAdmin or what is the root of problem?


texts: How to find MYSQL Logs for Drop Delete Record?

texts:
I am using SQlyog Ultimate for Mysql GUI. I want to know who is deleting, Dropping table and record. I want to track the User Ip and Name.

If there is any method to track please share with me.

I found some Link but not able to understand
How to show the last queries executed on MySQL?

Thanks
Sitansu


texts:
I have a database of open-source project mentions. However, a lot of the project mentions are repeating with slight alterations, e.g.

scandum/quadsort: merge sort on steroids github.com/scandum/quadsort
scandum/quadsort merge but on steroids github.com/scandum/quadsort
better merge github.com/scandum/quadsort its merge on steroids!

To make the alerts about project mentions more useful, I would like to remove group identical/ near identical mentions.

If I have 1 mention, I can find similar mentions using tsvector. But how do I go counting the number of ~unique mentions?


texts: What could cause a text comparison to fail?

texts:
DB Version: PostgreSQL 8.2.15 (Greenplum Database 4.3.4.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 10 2015 14:15:10

I'm having a problem in one of our environments where the tables in one schema seem to behave incorrectly when doing simple queries, character columns are not comparing to literal strings or to other columns. Joins within the schema to other identical columns work, but joins to tables outside of the schema do not. Here's a simple example with literal strings.

I do this:

SELECT *
FROM carriers
WHERE carrier_code = 'FR'

Then I copy and paste the value from a character(32) column and do this:

SELECT *
FROM carriers
WHERE carrier_hash = '11aedd0e432747c2bcd97b82808d24a0'

This returns nothing. That's a straight copy-and-paste from that actual column as returned by the first query. If I do this, however:

SELECT *
FROM carriers
WHERE carrier_hash like '11aedd0e432747c2bcd97b82808d24a0'

That returns the same record. This also works:

SELECT *
FROM carriers
WHERE substring(carrier_hash,1,32) = '11aedd0e432747c2bcd97b82808d24a0'

Why might = and like behave differently with a simple string and no _ or % characters? This has been working for months. The only thing that's changed recently is we've backed up, cleared out, and restored the database. Could something have become messed up during the restore?

We have other tables in other schemas that have the same column, and those work just fine.

Our DBA recently restored this environment from a backup. Don't know if that is relevant.


texts:
I want to calculate total driving time excluding time between Pause and Resume Driving using sql query.

Here is the sample data:

Create Table Tracker
(LocationID int,
RequestID int,
TrackingDatetime datetime,
Status varchar(30));
go

Insert into Tracker
values
(1, 22, '2019-06-17 14:10:00', 'Stop'),
(2, 22, '2019-06-17 14:08:00', 'Driving'),
(3, 22, '2019-06-17 14:06:00', 'Driving'),
(4, 22, '2019-06-17 14:04:00', 'Resume'),
(5, 22, '2019-06-17 14:02:00', 'Pause'),
(6, 22, '2019-06-17 14:00:00', 'Driving'),
(7, 22, '2019-06-17 13:58:00', 'Driving'),
(8, 22, '2019-06-17 13:56:00', 'Start');

Grid view of sample data: