texts:
I have this table

CREATE TABLE IF NOT EXISTS projects_t
(
  name     TEXT NOT NULL,
  metadata JSONB,
  content  BYTEA,
  version  TIMESTAMP WITH TIME ZONE DEFAULT now(),
  PRIMARY KEY (name, version)
);

The app accessing it doesn't know about version, only name metatdata and content

So I created a view to make it transparent + trigger for insert/update/delete

CREATE OR REPLACE VIEW projects AS
  SELECT DISTINCT ON (name)
    name,
    metadata,
    "content"
  FROM projects_t
  ORDER BY name, version DESC;

CREATE OR REPLACE FUNCTION f_insert_projects()
  RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO projects_t (name, metadata, content) VALUES (NEW.name, NEW.metadata, NEW.content);
END;
$$;

CREATE OR REPLACE FUNCTION f_update_projects()
  RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO projects_t (name, metadata, content) VALUES (NEW.name, NEW.metadata, NEW.content);
END;
$$;

CREATE OR REPLACE FUNCTION f_delete_projects()
  RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
  DELETE FROM projects_t
  WHERE name = OLD.name;
END;
$$;

CREATE TRIGGER insert_project_via_view_trig
INSTEAD OF INSERT ON projects
FOR EACH ROW EXECUTE PROCEDURE f_insert_projects();

CREATE TRIGGER update_project_via_view_trig
INSTEAD OF UPDATE ON projects
FOR EACH ROW EXECUTE PROCEDURE f_update_projects();

CREATE TRIGGER delete_project_via_view_trig
INSTEAD OF DELETE ON projects
FOR EACH ROW EXECUTE PROCEDURE f_delete_projects();

I cannot modify the SQL query ran by the app (it wouldn't be funny !). The app does an upsert :

INSERT INTO projects VALUES ('test', ('{ "last_modified_time": "' || (now() at time zone 'utc')::text || '", "last_modified_user": "' || current_user || '" }')::jsonb) ON CONFLICT (name) DO UPDATE SET content = EXCLUDED.content, metadata = EXCLUDED.metadata;

And it throw me an error 42P10, "There is no unique contraint nor exclusion constraint corresponding to the specification ON CONFLICT". Is there a way to make it work ?


texts: When executing a command via xp_cmdshell what user issues the command to the system?

texts:
I'm executing the following command to stop a service on a remote server from within a stored procedure in SQL Server.

----------------
-- Stop Service
----------------
SET @Command = 'sc \\' + @Server + ' stop ' + @ServiceName;
EXEC master.dbo.xp_cmdshell @Command;

Depending on the service sometimes this executes successfully but other times I receive the following error:

[SC] OpenService FAILED 5:
NULL
Access is denied.
NULL
NULL

Im curious what user executes the command.  Is it the user I login/connect with to the database?  Or is it the user Im logged onto the physical server with?  Can I pass in different authentication with the xp_cmdshell?


texts: How to export views and import it in other DB?

texts:
I am trying to export the database and everything is fine but I am not able to export the VIEWS.

Is it possible to export the views from sql queries and import it?


texts: Altering Tables using Tablenames from other Table

texts:
I have a database with several tables and i have a table which contains all tablenames which I want to alter.

I want to perform a query like this:

ALTER TABLE (SELECT "TABLE_NAME" FROM "RESOURCE_TABLES)  DROP COLUMN ID;

My Table RESOURCE_TABLE with Table Names:

TABLE_NAME     ID

Table1          1
Table2          2
Table3          3
  ...          ...

How can I alter all tables named in the table in the column TABLE_NAME with a loop or a query?

So i want Table1,Table2,Table3 to drop a column id without executing a single alter table query on each table because maybe the tables are 100 Tables.


texts:
Not a DBA so pardon my ignorance...

I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows.
When retrieving the data some of the foreign keys are used and some are not. Also, I would like to add indexes to two more fields that are used frequently while querying.

My questions are -

1) In the following code should we just index the field "criteria" or should col1, col2 be created as a non-clustered index?

SELECT col1, col2
FROM table1
WHERE criteria

My understanding - If only the field "criteria" was indexed then the query would use the index to resolve which rows to retrieve. Post that it would do a full table scan to retrieve the relevant rows

2) Can I remove the indexes on the foreign keys and still have the foreign key constraints work?

3) 10 indexes on a table with 20 fields. Would that cause a performance issue during inserts?

4) The most optimal storing scheme for the indexes - BTREE, RTREE and HASH. I assumed BTREE for queries that have > or < and hash for the = queries. Is that the right way to go?

Any suggestions would be greatly appreciated.


texts: Should I create multiple tables for different entity states, statuses or stages?

texts:
I have a tasks table in my database and, in the business domain of interest, tasks can have multiple states: “open”, “begun”, “in review” and “completed”.
Despite having “open” and “begun” in the same table, identified using flags, I decided for some reason early on to have “in review” and “completed”  tasks in their own separate tables. While this seemed like a good idea to start off with, making it easy to query for specific things, when I thought about implementing a comment feature for tasks I realised that the task_id value would be changing across the three tables.
Have I gone about this all wrong?
Current considerations
Some solutions I thought up would be to redo the organisation and put everything in one table, using flags to differentiate purely between the states (this would lead to a fair amount of reworking); to create some kind of UUID that is transferable across the tables - though I think there might issues with performance if this is done; and finally, to set the IDs across the tables to be no-longer auto incremented and to simply “inherit” the original value from the first task table (which auto increments).
What's the done thing in this situation? I'm not the most versed in database design and I'm kind of making this up as I go along.
Any help would be greatly appreciated.
Responses to further information requests made by @MDCCL via comments

So, the state (or status) of a task can change over time, right? How many times can the same concrete task present the same state, let us say, “open”? Will it vary depending on the kind of state presented by the relevant task?

Yes, a task.state could theoretically change up to four times. I won't go into specifics of the logic but the longest chain would be “begun” → “completed” → “review” → “completed”. And each time, currently, they're being put into another table.
The exception is whether it's “begun” or not, a user may abandon and then re-undertake the task infinitely - in theory at least. Which is at least helped by the fact that the “begun” status is a flag.

Ok, but a certain task will present, at all times, exactly one particular “present” state, right? For example, when a task row is INSERTed, it should be accompannied by a certain state value, which I assume would be “open”. Later, the state of such a task will evolve to only one of the other possible options, that is, either “begun” or “in review” or “completed”.

Yea, that's exactly right. A task will always be in a stage, and can't be in multiple ones.

Once a particular task is deemed as “completed”, can it change to another state?

Yes, it can be sent back to “review” if certain parameters are reached. And from “review” it can either go back to “begun” or back to “completed”.


texts: DBF file compatibility between Oracle 12.2.0.1 and 11.2.0.4?

texts:
We have a plan to make a copy of our PRD environment into our QA environment to get update data for testing by means of transporting the PRD DBF files over into our QA system.

However there is a discrepancy between the Oracle versions on both systems, on our PRD the Oracle database is 11.2.0.4 whereas in our QA environment Oracle database is newer at 12.2.0.1.

Would the QA environment having a more recent number be able to read DBF data files generated from an older version?

We will be moving all the control files along with the data files too.

Any advice from someone who've pulled such a stun before much appreciated :)


texts: What DML commands can be allowed / limited on a reporting server?

texts:
I have a largish database (PostgreSQL 9.1) which I'm preparing to replicate using streaming replication. The 2nd server will be used for reporting and backup purposes. When building reports, it's often useful to build "temporary" tables. By using "temporary" I mean tables which are considered scratch, but which are not using CREATE TEMPORARY TABLE.

Can I force a user to create new tables only their separate schema? Will these schema changes have any impact on streaming replication? The master server only ever has changes in the public schema.


texts: Grant View Server State - is it possible for a none SA user to have in Azure SQL?

texts:
I'm trying to set up some scheduled jobs which will collect some server stats, using system DMV's in SQL Server Azure.
If a none SA users uses 'sys.dm_os_schedulers', they get an error (below), query works fine for server admin.

VIEW SERVER STATE permission was denied on object 'server', database 'master'

The user is a  member of DBManager Role in the MasterDB, and they are a member of the db_owner role in User DB, and have added VIEW DATABASE STATE in user db.

I've attempted to 'GRANT VIEW SERVER STATE to [user]' in Master DB, using the server admin account (set up via portal on initial database creation). However, this errors :-

Grantor does not have GRANT permission.

Is it possible to grant this? (which is what every post I've seen says), or how do I do it with the Server Admin account?


texts:
I've inherited a SQL Server Web Edition RDS instance from another firm whereby they took a snapshot and then shared it with our firm's RDS account.

After reinstating the instance under our account, I wanted to set up native backups but ran in to permissions issues when running the msdb.dbo.rds_backup_database command:

The EXECUTE permission was denied on the object 'rds_backup_database', database 'msdb', schema 'dbo'.

Believing this was due to permissions issues on the account that was given to me, I reset the used the "New master password" functionality within the RDS console to reset the master password. After I was notified that succeeded, I attempted to login but got a login failed error.

In looking at the results from the following query, select * from master.sys.server_principals I can see that the master login is disabled.

When running the following query,

select suser_sname(owner_sid) as 'Owner', state_desc, *
from sys.databases

I can see that all the databases were created with the master account.

How would I enable the master user's login in this case or does anyone know of a workaround?

Thanks,

-Ben


texts: I have A mongo replica with 6 nodes, not sure why but 4 of them went down and two entered to recovery state. each time I tried to start the service of one of the down nodes it fails with error there is no member to sync from. and the 2 recovering nodes stuck in recovery can you please suggest what can be done? thanks


texts: Sub-query executes 1 time for a parent query or more?

texts:
If i apply a static data in where clause is it faster then applying a sub-query. Example:

(The query returns 5 records as a result set)

SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = '2012-08-21';

is faster then :

SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = (SELECT STARTDATE FROM TEST2 LIMIT 1);

or they are same in any case?

Sub-query executes each time a comparison perform by query with each record or only for 1 time?


texts: OLE DB error 0x80004005 using Copy Database Wizard

texts:
Background: freshly installed SQL Server 2012 SP3 on Windows Server 2012R2.

Problem: When I use the Copy Database Wizard to copy a database from server2 to server2 I receive:

  While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).

Yes, I use the wizard to make a database copy on the same server. (Accounting software and testing purpose.)  I receive this error when the wizard tries to create the Integration Services package.  Also what's important, it happens when I use SMSS from my computer. I'm a sysadmin on this SQL Server. I even added my user to this Windows Server's admin group.

We've got an old server1, which is configured in very similar way as server2. I checked every setting that came to my mind and both configs looks the same. No proxies, no users in SSIS Package Execution.

I can copy a database from server1 to server1 and from server2 to server1 without any problem.

It looks like I don't have sufficient right to location: server2_server2\DTS Packages\Copy Database Wizard Packages whatever it is.

Theoretically I could use RDP to server and domain admin account every time but that's pain in the... back. Also there's accounting software support that has high SQL rights but can't have domain admin.

I googled this and usually it's about SQL Server 2008 and running some custom packages.


texts: Would it be a good idea to apply indexing on temp tables when it is suggested to do so in the estimated execution plan?


texts:
I have the following table:

ID     Name    Items
-------------------------------------
1      John    7
2      Peter   533
3      Chang   13
4      Mike    9100

I want to order it by Items. I used ORDER BY items ASC, but it returns:

ID     Name    Items
-------------------------------------
3      Chang   13
2      Peter   533
1      John    7
4      Mike    9100

I want to return:

ID     Name    Items
-------------------------------------
1      John    7
3      Chang   13
2      Peter   533
4      Mike    9100

I think this might be a silly question, but I really don't have any ideas about how to solve this.

Thanks.


texts: How to most efficiently store possible combinations of categories?

texts:
I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of the ~10^10 combinations are likely to be ever searched for and I can constraint it even more). However, I'm not sure how to implement it.

Combination of 14 columns for each row in the category table looks simple, but it doesn't feel right. Any change in the category data would make a change in the combination table structure necessary.

Another option was to make the combination only with ID and combination name and store the important information in the category_combination join table. This feels "right", but making sure that each combination is unique becomes a non-trivial task (unlike simple applying unique constraint over a group of columns in the former case). In PostgreSQL, I would make a deferred trigger, but this is not possible in MySQL. The best option I can think of with MySQL is to make it a function and then calling it from the client app; if it finds a dupe, I'd cancel the transaction. I would aggregate the categories in the combination with GROUP_CONCAT (ordered, of course), either in the query or in an auxiliary view, and then compare the arrays.

Still, I'm not sure if there's no more elegant way, either a completely different approach, or an optimization of the second approach. How to do it best?

EDIT: category combinations will be often used in search, so it needs to be optimized for searching. Adding new a combination will be rare and I hope we will avoid adding new categories, though this should still be possible (even with a lot of work).


texts: PostgreSQL How do I convert struct text to plain *char in C functions?

texts:
I'm using my internal C function which doesn't know about postgresql's text struct, how do I pass text argument when char * expected?

#include <stdio.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(fun);

Datum fun(PG_FUNCTION_ARGS)
{
    text    *str1, *str2;

    str1 = PG_GETARG_TEXT_P(0);
    str2 = PG_GETARG_TEXT_P(1);

    FILE *fp = fopen("/tmp/fun.log", "w");
    fprintf(fp, "%s\n", VARDATA_ANY(str1));
    fprintf(fp, "%s\n", VARDATA_ANY(str2));
    fclose(fp);

    PG_RETURN_INT32(0);
}

Here's what xxd sees in log file:

0000000: 666f 6f74 6261 6c6c c88d 7409 70d3 6b09  football..t.p.k.
0000010: 080a 7a6f 6f6d 0a                        ..zoom.

After executing:

SELECT * FROM fun('football','zoom');

Why junk after football?


texts:
I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type being PAGELATCH_EX).

The issue appears to happen sporadically when the server is under decent load. I've been monitoring "Temp Tables for Destruction" rate and it can jump to 5,000+ during times when we have issues of PAGELATCH_EX on 2:1:103. From what I've read this counter should be 0 the majority of the time, but ours seems to stay anywhere from 300-1100 the majority of the time. The counter only goes to 0 when there is very few users on the system.

How can I narrow down what is causing the DDL contention on tempdb without having to look for a needle in a hay stack?


texts: Transactional versus Dimensional model for file upload web application -- preferred approach?

texts:
For a web application whose sole purpose is to allow users to upload raw file data at any time (to database tables), does a transactional or dimensional model make more sense for such an application?
Some reporting is done off the data at this time but it is very minimal.  If that changes in the future (such that reporting becomes more pervasive), does that change the model you suggest?
Total space for the database 100GB.


texts: Migrating database live while being severely handicapped

texts:
I "inherited" a web application which is designed and implemented horribly (both the application and the database). For example, the main data is stored using a sort of emulated key-value storage in a Postgres 8.2 database, making it virtually impossible to extract useful data from it in a reasonable amount of time.

Currently I'm working hard on replacing the entire application + database, however it will take a few months before the new application is finished. This is a problem since the website is really slow due to the extremely bad database design and even worse queries. Therefore I'm planning to fix the database design and the queries on the live site until the website has an acceptable load time as a temporary solution.

I do however have a few limitations to work around, the most problematic ones are:

I'll have to do everything from inside a proprietary build CMS.
The queries are all distributed over many many files, and there's no way to search for them. Therefore it's next to impossible to guarantee that I've updated all queries to a particular table.
I don't have direct access to the database, but I can execute queries using a query editor in the CMS, but only to tables owned by the application (so no pg_* tables for me).
I don't have access to a developer environment, nor can I create one. Everything has to be done live.

So basically I'll have to do a migrate a database live over the course of a few days, while simultaneously updating the application live such that it can handle the new application, without being able to search for the usage of each table.

With all these handicaps taken into consideration I came up with the following plan:

Create initially empty tables to store the data in a sane way.
Create triggers on the old tables which can sync the data with the new ones.
Export the data from the old tables to the new tables & enable the triggers.
Replace the queries one by one.

Using this strategy I will have 2 synced databases after step 3. Initially all queries will go to the old database, but while I'm updating the queries slowly the old database will be used less, and the new one more.

Taking this "sub-optimal" situation in mind, is this a good strategy to fix some of the problems? What things should I take into consideration while doing this?

Note that I fully understand that this is a very risky suicide mission. However, I'll have to do something in a short amount of time, otherwise the website becomes entirely unusable.


texts: What is Oracle's Window Buffer Operator?

texts:
Oracle database's explain plan sometimes includes Window (Buffer).  What does this operator do?

Here is the SQL.  The primary keys are RECORD_DATE, TYPE and KEY.
select
   source.RECORD_DATE,
   source.TYPE,
   source.KEY,
   source.AD,
   source.VALUE,
   source.STATE,
   source.SUB_STATE_KEY,
   source.AMOUNT
from
   COMPUTE_DETAIL_FACT source
where
   source.RECORD_DATE >= date '2018-12-09' and
   source.RECORD_DATE <  date '2019-02-25' and
   source.TYPE        =  'banana' and
   source.KEY         =  316598 and
   not exists
   (
      select
         0
      from
      (
         select
            lead(test.RECORD_DATE) over (order by test.RECORD_DATE) RECORD_DATE
         from
            COMPUTE_DETAIL_FACT test
         where
            test.RECORD_DATE    >= date '2018-12-09' and
            test.RECORD_DATE    <  date '2019-02-25' and
            test.TYPE           =  source.TYPE and
            test.KEY            =  source.KEY and
            (test.AD            =  source.AD            or (test.AD            is null and source.AD            is null)) and
            (test.VALUE         =  source.VALUE         or (test.VALUE         is null and source.VALUE         is null)) and
            (test.STATE         =  source.STATE         or (test.STATE         is null and source.STATE         is null)) and
            (test.SUB_STATE_KEY =  source.SUB_STATE_KEY or (test.SUB_STATE_KEY is null and source.SUB_STATE_KEY is null)) and
            (test.AMOUNT        =  source.AMOUNT        or (test.AMOUNT        is null and source.AMOUNT        is null))
      ) prev
      where
         source.RECORD_DATE = prev.RECORD_DATE
   )


texts: Are C++ vectors in a user-defined aggregate function a POD for Postgres?

texts:
The official postgres documentation (docs) states in section "37.10.11. Using C++ for Extensibility" that

If calling backend functions from C++ code, be sure that the C++ call stack contains only plain old data structures (POD). This is necessary because backend errors generate a distant longjmp() that does not properly unroll a C++ call stack with non-POD objects

So, are std::vectors< uint64_t > considered POD or not? I wish I could tell from reading the above instructions but I cannot. Can someone help? Thanks.


texts: Set value of character_set_client to utf8mb4

texts:
I'm trying to convert my DB to utf8mb4 following this guide. I have set:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
skip-character-set-client-handshake

But the value of character_set_client and character_set_results still won't change to utf8mb4.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

What am I doing wrong? How do I get those values set to utf8mb4?


texts: With SQL Server 2008, how can I roll back a transaction done some time before?

texts:
I am coding up a script to merge some accounts (and other tables) as an out-of-band customer request. All the updates will be done in a single transaction. They are all updates (no inserts or deletes) - effectively changing the "account_id" of some entities to achieve the account merge.

That's all fine, but my boss wants to be able to roll back the affected accounts after other transactions, i.e. DAYS down the road on a very active server.

My ideas are: -

print out all the affected rows (select all the previous data to console) so I could manually move everything back if required (there aren't too many affected tables - about 8)
export the transaction and "somehow" roll that individual transaction back - without affecting any subsequent transactions.
something else?

Many thanks!


texts: Post replication powershell script - on subscriber

texts:
I need to execute a command before a replication executes on the publisher, and after, on the subscriber.

I seem to have it working on the publisher, by adding another step in the replication agent.

But i have yet to discover how i can execute another step on the subscriber.
I't has to be possible to somehow trigger a job on the subscriber, after a replication has succeded.

Do anyone know how this can be done?


texts: How to schedule emails out with SQL Server

texts:
I am creating an easy "service desk" web application with SQL Server as database.

I have a table where the web application is inserting data which is used to send emails for alerting the users about tickets opened, closed, etc. This part is working perfectly fine using sp_send_dbmail and a trigger on insert.
However, there are cases where the email is scheduled to be sent later on (for example preventive maintenance).

I know I can create schedule jobs for this however I would like to know if there is any more effective method rather than creating a scheduled job everytime.


texts: SQL Percentage of users falling in a specific range

texts:
I have a list of users with corresponding date of birth.
Is there any possibility I can get a percentage of users born between 1990 and 2000 in a single SQL query?

The query I tried is as follows:

Select (Select count(distinct user) as users from Visitors
  where birthyear between 1990 and 2000)/count(distinct user) from Visitors

But it gives me a zero as result.


texts: Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?

texts:
I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1.

#TempTable1 has a BIT field called ToInclude which is set in a subsequent UPDATE query and uses a CASE statement based on other fields in the table to set the value appropriately.

The UPDATE #TempTable1 SET ToInclude = CASE... query takes a full minute to execute. But instead of doing an UPDATE, if I SELECT *, CASE...END AS ToInclude INTO #TempTable2 that only takes about five seconds, even though it's the same exact CASE statement I'm using in either query.

Here are the TIME and IO statistics for each instance.

UPDATE Query (#TempTable1):

SELECT *, CASE...END AS ToInclude INTO #TempTable2 Query:

I see in the first case (for the UPDATE query) SQL uses the Worktable. I've never heard of this concept before, but more so I'm interested in why it goes this route. It looks like in the first case, it takes an extra 15 million logical reads too, not sure why.

Also, I've tested this multiple times, switching the order of which query I run first, and dropping #TempTable1 between each test, to ensure I'm not experiencing a difference in memory caching. These results continue to be repeatable.


texts: Started backup via RMAN, any way to nohup it?

texts: Backup script was failing, so we started new backup manually through RMAN, is there any way to force RMAN to work after i terminate my ssh session? Ctrl+Z in rman unfortunately does not work.


texts: Can a session variable be set to the contents of a function parameter using SET SESSION?

texts:
I'd like to set a session variable inside of a function, using the values passed into the function.

Example:

CREATE FUNCTION my_function(my_param INT)
RETURNS VOID AS $$
BEGIN
    SET SESSION "myapp.myvar" = my_param;
END
$$
LANGUAGE plpgsql;

SELECT * FROM my_function(100);
SELECT current_setting('myapp.myvar');

What I get:

my_param

What I want:

100


texts: SQL Server: mirroring several tables in different databases

texts:
I want to keep several tables in two different databases mirrored. Both databases are within the same server, and both are SQL Server dbs. This mirroring only affects the data they contain, not the structure (although would be great if any table alteration was reflected as well). The data volume in those tables is low and only changes from time to time. There will be other tables in both databases that should't be mirrored.
The following list contains all the options I think I have:

Use triggers. Perhaps the best option, but as I want to mirror lots of tables this might imply more work.
Implemente SQL Server replication. I did this in the past on another pair of dbs and was really difficult to set up and wasn't working as desired, in terms of performance. This time the conditions are completely different, so it might work.

Does anybody know if there are other options? Which one do you prefer and why?

Thanks!!


texts: Mongo db sharded "the collection's metadata lock is taken" issue coming very frequently

texts:
I am seeing this warning in mongoS log consistently for one collection.

[conn96] warning: splitChunk failed - cmd: { splitChunk: "ibeat20150105.dgrpCount", keyPattern: { articleId: 1, host: 1 }, min: { articleId: MinKey, host: MinKey }, max: { articleId: MaxKey, host: MaxKey }, from: "shard0000", splitKeys: [ { articleId: "01a12144225c646875aeea79990432", host: "xyz.com" } ], shardId: "ibeat20150105.dgrpCount-articleId_MinKeyhost_MinKey", configdb: "x.x.x.x:27017,x.x.x.x:27017,x.x.x.x:27017" } result: { who: { _id: "ibeat20150105.dgrpCount", state: 1, who: "ibeatdb61:27017:1420207410:680478249:conn105:432206596", ts: ObjectId('54aa4f1c253720e5ba254fde'), process: "ibeatdb61:27017:1420207410:680478249", when: new Date(1420447516889), why: "split-{ articleId: MinKey, host: MinKey }" }, ok: 0.0, errmsg: "the collection's metadata lock is taken" }

This is coming continuously for one collection.
Also when i checked the data distribution this whole data is on one shard only for this particular collection.
I check lock with id: balancer it seems to be working fine. as i did not get a time for which it has locked indefinitely.
I have 5 mongos servers running in my application

This is creating load on my primary shard which is impacting write operations.
Could you please help me.


texts: Get Single greatest date for each record

texts:
I have a list of Devices with a gps data table. I would like to grab the last gps coordinates received for each device in the query.

SELECT g.[TimeStamp], d.DeviceId, p.IsHistorical, g.Latitude, g.Longitude, g.Speed, g.Bearing, g.Fix, g.SatelliteCount, p.MessageTypeId
FROM Obd.Device d
LEFT JOIN obd.Packet p ON p.DeviceId = d.Id
LEFT JOIN obd.GPSData g ON g.PacketId = p.Id
WHERE d.ISDId = 1000 AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL)


texts:
I was wondering how I would go about learning the functionality of all the different store procs and functions that come out of the box with sql server. Would I be right in saying that a book on sql server internals teach me all this?

Im pretty new to sql server and I have just finished volumes 1-3 out of 1-5 of Joes To Pros T-SQL querying 2012(Personally the writer is very good at putting together a book that enables you to learn and retain knowledge fast, but that's just my opinion).

Anyway, any help on this would be much appreciated. I love getting stuck into a good book as I think having structured learning with examples works best for me.

Thanks,

John


texts: SQL Server Merge Replication Computed Column Conflict Resolution

texts: I'm setting up a merge replication web publication for a table that needs to use the DateTime (Later Wins) conflict resolver so that the most recently modified row wins in any conflict. This table (and hundreds of other tables) use a datetimeoffset for their modified date as subscribers can be in many timezones. In order to get the required datetime value as a column I've added a ModifiedDateUTC datetime computed column which does a simple CONVERT to a datetime. The issue I'm seeing is that even though the article setup accepts this as a valid column, whenever I generate a conflict I get the error 'The specified conflict resolution column 'ModifiedDateUTC' could not be found.'


texts: How to change the Write Ahead Log directory

texts:
How do you change the defined WAL directory?
During the installation I defined a directory, /var/lib/wal and the $PGDATA/pg_xlog has a symbolic link pointing to that directory.

How do I change the postgres setting to write the WAL files elsewhere - without having to re-install?


texts: How can Cumulative Updates to SQL Server 2019 be Slipstreamed into a Single Installer?

texts:
I'd like to take the latest version of SQL Server (SQL Standard, 15.0.2000.5), and apply the latest CU to it so that I have a single installer, thus reducing the size of the resulting package. The custom installer that I'm developing packages the SQL Server installer, and thus reducing the size by several hundred MB is important.

In previous versions of SQL Server, it was possible to create a single merged media output folder using the /PCUSOURCE and /CUSOURCE parameters, however this seems to have been removed in SQL Server 2014 (See "Slipstream Functionality" here). The alternative provided is to use the /UpdateSource flag, however this approach only allows for the simultaneous application of patches at the time of installation (by bundling the CU media with the original installation media). Further, the /ACTION=PrepareImage facilitates a SysPrep installation, not a slipstreamed update to the install media.

So, is there a way to update the installation media of SQL Server Standard 2019 with CU patches such that there's a single (reduced-size) installation media bundle?


texts: Non-existent table that actually exists?

texts:
I've been tasked to solve a very difficult and interesting problem.
Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10.
We have an application that uses this database and which needs tables that have very specific naming, this can not be changed.
Our table in question is named something like vendor_catalog_scope1, so there are no weird characters in there or any naming conflicts.
The problem:

when trying to list the tables with SHOW TABLES, it's not listed
when attempting to execute CREATE TABLE, an error is returned: SQL Error (1050): Table 'vendor_db.vendor_catalog_scope1' already exists
when attempting to DROP TABLE: SQL Error (1051): Unknown table 'vendor_db.vendor_catalog_scope1'

So it doesn't exist, but it can not be created because it already exists, but also it can not be dropped because it doesn't exist #rolling-eyes-weird-mysql-logic.
I've started investigating by searching the tables in information_schema database to see if and what I find there about it.
I've actually found it in information_schema mentioned in these tables:

INNODB_SYS_TABLES
INNODB_SYS_TABLESPACES

but weirdly enough it's missing from INFORMATION_SCHEMA.TABLES

I don't know what happened or why, I just need to solve this somehow so that we can do a proper/correct CREATE TABLE (...) and use it in our application.
I'm guessing starting to delete rows from the information_schema tables is a very bad idea and more likely to break stuff rather than fix them.
Keep in mind that, being an Amazon-Aurora service, there's no way to SSH into it and poke around the actual MySQL files and change/rename/delete them.
Is there any way we can solve this without dropping + re-creating the entire database?


texts: How to check which database roles can see `masked` columns?

texts:
In SQL Server 2016 SP1 standard edition we can use dynamic-data-masking. The masking of data is controlled using the built-in security, for example:

REVOKE UNMASK TO user_who_cannot_see_senstive_data

Of course, this is not working for the users who are members of the db_owner database role.

Is there a list with security roles or cases showing when the REVOKE UNMASK is going to work?

I have found a database engine permissions but it's too complicated and does not seem to show when data cannot be masked from a user.


texts: After delete audit table doesn't have the rows that were deleted

texts:
CREATE TRIGGER trgEmp ON Emp
AFTER DELETE

AS
BEGIN

IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO EmpAudit
        (
            Change,
            Date,
            empID,
            fName,
            lName,
            ssn,
            address,
            state,
            zip,
            serialNoJob,
            dob,
            classification,
            pay
        )
 SELECT 'DELETED', GETDATE(), d.empID, d.fName, d.lName, d.ssn, d.address, d.state, d.zip, d.serialNoJob,
        d.dob, d.classification, d.pay
        FROM Emp e inner join deleted d on e.empID = d.empID
    END

END
GO

This trigger also had AFTER UPDATE, INSERT. The updates and inserts work as expected. So I decided to try the trigger with delete by itself. I tested again by deleting from Emp table, I still get
0 rows affected
1 rows affected
and nothing is actually inserted into the EmpAudit Table


texts:
Is it possible for this wait event to activate/showup even when the PGA has not crossed over PGA_AGGREGATE_LIMIT?

I am continuously seeing this wait event in the ASH, but the PGA allocated stays well within the limit set by PGA_AGGREGATE_LIMIT.

I used the below queries to check.

Total PGA that is currently used:

select sum(pga_alloc_mem) from v$process;

Parameter value for PGA_AGGREGATE_LIMIT:

select * from v$parameter where name like '%pga%limit';

For what it is worth, the documentation seems to be scarce for this wait event and the only official source doesn't help either:
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2138882_1.html

EDIT:

Adding bounty.

More info:

PGA_AGGREGATE_LIMIT is set to 8 GB
PGA_AGGREGATE_TARGET is set to 4 GB

I am getting this wait event with the following values in ASH

P1 (Limit) = 8.3 GB (approx)
P2 (Margin) = 3.9 GB (approx)
P3 (Growth) = 0 or some randomly large number

I am guessing P1 is the PGA_AGGREGATE_LIMIT and P2 is some sort of threshold used before the wait event fires, but I am not sure what these actually are.


texts:
Can the following query be optimized ?

DELETE FROM COLOR_TAGS
WHERE COLOR_PROJECTID = $id
AND NoteID IN(
SELECT ID FROM note_note
WHERE BibID=$bid AND isdel=0);


texts: Compare the contents of a single column and sync rows in that colum

texts:
This is SQL Server - I have 3 rows of data in a single table based on location I want the data for a single field to match in all three locations e.g I have parts in three locations, location 1, 2, and 3 but the price for the part is different in each of the three locations, I would like to sync the price for all three locations

This is table 1 - fields include item number, location, price. e.g

item    loc.    price
123     1       0.1
123     2       0.2
123     3       0.3

My desired outcome is that location 2 and 3 will have the same price as location 1.


texts: Trace Flag 3688 not working on SQL Server 2014

texts:
We are using some third party tools that fill up the SQL Server logs with entries like:

SQL Trace ID 1 was started by login "sa".
SQL Trace ID 2 was started by login "XX\xxxxx".
SQL Trace stopped. Trace ID = '2'. Login Name = 'XX\xxxxx'.
SQL Trace ID 2 was started by login "XX\xxxxx".

I am trying to suppress these by enabling TF 3688 but it is not working on SQL Server 2014, 2012 or SQL 2008 R2. Here is a snapshot of the configuration. Please let me know if I am missing anything:

I have restarted the instance and verified that the trace flag is active using DBCC TRACESTATUS:


texts:
I am looking to do regular backups of my SQL Server databases.

Is there any reason why I should not just backup just the MSSQL.10/DATA folder? I've got a great backup solution in place from Rackspace which does incremental change based backups.


texts: Executions/min and CPI ms/sec inside Activity Monitor is too high

texts:
We have executions/min is too high, around 20,000 to 30,000 for a query. Does this means the query is executing 20,000 per minute? I don't think we have that much load on server that a single query runs 20,000 per minute.
Also, I am always confused with CPU ms/sec. Does this means total CPU used per second? If yes how come we have lot of queries with 1000+ CPU ms/sec as we have limited CPU on server.


texts: mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

texts:
I am having some difficulty with my MySQL database.
It appears the server suffered from a power outage during the night. As a result, MySQL wouldn't start when it reboots.
I viewed the error log and saw:

[ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT
between the checkpoint 322393393 and the end 322394369. [ERROR]
InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned er [ERROR] Plugin
'InnoDB' registration as a STORAGE ENGINE failed. [ERROR] Failed
to initialize builtin plugins. [ERROR] Aborting

MySql will only start with innodb_force_recover=6
At this point I am trying to save the data by running from MySQL Workbench:
 mysqldump.exe --defaults-file=####  --user=#### --host=localhost --protocol=tcp --port=59452 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers "BusinessManager"
Workbench shows me this error:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'BusinessManager' AND TABLE_NAME = 'Activities';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
Operation failed with exitcode 2

However, the MySQL log shows:

[ERROR] InnoDB: Failed to find tablespace for table BusinessManager.Activities in the cache. Attempting to load the tablespace with space id 195

The log error is repeated for each table and the SQL dump fails.
Is there any way to save the data in the database and bypass these errors?
Thanks!


texts:
For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB

Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from the memory instead of disk, and will greatly improve QPS for my database?


texts: How to use Join for two tables that have related data but in different column names

texts:
I have to create an sql code to combine all the tables but when im using join, its creating another instance of the same column that's existing in both tables along with the column that is not. How can i only get the column that is not in table 1?

For example
In table 1 I have

FORMID
STATUS
TITLE
DESCRIPTION

In table 2 I have

HSID
FORM ID

when I use join I get the following output:

FORMID - STATUS - TITLE - DESCRIPTION - HSID - FORMID_1

Eg. CODE:

SELECT *
FROM PROJECT_FORM
JOIN ASSIGNMENT
    ON PROJECT_FORM.FORMID=ASSIGNMENT.FORMID


texts: MySQL on Server side PRIMARY KEY taking automatically random Id

texts:
I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values.

inserted id's like

1, 2, 35, 67, 153, 631, 3366, 3678, ..... 35325, 56753

How can i solve this problem?


texts: Can I match two tables in postgresql, sum the data in the number column and create a new column?

texts:
I have two tables. I have about 6500 rows in one table and about 450 rows in other table. The data in the two tables are the same.

What I really want to do is to collect the data in the column "adet" in my table of 450 rows(the table name is "depoyaeklenecek") and the "adet" column in the 6500-row table(the table name is depoadetleri"). Thus, I would like to refresh my table in a way that I can write the number that I have collected in the "adet" column in the 6500-row table.
However, there is a problem, I think I have to match the ids in the "kapino" column to be able to add them. Because the data in both tables are not sorted in the same way, the capino data are in mixed order. I want it to collect the data in the "adet" column in two tables when the "kapino" ids in both tables are equal.
They are not tables with the same rows anyway. One is about 450 lines and the other is 6500 lines. Since the data in one of them will not be in the other, I need to check if the data in the kapino column is equal, then I need to collect it, I think I'm not sure.
Can I ask for help for this? Thanks


texts: I need help understanding Postgres's archive cleanup functionality

texts:
I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to  me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform recovery upon the slave server crashing (so basically everything except the last 1 or few segments, depending on how far behind my slave is running). I'd like to be able to perform emergency point in time recovery, in the unfortunate case of data corruption or accidental deletion, if possible. To do this, am I left with the following choices:

Keeping all WAL segments since the last base backup was created (in my case, this is the one that the slave server was created from)
Making base backups all the time or doing something with PGBarman, etc.?
Am I misunderstanding something completely?

If option 1 is used, I presume the pg archive cleanup command is rendered useless to me. If option 2 is used, is it true that I should use the cleanup command as intended (deleting all the extra WAL segments immediately after they're committed)? Is there something more that I'm missing (see option 3)?

Note: I'm using Postgres 9.2.4


texts: DBCC Physical only giving Connection failure

texts:
I have a SQL server 2012 Ent DB.

The hosting company is trying to setup new backup solution using Symantec Backup Exec.

They have come back and said our database is corrupt because they get these errors:

  Consistency checking TrafficSmartCIT
  Consistency Check Physical Only.
  V-79-57344-65085 - There was a problem running the DBCC.
  DBCC results for 'TrafficSmartCIT'.

  Consistency checking TrafficSmartCIT_ArcStats Consistency Check Physical Only.
  V-79-57344-65085 - There was a problem running the DBCC.
  Connection failure

  Consistency checking TrafficSmartCIT_HTML Consistency Check Physical Only.
  V-79-57344-65085 - There was a problem running the DBCC.
  Connection failure
  ...

I have tried finding out what the "connection failure" bit is, as it sounds more network related then actual corruption.

I have run a DBCC CHECKDB on one of the DB's above that threw an error, and it had no errors.

Has anyone seen this sort of error before? And should I be concerned.


texts:
I have a database with 3 filegroups: PRIMARY, AP, and GL.

I did a full database backup. I did a partial restore of just the PRIMARY and AP files successfully using this command:

-- Partial Backup (Primary & AP) to same database (works)
RESTORE DATABASE TestPartial
    FILE = N'TestPartial',
    FILE = N'TestPartialAP'
    FROM DISK = N'J:\SQL\MSSQL10_50.SGA\MSSQL\Backup\TestPartial.bak'
    WITH
        FILE = 1, NOUNLOAD, REPLACE, PARTIAL, STATS = 10

Is it possible to do this same partial restore, but to a different database? I have a database called TestPartial1, which I am trying to partially restore into. However, I'm getting the following error:

The backup set holds a backup of a database other than the existing 'TestPartial1' database.

Here is my code:

-- Partial Backup (Primary & AP) to a different database (doesn't work)
RESTORE DATABASE TestPartial1
    FILE = N'TestPartial',
    FILE = N'TestPartialAP'
    FROM DISK = N'J:\SQL\MSSQL10_50.SGA\MSSQL\Backup\TestPartial.bak'
    WITH
        FILE = 1, NOUNLOAD, REPLACE, PARTIAL, STATS = 10,
        MOVE N'TestPartial' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1.mdf',
        MOVE N'TestPartial_log' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1_log.ldf',
        MOVE N'TestPartialAP' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1AP.ndf'


texts: bind variable and shell scripting with sqlplus

texts:
I wish to execute DMLs using bind variable with shell scripts.
For example, something like:

#!/bin/bash

SH_NUM=10

sqlplus -S test_user/test_pass <<EOD
var a number;
a:=${SH_NUM}
insert into test_table values(a);
commit;
EOD

I'm not sure if this is possible, I can use this approch when using pl/sql but I wish to know if I can do it this method as well.


texts: How to apply patches in MySQL in any version

texts:
How to apply patches in MySQL 5.1.67 or any version.

because  we need to fix so many bugs in our version without any upgrading.

please suggest me .


texts: SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

texts:
I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically  is:

  SQL Error: ORA-12991: column is reference in a multi-column constraint
  Cause: an attempt was made to drop a column referenced by some constraints
  Action: Drop all constraints referencing the dropped column or specify CASCADE CONSTRAINTS in statement.

So i have gone ahead and searched for all the constraints that referenced the column by doing something like:

  SELECT * FROM USER_TAB_COLS
  WHERE COLUMN_NAME = 'My_Column_Name'

After finding and removing all the referenced constraints on the column that i want to drop I tried to drop the column again but i am still getting the same issue. Before posting the question i did take some time to research before this and have read the potential of this being a "Supplemental Logging" issue. Again i am very new to oracle DB programming as such could someone please shed some light on this and maybe suggest how and why i can not drop the column after having temporarily removed the constraints that reference the column i want to drop? Thanks in advance!

PS: Using Cascade Constraint Personally i don't think will fix the issue and i also do not want to remove all constraints without knowing which of them are being removed. So this is not a viable solution for me.


texts: Delete duplicates from a table with all the columns duplicated

texts:
I needed to remove duplicates from a table where every column was duplicated and there was no Prime Key.  The ID column was also an Identity column.

[It perplexes me why the Identity column wasn't made a PK when the table was created.  I'm going to look at doing that and test to ensure I don't break anything.]

The table isn't huge--just a few hundred rows.  In terms of performance, it doesn't matter if this is the most efficient method.  However, if there is a better way, I'm interested in hearing.

USE DBMGMT
GO

If(OBJECT_ID('tempdb..#Temp1') Is Not Null) Drop Table #Temp1

SELECT DISTINCT
    [ID]
    ,[Load_Name]
    ,[Remote_File_Name]
    ,[Local_File_Name]
    ,[Status]
    ,[Return_Code]
    ,[Group_Name]
    ,[Priority_Level]
    ,[Enabled]
    ,[Table_Name]
INTO #Temp1
FROM [dbo].[monthly_prd04_ftp_list]

SET IDENTITY_INSERT [dbo].[monthly_prd04_ftp_list] ON
GO

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO

INSERT INTO dbo.monthly_prd04_ftp_list
    (
    [ID]
    , [Load_Name]
    , [Remote_File_Name]
    , [Local_File_Name]
    , [Status]
    , [Return_Code]
    , [Group_Name]
    , [Priority_Level]
    , [Enabled]
    , [Table_Name]
    )
SELECT
    [ID]
    , [Load_Name]
    , [Remote_File_Name]
    , [Local_File_Name]
    , [Status]
    , [Return_Code]
    , [Group_Name]
    , [Priority_Level]
    , [Enabled]
    , [Table_Name]
FROM #Temp1

GO

SET IDENTITY_INSERT [dbo].[monthly_prd04_ftp_list] OFF
GO


texts: I couldn't compile this little query (needs 2 tables)(mysql)

texts:
i have 2 tables as like seen :

user table  :   id, userid , country

friends table :  id, userid , friendid

so i want to get friendid rows from friends table where friends.userid == user.userid  and user.country='USA' ?

thanks for your helps


texts: Implementing a graph-like design on postgresql using CTEs and inheritance

texts:
This question is about attempting to model/design an elementary graph-like database within postgres.

The starting point is that graphs have:

Nodes
Nodes have properties
Node are connected by (bi/uni-directional) edges
Edges, too, have properties.

As an example, we consider a situation where the entities/nodes are people and news articles. There can be edges between people, and people can be friends or followers with each other, and can have a "similarity" value.

So in trying to mimic a graph in postgres, we first create a single-columned table called Node. The actual nodes people and articles are inherited from this table. Then there's another table called node_property with a foreign key to node. From the table node_property, we inherit two tables called people_property and articles_property, with foreign keys to the people and articles tables respectively. There could possibly be more than one property table per node, for instance if one wanted to split out read-heavy and write-heavy columns, or based on some other criteria.

Then we do edges. First a table called edge with an id, and two columns called source and target, with foreign keys to any of the node tables (i.e. people or property). From this edge parent table, we inherit three tables called person_person with both fk's to the people table, article_article with both fk's to the articles table, and similarly person_article. Finally there is a table called edge_property with a foreign key to the edge table. From this edge_property table, we inherit property tables for each of the 3 aforementioned edges. These edge property tables have info like date when person read the article, boolean for if the person liked/shared the article, boolean for if the two persons are friends, and so on.

This basically looks just like a highly normalized regular rdbms design. The (recursive) CTE's feature of Postgres comes in very useful when doing actual graph-like queries - for example, to find articles liked by friends of friends, etc. Materialized views using multiple inner joins are used heavily for fast queries across multiple tables.

The biggest advantage this offers is the stability of postgres, with the ability to do some graph-like things.
The outstanding questions are:

What are the shortcomings of this sort of design? This is being run as an experiment, so I'm sure there are plenty. Are there ways this can be improved? What is this missing?
A specific point, does it make sense to have separate node/edge and the corresponding property tables or is better if they're combined (i.e. all properties in the node table itself)?


texts: Check if contiguous date interval exists

texts:
I have a table in PostgreSQL which describes some events that have a start date and an end date:

CREATE TABLE my_table
(
  event_id serial NOT NULL,
  start_date timestamp without time zone NOT NULL,
  end_date timestamp without time zone NOT NULL
)

A single event may overlap the previous and the next one. In the following table the first 3 of 4 events form a contiguous time interval:

1   '2015-04-02 22:09:03'   '2015-04-02 22:19:05'
2   '2015-04-02 22:17:38'   '2015-04-02 22:27:38'
3   '2015-04-02 22:25:21'   '2015-04-02 22:36:23'
4   '2015-04-02 22:45:23'   '2015-04-02 22:55:23'

Is it possible to write a query that checks if a contiguous date interval between two given dates exists?

I would like to have something like:

select ...
from my_table
where start_date > '2015-04-02' and end_date < '2015-04-06'


texts: Is running a query on a view any faster or slower than running the raw query itself?

texts:
Is there performance reasons to use views?

I realize this answer might be different per database. I'm using MS SQL but am curious about any database really. Does a view in general cause a performance optimization over its raw sql query equivalent?

Only thing I can think of is the select string size itself sent to the server... which is almost never a factor that anyone cares about.


texts: SQL Server 2008 Mirroring Pauses without warning

texts:
I have several databases mirroring with a witness.  All 3 machines are local to our network and have generous nics.  Monitoring the mirroring, it does not seem like there is a lot of network traffic.

Yet some of the databases' mirroring is pausing without warning.  When I restart them, they pause again almost immediately.

What could be the culprit?

EDIT:

Well, now it looks like they are mirroring again...when I went into mirroring manager and tried restarting them, it succeeded now--whereas yesterday it would automatically pause again.

Anyone know why mirrors would just pause? Wouldn't they just failover?


texts: Possible to do INSERT with some columns from SELECT query and some columns with specific values?

texts:
Is it possible to do an one INSERT statement where some values are from a SELECT query and some from specific values?
Trying to do something like this where cust_id, rep_id are from a select query and sku_ordered is a specified value:
    INSERT into ORDERS (cust_id, rep_id, sku_ordered='ABC123')
     SELECT id,
            sales_rep
     from CUSTOMERS
     where cust_name = 'ABC Corp';


texts: PostgreSQL Serialisation failure on different ids

texts:
Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2:

begin isolation level serializable;
select * from u where uid = 1;

And then continue with for T1 and T2:

insert into u (uid, name) values (1, 'A');

After commit; only the first succeeds while the other throws a serialisation failure.

This is a very neat feature of the mode to handle unique key violations in complex transactions and not resorting to a specific 'hack' insert ... on conflict. However even if uids are different, say uid = 2 and uid = 3, both transactions T1 and T2 still won't be able to commit.

How could it be possible? Supposedly they create different predicate SIReadlocks and select uses index scan. Where's the trick?


texts:
I need to determine the total size of an oracle database. Ideas on improving the following query?

SELECT 'DataFiles: ' AS TYPE, SUM (bytes / 1024 / 1024) AS MB
  FROM dba_data_files
UNION
SELECT 'Temp.: ', SUM (bytes / 1024 / 1024) AS MB FROM dba_temp_files
UNION
SELECT 'Redologs: ', SUM (bytes / 1024 / 1024) AS MB FROM v$log;


texts: SQL Server Maintenance Plan Fails to Rebuild the Indexes

texts:
When I run a maintenance plan to reorganize and rebuild tables indexes, it fails.
Below the last two rows that displays the fail:

 Source: Reorganize Index
 Executing query "ALTER INDEX [PK_xxTableName] O...".: 15% com...
 The package execution fa...
 The step failed.

What would be the cause?


texts: Same restore file (.bak), huge difference of execution duration time on various computers

texts:
First of all, the question is nothing to do with SQL performance optimization.

I have a .BAK SQL Server backup, and have restored it on my computer.

My computer: i7 2600 3.4GHz, 240GB SSD, 8GB RAM, Win 7 Ultimate x64, SQL Server 08 Ent x64.

When I ran a particular stored procedure the execution time is about 22 ~ 28 sec.

I restored the same .BAK file to my several of my colleague's computers, and run the same stored procedure, execution time is about 2 ~ 4 seconds.

*My colleague's computers: Various Intel/AMD CPUs, SATA HDD, Windows XP or Server 2003 or Windows 7 (x86/x64), with SQL Server 2005 or 2008; one colleague even has the same OS and SQL Server as mine.

I also have a server, XEON 5600 CPU, Windows Server 2003, SQL Server 2005, restore and run in about 46 seconds.

I've tried every method I could think of however I have no idea why my computer is running the stored procedure so slowly.

Why would my computer, when spec-wise it is so much faster, perform so much slower?


texts: MySQL similar name for status or type column

texts:
As status and type is reserved key in MySQL, I need a column name similar to status to identify the status of a row.

I know there is way to ignore this problem but in different language and different framework it suddenly arise this problem in different way, so I need to ignore this word and need a similar word that sound almost same and can be understandable from the name.

The potential values for my use case are true/false. What do you suggest to name a Boolean column that defines the status of that row?

Here status is defining that row if it is active or inactive. And not every where I'm using status column for this purpose, Somewhere status column is defining if the status is pending, current, confirmed, applied, rejected, etc. Like this shorts of key.


texts: Oracle dbms_redefinition fails with ora-01749, why?

texts:
I ran this as a part of redefining a table

DECLARE
  l_num_ers PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'myuser',
    orig_table        => 'mytable',
    int_table         => 'redef',
    copy_indexes      => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers     => TRUE,
    copy_constraints  => TRUE,
    copy_privileges   => TRUE,
    ignore_errors     => FALSE,
    num_errors        => l_num_ers);
   DBMS_OUTPUT.put_line('l_num_ers=' || l_num_ers);
END;
/

And got the error

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
ORA-06512: at "SYS.DBMS_REDEFINITION", line 752
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1698

So how can I solve this?.
The same code worked on another database where the only difference I've found is that the user myuser did not have select right on the table.

I did

Select PRIVILEGE, GRANTEE from dba_tab_privs where owner = 'MYUSER' and TABLE_NAME = 'MYTABLE';

To find the differences.

Is the solution to revoke my own privilege to the table?


texts: Can you do Log Shipping from Production non-Temporal to Data Lake Temporal setup Table?

texts:
Can you do log-shipping from a table in production without temporal tables to a secondary that has temporal tables setup?

Details:

We want a frozen nightly / yesterdays-data-only data-lake. And we would like it to be historical. Without production dealing with this historical footprint.


texts:
Running SQL2008 R2 SP2, database is in FULL Mode

Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I can take the log backups.

I put it in Simple mode but was curious if could shrink in FULL?  Think it is; or at least was possible

thanks in advance

Thanks for comments as stated; it is in Simple.

My question is; Can a transaction log be shrunk while it is in FULL mode?


texts:
We have the 2 databases.

Reporting
HR

There are some users who want to extract data from HR database.  But we don't want to give direct access to that HR database.

So, we created the special views in Reporting database and the users have dbowner right for that Reporting database.

But, when they try to run the query, the error message is showing...

The server principal "test" is not able to access the database "HR" under the current security context.

When I grant them as the db_datareader reader for HR database, it's all fine and they can run the query.  But it breaks the security and we don't want them to get access to the HR database directly.  That's why we made the special views in Reporting database.

How can I enable those users to run the view/query without giving direct access to the HR database?


texts: Why is real table size much bigger than estimated size?

texts:
I'm doing some benchmarks and I found a strange thing - real table size is in 7 times bigger than estimated size

I created a table with 1 int column (4 bytes)

CREATE TABLE `t_int` (
  `c_int` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And inserted 57M records with value 1. I expected to get table 228Mb (4 bytes * 57M records), but got 1.8Gb

           Name: t_int
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 57391085
 Avg_row_length: 36
    Data_length: 2089795584
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2017-01-12 22:42:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

optimize tables has not changed the results

Why is that?

MySQL 5.7.11


texts: Why does an index scan read more pages than exist in an index?

texts:
I've been trying out running some queries for a testing scenario and I noticed that when doing a full-scan of a nonclustered index, SQL Server reads more pages than actually are in the index.

As you can see from the image below, my query is (a simple one):

select
    sometext
    , somemoretext
from tbl

and I have an exact covering nonclustered index for those columns.

In Profiler the number of pages read for this is 22,356 as you can see in the image.

But when looking at the index details, under Fragmentation, the number of pages for this index is 22,278.

What is the cause for this difference in pages read (78 to be precise)? Is the number in the Fragmentation section not reliable or is there some other explanation?

The index is not fragmented (or appears not to be), so there are no more pages than those 22,278 .. or are there? I wasn't able to find an explanation for this online, on Microsoft sites or otherwise.

(This is a controlled environment, no inserts and no deletes occurred on the table while taking screenshots for this question)

I have also pasted the plan here.


texts: Can i use a custom function with UpdateOne or UpdateMany in MongoDB Shell?

texts:
I wish to perform update on my database using Update command.
the data structure is as following:

{
    "_id" : ObjectId("5d06285570de7d11606275f8"),
    ...
    ...
    ...
    "ProfilePictures" : [
        {
            "ImageId" : ObjectId("5d0a65d05e82a23764150978"),
            "CloudinaryUrl" : "<fileAddress>",
            ...
        }
    ],
    ...
}

i wish to update my URL, for this first i wrote this function:

var updateHttpToHttps=function(url){ return url.replace(/^http:\/\//i, 'https://'); }

and then i tried to update a new field named CloudinarySecureUrl using data from CloudinaryUrl field, and then i need to fix mismatch in the CloudinaryUrl changing all url starting with https to http:

db.humanResource.user.updateMany({ProfilePictures:{$exists:true}, ProfilePictures:{$ne:null}, ProfilePictures.CloudinaryUrl:{$exists:true}}, {$set:{ProfilePictures.$.CloudinarySecureUrl:updateHttpToHttps(ProfilePictures.$.CloudinaryUrl)}})

formatted version:

db.humanResource.user.updateMany({
    ProfilePictures: {
        $exists: true
    },
    ProfilePictures: {
        $ne: null
    },
    ProfilePictures.CloudinaryUrl: { // I did this because it said for using $ i have to brign a field from that array to my query
        $exists: true
    }
}, {
    $set: {
        ProfilePictures.$.CloudinarySecureUrl: updateHttpToHttps(ProfilePictures.$.CloudinaryUrl)
    }
})

So after writing this update query, i notice, maybe i know that i want to pass the value of ProfilePictures.$.CloudinaryUrl to the javascript function used in this update, but it doesn't necesserity mean that it will be processed, since all i write is a query language, not javascript code, so the system cannot pass the value to the function...
Is there any way to do this? How?


texts: Restriction on self referencing on insert in MYSQL?

texts:
How to restrict insert on adding self-referencing rows in a recursive relation table (a table contains foreign key points itself)?

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

An employee can't be the BOSS of themselves. Hence

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");
Query OK, 1 row affected (0.04 sec)

should be declined by some constraint. How can one add such a constraint?

If possible suggest without using a trigger.


texts: Do I have to commit after an exception has been raised?

texts:
My database generates a lot of orphan pages, and I'm trying to figure out why.

I often do this in my python code, using the fdb driver:

cursor = connection.cursor()
for item in list:
    try:
        cursor.execute(sql_insert_statement, (item,))
    except fdb.DatabaseError:
        log(f"Exception: duplicate found in database for {item}")
        continue

connection.commit()

I generate a lot of exceptions due to primary key unique constraint violations (which is fine), and I only commit the "main" transaction once, when all items in the list have each executed their INSERT statement.

Is this the right way of doing things? Or do I have to commit after each  exception that occurred instead (with a finally clause which would trigger after every try regardless of its outcome)?

At the very least, I doubt I need to commit after each cursor.execute, judging from the documentation.


texts: Polybase with SQL Server 2019 on Linux or Containers

texts:
I would like to install SQL Server 2019 on Linux either a full Linux server (Ubuntu 18.04) or on a Docker container with Polybase.  Installing MSSQL 2019 on a Linux Server or a container is relatively easy.  It's the Polybase bit that I cannot figure out.
Using the instructions at this Microsoft Docs site:  https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-linux-setup?view=sql-server-ver15
There is a reference to install Polybase on Ubuntu, which should be applicable in both cases (server or container).  When I attempted to install the Polybase packages on an Ubuntu server I got this result:
sudo apt-get install mssql-server-polybase
Installed without any issue
sudo apt-get install mssql-server-polybase-hadoop
The following packages have unmet dependencies: mssql-server-polybase-hadoop : Depends: mssql-zulu-jre-11 but it is not installable E: Unable to correct problems, you have held broken packages.
I could not find a solution to installing the package mssql-zulu-jre-11 on a Linux Server.
In a container, I logged in as a Root user and attempted the commands above:
apt-get install mssql-server-polybase
E: Unable to locate package mssql-server-polybase
I did verify that within the container the Linux instance has the correct sources for apt to find this package.  I am completely confused as to how to get Polybase running with SQL Server 2019 on Linux.  Have any of your had success?  Any help would be greatly appreciated!


texts: MySql event ENABLED on both primary and replicate of RDS multi-AZ deployment

texts:
I have a mysql database instance deployed in a multi-az configuration on Amazon RDS. We've seen replication fail a couple of times in the last two months, with an error message like,

Apply Error 1590: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

Looking closer into the logs on the replica, I noticed that it was trying to execute an event (we have a heartbeat event that is ENABLED on the primary, and should be in a SLAVESIDE_DISABLED state on the replica(s). Looking closer, I realized the failing replica has this event in ENABLED state, and since the server is running as ``--read-only` it fails while trying to run the event. There are errors like below in the replica logs.

2016-02-28 20:55:00 16040 [ERROR] Event Scheduler: [database_name@%][database_name.update_heartbeat_event] The MySQL server is running with the --read-only option so it cannot execute this statement

I tried dropping the event on the primary, but it had no effect on the replica, presumably because replication is in failing at this point.

Any pointers to help fix this (besides creating a new readonly replica from the primary?)

Thanks


texts: How to find repeating records effectively?

texts:
I have a registration application for registering 6 courses. The program will check the repeated register users first. Those users that needed to be checked comes from last year. If users registered for one course, they cannot register for any other courses.
---firstroundregisted table structure---

id|englishname  |idcard |
1 |CHAN SIU MING|A1234  |
2 |TAK HO KANG  |Z4123  |

---Contain >5000 last year registered users records for the 6 courses---

The sql in PHP as below.
SELECT * FROM firstroundregisted
WHERE englishname='".$_POST['studentname']."'
AND idcard='".$_POST['idcard']."'
LIMIT 1;

Is this sql the fastest way to find out if the user registered or not?
Others:

I asked a question here :
How to increase Max_used_connections?
Some gentlemen suggested me to optimizing the sql query so I start a new question to focus on optimizing. Thank you all for your help.


texts: Unique insert performance: Primary Key vs Unique Index

texts:
I have a table of unique values (domains_unique), with column domain varchar(255), with more than 20 mil records.

What's the fastest way to insert into the table, by keeping the domain unique constraint?

I've decided that the query should be :

INSERT IGNORE INTO domains_table (domain) VALUE ('domain.com')

Should I make domain the primary key or should I make it a unique index?

Primary key method:

CREATE TABLE `domains_unique`
(
     `domain` varchar(255) NOT NULL
     PRIMARY KEY (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

Unique index method:

CREATE TABLE `domains_unique`
(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `domain` varchar(255) NOT NULL
    PRIMARY KEY (`id`),
    UNIQUE KEY unique_index (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

Also how would much would changing the CHARSET and COLLATION to ascii_bin affect performance?

obs:

I would use INSERT DELAYED IGNORE INTO, but I need to know if the specific row (domain) was inserted, that also rules out the bulk insert.

UPDATE:

As promised the benchmarks results:

INSERT 5k unique rows with 4.5k new rows into a 1 mil row table, one row at a time:

Primary Key method:   5.7 seconds
Unique Index method:   6.3 seconds

To test scaling I've also tested the Unique Index method on a 40mil rows table and it took 45.8 s


texts: How to link a customer to a country when the city/state isn't known

texts:
We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the country, as hospital groups never span countries.

We want to be able to save as much information as we know, so if we know the city, state and country, we want to save all three. If we only know state and country, we want to save those two. If we only know the country, we want to save just that.

Now, I can see three ways of doing this, and was hoping someone would be able to comment as to which is preferable...

1) Have three tables, Countries, States and Cities, each of which is linked from the previous, so any city will have a StateID, and any state will have a CountryID. That way, if we know the city, we can work up the links and find out the state and country. This is neat, and preserves data integrity, but has the disadvantage that if we don't know the city, we can't save it.

One way we thought of to get around this was for each country to contain a placeholder state, that wouldn't be shown in the UI, and for each state to hold one placeholder city. That way, if we only know the country, the placeholder state for that country would be saved, as would the placeholder city for the state. This neatly gets around the problem, but requires placeholder states and cities to be inserted. This isn't a performance issue, as there wouldn't be enough of them to be a problem, but feels a bit like a hack.

2) Another approach is to have a single self-referencing Locations table, that contains a LocationTypID that identifies if the location is a country, state or city, as well as a ParentLocationID, which (if non-null) would refer to the containing location (ie states contain cities and countries contain states). That way, we can save the most granular location that we know.

The disadvantage of this is that the querying would be messy. We are using an ORM, which would work very well with the first approach, as you can choose a country, then navigate to the Regions property to see the associated regions, then for any region, use the Cities property. Approach #2 would require a manual query every time you wanted to get a different entity.

This approach is very flexible, but is probably more flexible than we need, and probably not worth the extra effort required to implement it.

3) The final suggestion was for the Systems table to have a CountryID, a StateID and CityID, and populate whichever one(s) is/are known. This is simple, and doesn't require any placeholder entries, but has one huge disadvantage (in my non-expert opinion), and that is that it seems to involve repeated data which can easily become invalid.

For example, suppose CountryA contains StateA, which in turn contains CityA, whereas CountryB contains StateB, which in turn contains CityB - imagine the same with Cs. There is little to stop someone mistakenly setting a system to be in CountryA, StateB and CityC, even though CityC is not in StateB, and StateB is not in CountryA.

Having played around with this a bit, my feeling is strongly towards the first approach, despite the requirement for placeholder rows, but I would like to hear what the experts have to say.

Anyone any comments?


texts:
I have a strange issue with my MySQL server. It suddenly restarts. It's a rare but very big problem for me.

This server is running as Master in Master-Slave replication.
I'm using MySQL 5.6.16-1+sury.org~precise+1-log

In syslog I see next info:

Jun  4 10:53:32 localhost kernel: [18821099.786264] init: mysql main process (25937) terminated with status 1
Jun  4 10:53:32 localhost kernel: [18821099.786329] init: mysql main process ended, respawning
Jun  4 10:53:32 localhost kernel: [18821100.064250] type=1400 audit(1433404412.925:26): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=9957 comm="apparmor_parser"

I see no errors in mysql common error files. I use Ubuntu 12.04 LTS, there is 32G of RAM on the server. Here is the config

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

query_cache_type = 1
innodb_buffer_pool_size = 9500M
max_heap_table_size = 268435456
tmp_table_size = 268435456
query_cache_size = 512M
sort_buffer_size = 8388608

innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 'O_DIRECT'
skip-external-locking
low-priority-updates
skip-name-resolve

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 600
query_cache_limit       = 4M
log_bin                 = /var/lib/mysql/mysql-bin
binlog_format           = MIXED
expire_logs_days        = 7
max_binlog_size         = 100M
binlog-do-db            = db1
binlog-do-db            = db2
binlog-do-db            = db3

server-id               = 1355115082

max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

Could you please help me with this problem or give some advice to think about?

Thank you in advance!


texts: How do I import a specific line from a text file into MySQL

texts:
I'm building an app to import data from a text file to a database.

I want to import only specific lines from the text file.  With Navicat I found that we could limit number of line when importing data.

I also found an article saying that we could limit it, so I tried this:

load data infile 'c:/coop/wkwk.txt' into table jo fields
    terminated by '|' lines terminated by '\n' limit 10,100;

But I get an error in SQL syntax, what is the QUERY to make this work?


texts: Linked server connections to Multi-subnet failover cluster

texts:
We have recently migrated our SQL Server distribution database to a multi subnet failover cluster, and have found that occasionally when adding articles/viewing replication status from the publisher we get errors like:

  Cannot initialize the data source object of OLE DB provider
  "SQLNCLI11" for linked server "repl_distributor". (.Net SqlClient Data
  Provider)

This appears to be due to the linked server connection talking to a multi subnet failover cluster (as described here)

Other than following the solution of implementing an ODBC connection on each node of our publisher (also a multi subnet failover cluster) is there anything else we can try to fix linked servers connecting to a multi subnet FCI?


texts: Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues?

texts:
Everyone, the table relationship diagram for the database is the below one

In case the full DDL for the database is requested, I can provide it (although it will probably exceed the character limit of Stackexchange).
Question 1)
The task is to query a 'Duration' column that shows the duration between the ArrivalDate and ReturnDate in days. If a trip is canceled, the value in that column should say 'Canceled' accordingly. I have researched a lot and could not find a solution without sub-querying to show the 'Duration' in the format e.g. '6 days'.
I have managed to achieve it the below way - by converting the DATEDIFF() first and then using the result as a sub-query (please disregard the ELSE condition and it does not set the field name to 'Canceled')
SELECT [Id], Firstname + ' ' + ISNULL(MiddleName,'') +  ' ' + LastName AS [Full Name], [From],
            CASE
                WHEN CanceledOrNot IS NOT NULL THEN 'Canceled'
                ELSE CONCAT(Duration, ' ', 'days')
            END AS Duration
FROM (
    SELECT Trips.[Id] AS [Id], Accounts.FirstName, Accounts.LastName, Accounts.MiddleName AS Middlename, Cities.[Name] AS [From], Hotels.CityId, Hotels.[Id] AS HotelId,
                 CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) AS [Duration], Trips.CancelDate AS CanceledOrNot
            FROM Accounts
                    LEFT JOIN Cities
                    ON Cities.Id = Accounts.CityId
                    JOIN AccountsTrips
                    ON AccountsTrips.AccountId = Accounts.[Id]
                    JOIN Trips
                    ON Trips.[Id] = AccountsTrips.TripId
                    JOIN Rooms
                    ON Rooms.[Id] = Trips.[RoomId]
                    JOIN Hotels
                    ON Rooms.HotelId = Hotels.[Id] ) AS ToAddDays

Every time I try to add the string directly after the converted DATEDIFF() in the CAST statement. without using a sub-query, an error appears. Why is it so - the DATEDIFF() output is converted to varchar(30) which should be fine for concatenation with the 'days' string? Here is the error-causing query:
SELECT Trips.[Id], Accounts.FirstName, Accounts.LastName, Cities.[Name] AS [From], Hotels.CityId,
                (CASE
                    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
                    --CONCAT(CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS VARBINARY(MAX)), 'days')
                    --((CONVERT(VARCHAR(100), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate))),
                    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
                END) AS [Duration]
            FROM Accounts
                    LEFT JOIN Cities
                    ON Cities.Id = Accounts.CityId
                    JOIN AccountsTrips
                    ON AccountsTrips.AccountId = Accounts.[Id]
                    JOIN Trips
                    ON Trips.[Id] = AccountsTrips.TripId
                    JOIN Rooms
                    ON Rooms.[Id] = Trips.[RoomId]
                    JOIN Hotels
                    ON Rooms.HotelId = Hotels.[Id]

The corresponding error is:
Conversion failed when converting the nvarchar value '4 days' to data type int.

I have tried multiple viations, with CONCAT_WS, FORMAT but without success. Is the only way the sub-query approach?
Question 2)
May I ask you as well how is it possible to SELECT and show in an additional column 'To' that includes the City of the Hotel?
The correct result set should look like this (please disregard the missing [Full Name] column in my query - this is not a problem as concatenation or simple addition works as the datatypes of FirstName and LastName are identical.
Thanks for your support!


texts:
I am coming from MSSQL world and learning MySQL now. I see that schema and database is same in MySQL, however these are different in MSSQL.

I want to logically group my tables as can be done using schema in MSSQL. How can I achieve this?

Edit 1

As per comments, I can't use schema to logically group my tables. Is there any other system which I can use? Maybe using a prefix in table names?

Edit 2

My use case is, I am designing a big software which would have multiple modules. Each of the modules would have multiple tables. I want these tables segregated from each other. Although all the tables would be used for this software only, but they should be kept apart. E.g. Employee module tables should be separate from Inventory module tables.


texts: What MySQL data types REQUIRE a max length (size) declaration in a create table statement?

texts:
I am converting a SQL Server table structure to a MySQL table structure, and I need to know how to properly declare the column data types within the CREATE TABLE statement.

I have a CSV file where each row gives me column info like so:

columnName,sqlServDataType,maxLength,precision,scale,isNullable,isPrimaryKey
idYear,int,4,10,0,0,1
...

I have a file / information on how I will programmatically map the SQL Server data type to the MySQL data type, but I need to know what MySQL data types require the max length declaration in a CREATE TABLE statement.

So for instance, in MySQL you can declare columnName INT or columnName INT(4) though there's really no point, from what I've read. However, doing something like VARCHAR(40) is important for both performance and data insertion concerns. Make a column too big for small data, it's inefficient. Make a column's max length too small for a given chunk of data, and the insertion will fail.

Ultimately for a given MySQL data type, I need to know if the max length e.g. VARCHAR(40) is required or not. I couldn't find much in the documentation.

Any pointers?


texts: Reverse the order in the table derived from lateral join

texts:
Example table:

CREATE TABLE `level_2` (
  `id` int(11) NOT NULL,
  `comment_id` int(11) DEFAULT NULL,
  `comment_parent` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `user_photo` varchar(2083) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `url` varchar(2083) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `comment_date` timestamp NULL DEFAULT NULL,
  `edited` tinyint(1) DEFAULT '0',
  `removed` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT;

Example rows:

INSERT INTO `level_2` (`id`, `comment_id`, `comment_parent`, `user_id`, `user_name`, `user_photo`, `url`, `comment_text`, `comment_date`, `edited`, `removed`) VALUES
(349, 407, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '1', '2019-03-05 00:07:41', 0, 0),
(350, 408, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '2', '2019-03-05 00:08:09', 0, 0),
(351, 412, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '3', '2019-03-05 00:55:23', 0, 0),
(352, 428, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '4', '2019-03-05 18:09:19', 0, 0),
(353, 430, 402, 55555555, 'example name', 'http://example.com/example_photo', 'http://example.com', '555', '2019-03-05 18:28:37', 0, 0);

My query:

SELECT *
FROM (SELECT DISTINCT comment_parent
      FROM level_2
      WHERE url = 'http://example.com'
        AND removed = 0 LIMIT 10) cp,
LATERAL (SELECT *
         FROM level_2
         WHERE url = 'http://example.com'
           AND comment_parent = cp.comment_parent
           AND removed = 0
         ORDER BY comment_id DESC LIMIT 3) l2

Returns the comments with the id 353, 352, 351 sorted like that.
But I need to return 351, 352, 353.

ORDER BY comment_id DESC in my query makes sure that I get 3 LAST comments with the same comment_parent and not 3 first comments with the same comment_parent. But also it returns 3 last comments in reverse order, while I want to return 3 last comments in natural order, not reversed.

I've also tried the following:

SELECT *
FROM (SELECT DISTINCT comment_parent
      FROM level_2
      WHERE url = 'http://example.com'
        AND removed = 0
      LIMIT 10) cp,
LATERAL (SELECT *
         FROM (SELECT *
               FROM level_2
               WHERE url = 'http://example.com'
                 AND comment_parent = cp.comment_parent
                 AND removed = 0
                 ORDER BY comment_id DESC LIMIT 3) sub
         ORDER BY comment_id ASC) l2

but it outputs EXACTLY the same results, with the comments being ordered in DESC.

The following also gives the same undesired output:

SELECT *
FROM (SELECT DISTINCT comment_parent
      FROM level_2
      WHERE url = 'http://example.com'
        AND removed = 0
      LIMIT 10) cp,
LATERAL (SELECT *
         FROM level_2
         WHERE url = 'http://example.com'
           AND comment_parent = cp.comment_parent
           AND removed = 0
         ORDER BY comment_id DESC LIMIT 3) l2,
LATERAL (SELECT *
         FROM level_2
         WHERE id = l2.id
         ORDER BY comment_id ASC) id

So no matter what I try, it still sorts that group of 3 comments in the desc order.


texts: Problem with Error: InnoDB: page ... log sequence ... number is in the future!

texts:
i know this question was asked before, but i am at a point where i am really stuck and do not know how to continue with this problem and i would really like to know, what causes this error to come up.

we had a replication which worked without any problem. our setup includes one database master and four slaves, all running percona mysql, same version (5.5.x). the other day our database master died because of a hardware issue. so we went on and made a slave the new master. should not be much of a problem and was indeed no problem before. but now after getting things up again, i keep getting lot's of log entries in my syslog like:

Mar 15 13:17:25 dbslave3 mysqld: 120315 13:17:25 InnoDB: Error: page 263337 log sequence number 34159788445
Mar 15 13:17:25 dbslave3 mysqld: InnoDB: is in the future! Current system log sequence number 1096541424.

Ok, so i used xtrabackup -- which i used for this things before -- to create a dump from the new master and used the dump for applying it on the slaves. without success. still the same problem with this error message. next i tried the following:

i took my site offline and made sure, that all data was replicated to all slaves -- indeed: replication is working! no data get's lost, every slave has all the data it should have.

next i did a reset slave on the slaves and on the master i did a reset master. in my opinion this should reset any binlogs and start over again with fresh sequence numbers. next i did a show master status and used the new sequence file and number for the change master to ... command on the slaves. i activated replication again and took my site online again.

without success! still the same problem. so my biggest question:

WTF is going on!? how can this error occur on:

a fresh set-up slave using xtrabackup
after a binlog reset on master and slave

how to solve it? i mean: i read a lot about inserting gigabytes of data in the database, that "the sequence pointer can catch up" ... but this sounds a lot like a dirty hack and can't be a real solution to this problem, no!? and my biggest concern: is the problem indeed solved using this dirty hack?

any other ideas what i could do? dumping the data using mysqldump and importing it again is no option btw., because the database is so big, that it would take to long to import it again.

UPDATE

what i also did is: dumping one of the slaves, including: deinstalling mysql completly, removing all mysql related stuff including binlogs, databases, etc. and setting it up again -- without success. that's the fresh install i am asking about above.


texts: Multiple concurrent connections to same large SQLite file on a network share

texts:
I have a 7GB SQLite file and I am running multiple queries on it from different virtual machines in the cloud. Some of the queries are running in Python using the built in sqlite3 package, and others originate from SQL queries running in DB Visualizer. The file itself is on a network share (and for policy reasons cannot be copied locally).

Will these concurrent queries slow each other down?

I could copy the SQLite file multiple times on the network share and connect each concurrent query to its own copy of the file. Would that speed things up?

I can imagine that both the sqlite3 Python driver and DBVisualizer copy the database file into local temporary storage first (or into memory) in which case there is no speed-up to be gained, or they don't.


texts:
Does anyone know how can I select rows that contain maximum value for some group of fields based on some criteria?

For instance, let's say I have the following table:

Table1:

ID   fieldA    fieldB    fieldC
1      A         X         1
2      A         X         2
3      A         X         3
4      A         Y         4
5      A         Y         1
6      B         Y         2
7      B         Z         3
8      B         Z         4
9      B         Z         5

Let's say that I want a table with results of maximum values of fieldC for each different entry of fieldA where fieldB=X, fieldB=Y, fieldB=Z and so on. The result should be:

ID   fieldA    fieldB    fieldC

3      A         X         3
4      A         Y         4
6      B         Y         2
9      B         Z         5

I know how to do it for one criteria, but not for a set of criteria:

SELECT    fieldA, MAX(fieldC) AS maxFieldC
FROM      table1
WHERE     fieldA = 'A'
GROUP BY ID

I tried a UNION clause, but I couldn't make it work.


texts: Why does Sql Server makes this bad query plan choice?

texts:
Sql Server gives me a bad query plan and I'm trying to understand why that is.

The query is this:

SELECT TOP (10)
    [Project1].[C1] AS [C1],
    [Project1].[Id] AS [Id],
    [Project1].[SupplierNumber] AS [SupplierNumber],
    [Project1].[ArticleNumber] AS [ArticleNumber],
    [Project1].[ArticleName] AS [ArticleName]
    FROM ( SELECT
        [Extent1].[SupplierNumber] AS [SupplierNumber],
        [Extent1].[ArticleNumber] AS [ArticleNumber],
        [Extent1].[Id] AS [Id],
        [Extent1].[ArticleName] AS [ArticleName],
        1 AS [C1]
        FROM  [dbo].[SalesEntry] AS [Extent1]
        LEFT OUTER LOOP JOIN [dbo].[Article] AS [Extent2]
            ON ([Extent1].[ArticleNumber] = [Extent2].[ArticleNumber])
                AND ([Extent1].[SupplierNumber] = [Extent2].[SupplierNumber])
        WHERE [Extent2].[id] IS NULL
    )  AS [Project1]
    ORDER BY [Project1].[SupplierNumber] ASC, [Project1].[ArticleNumber] ASC
  OPTION (TABLE HINT ([Extent1], INDEX(IX_Main)))

I have already annotated the query with two hints:

the join is forced to be a loop join and
I force an index that fits the order by criteria.

With these hints, I get an efficient query plan that looks like this:

  Scan index IX_Main on SalesEntry and for each 10 entries, look up the respective article entries with IX_Main on Articles.

Both tables have an IX_Main index on (SupplierNumber, ArticleNumber).

That way the query is fast.

Without the hints, however, Sql Server does a clustered index scan on SalesEntry, which is not useful at all, and an index scan on IX_Main for Article, and then brings the two streams together with a hash match.

That's not so fast, particularly because all the rows of SalesEntry now need to be scanned although we're only interested in the top 10 regarding IX_Main.

I'm confused as to why Sql Server would make that decision.

There's a TOP 10 specifier. That should tell Sql Server that it can get enough rows for the result super-fast with the index it chooses to ignore (IX_Main). It then would need to do only a lousy ten lookups with the index IX_Main on Article.

I already tried and failed to reduce this to a simple example that can be reproduced, so I'm putting this with as much information out there as I think it relevant.

Does anyone have an idea about what Sql Server's thought process might be?

(The query looks a bit weird as it is based on what my ORM, Entity Framework produces.)

EDIT: Here's the problematic plan as xml in a gist.