texts:
We are just entering Azure world and will be migrating SQL server soon.I am trying to test throughput on Azure VM (DS13_V2) which is supposed to give 384MB/Sec as per documentation.

Here is the configuration :

a) No of cores : 8

b) Attached 2 disks (1TB each formatted with 64K block size) to the VM which          are P30 disks(200mb/sec).

c) I have striped 2 disks into one and called it F: drive with total 2TB which in theory is capable of giving 400mb/sec.

Test:
I ran SQLIO (with Test file size - 20GB on the striped drive F:) with multiple combinations and looks like throughput is throttled at 256mb/sec consistently.
I even ran diskspd which also gives me the same 256mb/sec throughput.

Question:
I am wondering where the bottleneck is or anything that i am missing as VM is capable of giving 384mb/sec and my striped disks are capable of giving 400mb/sec (200mb/sec each) wherein i should be seeing a throughput of 384mb/sec.

Please let me know if any suggestions would help me in doing a better storage test.


texts: SELECT Dynamic columns with values from other table

texts:
I need some help with a query. I want to select extra columns to a database query that are stored in another table. I will try to explain what I want to do. I have two tables a Task table and a TaskField table. When I query the task table then sometimes I need some extra fields selected from the TaskField table.

+-------------------+ +---------------------------+
|       Tasks       | |        TaskFields         |
+-------------------+ +---------------------------+
| TaskId PK int     | | TaskFieldId PK int        |
| TaskName string   | | TaskId FK -> Tasks.TaskId |
| TaskAuthor string | | TaskFieldName string      |
|                   | | TaskFieldValue string     |
+-------------------+ +---------------------------+

The rows in those tables will look like this. The Tasks table looks as follows

+--------+--------------+------------+
| TaskId |   TaskName   | TaskAuthor |
+--------+--------------+------------+
|      1 | Testing Task | Me         |
+--------+--------------+------------+

The taskfields table will contain this rows.

+-------------+--------+---------------+----------------+
| TaskFieldId | TaskId | TaskFieldName | TaskFieldValue |
+-------------+--------+---------------+----------------+
|           1 |      1 | Created_at    | 2017-31-05     |
|           2 |      1 | TaskLocation  | Berlin         |
+-------------+--------+---------------+----------------+

Now I want to dynamically select the data from taskfields into a row from table so I will get the following output.

+--------+--------------+------------+------------+--------------+
| TaskId |   TaskName   | TaskAuthor | Created_at | TaskLocation |
+--------+--------------+------------+------------+--------------+
|      1 | Testing Task | Me         | 2017-31-05 | Berlin       |
+--------+--------------+------------+------------+--------------+

I have no idea where to begin with this query and I don't even know if it's possible in mysql. I tried to start of with the following query.

SELECT `Tasks`.`TaskId`, `Tasks`.`TaskName`, `Tasks`.`TaskAuthor` FROM `Tasks`
JOIN `TasksFields` ON `Tasks`.`TaskId` = `TasksFields`.`TaskId`

This will return all rows joined but this is not the result I hoped for I need to dynamically append the colums to the result, but I have no idea how to get it done.


texts: Define a column as MD5 sum of other columns in MySql

texts:
Suppose I have following table -

id   name
--   ----
1    kshitiz

Is it possible to define another column that is the md5 sum (or some other hash) of values in the previous two columns?

id   name    hash
--   ----    ----
1    kshitiz 392b6be97993de246fe188ac5d0052a7


texts: Solutions to 4-byte utf-8 characters in mysql 5.1

texts:
I'm running into a problem that seems to be an issue w/ mysql 5.1s limitations on UTF8 characters.

From http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-utf8.html

Currently, MySQL support for UTF-8 does not include 4-byte sequences.

Our app is throwing periodic exceptions like

java.sql.SQLException: Incorrect string value: '\xF4\x80\x82\x8C  ...'

The ultimate solution looks to be migrating to 5.5 which supports UTF-8 > 3 bytes

Unfortunately this wouldn't be as simple as bouncing the instances under newer binaries.  Being a major version dump we'd need to do a full dump reload which will require some scheduled downtime.

Has anyone else had to deal this situation before?  Are there any good work arounds?

The naive approach seems to be have the app search and replace multi-byte sequences with question marks or � .  This seems pretty hacky and not a very palatable option to me or the developers.


texts:
I have this table with 16 rows only:

http://www.jassimrahma.com/temp/attendence_table.png

and I am using below code now to split the time of attendance into columns and getting this result:

http://www.jassimrahma.com/temp/attendence_result.png

but I am not happy with it! for example, 7th July, there is only one fingerprint so it should be only F1 but it's repeating it in F1, F3 and F4.

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 1), ',', -1) AS F1,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 2), ',', -1) ,NULL) AS F2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 4), ',', -1) AS F4
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

How can I fix this please?

Thanks,
Jassim


texts: MySQL Router cannot connect to MySQL after server restart

texts:
I have such a configuration:

3 MySQL Servers (v.5.7.17):

[A]. 192.168.0.10:3306

[B]. 192.168.0.20:3306

[C]. 192.168.0.30:3306

MySQL Router 2.1 (192.168.0.30:3308)

[router.cnf]

[DEFAULT]
plugin_folder  = /opt/mysql/router2.1.n/lib/mysqlrouter

[logger]
level = DEBUG

[routing]
bind_address = 192.168.0.30:3308
mode = read-write
destinations = 192.168.0.10:3306,192.168.0.20:3306,192.168.0.30:3306

MySQL router works fine if all servers are UP (alive).
But if we restart all the 3 servers (A,B,C) then MySQL Router says:

“Can’t connect to MySQL server”

Any idea?

P.S. In order to make it work again I have to restart MySQL Router so I am wondering whether MySQL Router can figure out it itself.


texts:
I have been tasked with encrypting all data on an SQL database (SQL 2016 standard).

However this doesnt support TDE (Transparent Data Encryption) when I try I get the below error.

  Transparent Data Encryption is not available in the edition of this
  SQL Server instance. See books online for more details
  on feature support in different SQL Server editions.

Product Version:          13.0.4001.0
Product Name:             SQL Server 2016
Product Level:            SP1
Product Edition:          Standard Edition (64-bit)

So I am guessing my only option is to use "always encrypted"?
I have opened the wizard and can only pick columns one at a time, is there a way to select all columns in all tables and encrypt them?


texts: Which is better: more rows or more columns in a student attendance table?

texts:
I want to create an attendance table for student attendance. Which structure should I go for?

I have two options.

attendance(student_id,date,is_absent,reason)
attendance(student_id,d1,d2,d3,d4,...,d30,d31,is_absent,reason)

Scenario: assuming 100 students in which 50 are absent all 31 days for about 5 months. In case 1 with the assumed scenario the number of total rows is 50 absent * 31days * 5month  =7750
At the same time case 2 will create 100 rows irrespective of number of absentees and the respective data is filled if the student is absent. So in total in case 2 100 * 5 months = 500 rows and 500 * 31 cols = 15500 cells (scenario doesn't matter in rows and cols but the number of cells filled is 50 absent * 31 days * 5 month = 7750)

Which one should I go for and would it be fast in querying when I query for attendance report or anything similar?
PS: MySQL as database and queries are sent from PHP


texts: Can I rely on functions being executed first in SQL

texts:
Please consider the following script:

create or replace function f(p_limit in integer) return integer as
begin
  set_global_context ('limit', p_limit);
  return p_limit;
end;
/

create view v as
select level as val from dual connect by level<=sys_context('global_context','limit');

select f(2), v.* from v;

/*
F(2)                   VAL
---------------------- ----------------------
2                      1
2                      2
*/

select f(4), v.* from v;

/*
F(4)                   VAL
---------------------- ----------------------
4                      1
4                      2
4                      3
4                      4
*/

Can I rely on f(x) being executed before the context is read inside the view, as it has been in this test case run on 10.2?


texts: ORA-00119 & ORA-00130 when trying to startup Oracle

texts:
I'm having a problem with an Oracle database. Yesterday everything was working fine, I had an application that was using the database and there was no problem. At the end of the day I installed MySQL on the same server (I don't think it can be related but anyway..) and had to restart the server.
Today when I was using my application to access the Oracle database I had a connection error:

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Then I went to the server. All the services are up and the database is down.
When I try to login normally, I get an error saying that Oracle is not available, and can only connect as sysdba. Then when I try to start the database up I get the following errors:

Any help is apreciated. Thanks in advance!


texts: mysql RANGE partitioning - how do I find partition boundaries?

texts:
I am implementing partitioning support for mysql in eludia engine
I need to compare partitioning description in model with actual database contents and alter table, if necessary.
To extract all partition info about table I use information_schema.partitions
ALTER TABLE foo PARTITION BY RANGE (is_archive)
(
 PARTITION p0 VALUES LESS THAN 1,
 PARTITION p1 VALUES LESS THAN MAXVALUE
);
SELECT * FROM information_schema.partitions WHERE table_schema = database() AND table_name = 'foo';

I've inspected information_schema.partitions, it has partition_method, partition_expression but not ranges (LESS THAN 1, LESS THAN MAXVALUE parts)
I know I can extract this from SHOW CREATE TABLE. It is hard way:(
How should I programmatically retrieve partition boundaries in mysql?
Enviroment: mysql 5.1+


texts: InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

texts:
I am designing forum database  using InnoDB and got few questions , Could any one please help me to clarify them?

Posts and Comments table design below

CREATE TABLE `my_posts` (
  `forum_id` mediumint(8) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,          // not an auto increment
   subject   varchar(200) NOT NULL,
  `details` text NOT NULL,
  `access` tinyint(3) unsigned NOT NULL,  //private,friends,public
  `created_by` int(10) unsigned NOT NULL,
  `created_on` int(10) unsigned NOT NULL,
  `updated_on` int(10) unsigned NOT NULL,
  `comment_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `ip_address` int(10) unsigned NOT NULL,
   sphinx_unique_id  int(10) unsigned NOT NULL,  // not an auto increment
   PRIMARY KEY (`forum_id`,`post_id`,created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `my_posts_comments` (
  `forum_id` mediumint(8) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,          // not an auto increment
  `comment_id` int(10) unsigned NOT NULL,
  `details` text NOT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `created_on` int(10) unsigned NOT NULL,
  `ip_address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`forum_id`,`post_id`,comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have queries based on forum_id, post_id, created_by and also sorting on updated_on , comment_count

SELECT * FROM my_posts  WHERE access=public ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts  WHERE access=public ORDER BY comment_count DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=?  and access=public ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=?  and access=public  ORDER BY comment_count DESC   //OR ASC
SELECT * FROM my_posts WHERE created_by=?  ORDER BY updated_on DESC     //OR ASC
SELECT * FROM my_posts  WHERE created_by=(Friends_ids) AND access=(public,friends)   ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=? AND  post_id=?
SELECT * FROM my_posts_comments WHERE forum_id=? AND post_id=?

1) Since the composite primary key on forum_id, post_id, created_by , I see problem accessing it just conditioning on created_by . What is the best design to query on  created_by? Can I remove created_by from composite key and just add index for it . I will loose Cluster  performance here ..

2) What is the best way to increment post_id on forum level ? Or can I increment at table level . for example

sample data ( Forum Level )

 FORUM_ID  POST_ID
    1         1
    1         2
    1         3
    1         4
    2         1
    2         2
    2         3
    2         4
    2         5

Or table level

 FORUM_ID  POST_ID
1          1
1          2
1          3
1          4
2          5
2          6
2          7
2          8
2          9

If I can use the above table level increment , I can remove  sphinx_unique_id column since I am using it only for sphinx search . Also in comments table , I can remove forum_id column .

3) All the queries I have sorting on  updated_on and on comment_count . These two are not of PK , so the first two queries looks like a major problem ..

Thanks all for your help


texts: How do I export data using PostgreSQL COPY command in required format?

texts:
I have some sample data in my PostgreSQL 9.5 db like follows:

ID(integer)   value_1(numeric)   value_2(numeric)  array(text)
1             25.2               14.1              1,112,292,19.7
2             11.3               5.9               2,30,110,22.3,60,270,30.1

I am exporting this data in my_table using Postgres COPY command to a custom text file like this:

Copy
(
Select
ID, value_1,
value_2, array
from
my_table
Order by ID
) to '~my_path/output.str' With DELIMITER ',';

I get the exported output like:

1,25.2,14.1,1\,112\,292\,19.7\
2,11.3,5.9,2\,30\,110\,22.3\,60\,270\,30.1\

However, my desired output is:

1,25.2,14.1,1,112,292,19.7
2,11.3,5.9,2,30,110,22.3,60,270,30.1

How do I remove these unwanted characters \ in COPY command export output?


texts: Unique indexed column in where clause and mysql execution after finding first row

texts:
Suppose in a table there are 2 columns: login and password

login is indexed as unique index, password not indexed.

query: SELECT * FROM mytable WHERE login = 'Jhon'
query: SELECT * FROM mytable WHERE login = 'Jhon' AND password = '123'

After finding first row, will mysql stop or continue searching?

What will mysql do in first case 1. and 2. ?


texts: Best way to backup & truncate transaction logs on a schedule

texts:
I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance.

So for a while I've been having my SSIS overnight process run a Execute SQL Task to perform the backups - basically running master.dbo.xp_create_subdir to ensure the destination folders exist, and then BACKUP DATABASE [DbName] TO  DISK = 'G:\Backups\DbName\DbName.bak' WITH INIT.

Whenever that task failed, the rest of the process would abort and I'd get a notification, and come in the next morning to notice the drive for transaction logs was filled to capacity, and so I'd manually truncate them and move on... until the story repeated itself and the transaction logs outgrew the available disk space again.

The "manual truncate" script looks like this:

use Staging;
alter database Staging set recovery simple
alter database Staging set recovery full
dbcc shrinkfile ('Staging_log', 0, truncateonly);
go

So I'm growing tired of this, and I decided to try and do things properly instead, and follow the steps here and create an actual maintenance plan:

Thing is, I've never done this before, so I have a few questions:

Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?
Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?
The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?
Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of G:\Backups. Does that make sense?
Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?

Sorry if this is too many questions for one post, if needed I'll edit and ask multiple questions instead - I think they're all tightly related though.


texts: crossplatform mysql_install_db script in mysql 5.5.34 on Windows

texts:
I downloaded mysql-5.5.34-win32.zip and found out there is mysql_install_db.pl script.
However mysql 5.5 documentation says:

  mysql_install_db is a shell script and is available only on Unix
  platforms. (As of MySQL 5.6, mysql_install_db is a Perl script and can
  be used on any system with Perl installed.)

https://dev.mysql.com/doc/refman/5.5/en/mysql-install-db.html

mysql-5.5.34-linux2.6-i686.tar.gz archive contains shell version of mysql_install_db script.

Why does mysql 5.5.34 for Windows contain this script? Is it an issue in 5.5 documentation?

UPD 1:

I found several comments in bugs.mysql.com which confirm that this perl script is useless on Windows:

  On Windows you don't need to use that script.

  If we  don't need it,
  why not remove it from source tree ?

  If those files are nor required
  for compilation in Windows, and they are not used by any other file, I
  agree with you they should be removed.

http://bugs.mysql.com/bug.php?id=60384

or

  I am not sure if this script is intended to be executed on Windows at all. Our manual clearly says: "It is unnecessary to run the mysql_install_db script that is used on Unix."

http://bugs.mysql.com/bug.php?id=52183

or

  mysql_install_db is not supported on Windows and scheduled for removal from our Windows packages.

http://bugs.mysql.com/bug.php?id=35105

But is there any official comment that this script really useless in mysql 5.5 on Windows?


texts: MySQL Workbench 5.2 edition - Unable to establish the connection

texts:
I have installed MySQL (mysql-workbench-gpl-5.2.37-win32.msi ) which is a Windows Installation Workbench.

Couple of times, I have done re-installation on my computer. Now, I am not able to establish the connection.

Following error message is thrown, while i try to connect to the database from Workbench

Check that mysql is running on server 127.0.0.1
Check that mysql is running on port 3306 ( note : 3306 is the default, but this can be changed )
Check the root has rights to connect to 127.0.0.1 from your address (mysql rights define what clients can connect  to the server and from which machines)
Make sure you are both  providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you’re connecting from


texts: Signal handler on mysql connection break

texts: I want to know if there is any signal that I can catch by which I can know that my mysql connection with the server has broken in c programming? I know I can check at query time whether my connection is active or not. But I just want to know if there is any signal I can catch so that I will not have to go though any connection checking process.


texts:
I have a mongos with 2 shard servers (and 3 config servers)

sh.status()
--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("55ae2ddab9e8641e489ce39b")
}
  shards:
        {  "_id" : "shard0000",  "host" : "192.168.212.182:27018" }
        {  "_id" : "shard0001",  "host" : "192.168.212.106:27018" }
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "Vision_1282",  "partitioned" : true,  "primary" : "shard0001" }
                Vision_1282.Group
                        shard key: { "groepNr" : 1, "valId" : 1 }
                        chunks:
                                shard0001       1
                        { "groepNr" : { "$minKey" : 1 }, "valId" : { "$minKey" : 1 } } -->> { "groepNr" : { "$maxKey" : 1 }, "valId" : { "$maxKey" : 1 } } on : shard0001 Timestamp(1, 0)
        {  "_id" : "db",  "partitioned" : false,  "primary" : "shard0000" }

I made the shard key as follows:

db.runCommand({ shardcollection : "Vision_1282.Group", key : {groepNr : 1, valId : 1}})

I insert about 10.000 records, where the groepNr is 1, 2, 3 or 4 and a valId can be between 1 and 4001.

When I look at the shardDistribution I see that shard001 only has records where groepNr = 1 and valId = 1.  All other records are in shard002.

I thought that the value behind the key value (here groepNr and valId) is 1 for ascending and -1 for descending.

PS. in PHP code, where I do the inserts I also ensure to use the index.
$collection->ensureIndex(array("groepNr" => 1 , "valId" => 1));

Did I do something wrong with creating the shardkey?


texts: Are mongodb bulk inserts really increase server perfomance (and faster)?

texts:
Bulk inserts always are faster in client side because of saving round-trips, but at the server-side is there any sensible performance improve?

It seems the code for bulk inserts, just loops over documents and inserts them one-by-one. The 2.6 documentation says "improved bulk insert" in 2.6, but it seems it's not related to performance.

If there is a real performance improve in bulk-inserts (that is not achievable using threads), what and where the improvements? We know that mongodb does not flush anything on every insert (flushes journal every 100ms, and all files every 30s, by default), so inserting multiple documents in one thread (via bulkinsert) or concurrently by many clients, should not have any effect on disk access. (and probably on index build or write lock)

(This question also is related to "https://stackoverflow.com/q/16533420/326792" but the accepted answer only mentions client-side improvement not server-side)


texts: How to normalize a database which uses tables as references to other tables?

texts:
I'm trying to create the database for my application, but I cannot manage to normalize my data on a MySQL database.

I have a Types of map entity
The Types must have one or more Models associated to it, in a particular order
The Models have a Grid, is_prediction flag and an Origin associated to it
There can be more than one Model using the same Grid, Origin and is_prediction condition, differing only on the name of the model
Not all Origins provide all the Models
The Types can only have Models associated to it that have the same condition of [Grid, Origin, is_prediction]

I tried to create a table types_hierarchy, using grid_id, origin_id and is_prediction as foreign key, but it seems wrong, according to the answer on my other question here.

How can I create a normalized database for my needs?

This is what I tried to do:


texts: Change Location of VALIDATE RECOVERY FILES work area

texts:
Is there a way to tell the VALIDATE RECOVERY FILES command to use a different location with more available disk space?
When I run the command in RMAN, it fails with the following error stack:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 06/05/2012 12:02:42
ORA-19502: write error on file "/opt/app/oracle/product/11.2.0/dbhome_1/dbs/...
ORA-27072: File I/O error
Additional information: 4
Additional information: 1071488
Additional information: 286720
ORA-19502: write error on file "/opt/app/oracle/product/11.2.0/dbhome_1/dbs/...

This appears to be due to the fact that the validation is using the database home for it's work area and there isn't enough disk space there.  I can actually see the 8GB free there drop to zero before the command fails and the space is returned.
This location is not affected by setting the location in the format when allocating the channel for the device.
DB_RECOVERY_FILE_DEST is set to '+FRA', but currently most of the backups are in /u01/back rather than ASM.  '+FRA' does have some control file backups.
Update:
I haven't found a way to do this, but I also haven't found anything definitive saying it is not possible.  There is an Oracle community topic discussing this, but no resolution.  Interestingly RESTORE ARCHIVELOG ALL VALIDATE and RESTORE VALIDATE DATABASE CHECK LOGICAL do not exhibit this behavior, which may be why more people haven't run into it.
Update 2:
I opened a SR with Oracle support to see if they can shed light on this question.
Update 3:
Oracle support was able to reproduce this issue in house and they are treating it as a bug.


texts:
I have the following query, the alias it's called DELAY, works fine, but when I try to run the first query adding AND DELAY < 0, the alias is not recognized.

I tried to create the alias QUERY AS DELAY or QUERY AS "DELAY" but no success.

The strange thing is that the alias DELAY in the order by doesn't generate errors.

 SELECT ITK_TCK_ID,
                   ITK_OPENED_DATE,
                   TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE,
                                                SYSDATE,
                                                ITK_NORMAL_BEGIN,
                                                ITK_NORMAL_END,
                                                ITK_WEEKEND_BEGIN,
                                                ITK_WEEKEND_END,
                                                ITK_FREEDAY_BEGIN,
                                                ITK_FREEDAY_END),
                                         GET_HS(ITK_OPENED_DATE,
                                                SYSDATE,
                                                ITK_NORMAL_BEGIN,
                                                ITK_NORMAL_END,
                                                ITK_WEEKEND_BEGIN,
                                                ITK_WEEKEND_END,
                                                ITK_FREEDAY_BEGIN,
                                                ITK_FREEDAY_END)) -
                                   ITK_EXPIRATION_TIME,
                                   2)) DELAY
              FROM INSTANTIATED_TICKLERS
             WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID
             AND DELAY < 0
             ORDER BY DELAY DESC;

Any suggestions?


texts: After upgrading from PG 9.3 to 10 indices became corrupted

texts:
So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier and simpler, but we had our reasons.
Here is some data about the environment

We keep all db data on separate virtual hard drive for flexibility and possible changing of its size.
Before upgrade I did not ensure that databases were initiated with the same locales pl_PL.UTF-8, pg_upgrade told me about that
Old machine: Ubuntu 14.04, system locale pl_PL.UTF-8
New machine: Ubuntu 18.04, system locale en_US.UTF-8

I executed following procedure

Turned off the old machine
Detached the disk with db from the old machine
Attached virtual disk with db to the new machine
Upgraded the database with pg_upgrade, it checked the locale, at first they did not match, I reinited the db and it worked like a charm
gathered new statistics

After that db has started and we launched the application. It turned out quite fast howeber, that there is some problem, we dig into that and found out the text indices has become corrupted. Application by using queries could not fetch the result, we got us some not wanted duplicates as well.
After reindexing the db started to operate normally, all the problems have disappeared.

I have reproduced the issue to explain it, but I have ran out of ideas. Here is what I have found.

such query does not work (does not return a result). It uses an index

select full_username
from account
where full_username = 'pattern';

but these ones do

select full_username
from account
where full_username like '%pattern';

select full_username
from account
where upper(full_username) like 'pattern'; --this one under condition pattern in dtabase is written with uppercase

select full_username
from account
where full_username = ('pattern' COLLATE "pl_PL");

select full_username
from account
where (full_username COLLATE "pl_PL") = 'pattern'

after turning off index scans (by forcing seq scan) it started to work

SET enable_indexscan = off;
SET enable_indexonlyscan = off;
SET enable_bitmapscan = off;

select full_username
from account
where full_username = 'pattern';

Notes:

the used pattern was always present in the table, I ensured that, problem occured only with index scans
pattern contained various characters (upper- lower case, special characters etc.)
problem occurs in every possible combination of collation settings. I have upgraded db with en_US locale on OS, with pl_PL locales set, I have changed it for current session, changed it globally in OS and restarted the machine, changed it in postgres config file, nothing works, the first query (the one not modifying the data) does not return result

I have searched but was not able to explain that. One of the queries shows, that the problem most probably lies in collation. This has definitely something to do with moving the data to another machine.
My question is - what have gone wrong and why were the indices corrupted. I still have the environment to run some tests, so if any more data is needed I can provide it. I will be grateful for any help with that


texts: sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?

texts:
I have this table in one of my databases:

IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL
DROP TABLE [dbo].[repl_Gender_Type]
GO
CREATE TABLE [dbo].[repl_Gender_Type] (
[Gender_TypeID]  CHAR(1)                          NOT NULL,
[Gender_Desc]    VARCHAR(20)                      NOT NULL,
[Create_Date]    DATETIME                         NOT NULL,
[Create_Userid]  VARCHAR(20)                      NOT NULL,
CONSTRAINT   [PK__Gender__46DD686B]  PRIMARY KEY NONCLUSTERED ([Gender_TypeID] asc))

there are only 3 records on it.

even after running the following command:

update statistics dbo.repl_Gender_Type with fullscan

I get no results when running the following query:

select SP.*
from  dbo.sysarticles A
OUTER APPLY [sys].[dm_db_stats_properties](a.objid,1) sp
where a.objid = OBJECT_ID('dbo.repl_Gender_Type')

so dm_db_stats_properties does not keep track of small tables?
what is the work around to find the number of records in the table?

  sys.dm_db_stats_properties returns an empty rowset under any of the
  following conditions:

  object_id or stats_id is NULL.
  The specified object is not found or
  does not correspond to a table or indexed view. The specified

  statistics ID does not correspond to existing statistics for the
  specified object ID.

  The current user does not have permissions to
  view the statistics object. This behavior allows for the safe usage of
  sys.dm_db_stats_properties when cross applied to rows in views such as
  sys.objects and sys.stats.

none of the above is right, me thinks.

I like to use this script below, that comes from this question:

sp_updatestats vs Update statistics

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;


texts: How to Delete rows based on a combination of columns having a certain count


texts:
I can not select the current SCN, with the command that I previously could.

I tried with this command:

select current_scn from v$database;
ORA-00942: table or view does not exist

select dbms_flashback.get_system_change_number from dual;
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

I get SCN only with this command:

select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
           835484


texts:
I have seen how to do multiple counts for different tables, but never how to combine them all into one.  I have a MySQL DB where I am running the following query:

SELECT characters.name, COUNT(*) AS wiki_unlocks
    FROM wiki_items
    INNER JOIN characters
    ON characters.character_id=wiki_items.character_id
    GROUP BY wiki_items.character_id
    ORDER BY wiki_unlocks DESC
    LIMIT 10;

This is giving me the following which is great:

name          wiki_unlocks
player1       2
player2       1

I want to get a combined count of all of the 'wiki_xxxx' tables.  For example I want 'wiki_items'(above) + 'wiki_armors' + 'wiki_weapons' + ...

Thanks for any help :D


texts: Reordering a postgresql timescaledb hypertable

texts:
Is the standard postgresql way also possible on a timescaledb hypertable?

CLUSTER [VERBOSE] tablename [ USING indexname ]

Example:

Creating table & hypertyble

CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data VARCHAR(8) NOT NULL,

SELECT create_hypertable('l1', 'timestamp', chunk_time_interval => interval '1 day');

Produces an index like this (can be viewed using this command):

l1        | l1_timestamp_idx                                               | CREATE INDEX l1_timestamp_idx ON public.l1 USING btree ("timestamp" DESC)

And than CLUSTER the data like this

CLUSTER l1 USING l1_timestamp_idx;
stdout: CLUSTER

It looks like it works.. But is this recommended? And is it possible to reverse the order of the CLUSTER command? Meaning, sorting it with the lowest timestamp first.


texts:
Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits 100% and the selection takes too long. I need to possibly save those averaged values so that SQL does not have to select all those records on each request. What can be done?

  SELECT DISTINCT
         CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour,
         MIN([timestamp]) as TimeStamp,
         AVG(MyField) As AvgField
    FROM MyData
   WHERE TimeStamp > '4/10/2011'
GROUP BY CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR)
ORDER BY TimeStamp


texts: Cannot choose Storage option on a table in SSMS for standard edition

texts:
I am looking into partitioning of tables for SQL Server on two different machines. One machine runs Microsoft SQL Server (RTM) 2016 Developer Edition, and the other is running Microsoft SQL Server 2016 (SP1) Standard Edition.

As far as I can see from this entry, SQL Server 2016 (SP1) Standard Edition should support partitioning.

However, from the SSMS on the Developer Edition, I can partition a table using the build-in wizard by choosing the "storage" option on a table, but this option is missing from the SSMS on the machine with the Standard Edition. What might be the cause of this? Does SSMS change appearance depending on which SQL Server edition it is running against?

Here is the menu in the SSMS for the Developer Edition:

And here is the menu in the SSMS for the Standard Edition:


texts: Error when trying to bakup from SQL Server 2012 to Azure

texts:
I'm trying to do a backup from SQL Server 2012 to Azure (free account), but I'm getting this error:

'URL' is not a recognized Device Type option

I´m writing the instruction like this:

BACKUP DATABASE DB_Verifica
TO URL = N'https://myaccount.blob.core.windows.net/myContainer/MyDatabase.bak'
WITH CREDENTIAL = 'SQLBackups';
GO

The size of the DB is small (less than 2 GB), I'm just trying out the Azure services.

The SQL Server 2012 does not recognize the word URL, I looked up for a solution on the web and I founded I was needing the SP1 Compact 2 or later versions to install, so I installed the mos recently version of SP1 Compact 4 and.. did not work.

Do you have any suggestions to solve my problem?

Thank you so much in advance!


texts: Why does null and true evaluate to null but null or true evaluates to true?

texts:
Why does null and true evaluate to null in PostgreSQL but null or true evaluates to true?

I would have thought using null with and or or would've evaluated to null in both cases?


texts:
I have two servers.

Eg. server A and server B.

I want to select data from server A and insert into server B using MySQL.

How to make it?  I can access server A's database remotely from server B but I can't do select and insert query. Because they are in different usernames.

Any ideas?


texts: How do I remove duplicate records in a table?

texts:
I have a table table1 with  1 millions rows of data.

I want to remove all the duplicate records in the table1

I was looking at this link:

  How do I remove duplicate records in a join table in PostgreSQL?

Can you tell me how to do this below query in SQL server?

 DELETE FROM questions_tags q
WHERE EXISTS (
   SELECT 1
   FROM   questions_tags q1
   WHERE  q1.ctid < q.ctid
   AND    q.question_id = q1.question_id
   AND    q.tag_id = q1.tag_id
    );


texts:
I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but I am using RAISERROR in every single unit test, I don't want to add another line of code for every test. (My unit tests are already wordy enough!) Is there a way to perform an inline cast/convert within the RAISERROR parameter list? Or is there another way around this deficiency?

Update:
So ultimately what I wish I could do is this:

RAISERROR('Unit Test FAILED! %f', 11, 0, @floatParm)

Unfortunately, RAISERROR doesn't handle %f or floats in general. So I have to do this instead:

DECLARE @str VARCHAR(40) = CAST(@floatParm AS VARCHAR(40))
RAISERROR('Unit Test FAILED! %s', 11, 0, @str)

...which just looks like a mess when it's scattered through dozens of Unit Tests. So I'd like to boil it down to something like this:

RAISERROR('Unit Test FAILED! %s', 11, 0, CAST(@floatParm AS VARCHAR(40))

But that gets me a Incorrect syntax near 'CAST' message. I don't understand why that's illegal, but it is. Is there another "one liner" I could use here instead?


texts: What is a good option to replace a daily SQL server snapshot replication job?

texts:
I am looking for a good alternative to replace SQL Server Snapshot Replication that replicates some tables to a reporting server everyday.

I'd prefer an agent job that I can schedule to run every night.

I want to completely replace replication. I am thinking of a process that can be automated to copy the tables to a new database (ETL maybe). I was hoping to get some ideas here and create a plan that can effectively do what the replication has been doing.

We'd like to consider reducing our server footprint from two servers down to one server, with a database for the production OLTP load, and a separate database for the reporting workload.

Edit/Update: I have created an SSIS package to repopulate the tables, it has been working well and updating changed records (In DEV). I will check if having both PROD & Reporting Databases on the same host adversely impact performance.


texts: Easy way to replace an YAML array of IDs with strings from a joined table

texts:
I have a database full of camp data. Each camp has many campers and has many camp_weeks.

In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project requires a query for display. Here's a sample row from the campers table:

19770,'Daniela',41,'---
- 406
- 407
')

My current query (based on the excellent tip from this blog):

SELECT
  campers.name,
  camps.id,
  camps.title,
  regexp_split_to_array(trim(regexp_replace(campers.camp_weeks, E'[\\n\\r''-]+', ' ', 'g' )), E'\\D+') AS week_array
FROM
  campers
LEFT JOIN camps ON campers.camp_id = camps.id
GROUP BY camps.id, campers.id

returns the campers, with a string/array of camp ids like this:

====================================
|  Daniela | 41 | sports | 406,407 |
====================================

I would like the query to return

============================================
|  Daniela | 41 | sports | cricket, tennis |
============================================

What would be an easy way to do this?

Performance is not a big issue for this query and the simpler the query is the better it will be.

Here's a SQL Fiddle.


texts: MySQL 5.7 GetDiagnostics is always returning null for SQLSTATE What am I doing wrong?

texts:
I have the following Handler and the sqlexception is being caught, but GET DIAGNOSTICS is not returning any information - they are all NULL.

I have the following code:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN

      GET DIAGNOSTICS @cno = NUMBER;
      GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

      GET DIAGNOSTICS CONDITION 1
        currentcode = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

    GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
    GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;

  SELECT @current, @stacked, @errno INTO currentcode,stackedcode,errorcode;

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;

CALL debug_msg(1,@full_error);

    SET _rollback = 1;
    SET ResultCode = CONCAT('currentcode: ' , currentcode, ' , stackedcode: ' , stackedcode);
  END;

I admit that the condition_number element - I do not understand if it represents the index of conditions that were caught or if it is the error number to look at and the documentation on the MySQL site says condition_number (ok I get that it is a condition_number.)


texts:
I am working on a query that requires the retrieval of 20 random rows from a cash book table, which I am doing using 'Top 20 & order by newId()'.
Let me explain: The cash book table is a list of customers with Account Number, Name, Balance. Each customer, depending on the number of Accounts they have will return a different number of rows. ie: one account will return one row, 7 accounts will return 7 rows etc.
The number of Customers can be anything between 150 - 300 depending on which cash book i'm looking at. So if each Customer had 3 accounts there could potentially be 900 rows in that particular cash book table.
What i need to do is return 20 'groups of results'.
So if customer one has 6 accounts, it returns 6 rows but only classes that as a single return, meaning that it will return the account details for another 19 customers irrespective of the number of accounts they have.
That's probably not very clear so i apologise, but if anyone can help i would be very grateful. regards, rich


texts:
It is a Java and Hibernate based application that processes rows in a table. The application when launched loops through rows in a table, executes anywhere from 10-25 queries per row and inserts results into two other tables (one mater record, and a bunch of detail records).
The processing speed is about 370 rows per minute in the first 30 mins or so. After that, it slows down significantly to barely 14 rows per minute. I am trying to pinpoint the issue whether it is database or application or OS related.
Any thoughts?
Thanks in advance.


texts: Should I use SQL Azure to store product descriptions and links to images in Azure Blob

texts:
For an html/Css/JQuery public Azure website to be coded using Zurb Foundation html5 responsive framework, I'd like to use SQL Azure (web edition) to store product descriptions and links to images that I store in Azure Blob Storage.

Therefore I would want to link my SQL Azure to my Zurb Foundation Azure Website and have an easy interface (I'm an absolute beginner in dba) to insert and update product descriptions. I'd like to be able to import the product descriptions, links and whatever other useful info for SEO from csv files.

Will a relational db like SQL azure perform well for this application (even if it scales to a few hundred to say three thousand concurrent users) or should I select from a document database or key/value database application (mongoDB, CouchDB etc?) that I would host in Azure ?


texts:
I am running a PostgreSQL 9.5 datbase. There is a master and a slave that is configured as hot_standby using streaming replication (I don't think this is related).

On the master I have the following wal settings:

postgres=# select name, setting, unit from pg_settings where name like '%wal_%';
             name             |   setting   | unit
------------------------------+-------------+------
 max_wal_senders              | 5           |
 max_wal_size                 | 128         | 16MB
 min_wal_size                 | 64          | 16MB
 wal_block_size               | 8192        |
 wal_buffers                  | 983         | 8kB
 wal_compression              | off         |
 wal_keep_segments            | 10          |
 wal_level                    | hot_standby |
 wal_log_hints                | off         |
 wal_receiver_status_interval | 10          | s
 wal_receiver_timeout         | 60000       | ms
 wal_retrieve_retry_interval  | 5000        | ms
 wal_segment_size             | 2048        | 8kB
 wal_sender_timeout           | 60000       | ms
 wal_sync_method              | fdatasync   |
 wal_writer_delay             | 200         | ms
(16 rows)

I guess the pg_xlog directory should grow to be 2GB max (max_wal_size = 128 * 16MB = 2GB), but it is 5GB now.

I don't understand why.


texts:
I've got the following query to measure the schema's size on my DB:

WITH schema_size AS (
SELECT
tab.table_catalog AS database_name,
tab.table_schema AS schema_name,
tab.table_name,
pg_total_relation_size(table_schema || '.' || tab.table_name) AS table_size_total,
pg_relation_size(table_schema || '.' || tab.table_name) AS table_size
FROM information_schema.tables tab
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
), pretty_size AS (
SELECT
database_name,
schema_name,
pg_database_size(database_name) AS database_size_bigint,
pg_size_pretty(pg_database_size(database_name)) AS database_size,
sum(table_size_total) AS schema_size_bigint_total,
pg_size_pretty(sum(table_size_total)) AS schema_size_total,
sum(table_size) AS schema_size_bigint,
pg_size_pretty(sum(table_size)) AS schema_size
FROM schema_size
GROUP BY database_name, schema_name
)
SELECT
database_name,
schema_name,
database_size,
schema_size_total,
schema_size,
((schema_size_bigint_total * 100) / database_size_bigint) AS perc_total,
((schema_size_bigint * 100) / database_size_bigint) AS perc
FROM pretty_size;

However, when running it I get this information:

My DB is 2 TB and that schema is 1.7 TB.

Question:

Is that related to the toast tables? How can I decrease the size of it?


texts: Getting the syntax right for MySQL select with inner join

texts:
Using MySQL version 5.1.73 and phpmyadmin 4.0.10.16.

I am unable to get this past the syntax checker:

select * from courses as co , course_type as ct
left join course_leaders as cl on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id
where co.course_type_id = ct.course_type_id
order by short,start_date ASC

It insists that there is no column "co.course_leaders_id1" which there is indeed.

Any ideas anyone?

ok, this is the result I came up (with the help from below - thanks)

select * from courses as co
left join course_leaders as cl on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id
inner join course_type as ct on co.course_type_id = ct.course_type_id
order by short,start_date ASC


texts:
I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III.

I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. Moreover, I need to update database statistics via ANALYSIS so query executor could select the best plan and I also need to REINDEX to regenerate indexes.

The windows I use for that purposes is here: http://www.pgadmin.org/docs/dev/maintenance.html

Some questions regarding the behavior and options:

if I run VACUUM without selecting any options below (FULL, FREEZE, ANALYZE) what PostgreSQL will do? Will it only free some space and that's it?
If I run VACUUM with Analyze option is it the same as running VACUUM without Analyze and then running ANALYZE separately. What is working faster?
Is it worth to do REINDEX after doing VACUUM and ANALYZE? I can see in verbose messages that indexes are affected somehow by VACUUM without options. Should I run REINDEX to make my indexes works faster?


texts: Getting unique names when the IDs are different (DISTINCT ?)

texts:
I've got this table:

id  | name
1   | John
2   | John
3   | Peter
4   | Mike
5   | Mike

And I want to get the unique names, getting the latest one in the list. I could go for:

SELECT DISTINCT name FROM table ORDER BY id DESC LIMIT 2

This would get me: Mike and Peter. All good. However, I also need the IDs, so then DISTINCT does not work anymore. What would be the right query to get:

5   |   Mike
3   |   Peter

In words: the latest 2 entries that are unique


texts:
I'm having a MySQL dual-master Replication of MySQL, each one of the servers have 21 GB memory, I'm now searching for a clustering solution for this project, It's possible to have 6 server: two of them with 20 GB of RAM and others with 10 GB of RAM. in the MySQL Cluster documentation it has written below formula for total memory and each node RAM, It seems that I need much more RAM of my existing Replication, and It's hard to make my company agree with this kind of RAM requirement, besides the purpose of clustering is to instead of making a big server use smaller (in resources) servers attached to each other but with this formula each node of clustering is much bigger than my existing servers.

  If designing a completely new database, the following calculations can
  be used to help determine the approximate memory sizing requirements
  for the data nodes:

  •  (in memory) Data Size * Replicas * 1.25 = Total Database Memory
  Requirements Example: 50 GB * 2 * 1.25 = 125 GB

  •  (Data Size *
  Replicas * 1.25)/Nodes = RAM Per Node Example: (2 GB * 2 * 1.25)/4 =
  31.25 GB

In this situation I have some questions :

Is it necessary to obey from these formulas? can I use less RAM for nodes?


texts: Need to generate PK value programatically in multi-threaded application

texts:
Suppose We create a table, with identity disabled:

CREATE TABLE TestIdentity (
    Id    int NOT NULL,
    abc   varchar(10) NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

Note : I have no rights to make Primary Key to identity-enabled, so I have to calculate next Id value from the application programme.

Suppose from multiple connections data is inserted in this table, Id field value is generated by C# application code, the problem is in multi-threaded application
multiple requests might generate the same id to insert row. In that case SQL server, PK constraint violates.

What is the best solution to reduce PK constraint violation?


texts: PostgreSQL recovery file syntax, ver 8 vs ver 9

texts:
In postgreSQL version 8.4 it seems that you can place the db server into warm standby mode with the following setting in recovery.conf

restore_command = 'pg_standby archiveDir %f %p %r'

In postgreSQL version 9.5 I see the same option, but I also see what appears to be an alternative syntax in recovery.conf:

standby_mode = (boolean)
trigger_file = (string)

So is this new syntax an alternative to the old syntax (both are accepted by PGSQL)?  Any benefit to the new syntax?

Does the new syntax mean that I can use the restore_command setting as well (to use my own copy command)?


texts:
In a typical social-network-like application I have the following query that gathers news from a set of followed channels.
The main query gets the news, while the subquery gets the followed channels:

QUERY 1: Get latest news:

select n.id, n.title, n.description
from community.news n
where n.id_channel in (
       select id_channel from community.user_follows uf
       where uf.id_user ='3d4c788e0e34657457febba2040297c7'
)

The app needs this table of recent news, but also needs the followed channels, so I execute the subquery again:

QUERY 2, same as the subquery:

select id_channel from community.user_follows uf
where uf.id_user ='3d4c788e0e34657457febba2040297c7'

Can I optimize this? Both queries are always executed in sequence. Is it worth to use temporary tables or something like that? Or is there a way to access a subquery result?


texts: SQL server Like pattern match with CONCAT() always fails

texts:
I am facing problem in a simple query script for SQL server. Please help

The script is

CREATE PROCEDURE [dbo].[Search_By_Author_And_Name]
(
    @Author_name AS [varchar](50)
)
AS
BEGIN
    Select B.*, R.R_number From Book AS B , Rack As R
    where B.Author LIKE CONCAT('%', @Author_name, '%')
    AND B.B_Id=R.B_Id
END
GO

The problem is this returns no columns. Even if I pass a valid argument(@Author_name) to the procedure it returns nothing. There must be a problem in the where clause in the usage of LIKE.

sample data in the table are

the first table is Book table and the other table is Rack table

I am calling the procedure by

EXEC Search_By_Author_And_Name 'Physics'

Please help

Thanks in advance


texts:
Had a random thought I was curious about.  In the case of a security breach where a user gains access to the DB server through legitimate credentials (social engineering, phishing, fired employee, etc) what are some quick and effective methods of locking down access?

Is it as simple as removing 'Connect SQL' from the securables for the login in question?


texts: Migrate SQL Server database with instance level logins

texts:
Is there a way to migrate database along with all users and logins at the instance and database levels?

I am aware of contained databases and orphan users and solution for that.

I am using SQL Server 2012, 2014, and 2016. I came across another blog that Import Export functionality can be used, but yet have to try it.


texts: How do I determine the cause of a very small table responding to queries slowly?

texts:
I have a table that stores less than 3 registers, but the queries on it are very slow.

This table has only a single Primary Key with 0% fragmentation.

> DBCC SHOWCONTIG scanning 'MYTABLE' table... TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7930.0
- Avg. Page Density (full).....................: 2.03%


texts: SQL Server : GRANT REFERENCES Fails With Msg 15151 as Windows User with sysadmin role and also as 'sa'

texts:
On SQL Server 2008 R2, I execute this command:

GRANT REFERENCES ON ASYMMETRIC KEY ::CorpKey to XYZZYEXT;

I get this result:

  Msg 15151, Level 16, State 1, Line 1
  Cannot find the asymmetric key 'CorpKey', because it does not exist or you do not have permission.

I've checked a bunch of articles, including

Can't grant role permissions under sysadmin login

But I'm just not seeing the problem.


texts: Performance - Database queries for range of dates

texts:
Currently we have a huge database from which we are aggregating necessary information for a project to be stored in a database specific to that project.

A new functionality now requires data to be fetched based on a time scale which can be - days, weeks, months, years. We are open to database redesign in order to optimize the queries.

The only solution I can think of is -

Have table with entries for days called day table, once it hits the 7day mark update a week table entry and once that hits a limit update the month table and so on. So when we have the query for 2010-2017 we'll fetch 2010-2016 from the year table then the months from the month table and the days from the day table.

The possible difficulty I can see at the moment with this proposed solution is that it would be difficult to calculate week ranges.

Is there a better way to solve this problem or any suggestions that can improve the solution proposed.

Any help is appreciated.I'm new to working with Database design.

Example :
From 2015-2017(till 06-Apr-17)
Then - Get values for 2015-2016 from the years table
Jan 2017 - Mar 2017 from the months table
01 Apr 2017 - 06 April 2017 from the day table


texts: How query errors affect Postgresql DB performance

texts:
I have an application stored measurements data in PostgreSQL-12.
Couple of processes write data by queries like:

INSERT INTO "<measurement>" (<columns>) VALUES (<values>);

But for some reason the "<measurement>" table was not created, and I got errors like:

ERROR:  relation "<measurement>" does not exist at character 13

The question is: Does massive errors count affect the DB performance?


texts: SSIS multiple parallel ETL: Task Scheduler or Service Broker

texts:
We have one SQL Server 2008 on which we run several ETL packages in parallel.
This setup has always suffered stability issues.

Since I have taken over I am trying to find a way to increase stability so my team focus on development instead of maintenance.

At the moment the SSIS ETL packages are scheduled using the task scheduler and fine tuning the starting time is how they fix stability issues.
When debugging using 1204 error flag we noticed a considerable amount of deadlocks, which are probably the cause of the issues. But the amount of queries to debug and eventually rewrite is a giangantic task.

The ETL duration is about 8h, with some packages sometime overlapping each other.

My question here is to know if using a broker such as Service Broker, or any message broker, instead of the task scheduler, would avoid overlapping, therefore avoid deadlocks, therefore increase stability ?


texts:
I try to run sqlplus on my server with the command sqlplus /as sysdba

and I got this error

  sqlplus: error while loading shared libraries: /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so.12.1: file too short

Update

[user@server]$echo $PATH
/u01/app/oracle/product/12.1.0/dbhome_1/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games

the output of which sqlplus is /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus

I set up all the necessary Oracle shell variables.

I took a look at the libclntsh.so.12.1 file and it is empty.

Can you help me ?
Thanks


texts: do single AZ AWS RDS instances self heal?

texts: I'm aware of "multi AZ" databases "auto failing over" if one side goes down.  My question is, if you have a single AZ RDS database, and the database box "dies" does AWS automatically spin up a new one for you?  Can you expect corruption in these cases?


texts: How metadata can be generated using Stored procedure in snowflake in Generic

texts:
I have requirement like user is going to place the different columns csv file each time.I want to generate metadata for each csv file to load the data into snowflake tables
I need help to resolve above scenario,Can any one Help......


texts: pgAdmin3 can't connect properly to Postgres 9.2

texts:
I have installed pgadmin3 version 1.10.2, however when it connects to PostgreSQL server version 9.2, it says:

  Warning:

  This version of pgAdmin has only been tested with PostgreSQL version
  8.4 and below and may not function correctly with this server. Please upgrade pgAdmin.

Then it will throw this error:

  An error has occurred:

  ERROR:  column "datconfig" does not exist LINE 1: ...b.dattablespace
  AS spcoid, spcname, datallowconn, datconfig,...

etc.

Also I am quite stuck with my working version of pgAdmin3 as I am using Ubuntu Lucid. So is there a work-around for pgAdmin3 to work properly keeping my curent version of pgAdmin3?


texts: Log error restoring old database to SQL Server 2017

texts:
I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80.  Initially I took a full database backup and tried to restore it to the new server, but got the following error:

  Msg 9014, Level 16, State 2, Line 32 An error occurred while
  processing the log for database 'nrf'. The log block version 2 is
  unsupported. This server supports log version 3 to 5.

I went back to the original server and restored the backup to a new name, nrf_temp, and then altered that database to compatibility level 100 (for 2008 R2). I took a new backup and again tried to restore to my new server, but got the same error again.  Then I went back to the original server, deleted my new name database, nrf_temp, and did a restore from that last backup that was taken after I put the compatibility level to 100 - thinking maybe a recreation of the physical files might help.  I took another backup and again tried the restore to my new server, but took the same error again.

Is there any way to get the restore to work on the 2017 server or will I have to define the database completely new and import data?  Note that I was able to successfully back up the other databases from that old server, that were already at compatibility 100, and restore those backups to my new 2017 build.


texts:
We currently have a asp.net application running on a Win 2012 dedicated server, with a local copy of SQL 2014 Express.

We are looking to add a second web server and have them in a NLB cluster. But what to do with SQL... The website relies heavily on the SQL databases (read & writes)

I am vaguely familiar with the forms of SQL replication, availability groups, log shipping, etc and have read through quite a few posts on StackExchange regarding the same topic, but I still have a few questions.

The site has only moderate traffic, but HA and RPO is still very important. I would rather have some downtime, vs loose 1 row of SQL data, although the whole point in migrating to this setup is to decrease the chance of downtime.

A solution that will work with SQL Express would be ideal, SQL Standard is fine, but I dont want to have to upgrade to SQL Enterprise.

A) What options do I have/suggestions do you have regarding keeping SQL on the IIS servers. I would rather span SQL over the 2+ web servers then have to add additional servers just for SQL.
B) Are there any new features coming out in SQL 2016 that will help with this?

Although I know this is not exactly a DBA question, but I'll ask anyway. Is there a way to force a asp.net to write to all available SQL databases at the same time, but can read from any of them?
(Kinda like Windows NLB uses the same IP for all servers, posting a update/insert to the SQL IP would write it to all of them, but a query would simply use the result from the first server that answers?

Thank you all!


texts: SUM() ignores GROUP BY and sums up 4 rows instead of 2

texts:
I'm having difficulty with GROUP BY in MySQL.

My database setup:

client_visit
    - id
    - member_id
    - status_type_id (type_of_visit table)
    - visit_starts_at
    - visit_ends_at

member
    - id

schedule_event
    - id
    - member_id
    - starts_at
    - ends_at

type_of_visit
    - id
    - type (TYPE_BOOKED, TYPE_PRESENT etc)

For the purpose of this question: a member teaches a class or leads an activity (a schedule_event) at a given time. A client signs up for this class or activity.

For example:

Client A, B and C book visits and those go to client_visit table which consists of schedule_event_id and member_id, so we know what class and what member is teaching/or having activity.

Now, we want to know the total time a given member spent teaching/leading events that clients signed up for (based on the client_visit type_of_visit column equivalent to "Booked" or "Present"). We'll take member ID 82 as our test case.

Member ID 82 had 4 clients in two different classes, so if each class took 2h 15 minutes (8100 seconds), that means total time should be 16200 seconds.

Here's my query first:

SELECT cv.member_id AS `member_id`,
       sch.id AS `scheduleId`,
       cv.visit_starts_at AS `visitStartsAt`,
       TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at) AS `totalTime`
  FROM `schedule_event` AS `sch`
         LEFT JOIN `client_visit` AS `cv` ON cv.schedule_event_id = sch.id
         INNER JOIN `type_of_visit` AS `tov` ON tov.id = cv.status_type_id
 WHERE (tov.type = 'TYPE_BOOKED' OR tov.type = 'TYPE_PRESENT') and cv.member_id = 82

The result is as follows:

This shows me the clients for the first class, and the one for the second. I just want two rows, one for each class. So, I add this:

 GROUP BY sch.id

Now, the result is as follows:

So far so good,

I know that there are two schedule ids for this member, so I modified the group by to pull those together into one:

 GROUP BY sch.id AND cv.member_id

I expect that it will first merge based on sch.id (the result it's already shown in the picture above) and cv.member_id (we got two rows, so after merging, should be one)

and the result is (I modified scheduleId by adding GROUP_CONCAT, so we can see both schedule IDs are there):

Now, just like I pulled together the two schedule IDs, I want to add up the time for the two scheduled classes.

I modify the query now:

SUM(TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at)) AS `totalTime`

And the result is:

I got 32400! For some reason, the SUM is still seeing all 4 rows instead of just the unique 2.

I expected that the final result would be

+-----------+------------+
| member_id | total_time |
+-----------+------------+
| 82        | 16200      |
+-----------+------------+

All other columns are not needed, I just made them to see what is happening

What's wrong?


texts:
I have two legacy tables, lets call them TableA and TableB. Both of them fall under the same schema design with identical columns, in other words you can union them directly as one table. The only difference is based on code column and you need to manipulate a specific table based on that code. So if the code falls under (1001, 1002,..) then it goes to TableA else goes to TableB. The reason they were partitioned intially was to reduce the cost in space and cpu but now these costs can be ignored (hardware is cheap)

The DBA can union both tables into one but unfortunate there are alot of legacy code (which is being constantly updated with new features) that explicitly specify the table name.

Is there anyway to in Oracle to solve this issue by merging the tables under one table for better administration? and bare in mind the developers can still query against the old tables and the new ones as well?


texts:
I have the following Group By clause:

Group By
Grouping Sets (a, (b, Rollup (c, d)) )

My thought process is

Grouping sets ( a )
Union All
Grouping sets( (b, Rollup (c, d) ) )

Then expand the latter into
Grouping sets ( (b, ( (c,d), (c), ( () ) )

Then do a Cartesian product, so the second part becomes
Grouping sets ( (b, c,d), (b,c), (b) ).

Overall, the equivalent expression would be

Grouping sets (a, bcd, bc, b).

Please help me point out which step is incorrect.

Thanks


texts: Where is authentication information saved in master?

texts:
When using SQL Server Authentication mode, I know that usernames and hashed passwords are saved in the master database. I wonder is there a way to retrieve that information? If yes, which tables stores it?
(except contained database users)

Thanks for support.


texts: sp_whoisactive - Do stats include activity for snapshot versioning?

texts: On a system that has read committed snapshot isolation enabled, do the reads, writes, CPU, etc. stats returned by sp_whoisactive include the activity associated with maintaining versions in tempdb?


texts: Properly escape characters in a string for JSON

texts:
With the below example, how do I escape the double-quote characters that are in the @data variable?

declare @data nvarchar(max) = N'"TEST"';
declare @jsonFragment nvarchar(max);
declare @id int = 999;

set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + @data + '"';

select @jsonFragment;

The result is:

 ,"999":""TEST""

I need it to be:

 ,"999":"\"TEST\""

As I understand it, selecting for json path requires that I define a static key name - but as can be seen, I have a scenario where key names are being defined dynamically.

In my real-world use case, this string construction is occurring within a stuff statement, itself part of a column definition in a larger select that utilises CTEs - meaning it would be very hard to create a dynamic sql statement to be run using sp_executeSQL.


texts:
im running out with ideas for creating postgres index for a better performance.
This example below is a selection of a big table with many entries for a diary to person relation.
usually, i create an index just of person_id with btree but this has a less impact of the performance. How i should create the right index for this example?
select * from diary2person where now() between valid_from and valid_until and person_id = ?
thanks in advance!
regards Lars


texts: How to Identify Who or What is Restarting SQL services via Service Control?

texts:
I investigated an instance that experienced an unexpected restart and came across the usual service control event but no user login associated with it. Is it still possible that an authorized user initiated the restart or is this due to Windows issuing the command? If an authorized user, outside of the SQL error logs and event logs, where could I go about attempting to identify the user?

Log Name:      System
Source:        Service Control Manager
Date:          12/24/2014 9:02:24 AM
Event ID:      7036
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      XXX
Description:
The SQL Server Agent (MSSQLSERVER) service entered the stopped state.


texts: How to clone a database from one environment to a database on another environment?

texts:
OS: RHEL

DB: Oracle 11g version 11.1.0.7.0

I am trying to clone a database on one environment (environment A) to another database on a separate environment (Environment B). Environment B's database is an out-of-date clone of Environment A's database. What would be the best methods for exporting the data from the database on environment A, and importing it to the database on environment B?
I have tried using the export and import data pump to do this but it has been unsuccessful for importing the full database.

Also, we use Oracle Warehouse Builder to load data into the databases. When there are issues loading the data I currently restore a previous snapshot of the virtual machine prior to loading the data. Is there also a method to export the data prior to loading data, and then importing it again if there are issues when loading the data? Would export and import data pumps solve this issue?

Thanks!


texts: Alter a view that refers to a non-readable secondary database

texts:
I have a database (DB1) that is not readable because it is secondary in an AG group.
I have another database (DB2) that is not replicated with the AG group. It contains some views that reference the first database. I need to periodically alter some views.
The error is:

Msg 976, Level 14, State 1, Procedure vw_xxx, Line 4 [Batch Start Line
9] The target database, 'DB1', is participating in an availability
group and is currently not accessible for queries. Either data
movement is suspended or the availability replica is not enabled for
read access. To allow read-only access to this and other databases in
the availability group, enable read access to one or more secondary
availability replicas in the group.  For more information, see the
ALTER AVAILABILITY GROUP statement in SQL Server Books Online."

I do not have these options:

Enable the readable secondary (lack of licenses)
AG failover (this is a DR site)
Add DB2 to the same AG (too large, I only want to replicate schema not data)

Are other options available?
I have 3 AG nodes. The DR is not readable because it lacks an active license to be readable. The ALTER VIEW occurs on DB2 at the DR site, referencing DB1 in that instance. I'm altering the views because I'm deploying changes with a dacpac in that DR site. I do not have licences to cover that DR site in an active way.


texts: T-SQL query to find backup folder for specific maintenance plan

texts:
I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012.

Is there any way to query the maintenance plans for databases, backupfolder, starttime and other information?

So far I have only have this:

select name, subplan_name from msdb.dbo.sysmaintplan_subplans sp join
msdb.dbo.sysmaintplan_plans p on p.id = sp.plan_id

Mostly this is regarding new servers so backup have not had a chance to run yet, rendering logs useless.


texts:
I am facing a strange situation... On a SQL2016 on Windows 2019, I have an application running on windows2019 that connect to the SQL server using a domain service account (same domain as the
SQL Server).

In the SQL server Log, I can see those errors:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: x.x.x.x]
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed   [CLIENT: x.x.x.x]

Well, the first error does not make sense as the login is in the same domain.

The weird thing is that when they reboot the app server, then suddently, those error goes away and are replaced by "login succeeded". And then, hours or days later, it start failling again.

If it was the spn, it would never work. If it was a TLS issue, I'm guessing it would never work as well.

What could it be that make it work for a certain time and then fail ?

Thank you.


texts: One of the secondary's is failing log shipping restore due to insufficient drive space.  This might not be looked at by IT for several days.   Can log shipping primary server purge transaction log backup before secondary has a chance to restore in this scenario?  Or does Log shipping primary just hold onto transaction log backups until all secondary's have restored it?


texts: Errors declaring variables in stored procedure?

texts:
I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this.

I don't know where the error is because when I switch the how_many section after the _rollback, The error changes.

What's wrong with the way I'm declaring variables here?

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
    DECLARE how_many INT;
    SELECT COUNT(*) FROM things INTO how_many;
    -- Prepare roleback.
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    -- Start transaction.
    START TRANSACTION;
    -- Do all the things.
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END $$
DELIMITER ;


texts: Can't "SET TRUSTWORTHY ON" With Non-SA User Account

texts:
I'm trying to run the following command with a user account(XXX_Admin):

ALTER DATABASE [XXX] SET TRUSTWORTHY ON;

This works with the "SA" account, but with my user account(XXX_Admin) I get:

  Msg 5011, Level 14, State 5, Line 1 User does not have permission to
  alter database 'XXX', the database does not exist, or the database
  is not in a state that allows access checks. Msg 5069, Level 16, State
  1, Line 1 ALTER DATABASE statement failed.

I've checked, and this user account should have the correct permissions:

(XXX Database)

(Login - Server Rules)

(Login - User Mapping)

I've also verified that the login and user are synced with the following commands:

EXEC sp_change_users_login 'update_one', 'XXX_Admin', 'XXX_Admin'

EXEC sp_change_users_login 'AUTO_FIX', 'XXX_Admin'

GRANT ALTER ON DATABASE:: XXX TO XXX_Admin

Now I'm really at a loss as to what to check next... As so often with SQL Permissions issues, I grant all access to my account that I can think of but am still unable to run commands. Am I missing something?


texts: Unable to login to SQL Server + SQL Server Authentication

texts: i forget my sql server username and password(sql server 2005).therfore i didn't get into sql server.So please give some answers to me.how can i solve this problem?


texts: How can I get more information about which lookup failed in an SSIS lookup?

texts:
I'm frequently getting errors along the lines of

Error: 0xC020901E at Build Test Data, Lookup ID String[120]: Row yielded no match during lookup.

To resolve these, I'm going back to the source and making intelligent guesses, but it would be far easier if I could see the row that fails.

What's the best way to do this in SSIS (using MSSQL2012)?

Thanks.


texts: Can UPDATE lead to deadlocks in SQL SERVER?

texts:
Can we experience deadlock if run the same update query concurrently?
Let's assume there's a table with millions of records and we need to update several thousand records at one shot.
UPDATE TABLEX
SET Column1 = '1'
WHERE Column2 BETWEEN 1 AND 10000

I'm wondering if running this query concurrently would lead to a deadlock as each update query may acquire update locks on rows in a different order.
In other words, is the following a possible scenario. The scenario is that two concurrent sessions run the same query to update the same set of records.
(My assumption is that each UPDATE statement will scan rows in a different order)

Session 1: Acquires Update lock on row 1 (Column2 = 1)
Session 2: Acquires Update lock on row 2 (Column2 = 2)
Session 1: Tries to acquire Update lock on row 2 but it fails as it's already held by Session 2. (Column2 = 2)
Session 2: Tries to acquire Update lock on row 1 but it fails as it's already held by Session 1. (Column2 = 1)
Deadlock is detected.

Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders. Does this lead to a deadlock?


texts:
I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day.

I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger.

The trigger consists of 10 updates to another table for slightly different criteria.  The deadlock occurs in the trigger.

When a person makes an application and the job is running that's when the deadlock occurs.  The application inserts into the same table as the scheduled job.

Looking at the trace it seems to happen when process 1 obtains a key lock, process 2 obtains a page lock, then process 1 escalates the key lock to a page lock and process 2 tries to obtain a key lock.

I've added missing indexes which seems to have helped but its still happening.  I'm not a DBA so any advice on an approach to solving this would be appreciated.

I've added a link to the deadlock xml - this is from a test I did to duplicate the problem.

deadlock xml


texts:
I'm having an issue using a WHERE clause and JOIN.

SELECT * FROM `CallDetailRecord`
  WHERE `StartTime` >=1357102799000
  AND `StartTime` <=1357880399000
  JOIN `CallEvent` ON `EventID` = `CallEventID`

  Error Code: 1064
  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 'JOIN CallEvent ON EventID = CallEventID
  LIMIT 0, 1000' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.004 sec
---------------------------------------------------

I'm just trying to limit data set by time "starttime', but I get query error and it refers to
join.

Any ideas? I will be happy to provide more info if required.


texts: Work queue with complex select and long processing: Ensuring concurrency

texts:
I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process.

Normally this kind of thing is done with select top (1) ... with (updlock, rowlock, readpast).
This works as expected and provides great concurrency when there is a covering index, but for this particular query the where condition is complex, depends on the time of the day and on presence of records in other tables (where exists(...)), and is followed by an order by of similar nastiness, so it cannot be covered by a single index or an indexed view.

In this situation the select top (1) puts the updlock on all rows that match the where, not just the first one, which leads to one worker winning the job and all the others waiting (or falsely reporting that there were no more rows to process).

While this does not lead to any errors, the concurrency is not great. The problem is that record selection is quite cheap, but record processing is very expensive. So the other workers sit there doing nothing for seconds.

In an attempt to fix this concurrency issue I came up with the following solution for the job selection (error handling removed for clarity):

exec sp_getapplock N'Pick a Job', 'Update';

declare @will_lock_that int;
select top (1) @will_lock_that = row_id from the_table with(readpast) where... order by...;

declare @locked int;
select @locked = row_id from the_table with(updlock, rowlock, holdlock) where row_id = @will_lock_that;

exec sp_releaseapplock N'Pick a Job';

The idea was that:

The applock makes sure only one process at a time will be picking a job. This is okay because picking a job is cheap.
The applock will be released very quickly, letting the other workers pick their jobs, while the original worker will be processing the item, having released the applock.
The first select statement will have to fetch all rows matching the where condition, as described above, but this time it will not put any persistent locks on them. The shared locks will be released as soon as the select returns.
The readpast in that select will skip over any rows already being processed, because they are locked with updlocks.
The locking hints in the second select will hide the row from the first, readpast-enabled select performed by others.

Of course this did not work. readpast did not try to skip over the updlocked rows. Instead it would wait for the first encountered locked row to be released. Changing the updlock to xlock in the second query does not change this, neither does making an actual update to the row.
The only way to make the readpast skip over rows seems to be adding a locking hint to the readpast query itself (e.g. with(readpast, updlock)) - but that fails the original purpose, because now all rows will again be locked with updlock.

Is there a way to solve this concurrency problem without using manual locks? (By manual locks I mean updating a special field in the row to 'Locked', outside of a transaction for others to see, and then updating it back when processing is finished.)

I cannot use the locking by deletion, too, because the row must stay in the same table, and only few of its fields will change after processing.


texts:
I have an Oracle db and a table and I'd want to sum the VAL column until I have the value in column SUMM 100 and the difference in column DIF - as in this example:

ID VAL  SUMM    DIF
1   40  40      0
2   20  60      0
3   30  90      0
4   15  100     5
5   22  0       27

How can I do this? Thanks!


texts:
I intend to create a simple job on a bunch (100+) sql servers (of differing flavors) that does a simple automated query periodically (like weekly). These jobs then need to push the result out to a central location. What is going to be a reliable, minimal config way to push those results?

I was thinking of converting the result set to text/xml and posting to a url. It seems that doing anything http from tsql is not simple, or standard. I think a CLR stored proc is probably my best option. I've been shying away from it because of the multi-step deployment.

(I also considered using a script that connects to every server and executes the query. I rejected this because of security concerns in introducing a shared account across all our servers.)

Can anyone suggest better options?

Thanks


texts: Execute Mysql queries using the same file more than once

texts:
I have a file with .sql extension in which I write a query like insertions, deletions or updates, and execute it in toadformysql . I repeat the same action many times because I have lot of queries, so it turned out that I have a lot .sql files.
for the first query  It works . but When I tried to add a second query in the same file and execute it  , there are  errors because the first query has already been executed. If the first query is delete for example, it displays an error "no such column" , which is logic beacuse I already delete the column.
Is there a way that I can have a single file in which I add all my queries and while executing it , I won't have errors from old queries like duplicates or others, something like errors handling. It is because I have to keep an history of all queries.
Only the query that I didn't already execute will throw an error if there is.

for example if my first query is

ALTER TABLE adbproject DROP COLUMN imageFormat

and I execute it. for the second time I want to add another query which is:

ALTER TABLE PERSON ADD MATRICULE VARCHAR(50) AFTER CODE;

So the file to be executed line's will be :

ALTER TABLE adbproject DROP COLUMN imageFormat;
ALTER TABLE PERSON ADD MATRICULE VARCHAR(50) AFTER CODE;

but I have logically this error : Can't DROP 'imageFormat'; check that column/key exists. I am searching a way to avoid this error.
Thanks in advance


texts: Oracle DataGuard: Find primary from standby DB

texts: I have SYS access to a data guard standby DB, but i need to find the primary one. I was searching in Oracle Docs but couldn't find anything that could help.


texts: To do a refresh of a "on premise" postgres database to a azure postgres database or to do a backup and restore?

texts:
We have a postgres database hosted on Azure, that we would like to refresh from a "on premise" postgres database.

I'm not sure, what would be the best way to do that?

Through an incremental backup then an incremental restore or through a Slony-I replication although I'm quite sure this feature do not exist for Azure?

Thanks


texts: SQL Server credentials - Maximum Password size

texts:
What's the maximum password size I can get on SQL Server 2012/2014? I was unable to find this information online.

I mean SQL Server credentials itself, not passwords inside tables.

Thanks a lot


texts:
In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected.

In one scheme a surrogate key is used which means that in the join condition between the two tables a single join condition is used. This join is done using a hash join.

The other schema uses a natural key of 4 columns. In this case postgres uses a merge join which is much slower. However I had understood that postgresql can do multi column hash joins.

Is this speed difference normal? Can I get the speed of the 4 column join more on par?

Here are the two queries and the explain analyze:

Natural key

EXPLAIN ANALYZE
SELECT *
FROM kassabon kb
    JOIN kassabon_regel kbr ON (
        kb.filiaal_id=kbr.kassabon_filiaal_id AND
        kb.kassanr=kbr.kassabon_kassanr AND
        kb.jaar=kbr.kassabon_jaar AND
        kb.bonnr=kbr.kassabon_bonnr);

"Merge Join  (cost=289690.28..309517.94 rows=11981 width=280) (actual time=5507.702..9675.564 rows=907105 loops=1)"
"  Merge Cond: ((kbr.kassabon_bonnr = kb.bonnr) AND (kbr.kassabon_filiaal_id = kb.filiaal_id) AND (kbr.kassabon_kassanr = kb.kassanr) AND (kbr.kassabon_jaar = kb.jaar))"
"  ->  Sort  (cost=183246.72..185514.48 rows=907105 width=141) (actual time=4608.588..7210.120 rows=907105 loops=1)"
"        Sort Key: kbr.kassabon_bonnr, kbr.kassabon_filiaal_id, kbr.kassabon_kassanr, kbr.kassabon_jaar"
"        Sort Method: external merge  Disk: 134664kB"
"        ->  Seq Scan on kassabon_regel kbr  (cost=0.00..28374.05 rows=907105 width=141) (actual time=0.013..617.881 rows=907105 loops=1)"
"  ->  Materialize  (cost=106442.89..109233.22 rows=558067 width=139) (actual time=899.101..1321.491 rows=907133 loops=1)"
"        ->  Sort  (cost=106442.89..107838.05 rows=558067 width=139) (actual time=899.097..1182.711 rows=558067 loops=1)"
"              Sort Key: kb.bonnr, kb.filiaal_id, kb.kassanr, kb.jaar"
"              Sort Method: external merge  Disk: 48808kB"
"              ->  Seq Scan on kassabon kb  (cost=0.00..13117.67 rows=558067 width=139) (actual time=0.009..95.594 rows=558067 loops=1)"
"Total runtime: 9757.286 ms"

Surrogate key

EXPLAIN ANALYZE
SELECT *
FROM kassabon kb
    JOIN kassabon_regel kbr ON (
        kb.id=kbr.kassabon_id);

"Hash Join  (cost=31538.51..121752.89 rows=907105 width=270) (actual time=226.936..1817.946 rows=907105 loops=1)"
"  Hash Cond: (kbr.kassabon_id = kb.id)"
"  ->  Seq Scan on kassabon_regel kbr  (cost=0.00..27461.05 rows=907105 width=131) (actual time=0.005..159.972 rows=907105 loops=1)"
"  ->  Hash  (cost=13117.67..13117.67 rows=558067 width=139) (actual time=226.770..226.770 rows=558067 loops=1)"
"        Buckets: 16384  Batches: 8  Memory Usage: 7652kB"
"        ->  Seq Scan on kassabon kb  (cost=0.00..13117.67 rows=558067 width=139) (actual time=0.004..70.969 rows=558067 loops=1)"
"Total runtime: 1866.136 ms"


texts:
Suppose I have a table with a numeric column (lets call it "score").

I'd like to generate a table of counts, that shows how many times scores appeared in each range.

For example:

score range  | number of occurrences
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3
   ...       |       ...

In the example I have given explicit ranges. But  don't know what range could be possible.

Is there an easy way to set this up?  What do you recommend?


texts: PostgreSQL could not connect to server on AWS

texts:
I am currently using AWS's free tier. I am receiving the error message:

could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "xxxxxxxxx" (-) and accepting TCP/IP connections on port 5432?

This has happened twice now with 2 different databases. Each time after creating the DB on AWS, they both worked perfectly fine for 4 or 5 days without any connection issues. I have not changed any settings in pgAdmin, psql, or AWS since creating the two databases, so I am unsure of the cause.

EDIT:
I just logged into pgAdmin, and now I can connect. I guess my updated question is 'has anyone had this problem with AWS'? I did absolutely nothing aside from checking the firewall as dezso suggested, and this was 4 hours after I restarted the database and still couldn't connect. Also, just to clarify, the database was definitely available after restarting it when I tried to, but couldn't connect the last time.

EDIT:

It has once again temporarily stopped connecting and no settings have changed.


texts: change mysql datatype column from string to json

texts:
I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON.

I already put a {" before and "} after the strings. So now the values of the column looks like:

{"/users/57/user-11.png"}

When I try to update the table by:

ALTER TABLE ah_website_dev2.vendor_horses MODIFY COLUMN image json;

I get following error message:

Error Code: 3140. Invalid JSON text: "Missing a colon after a name of object member." at position 27 in value for column '#sql-1d30_44.image'.  0.011 sec


texts: Using SQL Server 2017, can log backups be safely taken on primary when using Log Shipping or Availability Groups?

texts:
We have a CommVault server taking log 15-minutely log backups of our primary (on-premise) DBs (combined with full nightlies), for point-in-time restores.

We want to enable high availability for DR fail-over to a cloud-based SQL Instance and we will be using either Log Shipping or Availability Groups.

This secondary instance will be truly passive so as not to incur additional licensing costs (i.e. we will not be able to take backups from it)

We are using SQL Server 2017 Standard Edition and, again for licensing cost reasons, won't be upgrading to Enterprise.

My question is - can CommVault continue to take its log backups for reliable point-in-time recovery and at the same time that either of the high availability options is enabled, or is there a risk of interference?


texts: Query error: there is no matching unique or primary key constraint in the referenced table

texts:
I am getting an error of

*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

for my code

create table comm_Customer(CustomerID numeric primary key,
                           CustomerName varchar(20),
                           Address varchar(30),
                           PhoneNumber numeric,
                           foreign key(CustomerID)
                               references comm_ShoppingCart(CustomerID));


texts:
If I run this code:

select 99999 * 9999

result = 999890001

If I run this code:

select 9999923 * 99999999

result:

  Msg 8115, Level 16, State 2, Line 1
  Arithmetic overflow error converting expression to data type int.

And If I run the following code:

select 922337299987987689745893 * 999999994564

result = 922337294974162127011191918541325652

Why am I getting this arithmetic overflow error within a specific range?


texts: Is it possible to archive table rows of an RDS read replica?

texts: I would just like to know if I can purge 60 days old rows from a replica. Would the replica just copy the rows again from the master after deleting the rows? Because what I would really like to do is to leave the master as is and then do all the reads for the reports on the slave. So what I would really be doing is to copy the rows older than 60 days to an archive table and then delete it from the source table. Is it ok to have an additional table(archive) on the read replica that doesn't exist on the source?


texts:
I am trying to grant privileges for a user to a new database

mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword';

I get the following error

ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

I am using MySQL 5.6

mysql> select @@version;
+-------------+
| @@version   |
+-------------+
| 5.6.24-72.2 |
+-------------+
1 row in set (0.00 sec)

I did find an article on MySQL on updating Native Passwords on 5.6.x and later

  6.3.9.3 Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin
  https://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html

I ran the command they recommend as root

mysql> UPDATE mysql.user SET plugin = 'mysql_native_password'
    -> WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
ERROR 1054 (42S22): Unknown column 'plugin' in 'where clause'


texts: SSMS Object Explorer Details causes deadlock while job is running

texts:
I have a problem with one of my SQL Server jobs.  I've found that while the job is running, SQL Server Management Studio locks up.  It always locks up while I'm looking at tables and sprocs within the "Object Explorer Details" screen.

I am using version 10.0.1600.22 of the SSMS.  Incidentally, version 9.00.4035.00, which I also have installed, has no problem with this--I open this one to find the suspended mangagement studio process and kill it.

The job runs a stored procedure which does the following things:

-truncates a table
-pulls a ton of data from various select statements into the truncated table
-inserts & updates data in second table with records from the first table

All of this occurs within a Transaction, and i have set the DEADLOCK_PRIORITY to HIGH.  I tried removing this setting but it didn't solve the problem.

The procedure is much to involved to post here, but what should I look for to resolve this problem?


texts: How to check if a table exists in Informix

texts: What would be the query to check that a table exists in Informix ?


texts: Best way to store products of Multiple Fields of different information

texts:
I have around 6 table in one of 3rd party cloud database. I am planning to Build it MSSQL. My problem in each of these table there are 20-40 field of different information and Item. Which approach better to manage my data having a separate table for each or in a single table. if i store all in single table the number of field will in a table will be very large. I also plan to use C# for the Front end to develop Product Management System.

Thanks