Is it possible to create a view in one database from another database in oracle when databases are different machine? Lets say I have a oracle database db1(actually user)  in machine A
and another database db2 in machine B. Is it possible to create view in
db2 from db1.If possible how ?

Yes, by using database links. For example, in db2:

create database link db1_link connect to myuser identified by mypassword using 'db1';
create view myview as select * from mytable@db1link;
select * from myview;

Where using 'db1' refers to a TNS entry, but it could be replaced with the actual TNS descriptor.


Serialized objects vs multiple tables What would be the best way to store several entries in a database, each containing a list of other entries? An example of this would be a table of receipts, where each receipt has some information that's always present, such as time and the total amount. In addition to this, each receipt would have a variable-length list of entries, each one consisting of a descriptive text and a price.

I would be using a mySQL database to store the information and PHP to access it.

This is a typical 1 to many relationship (or parent child relationship). The parent would have the fixed infromation and the child would have the many varying information.
The key of the parent must appear in the child.
Example:

Receipt : {ReciptID, Date}
RecieptItem  : {ItemID, CustomerPrice, Quantity,TotalPrice,ReceiptID, Status, Date}
Item:{ItemID, ItemName, Price}

Now each Recipt can have 0,1 or more Recipt Items.
Each Item is references on 0,1, or more ReciptItems.

The column ReceiptID in ReceiptItem is referred to as a Foreign Key. A constraint could be built to make sure that every ReciptID in ReciptItem corresponds to a ReceiptID in parent Receipt table.

Example ERD:


Fulltext Search Doesn't Match a Hashtag We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns:

CREATE TABLE `forums_posts_search` (
  `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `topic_id` bigint(20) NOT NULL,
  `topic_title` varchar(255) NOT NULL,
  `post` text NOT NULL,
  PRIMARY KEY (`post_id`),
  FULLTEXT KEY `search` (`topic_title`,`post`)
) ENGINE=MyISAM AUTO_INCREMENT=1285900 DEFAULT CHARSET=latin1;

For a row that has the following forums_posts_search.post value:

INSERT INTO `forums_posts_search` (`post_id`, `user_id`, `topic_id`, `topic_title`, `post`)
VALUES
(1, 1, 1, 'Hashtag Test', 'Hi,
\n
\nMine #HME as the #BC don\'t work, too.
\n
\nI had to send a ticket about this ?
\n');

the following query does not return the result when a user goes to search for the parameters "#hme" or "#bc":

SELECT [...], MATCH(p.topic_title, p.post) AGAINST ("#hme") as score
FROM forums_posts_search p
INNER JOIN forums_topics t ON (p.topic_id = t.topic_id)
INNER JOIN forums f ON (t.forum_id = f.forum_id)
LEFT OUTER JOIN users u ON (p.user_id = u.id)
WHERE MATCH(p.topic_title, p.post) AGAINST ("#hme")
ORDER BY score

Running MariaDB on top of MySQL 5.5.44. I recently rebuilt the FULLTEXT indexes manually and then ran a REPAIR TABLE [...] QUICK; but the results are still the same.

Why isn't this query returning records where forums_posts_search.post contains "#hme" or "#bc"? Strangely, a longer search parameter (i.e. "#hashtag") returns all of the results.

Change the ft_min_word_len value to specify the minimum length of the word to be included in a MyISAM FULLTEXT index.

ft_min_word_len
The minimum length (default: 4) of the word to be included in a MyISAM FULLTEXT index.
Note: FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable.
Use REPAIR TABLE tbl_name QUICK.

Source: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_ft_min_word_len


Grouping stock in 6 month ranges I have been pulling the total stock within the first 6 months. How can I pull the total stock within the last 6 months in the same query?

Also I want the result of the query as shown below:

  Stock Code | the first 6 months   |  the second 6 months   etc.

My SQL query:

 SELECT TOP (100) PERCENT STOK_KODU,
    COALESCE (SUM(CASE WHEN kod1 = 'G' THEN kod2 ELSE 0 END), 0)
   - COALESCE (SUM(CASE WHEN kod1 = 'C' THEN kod2 ELSE 0 END), 0) AS StokToplam1
FROM s_hareket WHERE (STOK_KODU BETWEEN '01001' AND '75000')
and (tarih BETWEEN '2011-01-01' and '2011-06-30')
GROUP BY STOK_KODU
ORDER BY STOK_KODU

You should be able to do it with a dateadd and getdate functions:

SELECT TOP ( 100 ) PERCENT
        STOK_KODU ,
        COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                          ELSE 0
                     END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                  ELSE 0
                                             END), 0) AS StokToplam1
FROM    s_hareket
WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
        AND ( tarih BETWEEN dateadd(month,-6,GETDATE()) AND GETDATE() )
GROUP BY STOK_KODU
ORDER BY STOK_KODU

If I understand your question correctly, you want to union the results, then pivot it. That leads to the following query:

SELECT * FROM
    (SELECT TOP ( 100 ) PERCENT
            'last' AS t,
            STOK_KODU ,
            COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                              ELSE 0
                         END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                      ELSE 0
                                                 END), 0) AS StokToplam1
    FROM    s_hareket
    WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
            AND ( tarih BETWEEN dateadd(month,-6,GETDATE()) AND GETDATE() )
    GROUP BY STOK_KODU
    ORDER BY STOK_KODU

    UNION ALL
    SELECT TOP ( 100 ) PERCENT
            'first' AS t,
            STOK_KODU ,
            COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                              ELSE 0
                         END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                      ELSE 0
                                                 END), 0) AS StokToplam1
    FROM    s_hareket
    WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
            AND ( tarih BETWEEN '2011-01-01' and '2011-06-30' )
    GROUP BY STOK_KODU
    ORDER BY STOK_KODU) AS sourceTable
PIVOT (
    SUM(StokToplam1)
    FOR T IN ([first], [last])
) AS p


Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them I have a table with below structure:
create table TBL_TEST
(
  col_id   NUMBER,
  col_name VARCHAR2(500)
)

Some example data :
col_id | col_name
-----------------
  1    | aetnap
  2    | elppa
  3    | ananab

What I need to do is to split characters of column col_name for each col_id for example for col_id=1 we must have :
col_id | col_name
-----------------
  1    | a
  1    | e
  1    | t
  1    | n
  1    | a
  1    | p

this query is fine when there is only one record in the table :
SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL
  FROM tbl_test t
CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null

but as soon as I insert another record in the table (say col_id=2 and col_id=3) I can not have the desired result. I want to know two things:

Why is this query works fine for one record and it does not for more ?
what is the best way to split the characters when there is no delimiter(like , or . or *) between them?

Thanks in advance

1- Because with multiple rows, multiple paths exist to reach a level in the hierarchy
select lvl, count(*) from (
SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL, level lvl
  FROM tbl_test
CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null
) group by rollup(lvl)
order by 1;

       LVL   COUNT(*)
---------- ----------
         1          3
         2          9
         3         27
         4         81
         5        243
         6        486
                  849

2- I am not saying it is the best, it is just one possible way to do it (add delimiters and use other tricks):
drop table tbl_test;
create table tbl_test
(
  col_id   number,
  col_name varchar2(500)
);

insert into tbl_test values (1, 'aetnap');
insert into tbl_test values (2, 'elppa');
insert into tbl_test values (3, 'ananab');
commit;

select
  col_id, trim(column_value) val
from tbl_test,
  xmltable(trim(trailing ',' from regexp_replace(col_name, '(.)', '"\1",')));

COL_ID VAL
------ ---
     1 a
     1 e
     1 t
     1 n
     1 a
     1 p
     2 e
     2 l
     2 p
     2 p
     2 a
     3 a
     3 n
     3 a
     3 n
     3 a
     3 b

Here you can find multiple methods for tokenizing strings:
https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/


SQL Server Encrypted Connections - Configuration Manager does not see Certificate My goal is to implement encrypted connections on Test SQL Server instance

  First, tried to create self-signed certificate in the Certificates snap in (certlm.msc), Personal > All Tasks > Request New Certificate...

But creation failed, because Test SQL Server machine could not contact (no network connection to) one of the AD servers on which AD Certificate Services are installed.

It can contact some other AD servers, but these do not have AD CS, possibly sysadmin will help to resolve it but not today.

  So I moved on to "New-SelfSignedCertificate" PowerShell cmdlet, which can create self-signed certificates

Tried below ways for creating cert:

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com" -CertStoreLocation "cert:\LocalMachine\My"

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My"

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1"

New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048

New-SelfSignedCertificate -Subject "CN=TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048

New-SelfSignedCertificate -Subject "CN=TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048 -KeySpec KeyExchange

Each time after generating certificate, right clicked it in Certificates snap in, All Tasks > Manage Private Keys... and granted Read and Full Control permissions to SQL Server's service account

But, in the SQL Server Configuration Manager, each time when I go to SQL Server Network Configuration > Protocols for MSSQLSERVER > Properties, I can not see newly generated certificate on the Certificates tab

P.S. also tried adding "-KeySpec KeyExchange" to my PowerShell command, but Windows Security requests some smart card and I can't proceed further

  Question: what I am missing ?
  How to properly create self-signed certificate that will be visible in SQL Server Confirugation Manager ?

Answer from comment:

Try including -Type SSLServerAuthentication in the New-SelfSignedCertificate cmdlet to ensure the certificate is for Server Authentication which is a requirement for the SQL SSL Certificate. Also, check out this link for an example PowerShell script for generating a suitable self-signed cert


Get last login time of each User ID How to get last login time of selected IDs?

Table

|  user_id  |  lastlogin
|  100      |  2016-03-05
|  110      |  2016-03-21
|  100      |  2016-03-29
|  100      |  2016-04-03
|  110      |  2016-04-02

Here is what I need:

|  user_id  |  lastlogin
|  100      |  2016-04-03
|  110      |  2016-04-02

I have tried this:

SELECT DATE_FORMAT(lastlogin, '%d-%b-%Y') AS lastlogin,user_id
FROM last_logins
WHERE user_id IN (
100,110)
GROUP BY user_id
ORDER BY lastlogin DESC

If I use GROUP BY, it shows the old login record of user_id. If I use just ORDER BY it gives perfect results, but with multiple last logins of user_id.

You need a simple MAX:

SELECT DATE_FORMAT(MAX(lastlogin), '%d-%b-%Y') AS lastlogin,
   user_id
FROM last_logins
WHERE user_id IN (100,110)
GROUP BY user_id
ORDER BY MAX(last_logins.lastlogin) DESC

Don't sort by the formatted lastlogin as this is not a date anymore.

Don't alias using an existing column name, this might lead to confusion and well-hidden bugs (the same condition in WHERE and HAVING meaning different things, for example).

See SQL Fiddle by jkavalik


Merging databases with identical schema We used to have one database per client, and we're moving to using a single database.
I need to find a way to merge the databases without losing data, including foreign keys.
My primary keys are all integer.
What I understood is that the only way to do this is to update all primary keys and all foreign keys on all but one database (so that there's no conflict), then import them in a new db.
Is this true?
If so, the only way I found is to manually set all my foreign keys to DEFERRABLE, then manually increment all my primary keys.
Is this true? Is there a way to automate it to ALL my primary and foreign keys?

I have had this problem. Sometimes I have had to solve it the hard way. But others I simply take a large enough number and add it to ALL the numeric keys in one of the databases. Then I merge the two databases.
Let's say if my biggest key in BD1 is 1213456, I add 1000000 to all the keys in BD2.
You can prepare your scripts querying information_schema to generate all modifications/alterations/updates. Something like (only PK)
SELECT CONCAT('UPDATE ', U.TABLE_SCHEMA,'.', U.TABLE_NAME, ' SET ', U.COLUMN_NAME, ' = ',U.COLUMN_NAME,' + 100000' )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS    C
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
        ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
           AND U.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
           AND U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'

Will return queries like UPDATE dbo.Table1 SET id = id + 100000
And running results queries when you feel confident enough. Good luck!


Migration From Oracle to PostgreSQL Encoding Errors I'm working on migrating data from an oracle database to a postgresql database. I running into a problem where I export the data from oracle, using sql developer, and importing it into postgresql. When I try to import the data into postgresql I get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcb 0xcf
CONTEXT:  COPY project, line 646

********** Error **********

ERROR: invalid byte sequence for encoding "UTF8": 0xcb 0xcf
SQL state: 22021
Context: COPY project, line 646

The data in the oracle server looks like this in sql developer:

Ã9ÃýÃ0Ã

When sql developer exports the data to a file, it looks like this:

When I try to set the encoding in sql developer when I export the data I get this loading menu item:

and it doesn't change.

What do I need to do to properly export and import the data from oracle and postgresql?

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit.

This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value?

0xcb 0xcf is indeed nonsense as utf-8. It's a continuation byte without any appropriate context.

Trying other encodings, it's:

ËÏ in latin-1. Unlikely. Same in iso-8859 3,4,9,10,14,15, and in the default Windows encoding for Western Europe / US, cp1252.
ËĎ in iso-8859-2
ЫЯ in iso-8859-5
ثد in iso-8859-6
ΛΟ in iso-8859-7
หฯ in iso-8859-11
ĖĻ in iso-8859-13

Any of those look like likely candidates, given what you know about the original data?

You need to either export in a known encoding, or determine the encoding of the input reliably.

It's also possible that the data is in fact starting with a utf-8 continuation byte and was supposed to be utf-8, but something has mangled it by cutting utf-8 strings up byte-wise.

If you can post a complete line of exported CSV it might help.


Mysql error 28 no space left on the device I keep running in to the same problem with mysql with error code 28 which means there is not enough space left on the device.

Now the weird part is that there is enough space, here are some tips i found on google to find out if i have enough space. (please keep in mind that i'm newbie when it comes to linux systems)

du -sh /var/log/mysql

36K     /var/log/mysql

df -h

Filesystem            Size  Used Avail Use% Mounted on
udev                  1.5G  4.0K  1.5G   1% /dev
tmpfs                 604M  224K  604M   1% /run
/dev/disk/by-uuid/7a06472d-4cc9-4383-93b0-4ecee9b21c04
                       24G   15G  8.4G  64% /
none                  5.0M     0  5.0M   0% /run/lock
none                  1.5G     0  1.5G   0% /run/shm
/dev/vda1             461M   36M  402M   9% /boot

I have been trying to solve this for so long now, but nothing helps, any kind of advice would help.

EDIT

The most weird part is that if i delete big file 200-800mb this error goes away for a very short time and then it returns again. I have actually removed around 10GB files in hopes of fixing this but it didnt help.

"no space left on the device" could also mean that the partition is out of inodes. Use df -i to check inode usage.

Edit: Each file, directory, and symbolic link requires one inode. So the idea is to remove some files from the / partition. It doesn't matter how large the files are. You can of course just pick some files on the partition and move them to another partition, if you can find some that are convenient to move.

It's unusual to run out of inodes, so it's possible you have some directory full of small or zero-length files somewhere that you're not aware of. Unfortunately, I don't know of an easy way to find this sort of thing. Running find / -xdev would print the name of every file and directory on the partition. You might be able to spot some suspicious directory full of empty files.

If you can add additional disk partitions to the system, you could pick some directory on the / partition and make it into a separate partition. Copy all of the files in that directory to the new partition, delete them from the / partition, then mount the new partition on top of the old (and now empty) directory.

The nuclear option would be to back up the / partition, wipe it, and rebuild it with more inodes. That's an advanced topic and I won't try to explain it here.


Getting SQL Server query history I have used below query to get the history queries on ran SQL Server but it's not giving me the whole history it just returns queries ran on nearly about last 1/2 an hour. I want to get the queries ran on week ago. Is there any solution?

SELECT  dest.text, deqs.last_execution_time
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE    dest.text NOT LIKE '%dest.text%'
         order by deqs.last_execution_time desc

I believe this is handled internally in SQL Server's query cache using memory available on the system.  I know you can empty all of the cache by running the following:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

I just don't think you can alter how many queries it's saving.

Good luck.


MySQL upgrade 5.0.88 to the latest version I have some questions regarding upgrading from 5.0.88 to the latest version.

Why should I upgrade?
If so, which version should I consider?
What are all the known risks?
Is upgrading step by step suggested? Or is binary replacement suggested?

Why should I upgrade ?

For MySQL 5.1.37 and prior, InnoDB does not take advantage of multiple CPUs/multiple cores. No amount of scaling up of hardware will ever make InnoDB go any faster. MySQL 5.1.38 first introduced the InnoDB Plugin which has many aspects of tuning now available for multiple CPU/multiple core engagement. This is now fully available as native InnoDB in MySQL 5.5./5.6. Here are my past posts on this subject

Sep 12, 2011 : Possible to make MySQL use more than one core?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

  if so, which version should i consider?

Although it was released 2 months ago, I trust MySQL 5.6.10 thus far. I already gave a client that version from MySQL 5.0.51a. Check out this post from Jan 19, 2012 :
Which version of MySQL should I use? . The client's production server is replicating to the new MySQL 5.6 Cluster and waiting for the Client to cutover.

  What are all the known risks

As with any release of MySQL, you should always read the Release Notes. The only thing to worry about is not doing an upgrade correctly. Another risk is forgetting to run mysql_upgrade script to fix the mysql.user table's column layout.

  upgrading step by step is suggested? or binary replacement suggested?

Personally, I am uncomfortable with a binary replacement. I have a method to upgrade from MySQL 5.0.88 straight to MySQL 5.6.10.

STEP 01 : mysqldump all databases except the mysql schema

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql

STEP 02 : dump the grants as pure SQL commands

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql

I have recommended this technique before : importing myisam 5.0 database into a 5.5 innodb server

STEP 03 load each file into a new MySQL 5.6 Instance

Before you can do anything, you have so set a password for root@localhost in MySQL 5.6

The temporary password is located in ~/.mysql_secret. Login as root@localhost using that password. Then, change the password with

SET PASSWORD=PASSWORD('whateverIwantasthenewpassword');

While in the MySQL Client, simply run

source Data.sql
source Grants.sql

That's all that's needed.

Give it a Try !!!

UPDATE 2019-06-24 13:30 EDT

For the benefit of Linux users and in response to the comment from @SherylHohman, I tested the above statements from STEP 01 and STEP 02 without MYSQL_CONN defined (left blank on purpose) in a Vagrant environment:

root@vt-mysql:~# mkdir DBAStackExchange
root@vt-mysql:~# cd DBAStackExchange
root@vt-mysql:~/DBAStackExchange# ls -l
total 0
root@vt-mysql:~/DBAStackExchange# SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
root@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
root@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
root@vt-mysql:~/DBAStackExchange# DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
root@vt-mysql:~/DBAStackExchange# MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
root@vt-mysql:~/DBAStackExchange# mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange# SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sqlroot@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
root@vt-mysql:~/DBAStackExchange# mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange# ls -l
total 4056
-rw-r--r-- 1 root root 4146556 Jun 24 10:22 Data.sql
-rw-r--r-- 1 root root    1326 Jun 24 10:23 Grants.sql
root@vt-mysql:~/DBAStackExchange# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

The statements I posted here 6 years ago still work in Linux. I ran these in MySQL 5.6.33


Override Dimension Column Value I have a dimension with a column called 'Type'. There is a list of default 'Types' in this dimension.

However, this 'Type' column can be overriden. i.e. each user can replace the default value with their own value. So in theory there could be a different type for each user. Or multiple users could be using the same type.

What is the best way of doing this? Add a 'Type_User_Value' column in the fact table? Or how would I do this in the dimension?

Thanks!

Sounds like you've already implemented some form of Slowly Changing Dimension, dealing with times, but need to also deal with user preferences as well?  For ease, I'm going to use  Type 2 SCDs and assume temporal changes, but you could obviously swap those out for a more appropriate approach as your specific solution requires.  I'm also going to imagine the relevant parts of your structure completely abstractly in MS SQL so I can also refer to this SQL Fiddle later:

The example of an abstract system without any form of SCD involves the following structures and forms our base case:

CREATE TABLE dbo.DimFoo
(
    DimFoo_PK       TINYINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( DimFoo_PK ),
    FooName         NVARCHAR( 5 )
);

CREATE TABLE dbo.FctBar
(
    FctBar_PK       SMALLINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( FctBar_PK ),
    DateCreated     DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DimFoo_FK       TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                        REFERENCES dbo.DimFoo ( DimFoo_PK )
);

In this example, the fact table contains two entries, each keyed to the dimension table.  When queried, both entries show the dimension value to be "Sprocket".  The requirement handed down is that in 2010, the definition of "Sprocket" changed to "Schprocket," giving way to the need for an SCD to be implemented.  In the academic example, often these SCDs emerge through a replacement of the dimension table, but in practice, I've found it largely preferable to simply add SCDs to the structure and re-key the necessary fact tables.  This allows use of the original dimension table as form of master look-up and a continued entry point for new dimension values which can then propagated into the SCD and fact table.

CREATE TABLE dbo.ScdFoo
(
    ScdFoo_PK       TINYINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( ScdFoo_PK ),
    DimFoo_FK       TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                        REFERENCES dbo.DimFoo ( DimFoo_PK ),
    FooName         NVARCHAR( 16 ),
    DateStart       DATE NOT NULL,
    DateEnd         DATE
);

With this structure in place, we can run an update as outlined in the simple example of a Type 2 SCD, making sure to then drop the old foreign key reference from the fact table for the purpose of retaining normalization levels.  The new query pulls the expected name for each entry in the fact table, now reflecting changes in time.

At this point, I believe your question is largely based around the idea that a number of specific users are also interested in seeing this same fact data keyed differently.  In the vein of how this has been illustrated thus far, I assume the existence of another table for storing user profile information, which I will define as follows:

CREATE TABLE dbo.UserProfile
(
    UserProfile_PK   TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( UserProfile_PK ),
    Code             NVARCHAR( 8 )
);

An additional requirement detail is required at this point, either way - do the users which are interested in seeing their own definitions wish to do so per time slice ( as already expressed in the SCD ) or by value ( as originally stored in the dimension from the base case )?  In either solution, this can be accomplished in a simple manner through a one-to-many relationship between the user profile table and the original dimension table, then queried as shown in the original SQL Fiddle, or alternatively by simply swapping the reference from the original dimension to the slowly changing dimension instead.

CREATE TABLE dbo.DimUserProfileFoo
(
    DimUserProfileFoo_PK
                     TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( DimUserProfileFoo_PK ),
    UserProfile_FK   TINYINT NOT NULL, FOREIGN KEY ( UserProfile_FK )
                         REFERENCES dbo.UserProfile ( UserProfile_PK ),
    DimFoo_FK        TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                         REFERENCES dbo.DimFoo ( DimFoo_PK ),
                         UNIQUE( UserProfile_FK, DimFoo_FK ),
    FooName          NVARCHAR( 16 ) NOT NULL
);

If your users are particularly crusty, they may wish to see their own definitions of dimensions based on their own time slices.  This would simply call for the new user-keyed dimension to also be a SCD, which, sticking to the Type 2 SCDs we've used, could be queried as expressed in this SQL Fiddle.

CREATE TABLE dbo.ScdUserProfileFoo
(
    ScdUserProfileFoo_PK
                     TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( ScdUserProfileFoo_PK),
    UserProfile_FK   TINYINT NOT NULL, FOREIGN KEY ( UserProfile_FK )
                         REFERENCES dbo.UserProfile ( UserProfile_PK ),
    DimFoo_FK        TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                         REFERENCES dbo.DimFoo ( DimFoo_PK ),
    DateStart        DATE NOT NULL,
                         UNIQUE( UserProfile_FK, DimFoo_FK, DateStart ),
    DateEnd          DATE,
    FooName          NVARCHAR( 16 ) NOT NULL
);


The problem is (probably) in the definition of "later".  The query execution is free to evaluate pieces of the query in whatever order it desires.  This makes it difficult to predict whether it is trying to "use" the @ variable before or after it was "set".

And for that reason, @ variables are (or soon will be) deprecated.

If you can provide a small test case with input and desired output, perhaps we can help you rewrite the query to avoid @ variables.


SQL Server replication service in Amazon RDS I have a local infrastructure with multiple SQL Servers. Some of the servers have replications services to copy some data to a master database. Is it possible to have the replication services to work on a RDS SQL Server instance? Or do I need to have a EC2 instance with SQL Server and configure the service by myself?

Found the answer in the Amazon documentation. The replication feature is currently not supported.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html


Having issue with window function COUNT counting duplicates I have a query like so:

select
   distinct(line_items.email) as email,
   count(orders.order_id) over(partition by orders.order_id) as num_orders
from
   line_items
   inner join orders on
   line_items.order_id=orders.order_id

This is apart of a broader query where I need to be able to select from line_items vs. orders.

The problem here is that:

A user has multiple line items on a specific order (bought 2 different products)
The window function sees two rows and is still counting twice

I can't do count distinct because Redshift doesn't support count distinct as window function.

The records looks something like this:

line_item_id,order_id,email
123,1,bob@some.tld
124,1,bob@some.tld

The result of the above query looks like this:

email,num_orders
bob@some.tld,2

Is there a way I can get this to be:

email,num_orders
bob@some.tld,1

While retaining use of the window function?

It looks like you are trying to return the count of orders associated with an e-mail id? In which case I think you can change the partition to

count(orders.order_id) over(partition by line_items.email) as num_orders

Rather then using a window function, you could also just do a group by

select t.email,
          count(*) as num_orders
     from (
  select line_items.email,
         orders.order_id
    from orders
    join line_items
      on orders.order_id = line_items.order_id
    group by line_items.email,
              orders.order_id
    ) t
    group by t.email


Error converting data type varchar to numeric I keep getting this error and I dont  understand why. I checked all CONVERT functions but not sure where is the issue.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This is the query and you can see Line 1 does not have any type of conversion.
SELECT
  'Entity Code|Department Code|Job Code|Pay Code|Pay Period Code|Employee Code|Fiscal Year|Time
  Class|Unit Type|Amount|Home Entity Code|Home Department|Pay Period End Date' AS PRL_HEADERR
UNION ALL SELECT
  (ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DIST_COMPANY, 4))), '')
  + '|' + ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DST_ACCT_UNIT2, 4))), '')
  + '|' + ISNULL((SUB.JOB_CODE2), '')
  + '|' + ISNULL((SUB.PAY_CODE2), '')
  + '|' + ISNULL((SUB2.PAYPERIOD), '')
  + '|' + ISNULL(
      (CASE WHEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),1,1)='0'
      THEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),2,LEN(RIGHT(REPLICATE(0, 7) +
      SUB.EMPLOYEE, 7)))
      ELSE RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7) END), '')
  + '|' +       ISNULL((SUB.FISCAL_YEAR), '')
  + '|' +       ISNULL((SUB.TIME_CLASS), '')
  + '|' +       ISNULL((SUB.UNIT_TYPE), '')
  + '|' +       ISNULL((SUB.UNIT_VALUE), '')
  + '|' +       ISNULL(HOME_ENTITY_CODE, '')
  + '|' +       ISNULL(HOME_DEPARTMENT, '')
  + '|' +     ISNULL(PAY_PER_END_DATE, ''))  AS PRL_HEADER
FROM
 (
  SELECT
     TOP 100 PERCENT DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Hours' AS UNIT_TYPE,
     HOURS AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  UNION ALL
  SELECT TOP 1000
     DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Amount' AS UNIT_TYPE,
     WAGE_AMOUNT AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  ORDER BY
     EMPLOYEE,
     PER_END_DATE
 )
 AS SUB
 INNER JOIN
  (
     SELECT
        ROW_NUMBER() OVER (ORDER BY PP_END_DATE) AS ROWNUM,
        SUB3.PP_END_DATE,
        (
           (CONVERT(VARCHAR(4), SUB3.PP_END_DATE, 112) + '-') + ROWNUM
        )
        AS PAYPERIOD
     FROM
        (
           SELECT DISTINCT
              TOP 100 PERCENT PER_END_DATE AS PP_END_DATE
           FROM
              TEST_TABLE
           WHERE
              (
                 PER_END_DATE >= CAST('20200101' AS DATETIME)
                 AND PER_END_DATE <= CAST('20201231' AS DATETIME)
              )
           ORDER BY
              PER_END_DATE
        )
        AS SUB3
  )
  AS SUB2
  ON SUB2.PP_END_DATE = SUB.PER_END_DATE

This query was written in Oracle by someone else and I'm converting it to MS SQL.  Also the app is large and legacy, written in Cobol.  Good thing is there is not a lot of data here therefore performance is not a big concern.

It's because the first parameter of the REPLICATE function is a VARCHAR, not int. Please see the documentation here.
Instead of doing REPLICATE(0, 4) you'll want to do REPLICATE('0', 4) etc.
The error itself can be a little confusing when it says the word convert, because it doesn't necessarily mean the CONVERT function, rather it's trying to do implicit conversion of the INT value 0 to a VARCHAR value since that is the data type of the parameter for the REPLICATE function. This error can occur for any function that expects a certain data type for its parameter but is passed in a value that's a different data type that is unable to be implicitly converted.
An example of implicit conversion that would work, is if a function had a parameter that was of type BIGINT and you passed in an INT value like 13 instead.


How to populate a table's foreign keys from other tables? I have a database with the following tables:

Airport ( iata PK, airport, city, state, country, lat, long)

cancellation_cause ( cod_cancellation PK, description)

Manufaturer (id_manufacturer PK, manufacturer_name)

Model (id_model PK, model_name, id_manufacturer FK)

Airline( airline_code PK, description)

airplane_type (id_AirplaneType PK, airplane_type)

engine_type (id_engine PK, engine_tyoe)

Aircraft_type (id_aircraft PK, aircraft_type)

Airplane (TailNumber PK, id_model FK, id_aircraft FK, airline_code FK, id_AirplaneType FK, id_engine FK, Issue_date, status, year)

Flight (id_flight PK, cod_cancellation FK, TailNumber FK, iata_origin FK, iata_destin FK, Year, Month, DayofMonth, DayofWeek, DepTime, CRSTime, ArrTime, CRSArrTime, FlightNum, AtualElapsedTime, CRSElapsedTime, AirTime, ArrDelay, DepDelay, distane, TaxiIn, TaiOut, Cancelled, Diverted, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay)

Note: PK - Primary Key;  FK - Foreign Key

I have a csv file "flights" with millions of records containing information such as "TailNumber, AirlineCode, etc." for each of the flights.

Also have a supplementary csv "Airplane" with information about the plane (TailNumber, engine type, year, etc.)

In tables Flight, Airplane and Model some fields can be copied directly but I don't know how to load data from foreign keys correctly.

Someone help me?

Do you already have all the Airports?  Some of them?  None of them?  The details depend on which case you are doing.

Two steps (per normalization table):  INSERT...SELECT DISTINCT ... LEFT JOIN .. WHERE .. IS NULL to add to normalization table.  Then UPDATE ... SELECT ... JOIN... to get the ids back.

The code here is aimed at the "some of them" case, bu probably works for all 3 cases.

If you actually have FOREIGN KEY constraints enabled, be careful of the order of performing operations -- else you could get errors.


According to your desired result, you should group_concat workers group by department's name.

select d.name, group_concat(w.work_id) workers_id
from   department d
join   worker w
on     w.department_id = d.id
group by d.name;

name                            | workers_id
:------------------------------ | :---------
Academic Personnel              | 6,4
Anthropology                    | 3
Arts & Lectures                 | 5,2,7
Business and Financial Services | 1
Computer Science                | 8

dbfiddle here


Why distributed AG is preferred to normal AG for cross datacenter failover scenario? I have two node WSFC and currently I'm working on DR solution with availability group.I already read microsoft doc about distributed AG.So  my question is what are benefits of using distributed AG compared to normal AG? Can we just stretch WSFC across data center and make this happen with normal AG?

what are benefits of using distributed AG compared to normal AG?

Complete isolation of your clusters is the only benefit.

  Can we just stretch WSFC across data center and make this happen with normal AG?

Yes, and you normally should do just that.  Stretch clusters are not complicated, and modern versions of Windows make them downright simple.  IE the cluster node voting configuration and quorum is pretty much automatic.  And you can even implement automatic cross-datacenter fail-over with Cloud Witness.


Is there any better way to list all the databases in Postgres except database named postgres? SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true And datname NOT IN ('postgres')

Using the code above, I get all the databases except postgres as shown in the image below:

Is there any better/cleaner/nicer way than my code above to get the same result?

There is no real improvement possible, but I think the following is more readable:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
  AND datallowconn
  AND datname <> 'postgres';


How to secure SQL Server in an enterprise environment? So I've been tasked with learning how to secure our SQL Servers.  Here's the scenario:

Individual users have read access to a table in SQL Server.  This table have apx. 33 million rows, and growing (telemetry data).

Some genius user discovered Power BI and installed it (Desktop), along with the on-prem data gateway on their workstation.  Then they did the same thing for 40 of their friends.

THEN they created a .pbix report to query this table.
THEN they published the report to their personal workspace.
THEN they emailed the report to their 40 friends with instructions on how to set up the gateway on each workstation - and how to enable the data sync schedule for twice a day.

So now our 33 million records are exiting our network (40 users * twice a day = 80 times per day).  Besides being a strain on the SQL Server - we just don't want that  data being published to a service we don't have any control over by users that aren't authorized to be doing that.

So the questions are:
How can we prevent this scenario?  What's the proper way to set up a database so a user can access the data, but not be able to publish the data off-site?  Are there firewall rules we can set up to block the gateway service?  My understanding was it just needed port 443 outbound - which we obviously can't block.

Any recommended training courses that cover this kind of stuff?

Thanks!

Here are couple of comments / advice regarding performance / network:

1) Those 40 guys that currently download data daily, they obviously using "Import" mode, and they added this table as a data source, and Power BI pulls this whole table (33 mln row) every time report refreshes.
What you can do is to work with them to refactor how they query data. If they need an aggregate values from that table, they can use "Import" mode and run an aggregate query instead of pulling whole table each time. This will be MUCH lighter in terms of performance and network consumption

2) why all 40 users each need to have personal gateways ? If those guys really need this data, 1 user should be enough, this user can then share his report to his colleagues so others 39 won't be hitting your SQL Server - they would just read report data from the 1 guy's PC


MySQL consuming memory, seeking advice on how to configure it I have a small VPS that handles my 3 personal WordPress websites.

At a certain time MySQL started consuming more and more memory and actually (for months) the server has been running at 99% RAM usage with MySQL eating more than 2 GB alone.

My VPS is an Ubuntu 14.04.05 with MySQL 5.7.23 (I upgraded to avoid the memory eating bug), 2 amd64 CPU@3.9GHZ and 3 GB of RAM.

The webserver is configured by Virtualmin, with little to no modifications applied by myself (read after).

I tried MySQLTuner, and some other scripts but nothing helps.

I have only these 3 websites and every time the traffic spikes (these are personal websites, so "spikes" are maybe 3 users at the same time) the VPS crashes.

Can you please help me configure it to use lesser RAM or diagnose if there is an issue?
I'm thinking about switching to MariaDB, but without changing the configuration I don't think that anything will change.

All the relevant informations are in this pastebin (posted Aug 7, 2018) for readability:

https://pastebin.com/UasHBcH1

According to MySQL Calculator it should only require 293 MB and I don't have so much visitors to justify this usage.

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=20  # from 8  mysqld takes 8 minimum, keep breathing room
read_buffer_size=1M  # from 256K to reduce handler_read_next count
table_open_cache=500  # from 80  to support 327 opened since startup
innodb_buffer_pool_size=1G # from 128M for effective 750M for growth over time
innodb_log_file_size=64M  # from 8M to be larger than innodb_log_buffer_size
innodb_io_read_threads=16  # from 3 to speed up reads
innodb_io_write_threads=16  # from 3 to speed up writes
innodb_io_capacity=1000  # from 200 to increase iops limit
max_heap_table_size=16M  # from 12M to raise RAM capacity
tmp_table_size=16M  # from 12M should be same size and will reduce created_tmp_disk_tables
innodb_buffer_pool_dump_pct=90  # from 25 for reduced WARM up time
query_cache_limit=0  # from 525312 conserve RAM QC not being used

for additional suggestions, view profile, Network Profile for contact info, including Skype ID.


Update records in Postgres from a select query I am having 2 tables in PostgreSQL, have to fetch total records from both table . If count > 50 then update status=5 of oldest record. Here is query for oldest record. Have to update oldest record in single query. Here is query I have tried to find oldest record

 (select   'SPORTS' AS eventType,activity.articleId,activity.title,activity.articleText,
           activity.createdDate,activity.creatorscreenname as creatorScreenNameStr
  FROM     SPORTS activity
  where    activity.status=1
  and      activity.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   'MUSIC' AS musicType,activity.wikiTopicId as articleId,
           activity.title as title,activity.problemDescription as articleText,
           activity.createdDate,activity.creatorScreenName as creatorScreenNameStr
  FROM     MUSIC activity
  where    activity.status=1
  and      activity.createdBy=101
  order by createdDate ASC limit 1)
 ORDER BY 5 ASC limit 1

I've set up next example:

create table sports(id int, status int, createdBy int, createdDate date);
create table music(id int, status int, createdBy int, createdDate date);
insert into sports values (1, 1, 101, '20170101'), (2, 2, 101, '20170102'), (3, 1, 102, '20170103');
insert into music  values (1, 1, 101, '20170201'), (2, 2, 101, '20170202'), (3, 1, 102, '20170203');

with sel as
(
    select ttype, id, status, createdBy, createdDate
    from
        (
        (select 'sports' as ttype, id, status, createdBy, createdDate
        from   sports
        where  status = 1
        and    createdBy = 101
        order  by createdDate
        limit  1)
        union
        (select 'music' as ttype, id, status, createdBy, createdDate
        from   music
        where  status = 1
        and    createdBy = 101
        order  by createdDate
        limit  1)
        ) un
    order by 5
    limit 1
)
    , updsport as
    (
        update sports
        set    status = 5
        from   sel
        where  sports.id = sel.id
        and    sel.ttype = 'sports'
    )
    update music
    set    status = 5
    from   sel
    where  music.id = sel.id
    and    sel.ttype = 'music'
    ;

select * from sports order by id;

id | status | createdby | createddate
-: | -----: | --------: | :----------
 1 |      5 |       101 | 2017-01-01
 2 |      2 |       101 | 2017-01-02
 3 |      1 |       102 | 2017-01-03

select * from music order by id;

id | status | createdby | createddate
-: | -----: | --------: | :----------
 1 |      1 |       101 | 2017-02-01
 2 |      2 |       101 | 2017-02-02
 3 |      1 |       102 | 2017-02-03

Now try by changing createdDate on sports or music table just to change the filter criteria.

dbfiddle here


"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2 Some background: I am attempting to connect to an Oracle database. I have a username, password, IP address, port, and service name. I only have access to this database from an Ubuntu EC2 instance on AWS because the Oracle database is located inside another organization. Due to this, I do not have access to a DNS hostname for this machine.

I downloaded the "Oracle Instant Client with SQL Plus" client package from Oracle's website and extracted it to a folder. When I try to run it, I get this message:

  ORA-24454: client host name is not set

After two hours, I figured it out.

Oracle's documentation for the error didn't seem terribly descriptive at first:

  ORA-24454: client host name is not set

  Cause: The network host name in files like /etc/hosts was not set.

  Action: Set the host name and try again.

Now, the command worked on my machine (Ubuntu laptop), but it didn't work on EC2. I mucked around with the HOSTNAME variable for a bit and tried to figure out if there was a way to override the "client host name" in sqlplus's settings -- no dice. I finally compared my machine's /etc/hosts to the one in EC2, and noticed this line that wasn't in EC2:

127.0.1.1 my-laptop-hostname

I didn't know this before, but apparently it's normal on Debian-based machines to map the system's hostname to this IP address. I didn't even know that 127.0.1.1 was a loopback IP; I just knew about 127.0.0.1.

What's more, it seems sqlplus relies on this line being present. Still no idea why.

Anyway, the fix is simple:

$ sudo /bin/bash -c "echo '127.0.1.1 ${HOSTNAME}' >> /etc/hosts"


If the "list_of_jobs" is a known set of values, you can use UNPIVOT to do this, see this document

If it is an unknown set of values, you can use STRING_SPLIT in SQL Server 2016 or higher, see this document. NOTE: Your database compatibility level needs to be 130 or higher.

If you have SQL Server 2014 or earlier, or your compatibility level is below 130, you will need to write a user function to do the string splitting. Here's a good example.


Why is this very simple UPDATE so much slower than the SELECT it contains? In MariaDB 10.3.9:

SELECT COUNT(*) FROM photos

vs

UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos';

The select takes 1.1s vs the update which takes 2.6s.  3million rows in table photos, 3 rows in table counts.

ANALYZE format=JSON output:

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 1299.6,
    "table": {
      "table_name": "photos",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["photoId"],
      "r_loops": 1,
      "rows": 3178665,
      "r_rows": 3.25e6,
      "r_total_time_ms": 1085.7,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    }
  }
}

vs

{
  "query_block": {
    "select_id": 1,
    "r_total_time_ms": 2764.7,
    "table": {
      "update": 1,
      "table_name": "counts",
      "access_type": "range",
      "possible_keys": ["countName"],
      "key": "sCount",
      "key_length": "1023",
      "used_key_parts": ["countName"],
      "rows": 1,
      "r_rows": 1,
      "r_filtered": 100,
      "r_total_time_ms": 0.0756,
      "attached_condition": "counts.countName = 'photos'"
    },
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "r_loops": 1,
          "r_total_time_ms": 2764.6,
          "table": {
            "table_name": "photos",
            "access_type": "index",
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["photoId"],
            "r_loops": 1,
            "rows": 3178665,
            "r_rows": 3.25e6,
            "r_total_time_ms": 2541.8,
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          }
        }
      }
    ]
  }
}

Why is the subquery in the update query so slow?  I can easily fix this by doing a separate manual update with the result of the select query, but I'd like to know why this is slowing down so much.

On further examination, even THIS takes over twice as long as the simple select:

SET @foo = (SELECT COUNT(*) FROM photos);

What?  Why?!

Change to:

SELECT @ct := COUNT(*) FROM photos;
UPDATE counts SET countValue = @ct WHERE countName = 'photos';

Either have autocommit = ON, or at least don't put both statements in the same transaction.  This is to avoid locking counts while counting.

Also, countName should be the PRIMARY KEY.


How can an identity primary key index become fragmented? From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.

Example:

ALTER TABLE [dbo].[ClaimLineInstitutional] ADD  CONSTRAINT [PK_ClaimLineInsitutional]
PRIMARY KEY NONCLUSTERED
(
    [ClaimLineInstitutionalID] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,
FILLFACTOR = 100) ON [PRIMARY]

Update:

I am querying dm_db_index_physical_stats.avg_fragmentation_in_percent, so I believe it is physical fragmentation I am seeing.

Updates on data already there causes rows to be moved and forward pointers added

In this test, we can get 65% fragmentation on 115k densely packed rows

CREATE TABLE #FragTest (
    FragTestID int NOT NULL IDENTITY PRIMARY KEY,
    SomeString varchar(4100) NULL
    );

INSERT #FragTest (SomeString) VALUES ('a');
GO
INSERT #FragTest (SomeString)
    SELECT F1.SomeString FROM #FragTest F1 CROSS JOIN #FragTest F2;
GO 4
INSERT #FragTest (SomeString) SELECT F1.SomeString FROM #FragTest F1
GO 6

SELECT COUNT(*) FROM #FragTest

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('b', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 0

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('c', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 1

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('d', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 2

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

Edit, sorry, browser trouble

If you change the middle replicate to 2000 you get <5% at the end. This happens because there is free space from moved rows in the first update


How can I make this backup scheme more friendly for reporting? Currently, we have around 50 remote servers, each with a production database. Each night, all 50 of these servers run a backup job and zips the backup. Each morning, a local server runs a job for each site that copies the zip file over, and restores the database to a 'temp' server. The job then copies individual tables (not all tables are copied) to their corresponding database on a 'reporting' server. The database on the reporting server may contain custom tables to track information, so we can't simply drop the database every night.

I feel this process is somewhat redundant. I don't like having the same data in two places, and I don't like have to create scripts to copy individual tables for each remote server added (roughly 1 a month). However, I can't find a good way to do this and keep the custom tables we use.

One of my ideas was to turn the 'temp' server into a repository for the production database backups, and use the 'reporting' server solely for custom tables, but this leads to needing to use linked server joins.

Does anyone have an idea on how I can make this process better?

Setup SQL Server replication between the production servers and the reporting server so that the data on the reporting server is updated in real time automatically.


Resource Governor seems to increase resource usage We are in the unfortunate position of running a data warehouse on the same instance as our production OLTP system.  Recently a "power user" joined our company and has begun running SQL statements against the DW that are hogging resources.  Yesterday he ran a query that sent our page life expectancy from the usual 200K+ seconds down to 39.

I have thought of two solutions to this problem.  The first is to move the data warehouse to an Azure database.  This protects our OLTP systems from running out of resources, but it comes with a monetary cost.  The second solution is to use Resource Governor to limit the resources available for either specific users or the entire DW database.  This seems like a free solution, so it is the one that I am investigating first.  I have never used Resource Governor before, so I set up a few tests to make sure it would work as expected.  I first ran a few big queries with no Resource Governor set up and recorded the CPU usage, Reads, and Duration.  Then I created the proper Resource Governor settings and re-ran the queries.  The duration rose as expected, but I was surprised to also see the CPU and Reads increase drastically.  The CPU usage more than doubled and the Reads increased tenfold.  It did protect the Page Life Expectancy from dropping.  Is that a typical occurrence when using Resource Governor?  Does anyone have any other ideas for limiting specific users or databases from hogging resources?

I have three suggestions

Look into your Data Warehouse designs.  It should be optimized to let ad-hoc queries run relatively quickly. Analysis Services can help with this.
Off load DW queries into no busy hours. I find this works really well for companies I have worked at. One should not run "big" queries when most of your users are accessing the system. Instead, they should be saved at times when the server has a low load.
Check to see if the power user is writing proper queries. You can have highly optimized indexes, great architecture, and preprocessed cubes, but if the power user is writing solutions procedurally and not set-based, that will be a problem. Maybe he is brining in too much data yet only uses a small part. Who knows, but a poorly written query can very likely be the culprit.


What does k=v do in the context of INSERT INTO tbl VALUES (expr) Coming off this answer, I'm wondering why the lexer allowed it to get all the way down to the geometry grammar in this INSERT statement

CREATE TABLE foo ( x varchar(255) );
> INSERT INTO foo VALUES ( x='foo' );
Query OK, 1 row affected (0.01 sec)

I know that's not right, and as the answer above indicates that it should be INSERT INTO tbl SET, but what's happening? How is MySQL interpreting that? In PostgreSQL, such a syntax is rejected.

test=# CREATE TABLE foo ( x varchar(255) );
INSERT INTO foo VALUES ( x='foo' );

PostgreSQL interprets it as a column reference and errors out, further providing a hint

ERROR:  column "x" does not exist
LINE 1: INSERT INTO foo VALUES ( x='foo' );
HINT:  There is a column named "x" in table "foo", but it cannot be referenced
from this part of the query.

MySQL Seems to check to make sure the field name exists at run time but for what end I have no idea.

> INSERT INTO foo VALUES ( DOESNOTEXIST=5 );
ERROR 1054 (42S22): Unknown column 'DOESNOTEXIST' in 'field list'
> INSERT INTO foo VALUES ( x=5 );
Query OK, 1 row affected (0.01 sec)

If it does not exist you get an error. I'm not sure what happens if it does exist though. NULL is inserted, but why? Why that would be any less of an error. Can someone explain?

I know MySQL has a User-Defined Variable syntax, however I don't think that has anything to do with it here.

When you created the table with this command

CREATE TABLE foo ( x varchar(255) );

columnx was defined with an implicit default of NULL.

When you compare a non-existent variable with a real value as you did before you get NULL.

When it comes to the expression you gave, x=5, you are asking if NULL equals 5. In terms of symantics, NULL cannot by compared with anything:

mysql> select NULL = 5;
+----------+
| NULL = 5 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

not even with NULL itself

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

It needs a content context (if that makes any sense).

Anyway, when you tried to evaluate x=5, that gets processed internally as evaluating x, which of course, is NULL with the row coming into existence, followed by making a comparison with a non-NULL value of 5. Since the expression must evaluate as NULL, you essentially did this:

mysql> insert into foo values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql>

which has the same effect as as doing this

mysql> insert into foo values ();
Query OK, 1 row affected (0.01 sec)

mysql>


What is the performance impact of indexing a frequently updated column? I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer.

I have a table with potentially a very large amount of inserts and updates. Any time an update is made to a row, the 'modified' column gets changed. My application needs to query this table (along with some joins and filtering), and get paginated results sorted by that 'modified' column. Everything I've read tells me that I need to index the column that is used for sorting. But I've also read many articles telling me that indexing a column that gets updated frequently is a bad idea, because the index needs to be rebuilt every time the value of an indexed column changes.

So what is the proper way to go about doing this? If I index the 'modified' column, will this lead to problems later on when the index becomes very large and it needs to constantly rebuild itself over and over again? If I don't index the 'modified' column, will my pagination queries eventually become so slow that my application becomes unusable? Or is there another solution I haven't thought of?

-EDIT- I am leaning towards using an index on the column. But what I'm still not sure about is what affect a large number of UPDATEs to the indexed column will have on the SELECT queries that use that index. I know that indexes slow down UPDATEs and INSERTs, but I'm not sure if that increased I/O workload from rebuilding the index so many times (EVERY time a row gets updated, which there would probably be dozens of at any given moment) would be a major bottleneck to the server's performance in general. Or is it not as big of a bottleneck as I'm thinking?

Surely READ UNCOMMITED is a recipe for disaster? It's not called "Dirty Read" for nothing!

Everything I've ever read about Oracle says so. In particular, Tom Kyte's "Oracle Database Architecture" which flat out states that Oracle refuses to provide that level and that is a good thing!

I saw the MySQL tag on this post, but this is universally applicable to databases in general IMHO.

Of course indexes slow down INSERTs and UPDATEs. Also, naturally, they will speed up SELECTs.

You can't know in advance performance will be affected for either case except by extensive experience and/or testing. What to do about this is a business decision, not a technical one.

Your question cannot be reasonably answered without far more information. Your UPDATE queries? Your SELECT queries? Your transaction rates? Your disk setup? How are your iostat results? Your entire sar statistics? Is your system humming or do you experience bottlenecks/slowdowns?

How is your budget? How much of a delay in reporting can the business tolerate? My first instinct in your case is to go for an offline data-warehousing solution with a 24 hour lag (DW built overnight - using some form of replication).

This is from my experience of how OLAP reports can cripple a running OLTP system. However, I cannot say for sure without far more information.


SentryOne Plan Explorer properties window Does the SentryOne Plan Explorer have a properties window similar to SSMS. I see the tool tip window next to the operators in the execution plan but no properties option ?

If you right-click on the column header on any of the grids, there is an option called Column Chooser:

This exposes a panel that lists all the columns that are available in that grid. You can drag any of these onto any location in the grid. Here is an example from Top Operations, where both Predicate and Seek Predicates are available:

The really nice thing about the grids is that all operators are listed together. In SSMS, to compare some of the more obscure properties between two operators, you either have to flip between tooltips and/or properties panels, or take screen shots (ugh).

If you post your plan somewhere and describe which index seek you mean, I can show you how to make these properties front and center.


MySQL: Error 1114. table is full. can't drop the table I am trying to load a single 28GB csv file (257 million rows) into MySQL database for almost a week. However I got Error 1114 while loading after 4730 seconds (or more/less, different in every execution). I have tried to find the solution here and already adjusted the my.ini as following:

max_allowed_packet = 1024M
max_heap_table_size = 1024M
tmp_table_size = 1024M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_log_buffer_size = 1024M
innodb_log_file_size = 1024M
innodb_file_per_table = 1

But the error still occurred. The server logs just showed The table xxx is full. The second problem is that I cannot drop this corrupted, full table. The server connection broken everytime when I want to drop the table (Lost connection). I am wondering how can I modify my my.ini better to deal with this big csv file and drop the broken table?

My RAM is 4G and the harddrive still has enough space (162G, so I don't think this is problem). My session settings are: keep-alive interval / read time out / time out are all 99999 seconds.

wouldn't that whole file needed to be loaded into RAM first? I would recommend splitting it up into chunks not bigger that 3GB each before submitting them.

Also, this post suggests that "setting buffer pool a bit larger than your database size" was a good practice.

It looks to me that your whole set up is not quite up to the task.


How to test SQL Server Standard Basic Availability Group? I'm planning to set up a new SQL Server 2017 Standard Edition instance, with Basic Availability Groups. How can I test that in a dev or qa environment?

In my QA environment now, I have Developer edition installed. When I run the T-SQL to create a Basic Availability Group, I get the following error:

  'BASIC' failed because it is not supported in the edition of this SQL Server instance

I've learned that Developer and Evalualtion have same feature set as Enterprise, so looks like I can't use those? Do I need a fully licensed Standard edition in a dev/qa environment to test Basic Availability Groups?

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

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


expdp Hangs at DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM in oracle 12c Expdp Hangs at Below Stage for long time even in structure backup also .

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Database Version: -Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

so please let me know to get solution & if it is 12c Bug then please mention bug number also

P.S :-
here is expdp full script

 expdp username/password full=Y CONTENT=METADATA_ONLY directory=DIR1 dumpfile=structure_backup.dmp logfile=structure_backup.log KEEP_MASTER=Y

Database Size :- 10 GB

So in the end it's just a time issue you have. You would have to open up a ticket with Oracle if you want to speed things up. This is probably not an Oracle issue, but possibly an issue with your database design.

You have to understand that the Data Pump Export Utility has to determine all relevant constraints and relations before it can create a valid structured metadata export. This can take a substantial amount of time and depends on the structure of your database, the amount of schemas and other limiting constraints.

The more complex the metadata is, the longer it can take to export.

You might want to look at the object after the .../POST_SYSTEM_ACTIONS/PROCACT_SYSTEM as that could actually be the limiting factor.

You might want to have a look at the official documentation regarding Database Utilities | Data Pump Performance | Tuning Performance (Oracle 12c Documentation) for additional ideas on how to speed up things.


How to Handle Adding 2nd Node to single MariaDB Galera "cluster" already in production? I currently have a a single instance MariaDB 10.2.6 Galera instance sitting in production. For reasons, I botched the upgrade from 5.5.56 to 10.2.6 on a previously 4-node cluster.

Is there a way to minimize the downtime that will occur when adding a new MariaDB Galera node to this new cluster?

Could I mysqldump the data and have it rsync the rest of the data?

The grastate.dat on the primary node is as follows

# GALERA saved state
version: 2.1
uuid:    a75b7820-2b7c-11e3-9356-763f33730b6c
seqno:   -1
safe_to_bootstrap: 1

Or am I out of luck in terms of minimizing downtime?

The best way would be to use the xtrabackup-v2 SST method.  It is non-blocking for most of the transfer; there is a short blocking period at the end of the transfer when it has to get the latest data from the donor, but that is usually only a few seconds at most.  The other SST methods would need the donor to be offline for a much longer period since they aren't designed for online transfers.


How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements? I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating at a point of time?

This would depend on the Storage Engine.

MyISAM : Table-level locks are performed.  Consequently, all rows are locked.
InnoDB : Row-level locks are employed. Since InnoDB is transactional, all rows are locked implcitly. Those locks would be visible when you run SHOW ENGINE INNODB STATUS\G. If you want to manually lock those rows beforehand to prevent sharing, you could run SELECT ... FOR UPDATE.


SSDT Drop and Recreate Tables when nothing has changed We have a Visual Studio Database Project consisting of about 129 Tables. It is the primary database for our Internal Web Based CRM/Call Centre product, which is still under active development.

We use the SSDT Publish from within VS to deploy to instances as changes are made. We develop locally via SQL Express (2016), also have a LAB environment for performance and load tests running SQL 2014, a UAT environment running 2012 and finally a deploy to production which is running SQL 2016.

All environments (Except Production) the script generated on publish is very good, only does the changes. The production script does a massive amount more work. Seems to drop and recreate a lot more tables, that I know have not changed (37 tables last deploy). Some of these tables have rows in the millions, and the whole publish is taking upwards of 25mins.

If I repeat the publish to production, it again drops and recreates 37 tables. The production DB does have replication which I have to disable before deployments (unsure if that's a factor).

I don't understand what the Production publish always wants to drop and recreate tables even though nothing has changed. I'm hoping to get some advice as to where to look to establish why SSDT thinks these need to be re-created.

using Visual Studio Professional 2017 V 15.5.5 and SSDT 15.1

After a fair amount of frustration, finally found the cause, and posting as an answer as it may help others....

Finding the reason for why SSDT thought the scheme was different I think is the first point of call. SQL Scheme compare is your friend here. (In VS under Tools => SQL => New Scheme Comparison...)

Firstly thanks to @jadarnel27, was very helpful so upvote dude cheers, but wasn't the answer I'm afraid.
Computed column definitions and constraint definitions were definitely candidates, as was potentially column ordering. Ironically I did have one issue with a computed column but it was not the definition, it was the fact that I didn't add NOT NULL to the end of the column definition (which is the default of course) but SSDT saw that as different everytime.

So I had to change a column
from

[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED

to

[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED NOT NULL

then it stopped constantly dropping and re-creating the column. This showed up in Scheme Compare, not in the definition of the computed column.

Secondly, because we were using replication, SQL was adding NOT FOR REPLICATION to most of the tables that were being used in replication (reasons for is to do with the works of replication) but in essence was changing a tables DDL
from

CREATE TABLE [dbo].[Whatever]
(
    [WhateverId]    INT IDENTITY(1,1) NOT NULL
    ...etc more columns
)

to

CREATE TABLE [dbo].[Whatever]
(
    [WhateverId]    INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
    ...etc more columns
)

again this showed up on Scheme compare......

and to fix, either add NOT FOR REPLICATION into the DDL source code in VS for all tables required or under the publish settings -> Advance -> Ignore tab and scroll down a bit, tick the box as below:

and then everything was disco


Is reconfiguration required when switching sql server machine to use a FQDN? I'm going to be changing the Primary DNS Suffix (not the NETBIOS name) of a Windows 2008R2 Server that does not belong to a windows domain (eg. a WORKGROUP computer) in order to give it a FQDN.

Is it necessary to reconfigure SQL Server in any way after doing so? I'm thinking specifically of the steps that are required when changing the NETBIOS name of the computer located here: http://technet.microsoft.com/en-us/library/ms143799.aspx

No, changing the dns suffix of the server should not necessitate any change at the SQL Server.

The age-old mantra of test, test, test, certainly applies if you are making a change to a production machine since there may be repercussions at the client end depending on how your clients address the machine.


Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries ¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot.

This tables can be

Table A: Employees

-------------------------
| Name   | ID | Account |
-------------------------
| Nicole | 01 | 12345   |
| Alexis | 02 | 67890   |
-------------------------

And Table B: BankAccounts

--------------------------
| Name   | ID  | Account |
--------------------------
| Nicole | 01  | 12345   |
| Nicole | 01  | 67890   | //duplicates Accounts
| Alexis | 02  | 67890   | //duplicates Accounts
--------------------------

And i want to do this with a Left Join in a Table that can have more of 450,000 different entries

Result Table C

Column_A = ¿Exists the account number in other register?
Column_B = if(NumberOfMatches > 1) //this means that the account be found
in other user AND i want to get the first value of all posibles number of matches

                         |Account exists in other user|Match in User..
-----------------------------------------------------------------------------
| Name   | ID  | Account | Column_A | NumberOfMatches | Column_B    | BadID |
--------------------------------------------------------------------|-------|
| Nicole | 01  | 12345   | No       |       1         | Nicole (OK) | null  |
| Alexis | 02  | 67890   | Yes      |       2         | Nicole (BAD)|   01  |
-----------------------------------------------------------------------------

Thanks and regards!

Note: sorry for my english, im learning :p

SELECT DISTINCT
       CASE WHEN t2.ID IS NULL
            THEN t1.Name
            ELSE t2.Name
            END Name,
       CASE WHEN t2.ID IS NULL
            THEN t1.ID
            ELSE t2.ID
            END ID,
       CASE WHEN t2.ID IS NULL
            THEN t1.Account
            ELSE t2.Account
            END Account,
       CASE WHEN t2.ID IS NULL
            THEN 'No'
            ELSE 'Yes'
            END Column_A,
       ( SELECT COUNT(t4.Account)
         FROM BankAccounts t4
         WHERE t1.Account = t4.Account ) NumberOfMatches,
       CASE WHEN t2.ID IS NULL
            THEN CONCAT(t1.Name, ' (OK)')
            ELSE CONCAT(t1.Name, ' (BAD)')
            END Column_B,
       CASE WHEN t2.ID IS NULL
            THEN NULL
            ELSE t1.ID
            END BadID
FROM BankAccounts t1
LEFT JOIN BankAccounts t2 ON t1.Account = t2.Account
                         AND t1.ID < t2.ID
LEFT JOIN BankAccounts t3 ON t1.Account = t3.Account
                         AND t1.ID > t3.ID
WHERE t3.ID IS NULL
ORDER BY Account, ID;

fiddle


What are the MariaDB/MySQL servers types, and how do they differ?, e.g., 'standalone' vs 'embedded', vs ...? When configuring a MySQL or MariaDB server, the terms 'standalone' and 'embedded' are referred to, but without reference to what they are, or what they do that is different from each other.

What does the term 'embedded' refer to?  Is this like on an IP camera or something?
Also are these the only the two types of MariaDB/MySQL servers?  Or are there other similar servers, like for example are 'galera', or enterprise other types of servers?
What is the difference between these two or other server types?  In other words, what are they used for that is different, or how are they installed that are different, etc.

Note: When installing, these groups seem to apply:

[server]     apply to both mysqld and embedded   (note: 'mysqld' = 'standalone')
[mysqld]     apply to only mysqld
[embedded]   apply to only            embedded

From context: in Debian desktop, I think I'm running a 'standalone' server only.

"embedded" is for applications that link to a server library and run a database internal to their application. They don't have a connection and all their SQL is via library calls. These aren't that common.

"standalone" seems most appropriate for your use.

embedded and "networked (including sockets)" are the main two server types. Withing networked there are MySQL cluster and MariaDB's galera as special kinds of networked configurations.


Co working Space DB architecture I am trying to build a platform to share the empty desks in an office with someone outside the premises to utilize the space and generate some revenue. I am thinking of three major roles:

Member who will book a desk/seat(resources) for any period of time be it hours/days/weeks/months.
Member who will list empty spaces in his/her office.
Admin who will verify the listed spaces. Only verified spaces can be booked and other Admin related tasks such as managing users/resources etc.

I am trying to build this on MongoDB and want to focus on scalability.

Mongo

Mongo is a document store. It's not a great solution to model this in a document store. You need people to list days and times desks are available. I would want to use something that could model timestamp ranges, and with good support for transactions. I would use something like PostgreSQL.


Index over multiple fields I have a table with ~40 million entries and I have an index for most of my fields. All my queries are optimized, except for one, which gets called a couple times per second and uses most of the fields. Now I'm investigating on how to optimize this query, do I:

create an index over all fields?
create an index over all fields and remove the individual indexes?

If I have an index over all fields, would I even need the individual indexes?

This is the query:

SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.speedtest_id = 1
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

Indexes

With a multi-field index, you need to make sure each field in the index is used from left to right in the WHERE clause, otherwise the index will not be applicable.
If we assume that your index speedtest_id_2 is the only index you have (except for the primary key), then the following query will use the index

SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.speedtest_id = 1
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

Whereas the following query will not use that index.

SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

as the speedtest_id field which is first in the index is not part of the WHERE clause.

With your combined index, I would add keystrokes to the index before the created field, as that will help reduce the data being read through when calculating the output. Also use the EXPLAIN command to view the query plan that MySQL is going to use when running the query.

EXPLAIN SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.speedtest_id = 1
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

You may find that rearranging the fields in the multi-field index will change the query plan, and thereby the performance of the query.

Your speedtest_id single field index is not relevant, as the combined index has that field as the first field, so will be used even if you only filter on that one field.

Whether you leave the other individual field indexes in or remove them is entirely down to how your application works, and what other queries are run against it - you may find that they are all used at some point or another when your application runs.

You can leave your application running for a while, and then examine the following tables (if you have the sys schema installed) to see how the indexes are being used.

select * from sys.schema_index_statistics where object_schema = '{database}';
select * from sys.schema_unused_indexes where object_schema = '{database}';

This will give you an idea of which indexes are not used by the application.


Segregating email ids of each customer (if more than one) by creating the new lines/records for that customer My customer Database contains more than one email id in “Email” Field due to which my emails getting bounced. I want to have only one email id in “Email” Field but at the same time, I don’t want to lose any other email id also.

I want that if there are more than one email id in “Email” field then new lines (equivalent to total no. of email ids) to be created for that customer with one email id only in “Email” field and data in the rest of the fields should be copied as it is for that particular customer.

Following is the script.

CREATE TABLE mytable (
    NAME VARCHAR(26) NOT NULL PRIMARY KEY
    ,City VARCHAR(10) NOT NULL
    ,EMail VARCHAR(79) NOT NULL
    ,Contact VARCHAR(33) NOT NULL
    );

INSERT INTO mytable
(NAME, City, EMail, Contact)
VALUES
('EFG', 'Chennai', 'eminent5sales@gmail.com;eminentyusuf@gmail.com', 'Mr. Y'),
('ABC', 'AHMEDABAD', 'priyalcorporation@gmail.com', 'Mr. N'),
('XYZ', 'Ahemadabad', 'Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com', 'Mr. J');

Expected Output

+------+------------+------------------------------------------------+---------+
| Name |    City    |                     E-Mail                     | Contact |
+------+------------+------------------------------------------------+---------+
| EFG  | Chennai    | eminent5sales@gmail.com;eminentyusuf@gmail.com | Mr. Y   |
| EFG  | Chennai    | eminentyusuf@gmail.com                         | Mr. Y   |
| ABC  | AHMEDABAD  | priyalcorporation@gmail.com                    | Mr. N   |
| XYZ  | Ahemadabad | Dhruv_Patel@milacron.co                        | Mr. J   |
| XYZ  | Ahemadabad | mayur_a_patel@milacron.com                     | Mr. J   |
| XYZ  | Ahemadabad | Bharat_R_Parekh@milacron.com                   | Mr. J   |

CREATE TABLE mytable (
    NAME VARCHAR(26) NOT NULL PRIMARY KEY
    ,City VARCHAR(10) NOT NULL
    ,EMail VARCHAR(79) NOT NULL
    ,Contact VARCHAR(33) NOT NULL
    );

GO

INSERT INTO mytable
(NAME, City, EMail, Contact)
VALUES
('EFG', 'Chennai', 'eminent5sales@gmail.com;eminentyusuf@gmail.com', 'Mr. Y'),
('ABC', 'AHMEDABAD', 'priyalcorporation@gmail.com', 'Mr. N'),
('XYZ', 'Ahemadabad', 'Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com', 'Mr. J');
GO

select * from mytable;
GO

NAME | City       | EMail                                                                           | Contact
:--- | :--------- | :------------------------------------------------------------------------------ | :------
ABC  | AHMEDABAD  | priyalcorporation@gmail.com                                                     | Mr. N
EFG  | Chennai    | eminent5sales@gmail.com;eminentyusuf@gmail.com                                  | Mr. Y
XYZ  | Ahemadabad | Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com | Mr. J

 SELECT A.[NAME],A.[City],
     Split.a.value('.', 'VARCHAR(100)') AS [Email],[Contact]
 FROM  (SELECT [NAME],[City],
         CAST ('<M>' + REPLACE([Email], ';', '</M><M>') + '</M>' AS XML) AS [Email],[Contact]
     FROM  mytable) AS A CROSS APPLY Email.nodes ('/M') AS Split(a);
GO

NAME | City       | Email                        | Contact
:--- | :--------- | :--------------------------- | :------
ABC  | AHMEDABAD  | priyalcorporation@gmail.com  | Mr. N
EFG  | Chennai    | eminent5sales@gmail.com      | Mr. Y
EFG  | Chennai    | eminentyusuf@gmail.com       | Mr. Y
XYZ  | Ahemadabad | Dhruv_Patel@milacron.co      | Mr. J
XYZ  | Ahemadabad | mayur_a_patel@milacron.com   | Mr. J
XYZ  | Ahemadabad | Bharat_R_Parekh@milacron.com | Mr. J

dbfiddle here


On which fields to index? Fields queried (WHERE) or fields returned (SELECT) I have a large MySQL database table (~1 million rows and growing) on an AWS RDS Medium instance:

mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(500)  | YES  |     | NULL    |                |
| address         | varchar(500)  | YES  |     | NULL    |                |
| city            | varchar(200)  | YES  |     | NULL    |                |
| state           | varchar(100)  | YES  |     | NULL    |                |
| zip             | varchar(50)   | YES  |     | NULL    |                |
| country         | varchar(50)   | YES  |     | NULL    |                |
| phone           | varchar(20)   | YES  | UNI | NULL    |                |
| source          | varchar(20)   | YES  | MUL | NULL    |                |
| campaign        | varchar(200)  | YES  |     | NULL    |                |
| search_term     | varchar(200)  | YES  |     | NULL    |                |
| search_location | varchar(200)  | YES  |     | NULL    |                |
| added           | datetime      | YES  |     | NULL    |                |
| email           | varchar(150)  | YES  |     | NULL    |                |
| website         | varchar(150)  | YES  |     | NULL    |                |
| full_output     | varchar(5000) | YES  |     | NULL    |                |
| client          | varchar(50)   | YES  |     | NULL    |                |
| is_deleted      | int(2)        | YES  |     | 0       |                |
| is_valid        | int(2)        | YES  |     | 1       |                |
+-----------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

I often need to perform a variant of the following query:

SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;

And the relevant EXPLAIN:

mysql> EXPLAIN SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | clients    | range | source        | source | 63      | NULL | 1168144 | Using where |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.03 sec)

What optimizations should I be making? Should I add indexes on the name and zip fields, or on the added and source fields?

The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index.

A single index over both columns in the WHERE clause:

ALTER TABLE clients ADD KEY(source,added) -- adding this
ALTER TABLE clients ADD KEY(added,source) -- or this

...will usually help you more than an individual index on each column, because the optimizer may otherwise only select to use one of the two columns.  Which of the above indexes will help more depends on the distribution of values in "source" and "added."  The selected index used for the query will appear in "key" in the EXPLAIN output.  "Using where" usually means that, out of the rows the chosen query plan will result in fetching, the server realizes that some number of them will still not meet the selection criteria and will have to be subsequently filtered by the server (as in the example, where potentially a large number would have to be filtered, since no index was used).

A covering index could be particularly valuable also, because contrary to the assertion that "the fields within a row are quick and easy for the engine to get," they are only quicker and easier than finding the rows by scanning the entire table -- they still take time, and consume resources.

This is where covering indexes come in.  Adding an index with (source,added,zip,name) would likely improve your performance substantially, because once the server has found the relevant rows by using the index, it does not need to look up the rest of the data because the data is actually inside the index.  When a covering index is being used, the "key" column of explain will contain the name of the index being used, and the "Extra" column will include "using index" (meaning, using index to actually retrieve the data, not just find it.)

So while it is true that you index on your selection criteria, that is not the entire story.

Note also that no matter what is indexed, an index will only be used for actual searching starting with the leftmost column in the index, until a column is encountered that is not in the WHERE clause.

Thus, a index on (source,added) can optimize the finding of rows for a query with both "source" and "added" in the WHERE clause... or with just "source" in the WHERE clause, but this index will not be used for lookups with only "added" in the where clause, because there's a column to the left of that, that isn't being used.  Similarly, a single index on (source,added,zip,name) could optimize lookup for queries with WHERE clauses mentioning source ... or source and added ... or source and added and zip ... or source and added and zip and name... but not just "zip" ... not just "name" ... not "added" and "name" and "zip"... you get the idea.  An index is irrelevant beginning at, and to the right of, any column not referenced in WHERE when it comes to optimizing the lookup of rows.

Note that the order in which you list things in the where clause makes no difference, as long as all of the conditions are AND.  That's a misconception you'll find online.  Any equivalent expression is understood as equivalent by the optimizer.

Also, unless you explicitly disabled it, IN('Foo','foo') is redundant because the selection would be case-insensitive thanks to collations, so 'foo' should be sufficient to find any permutation of capitalization.


How to do two actions in conditional UPDATE? In a query  as

SET @x = 1

UPDATE table1 SET
col2 = IF(col1 = 'y', @x:=0, @x:=@x+1)

Every time reaching the value of y in col1, I want to SET the @x:=@x+1 and after that reset the x to 0.

In other words, I want to count the rows before the occurrence of rows ending to col1 = 'y'.

EXAMPLE

I want to UPDATE col2 as follows. In the above-mentioned query, rows for col2 is simply 0 for all col1 = 'y'.

id       col1       col2
1       d          1
2       s          2
3       w          3
4       y          4
5       s          1
6       c          2
7       y          3
8       s          1
9       y          2
10       s          1

If mysql had window functions, it would be quite easy. However, it doesn't, and even though there are quite a few solutions that use variables, most of them (at least the ones I read) warn about potential problems due to the changing order of evaluation when it comes to emulation PARTITION BY part.

My solution doesn't use variables at all (I know, performance is gonna be terrible for big amount of data, but it seems to have no side effects). I hope it can be somehow useful :

I split the task into 2 parts , 1st - creating select statement that returns required rank/rownumber, and 2nd - update which is trivial (simple update ... join) .

(Example in http://sqlfiddle.com/#!2/5c3926/1, but I post it here as well )
Table :

 create table table1(table1_id int auto_increment not null primary key,
  col1 char(1) not null,col2 int );
insert into table1(col1)
values ('d'), ('a'), ('b'), ('y'), ('x')
, ('a'), ('g'), ('y'), ('d'), ('d'), ('d')
, ('t'),('b'), ('y'),('y');

SELECT which returns desired value (hopefully) in "rank_column":

select a.*,
  (
  select count(1) from table1 c
WHERE c.table1_id >
    IFNULL
    (
      (
       select MAX(table1_id) as next_id
       from table1 b where col1='Y' and b.table1_id <a.table1_id
      )
    ,0)

   and c.table1_id <= a.table1_id
  )
as rank_column
 from table1 a

Now update itself which just repeats previous select :

  UPDATE table1 curr_val
  INNER JOIN
  (
  select a.table1_id,
  (
  select count(1) from table1 c
WHERE c.table1_id >
    IFNULL
    (
      (
       select MAX(table1_id) as next_id
       from table1 b where col1='Y' and b.table1_id <a.table1_id
      )
    ,0)

   and c.table1_id <= a.table1_id
  )as rank_column

 from table1 a
)new_val ON (curr_val.table1_id = new_val.table1_id)
SET curr_val.col2 = new_val.rank_column


PostgreSQL: Remove password requirement for user postgres I understand that, upon installation, PostgreSQL has no password for its db root user (postgres):

postgres=# select usename, passwd is null from pg_shadow;
 usename  | ?column?
----------+----------
 postgres | t
(1 row)

... and one is advised to set it with:

alter role postgres password '<<very-secret>>';

(and then update the pg_hba.conf file accordingly)

My question is: what is the SQL to use to revert back to the previous situation when no password was needed for user postgres.

In general, how can I remove the password requirement for any role? I am not asking how to change the password but rather how to remove the password requirement (null passwd column in table pg_shadow).

Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird.

pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same username in the operating system as in PostgreSQL, use peer (UNIX, only for local connections) or sspi (Windows).

If there's a password set, but pg_hba.conf doesn't tell PostgreSQL to ask for it, the password is ignored.

If pg_hba.conf tells PostgreSQL to ask for a password but there's none set, then all login attempts will fail no matter what password is supplied.