Improving MySQL query performance for a large database have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values has others 914 794 items in child table. That just for a month period, for all time the child table has more than 35 975 568 items.

A simple join, like that, filtering by a range date is so slow:

select  count(i.BrandId) from projectitem i
inner join project p on i.ProjectId = p.Id
where (p.Date between "2019-07-01 00:00:00" AND "2019-07-30 23:59:59");

That is the struct of tables:

    'CREATE TABLE `project` (
      `Id` char(36) NOT NULL,
      `Url` varchar(10) DEFAULT NULL,
      `Region` varchar(2) NOT NULL,
      `Area` int(11) NOT NULL,
      `Name` varchar(250) DEFAULT NULL,
      `Description` text,
      `AccountId` char(36) DEFAULT NULL,
      `UserId` char(36) DEFAULT NULL,
      `Date` datetime DEFAULT NULL,
      `ModifiedDate` datetime DEFAULT NULL,
      `DeletedDate` datetime DEFAULT NULL,
      `Deleted` tinyint(4) NOT NULL,
      `Likes` int(11) NOT NULL,
      `Views` int(11) NOT NULL,
      `Private` tinyint(4) NOT NULL,
      `OnlyBudget` tinyint(4) NOT NULL,
      PRIMARY KEY (`Id`),
      KEY `dateproject` (`Date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

'CREATE TABLE `projectitem` (
  `Id` char(36) NOT NULL,
  `BrandId` char(36) DEFAULT NULL,
  `SpecificationId` char(36) NOT NULL,
  `ProjectId` char(36) NOT NULL,
  PRIMARY KEY (`Id`,`ProjectId`,`SpecificationId`),
  KEY `project_item_key_idx` (`ProjectId`),
  KEY `brand_idx` (`BrandId`),
  KEY `spec_item_key_idx` (`SpecificationId`),
  CONSTRAINT `project_item_key` FOREIGN KEY (`ProjectId`) REFERENCES `project` (`Id`),
  CONSTRAINT `spec_item_key` FOREIGN KEY (`SpecificationId`) REFERENCES `specification` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

How I can improve that? I thing that one reason for the bad performance is because the lenght of my key in "projectitem" table.

I migrated that data from another database. Than, first a created all tables without keys and after the migration I used that command for create keys and indexes:

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE `projectitemreference`
ADD PRIMARY KEY (`Id`, `ProjectId`, `ProjectItemId`, `SpecificationId`),
ADD INDEX `reference_item_key_idx` (`ProjectId` ASC, `ProjectItemId` ASC, `SpecificationId` ASC) VISIBLE
, LOCK = NONE;

ALTER TABLE `projectitemreference`
ADD CONSTRAINT `reference_item_key`
  FOREIGN KEY (`ProjectId` , `ProjectItemId` , `SpecificationId`)
  REFERENCES .`projectitem` (`ProjectId` , `Id` , `SpecificationId`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `reference_project_key`
  FOREIGN KEY (`ProjectId`)
  REFERENCES .`project` (`Id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, LOCK = NONE;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Can it be a problem?

count(i.BrandId) checks BrandId for being not null before counting.  If you don't need that check, change to COUNT(*) to count the number of rows in the JOIN.  Or perhaps you wanted COUNT(DISTINCT i.BrandId) to count the number of different brands?

Then these indexes will help:

project:  INDEX(Date)
projectitem:  INDEX(projectid)           -- if using COUNT(*)
projectitem:  INDEX(projectid, BrandId)  -- if explicitly COUNTing on BrandId

When you dataset gets bigger than RAM, the GUIDs will kill performance due to the random accesses blowing out the cache.

Be aware that any change, even a minor change, to those queries may necessitate reevaluation of the indexes.


What permissions should the account for MS SQL Server Agent have? I'm in a situation where I need to change the account for the Server Agent of SQL Server from NETWORK SERVICE to an AD domain account. All I'm doing is making the domain account sysadmin in the instance of SQL where the Server agent is. I'm not making it a Windows administrators.

There are currently jobs running there and I really don't want to make crash.

What should I take into account?

You also need to verify that the AD account will have access to the parts of the file system that it might need.  We ran into this awhile ago, and I don't remember which jib had an issue.  Could have been backup, or something else.

If you're using SQL to connect to remote SQL Servers, then it depends on if you are using linked servers.  Linked servers use the SQL to connect to the remote server.  If you open a connection directly on the remote server, you'll need a Windows (probably) or SQL authentication login on that server


SQL SELECT query takes too long after updating the version To begin with, here is my query:

SELECT
  pd.meterclass, pd.meterscore, pd.rt_url, pd.imdbId, pd.title, pd.adult,
  pd.backdrop,pd.budget, pd.homepage, pd.plot, pd.poster, pd.release_date,
  pd.revenue, pd.runtime, pd.language, pd.tagline, pd.status,
  pd.imdbRatings, pd.imdbVotes, mg.genre_id, cst.cast_id,
  cst.character_name, cst.name,
  cst.profile_path, crw.job, crw.name as crew_name,
  crw`enter code here`.profile_path as crew_profile_path, crw.id as crew_id,
  crw.credit_id as crew_credit_id
FROM (
       SELECT *
       from movie_data_primary
       WHERE cinestopId="8QRJTD"
     ) pd
INNER JOIN movie_genre mg ON pd.cinestopId = mg.cinestopId
INNER JOIN movie_cast cst ON cst.cinestopId  = pd.cinestopId
INNER JOIN movie_crew crw ON crw.cinestopId = cst.cinestopId;

Before I upgrade my MySQL version to 5.7, this query used to take around 3 seconds (not the most optimal time, but tolerable anyway). After the upgrade, it takes more than half an hour. Here's the profile:

+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| starting             |   0.000221 |
| checking permissions |   0.000013 |
| checking permissions |   0.000006 |
| checking permissions |   0.000004 |
| checking permissions |   0.000008 |
| Opening tables       |   0.000039 |
| init                 |   0.000158 |
| System lock          |   0.000018 |
| optimizing           |   0.000025 |
| statistics           |   0.002507 |
| preparing            |   0.000063 |
| executing            |   0.000007 |
| Sending data         | 999.999999 |
| end                  |   0.000013 |
| query end            |   0.000012 |
| closing tables       |   0.000012 |
| freeing items        |   0.002281 |
| cleaning up          |   0.000023 |
+----------------------+------------+

The field 'cinestopId' is indexed. Can someone tell me what's going berserk?

Thanks!

Have you tried the query without joining against a subselect?

SELECT
  pd.meterclass, pd.meterscore, pd.rt_url, pd.imdbId, pd.title, pd.adult,
  pd.backdrop,pd.budget, pd.homepage, pd.plot, pd.poster, pd.release_date,
  pd.revenue, pd.runtime, pd.language, pd.tagline, pd.status,
  pd.imdbRatings, pd.imdbVotes, mg.genre_id, cst.cast_id,
  cst.character_name, cst.name,
  cst.profile_path, crw.job, crw.name as crew_name,
  crw`enter code here`.profile_path as crew_profile_path, crw.id as crew_id,
  crw.credit_id as crew_credit_id
FROM movie_data_primary pd
INNER JOIN movie_genre mg ON pd.cinestopId = mg.cinestopId
INNER JOIN movie_cast cst ON cst.cinestopId  = pd.cinestopId
INNER JOIN movie_crew crw ON crw.cinestopId = cst.cinestopId;
WHERE pd.cinestopId="8QRJTD"


Migrating MySQL database structure with foreign keys fails I am trying to set up a new testing server with a somewhat complex database structure by dumping the old database and importing it. But even though I use SET FOREIGN_KEY_CHECKS=0;, the import fails with Can't create table 'DatabaseX.TableY' (errno: 150).

The referenced table probably doesn't exist yet, so I understand why it complains. But I just can't figure out how to reorder the dumpfile in such a way that the import works, or how to make MySQL ignore missing tables etc during import.

Doh, I noticed that there was some remains from an earlier, botched migration. Dropped all databases, and then import worked.


Replication Subscribers and AlwaysOn Availability Groups (SQL Server) I have AlwaysOn Availability Groups configured with Replication Subscribers, and  is currently running successfully with one publisher using the command below.

-- commands to execute at the publisher, in the publisher database:
EXEC sp_addsubscription @publication = N'yocc_pub',
       @subscriber = N'repl_agl',
       @destination_db = N'YOCCDB',
       @subscription_type = N'Push',
       @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;
GO

EXEC sp_addpushsubscription_agent @publication = N'yocc_pub',
       @subscriber = N'repl_agl',
       @subscriber_db = N'YOCCDB',
       @job_login = null, @job_password = null, @subscriber_security_mode = 1;
GO

All commands complete successfully.

My first problem is "I want to add one more publisher " to the AlwaysOn subscriber but I am getting the error below:

  sql server error-21488 sql server error-18752

Note:-

node-1 (alwayson primary node and replication subscriber)
node-2 (secondary replica and report server)
node-3 (publisher 1)/yocc_pub(work as distributor)
node-4 (publisher 2)/yocc_pub2(configure successfully but got error-21488,18752)
listener name - repl_agl
database - yoccdb

Second problem-

How to maintain primary key value to avoid conflict between publisher 1 and publisher 2 to sync with node-1 because database are same ??

The first problem:

Error 21488

  No active subscriptions were found. The publication must have active subscriptions in order to post a tracer token.

This happens when attempting to put in a tracer token without an active subscription. This error seems pretty obvious.

Error 18752

  Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

Make sure there are no other logreader agents running for the same publishing database.

Second part:

  Second problem- How to maintain primary key value to avoid conflict between publisher 1 and publisher 2 to sync with node-1 because database are same ??

You'll either need to add a unique field (such as servername, etc) or configure some way of making each record unique. Generally, if not designed for at first, will require a schema change. Commons solutions are to use even/odd numbering in identity columns/id fields or using the source host name/database name.


"Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity" - meaning? http://web.cs.ucla.edu/classes/fall04/cs143/solutions/chap2.pdf
In the above link, there is the following statement - "Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity". I have searched a lot, but I can't find anywhere what partitioning is and how (also why) exactly the tuples in a weak entity set are partitioned. If someone can illustrate it with an example it would be very helpful

I believe what it's trying to say is because weak entity sets don't have their own primary keys, they are dependent on the strong entity sets that they have a foreign key relationship with (because those related strong entity sets do have primary keys).
So in other words, a tuple in a weak entity set will be partitioned (split up) from other tuples in that same weak entity set by their related strong entity set tuples.
I'm assuming the purpose of weak entity sets is a form of normalization by breaking related fields out of the strong entity set into a separate related set all together (a weak entity set without a primary key).


Actual Execution Plan - Estimated/Actual Rows Differ Greatly I'm wondering if I can speed this query up. One thing I noticed is the estimated number of rows differs greatly from the actual number of rows.

https://www.brentozar.com/pastetheplan/?id=H1ya3-3pe

ClientRequestID in tblCR_ClientRequestTIP has no statistics, and SQL Server would love to have some.  You can see that warning in the actual execution plan.  This leads me to believe perhaps you have auto create statistics turned off.  This may be causing issues.  Have you updated statistics recently?

I'd consider adding the following indexes:

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestTIP_ClientRequestID
ON dbo.tblCR_ClientRequestTIP(ClientRequestID)
INCLUDE (TIP_BeginDate, TIP_LocationName1);

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestJOB_ClientRequestID
ON tblCR_ClientRequestJOB(ClientRequestID)
INCLUDE (JOB_Date);

CREATE NONCLUSTERED INDEX IX_tblCR_ClientRequestPP_ClientRequestID
ON dbo.tblCR_ClientRequestPP(ClientRequestID)
INCLUDE (PP_BeginningDate)

(there are others that could be added as well, this is just a sample)

Also of note, tblCommitment, tblStaff, and tblProgram do not have clustered indexes.

The tblClientRequestJOB row estimates are not exactly perfect, but the actual data size vs the estimated memory required for that table shows that row estimate is not really problematic.  I doubt SQL Server would choose a different plan if it knew the actual row counts ahead of time in that instance.  A far bigger change is likely to come from getting rid of the eager index spool resulting from the clustered index scan of tblCR_ClientRequestTIP.


Looping with array of values in PL/pgSQL Can we use RETURN QUERY within this loop to display all values from UserResponseList instead of RAISE NOTICE?

FOR i IN 1 .. array_upper(UserResponseList, 1)
LOOP
   RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i];
END LOOP;

can we use return query within this loop ...?

You can use RETURN QUERY:

CREATE OR REPLACE FUNCTION f_pivot_arrays()
  RETURNS TABLE(question text, response_id int, response text) AS
$func$
DECLARE
   QuestionList         text[] := '{Who?,When?,WHY?}';
   UserResponseID_List  int[]  := '{1,2,3}';
   UserResponseList     text[] := '{Here.,Now.,Because.}';
BEGIN
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      RETURN QUERY SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i];
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

Or RETURN NEXT:

...
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i]
      INTO   question,        response_id,            response;
      RETURN NEXT;
   END LOOP;
...

But since Postgres 9.4, the simpler tool to unnest arrays in parallel is unnest():

   RETURN QUERY
   SELECT *
   FROM   unnest(QuestionList, UserResponseID_List, UserResponseList);

dbfiddle here

Related:

How do you declare a set-returning-function to only be allowed in the FROM clause?


Oracle dead with unknown reason Our oracle database dead from time to time.When i look into the log in the alert.It shows:

  Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xFFFFFFFEAA7DD0E0] [PC:0x8213123, kslgetl()+111] [flags: 0x0, count: 1]

  ORA-07445: [kslgetl()+111] [SIGSEGV] [ADDR:0xFFFFFFFEAA7C58C0] [PC:0x8213123] [Address not mapped to object] []

  DDE: Problem Key 'ORA 7445 [kslgetl()+111]' was completely flood controlled (0x6)

  Further messages for this problem key will be suppressed for up to 10 minutes

  Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/SDH/trace/SDH_p104_2581.trc  (incident=82376):

  ORA-00600: internal error code, arguments: [ksl_invalid_latch], [kslges], [0x2AFF2F2D8], [], [], [], [], [], [], [], [], []

Database version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Any idea? thanks!

http://support.oracle.com

There are at least 3 notes about these errors.

"The issue has been fixed in 11.1.0.7 and 11.2."


Why does a simple loop result in ASYNC_NETWORK_IO waits? The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:

DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);

SET NOCOUNT ON;

WHILE @outer_loop < 50000000
BEGIN
    SET @big_string_for_u = 'ZZZZZZZZZZ';
    SET @outer_loop = @outer_loop + 1;
END;

It accumulates 532 ms of ASYNC_NETWORK_IO waits according to both sys.dm_exec_session_wait_stats and sys.dm_os_wait_stats. The total wait time increases as the number of loop iterations increases. Using the wait_completed extended event I can see that the wait happens roughly every 43 ms with a few exceptions:

In addition, I can get the call stacks that occur right before the ASYNC_NETWORK_IO wait:

sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9

Finally, I noticed that SSMS uses a surprising amount of CPU during the loop (about half a core on average). I'm unable to figure out what SSMS is doing during that time.

Why does a simple loop cause ASYNC_NETWORK_IOwaits when executed through SSMS? The only output that I appear to get from the client from this query execution is the "Commands completed successfully." message.

The documentation for SET NOCOUNT says:

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

You are not running the statements in a stored procedure, so SQL Server sends DONE tokens (code 0xFD) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO waits.
Note the DONE tokens are different from DONEINPROC (code 0xFF) as the documentation notes:

A DONE token is returned for each SQL statement in the SQL batch except variable declarations.

For execution of SQL statements within stored procedures, DONEPROC and DONEINPROC tokens are used in place of DONE tokens.

You will see a dramatic reduction in ASYNC_NETWORK_IO waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;

DECLARE
    @outer_loop integer = 0,
    @big_string_for_u varchar(8000);

WHILE @outer_loop < 5000000
BEGIN
    SET @big_string_for_u = 'ZZZZZZZZZZ';
    SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;

You could also use sys.sp_executesql to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO wait begins:

An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1> had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00

Which decodes to:

TokenType = 0xfd DONE_TOKEN
Status = 0x0001 DONE_MORE
CurCmd = 0x00c1 (193)
DoneRowCount = 0x00000001 (1)

Ultimately, the number of ASYNC_NETWORK_IO waits depends on the client and driver, and what it does, if anything, with all the DONE messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd ran for 2-3 seconds with single digit ms waits; osql around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:


Best approach to maintain many dump for a single structure I have many database equal to each other with differences only in the data. I want to maintain a dump for each database for use in a installation process.

is recommended maintain it as a single file or split into many files?

Always the best practice is to take each database's backup with a separate file.
Why?

You can restore any database to anywhere.
It'll help to refresh you Dev database.
It'll help in PITR.
You can restore any specific table from the specific database backup.
Finally it will save a lot of time while restoring a single Database.

Recomendation:
Keep you database backup in multiple locations. This will help you in case of any disaster.

SAN
NFS
S3
Or any other location apart from the Database server.


Mysql big data solutions I am in charge of a mysql database which is about 12 gb, and it grows pretty quickly (was 3gb a few months ago). I wanted to know what measures i can take when the database grows even larger (lets say the 50-100gb area) to keep the performance good. I heard about sharding and archiving but it was pretty overwhelming so i was hoping to get some clarification here. Thank you very much

It depends on your table structures, data layout and usage pattern. However I wouldn't consider 12 gb or actually anything that can fit into the RAM of a reasonable priced commodity server big data (~100-400 Gb). I work with MySQL servers happily running with terabytes of data. If your tables are well designed, indexed and your queries are well written you won't see issues.

After that it's usually much easier to split your dataset by common usage pattern and logical dependencies. Move tables to a different database. For example split your log tables to a new server. You can repeat that as long as want until you reach the write limit of a single server and your cannot split your database anymore (all tables are strongly related). Then you can think about sharding.


How do I output MySQL logs to syslog? I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS).
I found information that output error log to syslog.

[mysqld_safe]
syslog

But I want to use syslog for logging general logs and slow query logs.
Please advise me how to write config file?

I could not find how to do that in the reference manual.

  http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html

This is MUCH more simply done this way:

cd to mysql folders:

mkfifo mysql-general.log

in my.cnf tell it:

[mysqld]
general-log-file = /path/to/mysql/dir/mysql-general.log

Then, configure your syslog/syslog-ng to read the FIFO pipe and do with it as it will..

In my case, I pipe it across net to a centralized server with only the error logs and slow query logs however.

In situations where you want to also keep local copy, just set it to output to table and file as described above.


What should be the schema of the database if the parent table is being referenced in more than one child table? I have three entities in my database: Category, SubCategory, and Product. Now the thing is SubCategory is dependent on Category, whereas Product can be dependent on SubCategory or directly either on Category.
How should I design the database for such a situation?
As of now, I have created these tables as below:
Category:

CategoryId
CategoryName
HasSubCategory

SubCategory:

SubCategoryId
CategoryId
SubCategoryName

Product:

ProductId
CategoryId
SubCategoryId
ProductName

Now, while creating a Product, if the selected Category's HasSubCategory is true then SubCategory would be selected by the user. Now consider a situation that the user selects a Category whose HasSubCategory is true, and based on the selected Category, SubCategories will be loaded and after selecting a SubCategory, the user submits the Product record. Now, what if the user modifies the SubCategory record which was selected in the Product, and changes its dependent Category. In this case, I need to update the Product record as well and change its Category as per the new Category selected in the SubCategory record.
Is there any better approach to handle such a situation or I shall manage like this only?

My approach in a model:
-- Categories and subcategories table
CREATE TABLE category (id INT PRIMARY KEY,
                       name VARCHAR(255),
                       parent_id INT,
                       FOREIGN KEY (parent_id) REFERENCES category (id));

-- Products table
CREATE TABLE product (id INT PRIMARY KEY,
                      name VARCHAR(255),
                      category_id INT,
                      FOREIGN KEY (category_id) REFERENCES category (id));

How it works: DEMO (MySQL used).
The queries are converted to procedures - only for simplifying their execution call.
If some section is empty we don't display it.
If we want to get the categories list with the info does they have subcategories we simply execute
SELECT *, EXISTS ( SELECT 1
                   FROM category t2
                   WHERE t1.id = t2.parent_id ) has_subcategory
FROM category t1;


Slave stopped : Could not parse relay log event entry My Slave machine stopped somehow, and when I ran START SLAVE to start replication, I got following error,

    SLAVE 1 !!! > show slave status \G
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: < My Master's IP >
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: master-bin.000777
            Read_Master_Log_Pos: 358597143
                 Relay_Log_File: slave-relay.000634
                  Relay_Log_Pos: 95144877
          Relay_Master_Log_File: master-bin.000777
               Slave_IO_Running: Yes
              Slave_SQL_Running: No
                Replicate_Do_DB:
            Replicate_Ignore_DB:
             Replicate_Do_Table:
         Replicate_Ignore_Table:
        Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table: mysql.%,information_schema.%, tmp1.%,performance_schema.%,mysql.%,information_schema.%, tmp1.%,performance_schema.%
                     Last_Errno: 0
                     Last_Error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                   Skip_Counter: 0
            Exec_Master_Log_Pos: 95264000
                Relay_Log_Space: 358481990
                Until_Condition: None
                 Until_Log_File:
                  Until_Log_Pos: 0
             Master_SSL_Allowed: No
             Master_SSL_CA_File:
             Master_SSL_CA_Path:
                Master_SSL_Cert:
              Master_SSL_Cipher:
                 Master_SSL_Key:
          Seconds_Behind_Master: NULL
    1 row in set (0.00 sec)

I followed this post,

https://stackoverflow.com/questions/12097696/mysql-replication-fails-with-error-could-not-parse-relay-log-event-entry

But my doubts are not clear and I don't want my slave to be crashed, So, could anyone please guide me to solve it ?

In My case :
should I try

CHANGE MASTER TO master_log_file='master-bin.000777', master_log_pos=358597143;  ?

Note :
My Read_Master_Log_Pos and Exec_Master_Log_Pos are not same

There are 4 possible explanations mentioned in the error message, and a fifth one that isn't mentioned.

the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log),
the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log),
a network problem, or
a bug in the master's or slave's MySQL code.
not mentioned in the message, it seems possible that this condition could be triggered if your slave is an older version of MySQL server than the master, particularly across versions, such as a master running 5.6.x and a slave running 5.5.x.  Slaves must almost always be the same version as the master, or newer.  When upgrading, slaves are done first, master last.

Replication involves the slave requesting a streaming copy of the master's binary log across the network, which is stored in the relay log by the slave IO thread, then executed by the slave SQL thread.

In this case, the slave SQL thread has found incomprehensible content in the relay log, and has concluded that the log was corrupt on the master, or has been corrupted on the slave, or was corrupted in transit.  TCP is "reliable" but its checksums are somewhat primitive and specific corruption patterns can go undetected.

Using CHANGE MASTER TO will discard the slave's relay logs and restart copying at the specified log position, which needs to match Execute_master_log_pos, which is where the slave SQL thread is currently pointing for its next needed event (and isn't valid).

If the log on the master is corrupt, this will not fix things, because the corrupt data will just be transferred again.  In this case, the only option will be to rebuild the slave.

Otherwise, CHANGE_MASTER_TO should remedy the problem if the fault lies in the slave'a relay log (which can be caused by having run out of disk space on the slave, under just the right conditions) or was caused by the network.

The mysqlbinlog utility can be used to validate the logs on both machines, if desired, and will be necessary on the master if you want to confirm the corruption when CHANGE_MASTER_TO doesn't fix the issue.


Create folder in Sql Server management Studio 2008 How can I group my stored procedure under folders in Sql server management studio 2008, Can I group and move related stored procedure to it.This should maintain the structure during database backup also.

SQL Server/SSMS does not have this functionality. However, you can use SQL Server Data Tools (ideally with source control integration) to organize database objects to your liking independently of the physical database deployment(s).


Why does Log Shipping .TRN file copy just stop I apologize in advance for a long post, but I have had it up to here with the situation of having to delete Log Shipping (LS) configuration and starting it over for any DB that has got this error.

I have LS setup on 3 win2k8r2 servers (primary, secondary, monitor) with 100 databases backed up and shipped transactions from the primary to secondary and monitored by monitor. Backups and copies are run every 15 mins and then the ones older than 24 hrs are deleted.

Some DBs are very active and some not so active but shipped regardless for uniformity sake (basically to make secondary server identical to primary). Some DBs are for SharePoint 2010 and the majority are for inhouse apps.

The issue is that after all LS configs are set up, everything works well for about 3 to 4 days. Then I go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status, because the time since last copy is over 45 mins so no restore has occurred. This seems random and the only errors I see is from a SharePoint 2010 DB (WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a Reporting DB that gets created weekly and LS just cannot figure out which the last copy to backup or to restore is.

I posted here regarding that and I have yet to find a permanent solution.

For my main error (time since last copy) I have not seen anything that could have caused that and I don't get any messages (even though some alert statuses have been ignored for 3 days).

Anyway, I would really appreciate any input on understanding what's causing this issue and how I could fix it.

Restarting both the source and destination servers housing the sql engines seemed to fix the issue. its been a week and all backup, copy and restore has no issues except for just that one SharePoint reporting db WebAnalyticsServiceApplication_ReportingDB_77a60938-####-####-####-##########.
Thanks to everyone for their input, as I at least came out of this more knowledgeable.


SQL queries to get the duplicate records based on trim I have a table that has got the below set of records.

UNMASK  MASK
123 897609
00123   896509
0000456 5632789
456 32567889
5678    5632789

From the above table I need to select only the below records.

  UNMASK
123
00123
0000456
456

(I.E) Whatever values in UNMASK has any number of '0' before the '1-9' and also the row that doesn't have 0s before that 1-9.

Like 123 and there can be 000000123, 456 and 00456, I need such records(only Prefixed with 0s and the original value) in the output using SQL query in Oracle.

I tried the below query but it doesn't work the way I want.

Select UNMASK,TRIM (LEADING '0' FROM UNMASK) from HSA.TEST_TABLE group by UNMASK having count(TRIM(LEADING '0' FROM UNMASK)) > 1;

Test data:

create table test_table(unmask varchar(20), mask varchar(20));
insert into test_table values('123', '897609');
insert into test_table values('00123','896509');
insert into test_table values('0000456','5632789');
insert into test_table values('456','32567889');
insert into test_table values('5678','5632789');

I've done this with a CTE, as I love them. casting the string to a number removes the leading characters, which I find easier than messing around with TRIM.

Query:

with duplicates as
(
  select cast(unmask as number) as unmask_num, count(*) as cnt
  from test_table
  group by cast(unmask as number)
  having count(*)>1
)
select *
from duplicates
join test_table on duplicates.unmask_num = cast(unmask as number)
;

DB Fiddle that demonstrates is here.


Can't connect to SQL Server through SSMS remotely Ok, look apologies if this is a really stupid question, but I'm struggling here.

Just started a company and they use VM's hosted externally for SQL Server.  You access the VM by RDP by putting in an IP address and a port number; e.g. 12.34.56.789:1234.

How do I connect to an instance on this server using SSMS on my local machine?

I've reviewed multiple help articles online.  I notice my SQL Server is set to TCP Dynamic Ports, and not a static port like 1433.  Windows Firewall is turned off so no issue there.  I've checked the netstat -ano and see that TCP is listening on:
[::]:49166 and
I'm able to telnet to the server using the 12.34.56.789:1234 address, but not able to telnet using the 12.34.56.789:(dynamic port address).

Am keen to even try to connect to it through the dynamic port but can't get through to it either.

SQL Browser is on.

Thank you in advance for you help.  Going mad here!!

Windows Firewall is turned off so no issue there.

There will be some form of firewall operating between the machine and the outside world (unless there is something really wrong with your company's infrastructure setup!) and that is most likely blocking access to the relevant ports for external connections.

  How do I connect to an instance on this server using SSMS on my local machine?

It may be that you cannot access the SQL Server directly at all, in which case you'll need to use tools installed on the VM(s) rather than local copies. It may be that there is a VPN seetup that you can connect through. It may be that there is a route through the firewalls connecting to a specific address+port from your location. In any case you will need to talk to your infrastructure people, we know nothing of your network arrangement so are unlikely to be able to help beyond these generic pointers.


Get value from nearest (lower) integer As jkavalik sais: I'm looking for "the greatest value not greater than". I have a table containing a kind of IDs and values like those

ID     value
101    A
2003   B
4005   C

when searching for "2003" I want to get "B"
when searching for "2004"I want to get "B", too.
when searching for "2002" I want to get "A"

If a always had exact matching IDs i would write

SELECT value FROM table WHERE id = 2003

But is there a way to handle my problem describe above? I'm using firebird 2.5.

SELECT FIRST 1 value FROM table WHERE id <= 2003 ORDER BY id desc

Get the greatest value not greater than.


Check if the value from one table exists in another tables in SQL Server I have one table (tbl1) with column ID, the values can be duplicated. I have others tables (tbl2, tbl3...) with column ID , values are unique.
I want to write a trigger on insert row in tbl1 and check if ID in new row has not exists in tbl2,tbl3.... There is part of my code.

    CREATE TRIGGER dbo.tbl1_ID
       ON dbo.tbl1
       AFTER INSERT
    AS
    BEGIN

       SET NOCOUNT ON;
       DECLARE @CHECK int

       SELECT OBJECTID,ID, ROW_NUMBER() over(Order by OBJECTID) as aID into #T1
       from inserted where (ID is not null)

       SELECT @CHECK = COUNT(p.ID from #T1 as p where not exists (select e.ID
                                                                  from dbo.tbl2 as e
                                                                  where p.ID=e.ID))

       IF @CHECK>0
           BEGIN
                RAISERROR("ID ALREADY EXISTS",16,1);
                ROLLBACK TRANSACTION;
           END
    END

I don't know how to check in a range of tables, they are stored in table JoinTables(f.ex.). I suppose I need function here.

Table JoinTables similar to

obID|firsttable|secondtable

 1  |   tbl1   |   tbl2
 2  |   tbl1   |   tbl3
 3  |   tbl1   |   tbl4
 4  |   tblM   |   tbl5
 5  |   tblM   |   tbl6

I think you could get it with a single statement.

CREATE TRIGGER t1_insert ON T1
AFTER INSERT
AS
BEGIN

    IF EXISTS(SELECT
                  1
              FROM
                  inserted
              JOIN
                  (
                   SELECT ID FROM T2
                   UNION ALL
                   SELECT ID FROM T3
                   UNION ALL
                   SELECT ID FROM T4
                  ) others
                  ON others.ID = inserted.ID)
    BEGIN
        PRINT 'YES';
    END
    ELSE
    BEGIN
        PRINT 'NO';
    END

END

db<>fiddle here


SQL Server Management Studio: The columns in table do not match an existing primary key or unique constraint I have two tables:  Students and Courses.  I want to set a foreign key in the Students table from the couseId column to the courseId column in the Courses table.  I have set primary keys to both courseId columns but Management Studio is still giving me the error The columns in table 'Courses' do not match an existing primary key or unique constraint.'  What am I doing wrong?

I think you want a junction table. I have no idea what the point-and-click UI is doing behind the scenes or how you are getting into this situation but I think this makes more sense:

CREATE TABLE dbo.Students
(
  StudentID INT PRIMARY KEY,
  ... Other columns ...
);

CREATE TABLE dbo.Courses
(
  CourseID INT PRIMARY KEY,
  ... Other columns ...
);

CREATE TABLE dbo.StudentCourses
(
  StudentID INT NOT NULL FOREIGN KEY
      REFERENCES dbo.Students(StudentID),
  CourseID INT NOT NULL FOREIGN KEY
      REFERENCES dbo.Courses(CourseID),
  PRIMARY KEY(StudentID, CourseID)
);

Now, I am not entirely clear why one of your images shows SemesterID as part of the key for one of the tables; I don't know enough about the business logic and how you define courses to guess where that should be. But for the student-course relationship, again unless I have a completely bizarre definition of "course," should not be declared in the Students table.

Also, I wouldn't use the CHAR data type for strings that will clearly have different lengths. You should use varchar (or maybe nvarchar, depending on location and your school's educational domain) for course name, and nvarchar for proper names.


In mongodb, is "sh.getBalancerState() is false" the same as "sh.isBanlancerRunning is false"? During upgrading config servers to use WiredTIger, I stopped balancer using sh.setBalancerState(false), and then I run sh.getBalancerState(). The output is false. Does this mean the balancer is not running? After this, I just started to upgrade the config servers to use WiredTiger. But after reading the document carefully, I am not sure whether no migrations are in progress after running sh.setBalancerState(false)? If some migrations are running, but I backup the config data and stopped the config servers one by one. What is the bad effect? Now the config servers are all up with WiredTiger, how to check whether the config servers have the same data, especially config data, meta data....?

Nothing bad happened even if you took the config server offline during a chunk migration. In order for a chunk to be marked as migrated, all three config servers need to be up (Contrary to popular belief they do not form a replica set.).

(The following is a tiny bit simplified for the sake of shortness.)
When a chunk is moved, a global (read cluster wide) lock is written to the config servers. Then, the chunk gets copied over to the target machine. Next, the metadata gets updated so that every mongos asking for data contained in that chunk is pointed to the new server. Only after a successful metadata update, the chunk gets deleted on the old machine. So if anything goes wrong during the chunk migration, the metadata will always point to the correct location, while the lock prevents any data to be written to the chunk. I am not sure when the lock is lifted, but iirc it is done after the metadata update.

So – since all three config servers need to be online for metadata updates – as soon as you put a config server offline, you prevent any chunk migrations from taking effect.


mysqlslap chokes on strings that contain ";" delimiter? I'm having a problem passing preexisting files full of SQL statements into mysqlslap.

For example, I have a file named create.sql that contains my table structure (as dumped by mysqldump), with normal ; delimiting.

I also have a file called slap.sql (actually a slightly munged general-log-file, but this is a tiny example that reproduces the error) that contains

INSERT INTO comments VALUES ("I like winks ;) and frowns :(");

And I run:

mysqlslap --delimiter=";" --create create.sql --query thing.sql

I get the error:

mysqlslap: Cannot run query INSERT INTO comments VALUES ("I like winks
ERROR : 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 '"I like winks' at line 1

Which is consistent with MySQL terminating the statement at the ; that's in the middle of a string.

What can I do to make mysqlslap take --create data from mysqldump and not hork on semicolons embedded in strings in the --query data?  Is it possible that mysqlslap does not follow normal parsing rules when you pass a file in to the --query parameter?

Looking briefly at the source code, it looks like mysqlslap doesn't do any sophisticated parsing for delimiters... it's just splitting strings.

If you write each of your queries on a single line, you don't have to terminate them -- the default statement delimiter is a newline.

const char *delimiter= "\n";

With one query per line, it splits the line and executes each statement.

The ; delimiter isn't actually needed at the end of each query to make the --query file valid and the server has no problem executing the example query you provided with the semicolon removed.

You could modify the output file from mysqldump to use a different delimiter, like $$, using sed or perl and then use that delimiter in your query file also.

mysqldump [args] | perl -pe 's/;$/\n\$\$\n/g' > create.sql

edit: oops, yeah, this is really primitive... multi-characterbyte delimiters aren't really even supported.

if (user_supplied_query)
    actual_queries= parse_delimiter(tmp_string, &query_statements, delimiter[0]);


Why is mysql logging to syslog? I have installed Multiple MySQL instances and i have two configuration files one for each one is using default location of /etc/mysql/my.cnf.

other server i have started like

root@ABC-240:/usr/local/mysql# bin/mysqld_safe  --defaults-file=/etc/mysql/binary_my.cnf &

root@ABC-240:/usr/local/mysql# 120104 10:19:34 mysqld_safe Logging to syslog.
120104 10:19:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

I have set the error log in both of the configuration files but one of them as above is logging to syslogs.

Why is it like that?

It may be possible that both instances of MySQL are trying to record log entries to the same location (same file).

I do not think mysql allows more that one error log per MySQL instance.

Here is something further on using error log with mysqld and mysqld_safe:

  No error log renaming occurs when the logs are flushed in any case if
  the server is not writing to a named file.

  If you use mysqld_safe to start mysqld, mysqld_safe arranges for
  mysqld to write error messages to a log file or (as of MySQL 5.1.20)
  to syslog:

  • Before 5.1.20, mysqld_safe behavior is to log to a file, using the
  default error log file if the --log-error option is not given to
  mysqld_safe. Otherwise, mysqld_safe uses the file name specified using
  --log-error=file_name.

  • From 5.1.20 on, mysqld_safe has two additional error-logging
  options, --syslog and --skip-syslog.

  In 5.1.21 and up, the default with no logging options is
  --skip-syslog, which is compatible with the default behavior of writing an error log file for releases prior to 5.1.20. To explicitly
  specify use of an error log file, specify --log-error=file_name to
  mysqld_safe, and mysqld_safe will arrange for mysqld to write messages
  to a log file. To use syslog instead, specify the --syslog option.

  In 5.1.20 only, the following conditions apply: 1) The default is to
  use syslog, which is not compatible with releases prior to 5.1.20. 2)
  Logging to syslog may fail to operate correctly in some cases; if so,
  use --skip-syslog or --log-error.

  For logging to syslog, messages from mysqld_safe and mysqld are
  written with a tag of mysqld_safe and mysqld, respectively. As of
  MySQL 5.1.21, to specify a suffix for the tag, use --syslog-tag=tag,
  which modifies the tags to be mysqld_safe-tag and mysqld-tag.

  If you specify --log-error in an option file in a section that mysqld
  reads, mysqld_safe also will find and use the option.

Since you started mysqld_safe to start mysqld, you just need to start mysqld_safe and explicitly choose a separate file for the error of the other MySQL Instance using the --log-error option.


How to get the free temporary memory available in a server How to get the free temporary memory available in a server

For example:- I am running one SSIS package to upload data to tables. First it will move all the data to temporary table and then it will upload to actual Tables. So before executing this package is there any way to get the available remaining temporary memory size? including SQL Server ( Because there are some parallel jobs are running by SQL Server 2005 and using the temporary space).  So if I can get the remaining memory size I can decide whether I can run the package or not. Please help me.

There is no such a thing in sql server as temporary memory. Temp tables and table variables created in tempdb and may use shared buffer pool of sql server as any other objects do

But

if you still insist on knowing everything about server memory use the following script:

set nocount on
declare @PI sysname
declare @PI_MemoryManager   sysname
declare @TotalServerMemoryKB            numeric
declare @TargetServerMemoryKB           numeric
declare @MemoryConnectionsKB            numeric
declare @MemoryLocksKB                  numeric
declare @MemoryOptimizerKB              numeric
declare @MemoryMinKB                    numeric
declare @MemoryMaxKB                    numeric
declare @MemorySortHashKB               numeric
declare @PhysicalRAMKB                  numeric
declare @qs_xp_msver table(
    [idx] [int] NULL,
    [name] [sysname] NOT NULL,
    [internal_value] [int] NULL,
    [char_value] [sysname] NULL
)
insert into @qs_xp_msver (idx, name, internal_value, char_value)
exec    master.dbo.xp_msver 'PhysicalMemory'
select  @PI = case when convert(sysname,ServerProperty('ServerName')) like '%\%' then 'MSSQL$' + @@servicename else 'SQLServer' end
set     @PI_MemoryManager   = @PI + ':Memory Manager'
select  @TotalServerMemoryKB    = sum(case when lower(p.counter_name) = 'total server memory (kb)'      then convert(numeric,cntr_value) else 0 end)
    ,   @TargetServerMemoryKB   = sum(case when lower(p.counter_name) = 'target server memory (kb)'     then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryConnectionsKB    = sum(case when lower(p.counter_name) = 'connection memory (kb)'        then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryLocksKB          = sum(case when lower(p.counter_name) = 'lock memory (kb)'              then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryOptimizerKB      = sum(case when lower(p.counter_name) = 'optimizer memory (kb)'         then convert(numeric,cntr_value) else 0 end)
    ,   @MemorySortHashKB       = sum(case when lower(p.counter_name) = 'granted workspace memory (kb)' then convert(numeric,cntr_value) else 0 end)
from sys.dm_os_performance_counters p
where object_name  = @PI_MemoryManager
and lower(counter_name) in
    (
        'total server memory (kb)'
    ,   'target server memory (kb)'
    ,   'connection memory (kb)'
    ,   'lock memory (kb)'
    ,   'optimizer memory (kb)'
    ,   'granted workspace memory (kb)'
    )
and instance_name <> '_Total'
select  @MemoryMinKB        = sum(case when name like 'min%'        then convert(numeric,value_in_use) * 1024 else 0 end)
,       @MemoryMaxKB        = sum(case when name like 'max%'        then convert(numeric,value_in_use) * 1024 else 0 end)
from
    sys.configurations with (readpast)
where
    name in ('min server memory (MB)', 'max server memory (MB)')
declare @PhysicalRAM    numeric
select  @PhysicalRAM = ceiling(convert(numeric,
            substring(char_value, charindex('(', char_value) + 1, charindex(')', char_value) - 1 - charindex('(', char_value) ) )
            / (1.0))
from    @qs_xp_msver
if (@PhysicalRAM is null)
    set @PhysicalRAMKB = 0
else if (@PhysicalRAM < 0)
    set @PhysicalRAMKB = (convert(numeric,4096.0)*1024*1024 + @PhysicalRAM) /1024
else
    set @PhysicalRAMKB = @PhysicalRAM/1024
if (@PhysicalRAMKB < @MemoryMaxKB and @PhysicalRAMKB > 0 )
    set @MemoryMaxKB = @PhysicalRAMKB
set nocount off
select
        @TotalServerMemoryKB            as CurrentMemoryKB
    ,   @MemoryMinKB                    as MemoryMinKB
    ,   @MemoryMaxKB                    as MemoryMaxKB
    ,   @MemoryConnectionsKB            as MemoryConnectionsKB
    ,   @MemoryLocksKB                  as MemoryLocksKB
    ,   @MemoryOptimizerKB              as MemoryOptimizerKB
    ,   @MemorySortHashKB               as MemorySortHashKB
    ,   @PhysicalRAMKB                  as PhysicalRAMKB
    ,   @TotalServerMemoryKB            as TotalServerMemoryKB
    ,   @TargetServerMemoryKB           as TargetServerMemoryKB


PostgreSQL array column, querying and grouping by array field I have the following  table data in a table named queryables in Postgres 9.4:

 queryable_id | liked_count | users_who_like_ids
--------------+-------------+--------------------
         2376 |           3 | {1,80,78,101, 188}
        18771 |           1 | {78,101, 123,125}
         1790 |           1 | {78}
         2257 |           1 | {78}

and I want to select for users who like 1,78,80

The user_who_like_ids column was defined as a text array:

 users_who_like_ids      | text[]                      | default '{}'::text[]

but I has been updated to an integer.

users_who_like_ids      | integer[]                   | default '{}'::integer[]

I perform my query as this:

SELECT * FROM  queryables WHERE users_who_like_ids  @> ARRAY['1','78','80'];

but this requires users_who_like_ids to match ALL of them. I'd like it to be ANY of them, and then group by the length.

How would I do this?

Use the "overlaps" operator:

select *
from  queryables
where array[users_who_like_ids]  && ARRAY['1','78','80'];

Your design is really quesionable: first why are you storing numbers as strings? And secondly why don't you store this as a properly normalized one-to-many relationship?


MySQL Escape Characters I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP.

This is an example:

The name to search is: Tommy -

The generated (error) query is:

Select *
From users
Where match(name,username)
            Against('+tommy -*' in boolean
                          mode)

What is the best way to fix it, and why the mysql_escape functions don't cover it?

MATCH(...) AGAINST('+tommy' IN ...)
    AND ... LIKE '%tommy -%'

That is, use FULLTEXT to do what it does best (search for words), then check the results against something more complex via LIKE or REGEXP.

Granted, it will take some intelligence in your app code to construct the suitable combination of MATCH and [R]LIKE.  But the result is fast and cover a lot more than FULLTEXT can do by itself.

Note:  The optimizer will do the MATCH first, then filter on the other stuff.


How can I resolve SSIS Excel Connection Manager Error 0xC0209303? I have created an SSIS package that imports an excel file into a SQL Server table.

The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where the package will be scheduled I get the below error (from a text file I am outputting errors to using SSIS logging).

After researching, the only recommendations I could find was to set the Run64BitRuntime property to false which I did but still no luck. I doubt this is what is causing my error though because the error is not specifying anything regarding 64 bit (as was the case in the articles I found).

I also thought that it might be that the Server does not have the appropriate Excel drivers but I don't think that is the case either because usually the error message would say something about the drivers not being registered.

I currently don't have access to remote into the server. I can only upload the package to a folder and then it is ran by an application so the only error messages I can see are what is in the text error log I have created.

  Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The
  AcquireConnection method call to the connection manager "Envision"
  failed with error code 0xC0209303.  There may be error messages posted
  before this with more information on why the AcquireConnection method
  call failed.

"Envision" is the name of my excel connection manager.

I populate the Excel File Path and the Connection String using expressions.

The connection string expression looks like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::SourceFilePath] +";Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";"

The SSIS Pacakge is executed by a windows username/account. I think it might be a web services account. (BDS_sprtIIS)

Does anyone have any solutions or suggestions as to how to resolve this issue of the package only working on my local machine but not on the actual server where the package will be deployed to?

I found the below answer on another forum, could it be what is causing my issues? They are basically saying that the Excel connection manager tries to access the users temp folder for some reason and if it does not have access to that folder then it will fail:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da77919c-0161-4eb5-bf89-7107d839435a/the-acquireconnection-method-call-to-the-connection-manager-excel-connection-manager-failed-with?forum=sqlintegrationservices

  I noticed too that the Microsoft.JET.OLEDB.4.0 driver will try to read
  the temp directory underneath the profile of the logged in user.

.

  ... We run our SQL Agents using a lower
  level domain account and run our SSIS packages using a Proxy Account.
  You are correct as Procmon confirmed it for me as well.  I gave the
  Proxy Account rights to the profile's temp directory (C:\Documents and
  Settings\SQLAgentDomainAccount\Local Settings\Temp) and it worked!

I am not using using SQL Server Jobs or Proxy accounts. The package is simply executed by a windows account most likely thru a command line script.

The windows account has access to the file but I am not sure if it has access to it's "TEMP" folder (which I do not ever reference in the package so I don't know why it would need to have access to that folder)...

There were 2 issues that were preventing the package from running on the server. Below are the 2 issues and the solutions I found.

The package is executed by an application that uses the 64-bit DTexec utility by default but the package needs to be run using the 32 bit version of the utility to be able to properly access the Excel file through the Excel connection manager.

I created a "wrapper" SSIS package that uses an Execute Process Task that calls the 32-bit (instead of 64 bit) DTExec utility and passes the command to open the original  package.

I also needed to install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable.

Further Reading:

Microsoft.ACE.OLEDB.12.0 is not registered (Stack Overflow)


Ola's maintenance solution is not processing sysjob* tables I have used Ola Hallengren's script to back up and maintain databases.

However, it does not perform any reindex or reorganize operations against certain tables in msdb, such as sysjobhistory, even though they have fragmentation above the defined thresholds. Here is the script I am using:

EXECUTE dbo.IndexOptimize
  @Databases = 'SYSTEM_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 5,
  @FragmentationLevel2 = 30,
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y'

You need an additional parameter to tell Ola's script to consider Microsoft-shipped objects, since those job-related tables in msdb are marked as is_ms_shipped:

@MSShippedObjects = N'Y'

(This parameter defaults to N'N', which means ignore Microsoft-shipped objects, and is documented here.)


How do I filter null values from a query? I am trying to filter the null values from the following query and it keeps giving me 2 records instead of 1.

Here is my base query

The following query brings me back 2 results and I only need to see one result as there is only one vender for vendor_id 91.  it's giving me a record for the null and an 'N' for the result of my case statement.

SELECT CASE WHEN vs.User_Data_Field1 = '14' THEN 'Y'
            WHEN vs.User_Data_Field2 = '14' THEN 'Y'
            WHEN vs.User_Data_Field3 = '14' THEN 'Y'
            WHEN vs.User_Data_Field4 = '14' THEN 'Y'
            WHEN vs.User_Data_Field5 = '14' THEN 'Y'
            ELSE 'N'
       END USER_DATA_FIELD
FROM RHOP..EDSLKP.VENDOR_SITES VS
where vendor_id = '91'
GROUP BY VENDOR_ID,VS.USER_DATA_FIELD1,VS.USER_DATA_FIELD2,VS.USER_DATA_FIELD3,VS.USER_DATA_FIELD4,VS.USER_DATA_FIELD5
ORDER BY VENDOR_ID

My question is, How do I code this to only bring back one 'N' for the one vendor?

Thanks for any help

If I'm interpreting your question correctly (that you want to return Y if any User_Data_Field for any matching row is 14), you can do something like:

SELECT CASE WHEN SUM(IIF(User_Data_Field1 = 14, 1, 0)
                    +IIF(User_Data_Field2 = 14, 1, 0)
                    +IIF(User_Data_Field3 = 14, 1, 0)
                    +IIF(User_Data_Field4 = 14, 1, 0)
                    +IIF(User_Data_Field5 = 14, 1, 0)) > 0 THEN 'Y'
ELSE 'N'
END USER_DATA_FIELD
FROM RHOP..EDSLKP.VENDOR_SITES VS
WHERE vendor_id = '91'
GROUP BY VENDOR_ID
ORDER BY VENDOR_ID

IIF is only supported in SQL 2012 and above, so prior to that you'll have to stick with CASE WHEN.


why it is doing index seek I created a sample table as below

CREATE TABLE [dbo].[StatisticsDemo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
   ) ON [PRIMARY]

Then I inserted below data as below:

SELECT NAME,COUNT(*) AS COUNT
FROM StatisticsDemo
GROUP BY NAME

NAME    COUNT
-------------
AABBCC  59999
XXYYZZ  1

Then I created below Non Clustered Index :

CREATE NONCLUSTERED INDEX [NCI_STATISTICSDEMO_NAME] ON [dbo].[StatisticsDemo]
(
    [Name] ASC
)

Now I ran the below query:

SELECT NAME FROM [dbo].[StatisticsDemo]
WHERE NAME = 'AABBCC'

As expected it is returning 59999 rows but it is doing Index Seek on Non Clustered Index. But as per my knowledge, it should do Index Scan as 99.99% of data satisfied the filter criteria mentioned in the select query.

Can some please tell me why it is doing Index Seek instead of Index Scan?

The purpose of the entire activity was to prove(as I am about to give presentation on statistics) that SQL Server looks into statistics to identify the number of records which matches the filter criteria of query before preparing the execution plan and based on the % of records matches out of total records in table, it will either decide to do a SCAN or SEEK. If % of records matches is approximately equal to total number of records in table, it should do SCAN. But that is not happening. Same is the case when I am using AdventureWorks2016 Database and running below query:

select * from [Sales].[SalesOrderHeader] WHERE SalesOrderID >= 43659 AND
SalesOrderID <= 73659

The above query returning 30001 records out of 31465. But it is still doing Clustered Index Seek.

I am getting terribly confused and it is shaking my concepts. :( Can some please help.

PS: I cleaned Plan cache as well but no luck. SQL Server version is 2016.

The purpose of the entire activity was to prove(as I am about to give
  presentation on statistics) that SQL Server looks into statistics to
  identify the number of records which matches the filter criteria of
  query before preparing the execution plan and based on the % of
  records matches out of total records in table, it will either decide
  to do a SCAN or SEEK. If % of records matches is approximately equal
  to total number of records in table, it should do SCAN.

this is incorrect so explains why you aren't seeing it. The BETWEEN 43659 AND 73659 range seek is doing a partial scan. It is just able to use the B-tree to seek into the point where to begin the scan (so avoid reading anything lower than 43659 ) and potentially exit early if there are rows with values greater than 73659.

For the rows that are in the range it just reads the pages and follows the linked list to the next leaf page in exactly the same way as an index ordered scan does.

There is no reason to want a scan here. At best it saves a handful of logical reads for navigating from the root to the leaf to find the start point but at the expense of reading additional rows outside the range seeked.


SQL Server distributor server move I need to move my distributor server to a brand new server without resnapshotting it. The scenario I have is similar to the one defined in below link.

http://www.sqlservercentral.com/Forums/Topic844095-291-1.aspx

Environment:

1 publisher
1 distributor
4 subscribers
Replication type: Transactional replication with updatable subscription

I want to know if someone has tried the solution given in above link or if you have any alternate solution.

I didn't try this but just giving an approach I would follow.

The log reader scans all the log records and keeps it in distributor DB. The subscriber reads the records from the distributor DB, then inserts it in subscribers and marks the last record it read.

Considering the above topology, I don't see any issues with moving the distributor DB. Assuming you are moving the publisher(push) or subscriber(pull) involved and the server name remains the same.

In a Push model, the distributor DB resides in publisher and in pull model, the distributor resides in the subscriber DB.

Replication stores all the server information for all publishers, distributors, and subscribers in the database. So if you go with this approach I would suggest the below steps:

Stop all jobs (log reader, distributor)
Move all databases(attach, detach) to new server with same server name

The only issue I see here is the instance name being the same as the old one, which can be tricky. You also can try testing this yourself with a single table replication and with the same setup.


Excluding Null Values In my syntax why does the NOT IN statement not exclude NULL values?  To me, if NULL does not exist in #GoodAnswers it should return the dv

Below is syntax, can someone help explain this to me?

Create Table #GoodAnswer
(
  answer1 varchar(100)
  ,dv varchar(10)
)

Insert Into #GoodAnswer Values
('Blue','Yes'),
('Pink','No'),
('Purple','No')

Create Table #Data
(
  bbc varchar(100)
  ,answer varchar(100)
)

Insert Into #Data Values ('lal', NULL), ('raj', 'Orange'), ('enif', 'Zoo')

Select bbc
    , case
        when answer not in (
            Select answer1
            from #GoodAnswer
        ) then (
            Select answer1
            from #GoodAnswer
            where dv = 'Yes'
        )
        else answer
        end as [Valid Answer]
FROM #Data

Drop table #GoodAnswer
Drop Table #Data

My data returned from this result-set is

+------+--------------+
| BBC  | VALID ANSWER |
+------+--------------+
| lal  | NULL         |
| raj  | Blue         |
| enif | Blue         |
+------+--------------+

I feel that instead of NULL being returned it should return Blue since that is the dv in the table

NULL is special. It is neither equal nor non-equal.

Given the same temp tables in your example, consider the results from these queries:

--returns 3 rows
SELECT * FROM #Data;

--returns 2 rows
SELECT * FROM #Data
WHERE answer NOT IN (SELECT answer1 FROM #GoodAnswer);

--returns 0 rows
SELECT * FROM #Data
WHERE answer IN (SELECT answer1 FROM #GoodAnswer);

This is because the definition of  NULL is that it is unknown. Since you don't know the value of it, you can't evaluate it's equality or inequality. Instead, you have to explicitly query for IS NULL or IS NOT NULL.


MariaDB 10.2 - SQL Dump of specific columns I am aware of the --where condition while taking dump of table where we can give condition on rows. But I have a table of the size 15GB and have over 100 columns.I need a dump of few columns out of it, and I want to do this as scheduled job where I will take dump of few columns of the source DB and store it to another server for reporting purpose.I also tried INTO OUTFILE but It won't save output as .sql and as INSERT statement.

Create a view v_ip that contains only the columns you want, and then dump that view into a csv file.

select * from v_ip into outfile '/tmp/test.sql'
   fields terminated by ','
   enclosed by '"'
   lines terminated by '\n';

Importing should be done with "load data".


Multi-thread Informatica connections to use different processors on target database server See this question for reference.

Can data streams from Informatica to SQL Server be multi-threaded?

I've got most of my problem figured out.  I've split up the one large table into four smaller tables.  I'm now moving the data from the four source tables to four target tables.  Each in their own mapping.  In Workflow Manager, each mapping is a separate session, inside of its own workflow.  And then I have a master workflow with four command tasks calling the four workflows.

How do I isolate the connections so that each command connects to the target SQL Server database, so that each runs on a different processor?

EDIT:

Informatica also has functionality called "Partitioning", which does exactly what I did here.  It splits the data up however you want.  But there is some Informatica server level setup for this to work.

When the "Max Degree of Parallelism" configuration in SQL Server (server properties > Advanced) to 0, the server will split up the connections to use as much of the server as it can.  With the way I set up the commands in Informatica, there are four separate processes.  When I tested on a 2 core server, it used two cores.  When I tested on a 4 core server, it used 4 cores.  Everything was running in parallel, or asynchronously.  It made the ETL process fly!


"symlink" data to new database I am just trying to figure some ways to do manage archiving of some/most of our Application data within the database and wondering if something like this would be possible:

Archive anything with a status of resolved, and a data updated over 3 years
Move MOST, but not all that data to the archive database, and replace the values within the current production database with a “SymLink” that points to the archived database?

Here is a simplified example:

#######################################################################################################
###                                         Active_Prod                                             ###
#######################################################################################################
#  ALIASAPPTYPE     ALIASAPPREASON  PZINSKEY                PZPVSTREAM                                #
#  App_Type_1234    New Enrollee    132387Something6357997  <SYMLINKED to Archive_Prod.pzpvstream>    #
#                                                                                #
#                                                                                #
#######################################################################################################
###                                     Archive_Prod                                                ###
#######################################################################################################
#  ALIASAPPTYPE     ALIASAPPREASON  PZINSKEY                PZPVSTREAM                                #
#  App_Type_1234    New Enrollee    132387Something6357997  [BLOB Data]                               #
#######################################################################################################

So the query for select * from Active_Prod would return the following results:

ALIASAPPTYPE    ALIASAPPREASON  PZINSKEY                PZPVSTREAM
App_Type_1234   New Enrollee    132387Something6357997  [BLOB Data]

We would not be concerned with updating or inserting data as the Archive_Prod database would be set to read only anyway.
My thinking here is we could drastically reduce the Active DB2 instance by archiving most of the date (The bulk of the data resides in the BLOB anyway), but keep the “Key” fields in the “Active” database for speedier lookups.
But by creating a symlink of the data, we can improve the performance of PEGA, and improve the backup/restore times by dramatically reducing the overall size of the database.

You can achieve something like this using a federated view. After configuring the federated data source as described in the manual, you would create a nickname for the archive table

CREATE NICKNAME Archive_Prod FOR archive_server.archive_schema.Archive_Prod;

After that you would create a view joining the two tables:

CREATE VIEW v_active_prod AS
  SELECT local.aliasapptype, local.aliasappreason, local.pzinskey, remote.pzpvstream
  FROM Active_Prod local
  INNER JOIN Archive_Prod remote
  ON (local.aliasapptype, local.aliasappreason, local.pzinskey)
   = (remote.aliasapptype, remote.aliasappreason, remote.pzinskey)

assuming aliasapptype, aliasappreason, pzinskey is your primary key.

If you want to fetch your BLOBs regardless of where they reside, you may want to choose an outer join instead:

CREATE VIEW v_active_prod AS
  SELECT local.aliasapptype, local.aliasappreason, local.pzinskey,
         COALESCE(local.pzpvstream, remote.pzpvstream)
  FROM Active_Prod local
  LEFT JOIN Archive_Prod remote
  ON (local.aliasapptype, local.aliasappreason, local.pzinskey)
   = (remote.aliasapptype, remote.aliasappreason, remote.pzinskey)

Don't expect performance to be stellar though when reading BLOBs from a federated data source.


Returning empty string when string_agg has no records I am trying to return a text field in a PostgreSQL query that is of the form
'stringOne' || string_agg(field, ',') || 'stringTwo'

where for certain elements in the group clause, field is always null. I want, and expect, to end up with stringOnestringTwo in that case, but instead I get NULL.
Why is this, and how do I accomplish what I'm trying to do?
Example
Suppose I have the tables
foo                 bar
+----+--------+     +----+-------+--------------+
| id | name   |     | id | fooid | baz          |
+----+--------+     +----+-------+--------------+
|  1 | FooOne |     |  1 |     1 | FooOneBazOne |
|  2 | FooTwo |     |  2 |     1 | FooTwoBazTwo |
+----+--------+     +----+-------+--------------+

and I run the query
SELECT
  foo.name AS foo,
  'Bazzes: ' || string_agg(bar.baz, ', ') AS bazzes
FROM
  foo LEFT JOIN bar ON bar.fooid = foo.id
GROUP BY
  foo.name

Then I want (and expect) to get the resultset
+--------+------------------------------------+
| foo    | bazzes                             |
+--------+------------------------------------+
| FooOne | Bazzes: FooOneBazOne, FooOneBazTwo |
| FooTwo | Bazzes:                            |  <== NOT NULL
+--------+------------------------------------+

but instead, the second row is ('FooTwo', NULL). How can I modify this query so that the second row returns ('FooTwo', 'Bazzes: ')?

Use COALESCE to catch and replace NULL values:

SELECT f.name AS foo
     , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes
FROM   foo f
LEFT   JOIN bar b ON b.fooid = f.id
GROUP  BY 1;

concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with separator"), though, to avoid the trailing space.

concat_ws(' ', 'Bazzes:', string_agg(b.baz, ', ')) AS bazzes

Why NULL?

Almost all aggregate function return NULL if all source fields are NULL (no non-null values, to be precise) - count() being the exception for practical reasons. The manual:

  It should be noted that except for count, these functions return a
  null value when no rows are selected. In particular, sum of no rows
  returns null, not zero as one might expect, and array_agg returns null
  rather than an empty array when there are no input rows. The coalesce
  function can be used to substitute zero or an empty array for null when necessary.

Related:

How to concatenate columns in a Postgres SELECT?
Combine two columns and add into one new column


Cannot delete proxy (7). It is used by at least one jobstep. Change this proxy for all jobsteps first I have written a script to delete and create a proxy but getting the following error

Cannot delete proxy (7). It is used by at least one jobstep. Change this proxy for all jobsteps first.

USE [msdb]
GO

-- Drop the proxy if it already exists

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'CoreRefData')
BEGIN
  EXEC msdb.dbo.sp_delete_proxy @proxy_name = N'CoreRefData'
END
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'CoreRefData',@credential_name=N'CoreRefData',
        @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'CoreRefData', @subsystem_id=11
GO

EXEC dbo.sp_grant_login_to_proxy
    @login_name = N'INT\svc-w-corerefdata-de',
    @proxy_name = N'CoreRefData' ;
GO

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-proxy-transact-sql

Your problem is:

  If a job step refers to the proxy specified, the proxy cannot be
  deleted and the stored procedure fails.

You should be able to use this query to get the proxy_id.

SELECT * FROM sysproxies WHERE NAME = 'CoreRefData'

Then run this to see what jobs are using the same proxy.  Change the proxy_id from 1 to whatever you get from above query.  If this query does not return any record you are good to delete the proxy but if it does records your delete will fail as explained in the above Microsoft document.

USE msdb;
GO
SELECT sysjobsteps.job_id,
       sysjobs.NAME    AS 'JobName',
       sysjobsteps.step_id,
       sysjobsteps.step_name,
       sysjobsteps.subsystem,
       sysjobsteps.last_run_date,
       sysjobsteps.proxy_id
       --, sysjobsteps.step_uid
       ,
       sysproxies.NAME AS 'ProxyName'
FROM   sysjobsteps
       LEFT JOIN dbo.sysproxies
              ON sysjobsteps.proxy_id = sysproxies.proxy_id
       LEFT JOIN dbo.sysjobs
              ON sysjobsteps.job_id = sysjobs.job_id
WHERE  sysjobsteps.proxy_id = 1

You can do few things here (the goal is to disassociate the proxy with any Job step):

Change those Job steps to run under different account temporarily. It can be SQL Agent account for example.
Script out those job steps and delete temporarily.

Once done you should be able to run your script without any error.


Several SQL syntax questions I have several small questions and I wrote some simple examples to clarify them:

Will a nested aggregate take one value from from all groups? Like the minimal maximum of all groups? or the minimum count?

Select x, min(max(z))
From y
Group by x

On the same note, is there any merit in doing the following to get the minimal count from all groups, or the second line is unnecessary?

select x, min(count(*))
select x, count(*)
From y
Group by x

If both are invalid, how would you do a query on all groups like taking the minimum of all maximums in each group?
Can you do a query inside a "from"?

Select x
From y natural inner join (select z AS y
                           from foo)

Is it allowed to do from * after a group by?

Select x
From y
Group by x
Having avg(x) > (select *
                 from *
                 where x > 1)

and if not, how would you do a query on each group after the group by?

Note: this isn't some live version of SQL server, just old theoretical SQL.

re 1)

With standard SQL the only way to do that is:

select min(cnt)
from (
  select x, count(*) as cnt
  from y
  group by x
) t

re 2)

yes you can join to a query, but you need to give the derived table an alias

Select x
From y
  natural join (select z AS y
                from foo) as t;

That assumes that the table y also has a column y - otherwise there wouldn't be two identical columns that the natural join could use.

But in general you should avoid the natural join. Use an explicit join instead:

select x
from y
  join (select z AS y
       from foo
  ) as t on t.y = y.id;

re 3)

No, from * is never allowed. But I have no clue what you intend to do with that. The sub-select used with > (or < or =) has to return exactly one row and exactly one column so you would need something like:

Select x
From y
Group by x
Having avg(x) > (select count(*) -- no idea what you would want to do here
                 from y
                 where x > 1);

If the subselect returns more then one row you would need to use ANY

Select x
From y
Group by x
Having avg(x) > ANY (select x -- still only ONE column allowed
                     from y
                     where x > 1);


Postgresql and jsonb: perform a like over all values of an array Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email:

["email1@foo.com", "email2@foo.com", "email3@foo.com"]

I want to be able to get a record that has an email like "email2".

I tried the following

SELECT * FROM users WHERE jsonb_array_elements(emails) like 'email2';

But it does not work (obviously).

If somebody can help... Thanks!

You can use the following query

SELECT
    id_user
FROM
    users
    JOIN LATERAL jsonb_array_elements(emails) AS x(email) ON true
WHERE
    email::text like '%email%' ;

jsonb_array_elements() returns a SET OF jsonb objects. You join them to user_id using a JOIN LATERAL. Then use a WHERE clause where you convert these jsonb objects to text, and then use your like '%email%'.

You can check a simple setup at dbfiddle.

NOTE: If you do not want repeated values: GROUP BY.


What to do when the active log directory is full? Here's my database configuration:

 Log file size (4KB)                         (LOGFILSIZ) = 125000
 Number of primary log files                (LOGPRIMARY) = 10
 Number of secondary log files               (LOGSECOND) = 0
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /db2/C21/log_dir/NODE0000/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0025598.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = YES
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100

Now /db2/C21/log_dir utilization is 100%. What  to do in this situation?

the upper limit of log file size, combined with the upper limit of the number of log files (logprimary + logsecond), gives an upper limit of 1024 GB of active log space.

You can increase the size of the log file size setting by issuing the command:

db2 UPDATE db cfg for <databaseName> using LOGFILSIZ <new_value>


Automatically restart SQL Server 2008 Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, but I was curious if there was a better way of doing this.

Please nevermind how this is indicative of a larger problem of operation, I've been arguing this for a month and we've ended up here.

Thanks :).

I won't go into detail about why it's not a great idea, since you're already aware of all that. For anybody else stumbling across this answer: Try not to do this if possible!

For the occasions when I need to schedule an after-hours restart (patching, configuration changes, etc), I normally just run this in a batch file scheduled via Windows Task Scheduler:

net stop SQLSERVERAGENT
net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

Note that service names may vary if you aren't restarting the default instance. Also, the user account that the scheduled task runs under will need to have permission to restart the service.

I don't see any reason why this wouldn't work with a recurring schedule, as opposed to the one-time runs that I do.


MS SQL Server, ask password before drop specific DB I've got 2 databases which are very important for our organization. I would like to prevent an accidental drop/delete of them... Do you have any suggestion? I was thinking about asking a password before perform the drop. Is this possible in MS SQL Server?

You should, as the comment says, rely on permissions for this.

Strictly speaking, is not possible to "ask password". Where would the password dialog even be displayed?? SQL Server is a client server product that accept commands from any client that uses the TDS protocol. That may be Management Studio, sqlcmd.exe, a PowerShell script, a ADO.Net SqlClient application. Anything.

If you must, then you can deploy, as a last resort, a DDL trigger. A DDL trigger will not replace permissions, but it can prevent an accidental drop.

CREATE TRIGGER safety
ON SERVER
FOR DROP_DATABASE
AS
   PRINT 'You must disable Trigger "safety" to drop databases!'
   ROLLBACK;

You can enhance the logic to check for database name, my purpose is not to give you a copy-paste working code.


Why MySQL init_connect variable gets updated after rebooting? After SET GLOBAL init_connect=''; query everything works fine. However after rebooting the server or restarting the MySQL service it updates the init_connect value to some random value (OÇÿOÇÖ). Do I need to set init_connect to '' after every reboot or is there any solution for that?

Thanks in advance,

`

You will have to find the my.ini, look for init_connect, and change it, remove it, or comment it out.

Then, run

net stop mysql
net start mysql

If the values keep getting reset anyway, you have to do the following:

Create a file called C:\mysql_init.sql the the contents

SET GLOBAL init_connect='';

Go back to my.ini and add this line

init_file=C:/mysql_init.sql

then run

net stop mysql
net start mysql

If you do not do this, you will have to run SET GLOBAL init_connect=''; after every reboot

This is funny. I talked about this before : Screwed up MySQL init_connect. Based on that old post of mine, I would check what character sets you are working with.

Perhaps you can put something bogus like

[mysqld]
init_connect='SET autocommit=1'

and restart mysql.


Data extraction from 2 tables Data in table A:

X   Y
------
m1  r1
m1  r2
m1  r3
m2  r1
m2  r4
m2  r5

Data in Table B:

Z
--
r1
r2
r3
r4
r5

Using a query I want a result like

m1  r4
m1  r5
m2  r2
m2  r3

The data required from join of two sample tables so that data corresponding to column Y which is not in table B. I have already developed a solution using cursors and inserting data in the table but that's is causing performance issues like 10 hours for 125122 distinct rows of column X in table A. I want a query where insertion operations are not involved so that we can save some disk I/O operations and optimize the performance.

It is not nice - sort of anti-join

http://sqlfiddle.com/#!9/cb0124/3

SELECT a1.x, b.z
FROM (
        (SELECT DISTINCT x FROM a) a1,
        b
     ) LEFT JOIN a a2 ON (b.z = a2.y AND a2.x=a1.x)
WHERE a2.x IS NULL;


Postgres numeric type overflow after 1023 bisection of 1 i am testing the numeric type in postgres according the precision and scale i declared the numeric type with (1000,900) which gives me 900 digits decimal points but when i test it by inserting and halving 1 it stops after the 1023 halving and throws the error: ERROR:  value out of range: overflow
SQL state: 22003.

I use this statement to insert:

 insert into numeric_test
  select   ((1 / power(2, i))::numeric)+10000000, pg_column_size(( 1/power(2, i)::numeric)+10000000)
from generate_series(1, 1024) as i;

And that is my table:

create table numeric_test (
  pos numeric(1000,900),
  sz integer
);

alter table numeric_test add column index_x integer;

Is there a limit how big and numeric type entry could get? The size of the 102 3iteration is 127 bytes and the 1024 iteration throws an error of overflow.

The numeric(1000,900) column has nothing to do with it; the select alone produces the overflow error.

Calling power() with integer arguments will default to the floating-point version of the function. power(2,1024) is causing the overflow, as the maximum base-2 double-precision exponent is 1023.

There is also a version of power() which takes numeric arguments, and will handle 21024 without overflowing. If you cast your inputs to numeric, it will call this version instead:

select ((1 / power(2, i::numeric)))+10000000, pg_column_size(( 1/power(2, i::numeric))+10000000)
from generate_series(1, 1024) as i;


Is 'internallength' always useful in custom type definition in Postgres? In our database we use a custom type to store 3D locations:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

In many examples I see INTERNALLENGTH used as a parameter with the definition of custom types. Also, the documentation tells me that "[t]he default assumption is that it is variable-length", if this parameter is omitted. Is this also true for types like the one above where the size can be calculated (3*4 bytes + 4byte padding)? So should I add 16 as internal length to the definition?

Also, are there other parameters that could improve the performance of handling many (~10,000,000) entries using this type?

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc.

What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the manual once more.

To optimize disk space and performance I would rather avoid using composite types due to big row overhead. Details depend on the details of your requirements.


PQgetvalue alternative in SQL code? In in the PostgreSQL source code there is a function called PQgetvalue that returns a valid text representation of the value that can be used as a constant expression in SQL code.

It essentially creates a literal for any value supported by PostgreSQL.

Is there any such function that is accessible from within the PostgreSQL DBMS itself?

This is an example of what I want:

select PQgetvalue(1); -- 1

select PQgetvalue('cats' || ' dogs'); -- 'cats dogs'

select PQgetvalue('1990-01-01'); -- '1990-01-01'

PQgetvalue() is a client-side function that does not create anything, it just returns the value of a field inside a resultset.

The server-side equivalent, say select SomethingLikePQgetvalue('cats' || ' dogs')
would simply be select 'cats' || ' dogs'. Or if it isn't, please give an example showing when it would differ.

If the goal is to reinject the value as a literal into another SQL statement, then the SQL function quote_literal can be used.


invalid integer value "CONNECTION" for connection option "port" Previous connection kept I'm new to postgres and I'm trying to configure table level logical replication(PUBLICATION/SUBSCRIPTION) on postgres-12.
I have updated postgresql.conf file and updated wal_level=replica. Also, then created 2 databases(test1 and test2). Here is what , I'm doing:
test1=# create table t1(a int primary key, b int);
CREATE TABLE
test1=# insert into t1 values(1, 1);
INSERT 0 1
test1=# create publication my_pub for table t1;
CREATE PUBLICATION

test2=# CREATE TABLE t1(a int primary key, b int);
CREATE TABLE
test2=# CREATE SUBSCRIPTION my_sub CONNECTION 'host=localhost port=5432 dbname=test2 user=postgres
password=password' PUBLICATION my_pub;
invalid integer value "CONNECTION" for connection option "port"
Previous connection kept

When creating subscription I'm getting this message: invalid integer value "CONNECTION" for connection option "port"
Previous connection kept and I'm unable to add this table into replication. In my postgresql.conf file as well I have mentioned 5432 port only. However, I'm unable to understand why it's saying invalid integer value

It seems to me that what you really must have done is something like:
\c CREATE SUBSCRIPTION my_sub CONNECTION ...

The arguments to \c are positional, and the 4th position belongs to the port.
I see no other way your message could include 'Previous connection kept', which is exclusively generated by attempts to use \c or \connect


Сopy rows from one table to another and replace duplicates in the process I have a SSIS job that loads daily data from CSV files into a raw staging table in a SQL Server database (technically is an Azure SQL database). Then after that, a stored procedure is executed to copy all rows from the raw table into a production table, whilst performing the necessary castings, handling of dates, etc. Here is a simplified example of the stored procedure code:

USE [example_database]
GO

/****** Object:  StoredProcedure [dbo].[Append_Data]    Script Date: 19/07/2016 11:21:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Append_Data] as
--Code to delete updated records

--Code to perform the append of incremental records
insert into dbo.[Admissions]
SELECT [refno]
      ,[name]
      ,[value]
      ,cast([modified_date] as datetime)
  FROM [RAW].[Admissions]

GO

I need some help to amend this code so it can handle duplicates as follows: Based on the column that uniquely identifies each row (named refno -- a candidate key), I need to replace any existing records with the same refno in the production key, as long as the modified date column (named date_modified -- a datetime column) is bigger (newer) than what is already in the production table.

Here is an example:

PRODUCTION TABLE

refno, name, value, date_modified
1, Alex, 2112.34, 08/Jul/2016 15:37:00
2, Geddy, 674.91, 08/Jul/2016 15:37:00
3, Neil, 914.73, 08/Jul/2016 15:37:00

RAW TABLE

refno, name, value, date_modified
1, Alex, 1001.93, 10/Jul/2016 12:27:00
2, Geddy, 674.91, 08/Jul/2016 15:37:00
3, Neil, 425.85, 01/Jul/2016 05:13:00
4, Hugh, 9807, 01/Jan/2016 13:42:00

The first row should get replaced in the production table because a row with the same refno exists in the raw table, and it has a newer modified date.
The second row has not been modified, so we leave it as is. Even though the matching row does exist in the target production table, we can see that the date_modified is the same so we ignore it.
The third row has been modified, but the modified date is earlier than what we have in the production table, so we ignore it.
The fourth row in the staging table is new (i.e. a row with that refno does not exist in the production table yet) so we copy it over.

Does that make sense? We don't tend to get many rows per day (about 1000 tops) and it is a straightforward, denormalised table (hence the stored procedure rather than going nuts in SSIS :) ).

Any help is greatly appreciated.

merge production as target
using raw as source
on target.refno = source.refno
when matched and souce.date > target.date
     then update set target.date = souce.date, target.value= souce.value
when not matched
     then insert ...

merge


How do I use the POSIX character classes upper and lower to search text in MySQL? I would like to search a body of text to find out when a specific word has been upper cased. MySQL has a REGEXP function with the POSIX character class [[:upper:]], but for the life of me I can't get it to return any results.

Say I want to search a column for the string literal 'FREE' only in upper case.

Let's say the table structure is something like:

CREATE TABLE article_body (
id INT NOT NULL AUTO_INCREMENT,
article_id INT NOT NULL,
summary VARCHAR(255),
body_text TEXT,
PRIMARY KEY(id));

I want to find body_text columns where the word FREE is in all uppercase, e.g.

SELECT id, summary FROM article_body WHERE body_text REGEXP '[[:upper:]]+FREE';

But of course that syntax is invalid.

Help?

From the article on mysql REGEXP:

  REGEXP is not case sensitive, except when used with binary strings.

A simple solution would be to use the BINARY operator:

SELECT * FROM article_body WHERE body_text REGEXP BINARY "FREE";

As to why your example doesn't work, the way I interpret it is "Any string with one or more uppercase letter followed by FREE", but of course FREE is case-insensitive there.


Partial db dump/restore A part of my database (PostgreSQL 9.3) relies on extra tables (ex. County, City, Town, ... ). I don't manage these tables, they are updated regularly by a third party. Each time I get a new full dump, but I have a hard time to push the changes back into my DB.

I've played with pg_dump / restore and run into some constraint issues duplicate key value violates unique constraint or cannot drop constraint ... because other objects depend on it even with --disable-triggers or --clean option.

Is there an option I've missed? I've found there are some ways to turn constraint on/off but I've no idea if it's the good way to solve this or just a dirty hack? (I'm not a DBA expert.) To be honest, I'm quite surprised there isn't an easy way to achieve this. Maybe I've missed it! I naively thought I could run pg_restore as a big transaction and check constraints at the end of the script. Is it possible?

How about trying to solve this problem with Point-In-Time-Recovery? A dump has the problem that you got an old set of data anyway. I highly suggest to use a backup method giving you ways more recent version of the data.

You can also write yourself a simple change-log trigger storing all the information which has changed in a simple table. Here is how it works: http://www.cybertec.at/tracking-changes-in-postgresql/
The code in the site should give you a rough prototype and help you to fix things.


How to add time from a Date value to another without a varchar conversion? Let's say I have 2 variables in a Stored Procedure, both are Dates

Foo contains '13/10/2019 00:00:00'

Bar contains '01/10/1914 16:33:11'

I want to add Bar's time to Foo's date, so that I get a Foobar variable containing 13/10/2019 16:33:11

Is there a way to achieve this without converting any of them into a varchar?

Simple addition and subtraction.

Although the code is SQL code, the math works the same in PL/SQL.

This code:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
with data as (
    select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo
         , to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual
)
select foo, foo + (bar - trunc(bar)) foo_modified, bar from data

Produces this desired results:

FOO                 FOO_MODIFIED        BAR
------------------- ------------------- -------------------
2019-10-13 00:00:00 2019-10-13 16:33:11 1914-10-01 16:33:11


Why are mysql password hashes internally saved with a star (asterisk)? I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get

mysql> select * from mysql.user limit 1 \G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

The password is obviously hashed, but why does it begin with the star (asterisk)?

Ok, found about this in the documentation itself.

This was a change introduced in mysql 4.1 so that the earlier password lengths of 16 characters and newer password lengths of 40 characters could be simultaneously supported. The Password column was made 41 bytes (chars) long, and the newer passwords would begin with a mandatory * to identify them.

From the documentation:

  Password hashes in the 4.1 format always begin with a “*” character, whereas passwords in the pre-4.1 format never do.