texts: Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table

texts:
I need to write a stored procedure to receive 4 numbers and insert them into a table. This is what I've developed so far:

Declare
@1  Int = 10,
@2  Int = 20,
@3  Int = 30,
@4  Int = 40

Create table #Temp(Num int)
Declare @I char(1) = 1

 While (@I <= 4)
   Begin

         Insert Into #Temp
         Select @I

     SET @I +=1
   end

Select * from #Temp
Drop table #Temp

I know that I can directly and statically insert the inputs into the table but I just want to know is there any better way to do that? I wanted to use a while statement but the problem is the numbers for (I) variable are being inserted into the table!! I mean the output is 1,2,3,4; what I want is 10,20,30,40.


texts: MySQL cpu won't go down after query; need restart

texts:
I have a query triggered towards a MySQL server. Every time I run such a query, the cpu goes up until the query ends. After that, cpu usage goes down, but not to normal value: a little cpu is still being consumed by MySQL. Only after restarting the server, cpu goes back to normal consumption.

In the next plot the pink area is cpu during the SQL query time. The dark-turquoise is a Pandas operation on the SQL table from before. Once done, cpu goes down but a little "pink" remains. After restart of the MySQL server, "pink" is hardly seen.

My query has some many joins. I'm using this query towards a MySQL 5.7.26-0ubuntu0.16.04.1. This DB is the one that comes out of the box when installing RackTables.

SELECT
ob.id,
ob.name,
ob.label,
INET_NTOA(ip.ip),
a.name,
d.dict_value,
av.string_value,
min(oh.ctime),
max(oh.ctime),
max(oh.user_name),
min(ol.date),
ol.user,
ol.content,
FROM Object AS ob
JOIN AttributeValue AS av ON (av.object_id  = ob.id) LEFT
JOIN Attribute      AS a  ON (av.attr_id    = a.id) LEFT
JOIN Dictionary     AS d  ON (av.uint_value = d.dict_key) LEFT
JOIN ObjectLog      AS ol ON (ob.id         = ol.object_id ) LEFT
JOIN ObjectHistory  AS oh ON (ob.id         = oh.id) LEFT
JOIN TagStorage     AS ts ON (ob.id         = ts.entity_id) LEFT
JOIN IPv4Allocation AS ip ON (ob.id         = ip.object_id AND ip.name = 'int0')
WHERE (a.name LIKE 'TEST%' OR a.name LIKE 'TRIP%')
GROUP BY ob.id, ob.name, ob.label, a.name, d.dict_value, av.uint_value, av.string_value, ol.user, ol.content, ip.ip

While running the query, I've issued a show processlist and obtained the following:

mysql> show processlist;
+-----+---------+-----------------+------------+---------+------+--------------+---------------------------------------------------------+
| Id  | User    | Host            | db         | Command | Time | State        | Info                                                    |
+-----+---------+-----------------+------------+---------+------+--------------+---------------------------------------------------------+
|   3 | netdata | localhost       | NULL       | Sleep   |    1 |              | NULL                                                    |
| 372 | root    | localhost       | NULL       | Query   |    0 | starting     | show processlist                                        |
| 377 | myUser  | localhost:42308 | racktables | Query   |   33 | Sending data | SELECT  ob.id,  ob.name,   ob.label,  INET_NTOA(ip.ip)  |
+-----+---------+-----------------+------------+---------+------+--------------+---------------------------------------------------------+

The Id 377 goes away, once the query has ended.

This is the content of my.cnf

$ grep -v '#' /etc/mysql/my.cnf
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

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

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 0.0.0.0
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 1G

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer_size     = 16M

Any hint? May be some tunning is needed on the server ...

Thanks!


texts: table partition in order to solve frequent deadlock issues - how would I know if it will work?

texts:
I use sql server 2005 enterprise edition and have a subscriber DB  - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS packages read from it and import data into SSAS.

This DB has over 800 GB, some tables have hundreds of millions of records.

All these reports and packages need to read data using READ COMMITTED isolation level.

As this DB is a subscriber DB and the publisher is very busy, replication keeps updating it all the time - whenever the publisher DB is updated.

Sometimes the replication procedures require exclusive locks on tables (or pages or records),
and it might be that the reports or packages are running from those tables (or pages or records) and as a result DEADLOCKS are more frequent than what I would like to admit.

Example of a replication procedure involved in a deadlock today:

create procedure [sp_MSdel_dbotblBOrder]
        @pkc1 varchar(20)
as
begin
    delete [dbo].[tblBOrder]
where [strBxOrderNo] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end

Just for illustration, I will post below the process that was deadlocked against the procedure above: (it is a monster - and it should be at least a procedure but it is an AD HOC query at the moment)

I could not post the process here, because it is over 30,000 lines.

Anyway, I have been thinking about PARTITION these tables, one by one, starting with the ORDERS table, which is the one that causes most of the DEADLOCKS.

Other than create missing indexes, I just cannot tune all the processes because there are too many.

I believe that my partitioning the orders table (and all of its indexes)
I can get a better management of LOCKS, and quicker updates from the replication agents.

I would partition the order table by DAY OF THE WEEK.
I would have to create another 6 different filegroups.
I have one single drive where I can put the data.

What can I monitor in order to guarantee that this project would work?
By work I mean - eliminate or at least reduce a lot of the deadlocks.

How would I know that partition by day of the week is the best way to partition the order table?
I could partition by month, but I believe the chunks would still be too big.

I could partition the previous years in an Archive partition, and the current year partition by day of the week.

How would I know that this is the best choice over enabling SNAPSHOT ISOLATION level for this database?


texts: User guest does not have permission to run DBCC checkprimaryfile (SMSS)

texts:
I am trying to attach an mdf file to a sql server using SMSS, and I got a message that says:

User guest does not have permission to run DBCC checkprimaryfile

Any clues about how can I solve that Issue? I can add more info about the details


texts:
If I want to export all the tables in a schema, can I just specify the schema parameter in the parfile or do I also need to list out all the tables individually in the tables parameters? I tried specifying just the schema parameter but the export keeps failing, so I want to confirm whether there is another mistake I am making or if it's because you need to explicitly specify the tables that you want to export.

Thanks!


texts: Separate tables or partition one huge table?

texts:
I'm building a rather large table that includes a particular column, my_column.  There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same number of rows associated with it.  Furthermore, it is likely that queries will only be focused on a single, specific value of my_column at a time.  I see that one option would be to partition this large table based upon the values of my_column.  The other option as I see it would be to create a separate table entirely such that each table associates with exactly one value of my_column.  I'd like to know what would lead me to pick one option over the other.

As far as I understand it, one large table that is partitioned seems like the "correct" or "pure" way to do it, as that seems to be a primary use-case for partitions.  However, I suspect that for practical reasons the second option is better, and that's what I'm leaning towards.  I don't know if it matters, but I'm dealing with MyISAM tables.


texts: Select N rows before/after specified row

texts:
Let's say I have a simple table in Postgres (11.3):

create table posts
(
    id serial not null,
    created_at timestamp(0)
    constraint posts_pkey
        primary key (id)
);

If a user requests id=5869, I need to be able to return the N rows before and the N rows after that row in a query that is ordered by the created_at column. If we're able to assume that the larger the id, the larger the created_at, we can do something relatively simple like this:

(select * from posts where id < 5869 order by id limit 10)
union all
(select * from posts where id >= 5869 order by id limit 11);

However, I am unable to assume that the higher id is most recently created and I'm wondering what the best way to retrieve that data is in that case. This method works, but is remarkably slow on a 100k row dataset:

WITH
  boundaries AS (
    SELECT *,
           row_number() OVER (ORDER BY created_at DESC) AS rownum
    FROM posts
  ),
  target_boundary AS (
     SELECT *
     FROM boundaries
     WHERE boundaries.id = 5869
 )
SELECT posts.*, boundaries.rownum
FROM posts
LEFT JOIN boundaries ON posts.id = boundaries.id
JOIN target_boundary ON boundaries.rownum BETWEEN target_boundary.rownum - 10 AND target_boundary.rownum + 10

Running through that was taking upwards of 800 milliseconds which is far too slow on a dataset so small.

I have also tried a variation of the above using lead() and lag(), but that was even less efficient.

Is there a better way to do this query? Is there perhaps a window function I am missing in Postgres that would handle it?


texts: Best way to prevent lock wait timeout exceeded try restarting transaction

texts:
I'm not totally savvy about databases, I just know the basics. We encountered this problem while trying to query something from our database. This query occurs every 10 seconds. This is the first I encountered this problem so I want to know a preventive measure.
I am running a MariaDB (10.5.4) Galera Cluster and as I was reading this guide, I am not sure if I should apply the innodb_rollback_on_timeout=1 on my.cnf would turning this on remove the query thus skipping it entirely?
Also I have set innodb_lock_wait_timeout=8000 on my cnf as this solution did.
Are there any more improvements I should do to prevent future problems for my database? This is my first Galera Setup which we use for development and QA, so I don't want to mess this up.


texts: How does MySQL return a result set from a stored procedure?

texts:
I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that procedure's result set. Unfortunately though, it seems there are references in the MySQL docs that say a procedure can return multiple result sets. How is this behavior triggered? How can I tell the MySQL server that I explicitly want only one result set returned?

(For instance, I have a query that does a SELECT and a few inserts. I don't want to tell clients about the inserts, but I do want the client to be given the SELECT's result set....)


texts: Log in as another SQL Server user via SSMS to check their permissions?

texts:
I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question:

Once I have given a set of permissions to an AD authenticated user via SSMS, is there a way I can 'log in' as that user to the SQL server to check and confirm that I've given them the correct privileges? I'm also logged in as an AD user with my own permissions so I wouldn't be able to log in as their AD account ID/password.

Thanks :)


texts: Merge replication with PDAs AND P2P replication between servers?

texts:
We're currently building a setup to scan tickets at an event. We plan on having 2 servers at each entrance but can't have a local link between the two.

We have scanners (sqlCE) and half of them are subscribed equally to one of the two server with merge replication, and we thought about replicating the two servers together using peer-to-peer replication. Is this possible or we should use merge replication all the way?

Heres an ugly diagram:


texts:
I have setup an oracle database in my Ubuntu machine. Now I have took the following steps to create tables

1) login to my ubuntu account

2) su oracle

3) sqlplus "sys as sysdba"
//Connected to an idle instance

4) sql prompt sql>startup

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

5) in sql prompt.. sql> exec dbms_xdb_config.sethttpport(8080);

6) sql> exit

7)lsnrctl start

Then I have login to oracle apex in a workspace which I created earlier and created a table named  person

but when I again login to to sqlplus like sqlplus "sys as sysdba" and run the following query

select * from person
It says , no table created.

I am a newbie in database , so please tell me how do I access the tables I have created in apex , from sqlplus console ? It used to be so simple in 10g.


texts: Sudden performance decrease in SQL Server

texts:
I've got a problem with a SQL Server 2008 R2 instance. Last week the performance suddenly broke down. All inserts are very very slow. It takes 4-5 times longer than before. On the hardware we found no defects. We had the same problem a month ago, but after one week the problem disappeared as fast as it came.

I'm not an expert on these things but I noted that the performance on all databases on the instance is poor. CPU and Disks are not at their limits, so it shouldn't be a hardware performance issue.

I'll took a look at the cached pages count with this query:

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Here I found very high values, but I don't know if it's bad or not. The count is from 201.236 to 1 on the different tables an indexes. Could this be an issue of the poor performance?

Another thing I noted is a very high number of log writes while inserting data. With SELECT INTO ... it's much faster.

Can anyone give me a hint, or can tell me what this cached_pages_count exactly means?

edit:

I also mentioned high waititme during the inserts (up to 300.000 ms) Waittype is CXPACKET and wait ressource is e_waitPortOpen and e_waitPipeNewRow. The high values occur on e_waitPortOpen.

kind regards
Thomas


texts:
I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the user. I logged in as postgres user
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE test_db TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

CREATE USER readonlyuser WITH PASSWORD 'read123';
grant readonly  to readonlyuser;

Now I can login as user readonlyuser but I can't read data from any tables. I get error SQL Error [42501]: ERROR: permission denied for table.
Any help would be appreciated.


texts: Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down

texts:
I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections?

I always flush the status and bring down this value to avoid DB connection refused errors and warning messages in the error log:

=====
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
=======

mysql> select version();show variables like "%max_connections%";show global status
         like "%Max_used%";show status like "%thread%";
            +--------------+
            | version()    |
            +--------------+
            | 5.1.52-2-log |
            +--------------+
            1 row in set (0.00 sec)

            +-----------------+-------+
            | Variable_name   | Value |
            +-----------------+-------+
            | max_connections | 150   |
            +-----------------+-------+
             1 row in set (0.00 sec)

            +----------------------+-------+
            | Variable_name        | Value |
            +----------------------+-------+
            | Max_used_connections | 151   |
            +----------------------+-------+
            1 row in set (0.00 sec)

            +----------------------------+-------+
            | Variable_name              | Value |
            +----------------------------+-------+
            | Com_show_thread_statistics | 0     |
            | Delayed_insert_threads     | 0     |
            | Slow_launch_threads        | 0     |
            | Threads_cached             | 89    |
            | Threads_connected          | 2     |
            | Threads_created            | 1344  |
            | Threads_running            | 2     |
            +----------------------------+-------+
            7 rows in set (0.00 sec)

mysql> show variables like "%thread_cache%";
            +-------------------+-------+
            | Variable_name     | Value |
            +-------------------+-------+
            | thread_cache_size | 90    |
            +-------------------+-------+
            1 row in set (0.00 sec)

Here is the Aborted Connections Status:

mysql> show global status like "%Aborted%";
            +------------------+----------+
            | Variable_name    | Value    |
            +------------------+----------+
            | Aborted_clients  | 119      |
            | Aborted_connects | 43855304 |
            +------------------+----------+
            2 rows in set (0.00 sec)

I observed in some scenarios that the max_used_connections value comes down dynamically, once the threads_connected falls down.
Is this only an indication variable to increase the max_connections? Posting this query for better understanding. Any detailed explanations please?

I read somewhere thread_cache_size should be greater than max_connections, what will be the impact if it is less ?

Max_used_connections

  The maximum number of connections that have been in use simultaneously since the server started.


texts: PostgreSQL - SSL connection without providing the root-ca certificate?

texts:
I set the following parameters to enable SSL on my PostgreSQL server:

ssl = True
ssl_ca_file = 'path-to-root-ca'
ssl_cert_file = 'path-to-server-crt'
ssl_key_file = 'path-to-server-key'

For my tests, I use the PGSSLROOTCERT variable which contains the path to the root ca certificate.

I realized that it is still possible for a client to connect to the database without providing the root CA certificate and that the prompt states that an SSL connection is used:

psql "postgres://user:XXXXXX@hostname:port/postgres"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

If the PGSSLROOTCERT variable is set to a wrong value:

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql: SSL error: certificate verify failed

The pg_hba.conf file:

local  all  postgres    trust
hostssl  all  all  0.0.0.0/0  md5


texts: how can Audit All Login To SQL Server BY ODBC Connection

texts:
I MAke Report By Access Form On SQL database

I make ODBC Connection For login To Update the data From Database

Now I want Audit All logins To know who make Update today Or who make Update Now ?

Any One Help ME


texts: Getting Only What Changed from SQL Server 2005

texts:
We have a 17 store system which are on SQL Server 2005 Standard Edition and on Windows 2003 server. We currently have the following process:

At each store we have an SQL job which runs a procedure to populate 1 table. Procedure basically first Truncates the table and then does the Insert from the system. This has records from day our store was open till now, approximately 2million records in this table. There is no way to archive it.
We then have an SSIS package which gets this table data from store's back to Home Office every night. It runs for 4-7hours depending upon connectivity, which is slow most of the time.

We would like to do the following:

We would want to create a separate table at the store, which would record ONLY the changes occurred to table its populating every night (above Step-1)
This way, when SSIS package pull the data, it doesn't have to pull back 2million records, instead it can only pull back the changes

Due to SQL Server 2005 we cannot use MERGE command and as table gets Truncated and Inserted is not the good candiate for replication. How should we get ONLY change back to the home office?

Thank you,
Hiren Patel


texts: Oracle Materialized View Error Log Table?

texts: I have a number of materialized views that for various reasons on (rare) occasion will fail to materialize. Is there a database table that stores the error messages from the refresh attempts that I can query?


texts:
I have a linked table id_group | id_user.  Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the application works.

From time to time some groups lose all the users they had, except the new ones, which are inserted.

The primary index of the table is the pair id_group - id_user.

I cannot reproduce this through a query, but the client has complained multiple times and there's an issue no doubt.

I could explain this if the table was innoDB with the DELETE locking the index and the INSERT not being able to add that pair again, however this is a myISAM table and myISAM locks the entire table during DELETE statements.


texts: SQL Server 2008 Maintenance Plan Failure

texts:
Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task.

The details are:

Connection = Local server connection
Databases = Specific databases (just one)
Object = Tables and Views
Free space options = I have tried both default amount and change percentage to 70%
Advanced options = Keep index online while reindexing is the only one checked.  This is the primary reason I wanted to use this was to see if we could keep an index online while still doing the rebuild.

When I run the T-SQL from this manually everything works ok, but I assume it is not keeping the index online.  Whenever I run the job though I get this very strange error that has not led me anywhere:

  Executing the query "ALTER INDEX [Agreement_PK] ON [dbo].[Agreement]
   RE..." failed with the following error: "String or binary data would
   be truncated. The statement has been terminated.". Possible failure
   reasons: Problems with the query, "ResultSet" property not set
   correctly, parameters not set correctly, or connection not established
   correctly.

USE [<dbase>]
GO
ALTER INDEX [Agreement_PK] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF )
GO
USE [<dbase>]
GO
ALTER INDEX [IX_Agreement_1] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF )
GO

The T-SQL is very generic and gives no indication as to what isn't set correctly or why such a simple local connection would be a problem.  EDIT: Here is that code from my comment.

It is set to use Windows NT Integrated security, but I am not sure why that would be a problem again on a local system connection.


texts: remote postgres user logon with certificate

texts: I would like to have each remote user logon using an SSL certificate and use their postgres logon role to better track their use of the system. I can set up a certificate for a given user per the documentation. However, there are many users. Is there a method for configuration using a single certificate, bound to a specific database, that would allow the various users roles to logon?


texts:
I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly.

They both have the same objects unshared for tables, however, sequences are shared, and dev is the owner. I just use normal create statements.

I want to grant all to junit for the sequence but this fails with:

dev=> grant all on sequence serial to junit;
WARNING:  no privileges were granted for "serial"
GRANT

After this junit has no select rights on the sequence, how can I fix this?

I also would like to know whether there are recipes for this kind of setups, where I can use the same DDL for the two different users without modifying the script? I've been looking into schemas but I haven't found the solution so far.


texts: How to denote separate records as "one and the same"?

texts:
Let's say there are two separate Person records

V. Baker
Vincent Baker

each with their own various associations. These could be things like photos, contact information, etc.

And let's say that it turns out these are the same person. What's the best way to figuratively "combine" them so that it's possible to access either record and also pull in the direct and associated content from the other record/s as well? I say "figuratively" because they'll need to remain separate so that each record's unique data will be preserved.

Create a one-to-many self-referencing relationship where one Person can have one or more People?
Create a separate column with a unique identifier? And when two or more records are deemed to be the same, simply give them all the same key?
Or some other technique I don't know about yet?

My concern, though, is that whatever solution I implement, there's a good chance it will need to be transitive. So if A is noted as being the same as B, if a new C comes along and is noted as being the same as B, this should also result in C being noted the same as the original A.

Thank you in advance for any help and insights!


texts: SQL Server Linked Server performance: Why are remote queries so expensive?

texts:
I have two database servers, connected via Linked Servers. Both are SQL Server 2008R2 databases, and the linked server connection is made via a regular "SQL Server" link, using the current login's security context. The linked servers are both in the same datacentre, so the connection shouldn't be an issue.

I use the following query to check which values of the column identifier are available remotely, but not locally.

SELECT
    identifier
FROM LinkedServer.RemoteDb.schema.[TableName]

EXCEPT

SELECT DISTINCT
    identifier
FROM LocalDb.schema.[TableName]

On both tables are non-clustered indexes on the column identifier. Locally are around 2.6M rows, remotely only 54. Yet, when looking at the query plan, 70% of the execution time is devoted to "executing remote query". Also, when studying the complete query plan, the number of estimated local rows is 1 instead of 2695380 (which is the number of estimated rows when selecting only the query coming after EXCEPT).

When executing this query, it takes a long time indeed.

It makes me wonder: Why is this? Is the estimation "just" way off, or are remote queries on linked servers really that expensive?


texts: SQL Server Log Shipping from 2012 to 2014/2016

texts:
I am being told that it is NOT possible to do Log Shipping from SQL Server 2012 to SQL Server 2014 or 2016. Is this correct?

If not, are there any caveats to the process when Log Shipping to a SQL Server version higher than the source?


texts: Multiple "model" DBs -- or db creation 'profiles'

texts:
I know you can't actually have multiple 'model' DBs on the same server instance. BUT, I'd like to have something almost like it.  Basically, we have a business model where each customer is its own DB, and these DBs are all very similar in structure & definition (different in actual data/record content of course).  And then we have some 'global' DBs as well that are accessed and joined-to from each customer-DB.

So when we obtain a new customer, we need to create their DB.  We want the DB to be pre-populated with certain tables/procs, and certain pre-seeded data that is pretty consistent (one could say duplicated/redundant - yes, that'd be fair, but it's also very customizable and doesn't necessarily remain redundant after we start customizing that client's configuration).

Our initial solution was to build all of that into the [model] DB.  However, it's tailored to a particular kind of client - say, California.  Now we need to model, say, a Michigan client, and create a number of new DBs based on that.  So this is a problem because we need to kill our [model] and put all that CA model-data/structures somewhere else.

My thought was, let's just create a 'template' DB for each state, and when we need to create a new client DB, we just restore-from template's full-backup file (doing the Rename in the process of course).  Is this the thing to do?  Or is there a better way?


texts: A question on view equivalency: what is the need for the Initial Read condition?

texts:
The Initial Read condition for view equivalence states

Initial Read: Initial read of each data item in transactions must match in both schedules. For example, if transaction T1 reads a data item X before transaction T2 in schedule S1 then in schedule S2, T1 should read X before T2.

I don't understand why this condition should matter for view equivalence. Like take the schedules
S1: R1(A),R2(A),W1(A),W2(A)
S2: R2(A),R1(A),W1(A),W2(A)
Are these two view equal, or does the initial read condition prevent them from being equivalent ? At some level it seems like both should give equal results.


texts: Mysql Group replication vs Mysql Cluster

texts:
[disclaimer] I'm not new to databases but totally novice about replication and cluster ...

As you may know Mysql made available a new functionality : MySql Group Replication.

Is anyone can explain me the fundamental difference between such architecture and a cluster ? any major functionality existing only one of both ?  node fault tolerance ? capability to come back to live status ?
Every advise is welcome ...

Thanks


texts: Merge two applications using different NoSQL databases

texts:
This is a very generic and high level set of queries related NoSQL type of datastores
Imagine I have two different applications using NoSQL datastores each hosting its own document collection.
Questions

What are the design considerations I should be aware of in case I wanted to merge these two applications together?

How can I redesign the datastore so that I can support both the applications from a single unified datastore.?

Apologies if this sounds vague, but this is the only clarity I have at the moment.
In the case of merging an RDBMS into NoSQL, I would have followed these steps:

Modelling of the RDBMS structure into NoSQL datastore
Dump the entire data from RDBMS into NoSQL with Select *
Change the application code wherever affected (ex: Insert/Delete/Lookup etc)


texts: Obvious reason Postgres Users can't read a table?

texts:
Situation: a PG user (non superuser, inherits from parent role) that is a member of a Role/Group cannot read from specific tables even though these Object Privileges have been specified:

DBName - Connect
SELECT - true
INSERT - true
Delete - true
UPDATE - true

I can't figure out what the tablename_tablename_id_seq object does-- there is a 1:1 relationship between all my DB's tables and sequences but not sure how this impacts permissioning.

I tried clicking the checkbox to True (In Navicat) for "Usage" but the user still cannot read from the specified table.

I have attempted to edit these permissions in PG Admin 3&4, Navicat, and Postico...any idea where I am getting stuck?


texts: Does latin1 have performance benefits over utf8?

texts:
I have a table in utf8 with > 80M records and one of the columns (char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL) can contain just latin symbols ([a-zA-Z0-9]). Does it have the sense to convert this column into latin1?

MySQL doc says:

  To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible character length. For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.

I made a test - created 2 tables with the same 50M records:

CREATE TABLE `t_utf8` (
  `c_1` char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `index_t_utf8_on_c_1` (`c_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

CREATE TABLE `t_lat` (
  `c_1` char(6) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  UNIQUE KEY `index_t_lat_on_c_1` (`c_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

but MySQL says that they have almost the same size:

           Name: t_lat
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 57557700
 Avg_row_length: 30
    Data_length: 1741668352
Max_data_length: 0
   Index_length: 0
      Data_free: 2097152
 Auto_increment: NULL
      Collation: utf8_general_ci
 Create_options: row_format=COMPACT

           Name: t_utf8
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 57554528
 Avg_row_length: 31
    Data_length: 1810874368
Max_data_length: 0
   Index_length: 0
      Data_free: 3145728
 Auto_increment: NULL
      Collation: utf8_general_ci
 Create_options: row_format=COMPACT

Why does it so?

MySQL 5.7
InnoDB

P.S: I made the same test with MyISAM and got expected benefit: table with latin1 - 383Mb, utf8 - 1Gb. But why it does not work for InnoDB?


texts: How does postgres host based authentication work?

texts:
This is a duplicate of: https://stackoverflow.com/questions/55804806/how-does-postgres-host-based-authentication-work
(I was asked to shift this query to DBA.Stackexchange)
I am installing DSPACE which needs Postgresql.
Please see this link: DSPACE INSTALL
It says:

Then tighten up security a bit by editing pg_hba.conf and adding this line:
host dspace dspace 127.0.0.1 255.255.255.255 md5.

I have read on the internet as to how the above line works. It needs a connection type followed by database name/user name followed by IP address and the authentication scheme.
My query is: Should this not be a local (in place of host) connection since the dspace user is running locally?
Can someone show me step by step as to what happens when a request comes in?
Where are the instructions that the dspace user will submit a request using md5?


texts: Performance impact of Splitting Table across files

texts:
EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:

If I have a SQL Server 2005 database using a single .mdf file and I change it to use 2 files, should I expect performance to increase, decrease or remain the same?

We have an archive database that stores all reports sent out from our company since the beginning of time. As you can imagine the size of the database is large and grows quite quickly (currently 150GB and a few GB a week).

The only table in this database has a numeric primary key and a varbinary(MAX) column holding the file. All documents are retrieved via the primary key and performance is not currently an issue.

The problem we are facing is from the network team who backup and transfer the .mdf file over to our DR site every night. For them it would be better if we had multiple smaller files, rather than one big file. They are also saying it would make it easier to manage allocating space for future growth from our virtual infrastructure that it is sitting on.

Are there likely to be any performance loss/gain in splitting the table across multiple files on different virtual drives (potentially different physical drives).

I don't want to explore the problems of storing documents in a database, or running SQL Server on a VM, and we don't really want to change the database schema if we can help it, due to the cost in rewriting chunks of multiple apps.


texts: How can I reliably determine if a given SQL Server instance has the "Lock Pages in Memory" right?

texts:
The "Lock Pages in Memory" right can be granted to the service account used by SQL Server.  This allows SQL Server to prevent memory being paged to disk.

I've noticed several of our SQL Server machines do not have the local policy configured to allow this right for the service account used by SQL Server.  Since we have a large number of servers, it will be tedious at best to manually check each one using the "Local System Policy" management console.

Is there a T-SQL query, extended stored procedure, or some other method I can use to determine if the server in question has the right or not?

I would like to not use EXEC xp_readerrorlog 0, 1, 'lock memory privilege was not granted'; since it relies on checking the current SQL Server error log; and that error log may not contain the relevant entry assuming the log has been rolled over since the server was last restarted.  I realize I could check older logs by modify the first parameter from 0 to 1, etc, however we only retain 10 error logs, and that may not be enough.  I want a fail-safe way of confirming the setting.


texts: INT(5) vs SMALLINT(5): numbers in parenthesis after the numeric type

texts: In MySQL table definitions is there a different between INT(5) and SMALLINT(5)?  Or do they both represent the same size?


texts: Why my query works on VIEW and doesn't work on a similar table?

texts:
I have a view openstreetmapview that combines two tables with different columns. The first table is openstreetmap and the second is B.

When I execute this query on the view:

select this_.gid             as y0_
     , this_.openstreetmapId as y1_
     , this_.name            as y2_
     , this_.streetType      as y3_
     , this_.oneWay          as y4_
     , this_.location        as y5_
     , this_.isIn            as y6_
     , this_.countryCode     as y7_
     , length                as y8_
     , distance_sphere(
           GeometryFromText(
               'POINT(11.059734344482422 46.07373046875)',4326)
             , ST_line_interpolate_point(
                       this_.shape
                      , st_line_locate_point(
                             this_.shape
                           , GeometryFromText('POINT(11.059734344482422 46.07373046875)', 4326)
                      )
                )
       ) as y9_
     , ST_AsText(ST_ClosestPoint(
               this_.shape,GeometryFromText( 'POINT(11.059734344482422 46.07373046875)', 4326)
       )) as y10_
from OpenStreetMapView this_ ;

Then, I get around 50k results. When I execute the same query on the table even though the table has the same column that the query needs, it returns 0 rows. Why is that?

I use PostgreSQL 8.4 database and PostGIS .


texts: Use a view to join tables or a stored procedure for a better execution plan?

texts:
First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans.

What I want to know is for a stored procedure which the results are pulled from two or more tables using primary and foreign keys to reference them, is it better practice to:

Have a view which performs the joins and contains flattened data for ALL corresponding records from the two (or more) tables and let the stored proc select from and filter that view
Let the stored proc do the join and the filtering.

I ask because in a C# world we would separate out the concerns, so one object or function would be responsible for the joining (in this case the view) and another object or function would be responsible for the filtering (in this case the stored proc).

Which option from above will perform better?
Which option from above is considered best practice?

In C# separation of concerns is generally considered more important than performance until it is proven that performance has become a serious issue with the code. But I don't know who things stack up in the SQL world!


texts:
Is there a way to find out how much of the transaction log a specific transaction uses?

I have a database with simple recovery model and a log file with max size 500 MB. During our tests we ran into a "log file is full" situation, which is pretty strange, because our largest table is just 200 MB in size, we use only implicit transactions, we have no concurrent users and we don't change large amounts of data, apart from the occasional bulk insert which adds another 80 MB or so.

We can probably find the culprit by refining our application tests, but is there a way to find the offending statement through some SQL Server monitoring means?


texts: Meaning of 'In current row format, BLOB prefix of 768 bytes is stored inline.'

texts:
I encounter below issue on MySQL v5.6.16:

  Row size too large (> 8126). Changing some columns to TEXT or BLOB or
  using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current
  row format, BLOB prefix of 768 bytes is stored inline.

The corresponding table has several columns of type VARCHAR(4000).

I understand that here the record size exceeds the row size limit (8126 bytes), have also read 'Overview of InnoDB Row Storage'; but I do not understand what the following part of the error message means: In current row format, BLOB prefix of 768 bytes is stored inline.

When searching for this error, I see fixes being suggested but I'm interested on understanding what exactly causes this error. I would really appreciate your help in understanding the cause of this error.

Thank you.


texts:
I'm trying to find the best way to load a remote table with a blob column (blob only contains text, so clob would have been better).
So far we've been able to reduce loadtime from 218 minutes to 2 minutes using a view on the source table and converting the blob to varchar2 using (simplified):
CREATE OR REPLACE FORCE EDITIONABLE VIEW "TABLE_V" ("ID", "DATE", "CLOB_PT1", "CLOB_PT2", "CLOB", CONSTRAINT "TABLE_V_PK" PRIMARY KEY ("ID") RELY DISABLE) AS
  SELECT
  "ID",
  "DATE",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 1)) "CLOB_PT1",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 2001)) "CLOB_PT2",
  CASE WHEN LENGTH(INHOUD) > 4000 THEN TO_CLOB(BLOB) END "CLOB"
  FROM OWNER.TABLE;

Because we cannot guarantee the size of the blob will continue to fit in our two varchars we put anything bigger into a separate clob column in the view.
now the idea is to merge the varchars or clob back to one clob in our staging area.
Here come's the challenge of ORA-22992: cannot use LOB locators selected from remote tables
Concatenating the two varchars into the targeted clob column works fine.
INSERT
  /*+  APPEND PARALLEL  */
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  )
SELECT
  TABLE_V_1.ID ,
  TABLE_V_1.DATE ,
  TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
FROM
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

Inserting the clob into the targeted clob column works fine.
INSERT
  /*+  APPEND PARALLEL  */
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  )
SELECT
  TABLE_V_1.ID ,
  TABLE_V_1.DATE ,
  CLOB
FROM
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

Using a 'case when' inserting either the concatenated varchars or the clob column fails!
INSERT
  /*+  APPEND PARALLEL  */
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  )
SELECT
  TABLE_V_1.ID ,
  TABLE_V_1.DATE ,
  CASE WHEN CLOB IS NOT NULL
    THEN TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
    ELSE TABLE_V_1.INHOUD_CLOB
  END
FROM
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

I also tried concatenating all three columns by default, (varchars1 and 2 will be empty if size blob >4000), same error.
Why is this failing?
Any suggestions on getting this to work?


texts: Update Table with Network Client Connection Status, I need help with DB design. SQL 2008 Ex R2

texts:
I have C# client application that updates a Server Database, SQL 2008 Express R2. The client application send a ping to a group of devices once every 30 seconds and displays their status then updates Database. (updates only on successful response).
There is also a web page (from the same server) that can display the current status of each device, getting data from the Database.
If a device goes offline the client application tries 4 times to get a response and if it fails 4 times in a row it marks the device offline saying "Offline since DateTime".

I have 2 tables.
1. A Devices table with Name and IP address plus Index/ID.
2. A Connections table with FK: DeviceID and DateTime and Index.

The Index on the Connections table is going to be a massive value in a very short space of time. I don't need all that Data. All I need is the last known Online dateTime for each Offline device.

If you can image my Primary Customer, the owner of these devices will open the Web Page on his IPhone and see: Device 1 to 6 is Online, Device 7 is Offline since 7AM Yesterday etc.

I have 2 possible solutions:
A. Hold the last connection value in the application and only update the database if the device goes Offline. (This is error prone and could loose information).
B. Create a new Table at Midnight and have Multiple Connections tables separated by Day. (Is this possible? Is it a good idea?)


texts:
I have multiple handhelds which sync their data via merge replication. From time to time, new core data is imported on the server-database.

For various reasons I keep the old data in the databse and set a flag deactivated = 1.

In a filter I only download rows where deactivated = 0.

The core-data I'm talking about are 6 tables. Each has hundreds of thousands of rows. About 30,000 rows are sent to the handhelds during syncing.

The .OUT-File in the MS SQL CE-folder on the server has about 10 MB (!).

On syncining via gprs I receive this error

  SQL CE error 28562 - Failed to create the Delete message to send to the Server.

Any ideas how to handle this problem?

Mobile DB: SQL Server CE 3.1 SQL Server 2008 R2


texts:
I have following question:

"What normal form does a surrogate key violate?"

My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that to me?


texts:
I have database with Id - autoincrement, and Text- varchar.

My database is simple:

+---+--------+----+----+
| Id| Text   |Col1|Col2|
+---+--------+----+----+
| 1 | adf    |1   |0   |
| 2 | qwer   |5   |1   |
+---+--------+----+----+

This harder than I thought. What I want to do is to cut the table at some record and delete the tail.

Reason for this is that I had unique column that at some point in time I started receiving duplicates (because i changed key UNIQUE(Text) into UNIQUE(Text, Id))

Rob Farley's answer was what I asked originally in question but it wasn't what i really needed. I tried a revised version based on Rob's answer but results are still wrong:

SELECT MIN(Id) AS SmallestId, Text FROM table
GROUP BY Text HAVING COUNT(*) <2
ORDER BY SmallestId DESC

Problem with Rob's answer is that if first record (Id=1) is duplicate that lowest Id will equal 1 and i'd have to cut whole table.

Problem with my way is that it reports last (highest) occurrence of duplicate so I would have to leave some duplicates inside after the cut.

I think that what I want in human language is:

Find a longest sequence that doesn't have duplicates inside, starting from Id 1 and ordered by Id ASC.

It's a side project now so there is no pressure on answering, reason might be to learn more SQL. I'm the asker of original question but I had to edit as another because I accidentally created two accounts.


texts: Cardinality estimate outside the histogram

texts:
Setup

I'm having some trouble understanding a cardinality estimate.  Here's my test setup:

the 2010 version of the Stack Overflow database
SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2
the new CE (compatibility level 140)

I have this proc:

USE StackOverflow2010;
GO

CREATE OR ALTER PROCEDURE #sp_PostsByCommentCount
    @CommentCount int
AS
BEGIN
    SELECT *
    FROM dbo.Posts p
    WHERE
        p.CommentCount = @CommentCount
    OPTION (RECOMPILE);
END;
GO

There are no nonclustered indexes or statistics on the dbo.Posts table (there is a clustered index on Id).

When asking for an estimated plan for this, the "estimated rows" coming out of dbo.Posts is 1,934.99:

EXEC #sp_PostsByCommentCount @CommentCount = 51;

The following statistics object was automatically created when I asked for the estimated plan:

DBCC SHOW_STATISTICS('dbo.Posts', [_WA_Sys_00000006_0519C6AF]);

The highlights from that are:

The statistics have a pretty low sample rate of 1.81% (67,796 / 3,744,192)
Only 31 histogram steps were used
The "All density" value is 0.03030303 (33 distinct values were sampled)
The last RANGE_HI_KEY in the histogram is 50, with EQ_ROWS of 1

Question

Passing any value higher than 50 (up to and including 2,147,483,647) results in the 1,934.99 row estimate.  What calculation or value is used to produce this estimate?  The legacy cardinality estimator produces an estimate of 1 row, by the way.

What I've Tried

Here are some theories I had, things I tried, or additional bits of information I was able to dig up while looking into this.

Density Vector

I initially thought it would be the density vector, the same as if I had used OPTION (OPTIMIZE FOR UNKNOWN).  But the density vector for this stats object is 3,744,192 * 0.03030303 = 113,460, so that's not it.

Extended Events

I tried running an Extended Event session that collected the query_optimizer_estimate_cardinality event (which I learned about from Paul White's blog post Cardinality Estimation: Combining Density Statistics), and got these sort of interesting tidbits:

<CalculatorList>
  <FilterCalculator CalculatorName="CSelCalcColumnInInterval" Selectivity="-1.000"
                    CalculatorFailed="true" TableName="[p]" ColumnName="CommentCount" />

  <FilterCalculator CalculatorName="CSelCalcAscendingKeyFilter" Selectivity="0.001"
                    TableName="[p]" ColumnName="CommentCount" UseAverageFrequency="true"
                    StatId="4" />
</CalculatorList>

So it appears the CSelCalcAscendingKeyFilter calculator was used (the other one says it failed, whatever that means).  This column isn't a key, or unique, or necessarily ascending, but whatever.

Doing some Googling of that term led me to some blog posts:

Joe Sack - The CSelCalcAscendingKeyFilter Calculator,
Itzik Ben-Gan - Seek and You Shall Scan Part II: Ascending Keys

These posts indicate the new CE bases these outside-the-histogram estimates on a combination of the density vector and the stat's modification counter.  Unfortunately, I've already ruled out the density vector (I think?!), and the modification counter is zero (per sys.dm_db_stats_properties anyway).

Trace Flags

Forrest suggested I turn on TF 2363 to get some more information about the estimation process.  I think the most relevant thing from that output is this:

Plan for computation:

  CSelCalcAscendingKeyFilter(avg. freq., QCOL: [p].CommentCount)

Selectivity: 0.000516798

This is a breakthrough (thanks, Forrest!): that 0.000516798 number (which seems to have been unhelpfully rounded in the XE Selectivity="0.001" attribute above) multiplied by the number of the rows in the table is the estimate I've been looking for (1,934.99).

I'm probably missing something obvious, but I haven't been able to reverse engineer how that selectivity value is produced inside the CSelCalcAscendingKeyFilter calculator.


texts:
I've got big database (about 900GB), and when I migrate it to new server dump speed is not more than 2MB/s (very slow). Link between servers is 10Gb/s.

The database contain 20 tables with different sizes (the bigest one has about 600GB).
Because servers have different version of postgresql (source 9.3 and destination 9.4) I can't copy all cluster to new server.
I try using:

pg_dump -h source | pg_restore -h 127.0.0.1

but no result. I also try dump to file and then restore with '-j' parameter but also no result (after 20 min only one thread work because others finish their job). Problem isn't disk speed because both methods give me similar speed.

I haven't any experience with migrate such big data so maybe this methods are bad.
Have You any advices why this migration is to slow, and how speed it up?


texts:
Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows.

The table is a list of hotel bookings, containing (most relevant) Check-In date and Booking Value

My boss wanted a diagram where the values are summed for every month/year in a special way:

Row 1: Sum of value between Jan 2012 - Jan 2012
Row 2: Sum of value between Jan 2012 - Feb 2012
Row 3: Sum of value between Jan 2012 - Mar 2012
Row 4: Sum of value between Jan 2012 - Apr 2012
...
Row 5: Sum of value between Jan 2012 - Dec 2012

And so on for every year. Basically row 5 in this example would return the summed value for the entire year.

Table:

CREATE TABLE IF NOT EXISTS `bookings` (
  `DBID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `checkin` date NOT NULL,
  `checkout` date NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY (`DBID`,`checkin`),
  KEY `total` (`total`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

My query:

SELECT
    YEAR(checkin) as year,
    CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month,
    MONTH(checkin) as monthnum,
    (
        SELECT SUM(total)
        FROM bookings as book2
        WHERE YEAR(book2.checkin) = year AND MONTH(book2.checkin) <= monthnum
    ) as sum
FROM `bookings`
GROUP BY YEAR(checkin),MONTH(checkin)
ORDER BY YEAR(checkin) ASC, MONTH(checkin) ASC

So the subquery is the magic behind this query, it all produces the exact result I want, but the execution time is unacceptable.

I'm not sure how to optimise this further, I can't use joins since it's on the same table, I've indexed everything neccessary, etc.


texts: Can You Verify the Health of an Offline MySQL Database Before Starting Services During a Version Upgrade?

texts:
Our product has MySQL embedded within it which is deployed by our installer.  During upgrades of our software the version of MySQL may be updated as well.  Recently we had a defect in our stack where MySQL did not get shut down properly when our services were stopped.  As a result mysqld could get forcefully terminated during the upgrade.  This can and did lead to tables needing recovery in the middle of a version upgrade.

As a result we had an upgrade go really bad.  The new mysqld (8.x) was started up using the database files from 5.x.  The tables needed some level of repair which the 8.x daemon attempted to do, but failed.  At that point the database would not start at all under 8.x.  We tried to revert back to 5.x but due to changes made by mysqld 8.x, mysqld 5.x would not start either.  Essentially it seemed the database files were corrupted by being 5.x files half (and failed to be) recovered by 8.x.  We ended up having to do a very major restore from dumps and binlogs to recover.

My question is this:

Is there a way to pre-verify the health of the database files on disk before attempting to start mysqld or can mysqld be configured to not attempt to recover tables if recovery is needed and just terminate with an error?

Procedurally we can avoid this during the upgrade process but we would like to find a way to have the installer detect an unhealthy database and abort before attempting to upgrade.  We use NSIS where the uninstaller has already been deployed as part of the last version installation so it is difficult to modify the uninstall process that happens first during the upgrade.

Thanks in advance for any tips or ideas!


texts: Is there a way to identify file/process by query string with high query time?

texts:
There is a MySQL DB server with high load average goes up and down around 1.50 with many PHP scripts running on.

I checked the MySQL slow log file and saw many terrible queries logged. Huge pile of files on the project and I am new to this project. Is there a way to find relation between files/processes and slow queries logged in MySQL slow log file? I need to find where are those slow queries and fix.


texts: How to force a merge join without a hint

texts:
Some time ago I used query hints but then realized that most of the time SQL Server is smarter than I. And it's better to build an additional index/reorganize data or the query and get a much better result than forcing the server to use a plan that is inefficient in general but speedy enough for some subset of data. But now I am in a situation where I don't know how to organize data better.

I have two tables. First table, T1, is (Id, CustomerId) and second, T2, has the same columns. I want to join T1 to T2 on CustomerId. And get top N rows. In this case the optimizer sees that I want only N top and says, "Hey, I will use loops and find N matches very quick, especially when I use an index seek." But it's not working, because there is no data satisfying the conditions. Thus it uses a loop join to join a 25m table and a 100k table which is pretty slow.

When I force SQL Server to use a merge join, I get the following plan, which is executed in one second:

I don't want to force it for two reasons:

First, SQL Server is smart enough, as I said earlier.
Second, I'm using an ORM and it's quite hard to inject hints into
the generated query, so I'd like to avoid it.

What should I do in this situation?


texts: How to restore a pg_dumpall dump without CREATE INDEX?

texts:
I am attempting to migrate from Postgres 9.6 to 10.3 and during the restore each index is recreated one by one - this is a problem.

So far I thought pg_dumpall is a good option.

pg_dumpall -U postgres -h localhost -p 5432 --clean --file=dumpall_clean.sql

Once this is done the file is around 1.2TB in size and I can load it to the new 10.3 instance with

psql -U postgres -h localhost -p 5433 < dumpall_clean.sql

simple.

Problem

As I learned the indicies are not backed up like tables are, they are simply recreated, and that is my problem.

The cluster has thousands of partitions each with several million rows and two indices (one BTREE and one GIST). This takes days since each index is created one by one.

As I have enough resources and I know which indices have to be created, I would perfer to do this step after the dump has been restored. Initially I made 8 FOR loops (to run in parallel) to go through the partitions, and created an index by moving a partition to a faster tablespace (SSD), create the index, then move the table and the index back to the default tablespace. So far this has worked for me.

Question
How can I have the same result* of a pg_dumpall dump without recreating the indices when loading the dumpall_clean.sql file?
A pg_dumpall --without-index would be nice.

  "This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole." - pg_dumpall manual


texts:
I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address.

I need to select all columns from the first table and another field 'blacklisted' that tells if the ip in that access log is blacklisted or not.

I used a left join and COUNT() function to count occurences for each ip address but this will return a number, while i just need a boolean.

Here is my SQL code:

SELECT usl.*, COUNT(blip.id) as blacklisted
FROM user_session_logs as usl
LEFT JOIN blacklisted_ips as blip ON blip.ip_address = usl.ip_address
GROUP BY usl.id


texts:
I receive current day transactional data which I dump into a collection called 'collA'. The data will be reportable for max of 4 days, after this the data becomes useless to me.

Are the below two possible in mongoDB?

Auto swap collections - when clock turns 12 AM, create new collection. At this point the old collection should be renamed to 'collA1'. So at the end of day 4, I'll be having collA, collA1, collA2, collA3 and collA4
WRITE queries should be directed only to the new collection collA
Read queries should be directed to all collA1 - 4 and collA


texts: Best approach for moving tempdb .ndf files

texts:
During a SQL Server installation, I "thought" I specified location of tempdb on 'F:' and  not 'C:', but I can see that although tempdb.mdf and templog.ldf are on 'F:', the 3 .ndf secondary data files are on 'C:'.

I'm thinking of moving the .ndf files by performing following steps:

 1.

    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2 , FILENAME = 'F:\MSSQLData\temp2' )
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3 , FILENAME = 'F:\MSSQLData\temp3' )
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp4 , FILENAME = 'F:\MSSQLData\temp4' )

Stop SQL Server (the instance isn't doing anything currently).
copy/paste the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location
Restart SQL Server.
Check if it worked:

    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');

Is that the best approach? The instance supports only 3 small databases (<10GB total).


texts: Indexed View over multiple partitioned tables and ALTER TABLE SWITCH

texts:
Is it possible to have an indexed view that combines 2 partitioned tables and still use ALTER TABLE SWITCH to move data out of both base tables? I'm receiving the following error when trying this and can't seem to figure out how to solve it (if it's even solvable):

  ALTER TABLE SWITCH statement failed. Table 'im_db.dbo.SM_MEDIA_NEUTRAL_STATE_FACT' is not aligned with the index 'IX_V_STATE_RSN_AGG_BASE' on indexed view 'V_STATE_RSN_AGG_BASE'. The table is partitioned on column 'START_DATE_TIME_KEY', but the index on the indexed view is partitioned on column 'start_date_time_key', which is selected from a different column 'START_DATE_TIME_KEY' in table 'SM_RES_STATE_REASON_FACT'. Change the indexed view definition so that the partitioning column is the same as the table's partitioning column.;

I've tried changing the partition column but it doesn't matter which I use (from which base table).


texts: Microsoft SQL Server how can i detach localdb to run on any computer

texts:
I am working on a personal project and have implemented a database system using the Entity Framework code first tutorial on msdn, link below,

http://msdn.microsoft.com/en-gb/data/jj193542

I am running Visual Studio 2012 and the database has been created using Microsoft SQL Server and localdb. However i wish to run my application on any computer and this current setup will not allow me to do so.

I am a bit of a novice when it comes to databases, and would like some advice/resources that outline how i can change my database setup so that it is available on any computer and also retain the data currently in my database.

Any help would be greatly appreciated,
Thanks in advance!


texts:
I am trying to map a drive to a UNC path where the UNC Path is a variable, but I keep getting an error saying:

  Incorrect syntax near '+'

I execute this script:

DECLARE @UNCSourcePath nvarchar(255) = '\\OTHER_SERVER\C$\Program Files'
EXEC xp_cmdshell 'NET USE S: ' + @UNCSourcePath

Any idea what the syntax should be?


texts: Simplest way to edit single entry in XML column?

texts:
As I understand MS SQL Server Management Studio doesn't allow directly editing/replacing entries in XML columns via GUI (copy/paste doesn't work etc.).

What is the easiest option to replace a single entry in an XML column? Do I have to use something different from UPDATE/REPLACE for an XML column value?


texts: Even after permissions are updated, I still get the error permission denied for relation users

texts:
I'm getting the error ERROR: permission denied for relation users and have tried to update privileges using both

grant all privileges on all tables in schema ops to my_user
GRANT ALL PRIVILEGES ON TABLE the_table TO my_user

In the Grants tab of DBVisualizer I can see that the my_user user has the privileges DELETE, INSERT, SELECT, TRUNCATE, and UPDATE.  IS_GRANTABLE is set to NO for all of these. Yet I still get this error. What is there left to do.


texts: Grouping operator in DBMS (γ) is duplicate-impervious. Why?

texts: I'm reading Database Systems by Ullman where its mentioned that γ is duplicate-impervious. I don't understand why a simple group operation should cause elimination of duplicates. Aliter, Duplicate-elimination operator δ is a special Aggregation operator. Why?


texts:
I have a database with creations and orders, and a bunch of tables linking them.

I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a specific creation name pattern, and a specific type of product references.

I have this, which doesn't work because the inner query does not knows the yearcrea and monthcrea columns:

select
date_part('year', c.created_at) as yearcrea,
date_part('month', c.created_at) as monthcrea,
p.reference,
count (*) as nb,
(
  select sum(o2.amount_ati_cents)
  from creations as c2
  inner join products as p on c.product_id = p.id
  inner join order_items as oi2 on c2.id = oi2.creation_id
  inner join bundles as b2 on b2.id = oi2.bundle_id
  inner join orders as o2 on o2.id = b2.order_id
  where o2.status NOT IN ('canceled', 'new')
  and c2.name like '% - Premium(%'
  and p2.reference like 'FLA_%'
  and date_part('year', c2.created_at) = yearcrea
  and date_part('month', c2.created_at) = monthcrea
) as ca

from creations as c
inner join products as p on c.product_id = p.id

where c.name like '% - Premium(%'
and p.reference like 'FLA_%'

and c.id in (
    select oi.creation_id
    from order_items as oi
    inner join bundles as b on b.id = oi.bundle_id
    inner join orders as o on o.id = b.order_id
    where o.status NOT IN ('canceled', 'new')
)

group by yearcrea, monthcrea, reference
order by yearcrea desc, monthcrea desc

I have also tried

and date_part('year', c2.created_at) = date_part('year', c.created_at)
and date_part('month', c2.created_at) = date_part('month', c.created_at)

but I get an error: subquery uses ungrouped column "c.created_at" from outer query.

How can I filter a select sub-query with "transformed" columns ?

Maybe I'm looking at it the wrong way and there is a simpler way to extract the info I want ?


texts: Can I recover a TDE certificate by restoring the MASTER database?

texts:
(We are, fortunately, not currently in this situation, just planning ahead to see what our options would be if it ever occurred.)

For a database encrypted with Transparent Date Encryption (TDE), a copy of the database backup is unrecoverable unless you have a backup of the certificate used to encrypt it.

What if you don't have that? Are there any additional options?

In the event of a total server failure, would restoring a backup of the MASTER database on new hardware also restore the certificate?


texts: SQL SERVER 2008 R2 - Merge Replication - Identity Seed issue

texts:
The server I am running has identity Management set to automatic for most of the tables I am replicating (A hand full have been set to manual).  When the replication goes to change the range it changes the constraint BUT it does not change the identity seed of the table.

When users try and insert records, I get the not so clear message of

"If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent."

These steps never seem to work and I end up have to reseed manually.  Is there a setting I am missing?  I have some suspicion that because some of the tables are set to manual and some set to automatic, the sp_adjustpublisheridentityrange does not work correctly when it is run by the merge process (If I run sp_adjustpublisheridentityrange manually with the @publication set, it errors out.  If I run sp_adjustpublisheridentityrange with @table_name set, it works).


texts: mariaDB: store data outside the default data directory(in a new disk)

texts:
Mysql has "general tablespace"which can point to any directory outside default data path. by this we can store data to external disks.
Is there similar option for marian db?
Basically I want to store data to a different disk.


texts: SQL Transaction - use values before commit?

texts:
So I am creating a stored procedure in SQL2014.  Towards the start it has:
Begin Transaction

Then  it does an insert into a table, which has an auto-incrementing primary key:
Insert into tNotice.

And then latter on in the stored proc, I need to write a query against the tNotice table to use to build up another insert statement into a different table.  My question is, will the newly inserted values be selected since this is going to be in a transaction, and the commit won't be until the end of the stored proc?


texts: Is it possible to synchronize multiple databases in to 1 database with the same tables?

texts:
We are facing a situation where we have 5 different businesses with comparable data and are building 1 single application to deliver their data to common customers.

In order to do this, the plan is to synchronize all data in to 1 central database.

the way that is currently planned, is to work via webservices and each business will manage their own data via those webservices. This however will give each business extra work load in order to keep their data integrity in check via those web services.

I was wondering if there is a better way. Is it possible for those 5 businesses to create some sort of "shadow" tables of the new central database and fill that up... So that a Third party software can handle all the issues of synchronization (or replication if this is the correct term)?

Is such a thing possible and does this type of software exist for sql server and/or oracle?

Thank you,


texts: Select multiple columns as JSON in a single query

texts:
How can I combine several columns (of varying types, e.g. int, datetime, nvarchar) as a single JSON-formatted expression in a select query? For example, I have columns col1, col2 and want the query to return a single column where each row like this:

{col1: val1, col2: val2}

Can I use FOR JSON? I'm using SQL Server 2017, but the database I'm querying is 2008 (100) compatibility mode.


texts:
So I have this query:

UPDATE ref_records SET count = count + 1 WHERE ref_url = 'http://www.example.com/' AND user_id = '32366' LIMIT 1

I have indexes set on each "ref_url" and "user_id" respectively. However, MySQL explain shows that it's merging the indexes:

Using intersect(user_id,ref_url); Using where

But why doesn't it do so in their actual column order (ref_url, user_id) ?
Also, would it help optimize this query by adding an index on (ref_url,user_id) or index on (user_id,ref_url) ?

Thanks.


texts:
I see in my 19c Cluster database a lot of sessions (over 350) with SYSRAC user and status INACTIVE with oraagent.bin TNS V1. They can consume my connection pool (how can I make this bigger number).
Why do I see so much those inactive connections and if this is realy so needed? Could this be some PL/SQL dbms monitoring metrics for the Oracle Cloud Control?
Kind regards.


texts: schema for product prices based on membership level

texts:
I need to model the following scenario:

Customers have 3 options of annual memberships: [basic:$0,silver:$50,gold:$100]
We have a supplier with product A at a cost of $30 per unit. (We will have multiple suppliers with multiple products)
Sell price based on level of membership for product A is A=[basic:$90,silver:$50,gold:$35]

Questions

So far this is the model I have come with and am looking for validation from more experienced dbas (see diagram below)
With this model I have to input a price per product/membership/supplier. If management wants to apply a flat discount 20% off for all products of supplier B for customer with membership Silver this will break my logic. Not being requested but I can see it comming. Is there a way of having both scenarios in the schema? I have no idea how.
Last but not least, the membership is a product customers need to buy in order to get some discount. By default all customers begins with Basic level. with this in mind how can I accommodate membership in my Product and Product_price??? the product_price table already takes in the membership on account so it is a kind of circular reference?


texts: Attatch database - cannot browse to database

texts:
I have installed MS SQL Server Developer 2014.  I am trying to attach the Adventure Works database.  The usual process I would follow is:

1) Right click on Databases
2) Select Attatch
3) Click Add

Step four usually involves navigating the file system to find the .mdf and .ldf, however instead of a file system I see this:

I am a Developer and not a DBA.  I am also new to SQL Server 2014.

What do I do now?


texts: SentryOne Plan Explorer properties window

texts: Does the SentryOne Plan Explorer have a properties window similar to SSMS. I see the tool tip window next to the operators in the execution plan but no properties option ?


texts: Automysqlbackup fix error "Skipping the data of table mysql.event. Specify the --events option explicitly"

texts:
I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.".

I found that its because of not specifying whether or not to add events table in mysqldump.

I need to know how to specify the  events option inside automysqlbackup config file.


texts: select insert query optimization on large volume MYSQL 8.x

texts:
Im trying to run this SELECT query from table bidPrice that has over 160 million rows into a second table called spreads_v2
INSERT INTO spreads_v2(SELECT bp1.timestamp, bp1.idExchangePair, bp1.idRequest, bp1.idExchangePlatform AS idEp_a, bp2.idExchangePlatform AS idEp_b, bp1.lastPrice as lastPrice_a, bp2.lastPrice AS lastPrice_b,
(SELECT IF (bp1.lastPrice> bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)),
(SELECT (((ABS(bp1.lastPrice - bp2.lastPrice))*100)/(SELECT IF (bp1.lastPrice> bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)))) AS spread_percentage,
ABS(bp1.lastPrice - bp2.lastPrice) AS spread_A_B
 FROM bidPrice AS bp1
JOIN bidPrice AS bp2
ON bp1.idRequest = bp2.idRequest AND bp1.idExchangePair = bp2.idExchangePair AND bp1.idExchangePlatform < bp2.idExchangePlatform)

The query works fine, i tested on a test db with the right output, but the issue comes when i run this into the production db that has large amounts of data, i ran into canceled query after 12 hours of execution.
heres the SHOW CREATE table for both
CREATE TABLE `bidPrice` (
  `idBidPrice` int NOT NULL AUTO_INCREMENT,
  `idRequest` int NOT NULL,
  `idCurrencyPair` int NOT NULL,
  `idExchangePlatform` int DEFAULT NULL,
  `idExchangePair` int DEFAULT NULL,
  `bidPrice` double DEFAULT NULL,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `wholeApiResponse` longtext,
  `askPrice` double NOT NULL,
  `lastPrice` double NOT NULL,
  `lowPrice` double NOT NULL,
  `highPrice` double NOT NULL,
  `volumePrice` double NOT NULL,
  `platformTimestamp` double DEFAULT NULL,
  PRIMARY KEY (`idBidPrice`,`idRequest`,`idCurrencyPair`),
  KEY `idExchangePlatform` (`idExchangePlatform`),
  KEY `idExchangePair` (`idExchangePair`),
  CONSTRAINT `bidPrice_ibfk_1` FOREIGN KEY (`idExchangePlatform`) REFERENCES `exchangePlatform` (`idExchangePlatform`),
  CONSTRAINT `bidPrice_ibfk_2` FOREIGN KEY (`idExchangePair`) REFERENCES `exchangePair` (`idExchangePair`)
) ENGINE=InnoDB AUTO_INCREMENT=176759983 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `spreads_v2` (
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `idExchangePair` int DEFAULT NULL,
  `idRequest` int DEFAULT NULL,
  `idExchangePlatform_A` int DEFAULT NULL,
  `idExchangePlatform_B` int DEFAULT NULL,
  `bidPrice_A` double DEFAULT NULL,
  `bidPrice_B` double DEFAULT NULL,
  `high_value` double DEFAULT NULL,
  `spread_percentage` double DEFAULT NULL,
  `spread_A_B` double DEFAULT NULL,
  UNIQUE KEY `idExchangePair` (`idExchangePair`,`idRequest`,`idExchangePlatform_A`,`idExchangePlatform_B`),
  KEY `idExchangePlatform_A` (`idExchangePlatform_A`),
  KEY `idExchangePlatform_B` (`idExchangePlatform_B`),
  CONSTRAINT `spreads_v2_ibfk_1` FOREIGN KEY (`idExchangePair`) REFERENCES `exchangePair` (`idExchangePair`),
  CONSTRAINT `spreads_v2_ibfk_2` FOREIGN KEY (`idExchangePlatform_A`) REFERENCES `exchangePlatform` (`idExchangePlatform`),
  CONSTRAINT `spreads_v2_ibfk_3` FOREIGN KEY (`idExchangePlatform_B`) REFERENCES `exchangePlatform` (`idExchangePlatform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As an aditional comment, i have this database in a AWS RDS t2.micro, im considering on upgrading the db instance for query speed up.


texts:
My system:

2X E5 2680 V3 Cpu
32 GB Ram
Windows 7 Pro

Apache 2.4.41
Mysql 8.0.17

I have a script and this for Customer Online Chat Script.  I have 10 clients for customer support.  It uses php json and ajax.  It generates high select query.
What can I do?  Is there any my.cnf example for this usage?
[client]
no-beep

# pipe
# socket=0.0
port=3306

[mysql]

default-character-set = UTF8MB4

[mysqld]
port=3306
basedir="C:\AppServ/MySQL"
datadir="C:\AppServ/MySQL/data/"
character-set-server = UTF8MB4

sql-mode=""
log-output=FILE
general-log=0
general_log_file="mysql.log"
slow-query-log=1
slow_query_log_file="mysql-slow.log"
long_query_time=3
log-error="mysql-error.log"
server-id=1

secure-file-priv="datadir="C:\AppServ/MySQL/Uploads"

max_connections=500

table_open_cache=4000

tmp_table_size=1024M
max_heap_table_size=1024M

thread_cache_size=200
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=975M
key_buffer_size=512M

read_buffer_size=256K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=20000M

innodb_log_file_size=250M

innodb_thread_concurrency=0

innodb_autoextend_increment=6
innodb_buffer_pool_instances=16

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=600

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=5161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

default_authentication_plugin=mysql_native_password


texts: Does Percona Xtrabackup support RedHat release 4

texts:
I downloaded Percona's Xtrabackup rpm file for RedHAT. I attempted to install but hit into an error. The error seems to point to missing dependencies and libraries. I have no issue with RedHat 6 OS. I could not find the binaries in Percona's website for RedHat4.

Is it supported in RedHat 4? Possible for a workaround?

rpm -Uvh percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm
warning: only V3 signatures can be verified, skipping V4 signature
error: Failed dependencies:
        libc.so.6(GLIBC_2.10)(64bit) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        libz.so.1(ZLIB_1.2.0)(64bit) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        perl(DBD::mysql) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        rpmlib(FileDigests) <= 4.6.0-1 is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        rtld(GNU_HASH) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        rpmlib(PayloadIsXz) <= 5.2-1 is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
    Suggested resolutions:
        /var/spool/up2dateperl-DBD-MySQL-2.9004-3.1.x86_64.rpm


texts: Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

texts:
I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?

I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP over ssh. It runs on port 3306 on the remote server where command-line mysql works fine.

I used the following session details:

Connection Method: TCP/IP over SSH.
SSH Hostname: mysql.myhost.com:3306
SSH username: my linux login
SSH public key file: my local public key file
MySQL hostname: 127.0.0.1 MySQL
Server Port: 3306
Username: root

I get an error message when I try to connect:
"Failed to connect to MySQL at 127.0.0.1:3306 through SSH tunnel at mysql.myhost.com with user root"

"Can't connect to MySQL server on '127.0.0.1' (10061)"

As another test - I set up a SSH tunnel with port 3306 using Putty and I can connect OK using MySQL Workbench through that tunnel which forwards connections to my local 3306 to the remote server as described above. But I can't get "TCP/IP over SSH" working in Workbench.

Secondary question: when Workbench asks for "Path to SSH public key file" doesn't it really need my private key file?


texts: I need to store large amounts of data in terms of time and source as dimensions. How should I design my database?

texts:
My project needs to save a large amount of historical data from sensors, and the number of them is large.
Although we have eliminated part of the data by the anti-jitter and anti-duplication, but becaues of my project needs to keeping running for a long time, so it still will generates a lot of records.
I used tried SQL Server and MySQL to save these records, and divide the data into tables by sensor(source) or time (Daily, Monthly).
[by sensor]
his_data_Dev1
his_data_Dev2
his_data_Dev3

[by time(daily)]
his_Data_20210301
his_Data_20210302
his_Data_20210303

[by time(monthly)]
his_Data_202101
his_Data_202102

example data:(no device column in divide by device)

device
date
value

Dev1
2021-03-01 00:00:12
3.53

Dev1
2021-03-01 00:00:23
3.44

Dev1
2021-03-01 00:00:26
3.52

Dev1
2021-03-01 00:00:33
3.53

But they are not easy to query and still have performance bottlenecks.
Currently looking for a better solution, which can better save large amounts of data (records for several years), and faster query speed (especially aggregate computing, we need to aggregate and calculate the historical values of a each device in units of years.).
The solution accepts to change to another database - but at least it should support .NET Core access.


texts: If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

texts:
If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp table created in the session that executes sp_ExecuteSql.

In other words, why does this work:

SELECT 1 AS TestColumn
INTO #TestTempTable

DECLARE @DS NVARCHAR(MAX) = 'SELECT * FROM #TestTempTable'
EXEC sp_EXECUTESQL @DS

Results:

My understanding for the reason why I can't do the opposite (create the temp table in Dynamic SQL and then access it outside the dynamic SQL query in the executing session) is because sp_ExecuteSql executes under a new session.


texts: Need to find the employees who have switched jobs atleast twice

texts:
I am trying the following query on the tables which are described below:

SELECT * FROM job_history INNER JOIN employees
ON job_history.employee_id = employees.employee_id
WHERE employee_ID IN
    (SELECT employee_id FROM

        (SELECT employee_id, COUNT(employee_id) AS cnt
         FROM job_history
         GROUP BY employee_id
         HAVING cnt >= 2) INNER
    )
    OUTER;

But I get an error in MySQL, that every derived table must have its own alias

We have a table job_history(employee_id, start_date, end_date, job_id, department_id) containing the following data:

'102', '1993-01-13', '1998-06-24', 'IT_PROG', '60'
'101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'
'101', '1993-10-28', '1997-03-15', 'AC_MGR', '110'
'201', '1996-02-17', '1999-12-19', 'MK_REP', '20'
'114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'
'122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'
'200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'
'176', '1998-03-24', '1998-12-31', 'SA_REP', '80'
'176', '1999-01-01', '1999-12-31', 'SA_MAN', '80'
'200', '1994-07-01', '1998-12-31', 'AC_ACCOUNT', '90'

We have another table employees(employee_id int,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_number varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salary float,
commission_pct float,
manager_id int,
department_id int,
constraint emp_pk primary key(employee_id)
)


texts:
I have Installed MySQL Server 5.6.23 on Centos server and the configuration of the server is

16 GB RAM
8 Core
CPU op-mode(s): 32-bit, 64-bit

When I check the status of the server it is showing me Queries per second avg: 22112.191

Still my server is running fine and there is no issue in read & write operation but I would like to know what is the maximum Queries per second my server can handle and what can I do to reduce it.?

Thanks in advance.


texts: What is the optimal way to upgrade production RDS instance?

texts:
I have MySQL  small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS.

As old-school  DBA I'm aware about "add slave; promote to master; switch clients" method, but AWS promises to provide magic one-click upgrade path, i.e. "upgrade instance", "add  provided IOPS".

Tried this on test RDS instance, downtime is too long, IMHO: about 5 min for small->medium upgrade, and 30 min (!!!) for switching to provided IOPS.

Is this normal behavior?
Is there any way to run upgrade on production RDS w/o downtime?
Do you recommend "stop; create a snapshot;  restore from snapshot to bigger instance" way?


texts: Is it possible to detach some specific database from MongoDB Server?

texts:
I have to keep several copies of production databases serving by a dockerized MongoDB server.

I do NOT want to backup, then to drop, then, when I need it again, to restore it. It is not as quick process as I would want it to be.

But when I keep those DBs online, MongoDB server eats a lot of my host system memory (16Gb total physical RAM).

I would like to detach some specific databases in order to free some memory and attach them again when I need them.

I tried to search the answer with google, but without success.

MongoDB 4.0, LinuxMint 19.3 Tricia


texts: Migrating from 32 bit using pg_basebackup

texts:
Has anyone migrated a 32 bit postgres 9.2 database to a 64 bit postgres 9.2?

I'm trying to use pg_basebackup but I'm not sure whether this is the right approach.


texts: The dump database on a SSD disk is slowly restored in SQL Server

texts:
I prepared new server for my DB on Windows 2008R2 + SQL Server 2005 SP4.

I always estimated time of restore database of a backup as time of copying of the file of a backup at that place where the base will be located.

But this time this indicator strongly differs.

Copying takes 30 minutes.

Restore database takes 300 minutes.

After restore database all operations work so quickly as expected.

I can't understand in what the reason of such slow restore database.


texts: How to create SQL Server backup from Windows Server to Linux Client with SQLCMD?

texts:
I've got sqlcmd installed on an Ubuntu LTS instance and I'm able to connect successfully to Windows SQL Server and do queries and such.
sqlcmd -S 192.168.0.100 -U SA -P secret

I need to be able to create a backup of the database (preferably as a gigantic db-backup.sql).
It appears that the BACKUP command can only save a .bak file to the remote Windows SQL Server, but I want to save it locally on the Linux client that's actually running sqlcmd.
BACKUP DATABASE [MyDatabaseName]
    TO DISK = N'./MS-SQL-Server-Test-DB.bak'
    WITH NOFORMAT, NOINIT,
    NAME = N'MS-SQL-Server-Test-DB Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

How can I backup to the local client instead of the remote server?
(I have full sudo access on the Linux client, but only sqlcmd db backup user and SA user access to the Windows Server).
Note: It's Windows Server 2019 or something like that. It doesn't have ssh installed, and I don't think I can get it installed.


texts: How to select specific items from table A which appear with all items of some column in table B?

texts:
I have 2 tables with the following schemas:

t1(id, type) where id and type are the primary key

t2(type, rate) where type is the primary key

I want to find all id's which appear with all types from t2.

For example given the tables:

t1:

 id | type
 ---------
  1 | a
  1 | b
  1 | c
  2 | a
  2 | b

and t2:

 type | rate
 -----------
    a | 100
    b | 100
    c | 200

We can see that there're 3 unique types in t2 and only id 2 from t1 appears with all of them.

I figured out a way to get the id's by counting the number of unique types and then grouping t1 by id's and selecting only those whose count equals of the total number of types from t2.

But I was wondering if there're other logical constructs like in/not in, exists or Cartesian product which can also be used here with more elegance?


texts:
Database X - tableCust - Column-CustNumber
Database Y - tableCust - Column-Custnumber
Datbase Z - tableCust - Column-Custnumber

I'm trying to create a view in database H from those three databases like below.

Select Custnumber,Orginal_DB_Name() as DatabaseName
from X.dbo.tablecust
Union
Select Custnumber,Orginal_DB_Name() as DatabaseName
from Y.dbo.tablecust
;
;
;
etc

But the database name is showing me Master instead of their DB_Name.

It needs to show output like below:

Database_Name CustNumber
X               221
X               1223
Y                122
Y               233
"
"


texts: SQL Import/Export defaults to byte stream on export

texts:
So, I've done some research and I can't seem to figure this out.  I'm not sure if it's some setting that I'm missing or what.

For the basic info, running SQL2012, 64 bit, all that good stuff.  I noticed that, for some reason, when I export from a table into a flat file using a query, the data type is defaulting to bytestream.  In the past, it always defaulted to DT_STR and went through without any issues.

Here's the query:

SELECT pin from IncrementalDM_Emails
where email in (select email from [7755_forsql])
order by pin

Here's the error I'm getting:

Here's what the export is showing when I select "Edit Mappings..."

Now, this can be easily fixed by simply selecting "DT_STR" under the Mappings dialog but I frequently export using this method so I'd like to find out why it's doing it and fix it so I don't always have to go into the Edit Mappings dialog.

Is it something to do with the query and the use of the IN function?  Any help would be greatly appreciated!

EDIT: The data in both tables is stored as varchar(50)


texts: what is the fast way to prepare test db? slimming mysql db

texts:
My idea is this:

get mysql dump file from production (GBs)
prepare set of requests based on access logs and run them
record all accessed records in mysql
remove everything else in my db
finally: have lightweight db and recorded requests as tests.

I have a problem with points 3+4. I wonder if such a feature even exists for MySQL, with the possibility to delete, and drop everything that was not accessed (including indexes rebuild).

Of course, having a lightweight DB is needed to easily auto-generate test fixtures, and this is the main purpose.


texts: Killed SPID on xp_LogInfo not rolling back and growing tempdb

texts:
I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task.
I checked the scheduled tasks and we had one that had been running for over a day, usually it runs for less than a minute, and it was showing a status of "Executing: 0(Unknown)" in the job activity monitor.
When I tried to stop the Job in Activity Monitor nothing happened, it just kept on running and filling up tempdb to almost the full size of the disc.  Knowing that the job itself could just be stopped and re-run without affecting the business, I, perhaps too hastily, killed the SPID, thinking this would "release" space back to tempdb and I would then restart the services out of hours to shrink the files.
Unfortunately this didn't work and the SPID has been in a Killed/rollback state for nearly a day now whilst the tempdb files grow (I arranged for the disk they sit on to be increased whilst I looked into it).
When I check the process for the SPID it appears to be running xp_LogInfo with the last batch relating to insert #nt select distinct domain+N'\'+name, sid, sidtype from OpenRowset(TABLE NETUSERGETGROUPS, @acctnam.
I believe that the owner of the SQL Job may have reset their password when the job kicked in to cause this (this is just me guessing) which has caused it to get stuck in a loop.
The CPU and MemUsage in sysprocesses hasn't changed for over a day now and the LastWaitType is showing as PREEMPTIVE_OS_AUTHORIZATIONOPS.
When running DBCC INPUTBUFFER for the SPID I previously killed I am receiving this:
EventType - Language Event
Parameters - 0
EventInfo - (@P1 nvarchar(128),@P2 uniqueidentifier)EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1, @job_id = @P2

I have now restarted the SQL Server Agent Service which stopped the job running but the tempdb log continues to grow.
What can I do to resolve this?
When running DBCC OPENTRAN(TEMPDB) I get the below, the spid it relates to is the one I killed:
Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID): 205
UID (user ID) : -1
Name : INSERT EXEC
LSN : (5293:26478:20)
Start time : Oct 19 2020 3:30:02:193PM
SID : 0x0105000000000005150000005f9b10c13b99fcfc4ad890fd8e220200

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

kill {spid} with statusonly returned "SPID 205: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."


texts: Solving supertype-subtype relationship without sacrificing data consistency in a relational database

texts:
Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem.

Here is an example using common requirements:

An online store sells different categories of product.
The system must be able to retrieve the list of all product categories, say food and furniture.
A customer may order any product and retrieve his order history.
System must store specific properties depending on the product category ; say the expiration_date and calories for any food product and manufacture_date for any furniture product.

If it wasn't for requirement 4, the model could be quite straightforward:

Problem is trying to solve requirement 4. I thought of something like this:

In this approach, the relationships product-furniture and product-food are supertype-subtype (or superclass-subclass) associations; the primary key of the subtype is also a foreign key to the supertype primary key.

However, this approach can not guarantee the category referenced via a foreign key to the product will be consistent with its actual subtype. For instance, nothing stops me from setting food category to a product tuple having a subtype row in the Furniture table.

I read various articles about inheritance in modelling relational databases, especially this one and this one which were very helpful but didn't solve my problem for the reason mentioned above. But whatever model I come with, I'm never satisfied with the data consistency.

How can I solve requirement 4 without sacrificing data consistency ? Am I going all wrong here ? If so, what would be the best way to solve this problem based on these requirements ?