should I change mysql platform for higher innodb_buffer_pool_size? Currently Im running a 160G MySQL database on Xampp (Windows). But Xampp has upper limit for innodb buffer pool size of 4G. My computer has 16G RAM. So if I want to make full use of resources, should I move database from Xampp to Workbench? Then I could set maybe 10G buffer.

So, here is my plan:

use mysqldump to create a backup file.
uninstall Xampp totally (otherwise MySQL will install in Xampp directory again, I tried before)
install new MySQL and workbench.
recover the dump file into new platform
set innodb buffer pool size to >10G.

Is this practical?

With a 160 GB database, it does not sound like a 10 GB cache is overkill, but it depends to some extent on the size of the active data as well.

I suggest that you look at Rolando's post:
How large should be mysql innodb_buffer_pool_size?

In the particular case he discusses, there was too much memory allocated for the cache. However, he offers considerable detail on what to measure and how to refine the size of the cache.

Some code snippets from Rolando's post:

Compute the Cache Size:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

After you reset the buffer size and restart the server, give it some time, then run this to see how usage is holding up:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;


Running MySQL Router with Fabric I've setup replication and failover with MySQL Fabric, and everything it's working fine; now I need to give access to the personal that access the database, I'm trying to use MySQL Router, but in every documentation and manual I've found on internet so far, I need to start MySQL router with

  mysqlrouter --config=/path/to/file/my_router.ini

So my server prompts for the password of the admin user of Fabric; when I do that, it starts fine; but I can't figure out how to start it and leave it running in background, so I can disconnect the session and Router keeps running. I found in the Changelog of MySQL Router (https://dev.mysql.com/doc/relnotes/mysql-router/en/mysql-router-news-2-0-2.html):

  The configuration file no longer accepts a password for the Fabric Cache plugin. Instead, MySQL Router prompts for the password at startup.

So I don't know if there is no way to keep it running... Has anyone managed to do this? Or is there another way to connect in a transparent form via MySQL Yog or DBeaver to MySQL Fabric?

Thanks

I would strongly encourage you to try out InnoDB clusters if you haven’t yet:

http://mysqlserverteam.com/mysql-innodb-cluster-new-5-7-17-preview-release-on-labs-mysql-com/
http://mysqlserverteam.com/mysql-innodb-cluster-a-hands-on-tutorial/

That being said, this is how I had previously setup MySQL Router 2.0 to run as a daemon on my test machines–which are systemd (EL7) based–so that you have a working reference implementation. In this test setup, the username:password combination for Fabric was admin:admin

[root@hanode1 ~]# cat /etc/mysqlrouter/mysqlrouter.ini
#
# MySQL Router configuration file
#
# Documentation is available at
# http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]
logging_folder = /var/log/mysqlrouter/
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[fabric_cache:lab]
address = 192.168.1.91
user = admin

# Routing into my Fabric managed cluster
# route to the current READ_WRITE / PRIMARY node
[routing:lab]
bind_address = 127.0.0.1:6600
destinations = fabric+cache://lab/group/hagroup
mode = read-write

# Routing into my Group Replication cluster
# route to the first available node
[routing:grouprepl]
bind_address = 127.0.0.1:7700
destinations = 192.168.1.92:3306,192.168.1.93:3306,192.168.1.94:3306
mode = read-write

[logger]
level = debug

[root@hanode1 ~]# cat /usr/lib/systemd/system/mysqlrouter.service
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
After=mysqlfabric.service

[Service]
Type=simple
User=mysql
Group=mysql

PIDFile=/var/run/mysqlrouter/mysqlrouter.pid

ExecStart=/bin/bash -c "echo 'admin' | mysqlrouter --config=/etc/mysqlrouter/mysqlrouter.ini"

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=true

[Install]
WantedBy=multi-user.target

[root@hanode1 ~]#

Best Regards,

Matt


What is the exact difference between Oracle (+) and comma separate tables and join tables What is the exact difference between Oracle (+) and comma separate tables and join tables.
Somebody force me to use oracle (+) instead of left join. Is their any performance issue ? Because as i aware of these notions they are identical.

Write both queries and run EXPLAIN PLAN on both of them. Comparing the plans will identify any performance differences.

From a development perspective, explicit joins offer several advantages over the older syntax.

By keeping the ON clauses together with the tables they reference, it's easier to see the join itself.

Separating out the ON clauses from the WHERE predicates make it easier to understand the developer's intention. Sometimes a spaghetti collection of tables might only have one WHERE predicate after all the join predicates are removed.

It's much harder to forget a predicate with the explicit syntax. If you forget a join predicate, you're going to return a cartesian product. Two tables with a million rows each will form a trillion-row cartesian product. If you know you've forgotten a join condition because your result set is suspicious, it's much easier to identify what's missing with the explicit syntax.

I will argue, against the advice of some longbeards, that even a large number of tables is easier to manage with explicit joins and that no query favors the implicit syntax for readability. Again, this is because the join predicates are kept next to their referenced tables. If you're limiting yourself to 80 columns of text, a long query could mean a lot of flipping up and down with the implicit syntax. If you think the typing is excessive, you should solve this with your text editor and not with sloppy syntax. (Multiline editing in Sublime Text is one solution, for example.)


Data Compression Enhancement in SQL Server 2012 Is there any specific enhancements in SQL Server 2012 for Data Compression Feature?

Last time I checked SQL Server 2008 R2 introduced support for Unicode characters so columns with NCHAR data types (excepts CLOBS and BLOBS) can now take advantage of compression technology in SQL Server.

There were under-the-covers performance enhancements (nothing concrete, see for example this book excerpt. Otherwise, no, 2008 R2 compression and 2012 compression are functionally equivalent.


Date and Time in Log file I've the translated Sybase ASA 9 log file, and the log looks like

--UPDATE-1120-05402698411
UPDATE DBA.titulo_receber
   SET dt_pagto='2014/apr/28 11:06:08.593',
       id_situacao='BA',
       vl_pago=90,
       cd_empresa_baixa=5,
       cd_usuario_baixa=197,
       nr_recebimento=5015693,
       dt_manutencao='2014/apr/28 11:08:22.949895'
 WHERE cd_empresa=3
   AND nr_titulo=20154
   AND nr_parcela=5
   AND serie='CF05'
go
--INSERT-1120-05402698481
INSERT INTO DBA.mensagem(nr_mensagem,dt_mensagem,hora,cd_usuario_remetente,
      cd_usuario_destinatario,id_situacao,assunto,texto,id_confirmacao,
      dt_recebido,nr_mensagem_resposta,dt_mensagem_enviada,dt_ult_alteracao)
VALUES (50019297,'2014/apr/28 00:00','11:07:36.0',197,59,'N','TITULO BAIXADO','Empresa Venda: Loja3\x0D\x0AEmpresa Baixa: Loja5\x0D\x0ACliente: 3340-EZIONEI CONSTANTINO (Cliente da Loja3)\x0D\x0AT�tulo(s): 20154/05  R$ 90,00\x0D\x0ATOTAL PAGO: R$ 90,00','N',NULL,NULL,'2014/apr/28 11:06:08.593','2014/apr/28 11:08:22.952065')
go
--COMMIT-1120-05402701966
COMMIT WORK
go

I need to know the date and time in the "--UPDATE" / "--INSERT" and "--COMMIT" were executed in the db.

I can't find in the Sybase's help file nor in my search in Internet.

Kind Regards,
William Bertan

I'm afraid that information is not available. The transaction log file doesn't store information to that level of detail for most statements. You can get the date and time for connection attempts and checkpoints, but not for DDL statements.

Disclaimer: I work for SAP in SQL Anywhere engineering.


Avoiding null comparisons in where clause It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given.

If that statement is true, what is a better way to write the following?

SELECT * FROM dbo.DpdRoute
    WHERE DestinationCountry =@COUNTRY
    AND (  (BeginPostCode IS NULL      AND ENDPOSTCODE IS NULL)
        OR (BeginPostCode  = @POSTCODE AND ENDPOSTCODE IS NULL)
        OR (BeginPostCode IS NULL      AND ENDPOSTCODE  = @POSTCODE)
        OR (BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE))

I'm looking for a list of routes, for a given country where a postcode is either within a range of postcodes, equal to the min or max post code, or the min or max post codes are both null.

How does one "not compare to null" when the comparison is specifically about "nulls"?

If you have an index on DestinationCountry,BeginPostCode,ENDPOSTCODE then your original query seeks into the DestinationCountry part but no further.

You may find better performance rewriting as an UNION ALL if you have that index.

The below can seek into non overlapping parts of this index.

An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) =  (@COUNTRY,NULL, NULL)
An exact seek on (DestinationCountry, BeginPostCode, ENDPOSTCODE) =  (@COUNTRY,NULL,  @POSTCODE)
An equality seek on (DestinationCountry) =  (@COUNTRY) and then a range seek on the secondary key BeginPostCode <= @POSTCODE with a residual predicate on all rows that match.

__

WITH Country
     AS (SELECT *
         FROM   dbo.DpdRoute
         WHERE  DestinationCountry = @COUNTRY)
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE IS NULL
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode IS NULL
       AND ENDPOSTCODE = @POSTCODE
UNION ALL
SELECT *
FROM   Country
WHERE  BeginPostCode <= @POSTCODE
       AND ( ENDPOSTCODE >= @POSTCODE
              OR ( BeginPostCode = @POSTCODE
                   AND ENDPOSTCODE IS NULL ) );


Weird WHERE Clause Behavior. Why does this return a row? In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example?

WITH SRC AS (SELECT cast('12345' as varchar) DEMO)
SELECT * FROM SRC WHERE DEMO='12345  '

What if one needs to query to find '1234 ' but not '1234'

What is happening is that SQL pads spaces to the end of strings so that they are of the same length. So if you try do something like:

SELECT 1 WHERE '' = ' '

You will actually get 1 back even though they are not the same. However, if you do something like:

SELECT 1 WHERE 'a' = ' a'

It will not return anything as it would be comparing 'a ' to ' a' which do not match.

However, this would return 1:

SELECT 1 WHERE 'a' = 'a '

As it is comparing 'a ' to 'a '


Database design - groups chat - seen messages I have an application in which users form groups and chat together. One of the features is the well known "this message was seen at {some date here} by user {some user}". My concern is whether I've designed this correctly as with my current implementation database grows really fast in size.

My approach is really simple. Once a user sends a message to a chat group, assuming there are N memebers in that group, N - 1 rows are created in chatMessagesSeen table.

Columns of that table are id, messageId, userId, groupId, date and seen. Last column is a flag used to store whether the message was seen. So that's 4*4+8+1 = 25 bytes per entry. Pretty large chat groups are common, so 100 members in one room is nothing exceptional.

I'd very much appreciate if anyone could share experience with a similiar problem, what should I take into consideration and whether there are any better ways to do this (sure they are, right). I wonder how giant social sites with this feature handle the problem.

An alternative would be to not add those rows right when the message is created, but add a row whenever a user actually reads the message. Then you can omit the seen flag – the existence of the row is enough to know that the user saw the message.

That has two advantages:

fewer table rows
no extra work when a user joins a group

With your design, adding a user to a group requires adding a row for every message written by someone in the group.


ON creating BITMAP INDEX on a table in mysql throws error I have database named "demo_dev" and added a table named "countries".

I wanted to create a bitmap index on a column, say, region as it has fewer unique values, using following query.

 CREATE BITMAP INDEX ON countries (region);

It threw an error as shown below:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BITMAP INDEX ON countries (region)'

What shoud be done for it to run successfully?

I just refered the following link : http://dev.mysql.com/worklog/task/?id=1524

mysql version : 5.5

You are looking at a feature request. See here for this table

Storage Engine  Permissible Index Types
InnoDB  BTREE
MyISAM  BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE (see note in text)

They are not even available in 5.7 - they would be a nice to have, but not yet - maybe in the future. Check this out - pages 32 and 33 - not even the columnar stores (InfiniDB and Inforbright) have them yet - AFAICS.


How to get the Sum of all column values in the last row of a resultset without using UNION? I need to get the sum of all column values of a result set in the last row.
Here is my SQL query.

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        'Total' AS COST_CARRIER,
        'Total' AS COST_CARRIER_CD,
        SUM(CS_WG_CST) AS WAGE_COST,
        SUM(CS_PART_CST) AS MATERIAL_COST,
        SUM(CS_DH_CST) AS DH_SUBLET_COST,
        SUM(CS_TOTAL_CST) AS TOTAL_COST

FROM    ASPECT.WR_CD_CC_RPT_SUMM
WHERE
        CS_CNTRY_CD = '81930' AND
        CS_YEAR = 2016 AND
        CS_MNTH = 1 AND
        CS_WK = 2 AND
        CS_MFC_CD = 'CBU'
GROUP BY CS_YEAR, CS_MNTH, CS_WK

UNION ALL

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        CS_CC_KIND AS COST_CARRIER,
        CS_CC_CD AS COST_CARRIER_CD,
        CS_WG_CST AS WAGE_COST,
        CS_PART_CST AS MATERIAL_COST,
        CS_DH_CST AS DH_SUBLET_COST,
        CS_TOTAL_CST AS TOTAL_COST

FROM    ASPECT.WR_CD_CC_RPT_SUMM
WHERE
        CS_CNTRY_CD = '81930' AND
        CS_YEAR = 2016 AND
        CS_MNTH = 1 AND
        CS_WK = 2 AND
        CS_MFC_CD = 'CBU'

I am getting the result like this-

is there any alternate way to get the total of all the cost on the basis of year, month and week no without using UNION.

It really depends on your main goal - if it should look 100% like your result in the question above then I am sorry the answer is no.
If you want to get a result with the sum etc. yes there are ways round the union but you might not see the "Total" text.

Here is one solution idea (with less data and columns as I could not extract it from your question).

create table test (year integer, month integer, week integer, cost_carrer char(1),
                    wage_cost decimal(10,2), material_cost decimal (10,2), total_cost decimal(12,2));

insert into test values (2016, 1, 2, '0', 20000.00, 30000.00, 50000.00),
                        (2016, 1, 2, '1', 30000.00, 40000.00, 70000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00);

select year, month, week, cost_carrer, sum(wage_cost), sum(material_cost), sum(total_cost)
 from test
 group by grouping sets ((year, month, week,  cost_carrer), (year, month, week))
 order by year, month, week, cost_carrer

This will return:

YEAR MONTH WEEK COST_CARRER WAGE_COST MATERIAL_COST TOTAL_COST
 ---- ----- ---- ----------- --------- ------------- ----------
 2016     1    2 0            20000.00      30000.00   50000.00
 2016     1    2 1            30000.00      40000.00   70000.00
 2016     1    2 2            75000.00     105000.00  180000.00
 2016     1    2 NULL        125000.00     175000.00  300000.00

And I think this is the "idea" behind your question and example.

Other - even more powerful group by variantions are "group by rollup" and
"group by cube" which is described here in the documention (with examples)


Mysql Index for concat select For Below Query

SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG
    FROM entry_log
    WHERE TRIM(LEADING 0 FROM card_no)='2948'

I've index on

date
card_no
date,last_entry
date,last_entry,card_no

My Explain Shows

id  select_type     table       type    possible_keys   key             key_len     ref     rows    Extra
1   SIMPLE          entry_log   index   NULL            date_last_card  158         NULL    103766  Using where; Using index

My Explain Extended shows

id  select_type     table           type    possible_keys   key             key_len     ref     rows    filtered    Extra
1     SIMPLE        entry_log       index   NULL            date_last_card  158         NULL    103766  100.00      Using where; Using index

I want to know which index can i remove/use, Am i in right path, How should i improve execution time for above query?

CREATE TABLE `entry_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `card_no` varchar(50) NOT NULL,
 `date` date NOT NULL,
 `first_entry` time NOT NULL,
 `last_entry` time NOT NULL,
 `all_entry` text NOT NULL,
 `entry_time` datetime NOT NULL,
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9308 DEFAULT CHARSET=latin1

Cleanse your data before storing it.  Otherwise, INDEXes may be useless.

I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless.

This SELECT would run a lot faster because of the index:

SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG
    FROM entry_log
    WHERE card_no = '2948'

OK, you don't like the Cleanse advice.  And your card_no is always an 8-digit number.  In that case, this would allow the use of the index:

WHERE card_no = RIGHT(CONCAT('00000000', '2948'), 8)

Please provide SHOW CREATE TABLE so we won't have to guess any more.

Edit

If a card_no is just a number, but with some extra zeros in front, it could be stored more compactly as an INT, BIGINT, or DECIMAL(...).  Then do the LPAD to prefix it with the needed zeros when fetching it.

SMALLINT (2 bytes) can handle 4 digits, as with your example.
INT (4 bytes) can handle 9 digits.
BIGINT (8 bytes) can handle 18 digits.
DECIMAL(32,0) (15 bytes) can handle 32 digits.  A smaller number could be used if you never need a full 32.

You would need to pick whatever size will handle the largest card_no you can have.

VARCHAR(50) takes 2 + N bytes, where N is the number of digits.  If card_no varies a lot is length, this might still be the best bet.

Edit2

If you have a lot of rows for a card_no, it will run a lot faster if you do these:

Combine date (date) and last_entry (time) into a single column of DATETIME datatype.  I'll call the column last_log.
SELECT MAX(last_log) AS LAST_LOG
INDEX(card, last_log)

Rationale:  The INDEX can do essentially all the work of the MAX -- this will run a lot faster.

(Note:  This comment about last_log is independent of the discussion on leading zeros for card_no.  Be sure to formulate the WHERE clause as WHERE card_no = ... in order to make use of the index.)

Edit 3

In the newest versions of MySQL 8.0 and MariaDB 10, you can index "virtual" columns.


Ident authentication failed for user "postgres" I am using PostgreSQL12 on Cent0S8, and I am able to connect to PostgreSQL using the command:
sudo -u postgres psql
However, when I attempt to connect using:
psql -U postgres -h localhost
I recieve the following error:
psql: error: count not connect to server: FATAL: Ident authentication failed for user "postgres"
Every option is set to trust within my pg_hba.conf file, and I successfully restarted the server after making these changes.
Does anyone know of what I would need to change to be able to login using the psql -U postgres -h localhost command?

If you are editing wrong hba file this should fix

Switch to postgres user on OS
run psql
should be connected to PostgreSQL
run show hba_file;
should be seeing to correct hba_file directory and edit that file.
reload postgresql

lloyd@localhost:~$ sudo su - postgres
postgres@localhost:~$ psql
psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1), server 12.5 (Ubuntu
12.5-1.pgdg18.04+1)) Type "help" for help.
postgres=# show hba_file ;

hba_file
/etc/postgresql/12/main/pg_hba.conf (1 row)
postgres=# select pg_reload_conf();

Last query should return true if it's not, there is an error in hba file so PostgreSQL won't refresh hba file. So you should look for error in file. Also you can look at logs. After reloading PostgreSQL, it shows you error with line.


Azure SQL: Should Manual Indexes be Applied with automatic tuning? Should Manual Indexes on tables be applied in AzureSQL Cloud Database? We read that AzureSQL does automatic tuning, so are manual indexes even required? We read, there can be cases where AzureSQL drops our manual indexes, if its not needed. Is there anyway to prevent this ?

From everything I'm reading this is only a DEFAULT option in this March 2020 doc from MS Azure.1:

In March 2020, Azure SQL Database will introduce new automatic tuning default settings designed to provide optimal database performance.

Existing servers that aren’t configured with automatic tuning will have the new default settings automatically applied and enabled. Servers that are already configured for automatic tuning won’t be affected.

When new Azure SQL Database servers are created, the default settings for automatic tuning will be FORCE LAST GOOD PLAN = enabled, CREATE INDEX = disabled and DROP INDEX = disabled.

How to disable it, and do 'custom' options are found here in the MS docs.  The option you are looking for is called AUTOMATIC_TUNING


Scalable way to track who's viewed a post? I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly everyone, it will be difficult to find the needle (the user who hasn't seen it) in the haystack (users who have seen it)

Are there any known strategies to resolving this problem?

I think the complexity of performing this task is small and does not change with size. The same SQL that will find a missing recipient from 100 users will also find a single missing recipient from 100M users.  The time to do so, however, is likely to be linear in the number of posts and the number of users.

I can see two ways to organize the data. One is a "positive" list, the other a "negative". For the positive route the system actively stores what has happened. As a post is dispatched a list is kept of the recipients. For one million users this will be one million separate data items. Each item is marked as "unread". As a user sees a post the corresponding data item is marked "read". This will result in two million writes per post. It is likely most reading would happen within a short interval of the post being sent and tail off rapidly thereafter, so there will be spikes in write activity. The system will have to account for new users joining after the post was sent and users leaving before or after seeing the post.

In the "negative" route you store only read events - as a user sees a post the corresponding event is stored. When you need to check who has read the post the system compares this "read" list to the master list of users and returns the intersection or complement as appropriate. Should a new user join after the post was sent they will show as "not read" even if that post was never sent to them. (This assumes a push model, like email, rather than a pull model, like browsing a website.)

How to store this? In a relational model you would have one row per user/post. For 1M users and 100k posts, and integer IDs for each, this works out at 150GB, probably about 500GB once indexes and overhead are considered. This is not big by modern standards. Data compression or a columnstore would help here, but IIRC PostgreSQL does not provide either out of the box.

It would be possible to reduce the amount of storage at the cost of additional writes. Rather than store the individual user/ post receipts store a post/ userid range i.e.

Post A has been read by users 1 through 10
Post A has been read by users 11 through 11
Post A has been read by users 45 through 78

As new receipts arrive existing ranges can be extended, merged or inserted. Assuming receipts arrive in random order, the number of rows will increase (to at worst COUNT(users)/2 before reducing toward 1 as rows merge). I have a nasty feeling that this would be polynomial in IO, however, something like O(Nx).

If UserIDs are allocated without gaps the checks are easier. In the real world there are likely to be gaps and there will be a lot of look-ups on the user table to see if two receipt ranges can be merged.

The above is symmetrical for "user has read post x through y" and also read vs unread.

Having thought it through some more, I think the algoritm is actually O(log N). For each message receipt the algorithm will have to read where that message lies in the current log and UPDATE, INSERT or DELETE as appropriate. Assuming a BTree index exists these are O(log N) operations (even though there may be several operations per read receipt).

In the best case scenario read receipts would arrive in exactly primary key sequence (massively unlikely). Then the logging table would only ever have one row. In worst case read receipts would arrive in alternate sequece e.g. all the odd numbered keys followed by all the even numbered keys. This will cause the log table to grow to 50% of the number of posts sent.

A rough prototype with read recipts arriving in random sequence saw the logging table grow to a maximum of 25% of the number or posts. So for a sample of one million posts the log table would start with a single row; this would grow to about 250,000 rows; and then shrink to zero rows when the last read receipt was processed. This was consistent across several orders of magnitude.  Some day I'll sit and work out the maths of why this should be so, but not today.


ORACLE DB Connects only when I do not specify SID I have an Oracle 19c database in a Unix server.
SQLPLUS command connects only when I do not specify the SID.
sqlplus system/manager
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 20 12:37:30 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 20 2020 11:45:41 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

However, when I try with the SID, it does not connect.
sqlplus system/manager@S19110

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 20 12:40:01 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

Enter user-name:

I see the following in my listener.log :
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

I googled the error and tried the following troubleshooting methods but no help.

6751 permission for $ORACLE_HOME/bin/oracle
Increase max number of processes in the database.

Please help.

Found the issue. But I don't have any explanation why or how it worked.
It worked after removing GLOBAL_DBNAME parameter in listener.ora under SID_LIST_LISTENER


Update value by column/field in INSERT SQL I want to update the column value in multi-rows insert SQL.
For example:

INSERT INTO `product_price` (`product_id`, `store_id`,`price`)
VALUES (20001, 1, `price`+10),
(4442, 1, `price`-5),
(2442, 1, `price`*1.1),
(3002, 1, 399),
ON DUPLICATE KEY UPDATE `price` = VALUES(`price`);

Where you can see, I want to update the price by some constant value or by some self updates (+, -, *, /).

But it doesn't work in case of INSERT (works fine in UPDATE statement).

How to make it workable in INSERT statement.
FYI, I am using MySQL 5.7.

For an update, you are changing an existing column value in a number of existing rows, 'select'ed based on the "where" clause you specify:

update product_price
set price = price + 10
where product_id = 4442 ;

The query locates any rows with product_id 4442 and then, for each one, extracts the current value of price, adds 10 to it, and stores that result back into each record.

For an insert, you are creating a completely new record which, by definition, cannot contain any values (until after it's been created):

insert into product_price
values ( 2442, 1, price * 1.1 )

So; what value does price represent in this query?

Before this statement executes, there is no row with product_id 2442.
Therefore, there is no price value (in that non-existent row) for the query to retrieve and modify.


Dealing with NULL values and EMPTY strings in UNION of two tables I want return records from two database tables when a record differs in at least one of the fields.

I have written a query for it using the UNION operator.  It is working absolutely fine.

However, I want to treat NULL and '' (empty string) as the same value.

For example, all entries have a field Email-ID having value NULL in one table and '' in the other table.

Using the query I have, it is showing these two are different.  I want these two values to be treated the same.  I have around 15 such fields.

How do I do this?

Use ISNULL or COALESCE

SELECT COALESCE(A.NULLABLEFIELD,'') FROM A
UNION
SELECT COALESCE(B.NULLABLEFIELD,'') FROM B


Will SSIS 2012 work with SQL Server 2008R2 (not integrated) Our company has SQL Server 2008R2 and plans to upgrade to 2012 in late 2013 or early 2014. I would like to use SSIS 2012 now as we do have the software.

We have a company policy where we cannot run SSIS packages on the database server itself. We run them from another app server, not integrated services.

My question is, if I were to install all 2012 composent for SSIS on our app server would there be any potental issues? I have not used SSIS 2012 yet so I do not know if the OLE DB compoents Id use to point to our SQL 2008 instance would be an issue.

There will be no issues as SSIS 2012 will work with previous versions of SQL Server. I recently upgraded from 2008R2 to 2012 with 45 SSIS packages and all are working fine. As Always Test all your packages before hand on 2012.

from this link :

As you may already know, the SQL Native Client OLE DB provider is being deprecated. This doesn't affect other OLE DB providers or the OLE DB API. Also, the SSIS OLE DB components are not being deprecated.
In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from the packages.


After successfully executing this query, how do go I back to the previous stage in MySQL? +----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Query:

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

After successfully executing this query, how do I back to the previous stage?
I have tried rollback, but it is not working.
My engine is InnoDB.

If your table engine is not InnoDB, or you haven't used transactions for this operation, then it might be not possible.

To have possibility to ROLLBACK DML statements (like INSERT, UPDATE or DELETE queries) you should use transaction blocks:

START TRANSACTION;

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;
-- and more DML queries

COMMIT; -- or ROLLBACK;

Since transaction was COMMITed it can not be rolled back. Only option (that I see) is to restore database backup (if it exists).

UPDv1:

Just as an advice: Before you do any critical changes to database (perform transactions, execute complex scripts, that includes INSERTs, UPDATEs and DELETEs or change data structures with ALTERs or DROPs) make a backup of your database.

You may probably restore backup as another database copy, then test script on it. If nothing goes wrong, then perform script for a real database.


Extract all records even with missing "optional" fields in another table I have a main product table ICITEM listing product IDs and another table ICITEMO (please don’t ask about the table names) with a number of optional product fields.  Not all optional fields exist for all products, for example:

ICITEM.FMTITEMNO
----------------
Product A
Product B
Product C
Product D

ICITEMO.ITEMNO | ICITEMO.OPTFIELD | ICITEMO.VALUE
---------------+------------------+---------------
 Product A     | COLOR            | Blue
 Pruduct A     | SIZE             | 12
 Product B     | COLOR            | Red
 Pruduct D     | SIZE             | 14

I need to retrieve all product IDs from the main ICITEM tables regardless if they have corresponding optional field(s) in the secondary table ICITEMO, so for example looking at the COLOR field:

ICITEM.FMTITEMNO | ICITEMO.VALUE
---------------- +--------------
Product A        | Blue
Product B        | Red
Product C        | NULL
Product D        | NULL

At the moment I have a rather ugly UNION query which works but I’m sure it can be done in a more professional and elegant way:

SELECT ICITEM.FMTITEMNO, ICITEMO.VALUE
FROM ICITEM, ICITEMO   WHERE ICITEM.ITEMNO = ICITEMO.ITEMNO  AND
ICITEMO.OPTFIELD="COLOR"

UNION

SELECT ICITEM.FMTITEMNO, NULL as GENDER
FROM ICITEM
WHERE ICITEM.FMTITEMNO NOT IN
(SELECT IC.FMTITEMNO
FROM ICITEM IC LEFT JOIN ICITEMO ON IC.ITEMNO = ICITEMO.ITEMNO
WHERE ICITEMO.OPTFIELD="COLOR")

What you need is an outer join, likely a left outer join. From the documentation

  Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

For you the "left" table will be your product table and the other table is the attributes. The query will be

select <some columns>
from ICITEM as p
left outer join ICITEMO as o
  on o.FMTITEMNO = p.FMTITEMNO
  and o.OPTFIELD = 'COLOR';


PostgreSQL: How to list all stored functions that access specific table Introduction:

PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.

Problem

I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's \df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  NOT p.proisagg
AND    n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
AND    pg_get_functiondef(p.oid) ~* '\mbig\M';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

\m and \M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

How to drop all of my functions in PostgreSQL?


Upgrade process from SQL Server 2000 to SQL Server 2012 I have a SQL Server 2000 database that I would like to upgrade to SQL Server 2012 (running on a different server).

I don't think there is a direct upgrade path, but if there is, what is it?

My current plan is:

Backup SQL Server 2000 database
Restore on server with SQL Server 2008 installed
Set compatibility level to 100 (SQL Server 2008 / SQL Server 2008 R2)
Backup database from SQL Server 2008 server
Restore on server with SQL Serevr 2012 installed
Set compatibility level to 110 (SQL Server 2012)

Am I doing unneeded steps?  Am I missing anything (like running utilities to identify usage of unsupported Transact-SQL)?

No, as I see it, you didn't miss anything. I have upgraded one of my databases from SQL Server 2000 to SQL Server 2012.

Another way is to upgrade the entire server – in this case you can use the upgrade wizard.

UPDATE: my upgrade process was successful, but be careful about discontinued and deprecated features in SQL Server 2008 and 2012.


Is it possible to change the Access data source to SQL Server? I'm currently managing a "db" in Access (meh). A complex structure of masks and VBA scripts was built over this "db".

What I'd like to do is change the Access datasource from Access itself to SQL Server. I've already upsized and imported the whole data. In this way the users keep managing the system using the old Access masks & scripts but the data is inside SQL Server.

Is this possible? Or do I have to create the whole managing structure from scratch?

My problem is that more than one "client" have to manage the data simultaneously, that's why I'm searching for the magic "edit data source" button. The upsize seems to link a single instance of Access to the new data source.

Yes.

When you "upsize", Access creates "linked tables".
The queries and forms on top of this don't know any difference

Some links:

https://stackoverflow.com/q/184406/27535 (SO)
http://support.microsoft.com/kb/2279654/en-us (MS KB)

Edit:

You'll need to deploy a new mdb/accdb to each client, along with a DSN (Control Panel, Admin tools, "Data Sources (ODBC)")


Install db2 express c on Mac OSX I have posted the question yesterday, then I was able to figure it out by myself. As I haven't found any complete guide, I'm writing down the steps just in case.

So the question is: "How do I install db2 express c on Mac OS X?".

First of all you need to edit/create the /etc/sysctl.conf and insert the following:

 kern.sysv.shmmax=1073741824
 kern.sysv.shmmin=1
 kern.sysv.shmmni=4096
 kern.sysv.shmseg=32
 kern.sysv.shmall=1179648
 kern.maxfilesperproc=65536
 kern.maxfiles=65536

reboot. Then google db2 express c mac osx and download db2 express c 9.5.2 for mac osx. Once downloaded uncompress the file

tar -xzvf db2exc_952_MAC_x86_64.tar.gz

enter the exp directory and start the installation wizard as root

cd exp
sudo ./db2setup

before continuing you need to create 2 users, one for administration purposes and another one as instance owner. For the second user name use no more than 8 characters and use just letters, otherwise the node name won't be accepted later on. Follow the installation wizard steps: enter the username for the DAS and for the instance owner. Once the installation is complete, you need to switch to the instance owner (dbuser in our case):

su dbuser

add the following line to your .bashrc file:

. ~/sqllib/db2profile

exit and relogin. If everything went well you should be able to start db2 (as a root) and create the SAMPLE database

sudo db2start
db2sampl
db2

That's it!


How do you update a large MySQL table that's on a server? I have an application on a web server.

The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this table to check if a domain name is available and it works surprisingly fast.

Every day, however, the list of taken domain names is updated by the organization which provides it.  I have to download the compressed new list of words and then use it to update the domains table for it to remain accurate.

I am not sure, however, how to go about updating the table which is live on the web MySQL server and takes up roughly 3 gigabytes.

I've researched it a bit, and the best so far I've found is to create a new table/database then delete the old one and switch them over. The changes to the table, however, are probably less than 1% daily, so this feels overkill and inefficient.

Would be great to know if there is a better way.

Given the clarifications on your comment, I would recommend 2 options:

Use LOAD DATA INFILE IGNORE to load the data directly from the filesystem. This will insert new domains and not touch the "old" ones, but it will not delete the ones that are removed. On the bright side, it will reduce IO a lot.
Go for your approach: use LOAD DATA INFILE on a new table, then RENAME the old and new table, which is a very fast operation. Inserting on smaller tables is faster than inserting on larger tables on InnoDB, but the whole table has to be written.

In both cases, there are a lot of things that I can recommend you to improve the inserting performance.

If you do not perform other write operations than the import, this would be one of the few cases in which I would recommend you to use MyISAM- its import performance is faster, as it does not have to deal with a transaction log, and you will not have to deal with corruption, as you will only use it as a read-only table. If the import fails, you can always repeat it from the file.

If you use InnoDB, on the other side, you can check for duplicates/missing records and modify them in parallel to match MyISAM performance but that only will be performant on the latest MySQL versions (>= 5.6).

Other things to have into account: disable temporarily the binary log; disable keys, try fixed-with format (for MyISAM), disable the doublewrite buffer and autocommit, augment the buffer pool and transaction log, make sure you use innodb_file_per_table=1 (for InnoDB). With proper tuning, for 5.5, I am able to insert a 50 million table (much wider than yours) in 2-3 minutes in MyISAM (without flushing the cache) and 11-12 minutes (durably) in InnoDB on spinning disks. 5.6 and 5.7 improved InnoDB performance closer to MyISAM.

Addendum: On other databases, I would recommend you to use a cursor to analyse the table row by row, but I do not have much faith on MySQL's programatic features.


The right way of starting up a Galera Cluster I am confused about the bootstrap process of MariaDB Galera Cluster.

Let's say we have 3 nodes of a MariaDB Galera Cluster, working and functioning normally on CentOS6/7. However, at some point we have (doesn't matter by what reason) to stop all 3 nodes and then start up the cluster. We are stopping the nodes 1 by 1 normally and cleanly. In order to start up the cluster, I go and check out grastate.dat and see which node is with most current data. I bootstrap this node first.

What is unclear to me is how the bootstrap is done exactly for CentOS 6 and 7. The 2nd and 3rd node can be started with systemctl start mysql. How do we start the first node? Do we use service mysql start --bootstrap on the first node with most current data? I read that for CentOS 7 it's systemctl start mysql@bootstrap.service. What is the procedure of boostrapping the first node of the cluster after we stopped all 3 nodes normally?

PS: Related to this, with some more details, is the post by dr01:
grastate.dat with seqno -1 on a healthy cluster. Why?

At MariaDB 10.1 where Galera included you need to start first node with key --wsrep-new-cluster

e.g.
/etc/init.d/mysql start --wsrep-new-cluster

All other nodes starts as usually:

/etc/init.d/mysql start

To determine which node needs to be bootstrapped, compare the wsrep_last_committed value on all DB nodes:

KVM-1> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed | 21568       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

KVM-2> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed |  1359       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

KVM-3> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed |   537       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

KVM-1 has the most up-to-date data. In this case, all Galera nodes are already started, so you don’t necessarily need to bootstrap the cluster again. We just need to promote KVM-1 to be a Primary Component:

KVM-1> SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

The remaining nodes will then reconnect to the Primary Component (KVM-1) and resyncing back data based on this node.


Using CHECKSUM on where clause I'm going through a long stored procedure and I found this, as part of a long query:

SELECT ....
FROM   ---
WHERE  condition
  AND  CHECKSUM(r.roomprovider_id) <> CHECKSUM(ISNULL(br.roomprovider_id, r.roomprovider_id))

roomprovider_id is an INT value

First time I see it and I've no clue on what is being achieved with this. I made some searches, read the definition of CHECKSUM on the tech pages and couple of sources more but can't understand the point of using CHECKSUM here.

I'm not looking for an answer that explains exactly this case (if possible, even better) but I would be satisfied with an answer explaining the point of this way to use checksum.

Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than:

r.roomprovider_id <> br.roomprovider_id

(And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when the right side is NULL?)

My guesses for their motivation include:

job security through obfuscation
job security through forcing bad performance, that they could fix later easily, since this will remove the possibility to seek on any of those columns if they are suitably indexed to satisfy the query
CHECKSUM() was a shiny new function they just learned, and they just had to implement it somewhere
they believed that CHECKSUM() was less prone to inaccuracy from collisions than other methods, or that - with only one input - it served any purpose whatsoever


Renaming table and putting a view in its place on Postgres I have a very central table in our database that is used by a range of applications, it has rules attached to it, triggers and all the dependencies that you can imagine. Now I would like to modify the table without causing any problems with the dependencies. I have previously been successful with doing the following but in a much less complex case:

alter table reconciliations rename to matches;
create view reconciliations as select * from matches;

What this achieves is that I could now modify the new "matches" table and for example add a column or rows, which do not need to get presented in the "reconciliations" view (by adding a where clause to filter them out).

I am on Postgres 9.5 so the view is automatically updatable. Initial tests shows that there are no immediate problems with this, so I am asking this question to know what kind of problems I should be looking for. Performance is not a big issue.

If you do this in production, be aware of prepared statements. Those have already been parsed, rewritten (and the query plan cached). The effect kicks until prepared statements are deallocated (which can take a long time).

To check for prepared statements (of the same session only!):

TABLE pg_prepared_statements;

The effect also extends to plpgsql functions that handle SQL commands like prepared statements internally.

Normally, prepared statements are forced to be re-planned after any change to involved objects. But your actions circumvent this security mechanism.

Also, most queries will just keep working. But not all.

Demo

CREATE TEMP TABLE foo (id int);
INSERT INTO foo VALUES (4);
PREPARE x AS SELECT foo FROM foo WHERE id > $1;  -- uses row type

This works:

ALTER TABLE foo ADD COLUMN t text;
EXECUTE x(3);  -- automatically re-planned

But this does not:

ALTER TABLE foo RENAME TO foo1;
CREATE VIEW foo AS TABLE foo1;
ALTER TABLE foo1 ADD COLUMN t text;

EXECUTE x(3);  -- not re-planned!

ERROR:  cached plan must not change result type

The manual on PREPARE:

  Although the main point of a prepared statement is to avoid repeated
  parse analysis and planning of the statement, PostgreSQL will force
  re-analysis and re-planning of the statement before using it whenever
  database objects used in the statement have undergone definitional
  (DDL) changes since the previous use of the prepared statement. Also,
  if the value of search_path changes from one use to the next, the
  statement will be re-parsed using the new search_path. (This latter
  behavior is new as of PostgreSQL 9.3.) These rules make use of a
  prepared statement semantically almost equivalent to re-submitting the
  same query text over and over, but with a performance benefit if no
  object definitions are changed, especially if the best plan remains
  the same across uses. An example of a case where the semantic
  equivalence is not perfect is that if the statement refers to a table
  by an unqualified name, and then a new table of the same name is
  created in a schema appearing earlier in the search_path, no automatic
  re-parse will occur since no object used in the statement changed.
  However, if some other change forces a re-parse, the new table will be
  referenced in subsequent uses.

The above is another example where the "semantic equivalence is not perfect".


Seeking help with Extended events behaviour I'm using Extended Events to track the procedures that are being executed on the server, along with the Hostname of its executor.

I've used the following XE script for the same:

CREATE EVENT SESSION [EXEC_SP] ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
  SET collect_statement=(0)
  ACTION
  (
    sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,
    sqlserver.nt_username,sqlserver.server_instance_name,
    sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,
    sqlserver.transaction_id,sqlserver.username
  )
  WHERE ( [sqlserver].[is_system]=(0))
)
 ADD TARGET package0.asynchronous_file_target
 (
   SET FILENAME = N'C:\EXEC_SP.xel',
   METADATAFILE = N'C:\EXEC_SP.xem'
 )
 WITH
 (
   MAX_MEMORY=4096KB,
   EVENT_RETENTION_MODE=NO_EVENT_LOSS,
   MAX_DISPATCH_LATENCY=15 SECONDS,
   TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
 );

However, every statement inside the body of the procedure being executed constitutes a row at the target.

For instance, if the body of my procedure has 5 statements in it, the event will be fired 5 times, and there will be 5 different entries corresponding to the same at the target.

I seek just a single entry for every procedure being executed instead of multiple entries.

That is every procedure being executed should constitute just 1 row instead of multiple.

How to resolve this?

sp_statement_completed is doing exactly what you are asking it to : fire for every statement in a procedure.

You likely want to change that to sqlserver.module_end - but some other things in your session don't seem to match what you're after anyway, like transaction_id, when would instance name ever be different, and capturing both database_id and database_name is kind of redundant (unless you are dropping and re-creating databases constantly, you can derive one from the other, but I would argue that database name is more useful). Perhaps:

CREATE EVENT SESSION [EXEC_SP] ON SERVER
ADD EVENT sqlserver.module_end
(
  SET collect_statement=(0)
  ACTION
  (
    sqlserver.client_hostname,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.server_principal_name,
    sqlserver.session_id,
    sqlserver.sql_text,
    sqlserver.username
  )
  WHERE ( [sqlserver].[is_system]=(0))
)
 ADD TARGET package0.asynchronous_file_target
 (
   SET FILENAME = N'C:\EXEC_SP.xel',
   METADATAFILE = N'C:\EXEC_SP.xem'
 )
 WITH
 (
   MAX_MEMORY = 4096KB,
   EVENT_RETENTION_MODE = NO_EVENT_LOSS,
   MAX_DISPATCH_LATENCY = 15 SECONDS,
   TRACK_CAUSALITY = OFF,
   STARTUP_STATE = ON
 );


How does SQL Server control which users get automatically added to a new database? I re-create my Dev Database with a Visual Studio DB Project twice a week.

Today I fixed my permissions to be Active Directory group based rather than giving individuals permissions.

After I re-ran my build I noticed that one domain/windows use was still in there.  I searched all through the DB project and could not find this user in there anywhere.

So to test, I created a different database (ie MyDummyDb).  Sure enough the user was added to that db too.

This user does not even have a valid login (I changed the logins to be AD Group based too).

So I have to assume there is a server level setting that controls which users get automatically added to a new database.

Is this right?  Does anyone know where this setting is?

You have a user defined in the model database. Good odds someone mis-clicked once upon a time and created the spurious user in model accidentally. model is the template for any databases created on an instance, so if a user exists there it will exist in all databases you create.

Run the script below (on a development instance!) and you'll see a user created in model is added to the TestForModelUser database.

USE [master]
GO

CREATE LOGIN [ModelUser] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [model]
GO

CREATE USER [ModelUser] FOR LOGIN [ModelUser]
GO

CREATE DATABASE TestForModelUser
GO

USE [TestForModelUser]
GO

SELECT * FROM sys.sysusers WHERE [name] = 'ModelUser'
GO

USE [master]
GO

DROP DATABASE [TestForModelUser]
GO

USE [model]
GO

DROP USER [ModelUser]
GO

USE [master]
GO

DROP LOGIN [ModelUser]
GO


Moving rows from one table to another I am moving records from one database to another, as a part of archiving process. I want to copy the rows to destination table and then delete the same rows from the source table.

My question is, what is the most efficient way to do a check if the first insert was successful before deleting the rows.

My idea is this, but I feel there is a better way:

@num_records=select count(ID) from Source_Table where (criteria for eligible rows)

insert * into Destination_Table where (criteria for eligible rows)

if ((select count(ID) from Destination_Table where (criteria) )=@numrecords)

delete * from Source_Table where (criteria)

Is it better/possible to combine it with RAISERROR function? Thank you!

If your archive table does not.

Have enabled triggers defined on it.
Participate on either side of a FOREIGN KEY constraint.
Have CHECK constraints or enabled rules.

You could also do it in one statenent.

DELETE FROM source_table
OUTPUT deleted.Foo,
       deleted.Bar,
       SYSUTCDATETIME()
INTO archive_table(Foo, Bar, archived)
WHERE  Foo = 1;

This will either succeed or fail as a unit and also avoids possible race conditions with rows being added in between the INSERT to the archive and DELETE (though your WHERE clause may well make this extremely unlikely anyway).


How to start and then stop one cyclic process I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) should not wait and should not get execution timeout. How it can be implemented?

CREATE PROC dbo.CyclingProcess
AS
BEGIN

    /*

    .. how to start or stop process by external caller

    */

    WHILE(1 = 1)
    BEGIN

        EXEC mysp_WorkingProcedure -- some working process...
              @input1 = @input1
              ....
             ,@delay  = @delay output

           WAITFOR DELAY @delay
    END

END

Clarification:

I have to do some repeated work during long time. I have to start this process and stop it. Why stored procedure? I have to change some input parameters for [some work] and call it again after some delay. Maybe you know other more smart way to implement it. This cyclic process should work in database.

If you want the stored procedure to be invoked by one process only with no other concurrent callers, you'd use sp_getapplock. Note that you'd have to throw an error for a 2nd process (with a zero timeout) but you can swallow this with a BEGIN TRY/CATCH arrangement.

Then you'd release it when the stored proc finishes.

Note that this uses of a stored proc is basically wrong. You'd have SQL Server Agent running the stored proc every minute. You may want sp_getapplock to prevent manual execution too but SQL Server Agent won't allow overlapping calls for the same job.

Or trigger is with Alerts in SQL Agent or Service Broker queues. We don't have enough info: you've asked for help with your chosen code-level solution. We don't know the problem you're trying to solve

If this is a single unit of work, then you need a transaction, and a 12-24 hour transaction is plain wrong


oracle null value in quotes - yet another newbie question I have a where clause like

"and uc.completion_status in ('[P....]') "

here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options Y, N or NotStarted

not started is defined with nvl(uc.completion_status, 'NotStarted') therefore NotStarted is null actually. and null in quotes is not working. how can this problem can be handled?

thanks in advance.

Simple! Put NULL without quotes.

E.g.

INSERT INTO tableName (columnName1, columnName2) VALUES ('Value1', NULL)

We must always use NULL without quotes when we intend to use NULL values. Putting quotes around it, like 'NULL' makes it a string literal and it is treated as character data. Hence for using NULL values, we must always use NULL without quotes, be it a null string, a null number or a null BLOB. For every data type, we must use NULL without quotes.


SQL Server log file is too big We are using SQL Server 2008 with full recovery model, the database size is 10 GB and the log file is 172 GB, we want to clear up the space of the log file internally ,we did transaction log file backup it should be clear up, but it still 172 GB ,what to do?

The log file won't get smaller when doing transaction log backups, but it will clear up space internally.

The only way to make the physical file smaller is to shrink it and expand it to a suiting size again. I'm guessing you also have some VLF issues with that size too.

Edit: You should do regular backups to keep the log from expanding again.

You could run something like this to check how much of the log file is in use:

SELECT
   name
   ,type
   ,size/128 AS [size_mb]
   ,FILEPROPERTY(name, 'SpaceUsed') / 128  AS [size_used_mb]
FROM sys.database_files


Optimize query or model, to get an population overview in time I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance.

I wonder if there is a trick in my model, or MySQL query, to speed up the execution time to get an overview of my population at a specific time.

I have the following table which holds the status (history) of my users:

CREATE TABLE `status` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` varchar(9) NOT NULL,
 `status` enum('NEW','REGISTERED','CANCELED') NOT NULL,
 `timestamp` datetime NOT NULL,
 `explanation` varchar(25) NOT NULL,

 PRIMARY KEY (`id`),
 KEY `index1` (`user_id`,`timestamp`,`id`),
 KEY `index2` (`timestamp`,`user_id`,`id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8

My table will hold a lot of users (5 million), which can have one, or more statuses (so the table will probably hold 10+ million records).
Records are inserted in chronological order, so ID and timestamp are "in sync" (timestamps are not required to be unique, but the autoincrement ID's are)

What I do to get an overview of my population at a specific time is:

SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;

Which gives me the correct ID's, but since MySQL cannot use indexes when sorting on a column (for the where clause), which is not in the GROUP BY, there is no performance.

The explain tells me:

explain SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra                                                     |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | range | index2              | index2              | 8       | NULL | 13897517 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+

Does anyone knows how I can optimize my model, or my query (for instance with subqueries) (, to allow MySQL to make use of indexes), and so boost performance?

What I eventually want is:

SELECT user_id, status, timestamp, explanation FROM `status` WHERE id IN (
  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id
);


How to connect to Microsoft Dynamics Rms 2.0 Running sql2008r2? Would anyone know anything about Microsoft Dynamics Rms 2.0 (2012)? We are having trouble connecting our sql server to the client side cashier (windows 10 pro) this software sunset in 2016 I believe and Microsoft won’t help. (We used an emergency ticket) So the “cashier”, doesn’t see the database, however it will see the server it’s self. When ever using ole ob connection the server is seen but not able to be connected to. We have opened every port on both computers to test to see if we could establish a connection, but to no avail... I have spent 16 hours on this with several people and can’t figure it out.
Here is the original question asked here https://www.facebook.com/groups/thisisanitsupportgroup/permalink/2698630683716128/
Covers a lot of what I have already done so

You're going to need SQL Server 2005 backwards compatibility components installed at the client PC for that version of RMS to connect to any SQL Server newer than 2005 if I recall correctly.  Good luck finding a download link. Microsoft seems to have nuked all of them from what I can tell.  If you are taking over support of a system, search for an msi installer package named "SQLServer2005_BC_x64.msi" or x86. You probably want x64 on any newer clients though.  Hopefully you have a local copy already in an install folder somewhere assuming the previous RMS clients needed it to connect to the same DB Server.
Hope this helps.


Copy database wizard - any change in lsn number of source database After Copy database wizard - database restore in another server . Any change in LSN number of source database . is it same as copy only backup?

One method which you can choose will use the SMO programming tools.  The other method which results in the source database going offline is detach/attach.  Neither of these will affect backups or LSN's.


UPDATE Blocking SELECT Of Unrelated Rows I have TableA with Col1 as the primary key. I am running the following transaction without committing it (for test purposes).

BEGIN TRANSACTION
UPDATE TableA
SET Col3 = 0
WHERE Col2 = 'AAA'

In the meanwhile, I run the following query and see that it waits on the first transaction to complete.

SELECT *
FROM TableA
WHERE Col2 = 'BBB'

But the following query returns the results immediately:

SELECT *
FROM TableA
WHERE Col1 = '1'

So I thought that the second query might need to read rows that have exclusive locks put by the first transaction in order to select rows with Col2 = 'BBB'. That's why then I tried to index Col2 so that a table seek will not be necessary but that did not work either. Second query still waits on the first transaction.

What should be done to prevent SELECT from blocking (except the use of NOLOCK).

P.S: Transaction isolation level is "Read Committed".

I might have to modify this answer once you provide more information based on my comment.

For now follow this example.  I am using SQL 2016, SP1.

--Creating table
    DROP TABLE IF EXISTS TableA
    CREATE TABLE [dbo].[TableA](
      [col1] [int] NULL,
      [col2] [nchar](10) NULL,
      [col3] [int] NULL
    ) ON [PRIMARY]

   GO

Inserting data:

INSERT INTO [dbo].[TableA]
           VALUES (10, 'AAA', 11),
                  (12, 'BBB', 13),
                  (14, 'CCC', 15)

Session ID 65 running this.

BEGIN TRANSACTION
UPDATE TableA
SET Col3 = 0
WHERE Col2 = N'AAA'

Session ID 66 running this:

SELECT *
FROM TableA
WHERE Col2 = N'BBB'

Looking at the locks held by both session. Session 65 is holding an exclusive lock on file id:1, page id:488 and slot id=0, shared lock is requested on the same resource by session 66 and is waiting.

Running the 2nd query and looking at the actual execution plan it is doing a table scan and because of your isolation level reading requires a shared lock to prevent any update or delete on the same row.

Creating an index on col2 does not change the behavior in my case because of number of rows.

CREATE NONCLUSTERED INDEX [NCI_TableA_col2] ON [dbo].[TableA]
(
    [col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

After I inserted 3000+ rows I can get a index seek and session 66 will not wait for the session 65 anymore.

Why did your 3rd query ran without waiting: I can answer that once you provide more information.


Error Code: 1136. Column count doesn't match value count at row 1 I'm struggling here. This is my code

CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
INSERT INTO country(
    countryName,
    nationality,
    countryPrefix
)
SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix
) AS tmp
WHERE NOT EXISTS
    (
    SELECT
        countryName
    FROM
        country
    WHERE
        countryName = @countryname
)
LIMIT 1

And i'm trying to execute code with this sentence

call ccc.setCountry('Togo', 'Togolese', 228);

The ERROR it displays is this :

    Error Code: 1136. Column count doesn't match value count at row 1

Change from

SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix

to

SELECT
    @countryname AS countryName,
    @nationality AS nationality,
    @countryprefix AS countryPrefix


When should we use table partitioning in Postgresql? Context: we have two fairly large tables in our database, one holding 80 millions records and the other 160 millions.
We're seeing performances issues and are thinking about using table partitioning for these 2 tables.
My question is: is there a number of records that indicates that we should partition or not to keep good performances ?
I know there isn't a "one size fits all" answer, but there might be a general advice such as "passed X millions records, you should partition the table".
There is a lot of guidance regarding how to partition, but not "when".

No, there is no real row number threshold. If you only have queries that select rows by primary key, the size of the table doesn't really matter.
Partitioning is also primarily a management tool to quickly remove no longer needed rows, not so much a performance tool.
It can be used to improve performance, but only if you have queries that only need a (small) subset of all the rows. If all queries (or at least all performance critical one) do contain the partitioning key, then partitioning can help with performance.
You also need to choose the partitioning key based on then number of partitions this results in. With Postgres 12 or later "thousands" of partitions are feasible (I have heard of users using ~20000 partitions successfully, but I think that's already a stretch). An excessive amount of partitions will most probably be not practical as it will make the planning of the queries a lot slower.
You should also take the fact into account, that a partitioned tables is limited in what the primary key can be - it has to include the partition key. So if you have foreign keys referencing the partitioned table, this might get complicated.


Why is MySQL caching data on our dynamic PHP site? We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that have been completely removed from the database, yet they do not show updated records or even missing data.

If we have the patience of waiting several (undefined) hours, the data will eventually show up. I've added the regular no-cache headers via PHP on all of the pages, but it doesn't seem to affect the MySQL output.

The PHP portion of the site is instantly updateable though, and shows HTML etc changes immediately. Is there a way to force MySQL to only draw fresh data from its queries? I thought this was the way it worked anyways!

Thanks,
Chris

P.S. Here are the php codes I'm using to try to force a no cache situation:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // Always modified
header("Cache-Control: private, no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0

Nothing that MySQL does will lead to stale data -- not the Query cache, not SQL_CACHE, not the key_buffer, not the buffer_pool.  The problem must be outside MySQL.


What structure is used for storing CONTEXT, CTXCAT and CTXRULE Oracle text indexes? AFAIK there are three major structure types used for storing Oracle index data: B-tree, R-tree and Bitmap. All indexes use one of these structures. However, not sure about CONTEXT, CTXCAT and CTXRULE Oracle text indexes...

The documentation covers this thoroughly.

See this link for more detail.


How to append datetime in filename in postgresql How to append datetime in filename in below command:

COPY (select  query) To '/tmp/test.csv' DELIMITER ',' CSV HEADER;

I want my filename like test_date_tim.csv.

If using psql via bash command line, you can simply do:

psql -c "COPY (select  query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"


Oracle Database Migration using Oracle SQL Developer I've 2 Oracle database on different machines [I connect to them via oracle SQL Developer

Now I am planning to have machine 3 which will consolidate both Oracle instances on one new node

Question is what is best way to do this ?

I surfed & found that oracle SQL Developer can help check this Link

Does this will work ??
Or
I have to fallow RMAN cloning option ?

What is best suitable way for doing this ?

why do you need windows at all? you even do not need sql developer.

shutdown the source database
scp datafiles(.dbf .ctl)
scp files in ORACLE_HOME/dbs (like initSID.ora, spfileSID.ora, and also password file)
you should also create dirs for diagnostic_dest (where alert.log is placed)
amend /etc/oratab
set the right SID
start the database

PS: this assumes that the Oracle version (including fixpack) is exactly the same.


SQL Server Database Migration - Backup/Restore while Original DB still in Production I have a very large DB in full recovery mode. My SQL Server is 2008 R2. The thing is that I need to migrate to a new server while having the original DB still in production.

Both backup and restore will take several hours, so...

What happens to the records added into the database while doing the backup?
If I'm right, this is not a problem and the .bak file will include all data until the time of the end of the backup. Do I mark the Copy-only?
Once the backup is done, and I start to restore... what's the best option to get the rest of the records which have been added to the original DB while restoring in the new server?

I've tried full+differential, but I'm getting a message

  the differential backup cannot be restored because the database has not been restored to correct earlier state

in my test environment.

It's sure that it's the last full backup because I'm doing it one minute later after just modifying one record.

Also, I restore the full with norecovery and the differential with recovery.

What do I do wrong?

The backup will be to disk because I need to do it as fast as possible.

Edited:

I'm still getting the message 'the differential backup cannot be restored because...'. What's the problem?

Do i have to do something special while doing the backup to disk? These are the LSNs:

+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
| BackupType |  |     FirstLSN      |  |          LastLSN  |  | CheckpointLSN     |  | DatabaseBackupLSN |
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-
| FullBackup |  | 99000000025100209 |  | 99000000034000001 |  | 99000000033800001 |  | 98000002545900051 |
| DiffBackup |  | 99000000033800001 |  | 99000000036800001 |  | 99000000035000041 |  | 98000002545900051 |
+------------+--+-------------------+--+-------------------+--+-------------------+--+-------------------+-

First step is to change the recovery mode to FULL.

Take a full backup (with compression), and restore the full backup to new server with NORECOVERY. This will allow you to restore additional log backups.

Now configure logshipping from the primary (old) to secondary (new) server.

During the time of cutover, take a tail log backup and bring the secondary server online by restoring the log backup WITH RECOVERY.

make sure you have instant file initialization enabled on both servers to cut down the restore times.

Refer to my answer for Moving Databases to New Datacenters for more detailed steps.


MySQL getting result using 3 Tables and case statements I have 3 tables  named person,person1, person2. All the 3 table has 2 columns name and phno. Now my requirement is, if i give any particular phno it has to show either yes or no
for the corresponding table.

My sample table is :

Person Table:
Name      phno
abc       1234
def       2345
ghi       3456

and person2 table is

Name      phno
abc       1234
def       2345
ghi

and person3 table is

Name       phno
abc
def        2345
ghi        3456

As you can see, the same name can contain phno in one table and not in the other table. The required output is like :

Output 1:
 Phno            person         person2     person3
 1234              yes            yes         no

Output 2:
phno              person         person2     person3
3456                 yes           no             yes

I have tried something like this..

select a.phno,case when a.phno = '1234'then 'Y' else 'N' end as phone_number1,
case when b.phno = '1234' then 'Y' else 'N' end as phone_number2,
case when c.phno = '1234' then 'Y' else 'N' end as phone_number3
from person as a, person2 as b, person3 as c
where a.phno = '1234' and b.phno = '1234' and c.phno = '1234';

can anyone help me to improve this to achieve what I want.Thanks

MySQL does not support full outer joins, so you will have to use unions:

select phno,
if(count(phno1) = 0, 'No', 'Yes') as person,
if(count(phno2) = 0, 'No', 'Yes') as person2,
if(count(phno3) = 0, 'No', 'Yes') as person3,
from (select phno, phno as phno1, NULL as phno2 , NULL as phno3 from person
      union
      select phno, NULL as phno1, phno as phno2 , NULL as phno3 from person2
      union
      select phno, NULL as phno1, NULL as phno2 , phno as phno3 from person3
) sub
group by phno

If you only want to check for a single person, you can use Aditys code slight modifications:

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

It does work, I just tested it myself, feel free to execute my test and confirm:

DROP TABLE IF EXISTS person;
CREATE TABLE person(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person2;
CREATE TABLE person2(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person3;
CREATE TABLE person3(NAME VARCHAR(255), phno INT);

INSERT INTO person(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', 3456),('jkl', NULL);

INSERT INTO person2(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', NULL),('jkl', 4567);

INSERT INTO person3(NAME, phno)
VALUES ('abc', NULL),('def', 2345),('ghi', 3456),('jkl', 4567);

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

result:

phno  person  person2  person3
4567  NO      Yes      Yes


Replication consistently fails to bring new rows to subscriber Short description of the problem:

Initial synchronisation to the subscriber works fine.
Subsequent synchronisations fail to download new rows.
Re-initialising the subscription "fixes" the issue and the subscriber gets the new rows.
GOTO step 2

More details:

We've set up a new merge replication, on SQL Server 2008 R2 (on both the publisher and subscriber). Most tables use a filter for which rows they're going to replicate, similar to this example:

SELECT <published_columns> FROM [dbo].[Table1]
WHERE table1_id in (select table1_id from [dbo].[fn_GetTable1IDsForUser] (SUSER_SNAME()))

The fn_GetTable1IDsForUser function brings the relevant Table1 IDs for each subscriber based on pre-set criteria. Running the above query on the publisher (after replacing <published_columns> with *) returns the expected rows correctly.

Each synchronisation that is not done immediately after a (re)initialisation of the subscription seems to have succeeded, as it produces no error message, however no new rows are downloaded to the subscriber. Reinitialising the subscription allows the new rows to be downloaded, but only for the first synchronisation.

If I try to validate the subscription (in Management Studio; it only seems to work if I do it for all subscriptions, trying to do it for one has no effect), the next synchronisation produces the following error message:

  Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.

I have followed the advice in this article: http://technet.microsoft.com/en-us/library/ms152532%28v=sql.105%29.aspx
However none of the suggested solutions do anything.

  The table is filtered, and there are no changes to deliver to a given Subscriber.

This basically suggests to test the filtering query, which is working correctly, as I've described above.

  One or more agents are not running or are failing with an error.

There are no errors, except when I ask for the subscription to be validated. There are no conflicts either.

  A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.
  Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
  The INSERT stored procedure used by a transactional article includes a condition that is not met.

This is a merge replication, not transactional.

  Data is being deleted by a user, a replication script, or another application.

Nothing acts on the data immediately after synchronisation.

  Data is being deleted by a trigger, or a trigger includes a ROLLBACK statement.

There are no triggers except for the ones set by replication itself (MSMerge_*).

In the end we've not been able to figure out why this happens. We'd welcome any suggestions.

I believe you are running into the issue described in Subqueries that you define on merge articles are not re-evaluated.

From the article the cause is:

  This behavior occurs because the query is not re-evaluated and the row
  is not propagated as part of replication when you update a row in a
  table that is referenced by a subquery. Although you can place a
  subquery in a row filter, it is not a join filter. Although you can
  define a subquery that is based on data from another table, this too
  can cause unexpected results at the Subscriber.

And the resolution is:

  To resolve this behavior, use a join filter when you want the filter
  definition to be re-evaluated during every merge synchronization
  process.


Multiple update of records in the same table I have an update query e.g.:

UPDATE table SET name = ? WHERE id = ?

I have all the relevant ids collected. I am looping over the ids and executing the update query for each one. In pseudo code:

for (id..ids)  {
   stmt->executeUpdate(query, nameForId(id), id);
}

I was wondering is there a smarter/more efficient way to do this query update without looping? For instance, is there a way to create a big string once, like:

UPDATE table set a=? where id=? AND a=? where id=? ...etc.

Philᵀᴹ: Insert all of the data pairs into a temporary staging table, then run a single update. Will be lots quicker.

Kondybas: Create table pairs(id,name) from collected data and then:

UPDATE table
JOIN pairs ON table.id=pairs.id
SET table.name=pairs.name

Wiki answer generated from comments on the question.


How would i create a report involving multiple many to many relationships Lets say table A has a many to many relationship with table B, and table B has a many to many relationship with C. Two junction tables are created for AB and BC to form the many to many relationships.
How would i query these tables so that i can return all the related records between tables A, B, and C?

Is a setup like this a sign of bad design?

Edit: To list my specific issue, I have a table of electronic devices. Each device could have a feature such as wifi, and each feature could apply to that device. Now each feature could pose a security risk, and that same risk could apply to multiple features. So I have many devices, with many features, and many security risks.
My goal is to get the results of a question in this form "For each device, show me each feature, and the security risks that it creates".

I think it is a sound design, based on what is called an associative entity.  To query:

Select A.FieldList..., B.FieldList..., C.FieldList...
   From A
   Join AB On AB.AID = A.ID
   Join B  On B.ID   = AB.BID
   Join BC On BC.BID = B.ID
   Join C  On BC.CID = C.ID


32-bit ODBC driver on 64-bit SQL Server I am unable to use an ODBC 64-bit driver with SSMS 2012 running on a SQL Server 2012 64-bit instance. However, I believe that the 32-bit driver works.

My question is can I use SSMS 2005 32-bit to create a linked server using ODBC on a 64 bit instance of SQL Server 2012? I tried to do this, but when I clicked on OK to create the linked server I got the message:

  Index was outside of the bounds of the array

Is what I am doing completely wrong or is there somehow a way to create this linked server using a 32-bit ODBC connection?

No, you cannot load a 32-bit ODBC driver into the 64-bit SQL Server address space (reference). From your flurry of recent questions, it seems you are having continuing problems accessing a Pervasive database using their 64-bit driver. Have you considered contacting Pervasive support for assistance or an updated driver? You could also ask a question on their forum.


Can I execute a prepared statement inside a MySQL SELECT? I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all authorized/unlocked sellers from a 2nd table (any number of sellers).

I have the basic search query working, but I can't get the "dynamic LEFT JOIN" to work. I was told to do this in a prep statement, which I'm (a) struggling mightly (first time) and which I don't know where to put when working.

My search query will look like this:

SELECT articles AS art
<< need to left join here >>
FROM bigtable AS bt
WHERE
  a lot of other criteria

This is the prep statement I came up with:

SET @sql_text := '
    DECLARE strCount   INT DEFAULT 1;

    SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs
    FROM buyerList AS b
    LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
    WHERE b.bid = ?

    SET @string = "LEFT JOIN preislisten AS p";

    lj:
      LOOP

        SET @string = CONCAT( @string, "ON (p.iln = a.iln AND p.preisliste = sid AND p.ean = a.ean AND p.iln = pricelist ) OR");

        SET strCount = strCount+1;
        IF strCount = recs
        THEN LEAVE lj;
        END IF;

    END LOOP lj;

    SET @string = CONCAT( @string,"(1=0)")
    ';

SET @param_iln = param_iln;
PREPARE stmt FROM @sql_text;
EXECUTE stmt using @param_iln;
DEALLOCATE PREPARE stmt;

So I'm basically finding all sellers & pricelist-name (default BASE) and then try to construct a string like this:

LEFT JOIN pricelists p ON
(p.sid = a.sid AND p.pricelist = "foo" AND p.ean = a.ean AND p.iln = 23467 ) OR
(p.sid = a.sid AND p.pricelist = "bar" AND p.ean = a.ean AND p.iln = 99999) OR
...
(1=0)

My questions:
(1) If there are 25 sellers, I will do 25 loops, but will the correct seller and pricelist be inserted into my string like this?
(2) How do I insert this into my actual search query? If I execute, I may get a string back but I can't just put the string into the query, can I?

Thanks for being easy on me... first time prep-statement, 2nd week MySQL...

EDIT:
So this is what I came up with:

# bigtable query
SELECT count(a.id) AS gesamt_datensaetze, a.nos, a.nos_anzeige
    FROM artikelstammdaten AS a

    #JOIN colors
    LEFT JOIN farbenzuordnung AS zu
        ON a.farbe = zu.farbe
        AND ( ( param_filter = '' ) AND (1=1) OR ( zu.ILN = param_filter ) )

  # ========== NEW PART ==========
  LEFT JOIN preislisten AS p
    ON (        p.iln = a.iln
            AND p.EAN = a.EAN
            AND p.preisliste = (  #NEW GET pricelists
                                                        SELECT IFNULL(klhs.preisliste, "-Standard-") AS pricelistID
                    FROM kundenliste_haendler AS klhd

                    LEFT JOIN kundenliste_hersteller AS klhs
                        ON klhs.iln = klhd.iln_verkaeufer
                        AND klhs.plz = klhd.plz
                        AND klhs.cid= klhd.cid
                                                WHERE klhd.iln_kaeufer = param_iln
                    GROUP BY pricelistID
                              )
           AND p.iln = ( #GET seller
                  SELECT klhd.iln_verkaeufer AS sellerID
          FROM kundenliste_haendler AS klhd

                   LEFT JOIN kundenliste_hersteller AS klhs
                     ON klhs.iln = klhd.iln_verkaeufer
                     AND klhs.plz = klhd.plz
                     AND klhs.cid= klhd.cid

                     WHERE klhd.iln_kaeufer = param_iln
                     GROUP BY pricelistID
                     )
    )
    # active
    WHERE   a.aktiv = "ja"
    # more criteria

Stil trying to find out if this works correctly. If anyone can shed some insights on whether I can combine both my (identical) nested selects into a single nested select, it would be greatly appreciated!

From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.

With regard to what you want to accomplish, you do not need a stored procedure.

Try assembling the SQL statement like this:

@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G

This will print out the desired query

If it is the desired query, execute it

PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;

Give it a Try !!!

If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup

SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
    (p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;

I also just noticed you have a.sid and a.ean, what table has an alias of a ?


How can we restrict collections on specific shards of Mongodb while using Tag Awareness Sharding? We have a Mongodb cluster which has 10 shards (0-9) and two different applications supposedly as APP1 and APP2. Both APP1 and APP2 have different data model and shard keys.

We want that to assign shards 0 to 5 to APP1 and 6-9 to APP2. For that we have created tags for each shards and have assigned these tags with a range to each application using the following commands:

sh.addShardTag('shard0', 'APP1_V0')
sh.addTagRange("test.collectiontest", {"_id.s":0,"_id.i" : MinKey}, {"_id.s":0, "_id.i" : MaxKey}, "APP1_V0")

We have added tags ranges similarily on each of shards based the shard key of the applications.

The problem that we are facing while creating collection, the primary shard i.e assigned to the collection is different from tags assigned to the collection. For eg, App1 collection is tagged to shards 0-5 but when i'm a creating the collection , primary shard assigned to the collection is shard7.

I checked the primary using the sh.status() command.

Can we control this behaviour i.e while creating the collection assign it to the specific shard.

Mongodb Version : 3.4.2

The answer is no. Tags only "guide" where balancer moves chunks. You need to use movePrimary command after creating DB to "change" that primary shard.


Cannot set READ_COMMITTED_SNAPSHOT to OFF on Azure SQL Me and my team are trying to use Azure SQL for our development environment and ran into some critical and unwanted differences in locking behaviour  to our production environment. The main problem is duplication in increment values because with READ_COMMITTED_SNAPSHOT set to ON the xlock, holdlock, rowlock does not prevent selects to other transactions, so we get the typical:

[tran1] select value (1),
[tran2] select value (1),
[tran1] update value (++1),
[tran2] update value (++1)

Instead of the second transaction setting the value to 3.

We tracked it down to having READ_COMMITTED_SNAPSHOT set to ON in Azure SQL, which comes as a default, unlike if you do your own SQL Server setup (that's weird but lets not dwell).

Great, so now we just needed to set READ_COMMITTED_SNAPSHOT to OFF but this is where we are stuck. In Azure SQL we can't seem to do that, even if no other processes are connected to the server we always get a deadlock:

ODBC error: State: 40001: Error: 1205 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.'.

Even if we run the alter with rollback immediate:
ALTER DATABASE [xxx] SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

We spent some time trying to go around this problem with no success so far, help!

Azure SQL Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options set to ON. You cannot change the database default isolation level. However, you can control the isolation level explicitly on a connection. One way to do this, you can use any one of the following statements in Azure SQL Database before you BEGIN TRANSACTION:

SET TRANSACTION  ISOLATION LEVEL  SERIALIZABLE
SET TRANSACTION  ISOLATION LEVEL  SNAPSHOT
SET TRANSACTION  ISOLATION LEVEL  REPEATABLE READ
SET TRANSACTION  ISOLATION LEVEL  READ COMMITTED
SET TRANSACTION  ISOLATION LEVEL  READ UNCOMMITTED

SET TRANSACTON ISOLATION LEVEL controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server and spans batches (GO statement). All of the above works exactly the same as SQL Server.

In addition, the above statement "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" will set the isolation level to read committed snapshot isolation (RCSI). This isolation level is different from read committed (RC). In other words, the default behavior of “SET TRANSACTION ISOLATION LEVEL READ COMMITTED" in on-premise SQL Server is RC, but in Azure SQL Database is RCSI. If you want to use exact RC (not RCSI) behavior in Azure SQL Database, you have to set the lock hint to the SQL statement.


How to get N set of data base on one column example, my data

XXX yyy  order_time
x1  y1  2019/11/11 00:11:22
x2  y2  2019/11/11_00:11:22
x3  y3  2019/11/11_00:22:33
x4  y4  2019/11/11_00:33:44
x5  y5  2019/11/11_00:33:44
x6  y6  2019/11/11_00:44:55

To get all data, I can use:

select xxx,yyy,order_time from my_table where condition=bala;

To get 3 set of the order_time

select distinct(order_time) from my_table order by order_time limit 3;

But how to combine them together?
I want to get all data that the order time within the 3 set of the order_time.

expect would be:

XXX yyy  order_time
x1  y1  2019/11/11 00:11:22
x2  y2  2019/11/11_00:11:22
x3  y3  2019/11/11_00:22:33
x4  y4  2019/11/11_00:33:44
x5  y5  2019/11/11_00:33:44

Thanks!

MySQL 8+

WITH cte AS ( SELECT *,
                     DENSE_RANK() OVER (ORDER BY order_time) dr
              FROM my_table
              WHERE <conditions> )
SELECT xxx,
       yyy,
       order_time
FROM cte
WHERE dr <= 3

MySQL 5.+

SELECT my_table.*
FROM my_table
JOIN ( SELECT DISTINCT order_time
       FROM my_table
       WHERE <conditions>
       ORDER BY order_time
       LIMIT 3 ) times USING (order_time)
/* WHERE <conditions> */


Missing Polybase enable configuration on SQL Server 2017 I installed SQL Server 2017 Enterprise (14.0.3162.1) on my box. With the polybase (Scale-out Group). But when I try to enable through:

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE WITH OVERRIDE;

It shows:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]
The configuration option 'polybase enabled' does not exist, or it may be an advanced option.

So I used:

sp_configure 'show advanced option', 1;
GO
RECONFIGURE
Go

and check by:

select * from sys.configurations where name like '%poly%'

only:

polybase network encryption
allow polybase export

shows up.

How can I enable this feature in this case?

Please let me know if I missed any.

SQL 2017 is not good with Polybase. Currently, I am using SQL 2019 for this and all is good.