texts: Slow join behaviour with 'or' in predicate

texts:
I'm faced with a situation which I can't understand and overcome.

In short we have left-join query like:

select from a
left join b on a.key1=b.key1 or a.key1=b.key2

This works VERY slowly, while at the same time both separately:

select from a
left join b on a.key1=b.key1

select from a
left join b on a.key1=b.key2

work very fast.

b.key1 has normal index

b.key2 has normal index

I can't understand the reason for such behavior? Am I missing something very basic in my join strategy or index usage?

Here we go with detailed plans:

WITHOUT OR(TOP_USTR_ADMIN_IP - index name for ustrip column):

SQL> explain plan for
SELECT * FROM top.macs_constraint mc
LEFT JOIN top.top_ustr tu ON  tu.ustrip = mc.IP;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

   Plan hash value: 349751289

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    22 |  1296   (1)| 00:00:16 |
|   1 |  SORT AGGREGATE        |                   |     1 |    22 |            |              |
|*  2 |   HASH JOIN RIGHT OUTER|                   |   981K|    20M|  1296   (1)| 00:00:16 |
|*  3 |    INDEX FAST FULL SCAN| TOP_USTR_ADMIN_IP | 23187 |   181K|    62   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | MACS_CONSTRAINT   |   629K|  8601K|  1231   (1)| 00:00:15 |
--------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TU"."USTRIP"(+)="MC"."IP")
   3 - filter("TU"."USTRIP"(+) IS NOT NULL)

WITH OR:

explain plan for
SELECT count(*) FROM top.macs_constraint mc
LEFT JOIN top.top_ustr tu ON (tu.ustrip = mc.IP or tu.skladIP = mc.IP);

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2704565128

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     1 |    14 |   322M  (1)|999:59:59 |
|   1 |  SORT AGGREGATE      |                 |     1 |    14 |            |            |
|   2 |   NESTED LOOPS OUTER |                 |  1887K|    25M|   322M  (1)|999:59:59 |
|   3 |    TABLE ACCESS FULL | MACS_CONSTRAINT |   629K|  8601K|  1231   (1)| 00:00:15 |
|   4 |    VIEW              |                 |     3 |       |   513   (1)| 00:00:07 |
|*  5 |     TABLE ACCESS FULL| TOP_USTR        |     3 |    72 |   513   (1)| 00:00:07 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("TU"."SKLADIP" IS NOT NULL AND "TU"."SKLADIP"="MC"."IP" OR
              "TU"."USTRIP" IS NOT NULL AND "TU"."USTRIP"="MC"."IP")

Why does using or in the predicate give nested loops and no index usage? Is it possible to force index usage?

UPDATED:
missattached wrong plan for "without or".fixed


texts: Best way to model a single user with multiple usernames

texts:
I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames.

The context is a social gaming website. This may result in a user wishing to have more than one username. For example, one username for playing within a team (prefixed or similar) and one for playing alone.

I've been trying to rack my brains over the best way to represent this in my database, so far I've come up with:

In this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

I guess with this one, we're more or less guaranteed to have a username for each user, and the owned usernames table provides a list of usernames owned by the user. It feels better, but then there's duplicate information (the username is specified in two tables when active).

Anyone have better ideas of how I can structure this? Is there a common pattern for this?

Essentially, what I am envisaging is some way of providing functionality to my website of having one user per person, logging in with an email and password, and linking one or more usernames to that user.

I guess a 1:N relationship between user and username where N > 0. I'm just not sure about how to model this effectively. Or indeed, if this is a sign that my desired functionality needs re-thinking.

There will be no formal notion of a team, this was just an example where a person would maybe want more than one username.

Of course, we could simplify this and force people who want separate usernames to sign up multiple times. This would simplify the database a huge amount, but it'd be more convenient to the user to allow one login for multiple usernames.

I'm thinking of limiting this to start with to a maximum of 2 or 3, but I guess this doesn't change the design, just a front end limitation.


texts: Best lock strategy to prevent deadlock on concurrent updates

texts:
Yesterday I stumbled across a deadlock caused by two apps concurrently updating the same table. The operation would have been harmless in this instance if it weren't for the deadlock. The pattern is as below, which I've shamelessly copied from here.

-- Transaction 1
UPDATE customer SET ... WHERE id = 1
UPDATE customer SET ... WHERE id = 2

-- Transaction 2
UPDATE customer SET ... WHERE id = 2
UPDATE customer SET ... WHERE id = 1

There are countless explainers as to why this deadlocks, and suggestions as to best practices to avoid the pattern in the first place. What I haven't really found is a consistent suggestion as to what locking strategy is best employed for the simple case above, assuming you want or are happy to use explicit table locking to work around it.

The Postgres documentation is generally very good, but I find the explicit locking doc a bit of a head-spin in the way things are described in a sort of circular reference manner.

My first thought, given the docs state "this lock mode will be acquired by any command that modifies data in a table", was that ROW EXCLUSIVE would force a queue. But then in the conflict matrix on the same page it shows ROW EXCLUSIVE does not conflict with itself, so that isn't going to help, is it? So I'm thinking maybe SHARE ROW EXCLUSIVE, but given that is obtained by CREATE and UPDATE table it sounds perhaps heavy handed.

So I guess I'm hoping some long-in-the-tooth Postgres guru can say which lock they'd use here, assuming no complicating factors?


texts: How to query EndDate value from ReportServer..Subscriptions.MatchData

texts:
In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things.

While querying the ReportServer db, I've been successful in checking reports, shared data sources, etc. The one thing that I am having trouble with is querying for any value within the Subscriptions.MatchData column. Everything from this column comes back as NULL (unless I return the entire contents of the column).

Specifically, I need the value for EndDate. I've tried a number of tweaks to no avail, including toying around with XPath, XMLNamespace, etc. I found this basic example of how it is supposed to work in 2005. What is different about 2008 that this will not work? And how can I query this value in 2008 R2?

`
SELECT
[Description]
,x.m.value('StartDateTime[1]','varchar(30)') AS StartDateTime
,x.m.value('EndDate[1]','varchar(10)') AS EndDate
FROM
(
    SELECT
        [description]
        ,CAST(MatchData AS XML) AS MatchData
    FROM ReportServer.dbo.Subscriptions
) AS P
CROSS APPLY MatchData.nodes('//ScheduleDefinition') x(m)
`


texts: Best read performance when every query is known, and the data doesn't change

texts:
I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or so queries for the map rendering is static, except that the bounding box for each tile changes.

Is this a common situation? What's the best approach? For example:

Create an index exactly matching each query:

CREATE index roads_high_idx
 ON planet_osm_line
USING gist(way)
WHERE (highway IS NOT NULL OR railway IS NOT NULL)
  AND (tunnel IS NULL OR tunnel = 'no')
  AND (bridge IS NULL OR bridge = 'no');

Pre-load all the queries into other tables:

SELECT [columns]
INTO roads_high
FROM planet_osm_line;
CREATE index roads_high_idx
 ON planet_osm_line
WHERE (highway IS NOT NULL OR railway IS NOT NULL)
  AND (tunnel IS NULL OR tunnel = 'no')
  AND (bridge IS NULL OR bridge = 'no')
ORDER BY ...;

Some smarter approach, where Postgres figures this out implicitly?

Although all the queries are known, they're buried in an XML project file, so it's not quite trivial to extract them all.


texts: Database structure for hierarchical quiz?

texts:
I'm doing a personal project where I have a quiz with a number of boolean questions. Each question will have a two answer choices "true" and "false". Based on the selected answer, a new question will be asked with the similar pattern and based on that an another question. Any question may have any or no number of sub-questions.

The last questions in the tree (E, D) and questions with no sub-questions (F) will also store the correct answer from  either "true" or "false".

The alphabets A,B,C,D,E,F are questions. A quiz can have any number of master questions (like A), sub-questions like (like B,C,D,E) and individual questions (like F).

Kindly help me design a simple database schema for for this kind of quiz. I'm open to both relational (mysql) and document (mongodb) databases.


texts: Why SQL function always returning empty result set?

texts:
Following function I have write which accept xml and returns table as result.

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN
    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT
        l.v.value('Value2[1]','INT'),
        l.v.value('Value1[1]','INT')
    FROM @XML_Details.nodes('/Listings/listing')l(v)
    RETURN
END

And following is the code I using to run against above function but it always returning Empty result.

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)

DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT *
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result


texts:
What are the privileges required to execute a trigger function in PostgreSQL 8.4?

It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some day that the privileges required to execute a trigger function is the EXECUTE privilege but for the owner of the table, not the actual role that performs the action which fires the trigger that calls the trigger function.

I cannot find the documentation part that explains that point, any help?


texts: Replicate database without Internet connection

texts: I'm working on a company where I have a central DB and several agency databases. See the diagram at here. All the databases are identical, just the data can change and in some cases. I've no internet connection between agencies and central but I need to maintain a replication between central and agencies and in both path meaning for example if I make a change at central this change should be replicated across all the agencies and viceversa, if I any of the agencies make a change then should be replicated. This has a additional problem and is the following: from agencies to central all the data should be replicate all the time but from central to agencies only the updated data should be replicated and depending on which agency. For example: I made changes in Agency1 DB and Agency2 DB and those changes will be replicated to Central, I made changes in Central but for data taken from Agency1 DB, so I should replicate the changed data from Central to Agency1 DB only. Any help? Advice? Method?


texts: SQL Server on Windows Server (VMware) - online storage swap

texts:
I have a VM cluster designed to store multiple SQL Server instances. We use variety of storage providers (i.e. 3PAR, XIV) which have to be changed now (not relevant to what brand). On Oracle on Linux we use VLM / Oracle ASM to perform the storage swap while the Oracle instance remains online. I need to swap the storage of disks used as User_DB, User_DB_Logs and Backup partitions in Windows. Is there anything I can do in the SQL Server / Windows Server world to achieve it?

SQL Server Std & Enterprise 2012-2017

Windows Server 2012 R2 - 2019

EDIT 1:

I am looking for options like writing both data and log into two targets at the same time (using one MSSQL instance) or Windows Server / VMware level disk mirroring which would allow me to do a hot swap onece both disk are in synch.

My set up has well more than 500 individual SQL Server instances, many with 100+ DBs on it. Solution must be orchestrated and rather generic.

As far as I know there is nothing like that in Windows/MSSQL world, but this forum surprised me enough times to raise this question anyway.

EDIT 2:

I ended up with an idea to create second set of my databases on the same instance as they are primarily located. That would allow me to have a copy of my DB set located of freshly introduced disks (backed by fresh storage provider behind the VM). In my set up I need to migrate only user databases (including their logs) everything else stays on its current drive set (backups are easy to move).
Therefore what I need to achieve now is to force one of the available HA technologies to create a copy of my DB set into the same SQL Server instance.
Therefore the plan I have looks like that:

Spin up new disks for SQL DB data and DB logs
Introduce them to Windows and SQL Server
Set up a Transactional Replication of all user DBs into the same SQL Server instance, changing DB names and their physical location on drives
Once everything in synch, stop the SQL traffic, rename DB sets and fail-over into DB located on fresh drives
Clean up (old DBs, replication, buffer pools etc)

That would allow me to introduce new drives with minimal time off.

Can you think about anything faster than (not involving introducing of new SQL Server instances)?


texts: Why is MySQL not connecting when reading from mylogin.cnf?

texts:
On Ubuntu 20.04, I can log into a server by specifying the log credentials on the CLI:
$ mysql -h172.30.0.2 -uroot -p bar -e "SELECT id FROM users LIMIT 1;"
Enter password:
+----+
| id |
+----+
|  1 |
+----+

However, I cannot log in when using ~/.mylogin.cnf:
$ cat ~/.mylogin.cnf
[foo]
user=root
password="notTheRealPassword"
host=172.30.0.2

$ mysql --login-path=foo bar -e "SELECT id FROM users LIMIT 1;"
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I have confirmed that the file is being read by changing the permissions of the file and noticing MySQL complaining:
$ chmod 660 ~/.mylogin.cnf

$ mysql --login-path=foo bar -e "SELECT id FROM users LIMIT 1;"
mysql: [Warning] /home/dotancohen/.mylogin.cnf should be readable/writable only by current user.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Why might MySQL not connect when using ~/.mylogin.cnf? How should I debug why MySQL is not connecting?


texts: How to write a query which finds all circular references when a table references itself?

texts:
I have the following schema (names changed), which I cannot change:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);

That is, each record is a child of another record. If a record’s ParentId is equal to its Id, then the record is considered a root node.

I want to run query which will find all circular references. For example, with the data

INSERT INTO MyTable (Id, ParentId) VALUES
    (0, 0),
    (1, 0),
    (2, 4),
    (3, 2),
    (4, 3);

the query should return

Id | Cycle
2  | 2 < 4 < 3 < 2
3  | 3 < 2 < 4 < 3
4  | 4 < 3 < 2 < 4

I wrote the following query for SQL Server 2008 R2, and I am wondering if this query can be improved:

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (Id INT, HasParentalCycle BIT, Cycle VARCHAR(MAX));

DECLARE @i INT,
    @j INT,
    @flag BIT,
    @isRoot BIT,
    @ids VARCHAR(MAX);

DECLARE MyCursor CURSOR FAST_FORWARD FOR
    SELECT Id
    FROM MyTable;

OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @i;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF OBJECT_ID(N'tempdb..#Parents') IS NOT NULL DROP TABLE #Parents;
    CREATE TABLE #Parents (Id INT);

    SET @ids = NULL;
    SET @isRoot = 0;
    SET @flag = 0;
    SET @j = @i;
    INSERT INTO #Parents (Id) VALUES (@j);

    WHILE (1=1)
    BEGIN
        SELECT
            @j = ParentId,
            @isRoot = CASE WHEN ParentId = Id THEN 1 ELSE 0 END
        FROM MyTable
        WHERE Id = @j;

        IF (@isRoot = 1)
        BEGIN
            SET @flag = 0;
            BREAK;
        END

        IF EXISTS (SELECT 1 FROM #Parents WHERE Id = @j)
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
            SET @flag = 1;
            SELECT @ids = COALESCE(@ids + ' < ', '') + CAST(Id AS VARCHAR) FROM #Parents;
            BREAK;
        END
        ELSE
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
        END
    END

    INSERT INTO #Results (Id, HasParentalCycle, Cycle) VALUES (@i, @flag, @ids);

    FETCH NEXT FROM MyCursor INTO @i;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;

SELECT Id, Cycle
FROM #Results
WHERE HasParentalCycle = 1;


texts: Is there an equivalent of 'FORMATFILE' in MySQL?

texts:
I'm looking for a way to import data from a text file to a MySQL database,
the text file used to be imported in a SQL SERVER db via BCP and FORMATFILEs option for exemple :

BULK INSERT [MyTable] FROM 'myFolder\.txt'
WITH (
FORMATFILE = 'myFolder\MyTable.fmt',
ROWS_PER_BATCH = 5000,
MAXERRORS = 5000,
CODEPAGE = 'ACP',
ERRORFILE = 'myFolder\errors.txt'
  )

MyTable.fmt :

7.0
4
1 SQLCHAR 0 3 "" 1 Field1
2 SQLCHAR 0 7 "" 2 Field2
3 SQLCHAR 0 3 "" 3 Field3
4 SQLCHAR 0 2 "" 4 Field4

Fields (or columns) in the source file are not separated, the only thing to distinct data is the number of character by each column as mention in the  formatfile MyTable.fmt e.g. :

Field1 the first          3 characters form the left
Field2 the first (3+)     7 characters form the left
Field3 the first (3+7+)   3 characters form the left
Field4 the first (3+7+3+) 2 characters form the left

Any ideas how to proceed in MySQL ?


texts:
I have a problem with MySQL on Windows Server 2008 x64.

This is the first time I've installed MySQL 5.5 with performance problems.  I decided to go back to the older version that worked well with my server on Windows Server 2003 x86.

Now the MySQL service is slower than before when used on Windows Server 2003.

What would be the reason for the lower performance ?

I have a server with 2 processors (4 cores), 12 GB RAM and hard disc in RAID 5.

my my.ini

port=3306
default-character-set=latin1
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="D:/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
max_connections=160
query_cache_size=200M
table_cache=939
tmp_table_size=103M
thread_cache_size=16
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=339M
key_buffer_size=316M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=4096M
innodb_log_file_size=512M
innodb_thread_concurrency=10
log-slow-queries="D:/MySQLLog/SlowQuery/SlowQuery.log"
log-error="D:/MySQLLog/Error/error.log"

Thanks in advance!


texts: How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

texts:
I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and XML text and will make validation.

I will have 1 configuration database and many installations databases. From that prespective I wonder where to create that function - inside configuration database or inside each installation DB?

From support point of view it would be better to have only one CLR fuinction.


texts: List column value for all databases that have a specific table and column

texts:
What I am really looking for is the name of each database with table ApplicationVersion and column [Version] as well as the value in [Version]. Some of my databases don't have the specified table column and should be ignored. The table always has only one row.

Database    ApplicationVersion
DB_001      5.123.0.234
DB_002      5.123.0.234
DB_003      6.223.0.435


texts:
I have three tables:

competitions (id, name, date)
athletes (id,name)
results (place, id_athlete, id_competition, ranking_points)

where:

results.id_athlet=athlet.id
results.id_competition=competitions.id

I need a query to select the latest ranking_points of each athlete based on competitions.date.


texts:
I'm trying to send email using the DatabaseMail on SQL Server 2008, I can see my emails setting in the

msdb.dbo.sysmail_unsentitems

But they just sit there, I get no error messages.
I've checked I have rights to use the DatabaseMail by using the following TSQL:

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';

And I have also run

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

which shows the length as the number of emails I've tried to send but the Status is still showing as Inactive.

Am I missing something else here?


texts: SQLCLR assembly throws error when multiple queries run simultaneously

texts:
I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of late, the database has grown and the number of records have crossed a few hundred thousand with multiple users performing searches. Now, I encounter this error.

Msg 6522, Level 16, State 2, Procedure SchoolNameAutoCompletion, Line 42
A .NET Framework error occurred during execution of user-defined routine or aggregate "QGramsDistance":
System.InvalidOperationException: Collection was modified; enumeration operation may not execute.
System.InvalidOperationException:
   at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Collections.Generic.List`1.Enumerator.MoveNextRare()
   at System.Collections.Generic.List`1.Enumerator.MoveNext()
   at SimMetricsMetricUtilities.QGramsDistance.GetActualSimilarity(Collection`1 firstTokens, Collection`1 secondTokens)
   at SimMetricsMetricUtilities.QGramsDistance.GetSimilarity(String firstWord, String secondWord)
   at MyAssembly.DbTextFunctions.StringMetrics.QGramsDistance(SqlString firstWord, SqlString secondWord)

I have tried using the latest version of the SimMetrics in SQL Server 2012 using the .NET framework v4.0.30319 but has not made a difference. It's pretty clear that this error occurs only when more than a single search is performed.
Has anyone come across such an issue and have it resolved?


texts: Installation of SQL Server 2014 fails on Windows Server 2012 R2

texts:
I am attempting to run a silent installation of SQL Server 2014 with SP1 on Windows Server 2012 R2 core install, although it also fails when using UIMODE="EnableUIOnServerCore":

  (01) 2016-02-05 19:48:33 Slp: Running Action: ExpandInstallFeatures
  (01) 2016-02-05 19:48:33 Slp: Error: Action   > "Microsoft.SqlServer.Configuration.SetupExtension.ExpandInstallFeatures" threw an exception during execution.
  (01) 2016-02-05 19:48:33 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Unable to load DLL 'sqlconf.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) ---> System.DllNotFoundException: Unable to load DLL 'sqlconf.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

  Error result: -2146233052
  Result facility code: 19
  Result error code: 5412

Any thoughts on why sqlconf.dll would not be present? Prior to the installation I have run SqlSupport.msi.

This server is part of a two-node FCI which is meant for AlwaysOn.

Summary.txt is available at https://onedrive.live.com/redir?resid=CBCE97C71A32BCAE%21368391.


texts: How does PostgreSQL compute the cost for index scan?

texts:
Consider the following plan:

Index Scan using foo_c1_idx1 on foo  (cost=0.00..8.38 rows=1 width=37) (actual time=0.012..0.013 rows=1 loops=1)
  Index Cond: (c1 = 500)
  Buffers: shared hit=4
Total runtime: 0.036 ms

As we can see, the cost to get all rows is 8.38. On my system

SELECT current_setting('random_page_cost');

return 4.

So, as long as the index scan includes 2 random page reading (The first one is to read an index, and the second one to read the actual table), it's not clear what the 0.38 means in the plan?

Could you explain how it's computed?


texts: Query optimizer triggering statistics update seems to be slowing down queries

texts:
We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around the last 6 months of data, with roughly the same number of rows per day. On a daily basis, we delete the oldest day and insert the most recent day. The select stored procedure has isolation level set to read uncommitted (which I understand has its own issues, but we felt the risk of blocking was greater than the risk/likelihood of reading incomplete data).
Recently, the select query has been timing out at 30 seconds (a timeout value we set - not sure how long the query would have actually taken).  The issue ultimately resolves itself anywhere between 30-120 seconds later.
I explored various possible causes, but my current theory is that the query optimizer detects out of date statistics, triggering a stats update on the table, which it waits to complete, and then creates an execution plan once the stats have been updated. This was confirmed by looking at the date/time the stats were last updated, which coincides exactly with the query slowdown.
Side note: the first time this issue occurred, it coincided with our daily insert of data. The second time it occurred, it didn't seem to coincide with the insert or delete. But both times, the slowdown coincided with a stats update. I'm not sure why the stats became outdated the second time.
I see a couple options here:

set auto stats update async so that the query optimizer doesn't wait for the stats update to complete
disable auto stats update and manually update stats nightly (would also probably specify a query hint in the stored procedure to use the clustered index, but not sure if that's necessary).

So, does my theory for the cause sound reasonable?
Which of my options is best?
Are there other options?


texts:
I tried adding this line in /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu)

innodb_strict_mode = 1

This is within the [mysqld] section, where I have seen this line in other examples. I know this config file is being read at startup because I get errors if I type in nonsense.

Nevertheless, SHOW VARIABLES lists this variable as OFF and apparently I can insert rows without specifying my non-default fields.

Why isn't this variable being set?

I have tried =on instead of =1, no difference. The config is otherwise unmodified from the installation default, AFAIK, full text here.


texts: Executing a SQL Stored procedure from a SSRS text box item - Is it possible

texts:
I know the we can use custom code in SSRS and I know you have actions on Textbox items in a SSRS report.

Is it possible to "combine" this functionality for the following, which I appreciate is quite out the normal use of SSRS

I wish to produce a list of outstanding requisitions for purchase orders from the SQL tables of our ERP system. In the report against each item would be a Text box with the word "Approve" in
The idea is for the requisitions the user wishes to approve they would click on the "Approve" and then use the custom code would fire a SQL procedure, passing in the id of the requisition to the procedure. This procedure would interact with the ERP system to do the approval.

Is this possible?

Some pseudo code for the SSRS report code

Imports System.data.sqlclient
Dim connectionString AS String = "Server=XXXXXXX;Database=db1234;User Id=user_name;Password=my_password"
Dim Command As New SQLCommand("[CHC_AppBuilder].[Testssrscall]", connectionString)
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()

The SQL procedure [CHC_AppBuilder].[Testssrscall] just currently executes dbmail to myself

Problems so far:

Location of the System.data.sqlclient.dll to add the references as per you would in visual studio, so far not been able to test the code.
Trigger the Stored procedure per item.


texts: Using unique non clustered index with unique clustered index

texts:
Would there be any scenario where it would make sense to create a unique non clustered index that includes a column that is contained in a unique clustered index?

In other words, if I have a Products table with a unique clustered index on the ProductID column. Would there be an acceptable scenario where I would need to create a unique non clustered index that includes the ProductID column in addition to a couple of other columns, or, would the mere inclusion of the unique ProductID column make creating the non clustered index unique irrelevant?


texts: MySQL determine day of week when working with multiple date fields

texts:
So I have this existing case statement in my query:
SELECT
CASE
WHEN dl.list_id = 1000000
AND dl.date_inserted != ''
THEN dl.date_inserted
ELSE dl.entry_date
END AS date

Let me give some context here. So as soon as we have a record move to list_id 1000000 I need to reference date_inserted to determine when this record entered the table but for all else I need to reference entry_date.
Next I need to be able to identify which day of the week the above date amounts to for ex. Saturday.
So I created a 2nd table which lists each date for 2020 and the corresponding day so my plan is
CASE
WHEN mc1.day IS NOT NULL
THEN mc1.day
ELSE mc2.day
END AS day_of_the_week
LEFT JOIN "db"."meta_calendar" mc1 ON dl.date_inserted = mc1.date
LEFT JOIN "db"."meta_calendar" mc2 ON dl.entry_date = mc1.date AND dl.date_inserted = ''

The result seems to be producing the expected output but I have a feeling there could be a better way to achieve this. Basically I need this validated and if this is not the ideal way to achieve this then please advise


texts: What is "special" about PostgreSQL update vs delete+insert

texts:
My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple.
In other words, update = delete + insert.
Or so I had believe. But it appears there something fundamentally different about update from delete+insert in MVCC.

Setup:
CREATE TABLE example (a int PRIMARY KEY, b int);
INSERT INTO example VALUES (1, 1);

Method 1: Update
-- session A                          session B
BEGIN;
UPDATE example SET b = 2 WHERE a = 1;
                                      DELETE FROM example WHERE a = 1;
COMMIT;
-- now there are 0 rows in table example (1 row was deleted by session B)

Method 2: Delete and insert
-- session A                          session B
BEGIN;
DELETE FROM example WHERE a = 1;
INSERT INTO example VALUES (1, 2);
                                      DELETE FROM example WHERE a = 1;
COMMIT;
-- now there is 1 row in table example (0 rows deleted by session B)

Thus
UPDATE example SET b = 2 WHERE a = 1;

is different from
DELETE FROM example WHERE a = 1;
INSERT INTO example VALUES (1, 2);

How am I to understand the MVCC nature of update? Does the tuple has some sort of MVCC "identity" that is preserved during the update? What is it?


texts: Configuring SQL Server 2008 R2 Change Data Capture with SSAS

texts:
I'm completely new to SQL Server Analysis Servers (SSAS) so you'll have to excuse my ignorance.  I've been running through the MSDN SSAS tutorial get a handle on how to use SSAS.

We have SQL Server Reporting Services (SSRS) reports configured to display user login history over a given time period.  We have a table with user id and login time. This is joined to the user management table to display details about the user on the report. However, this is not true point-in-time reporting as the user's details, their surname for example, may have changed over the given time period. We are not using SSAS at the moment. We do have other, more complicated, requirements but this is a good example to begin with.

I want to enable SQL Server 2008 Change Data Capture on the user management table.  This data would then be pulled into the reporting database and transformed into a data warehouse for faster searching.

Firstly, is SSAS the way to go? I could write a SQL query to get the latest user record for each login, however it's a little fiddly, especially in queries that have multiple joins, and I don't want to reinvent the wheel if some technology already does that, possibly faster.

If SSAS is indeed the way to go how would I go about achieving this? Has anyone out there already implemented this solution and what did you do?

I've been looking for books or any forum posts and can't find any information on this specific requirement, including emailing people at Kimball. I really need an idiots guide.


texts:
A table of boxes, with a capacity column, and a table of items
with columns box and size, and you want to enforce the constraint that an item can't be put in a box of capacity smaller than the item.

AFAIK in oracle, this can be done with a materialised view, or a trigger. Which would perform better, and are there other reasons to use one rather than other?


texts: Parallel execution of a SQL Server external script

texts:
TLDR for a reader with the same problem
If your external script is too slow, you may do something in your scritpt which can be implemented in TSQL, which is a big problem. If this is not the case use CLR or process your data in a external application where the functionality is present. Normally you should not rely on external scripts to achieve high performance. CLR is also more secure.
I am not allowed to do the above, because of workspace/interpersonal reasons.
Problem
I have to write a query which must rely on regular expressions and I am not allowed to use CLR or use anything outside the SQL Server environment. So I must use a R or Python external script. I have experience with Python. So I went with that.
I wrote the script it works but the input data is extremely large so the external script execution takes a long time. I already heavily optimized the script, but it is still slow. So I plan to parallelize it.
Python's multiprocessing module doesn't work inside the external script. So I got a strange idea:
What if I horizontally partition the temp table which holds the input data and execute the external script on the partitions using asynchronous procedure execution, then I just get back their union?
Or perhaps there is a better way to parallel execution of an exterenal script?
I work on SQL Server 2019 Development version we also have an enterprise version which I guess doesn't matter here.
Details
@J.D rightly asked for more details. I am only allowed to share a limited amount:
The temp table providing the input has different columns for the actual-, birth- and mother names for different people.
I also have a dataframe in the external script containing valid first names and associated genders parsed from a JSON which is passed as a nvarchar(max). This method described here.
My script extracts titles, surnames, given names and guess biological sex and martial status. The names are not English names.
I apply a function to the first axis of the input Pandas Dataframe which does the extraction.
I use only Pandas provided methods.
With a standard python environment I would use the np.array_split function to split the dataframe and use the multiprocessing module to apply extraction function on the splits in parallel processes, then concatenate the dataframes. As you can see this is a text processing problem and cannot be vectorized as mathematical one could be. So tools like Dask cannot be used here.
Why not CLR?
Using CLR would be a sane choice. So a colleague of mine and I also recommended to use CLR. Our expert said using CLR is very dangerous. Yes I know about the CLR strict security option, but our expert may not. So I am forced to use external script against my better judgement.
Why not use multi-threading
Python's Global Interpreter Lock only allows one thread to be executed by the interpreter. So multi-threading with python will not provide parallel computation, at least with the standard implementation. This is not a limitation for Jython or IronPython, but this is a different story.


texts:
SELECT
   SUM(a)-SUM(b) AS c1,
   SUM(a) AS c2,
   SUM(b) AS c3
FROM tbldoc
GROUP BY Cid
WHERE c1<>0

If you take ( WHERE c1<>0 )  not error

Thank you Answer


texts: When benchmarking using sysbench and utilizing concurrency (multiple threads) for example on a table with 1,000,000 records, does sysbench run "identical" queries in a loop on the table?


texts: How do I round time to the upper multiple of an arbitrary time interval?

texts:
Example:

If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00.
If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '10 minute', then the desired output is 2018-05-17 22:50:00.
If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '1 hour', then the desired output is 2018-05-17 23:00:00.
If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '1 day', then the desired output is 2018-05-18 00:00:00.


texts: In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?

texts:
I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.
Here is the PostgreSQL script I wrote to explain my question.
drop table if exists t1 cascade;
drop table if exists t2 cascade;
create table t1
as
select trunc((i-1)/15) n1,
       trunc((i-1)/15) n2,
       rpad(i::text,180)::text    v2
  from generate_series(1,30000) a(i);
create table t2 as select * from t1;

update t1 set v2 = 'kk' where v2 like '42%';
vacuum t1;
vacuum t2;

Below is the query I executed followed by its execution plan
select  * from t1
except
select * from t2;

**HashSetOp Except** (actual time=32.602..33.485 rows=11 loops=1)
   Buffers: shared hit=1716
   ->  Append (actual time=0.014..15.065 rows=60000 loops=1)
         Buffers: shared hit=1716
         ->  Subquery Scan on "*SELECT* 1" (actual time=0.013..6.388 rows=30000 loops=1)
               Buffers: shared hit=858
               ->  Seq Scan on t1 (actual time=0.010..2.801 rows=30000 loops=1)
                     Buffers: shared hit=858
         ->  Subquery Scan on "*SELECT* 2" (actual time=0.011..5.596 rows=30000 loops=1)
               Buffers: shared hit=858
               ->  Seq Scan on t2 (actual time=0.009..1.590 rows=30000 loops=1)
                     Buffers: shared hit=858
 Planning:
   Buffers: shared hit=40
 Planning Time: 0.230 ms
 Execution Time: 34.567 ms

Below is another query with the same data followed by its execution plan.
select n1, n2, v2
  from t1
 where not exists (select 1
                     from t2
                    where t1.n1 = t2.n1
                      and t1.n2 = t2.n2
                      and t1.v2 = t2.v2)
  Hash Anti Join (actual time=12.733..35.390 rows=111 loops=1)
   Hash Cond: ((t1.n1 = t2.n1) AND (t1.n2 = t2.n2) AND (t1.v2 = t2.v2))
   Buffers: shared hit=1717, temp read=800 written=800
   ->  Seq Scan on t1 (actual time=0.008..1.985 rows=30000 loops=1)
         Buffers: shared hit=858
   ->  Hash (actual time=12.455..12.455 rows=30000 loops=1)
         Buckets: 32768  Batches: 2  Memory Usage: 3679kB
         Buffers: shared hit=858, temp written=400
         ->  Seq Scan on t2 (actual time=0.003..2.125 rows=30000 loops=1)
               Buffers: shared hit=858
 Planning:
   Buffers: shared hit=33
 Planning Time: 0.484 ms
 Execution Time: 35.457 ms

My question is :

I know that both the hash join and HashSetOp Except above use a hash table.
While the hash table in the first query overflowed work_mem, it seems that HashSetOp Expert operation didn't overflow work_mem (I can't see any indication that it used temp files in the execution plan.)
Do they use different memory areas?
If both the hash join and the HashSetOp Except use the same memory area, how does the HashSetOp Except operation consume smaller area?
I would appreciate it if anyone could give me a clue or answer.


texts: Difference in size of table between InnoDB and MEMORY engines

texts:
Question

The same table stored on hard drive (InnoDB engine) takes 930.72 MB when in Ram (MEMORY engine) it takes 1538.54 MB.

For what reason?
How to calculate space needed to save data in memory? Are there any general rules?

Example

Text below is not question, but allow reconstruct my database state.

We create database:

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;

Create table innoDB

CREATE TABLE main(
  id INTEGER UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value INTEGER
);

Define procedure to load data

delimiter #
create procedure load_data(IN _max INTEGER)
begin

declare counter int unsigned default 0;

  truncate table main;
  start transaction;
  while counter < _max do
    set counter=counter+1;
    insert into main (value) values (counter);
  end while;
  commit;
end #

delimiter ;

Call procedure

call load_data(25000);

Create table MEMORY

-- SET max_heap_table_size = 16*1024*1024;

CREATE TABLE memory_main (
  id INTEGER UNIQUE NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value INTEGER
) ENGINE=MEMORY
AS SELECT * FROM main;

We measure size of both tables and this size is not the same.

SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "test"
    AND table_name LIKE "%main%";


texts: Is there a way to get count based on multiple columns but still have it be distinct in SQL?

texts:
I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL.

Hopefully, this is possible as the algorithm I have is not giving me the correct results.


texts: Sum of two marks divided by 2 for each student

texts:
The image shown below is the answer I found, but I want the sum of two marks divided by 2. The image shows that we take only the better mark from task 1 and task 2. I want the better mark from task 1 and task 2 and add the "pen paper" mark divided by 2 for each student. My code is shown below:

WITH StudentMarks
AS (
SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
WHERE  m.AssessmentToolID IN ('3','4')
)
SELECT Class
,Division
,Subpart
,Term
,Subject
,StudentID
,Mark
,Grade
,Assesmenttool
,Studentname
FROM StudentMarks a
WHERE Mark = (
    SELECT max(Mark)
    FROM StudentMarks
    WHERE Class = a.Class
        AND Division = a.Division
        AND Subpart = a.Subpart
        AND Term = a.Term
        AND Subject = a.Subject
        AND StudentID = a.Studentid

    )

union

SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
where (M.AssessmentToolID=5)


texts: Copy table form MS-SQL server to MySQL, is it possible?

texts:
I have a procedure, say "proc1", inside a MS-SQL server.
I would like to create a table (copy of proc1) inside a MySQL server.

So far what I do is this:

1-Create a connection to MS-SQL Server
2- run the code $sql = "exec [procedureName] '$param' ";
3-Traverse all 500.000 lines.
4-Store all lines inside a php variable (had to set ini_set('memory_limit','1024M');).
5-Close the connection.
6-Store the data inside a file.
7-Create a table inside a Mysql server, says MySQL_tbl1.
8-Traverse the file, line by line, Sanitize each field -> escape special character and insert inside MySQL_tl1.
Note: I've tried some variations like insert in one shoot. It improve my code only in 10 seconds.

If I was dealing with a MySQL table to generate another table inside MySQL I could use INSERT INTO ... SELECT. . I think this could be the faster way to create a new table inside the MySQL server.
But I am dealing MS-SQL to MySQL.
So my question is that:
Is it possible to use INSERT INTO ... SELECT in this situation? If yes, how?

Just add a note:
I would like to do this using code => (php+Mysql)


texts: Why can't I make this insert statement a procedure?

texts:
The insert statement works, but when I try to make it a procedure it doesn't compile.
CREATE OR REPLACE PROCEDURE INSERT_INTO_CPP AS
BEGIN
    INSERT INTO cpp /*+ ignore_row_on_dupkey_index ( cpp ( pk_cpp ) ) */
    SELECT * FROM EXTERNAL (
        (
      cpp VARCHAR2 (50),
      rfu1 NUMBER (6, 2),
      rfu2 NUMBER (6, 2),
      mean_rfu NUMBER (6, 2),
      charge_ph7_4 NUMBER (2),
      hydropathy NUMBER (3, 1))

        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY cpp_data
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            SKIP 1
            BADFILE cpp_data:'cpp_badfile.bad'
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
            )
        LOCATION ('LYS_cpp.CSV')
        REJECT LIMIT UNLIMITED) ext
        WHERE NOT EXISTS (
            SELECT * FROM cpp c
            WHERE c.pk_cpp = cpp );
END INSERT_INTO_CPP;
/

I get this error:
PL/SQL: ORA-06553: PLS-103: Encountered the symbol ")" when expecting one of the following:     . ( of


texts: MYSQL How do I query to show data in separate columns from one field?

texts:
sorry if my question is worded poorly, my SQL vocabulary is very poor.

So basically, suppose I had a table as so:

CREATE TABLE MYTABLE
{
item_id   int   AUTO_INCREMENT  PRIMARY KEY,
item_name VARCHAR(20)
};
INSERT INTO MYTABLE (item_name) VALUES
('Fred'),
('Arthur'),
('Jane');

Now i want to fetch data that lists "Fred", "Arthur", and "Jane" into three separate columns. Is there a way to do this?


texts:
We recently hit this error shortly after enabling replication on a table.

Length of LOB data (%1) to be replicated exceeds configured maximum %2

I've found solutions to this problem already:

https://stackoverflow.com/questions/925824/when-insert-length-of-lob-data-to-be-replicated-exceeds-configured-maximum-65536
http://sqlmusings.wordpress.com/2008/09/22/issue-length-of-lob-data-to-be-replicated-exceeds-configured-maximum-65536/

However there's no explanation of what this configuration was there for in the first place.  If we turn off the LOB size limit what side effects does that have - are we opening ourselves up to more issues in the future?

Thanks in advance.


texts: Moving data into an archive table and reducing the size of the original table

texts:
On a SQL Server I inherited, there is a 55GB database on a 70GB drive. There are many small tables, and one big table that is 50GB in size (approx 36GB data, 15GB index). The log file is on a different drive. After shrinkfile the log file's drive has about 12GB free. Database is in simple recovery mode.

The table is badly designed, for example there are many columns defined as char(10) that have nothing bigger than 3 characters in them.

There isn't enough free space on the drive to alter these columns and then rebuild the clustered index (when altering a column the database creates a new column then copies the data from the old).

I'm wondering if I can instead do the following:

Create an archive table with a much better space saving design (appropriately sized columns for the data they contain)
Incrementally move old data from the huge table to the archive table.

Will the source table reduce in size when data is deleted from it (after being moved to the archive table)? Or would I have to do a clustered index rebuild for that to happen?  (In which case I cannot do it because there isn't enough space.)

It now occurs to me that column alteration uses space in the log file, but whenever I do a column alteration on a smaller table, the main data file grows as well.


texts: How do I reclaim space in filestream data files for database in a SQL Server Availability Groups

texts:
I have a SQL 2016 Ent. Edition AG with 3 nodes. We have a database in the AG with 5 filestream tables. Each table is in it's own filestream data file. Today I fixed a bug where all of the files were being saved to one filestream data file by rebuilding the indexes.

In dev, we don't have an AG and the database is in simple recovery. The space was reclaimed. The before and after looks like this:

When I ran the same code in the AG, all of the nodes look like this:

The files were moved to the right filestream data file, but space wasn't recovered from the original data file.

At first I thought it was garage collection, I read Paul Randal's blog post. I shrunk the logfile, then created a junk table, added a ton of rows via an explicit trans, ran a log backup and checkpoint, all on the primary node. The log file did grow, the previously active VLF was marked inactive.

To complicate matters, backups are full copy-only \ log backups on a secondary node.

What's the correct way to reclaim the space in this scenario?

Edit: After following Andy's steps in his blog, space was reclaimed. Each AG node looks like:


texts: Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

texts:
For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error, or it says it does not exist.
I've read restore table from .frm and .ibd file? that had similar problem to this, but I don't know how to check if innodb_file_per_table is enabled, and I'm overall just really confused. I also converted a copy of my mysql-bin.000002 file to a txt file so I see that the data from my database is not completely lost.
The database was created last year. I have 6 of those mysql-bin.00000 files, but for some reason the .000002 is the largest. Right now, I have the .ibd and .frm files for all my databases, but I'm at a loss as to how I can restore it back into MySQL, or at least into something I can read.
I'm using WampServer 2.4 and MySQL 5.6.12 on Windows 2003 Server. Also, am I supposed to download a plugin in InnoDB?


texts:
I need to set up a good database clustering solution for a system that I am putting into production. I was hoping to get some advice from the experts as I am a primarily a developer and not a database administrator.

Background
My team and I are replacing a legacy system with a .NET-based system. Currently, we are doing parallel testing and slowly migrating users over to the new system; however, it strikes me that our current database solution is not adequate to handle the load once thigs really start going. Right now I have our entire database on one Dell PowerEdge Server running SQL Server 2008 R2 on it. Its been fine for development and for basic testing but when we decide to pull the plug on the old system and go forth completely with the new system we will need things like failover, load balancing, redundancy, etc. I have never had to implement a cluster before because everywhere I worked this was already implemented, and was not my responsibility to maintain. Since we are implementing the PC-based system from scratch I am looking for a starting point.

Recommendations
What do you recommend for my current situation: We have about 50 internal users that will be doing everything from data entry, claims processing, and the like, and the possibility of about 1500-2000 concurrent connections from outside. Is it better to do a virtual or physical setup? Fibre Channel or SATA?


texts: PostgreSQL Size Quota on Table or Schema

texts:
How do one limit the size of a PostgreSQL table? (or schema)

Limit in either bytes or rows would be OK.

I'm starting to think that there's no easy and obvious solution.

I have a lot of identical tables in multiple schemas (my take on multi-tenancy) and I would like to restrict each schema to a certain max size.

I have yet to find anything in Postgres that allows me to turn on any kind of quota. So I suppose you need to build this functionality yourself.

The naive 'solution' would be to do something like:

insert only if select count(*) < max quota.

But that does not feel right.

Anyone with better solutions in mind?


texts:
EER Diagram

Question

List the customer number, customer name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order cost (product quantity * product unit price) for telephone orders (A telephone order has an associated employee) placed on January 23, 2007 and the order cost exceeds $150. Order the result by the order
cost in descending order.

My Code

SELECT
    Customer.CustNo,
    Customer.FirstName,
    Customer.LastName,
    `Order`.OrderNo,
    `Order`.OrderDate,
    Employee.EmpNo,
    Employee.FirstName,
    Employee.LastName,
    Product.ProdNo,
    Product.ProdName,
    CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS `Order Cost`
From Customer, `Order`, Employee, Product, ProductInOrder
WHERE
    `Order`.Employee_EmpNo IS NOT NULL                          AND
    STR_TO_DATE(`Order`.OrderDate, '%m/%d/%Y') = '2007-1-23'    AND
    ProductInOrder.Qty * SUBSTRING(Product.Price, 2) > 150
ORDER BY `Order Cost` DESC;

Result

14:57:29    SELECT  Customer.CustNo,     Customer.FirstName,     Customer.LastName,     Order.OrderNo,     Order.OrderDate,     Employee.EmpNo,     Employee.FirstName,     Employee.LastName,     Product.ProdNo,     Product.ProdName,     CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS Order Cost From Customer, Order, Employee, Product, ProductInOrder WHERE  Order.Employee_EmpNo IS NOT NULL       AND     STR_TO_DATE(Order.OrderDate, '%m/%d/%Y') = '2007-1-23' AND     ProductInOrder.Qty * SUBSTRING(Product.Price, 2) > 150 ORDER BY Order Cost DESC LIMIT 0, 1000   1000 row(s) returned    0.290 sec / 0.011 sec

My Question

I think there is something wrong with the code From Customer, Order, Employee, Product, ProductInOrder. How to join all the tables together? If I use INNER JOIN, it will be more complicated.

Thank you for your help.


texts: SQL Server Query performance with window function

texts:
We write a query that include unpivot, partition by and order by.Query is:

SELECT PersonId
    ,SalaryDate
    ,ID
    ,Type
    ,SalaryValue
    ,ROW_NUMBER() OVER (
        PARTITION BY PersonId ORDER BY SalaryValue
        ) AS rn
FROM (
    SELECT lp.PersonId
        ,lp.SalaryDate
        ,lp.Salary1
        ,lp.Salary2
        ,lp.Salary3
        ,lp.ID
    FROM rdd.Salaries AS lp WITH (NOLOCK)
    WHERE lp.SalaryDate > DATEADD(day, - 31, getdate())
    ) AS t
unpivot(SalaryValue FOR Type IN (
            lp.Salary1
            ,lp.Salary2
            ,lp.Salary3
            )) AS UnpivotTable

The query returns about 68.000.000 rows and execution time is 20 minutes.
Can I improve the query's performance or rewrite effectively? What is the alternative of partition by?


texts: Invalid data types in restore headeronly resultset

texts:
I'm trying to write a SQL Server script to iterate through .bak files in a directory and then restore them to our server. In doing so, I've created three temp tables: #Files to keep track of the file-list returned by running xp_dirtree, #HeaderInfo to hold data returned when querying restore headeronly to get the database names and #FileListInfo to hold data returned from querying restore filelistonly to get the logical file names.

My question is regarding the #HeaderInfo table. Consulting the MSDN definition of the resultset returned from restore header only, I find that the fifth column (Compressed) and the last column (CompressedBackupSize) have 'invalid data types' (BYTE(1) and uint64, respectively). This, obviously, gives me an error when I try to execute the query. To get around this, I have used tinyint and bigint, respectively, and the code now runs fine.

My question(s) is/are this/these:

Is using tinyint/bigint the 'correct' work around for this? Or is there a better way to do it?
Is using them likely to cause any undesired behaviour?
If SQL is expecting BYTE(1) and uint64s, why does using different data types not cause an error?
And why does MSDN specify BYTE(1) and uint64 if they're not what gets returned? What are these used for and where?
Bonus question, for anyone who's interested, is there a more elegant/efficient way of automating a restore script?

Many thanks

EDIT: SQL Server 2008


texts:
Version - SQL Server 2012 SP1 CU3.

I have been using sys.dm_os_volume_stats for a while and it was working. Recently I have not been able to return any result from this DMF on one SQL Server 2012 instance.

e.g.

SELECT * FROM sys.dm_os_volume_stats(1, 1);

I have no issues with the same code on other SQL Server 2012 or SQL Server 2008 R2 instances. Does anyone know the reason behind and has fixed this problem?


texts: Cannot connect to database remotely (not even locally when using ip address)

texts:
I can login using this command:
sqlplus test/test@127.0.0.1:1521/local
But not this one: (where 192.168.0.240) is my IP
sqlplus test/test@192.168.0.240:1521/local

I am absolutely sure that my ip address (server's adress) is 192.168.0.240. So why am I getting the following error? ORA-12541: TNS:no listener

I get a connection timeout when trying to connect from other computers.

lsnrctl statusgives me the following:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                23-OCT-2013 15:27:35
Uptime                    0 days 21 hr. 37 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\pc2\product\11.2.0\dbhome_2\network\admin\liste
ner.ora
Listener Log File         c:\app\pc2\diag\tnslsnr\pc2-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "local" has 1 instance(s).
  Instance "local", status READY, has 1 handler(s) for this service...
Service "localXDB" has 1 instance(s).
  Instance "local", status READY, has 1 handler(s) for this service...
The command completed successfully


texts: SQL Server (2012) grant right to regular user to set database in read-only mode

texts:
i would like to grant right to specific regular users on an sql server 2012 the right to set the database from read-write mode into read-only mode and vice versa. but i would not like to grant them any other administrative privileges.

how do i achieve that? do i have to create a stored procedure or a new database schema? or is there a schema that incoorporates that already?

the code needed to set the database into read-only mode goes as follows:

ALTER DATABASE database-name SET READ_ONLY

or

ALTER DATABASE database-name SET READ_WRITE

cheerio :)


texts: What permissions does HAS_DBACCESS check?

texts:
I assume that

SELECT HAS_DBACCESS('database name')

is equivalent to

SELECT HAS_PERMS_BY_NAME('database name', 'DATABASE', 'permission name')

for some permission (or set of permissions, the results of which need anding together).

Is this assumption correct? If so what are the permissions?


texts: Performance difference between Text and Varchar in Mysql

texts:
I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table (
  pk int(11) NOT NULL AUTO_INCREMENT,
  varc varchar(255) DEFAULT NULL,
  txt text,
  PRIMARY KEY (pk)
) ENGINE=InnoDB AUTO_INCREMENT=103925 DEFAULT CHARSET=utf8

The table has around 100k rows.

I have executed the below queries and profiled the both

Query 1 (Group by on varchar column)

select varc,count(*) from small_table group by varc;

Profile 1 result

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000064 |
| checking permissions | 0.000004 |
| Opening tables       | 0.004812 |
| init                 | 0.000026 |
| System lock          | 0.000005 |
| optimizing           | 0.000006 |
| statistics           | 0.000010 |
| preparing            | 0.000006 |
| Creating tmp table   | 0.000021 |
| Sorting result       | 0.000003 |
| executing            | 0.000001 |
| Sending data         | 0.052795 |
| Creating sort index  | 0.000049 |
| end                  | 0.000003 |
| query end            | 0.000006 |
| removing tmp table   | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000006 |
| freeing items        | 0.000021 |
| cleaning up          | 0.000018 |
+----------------------+----------+

Temp table Details 1

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Query 2 (Group by on text column)

select txt,count(*) from small_table group by txt;

Profile 2 result

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000134 |
| checking permissions |  0.000010 |
| Opening tables       |  0.006690 |
| init                 |  0.000034 |
| System lock          |  0.000007 |
| optimizing           |  0.000004 |
| statistics           |  0.000017 |
| preparing            |  0.000008 |
| Creating tmp table   |  0.000027 |
| Sorting result       |  0.000005 |
| executing            |  0.000002 |
| Sending data         | 26.359877 |
| Creating sort index  |  0.001042 |
| end                  |  0.000007 |
| query end            |  0.000007 |
| removing tmp table   |  0.000208 |
| query end            |  0.000003 |
| closing tables       |  0.000007 |
| freeing items        |  0.000028 |
| cleaning up          |  0.000020 |
+----------------------+-----------+

Temp table Details 2

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Both profiles took almost same time in each stage expect 'Sending data stage'. For first one it took 0.052795s and for second one it took 26.359877s. Why there is that much difference in that stage? (same queries without group by clause show no difference in 'Sending data stage')


texts: SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

texts:
I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks.
So with internet searching (MSDN, mssqltips...) I oriented my config toward "Distributed Groups".
My configuration is: AG1 (2 SQL Server with 2016) and AG2 (2 SQL Server with 2019).

I do the whole tutorial from mssqltips, Failover and then I have every Databases names with Warning (Image for 2019) but nothing imported.. why? I didn't find answer.
By the way ... The workaround is to install same SQL Server 2016 on the new windows 2019.. Do the all process with Failover throught the Distributed Group and then ... Install manually SQL Server 2019 on the windows 2019. I find it weird that we have these steps and try to understand if I missed something or whatever?
Both editions I used are Developer.


texts: Using the partition function in the Where clause

texts:
I've been asked to test out performance of a database (a test system) after implementing horizontal partitioning.  The database is on SQL Server 2008 r2.  I'm inheriting this from someone else and found something I didn't expect in some of their code.  In some cases, I've seen that adding WHERE $partition.archiving_Partition_function(MyTable.IsArchived) = 1 to the Where clause improves query performance.

(The client wants to "archive" old data by partitioning it into an "archive" partition.  They can't use dates, since some projects are considered old after different time intervals.  So they want to manually flip a bit to archive it.)

So, I have a handful of tables on which I have modified the clustered index in order to partition the table.  (This is my first go-around with partitioning, so bear with me if my terminology is lacking.)  And I've been performance tuning several queries, often adding something like Where MyTable.IsArchived = CONVERT(BIT,0) in order to restrict to a single partition.  However, some queries perform better when I replace MyTable.IsArchived = CONVERT(BIT,0) with $partition.archiving_Partition_function(MyTable.IsArchived) = 1.  This surprises me.  My expectation is that the latter simply passes MyTable.IsArchived to the partition function and compares the result to an integer constant.  It isn't sargable and doesn't look like it would help determine what index is best.  It looks like it simply adds some overhead by calling a function, whereas the former code does the same thing... Compare the bit value to some constant.  Except that the former code follows the guidelines to help use the clustered index.

So, I guess my question is, "What's going on under the covers with $partition.MyPartitionFunction?  It looks to me like unnecessary overhead, but sometimes performs better than checking the partition column directly.  And I can't seem to find any documentation that sheds light on this.

Thanks.


texts: Create/Update Test Database duplicated timely from Production database on same server

texts:
I want to create a test environment database that is refreshed from the production database every month or as needed (they would both be on the same server). It would not just be data refreshing because if I make changes to the schema I would like that to be updated as well. What would be the best way?
I have tried some powershell commands but most of them are for one time duplications  like this. If powershell is the way to go I would like it to look for that database and if it exists I would like it to update.


texts: Get nested procedure and sql execution oracle

texts:
In my Oracle 11g environment, I got performance issue with a procedure named : myProc().
In myProc() it's called to others proc named myChildProcA(), myChildProcB(),myChildProcC()......( ~10 child procedures)
In Postgresql, I have some userful tool like pg_stat_user_functions, pg_stat_statements, auto_explain...
it's provide detail information about child function excutions.
But I don't known much about Oracle.
I tried with v$sql, sql_trace and tkprof to get statistic information but it's not provide child procedure information.
My question :
Are there any way or tool to get elapsed executions time for child procedures in Oracle?


texts: Storing a transaction sensitive constant in Firebird

texts: I'd like to store a number in my Firebird database that indicates the revision of its structure. The number is supposed to be updated only when the structure has successfully been altered by some migration script. Therefore the storage mechanism needs to be transaction sensitive, which is why I cannot use a generator. The alternative I considered was to define a stored procedure which returns the number, and alter its definition in a transaction, but that as well seems to survive a rollback. What options do I have, not using a table?


texts: How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column

texts:
I have a query that is returning all RATINGS for STORES and a RATING_DATE. There isn't a fixed amount of ratings, it could be from one to unlimited rows that are returns that have the same STORE_ID value.

I'm trying to find the min, max, and difference for the STORE_ID, like in the example below:

Example data:

STORE_ID RATING_DATE    RATING
-------- -----------    ------
34       2015-01-20     4.0
34       2016-01-01     4.14
34       2017-01-01     4.56
34       2018-01-01     4.78
46       2015-01-01     3.5
46       2017-01-01     3.7
46       2016-01-01     3.95

Desired results:

STORE_ID DIFFERENCE MIN  MAX
-------- ---------- ---  ----
34      .78         4.0  4.78
36      .45         3.5  3.95


texts: What data gets passed between mirroring principal and secondary?

texts:
We are trying to figure out which of the below statements is correct when talking about SQL Server mirroring:

The master database server executes the query, and takes the resulting transactions and ships them over to the secondary server, which "rolls in" the transactions into its database.
The master database server executes the query and simultaneously writes the query to the log. The log contents are sent to the slave and the queries are executed as they were on the primary.

Update: I unfortunately couldn't modify the two statements to avoid tampering. I think because the word "simultaneously" is used in the second statement it leads people away from the focus of question.

The main difference between the two statements is that in Statement 1, the transaction log that is sent to the secondary includes every row change action (insert/update/delete) that RESULTED from the query. In statement 2, the transaction log is actually only passing the query, and then executing it on the secondary. The key difference is, if Statement 2 is correct and you're updating millions of rows, it has little impact on how much data is passed (only the original query). However, if Statement 1 is correct, then updating millions of rows will require millions of rows of data to be sent to the secondary.


texts:
A day before I tried to back up our ddbb log file to azure storage. So, the transact is simple

backup [log|database] <name>
To URL = <Azure_Storage_url>

Credentials are defined apart,

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <Key>

CREATE CREDENTIAL <name_credentials>
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
    ,SECRET   = <SAS_Token>
go

I got the error below:

Msg 3201, Level 16, State 1, Line 34
Cannot open backup device <Azure_Storage_url>. Operating system error 50(The request is not supported.).

I tested that credentials with a bulk insert on the same container I was pointing with  it works fine.

The SAS token was generated with full permissions

No policy were made on the container since I understood (Is my fault here?) that SAS token already had full access.

How can I do a successful backup from scratch?

Thanks!


texts:
I'm using SQL Server Change Data Capture in a production database. To my surprise, I found that when restoring that database to a lower environment the CDC data is not kept—all the tables, functions, and so on are gone because SQL Server removes the CDC information during the recovery phase of a restore to any other database besides the one that originated the backup.

I learned that there is a RESTORE option, KEEP_CDC, that can be used in conjunction with the RECOVERY option (as the last step of a restore chain) to prevent the CDC information from being removed.

  Transact-SQL REFERENCE: RESTORE Arguments

  KEEP_CDC

  Supported by:  RESTORE

  KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.

Our organization uses NetBackup as its primary way to backup and restore databases. Getting ahold of a backup file outside of that is not something that will be easy to do and likely could not ever be routine. We have a web site that lets developers restore production backups to other environments without having to touch the files themselves.

Question: So what do I really need—to find out if there is a way in NetBackup 7.7 to specify additional restore options so that WITH_CDC can be passed to the RESTORE command? Or does NetBackup not use a true RESTORE statement? It seems like it would have to somehow use the built-in SQL Server restore functionality, otherwise it couldn't trigger the recovery phase of a restore properly. Or maybe I'm just out of my depth, here.

I can't find much information on NetBackup's abilities, and have been assured by a Senior Database Engineer that NetBackup 7.7 can't do this. However, I'm wondering how deeply he searched and whether someone perhaps more intimately familiar with the product might know of a way to hack such a thing in. Apparently version 8.0 is coming out soon-ish, but again I can't seem to find online a solid documentation source for how this might be accomplished. (It seems like "additional command-line arguments" could be possible, even if the option is not explicitly supported through a named switch or other setting.)


texts: SQL Server 2012 High Availability on VMware virtualization

texts:
We're going to host a large SQL Server 2012 under a VMware virtualization and we need it to be highly available.

These are the three options I found supported, but couldn't find a lot of info and comparison on who's better and why (and mostly - what's the disadvantages):

VMware HA - pro: cheap and easy to use and configure, just let the VM team deal with it. con: no HA for system upgrades, e.g. SQL upgrades, Windows services-packs\KBs etc.
SQL Server Failover Cluster (FCS) - pro: well known, supported, saves space (compared to AlwaysOn Availability Group), HA for system upgrades. con: I haven't heard of successful clusters over VM, or at least good ones. Heard it's a mess and to "stay away from it". A bit harder to admin, some problems may take days to figure out (no-one with a lot of knowledge).
SQL Server AlwaysOn Availability Group - pro: HA for everything! system, DB etc. con: New, added administering and hard to confiugre, new problems to deal with, double the space(!), "no strings attached" (to other servers).

That's pretty much it.

I would appreciate any info you may have about this, and would appreciate even more recommendations (hopefully based on test cases).

~Thanks.


texts: sql server: trigger to update in sum after update

texts:
I have 3 tables:

Artist = {artistname, startdate, members, genre}
Musician = {msin, firstname, lastname, birthdate}
Plays = {artistname, msin, share}

Key:

Artist: artistname
Musician: msin
Play: artistname (foreign key ref Artist), msin (foreign key ref Musician)

I need to create trigger on Plays:  The members attribute of the Artist table should always equal the number of musicians who are members of the artist. Any insertion, deletion, update in Plays triggers changes of members attribute in Musician.

So I did following:

create trigger memberAdd
on Plays
after insert
as
if exists ( select * from Plays p )
begin
    update Artist set members = members + 1 where artistname = (select artistname from inserted)
end
else
begin
    update Artist set members = 1 where artistname = (select artistname from inserted)
end

create trigger memberDel
on Plays
after delete
as
begin
    update Artist set members = members - 1 where artistname = (select artistname from deleted)
end

The problem is update. I can use deleted/inserted table to trigger changes. but for updating existing the data which table should I use? How can I the find the artistname?

create trigger memberUpdate
on Plays
after update
as
begin
    update Artist
    set members = (select sum(msin)
                    from Plays p
                    group by artistname
                    having artistname = (select artistname from ????))
end


texts: sp_helpdb -- returns nothing, because db is restoring (log shipping target) SQL Server

texts:
How can I see where the db files live, etc?

e.g. sp_helpdb output when sp_helpdb itself won't give results because db is in restoring state.

This is SQL Server 2014


texts: Remove duplicate records with special rules - Is it possible with a single query?

texts:
I have the following data in a table:
id    loc1    loc2    zip_code
------------------------------
10    1       null    12345
10    null    1       null

11    1       null    null
11    null    1       43210

12    1       null    54321
12    null    1       87654

13    1       null    null
13    null    1       null

14    1       null    65432
15    1       null    null
16    null    1       76767
17    null    1       null

The goal is to remove the duplicates with some special rules.
To accomplish this, I have the option to delete the "bad" records directly from my original table or copy the "good" records into a new table.
Rules:

Each record always has one location set. i.e. we either have loc1 or loc2 for each row.
The unique rows (id: 14-17) are good as they are
For the duplicate rows (id: 10-13), I want to choose only one row:

The row that has a zip_code
For all other cases, we must always choose the primary location. ie. the one that has loc1

After de-duplication, the final data should look like this:
id    loc1    loc2    zip_code
------------------------------
10    1       null    12345
11    null    1       43210
12    1       null    54321
13    1       null    null

14    1       null    65432
15    1       null    null
16    null    1       76767
17    null    1       null


texts: Data dictionary best practices in SQL Server 2008 r2

texts:
We are interested in sharing the meta data and data dictionary among the team. I know that we can use the Extended Properties for this purpose, but based on my experience I've seen it gets out of date easily, because team members tend to forget to update them or skip this step.

I'm wondering if there is a more convenient way to create the data dictionary which can be maintained with the least amount of effort and time.

Thank you.


texts: Is There a Single-File Based Graph Database Management System?

texts:
I am considering using a graph database management system (DBMS for brevity) as an application file format for my single-user desktop application.

Is there a single-file based graph DBMS (the "equivalent" of SQLite for graph DBMSs)?


texts: Understanding MongoDB's contra-indications for read preferences

texts:
I'm very new to MongoDB and am reading the manual to familiarize myself with it. Would appreciate some help in understanding some points related to read preferences.

  In general, do not use secondary and secondaryPreferred to provide
  extra capacity for reads, because:

  All members of a replica have roughly equivalent write traffic; as a
  result, secondaries will service reads at roughly the same rate as
  the primary.
  Distributing read operations to secondaries can compromise
  availability if any members of the set become unavailable because
  the remaining members of the set will need to be able to handle all
  application requests.

I don't agree with point no. 1, and I don't understand point no. 2. If I have three members in my replica set, then it seems like common sense that read traffic can be made one-third by distributing it evenly, provided I don't care much about data freshness. As for point no. 2, isn't that the failover situation that MongoDB is fundamentally good at solving? I mean, if the secondary dies, does the client lose all capacity to read? As I see it, there should be a new secondary should be automatically elected to replace it (and if there are none, the load should transfer to the primary).

Please help explain!


texts: mongos is not listen when configured for sharded architecture (version 3.4)

texts:
Deal all,
I am trying to mount a sharded mongodb server. I configured a replica set, and a configure replicat set. Both replicate are composd from 3 servers

root     23792     1  0 14:43 ?        00:01:55 mongod --replSet rs01 --journal --port 27102 --shardsvr --dbpath /mongo/rs01i01/ --logpath /mongo/rs01i01/mongo_102.log --fork
root     23820     1  0 14:44 ?        00:01:56 mongod --replSet rs01 --journal --port 27103 --shardsvr --dbpath /mongo/rs01i02/ --logpath /mongo/rs01i02/mongo_103.log --fork
root     23852     1  0 14:45 ?        00:01:21 mongod --replSet rs01 --journal --port 27101 --shardsvr --dbpath /mongo/rs01iar/ --logpath /mongo/rs01iar/mongo_101.log --fork
root     25240     1  0 16:18 ?        00:01:37 mongod --replSet conf --journal --port 27201 --dbpath /mongo/config --logpath /mongo/config/mongo_201.log --fork
root     25269     1  0 16:19 ?        00:01:37 mongod --replSet conf --journal --port 27202 --dbpath /mongo/config2 --logpath /mongo/config2/mongo_202.log --fork
root     25297     1  0 16:19 ?        00:01:38 mongod --replSet conf --journal --port 27203 --dbpath /mongo/config3 --logpath /mongo/config3/mongo_203.log --fork

However, when I try in the last step, to start mongo, the process is lunched  (3 times :O ) but is not listening on the corresponding port.

mongos --port 27301 --configdb conf/127.0.0.1:27201,127.0.0.1:27202,127.0.0.1:27203 --logpath /mongo/mongos/mongos_27301.log --fork
about to fork child process, waiting until server is ready for connections.
forked process: 9803

but, mongo does not connect on that port

mongo --port 27301
MongoDB shell version v3.4.10
connecting to: mongodb://127.0.0.1:27301/
2017-11-01T22:56:00.898+0000 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27301, in(checking socket for error after poll), reason: Connection refused
2017-11-01T22:56:00.899+0000 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27301, connection attempt failed :
connect@src/mongo/shell/mongo.js:237:13
@(connect):1:6
exception: connect failed

Please help


texts: Convert yymmdd to datetime using ssis derived column

texts:
Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have (DT_DATE)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2)), but it's not working.

470324
470324
470209
101
0


texts:
We have already setup Oracle replication using Dataguard from a primary active site to a near DR site. The near DR is a passive site.

Is it possible to replicate from the Near DR (Passive) site to Far DR site using dataguard?


texts: Could I use the SQL statement, SELECT * from sysobjects, with C# MySQL Connector - .NET?

texts:
I would like to port a SQL statement, SELECT * from sysobjects,  written for Microsoft SQL Server  to MySQL version 5.6.

Could I use the SQL statement,  SELECT * from sysobjects, with C# MySQL Connector - .NET or do I have to modify it?

I realize that I can get such a C# MySQL Connector - .NET to compile but that is no guarantee it can be executed correctly?

If I have to modify the SQL statement, how might I do it?
  Any help is greatly appreciated.


texts: What are the origins of the Identity Column?

texts: Who were the first creators of the Identity column? What problem were those creators originally trying to solve? Is there a story behind it?


texts: MySQL Enterprise Connector/PHP Plugin for php 5.4

texts:
I'm trying to setup mysqlenterprise.so. I have installed the native driver and loaded before mysqlent, but when I try to start it I got:

Starting php-fpm: [14-Jul-2015 14:27:40] NOTICE: PHP message: PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/mysqlenterprise.so' - /usr/lib64/php/modules/mysqlenterprise.so: undefined symbol: php_body_write in Unknown on line 0

I have googled and found the error undefined symbol: php_body_write in Unknown on line 0  for other php modules that got solved upgrading the module.

Is it possible to load this mysqlenterprise.so into php 5.4? Documentation says that i need a minimal version of 5.3.

So far I got:
Config files:

ls -la /etc/php.d/
total 96
drwxr-xr-x.  2 root root 4096 Jul 15 20:35 .
drwxr-xr-x. 87 root root 4096 Jul 15 18:21 ..
-rw-r--r--.  1 root root   49 Jun 15 09:46 curl.ini
(omitted output)
-rw-r--r--.  1 root root   55 Jun 15 09:46 mysqlnd.ini
-rw-r--r--.  1 root root   69 Jun 15 09:46 mysqlnd_mysqli.ini
-rw-r--r--.  1 root root   67 Jun 15 09:46 mysqlnd_mysql.ini
(omitted output)
-rw-r--r--.  1 root root   29 Jul 15 20:35 zzmysqlenterprise.ini

Loaded modules

ls /usr/lib64/php/modules/mysqlenterprise.so
/usr/lib64/php/modules/mysqlenterprise.so

Config file contents:

cat /etc/php.d/zzmysqlenterprise.ini
extension=mysqlenterprise.so

With @mim help now I know that php 5.3 vs 5.4 changed on output.c symbol php_body_write in favour to php_output_write (why? why broke like this the libs compat?)

https://github.com/php/php-src/blob/PHP-5.4.42/main/output.c
vs
https://github.com/php/php-src/blob/PHP-5.3.29/main/output.c

nm /usr/lib64/php/modules/mysqlenterprise.so | grep php
                 U _php_stream_free
                 U _php_stream_get_line
                 U _php_stream_read
                 U _php_stream_write
                 U _php_stream_xport_create
                 U php_base64_encode
                 U php_body_write
                 U php_error_docref0
                 U php_info_print_table_end
                 U php_info_print_table_header
                 U php_info_print_table_row
                 U php_info_print_table_start
0000000000006d42 t php_mysqlenterprise_conn_change_user_pub
0000000000006e8c t php_mysqlenterprise_conn_close_pub
0000000000006e4a t php_mysqlenterprise_conn_end_psession_pub
0000000000006e6b t php_mysqlenterprise_conn_free_contents_pub
0000000000006cbd t php_mysqlenterprise_conn_kill_connection_pub
0000000000006c90 t php_mysqlenterprise_conn_ping_pub
0000000000006b76 t php_mysqlenterprise_conn_query_pub
0000000000006cf9 t php_mysqlenterprise_conn_select_db_pub
0000000000002659 t php_mysqlenterprise_init_globals
                 U php_sprintf
                 U php_stream_xport_get_name

I'm having trouble opening Oracle support case, but I'm suspecting is incompatible or there are newer versions from them.


texts: PostgreSQL not using partial index when using boolean in WHERE clause

texts:
I'm trying to create a partial index which includes rows based on a boolean value but in my tests neither rows with the value set to TRUE or FALSE are included in the index, or at least the index isn't being used.

Example schema

CREATE TABLE pitest
(
    id SERIAL,
    name TEXT,
    location TEXT,
    inactive BOOLEAN DEFAULT false NOT NULL
);

CREATE INDEX idx_name ON pitest (name) WHERE inactive IS NOT TRUE;

Example rows

id  |        name         |          location          | inactive
----+---------------------+----------------------------+----------
101 | Anne Lopez          | Bajiao                     | t
102 | Jane Garcia         | Sanhe                      | f

I've populated the table with 1000 rows of which 109 have inactive = TRUE and the remaining 891 have inactive = FALSE.

Queries that should and shouldn't match the WHERE clause in the index both result in a sequential scan:

# EXPLAIN SELECT location FROM pitest WHERE name = 'Anne Lopez';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on pitest  (cost=0.00..21.50 rows=1 width=9)
   Filter: (name = 'Anne Lopez'::text)

# EXPLAIN SELECT location FROM pitest WHERE name = 'Jane Garcia';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on pitest  (cost=0.00..21.50 rows=1 width=9)
   Filter: (name = 'Jane Garcia'::text)

I've tried this on 2 different servers, 9.3 and 9.4 and with a larger test table with >1.5m rows of which around 150,000 had inactive = TRUE.

I'm trying to minimise the amount of disk space used by indexes by only indexing users without the 'inactive' flag set, but don't want to have to have to specify this flag in queries. Is it possible to use partial indexes in this way? Thanks!


texts:
I am on Windows 8, my SSD got corrupted and I installed Windows on a new drive. I had before MySQL 5.6.12, now I installed 5.6.17 and tried to move the entire data dir. The location of the MySQL installation is the same as before. But when I start it I get the following error:

2015-02-23 17:44:44 12492 [Note] Plugin 'FEDERATED' is disabled.
wampmysqld64: Incorrect information in file: '.\mysql\plugin.frm'
2015-02-23 17:44:44 12492 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-02-23 17:44:44 12492 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-02-23 17:44:44 12492 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-23 17:44:44 12492 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2015-02-23 17:44:44 12492 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-02-23 17:44:44 12492 [Note] InnoDB: Not using CPU crc32 instructions
2015-02-23 17:44:44 12492 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-02-23 17:44:45 12492 [Note] InnoDB: Completed initialization of buffer pool
2015-02-23 17:44:45 12492 [Note] InnoDB: Restoring page 0 of tablespace 0
2015-02-23 17:44:45 12492 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
2015-02-23 17:44:45 12492 [ERROR] InnoDB: space header page consists of zero bytes in data file .\ibdata1
2015-02-23 17:44:45 12492 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-02-23 17:44:45 12492 [ERROR] Plugin 'InnoDB' init function returned error.
2015-02-23 17:44:45 12492 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-02-23 17:44:45 12492 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-02-23 17:44:45 12492 [ERROR] Aborting

mysql_upgrade fails with FATAL ERROR: Upgrade failed

Thank you very much for your time.


texts: What happens if SQL Server mirroring fails over and during synchronisation?

texts:
I have two sql boxes, one primary and one mirror.

The mirror stopped synchronising last night for about 12 hours and so now the primary is transferring about 25GB of logs that built up.

However it's taking a long time.

What happens if the primary falls over (for whatever reason) during this time? Presumably the mirror will take over as primary, but I don't know how much of the transaction logs have been transferred.


texts: Determining sizes for Separated Partitions of programs, data and logs in SQL Server

texts:
As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.

Question is, what is the recommended way to go on about that, especially with regards to how size should be divided between them? If you wanted to do that, how'd you set it?

Thanks


texts: How can I take a full backup from MySQL without any System tables?

texts:
We are downgrading our version of MariaDB, and are looking to take a full backup of all of the databases.  I do not want to include system tables since they would be incompatible between the two versions.  What would be the best way to accomplish this?

After reading through http://mysqlserverteam.com/creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/, it seems like everyone is saying that you have to manually exclude them, however I don't want to manually have to type in all of the system databases.  What is the best way to accomplish this?

Currently I have the following options -

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction --gtid --master-data --dump-slave --add-drop-database"

This is the current method I have tried -

SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

echo $SQL

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

Dblistfile includes all of my database names that I want to backup.  Is there a better way to do this?


texts: I am using SQL server 2017, I will like to know how Connection String can be use to talk via Listener to Databases rather talking directly to the DB


texts: SELECT every second row without auto_increment

texts:
How can I select every second row in MySQL, without using any data of the table or some auto_increment?
Something like

SELECT EVERY SECOND ROW FROM `table`;

Thank you!


texts: Postgres 10 and malformatted array literal when using EXECUTE

texts:
I am trying to create a function on Postgres 10 that would check whether all the given ids exist
in the target table.

CREATE OR REPLACE FUNCTION "public"."has_mismatching_ids"(
  _ids TEXT[],
  _column_name TEXT,
  _table_name TEXT,
  _schema_name TEXT DEFAULT 'public'
)
    RETURNS BOOLEAN AS
$$
DECLARE
  result TEXT[] := '{}';
  _query TEXT := '';
BEGIN
  IF _ids IS NULL THEN
      RETURN false;
  END IF;

  _query := CONCAT('SELECT UNNEST($1) EXCEPT SELECT "', _column_name, '" FROM "', _schema_name ,'"."', _table_name ,'"');
  EXECUTE _query INTO result USING $1;
  RAISE NOTICE 'Executed';

  IF array_length(result, 1) > 0 THEN
      RETURN true;
  END IF;

  RETURN false;
END;
$$
LANGUAGE plpgsql
    STABLE
    SECURITY INVOKER;

This is my function that returns correctly true if all the ids exist, but it throws an exception if any of
the ids does not exist.

CREATE TABLE "public"."categories" ("category_id" TEXT NOT NULL PRIMARY KEY);
INSERT INTO "public"."categories" ("category_id") VALUES ('foo');
INSERT INTO "public"."categories" ("category_id") VALUES ('bar');

This works

SELECT FROM "public"."has_mismatching_ids"(ARRAY['foo'], 'category_id', 'categories', 'public');
NOTICE:  Executed
 has_mismatching_ids
---------------------
 f
(1 row)

but this fails to even

SELECT FROM "public"."has_mismatching_ids"(ARRAY['foo2'], 'category_id', 'categories', 'public');
ERROR:  malformed array literal: "foo2"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  PL/pgSQL function public.has_mismatching_ids(text[],text,text,text) line 14 at EXECUTE

and strangely this works also if I write a function that uses explicit names and without EXECUTE

SELECT UNNEST(ARRAY['foo', 'foo3']) EXCEPT SELECT "category_id" FROM "public"."categories";
unnest
--------
 foo3
(1 row)

Why doesn't my dynamic function work when comparing to values that do not exist in the target table?


texts: Does deploying SSIS really required an azure data lake house when deploying on Azure database?. I am currently working on an SSIS project that its database is currently deployed on Azure and I am currently having an issue deploying it because it sends me directly in the "Integration Services Project or Packages" page and having this I think will require me to turn on Azure Data Lake Storage and this will cost us. is there anyway that I can deploy an SSIS package for free?