texts:
Following query I have written returns the result as expected, example 121350 rows.

SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id

However,WHEN i TRY TO ADD a subquery following
SELECT statement the resulted ROWS increases TO 155550 row due TO subquery returning one TO many ROWS.

(
               SELECT CASE
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG

Ordering by OrderId.

How can I solve this so that my consolidation of first query does not break due to subquery?

Current result I am getting with subquery...

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    0.639           21   0
125                L                121212                    0.639           21   0

expected result with consolidation.

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    1. 278         42   0


texts: Count based on status on latest association

texts:
I have two tables in PostgreSQL like this:
Process
- id : uuid

Log
 - created : date
 - status : string
 - parentProcess : uuid

A process can have several logs related to it, and their status can be 'SUCCESS', 'FAILED', 'PENDING'.
I would like to group all of my processes based on the latest associated Log status:

PENDING
FAILED
SUCCESS

7
4
23

For example, if a Process has 3 Logs with the following data
Process a {
   id: 1
}

Log 1 {
 - created : 2020-05-18 11:02:23.035+00
 - status: "PENDING"
 - parentProcess: 1
}

Log 2 {
 - created : 2020-05-18 17:57:44.002+00
 - status: "SUCCESS"
 - parentProcess: 1
}

Log 3 {
 - created : 2020-05-18 09:31:02.081+00
 - status: "FAILED"
 - parentProcess: 1
}

this process should be increase the 'SUCCESS' column because its latest log (Log 2) has 'SUCCESS' status.
How would I write the SELECT to be able to do this?


texts: differences in the authentication scheme - could it be because of Kerberos?

texts:
I am using this script to check the connections to my sql server:

select
C.client_net_address,
S.host_name,
 c.session_id,
 c.net_transport,
 c.protocol_type,
 c.auth_scheme,
 s.program_name
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s
on c.session_id = s.session_id;

I have noticed that our servers in the UK are generally different to the servers in the US.

US:

UK:

I have found a very interesting link:

Kerberos Authentication requires that you have Service Principal Names registered

and I was wondering if that could be the case for the differences in the authentication scheme on the pictures above.


texts: SQL Server 2012 certificate encrypt connection

texts:
I'm not a DBA by trade. My organization doesn't have a dedicated DBA, we have to just figure things out. I have a SQL Server 2012 Enterprise installed on a Sever 2012 R2 OS. It is a not in a SQL cluster. The server is a virtual machine. I've been asked to encrypt the connection for the SQL server, as described here:
https://msdn.microsoft.com/en-us/library/ms191192(v=sql.110).aspx

I don't have any problems until I get to selecting the certificate. The certificate does not display. It's loaded properly in the MMC certificate plug in. Before posting here, I've looked at numerous links. I was going to post a few of them, but I don't have enough reputation points. Here is one I looked at:
https://www.experts-exchange.com/questions/27860135/SSL-cert-not-showing-up-in-SQL-Server-2008-Configuration-Manager.html

The Engine and Sql Agent services do run as domain accounts. I've logged in as the engine account and added the certificate to the user account's personal store. I've also given both domain accounts full control to the private key. I've restarted the sql engine, sql browser and sql agent services. I've restarted the server. It still doesn't show up under Protocols for MSSQLSERVER > Properties > Certificate tab.

As for creating the certificate request. I created the certificate request on the database server. I used the MMC snapin for certificates (local computer). I right clicked Personal > All Tasks > Advanced Operations > Create Custom Request. The friendly name is the servers host name (not FQDN). For the subject name, I used the Common name. Key usage options I selected were CRL Signing, Decipher only, Encipher only, Key agreement, Key encipherment. And I selected "Make these key usages critical". For Extended Key Usage, I selected Server Authentication and Client Authentication. I did NOT select "Make the Extended key usage critical". Basic constraints are enabled but left blank. I selected "Make the basic constraints extension critical". The private key is RSA, 2048-bit. Private key is exportable. Hash algorithm is SHA512.

Any help is greatly appreciated.


texts: Sometimes SQL Server Agent Service is automatically disabled

texts:
I am using following SQL Server version:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Sometimes I find that its agent service automatically goes to a disabled state. How I recover such problem?


texts: Gap Locking in Read Committed isolation level in Mysql

texts:
MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb

I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where even Read Committed has to use gap locking.

  READ COMMITTED
  ..... For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

IMHO, only record lock is enough. Can anyone explain the scenario for Gap locking and why mysql does that?


texts: Changing two publications at the same time causes deadlock in distribution database

texts:
I have transactional replication setup.  Let's say three databases on server A replicate to corresponding three databases on Server B.  The databases are identical (same structure for different clients).  Server B also acts as a distributor.  Everything is working fine.  Now I need to change one of the tables (which is also a publication article).  I need to do it in all databases at the same time.  I follow this process (it is scripted):

Drop article for the table I need to modify from the publication
Make the change to my table (it has to be dropped and re-created)
Add article back
Generate snapshot (it possible to have partial snapshot for just the modified article, see good explanation here)

This is where I run into deadlocks.  The deadlock takes place in the distribution database when this stored procedure is executed: sp_MSdrop_subscription.  I don't call this procedure directly, but apparently it gets executed when I run sp_dropsubscription (see step #1 above).  Once again, I modify all publication databases at the same time and they all use the same distribution database, so this is where the problem lies.  The two conflicting statements are captured by the deadlock trace (1222) and apparently come from inside sp_MSdrop_subscription.

First connection runs:

if not exists (select * from dbo.MSsubscriptions    where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or @publication_id is NULL or @independent_agent = 0 ) and
independent_agent = @independent_agent and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
subscription_type = @subscription_type)

Second connection runs:

delete from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or
@publication_id is NULL) and
(@article_id is NULL or
article_id = @article_id) and
Message
((subscriber_id = @subscriber_id and
(subscriber_db = @subscriber_db or @subscriber_id = @virtual)) or
-- Delete virtual anonymous subscription
-- if deleting virtual subscription for a anonymous publication
(@subscriber_id = @virtual and subscriber_id = @virtual_anonymous))

and here is the resource list of what's being locked:

resource-list

keylock hobtid=72057594039959552 dbid=34 objectname=distribution.dbo.MSsubscriptions indexname=ucMSsubscriptions id=lock13563b300 mode=X associatedObjectId=72057594039959552

owner-list
owner id=process2d7241498 mode=X
waiter-list
waiter id=process212b58188 mode=S requestType=wait

keylock hobtid=72057594040025088 dbid=34 objectname=distribution.dbo.MSsubscriptions indexname=iMSsubscriptions id=lock17ff3af80 mode=X associatedObjectId=72057594040025088

owner-list
owner id=process212b58188 mode=X
waiter-list
waiter id=process2d7241498 mode=U requestType=wait

I appreciate any pointers on how to avoid this.  The only work-around I can think of right now is to have separate distribution databases per publication database, but I can't believe that replication doesn't support modifying multiple publishers at the same time.

Thank you!


texts:
In a simple scenario, where I have many measurements table that describe the measurements that comes from a specific protocol and every measurement is linked to the respective parent device, I was looking for an optimization of my database, putting all the measurements into a table and reference from this table the respective device.

Now, keeping in mind that this table is heavily used in read/write and especially in filtering operations, I was looking for a smarter solution...I've found the Django Generic Relations and Reverse Relations, but this approach it doesn't convince me at all because, as now, with a simple select with a where the problem is solved, but using a generic relation, the ORM will generate a lot of inner joins (if I'm not wrong) and this is an aspect that I would want to avoid.

So, anyone with smarter idea?

My goal is to remove tons of if in my code that at this time are used to decide what table it will be queried based on the protocol type, as shown below:

#MODBUS
if smartdevice_device_parent == 'smart_device_modbus':
    ...Select from the right table and serialize the data...

#RS485
elif smartdevice_device_parent == 'smart_device_rs485':
    ...Select from the right table and serialize the data...

#OPC
elif smartdevice_device_parent == 'smart_device_opc':
    ...Select from the right table and serialize the data...

#MQTT
elif smartdevice_device_parent == 'smart_device_mqtt':
    ...Select from the right table and serialize the data...
else:
    return JsonResponse({'success': True, 'message': (_(ERROR)),
                         'data': ''})


texts: Please review my first thought-out attempt at a database schema

texts:
I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL.  I would like, if someone has time, to review my layout and make sure that it is logical.  Tips and criticism aimed at bettering my knowledge is of course welcome! :)

Here is my general goal:
Create a database that will allow for a business with multiple stores to be able to track employee training progress by simply marking date trained and date verified.  Basically, this would be the hierarchy:

Business 1
--Store Location (a)
----Employee
----Employee
----Employee
--Store Location (b)
----employee
----employee
----employee

Business 2
--Store Location (a)
----employee
----employee
--Store Location (b)
----employee
----etc...

This schema should allow for unlimited businesses, stores, and employees.

As far as the training goes, there should be this type of hierarchy:

Business 1
--Employee Class           (Manager, Employee, Salesman, Associate, etc)
----Training Category      (Sales, Stocking, Manufacturing, etc)
------Training Activity    (How to sell X product, How to stock Y aisle, etc)
------Training Activity
------Training Activity
----Training Category
------Training Activity
------Training Activity
--Employee Class
----Training Category
----Training Category

Business 2
--Employee Class
----Training Category
------Training Activity
------Training Activity
------Training Activity
----Training Category
------Training Activity
------Training Activity
--Employee Class
----Training Category
----Training Category

The following things should be variable based on business needs and created upon creation of business's account:

 * Number of Stores
 * Number of Employees
 * Employee Classes
 * Training Categories
 * Training Activities

So the grand questions are:

How close did I get?
How can it be done better?

Here is the Diagram (http://i.stack.imgur.com/V38cr.jpg) :


texts:
I'm designing a database for students. Computer science students here in Switzerland can decide between different specializations, e.g. application development, system engineering etc.

There are students from other professions in this application as well, which have no such specialization. A few examples:

+--------------------+-------------------------+
| Profession         | Specialization          |
+--------------------+-------------------------+
| Computer scientist |                         |
|                    | Application development |
|                    | System engineering      |
|                    | Support                 |
|                    |                         |
| Electrician        |                         |
|                    | none                    |
|                    |                         |
| Janitor            |                         |
|                    | none                    |
|                    |                         |
| Architect          |                         |
|                    | Small buildings         |
|                    | High buildings          |
|                    |                         |
+--------------------+-------------------------+

I hope you get the idea. My question now is, how do I design the database tables with these attributes, since they're dependent of each other? Every user has a profession, some do not have a specialization depending on their profession. So an electrician should not be an application developer, nor should an architect.

My thoughts so far 1

+-----------------------------+
| User                        |
+-----------------------------+
| #id                         |
| profession_id               |
| specialisazion_id, nullable |
+-----------------------------+

Enforce logic through constraint checks

Approach 2

+-------------------+     +----------------+     +------------+
| User              |     | Specialization |     | Profession |
+-------------------+     +----------------+     +------------+
| #id               |  +--| #id            |  +--| #id        |
| username          |  |  | name           |  |  | name       |
| specialization_id |--+  | profession_id  |--+  +------------+
+-------------------+     +----------------+

Manage the logic myself and ensure, that every profession with no specialization has a specialization entry.

Approach 3

+----------------+     +-----------------------------+     +------------+
| Specialization |     | spec_prof                   |     | Profession |
+----------------+     +-----------------------------+     +------------+
| #id            |--+  | #id                         |  +--| #id        |
| name           |  +--| specialization_id, nullable |  |  | name       |
+----------------+     | profession_id               |--+  +------------+
                       +-----------------------------+
                                                |
                                                |
                              +--------------+  |
                              | User         |  |
                              +--------------+  |
                              | #id          |  |
                              | username     |  |
                              | spec_prof_id |--+
                              +--------------+

Somehow all different approaches feel clumsy, dirty. What are the arguments for and against the different approaches? Is there a better way?

And how do I even search for this problem? Is dependent the correct naming?

Any help is greatly appreciated.


texts: How to select data from differents servers with a single query?

texts:
I have a challenge! I need select from 4 different tables from 4 different servers.

Example:

Select * from server1.tableS1.sys.view
Select * from server2.tableS2.sys.view
Select * from server3.tableS3.sys.view
Select * from server4.tableS4.sys.view

How I can make the select in the same query?


texts: When to use JSON or JSONB array vs SQL array in postgres

texts: I am looking for the main points to consider when choosing between a JSON or JSONB array and the regular SQL postgres array. If you know that you will only be inserting one data type into an array, why would you use the SQL array over the JSON array? Thanks


texts: Incorrect string value: '\\xD0\\xA2\\xD0\\x9E\\xD0\\x9E...' for column Username in MySQL DataGrip specific

texts:
I know that such error can be caused by incorrect character set applied to some table, but in my case it's a bit different:
I have some procedure with select, create temporary table and cursor...
The actual problem is that if I do DROP/CREATE procedure from JetBrain's DataGrip and CALL it - error appears. But in case I do DROP/CREATE of SAME procedure without any modification from other software, for example I've used DBForge - CALL works just as expected! What can cause such behavior? I've used DataGrip for years now and this is the first time after which I start to worry about recreating procedures on production database.
Additional information: the error is caused by FETCH of Username column in cursor loop body. In case of commenting that part - everything works. So there is a problem FETCHING Username to my local variable DECLARE Username VARCHAR(300);. Sizes of variables are same.


texts: listen_addresses set to '*' in postgresql.conf disappears every reboot of Ubuntu

texts:
I have set listen_addresses to '*' in /etc/postgresql/9.1/main/postgresql.conf and stopped/started Postgres 9.1 with /etc/init.d/postgresql to "reload" configs.

This works fine and I can access my database with 3rd party tools from my windows computer as expected.

But everytime I restart my virtualized Ubuntu (where Postgres 9.1 is running), this entry listen_addresses completely disappears from postgresql.conf

Where do I have to make this entry that it will survive a reboot of Ubuntu?


texts: join for between multiple tables without common fields

texts:
table E : id, title, datetime
table L : id, place, xpos, ypos
table M : id, title, datetime, contact, agenda
table ER : parentid, childid, parenttype, child type

data in ER : e.id, l.id, e, l -> E object linked to L object
             m.id, l.id, m, l -> M object linked to L object

I want to select all objects from table E and M such that they have a connection to object in L (in table ER) and that object has value of xpos>25 (arbitrary)

select * from L where xpos > 5
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid

I feel I'm missing the basic working of joins and am constructing a wrong query (approach here). Can it be combined in one query? Or do I have to use multiple queries, one for E and one for M to get the results from each of them?


texts: What folders should I check to see if these MongoDB applications are installed on my server?

texts:
I have a CentOS (Linux) server and looked at the /bin directory, but don't find the applications listed below. What folders should I check to see if these applications are installed on my server?

I'm reading a MongoDB book and it states:

After you install or extract MongoDB successfully, you will have the applications show in table 2-1 available in the bin directory (in both Linux and windows).

Table 2-1

--bsondump
--mongo
--mongodb
--mongodump
--mongoexport
--mongofiles
--mongoimport
--mongooplog
--mongoperf
--mongorestore
--mongos
--mongosniff
--mongostat
--mongostop
--mongorestore


texts: Workaround for using functions in restricted system

texts:
I have access to an environment where I can run SELECT statements but cannot call functions. I know what all the code is inside the functions.

The functions are merely lookups (SELECTs and not INSERTs or UPDATEs)

Is there a way to simulate or declare the functions in a local context to get around this problem.?


texts:
I have a procedure that generates an XMLTYPE and I want to validate it against a schema.  The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the procedure as AUTHID DEFINER it gives the error "ORA-31050: Access denied", but when I run it as AUTHID CURRENT_USER it actually returns a validation specific error (I'll deal with that separately).  CURRENT_USER is not an acceptable solution.

My supposition is that CURRENT_USER works because the user has the XMLADMIN role.  Granting the permissions the role includes does not resolve the issue, so it must be the roles ability to bypass the ACLs.

The thing is, querying RESOURCE_VIEW for the ACL that protects the resource shows that it is protected by /sys/acls/all_owner_acl.xml.  DBMS_XDB.getPrivileges shows that the xsd has all the following permissions:

  <read-properties/>
  <read-contents/>
  <write-config/>
  <link/>
  <unlink/>
  <read-acl/>
  <write-acl-ref/>
  <update-acl/>
  <resolve/>
  <link-to/>
  <unlink-from/>
  <dav:lock/>
  <dav:unlock/>
  <dav:write-properties/>
  <dav:write-content/>
  <dav:execute/>
  <dav:take-ownership/>
  <dav:read-current-user-privilege-set/>

Using DBMS_XDB.getAclDocument shows a principal of dav:owner has all privileges, so that must not be enough to allow the owner of the schema to create schema based XML.  With this thought in mind I created a block to run DBMS_XDB.createResource creating a new ACL.  I can successfully create the ACL and from SQLDeveloper I can see that it exists in the location I created it in.

There are any number of places I could be going wrong in this process, so the core of what I am looking for is this:

What must be in place to validate a XMLTYPE against a schema?

=== Update 4/3/2013 ===
I can set the acl for my xsd file to /sys/acls/all_all_acl.xml and back to /sys/acls/all_owner_acl.xml.  As before, neither of these resolve the permissions issue.  I also tried a createResource using the acl definition copied from all_owner_acl.xl and using the same path as those files of /sys/acls/.  This at least successfully sets the ACL.  I have run it for all my XSDs and queried RESOURCE_VIEW to confirm that they are set to the new ACL.  After all this though, createSchemaBasedXML still gives an access denied error.  Is my ACL correct or might there be another issue?

<acl xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
   xmlns:dav="DAV:"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
      http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
       <ace>
         <grant>true</grant>
         <principal>MY_ORACLE_USER</principal>
         <privilege>
           <all/>
         </privilege>
       </ace>
     </acl>

=== Update 4/9/2013 ===
I have an anonymous block that can successfully validate XML based on a schema.  This again points to a permissions issue that allows this to work when roles are enabled, but not when they are not available.

== Update 4/12/2013 ===
Every bit of evidence I get seems to indicate that there is something wrong with my ACL or perhaps more likely with the way I set the ACL.  Taking the XDBADMIN role away from the user causes the anonymous block to fail with an access denied error even though I have granted all the permissions the role gives according to dba_tab_privs.  My setACL follows this form:

DBMS_XDB.setACL('/sys/schemas/MY_ORACLE_USER/account.xsd', '/sys/acls/acl_acc.xml');

A complete test case can be found on Oracle Communities.


texts: How to select the largest value of the same column in multiple tables?


texts: select rows older then column DATETIME - INT value of other column in single query

texts:
I have 2 columns in my table:

(DATETIME)lastsendattempt
(INT)sendgap

I know I can select all rows that have a "lastsendattempt" DATETIME which is older then the current time. But, am I able to use the value of another column to select the rows that are older than the current time - that sendgap value ?

I want it all in a single query instead of getting the sendgap value in a query and then doing another one with a datetime value

Something like...

SELECT * FROM table WHERE lastsendattempt <= (2012-04-10 01:30:15)-(INT)sendgap


texts: Link up an ms access database to SQL Server 2008 R2?

texts:
I've installed the OLE DB Provider for JET by downloading this and installing it.

I closed and reopened Microsoft SQL Studio, and connected to my SQL Server Instance.

In <instance-name> -> Server Objects -> Providers according to this, there is now supposed to be an item named  Microsoft.ACE.OLEDB.12.0 or Microsoft.Jet.OLEDB.4.0 item, but there is not.

Do I need to get this somewhere else?


texts: How would one start the secondary mirror instance if the principal SQL server instance went off line?


texts: Query Cache utilisation ~50% Qcache_lowmem_prunes still high

texts:
I ran two tests, each running for about a week

Test 1

Query Cache Settings

query_cache_limit: 50K
query_cache_size: 128M
query_cache_min_res_unit: 1K

Results

Qcache_free_blocks: 6,004
Qcache_free_memory: 93,598,504
Qcache_hits: 377,204,411
Qcache_inserts: 13,056,941
Qcache_lowmem_prunes: 384,610
Qcache_not_cached: 5,794,907
Qcache_queries_in_cache: 16,006
Qcache_total_blocks: 38,724

Results (Calculated Values)

Utilisation: 40.7%
Removal Ratio: 2.9%
Hit Rate: 95.2%
AVG Query Size: 4,012
Frag Indicator: 15.5%

Utilisation = 100-Qcache_free_memory*100/query_cache_size
Removal Ratio = Qcache_lowmem_prunes*100/Qcache_inserts
Hit Rate = Qcache_hits*100/(Qcache_hits+Qcache_inserts+Qcache_not_cached)
AVG Query Size = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
Frag Indicator = Qcache_free_blocks*100/Qcache_total_blocks

Test 2

Query Cache Settings

same as above except

query_cache_min_res_unit: 2K

Results

Qcache_free_blocks: 1,287
Qcache_free_memory: 70,082,320
Qcache_hits: 223,742,107
Qcache_inserts: 7,104,530
Qcache_lowmem_prunes: 186,235
Qcache_not_cached: 3,218,864
Qcache_queries_in_cache: 20,802
Qcache_total_blocks: 43,424

Results (Calculated Values)

Utilisation: 55.6%
Removal Ratio: 2.6%
Hit Rate: 95.6%
AVG Query Size: 4,217
Frag Indicator: 3.0%

now for the question....

As expected, when I lowered query_cache_min_res_unit, fragmentation went up. What really has me confused is why is the removal ratio still so high even when utilisation is low?


texts: New and old passwords on same MySQL database?

texts:
Is it possible to set up a user in MySQL 5.5 with a new password whilst keeping existing users with the old type of passwords? By old and new passwords I mean the style of hash.

I was thinking I could try specify the hash directly but I'm struggling to get that working (I'm new MySQL).

Unfortunately I can't update all users to the new type of passwords as it would break access to a system I can't control. I also need the default new account to use the old style hash.


texts: I need to migrate a MySQL database to SQL Server 2012. Found enough information about it -- looks like it can be done either with SSMA tool or via Data Import utility in SQL Server Management Studio. What I'm still confused about is whether I have to perform this directly from the server that hosts my SQL Server database or I can do it from my client PC running SQL Server Management Studio.


texts: Modelling a database structure for multiple user types and their contact information

texts:
I'm designing a database that will store users of varying types. Predominantly (but not exclusively) they will be Actors, Directors and Writers. Currently there are just four user types that are of relevance. There is a outside chance that this number may increase, but the probability is low - and in such a case would be by a very small number.

The plan is to have a users table responsible pretty much solely for logging into the site (name, email and password columns plus one or two others such as whether they've been approved, and updated_at), and additional tables for each of the respective user types that each have their own unique set of columns. Only actors, for example, will have an ethnicity column, only Directors would have a bio column, and only Writers would need to provide their location. However, as I've not managed a database of this complexity before, I'm wondering how to organise a couple of aspects:

Firstly, users can be any one, or any combination, of the above types. So I understand I would need something like (for example) a director_user table with director_id and user_id columns. Would this then be sufficient to be able to filter all users by role type and so on?

Secondly, most users will the option of a twitter profile and phone number. And all actors will have to include at least one URL for any of their other online actor profiles; currently there are three that they can include, but this number may increase. Am I right in assuming that a separate table for each of the possible profiles/contact methods is an optimal way to organise data?


texts:
I have 2 tables:
sales transaction tablem payment (reference_no) can be duplicate

-----------------------------------
| id | reference_no | invoice_amt |
| 1  | inv001       | 100.00      |
| 2  | inv001       | 30.00       |
| 3  | inv002       | 150.00      |
| 4  | inv003       | 50.00       |

payment tablem (reference_no) also  can be duplicate.

-----------------------------------
| id | reference_no | payment_amt |
| 1  | inv001       | 130.00      |
| 2  | inv002       | 30.00       |
| 3  | inv002       | 50.00       |
| 4  | inv002       | 50.00       |
| 5  | inv002       | 20.00       |
| 6  | inv003       | 20.00       |

I want to match so those payment_amt not tally with invoice_amount will appear.
Example:

inv001 total sum invoice_amt is 130 and payment_amt is 130

inv002 total sum invoice_amt is 150 and payment_amt is 150

inv003 total sum invoice_amt is  50 and payment_amt is 20

So it will only display inv003 since the SUM of the invoice amount is not same with total payment_amt.

How to write the sql for this display?


texts: SQL Server 2016 vs 2012 insert performance

texts:
I have two SQL Server instances on the same server:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition
(64-bit)
Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) -
13.0.4451.0 (X64) Enterprise Edition (64-bit)

sp_configure results are the same on both instances (except of new 2016 options).
I have created new databases on both instances on the same disk folder. Autogrowth parameters are the same.
Autocreate and autoupdate statistics options are turned off.
Then I have made a test with 10000 inserts into a heap:

set nocount on
go

create table dbo.TestInsert ( i int not null, s varchar(50) not null )

declare @d1 datetime, @d2 datetime, @i int
set @d1 = getdate()
set @i = 1

while @i <= 10000
begin
  insert into dbo.TestInsert ( i, s ) select @i, replicate( 'a', 50 )
  set @i = @i + 1
end

set @d2 = getdate()
select datediff(ms, @d1, @d2)
drop table dbo.TestInsert

Result 1

The average test time

2012 - 530 ms
2016 - 600 ms

So, 2016 is about 11% slower.

Then I have made an SQL Profiler trace with results saved to the table to see the single insert duration in microseconds.

Result 2

The histogram of a single insert duration 2012 vs 2016:

Transaction logs' growth from sys.dm_io_virtual_file_stats is:

2012 - 5174784 bytes
2016 - 5171200 bytes

During these tests both instances are started. But one test is running only at the one instance each time. I have allocated 8Gb RAM per instance. The query plans are the same. It would be interesting to run each instance on its own box. But probably the one machine is better, because here we have no hardware and enviromnent hidden differences.

Questions

Why is 2016 slower?
May someone reproduce this test?


texts: The client need 'SUPER' privileges to the reports DB in order to create and maintain my Functions

texts:
I have problem with one of my client

Provided super privileges to the client James on  'Reports ' database , but the problem is the client need  'SUPER' privileges to the reports DB in order to create and maintain my Functions.  So I'm still unable to move forward...

BUT, I still need SUPER privileges to the reports DB in order to create and maintain my Functions.  So I'm still unable to move forward...                                 (This is what client responding , he can login but he can't create nor maintain functions on reports db server  )

After providing super privileges the client cant maintenance any of this functions nor he can create functions , how to work on this .


texts:
I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version to the same version number as the fresh install.

Basically I want to make sure that the upgrade scripts worked properly and all the proper columns and constraints are in the upgraded version just as they are in fresh install. I thought a simple diff/sdiff would be good enough for this but I ran into the problem with that: Column order.

The column order between the fresh install and the upgrade are different giving many false positive "differences". It is easy to see when looking at the output that the same column is simply in a different spot but with hundreds of tables it would take a long time to check and identify these false positives.

Is there a better way to compare these two dumps that will ignore column order (or a way to force the dumps to have columns in alphabetical order or something similar) or an entirely different approach that would work better. I am admittedly fairly new to MySQL and am just learning as I go so any help would be much appreciated.

Thanks.


texts: Same schema, same data, same execution plan?

texts:
I have a T-SQL stored procedure in SQL Server 2014 SP2 that takes 3x as long on the server as it does from my local environment.

I restored my local database to what was on the server, so schema and data are the same.

I'm a programmer, not really a DBA, so bear with me on this.

The execution plans seem the same until you look at the XML. On my local environment, a Stream Aggregate is used for one of the queries to make a distinct select, while on the server, it looks like a Sort is used with a hash.

It appears that the fact that the server has less processors than my (beast) local machine is contributing to the problem. Every query takes a little longer on the server. Could this be the reason that the sort/hash is chosen on the server instead of the Stream Aggregate?

Is there a way to force the Stream Aggregate?

Here is the SQL for the stored proc: http://pastebin.com/kkXM3Bsf

Here is the execution plan for my local environment: http://pastebin.com/0u7SHxnM

Here is the execution plan for the server: http://pastebin.com/9sE9DfM0


texts:
can someone tell me how to read the mysqld.log?

I'm curious about the third column.
Because every time i start/stop the mysql it give different output. I hope someone can explain to me :).

Below is the example:

What is 18614?

2018-08-09 23:08:03 18614 [Note] InnoDB: Using CPU crc32 instructions
2018-08-09 23:08:03 18614 [Note] InnoDB: Initializing buffer pool, size = 80.0G
2018-08-09 23:08:07 18614 [Note] InnoDB: Completed initialization of buffer pool
2018-08-09 23:08:08 18614 [Note] InnoDB: Highest supported file format is Barracuda.
2018-08-09 23:10:36 18614 [Note] InnoDB: 128 rollback segment(s) are active.
2018-08-09 23:10:36 18614 [Note] InnoDB: Waiting for purge to start
2018-08-09 23:10:36 18614 [Note] InnoDB: 5.6.36 started; log sequence number 6889901435830
2018-08-09 23:10:36 7fc3935fe700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
2018-08-09 23:10:36 18614 [Note] Server hostname (bind-address): '*'; port: 33060
2018-08-09 23:10:36 18614 [Note] IPv6 is available.
2018-08-09 23:10:36 18614 [Note]   - '::' resolves to '::';
2018-08-09 23:10:36 18614 [Note] Server socket created on IP: '::'.
2018-08-09 23:10:36 18614 [Note] Event Scheduler: Loaded 0 events
2018-08-09 23:10:36 18614 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36-log'  socket: '/var/lib/mysql/mysql.sock'  port: 33060  MySQL Community Server (GPL)

What is 20239 ?

2018-08-09 23:12:23 20239 [ERROR] Plugin 'InnoDB' init function returned error.
2018-08-09 23:12:23 20239 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-08-09 23:12:23 20239 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-08-09 23:12:23 20239 [ERROR] Aborting

What is 100508?

2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'MyISAM'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'CSV'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'MEMORY'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'MRG_MYISAM'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'sha256_password'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'mysql_old_password'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'mysql_native_password'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'binlog'
2018-08-19 23:35:32 100508 [Note] /usr/sbin/mysqld: Shutdown complete

What is 2283?

2018-08-19 23:38:15 2283 [Note] InnoDB: 5.6.36 started; log sequence number 6952861241977
2018-08-19 23:38:15 7f5dbffff700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
2018-08-19 23:38:15 2283 [Note] Server hostname (bind-address): '*'; port: 33060
2018-08-19 23:38:15 2283 [Note] IPv6 is available.
2018-08-19 23:38:15 2283 [Note]   - '::' resolves to '::';
2018-08-19 23:38:15 2283 [Note] Server socket created on IP: '::'.
2018-08-19 23:38:16 2283 [Note] Event Scheduler: Loaded 0 events
2018-08-19 23:38:16 2283 [Note] /usr/sbin/mysqld: ready for connections.

Thank you.


texts: Get physical disk id and drive letters from management studio?

texts:
Can anyone tell me how I can go about getting the physical disk id and drive letters of the local server from within management studio?  I know I can grab the drive letters with xp_fixed drives and extrapolate to the full disk size with xp_cmdshell and fsutil, but what I want is something like the below:

╔══════════╦═════════════╦════════╦════════════╗
║ Drive_id ║ Dive Letter ║  Size  ║ Free_Space ║
╠══════════╬═════════════╬════════╬════════════╣
║        0 ║ C           ║  10240 ║       8000 ║
║        0 ║ E           ║ 102400 ║      25000 ║
║        1 ║ F           ║ 102400 ║      22000 ║
╚══════════╩═════════════╩════════╩════════════╝

Any ideas?

EDIT:
Just to be clear I have all of the pieces of the puzzle in the below code EXCEPT the physical drive_id.  If anyone can think of a way to add it to the code below that would be fantastic - if I need to blow all that away and redo it to get the information I'm after I can live with that too.

Code So Far:

SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#FreeDiskSize') IS NOT NULL DROP TABLE #FreeDiskSize

CREATE TABLE #FreeDiskSize (name varchar(5), available_mb int)

INSERT #FreeDiskSize(name,available_mb)
EXEC master..XP_FIXEDDRIVES
DECLARE @Drive VARCHAR(5), @CMD VARCHAR(1000), @pos SMALLINT

IF OBJECT_ID(N'tempdb..#TotalDiskSize') IS NOT NULL DROP TABLE #TotalDiskSize

CREATE TABLE #TotalDiskSize (TotalBytes VARCHAR(1000), Drive VARCHAR(5))

DECLARE Drive_name CURSOR FOR

SELECT name FROM #FreeDiskSize
OPEN Drive_name
FETCH NEXT FROM Drive_name INTO @Drive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD='MASTER..XP_CMDSHELL ' + ''''+ 'FSUTIL VOLUME DISKFREE ' + @Drive + ':| find '+ '"Total # of bytes"'+''''
INSERT #TotalDiskSize(TotalBytes) EXEC (@CMD)
UPDATE #TotalDiskSize SET Drive=@Drive WHERE Drive IS NULL
FETCH NEXT FROM Drive_name INTO @Drive
END
CLOSE Drive_name
DEALLOCATE Drive_name
DELETE FROM #TotalDiskSize WHERE TotalBytes IS NULL
SELECT @pos=charindex(':',TotalBytes) FROM #TotalDiskSize

SELECT b.Drive as Drive, CONVERT(BIGINT,(RIGHT(b.TotalBytes,(LEN(b.TotalBytes)-@pos))))/1073741824 AS TOTAL_Drive_SPACE_GB, a.available_mb/1024 AS AVAILABLE_SPACE_GB
FROM #FreeDiskSize a WITH (NOLOCK)
INNER JOIN #TotalDiskSize b WITH (NOLOCK)ON a.name=b.Drive


texts:
We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table.  Replication converted that to 7.5 million individual update statements.  When my alerting notified me that our publication was behind our threshold, I started to examine the issue.

I discovered that the updated was executed and it would have taken a few days to chew through those update statements.  So I decided to see if we could skip over those records that it was trying to process.  Using the system tables and stored procedure I was able to determine the period of time that the updated was executed.  Once I found the last xact_seqno, I stopped the distribution agent, and did the update manually to the subscriber database.  I then executed sp_setsubscriptionxactseqno, to skip past all of those 7.5 million transactions.  When I started up the Distribution Agent, it seemed to have worked and was able to process the remaining transactions.

For good measure I use Redgate’s data compare to see if the data was messed up, but I was missing about 24 records (which could have been not there originally because I didn’t set it up).

My question is was that the right way to fix it?  How are you always assured to get the next Xact_seqno?  Do you order the transactions by Xact_Seqno or entry_time?


texts:
A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII chars. Two examples are 1450123410123 and 21347854123024Asd_uhA.

Currently, I'm storing this field as a VARCHAR with a PRIMARY KEY, and am worried about the memory usage of the index. I'm thinking of splitting this field into two columns, a BIGINT and a VARCHAR. Will the PRIMARY KEY created as a combination of these two columns consume less memory?

Update: Here are the two possibilities.

Please note that I care about inserts and updates. I will have only one huge select to export the data after a long time.

CREATE TABLE without_split (
  id varchar(21) CHARSET=ascii COLLATE ascii_bin NOT NULL,
  content text NOT NULL
  PRIMARY KEY id
);

CREATE TABLE with_split (
  id_prefix bigint unsigned NOT NULL,
  id_suffix varchar(7) CHARSET=ascii COLLATE ascii_bin NOT NULL,
  content text NOT NULL
  PRIMARY KEY (id_prefix, id_suffix)
);


texts: Can I use MySQL-SERVER tunning variables in MYSQL CLUSTER?

texts:
When tunning MySQL-SERVER for best performance, the edition is done down my.cnf file.

Such as:

max_connections = 500
wait_timeout = 600
sort_buffer_size = 2M

Can I improve MySQL CLUSTER performance with those editable variables down [mysqld] tag of config.ini of mgmt node or my.cnf of ndbd node?

The main reason of this question is that I'm getting ERROR 1205: Lock wait timeout exceeded; try restarting transaction when importing a 1.2 G .sql file. I can't find any solution to this refering to MySQL CLUSTER so I want to try with the wait_timeoutvariable, but I don know if it's compatible with.

Will it accept those variables?


texts: What is the best way to update a table from a JSON string?

texts:
I'm fiddling around with JSON and SQL, and I need to update a record based on a new JSON string.

Here's what I've got, but to be honest, It seems like an awful lot of code:

    create procedure proc_alter_contact (@json nvarchar(max))

    as

    with new_values as (select * from
            openjson(@json)
            WITH
            (
            email varchar(255)          '$.Email',
            Prefix varchar(10)          '$.Prefix',
            FirstName varchar(255)      '$.FirstName',
            MiddleInitial varchar(10)   '$.MiddleInitial',
            LastName varchar(255)       '$.LastName',
            Suffix varchar(10)          '$.Suffix',
            Title varchar(255)          '$.Title',
            Company varchar(255)        '$.Company',
            Address1 varchar(255)       '$.Address1',
            Address2 varchar(255)       '$.Address2',
            City varchar(255)           '$.City',
            State varchar(10)           '$.State',
            Zip varchar(5)              '$.Zip',
            Zip_4 varchar(4)            '$.Zip4',
            Phone varchar(55)           '$.Phone',
            Extension Varchar(55)       '$.Extension',
            Unregistered bit            '$.Unregistered'
        ))

    update t1
    set
    t1.email            = t2.email,
    t1.prefix           = t2.prefix,
    t1.FirstName        = t2.FirstName,
    t1.MiddleInitial    = t2.MiddleInitial,
    t1.LastName         = t2.LastName,
    t1.Suffix           = t2.Suffix,
    t1.Title            = t2.Title,
    t1.Company          = t2.Company,
    t1.Address1         = t2.Address1,
    t1.Address2         = t2.Address2,
    t1.City             = t2.City,
    t1.State            = t2.State,
    t1.Zip              = t2.Zip,
    t1.Zip_4            = t2.Zip_4,
    t1.Phone            = t2.Phone,
    t1.Extension        = t2.Extension,
    t1.Unregistered     = t2.Unregistered
    FROM announcement_contact t1
    inner join new_values t2 on t1.email = t2.email;

here's an example JSON string:

    exec proc_alter_contact

    @json =
    '{
    "Email": "WMogh@starfleet.gov",
    "Prefix":null,
    "FirstName": "Worf",
    "MiddleInitial": "",
    "LastName": "Mogh",
    "Suffix": "Son Of",
    "Title" :"LT Commander",
    "Company": "Starfleet",
    "Address1": "1 Academey Drive",
    "Address2": "suite 1701",
    "City": "San Francisco",
    "State": "CA",
    "Zip" :"59312",
    "Zip_4": "9577",
    "Phone": "5469781769",
    "Extension":"9",
    "Unregistered": 0
    }'

Question Is there are more efficient, or more sensible way to do this?


texts:
This is my first post here, so I hope its clear enough. I want to make an application for people who sell, buy, rent and fix a certain product.

I need to create a database that would keep the data of the product and of the accounts (with logins and personal information). My problem is here, an “account” can be a seller, a buyer, a renter, a fixer, it can be all, none, or some. The sellers, buyers, renter and fixer would have different fields though, like a seller would be able to post products, and have save some information that only a seller would have, a fixer would just be able to put his listing, but a seller, can also be a fixer. A buyer would be able to sell products too and favorite others. The difference between a seller and a buyer is that a seller has to be a company, etc…

My idea of the databases would be something like this:

Product

product_id
product_name
owner_id (account_id)

Accounts

account_id
email
password

Seller

seller_id
account_id
business_name
address
[some fields that only sellers would have...]

Buyer

buyer_id
account_id
first_name
last_name
address
[some fields that only buyer would have...]

Renter

renter_id
account_id
business_name
address
[some fields that only renter would have...]

Fixer

fixer_id
account_id
business_name
address
years_experience
[some fields that only fixers would have...]

Then other tables like account_favorite (to keep info on which products have been chosen as favorite).

Now, I feel that the way I'm doing it is not the correct way. Also thinking that if in the future, I think about something else like for example "collector" I would have to create a new table. Because this is an application that will be used by a lot of people I have to care about speed, but I also have to care about people maintaining and analyzing the data. I hope this post was clear, if it's not, please ask me.


texts: How can I use a full-text search on a jsonb column with Postgres?

texts:
So i have a jsonb column that has entries like this: https://pastebin.com/LxJ8rKk4

Is there any way to implement a full-text search on the entire jsonb column?


texts:
I'm trying to import a large one giga tsv file names bouffe.csv. It is in ̀ mongo.../bin but seems there is a syntax error :

> ./mongoimport -d db_name -c collection_name --type tsv --file bouffe.csv -f --numInsertionWorkers 8 --headerline

2017-12-05T22:11:01.064+0100 E QUERY    [thread1] SyntaxError: expected expression, got '.' @(shell):1:0

I am not able to handle it, I am quite new to this database system.

Here is an overview :


texts: MySQL query optimization with aggregate ORDER BY

texts:
I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items."

The schema is as follows:

CREATE TABLE `t1` (
    `imps` bigint(20) DEFAULT NULL,
    `n_id` int(11) NOT NULL,
    `b_id` bigint(20) NOT NULL,
    `date` date NOT NULL,
    `m1` bigint(20) NOT NULL,
    `m2` bigint(20) NOT NULL,
    `is_b` tinyint(1) NOT NULL,
    `i_id` varchar(128) NOT NULL,
    KEY `idx1` (`b_id`,`date`,`i_id`,`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(This table is split into 256 partitions by HASH(b_id), in case that matters)

A representative query:

SELECT i_id, n_id, SUM(m1), SUM(m2)
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
ORDER BY (SUM(m1) + SUM(m2)) DESC
LIMIT 10 OFFSET 0

And the EXPLAIN output:

+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | idx1          | idx1 | 8       | const | 1395772 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+

On a table with ~110M records these queries take > 10s to execute. I believe the SUM function in the ORDER BY clause forces the entire resultset satisfied by the WHERE clause to be written to disk, hence the "Using temporary; Using filesort" messages.

Do I have any recourse in improving the performance of these queries short of redesigning the schema and application?

Edit: Here is a SQLFiddle with the schema and dummy data: http://sqlfiddle.com/#!2/beb63/1


texts: TOP(1) BY GROUP of very huge (100,000,000+) table

texts:
Setup

I have a huge table of ~115,382,254 rows.
The table is relatively simple and logs application process operations.

CREATE TABLE [data].[OperationData](
    [SourceDeciveID] [bigint] NOT NULL,
    [FileSource] [nvarchar](256) NOT NULL,
    [Size] [bigint] NULL,
    [Begin] [datetime2](7) NULL,
    [End] [datetime2](7) NOT NULL,
    [Date]  AS (isnull(CONVERT([date],[End]),CONVERT([date],'19000101',(112)))) PERSISTED NOT NULL,
    [DataSetCount] [bigint] NULL,
    [Result] [int] NULL,
    [Error] [nvarchar](max) NULL,
    [Status] [int] NULL,
 CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED
(
    [SourceDeviceID] ASC,
    [FileSource] ASC,
    [End] ASC
))

CREATE TABLE [model].[SourceDevice](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_DataLogger] PRIMARY KEY CLUSTERED
(
    [ID] ASC
))

ALTER TABLE [data].[OperationData]  WITH CHECK ADD  CONSTRAINT [FK_OperationData_SourceDevice] FOREIGN KEY([SourceDeviceID])
REFERENCES [model].[SourceDevice] ([ID])

The table is clustered at around 500 clusters and on a daily base.

Also, the table is well indexed by PK, statistics are up-to-date and the INDEXer get defraged every night.

Index based SELECTs are lightning fast and we had no problem with it.

Problem

I need to know the last (TOP) row by [End] and partitioned by [SourceDeciveID]. To get the very last [OperationData] of every source device.

Question

I need to find a way to solve this in a good way and without bringing the DB to the limits.

Effort 1

The first try was obvious GROUP BY or SELECT OVER PARTITION BY query. The problem here is also obvious, every query has to scan over very partition order/find the top row. So the query is very slow and has a very high IO impact.

Example query 1

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY [SourceDeciveID] ORDER BY [End] DESC) AS rn
   FROM [data].[OperationData]
)
SELECT *
FROM cte
WHERE rn = 1

Example query 2

SELECT *
FROM [data].[OperationData] AS d
CROSS APPLY
(
   SELECT TOP 1 *
   FROM [data].[OperationData]
   WHERE [SourceDeciveID] = d.[SourceDeciveID]
   ORDER BY [End] DESC
) AS ds

FAILED!

Effort 2

I created a help table to always hold a reference to TOP row.

CREATE TABLE [data].[LastOperationData](
    [SourceDeciveID] [bigint] NOT NULL,
    [FileSource] [nvarchar](256) NOT NULL,
    [End] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_LastOperationData] PRIMARY KEY CLUSTERED
(
    [SourceDeciveID] ASC
)

ALTER TABLE [data].[LastOperationData]  WITH CHECK ADD  CONSTRAINT [FK_LastOperationData_OperationData] FOREIGN KEY([SourceDeciveID], [FileSource], [End])
REFERENCES [data].[OperationData] ([SourceDeciveID], [FileSource], [End])

To fill up the table a created a trigger to always add/update the source row if higher [End] column is inserted.

CREATE TRIGGER [data].[OperationData_Last]
   ON  [data].[OperationData]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    MERGE [data].[LastOperationData] AS [target]
    USING (SELECT [SourceDeciveID], [FileSource], [End] FROM inserted) AS [source] ([SourceDeciveID], [FileSource], [End])
    ON ([target].[SourceDeciveID] = [FileSource].[SourceDeciveID])

    WHEN MATCHED AND [target].[End] < [source].[End] THEN
        UPDATE SET [target].[FileSource] = source.[FileSource], [target].[End] = source.[End]

    WHEN NOT MATCHED THEN
        INSERT ([SourceDeciveID], [FileSource], [End])
        VALUES (source.[SourceDeciveID], source.[FileSource], source.[End]);

END

The problem here is, that it also has a very huge IO impact and I don't know why.

As you can see here in the query plan it also executes a scan over the whole [OperationData] table.

It has a huge overall impact on my DB.

FAILED!


texts: Do I need to create an index on the_id / ObjectId field in a mongodb collection if I want to order or filter documents by their creation date?

texts:
The ObjectId value contains a timestamp. Actually, it begins with a timestamp (
https://docs.mongodb.com/manual/reference/method/ObjectId/#ObjectId ). All ObjectId values have a getTimeStamp() method that returns the creation time ( https://docs.mongodb.com/manual/reference/method/ObjectId.getTimestamp/#objectid-gettimestamp ). By default, document identifiers are ObjectId values in MongoDb. A unique index is created for the _id field in every collection. It would be advantageous if MongoDb could use this index (partially) to effectively filter and sort documents by their creation date stored in the ObjectId values.

However, I clould not find information about this anywhere. I know for a fact that MongoDb can use compound (multi-field) indexes partially to speed up filter/sort operations (MongoDb use indexes partially) But this question is somewhat different. The primary key's unique index is not a compound (multi-field) index. But it shares some properties with multi-field indexes. In particular, the order of ObjectId values and the order of creation dates are the same. So in theory, it would be possible to use it as a partial index. I just don't know if MongoDb can do that or not. Of course, it also depends on the index type. If the primary key's index is implemented with a hash, then it probably cannot be used for sorting and filtering, only for lookups.

Can anybody tell, if I need to create a separate "created" datetime field and an index on it, to be able to effectively sort and order documents by the creation date? (I'm using mongodb 4.2)


texts:
I am using barman for bsackup version 2-1.1 and restore postgresql

first after 2 restore that was successfully
I perform some show-backup on other backup , and I notices all backup return this errors: ( we have more then 50 backup's )

barman show-backup main-db-server  20170804T085002
EXCEPTION: ('Invalid LSN: %s', '00000001000000000000002B')
See log file for more details.

please help me to understand what is the problem here?
how it can be that all 50 backups suddenly are corrupted ?

it is no make sense !!!

from /var/log/barman I see the following

2017-08-04 10:37:47,824 [4256] barman.command_wrappers DEBUG: Command:
'command(){ gzip -c -d > "$2" < "$1";}; command \'/var/lib/barman/main-db-
server/wals/00000008.history\' \'/var/lib/barman/main-db-
server/wals/.00000008.history.tQDDSb.uncompressed\''
2017-08-04 10:37:47,836 [4256] barman.command_wrappers DEBUG: Command return
code: 0
2017-08-04 10:37:47,836 [4256] barman.command_wrappers DEBUG: Command
stdout:
2017-08-04 10:37:47,836 [4256] barman.command_wrappers DEBUG: Command
stderr:
2017-08-04 10:37:47,837 [4256] barman.cli ERROR: ('Invalid LSN: %s',
'00000001000000000000002B')
See log file for more details.
Traceback (most recent call last):
 File "/usr/lib/python2.7/site-packages/barman/cli.py", line 1041, in main
 p.dispatch(pre_call=global_config)
 File "/usr/lib/python2.7/site-packages/argh/helpers.py", line 47, in
 dispatch
 return dispatch(self, *args, **kwargs)
 File "/usr/lib/python2.7/site-packages/argh/dispatching.py", line 121, in
 dispatch
   for line in lines:
   File "/usr/lib/python2.7/site-packages/argh/dispatching.py", line 197, in
  _execute_command
   for line in result:
   File "/usr/lib/python2.7/site-packages/argh/dispatching.py", line 153, in
  _call
   result = args.function(args)
   File "/usr/lib/python2.7/site-packages/barman/cli.py", line 550, in
   show_backup
   server.show_backup(backup_info)
   File "/usr/lib/python2.7/site-packages/barman/server.py", line 1688, in
   show_backup
   backup_ext_info = self.get_backup_ext_info(backup_info)
   File "/usr/lib/python2.7/site-packages/barman/server.py", line 1674, in
   get_backup_ext_info
   forked_after=backup_info.end_xlog)
   File "/usr/lib/python2.7/site-packages/barman/server.py", line 1853, in
   get_children_timelines
   self.backup_manager.compression_manager)
   File "/usr/lib/python2.7/site-packages/barman/xlog.py", line 381, in
   decode_history_file
   switchpoint=parse_lsn(contents[1]),
   File "/usr/lib/python2.7/site-packages/barman/xlog.py", line 247, in
   parse_lsn
   raise ValueError('Invalid LSN: %s', lsn_string)
   ValueError: ('Invalid LSN: %s', '00000001000000000000002B')

my sanity on barman server

    Server main-db-server:
    PostgreSQL: FAILED
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (interval provided: 1 day, latest backup age: 1
    hour, 54 minutes, 55 seconds)
    compression settings: OK
    failed backups: FAILED (there are 4 failed backups)
    minimum redundancy requirements: OK (have 69 backups, expected at least
     0)
    ssh: OK (PostgreSQL server)
    not in recovery: OK
    archiver errors: OK

my crontab:

crontab -l
30 23 * * * /usr/bin/barman backup main-db-server
 * * * * * /usr/bin/barman cron

I use rsync as appears in postgresql file

 archive_command = 'rsync -a %p barman@109.56.34.32:/var/lib/barman/main-db-server/incoming/%f'


texts: phpMyAdmin -- how do I figure out which DB these connections and processes belong to?

texts:
I've got about 8 databases on one server, mostly corresponding to WordPress sites. I'm using shared hosting that does not give us superuser permissions. When I log into phpMyAdmin (version 3.5.2) and head to the Status -> Monitor tab, I see more connections open at any given time than I might expect. What I want to do is figure out which sites/databases are causing this to happen.

Google brought me to the "show full processlist" command, but my understanding is that it's not actually going to help unless I have superuser permissions, which I do not have. The command does run, but it doesn't come back with any information. I saw that there were also general and "slow query" logs, but again that seems like something I can't actually access with the current user permissions. Given that, it actually seems a little odd to me that it shows me as much as it does on the "status" pages. I'm just not technically proficient enough to be able to parse anything meaningful out of what's there.

Perhaps the better route is to search for a way to track this from the WordPress end of things?

Bear in mind that I'm very new to the world of administrating databases when replying. Thanks!


texts: Can I move SQL Server backup files on a running database?

texts:
Our backup drive is filling up with TRN and BAK files. I have administrative privileges on the server, but I don't have a login to the database.

Can I move the oldest files off to somewhere else without causing service interruption? Which ones?


texts: Produce fixed length output to file regardless of the length of data in each column/row

texts:
Not sure about this as I'm kind new to this.
I need to output rows to fixed length files format.

The table's columns are ID, Name & Desc. The output should be the same length for each row regardless of the data length. Name in one row can be 5 chars and another can be 7 but in the end the file output should be 15.

So I believe the idea would be, I'll define the output length for each column and then output the column data and fill the difference between the data and the column length with ' '(spaces).

Not sure how to do that. I've read about rpad but it requires you know the length of the column date which is unknown to me.


texts:
I added the user myuserto Postgres.

Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres.

Then I tried to give all rights to access and modify mydatabase to myuser. I logged into psql as user postgres:

psql -d template1 -U postgres

and then I ran this query:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser

Now I can use myuser to log, but if I try a simple query I get this error:

ERROR:  permission denied for relation table_name

Am I missing something? Can you help me solve that?


texts: Create a Postgresql trigger that updates column with PK of other table

texts:
Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback.
I'd like to update a column (village_id) every time a new row is entered or updated with the primary key value of a different table (which is not connected as a foreign key directly). See below for an outline of my tables and trigger + function.
Unfortunately, after using the code below I received an error each time I was entering a new row with information. I used an UPDATE statement, but I also considered using an insert into statement, but wasn't sure what is more preferable. Thank you in advance.
CREATE TABLE register_table
(
  register_id integer CONSTRAINT register_id_pk PRIMARY KEY,
  village_id integer NOT NULL
);

CREATE TABLE er_table
(
  er_id integer CONSTRAINT er_id_pk PRIMARY KEY,
  register_id integer NOT NULL,
  village_id integer NOT NULL
);

CREATE OR REPLACE FUNCTION update_village_id_column()
RETURNS trigger AS
$BODY$
BEGIN

  UPDATE schema_example.er_table
  SET    village_id = register_table.village_id
  FROM   schema_example.register_table
  WHERE  (schema_example.register_id = new.register_id);

  RETURN new;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER update_village_id
AFTER INSERT OR UPDATE
ON schema_example.er_table
FOR EACH ROW
EXECUTE PROCEDURE update_village_id_column();

EDIT: removed recursive part from trigger
CREATE TRIGGER update_village_id
AFTER INSERT
ON schema_example.er_table
FOR EACH ROW
EXECUTE PROCEDURE update_village_id_column();


texts: How can i improve MySQL Delete performance?

texts:
When I delete 5000 rows from a table .It is costs more than 2 seconds but if I change delete to select it is just costs a few milliseconds.

How can I improve the performance?

Here is the details:

There are 2 million rows. And the meeting_number have index.

    mysql> show table status like 'zm_meeting_ext'\G;
    *************************** 1. row ***************************
               Name: zm_meeting_ext
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 2018854
     Avg_row_length: 318
        Data_length: 643842048
    Max_data_length: 0
       Index_length: 1225310208
          Data_free: 59768832
     Auto_increment: NULL
        Create_time: 2015-08-29 05:48:54
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.24 sec)
mysql> show index  from zm_meeting_ext\G;
*************************** 1. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: meeting_ext_key
 Seq_in_index: 1
  Column_name: meeting_number
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

delete Statement：

DELETE  FROM zm_meeting_ext
        WHERE   meeting_number IN
                 (
                    888234641
                 ,
                    713842289
                 ,
                    587345701
                 ,
                    543533869
                 ,
                    885497679
                 ,
                    805900814
                 ,
                    804899468
                 ,
                    115617318
                 ,
                    582279870
                .......
                5000 numbers
                )

So how can I improve the performance and what is the principle of the delete operation?


texts: Load into memory-optimized table gives insufficient memory error

texts:
I am trying to load a memory-optimized table with rows from a disk-based table. It is just a simple INSERT..SELECT since the table structures are almost the same.

The database is on a dedicated server (not a VM) with 512 Gb RAM. However, I keep on hitting the following error:

  Msg 701, Level 17, State 103, Line 10

  There is insufficient system memory in resource pool 'default' to run this query.

The table has about 66 million rows and about 50 columns. Max server memory is set to the default 2147483647 Mb. Max memory % on the resource pool is set to 100%. The server is running SQL server 2016 SP1.

Things that I've tried so far, with no success:

• Enclosed the INSERT..SELECT in a loop so that it will only insert 10,000 rows at a time. It will run for some iterations and then error out. The number of iterations varies a lot at each run -- it could error out after 10,000 one time, and then go for a million the next.

• Created a dedicated resource pool for the database, instead of using the default (read this somewhere). Same problem.

• Tried using SQL server 2017 RC2. Same problem.

Any help would be greatly appreciated.

Added:

The INSERT..SELECT looks like the following:

INSERT INTO apc.Policy
    (PolicyID, ReportingCode, CarrierCode, -- and some more columns
    ProcessDate, LastUpdateDate)
SELECT
    PolicyID, ReportingCode, CarrierCode, -- and some more columns
    ProcessDate, GETDATE()
FROM Policy (NOLOCK)
WHERE PolicyID BETWEEN @Index * 10000 + 1 AND (@Index + 1) * 10000 -- @Index is the loop index
ORDER BY PolicyID

(This was originally posted in StackOverflow. Closed there. Opened here instead.)


texts: Need help designing a query to pull a list of songs based on ranking and the last play date

texts:
I have a database of a list of songs used during a performance.

The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best.  The table is linked to tblEvents which lists every time a song is performed.  tblEvents has fields EventID and Date.  The two tables are linked through tblSongsPlayed (as, obviously, it's a Many-To-Many relationship) which has two fields, SongID and EventID.

The database is in MS Access.

The part that gets complicated for me is to come up with a query that picks the songs as follows:

Show me all 5 star songs that haven't been played in the past two months AND
Show me all the 4 star songs that haven't been played in the past three months AND
Show me all the 3 star songs that haven't been played in the past five months

Wanted to respond that I see your answer and I'll check it out as soon as I can.  Thank you so much!

The reason I have three tables, and maybe I'm getting this wrong here, but you can have one song played on multiple occasions, and multiple songs are played during one event.  So, for example, we play SongID 1 through 5 on date 1, and then song 3 through 6 on date 2, and so forth.  So it seemed to be a many-to-many relationship, a unique song can be played at multiple events.


texts: How to restore SQL Server bak files in Access?

texts:
I backuped some SQL Server 2008 R2 databases using .bak files within SSMS, I sent them to a client and they're willing to open them within MS Access.

Can you please tell me if this is possible and if limitations exists to perfmrom this procedures?

My bak files are 40GB aprox

Thanks in advance


texts: Importing large file while keeping table available for other operations

texts:
We need to import millions of rows in a quite big table.

Say like 5 million rows into a table with more than 1 billion rows.

After preprocessing the import file we will have update operations and insert operations.

I have a couple of questions:

How the table will be affected? During this import (updates and inserts) what could happen to other processes trying to read or write data to this table?
Definetely we should be using bulk operations right? Insert and updates. I know bulk insert functionality as being something extremely fast compared to normal inserts. Does SQL Server 2008 provides something similar for UPDATES?
Which techniques or strategies should we consider in order to make this process as fast as possible and at the same time keep the underlying table available for other processes to read and write to it while importing?

My ideas are:

All the row to be imported will be of the same UserId, so having this table partitioned by userid will improve the performance right? As all the other queries related to other users which doesnt belong to the affected partition will not be affected... Is this correct?

What about indexes? Should I be droping them and recreating on the affected partition?

I will continue reading and testing on this topic. If anyone has experience on some of the above questions would be great or could point me to some related information would be great!


texts: Extract Year only from 'Select @@version' output

texts:
I'm trying to automate a short task in configuring our SQL Server for which I have to grab SQL Server year. Is there a way to extract year only from the output of following query: Select @@version ?

As you know it outputs the whole informatioin e.g.:
Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64)   Nov 16 2019 01:14:50   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

I tried the year function as below but I'm getting conversion error.

declare @SQLVersion varchar(100)
set @SQLVersion = (Select year(@@version))
print @SQLVersion

Or is there another way of grabbing the year only using tsql?

Any help is appreciated.


texts: Why am I able to query a remote database without a dblink?

texts:
While researching a security concern, I discovered that users are able to query a remote database without the need of a dblink.

However, this was only true for databases that had no value for DB_DOMAIN. If the local database's DB_DOMAIN was qualified with "foo.com", then a user could not query the remote DB and required a dblink.

I could not find any documentation that explains why this happens and am a very new DBA. What is it about Oracle that allows this to happen? Is the only way to prevent this to qualify every database's DB_DOMAIN with "foo.com"?

Example of accessing information from remote DB without a dblink below:

From DB: FooB

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:05:21 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string

From DB:FooA

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:08:25 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
db_domain                            string
SQL> select sysdate from dual@FooB;

SYSDATE
---------
26-FEB-15

SQL> select db_link from all_db_links;

no rows selected


texts: how to stop using sql server login credentials in a linked server?

texts:
I have a linked server based in Italy server_italia that connects to a server based in Oregon USA ORDB1

we are on different but trusted domains.

The connection from server_italia to ORDB1 is made through a linked server using a sql server account that has a login and the required permissions on ORDB1.

I am a sysadmin and even domain admin in my domain, and my server_italia but I am only sysadmin inside the sql server on ORDB1.

There is a similar question:

How can I get my linked server working using Windows authentication?

but it does not provide the answer.

Here is my linked server:

when I run the following queries on server_italia I get the following results:

SELECT [the_server]=@@servername,
auth_scheme
FROM sys.dm_exec_connections WHERE session_id = @@spid ;

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

In Oregon I get this:

If my American colleagues get:

the Service Principal Names registered for the services being run by your service account
set up Kerberos Authentication  on their server
Configure Constrained Delegation as that will allow the credentials to be passed along, otherwise you may still run into the double-hop issue. – added on the comments by John Eisbrener

Would I be able to use my linked server without the sql server account login impersonation?


texts: Synchronise two databases (but not really)

texts:
I'm trying to design the following system.

An arbitrary number of of embedded systems will each record data constantly and indefinitely. It is required that these devices upload their data to a server which maintains a complete history of all captured data (and records the source of the data by an ID number of something). The embedded systems are required to keep a small local history of about a day.

Is it possible to use MySQL's replication functionality to do this? How would it work, since the local DB will be deleted periodically, while the server copy must never be deleted.

Another way is possibly just periodically running a program to upload the day worth of data and clear the table, but this seems messier especially on an intermittent internet connection.

What do you think would be a good design for this situation?

Thanks


texts: How to use MariaDB's new "Invisible" column type

texts:
I have been playing around with this new column type. The use case being that I want to hide this column from a client issuing a "Select *" query.

But the column seems to behave like any normal column and is not hidden. I have enabled the Invisible column type by using the following command:

ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE;

Reading up in the MariaDB documentation, the examples provided are only with Create statements. Which makes me wonder if one can only enable this feature when creating a table and NOT with an Alter statement. Can anybody shed some light?


texts: PostgreSQL 10 Identity Column gets "null value" when inserting multiple rows with default keyword

texts:
I recently upgraded from PostgreSQL 9.5 to PostgreSQL 10. One of the nifty features in PostgreSQL 10 is the new identity column type, an alternative to PostgreSQL' serial pseudo-type. Official documentation for identity column can be found one the CREATE TABLE page.

However, when inserting multiple rows into a table with a GENERATED BY DEFAULT AS IDENTITY column and using the keyword DEFAULT to get the next ID value, the default value is coming back as null.

For example, let's say I have a table

CREATE TABLE test (
  id int GENERATED BY DEFAULT AS IDENTITY,
  t text
);
CREATE TABLE

Inserting a single row with the DEFAULT keyword seems to work fine.

INSERT INTO test (id, t) VALUES (DEFAULT, 'a');
INSERT 0 1

Inserting multiple rows does not.

INSERT INTO test (id, t) VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, b).

Inserting multiple rows using an implicit default also works.

INSERT INTO test (t) VALUES ('d'), ('e');
INSERT 0 2

The problem specified above does not appear to be present when using the SERIAL column pseudo-type.

CREATE TABLE test2 (
  id SERIAL,
  t text
);
CREATE TABLE

INSERT INTO test2 (id, t) VALUES (DEFAULT, 'a'), (DEFAULT, 'b');
INSERT 0 2

So my question is: am I missing something? Is the DEFAULT keyword just not expected to work with the new identity column? Or is this a bug?


texts: MariaDB CONNECT engine BIN table - file record layout, storage of a date data type, padding

texts:
In mariadb-10.4.5, running on Windows 10, I created this table (in the local database):

`CREATE TABLE IF NOT EXISTS `testbalNoFieldFormat` (
  `fig` int(4) NOT NULL,
  `name` char(10) NOT NULL,
  `birth` date NOT NULL,
  `id` char(5) NOT NULL,
  `salary` double(9,2) NOT NULL DEFAULT 0.00,
  `dept` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8 `table_type`=BIN `block_size`=5 `file_name`='TestbalNoFieldFormat.dat'

INSERT INTO testbalNoFieldFormat VALUES(1,"1234567890",'2009-01-20','abcde',100000.12,1234)

I inspected the file in Binary Viewer from https://www.proxoft.com/BinaryViewer.aspx.

In Binary Viewer (enclosed in double quotes so you can see the start and end):

As individual bytes viewed as unsigned integers, not octals:

"   1   0   0   0   49  50  51  52  53  54  55  56  57  48  32  32
    32  32  32  32  32  32  32  32  32  32  32  32  32  32  32  32
    32  32  48  93  116 73  97  98  99  100 101 32  32  32  32  32
    32  32  32  32  32  184 30  133 235 1   106 248 64  210 4   0
    0"

As ASCII, it is:

"   .   .   .   .   1   2   3   4   5   6   7   8   9   0

            0   ]   t   I   a   b   c   d   e
                        ?   .   ?   ?   .   j   ?   @   ?   .   .
    ."

Grouped into 4-byte integers (Little Endian), the bytes are:

"   1           875770417   943142453   538980409
    538976288   538976288   538976288   538976288
    1563435040  1650542964  543515747   538976288
    538976288   2233382944  4167696875  315968
    538976256"

I can see the first int value that I inserted (first field, value 1) but not the second int value that I inserted (last field, value 1234). Possibly there is some padding affecting things but 65 bytes seems an odd number to pad to - I was thinking it would pad to a multiple of 8 bytes, or 4 bytes.

The file length is 65 bytes, vs 46 bytes for the previous table (testbal).

Can anyone interpret the byte structure for me? In particular, which bytes represent fields 3 (birth), 5 (salary), 6 (int)?

Which bytes represent padding?

How can I extract the year, month and day of month from the bytes representing the value of the date field 'birth'?


texts: Stress Test to Saturate Memory for MySQL InnoDB

texts:
I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of memory.

Perhaps a query I could use for mysqlslap?

Update

I thought other people would benefit from a simple way to load test mysql and this was an easy question where lots of people would have "tools". They would help my obscure situation and I could use to investigate our real problem.

We have our configuration tuned, and it was fine for months but recently we eventually get mysqld: Out of memory (Needed 220428 bytes) and MySQL will crash minutes later.

I'm not researching your usual "whoops I allocated too much to my buffer pool" problem. That memory is allocated at start up and improper setting causes immediate shutdown. I'm really after the other 20%. Just creating the maximum number of connections with a high buffer pool hasn't proven to reproduce the problem so I want to stress everything. I am not ruling out stressing the buffer pool in case we are actually suffering a MySQL bug relating to it.

Here is an information dump for people interested:

/3GB to overcome 2GB for mysql - we haven't set /LARGEADDRESSAWARE but we do have /3GB and /PAE with 8gb memory
We are at risk of low System Page Table Entries but last we saw over 18,000 (not a lot but enough, it stayed at this level at last crash)
Mysql keep fixing memory related errors in their change logs
Mysql 5.1 let you put the inndb buffer pool in Address Windowing Extensions (i.e. use PAE) with innodb_buffer_pool_awe_mem_mb but MySQL 5.5 has no such option

I'm no authority on any of the above I just want to be able to produce high memory activity so we can A) reproduce the problem and then B) solve it in the medium term before moving off 32bit, which is the real solution but it won't happen tomorrow.

Update 2

Turns out Mysql 5.5's mysqld.exe is already built to be /LARGEADDRESSAWARE but cannot set the innodb engine size equal to or greater than 1600M. Connections do use the extra memory from /3GB (proven with mysqlslap).

vcvars32.bat
dumpbin.exe /headers mysqld.exe
Microsoft (R) COFF/PE Dumper Version 10.00.40219.01
Copyright (C) Microsoft Corporation.  All rights reserved.

Dump of file mysqld.exe

PE signature found

File Type: EXECUTABLE IMAGE

FILE HEADER VALUES
             14C machine (x86)
               5 number of sections
        4E6A3CEF time date stamp Sat Sep 10 04:21:03 2011
               0 file pointer to symbol table
               0 number of symbols
              E0 size of optional header
             122 characteristics
                   Executable
                   Application can handle large (>2GB) addresses
                   32 bit word machine

My conclusion. If you don't use /3GB or otherwise set connection memory + innodb buffer memory greater than available memory you are bound to eventually get mysqld: Out of memory... although mysql seems to do it's best to reject connections to avoid this problem.

Take home message, test that you can actually achieve max_connections with mysqlslap and the rest comes down to maintaining OS memory (and other resources like free system PTE).


texts: need the rows with a unique value on certain columns but date column doesn't help?


texts: naming a column differently then its original one in foreign data wrapper of PostgreSQL

texts:
I have two databases. In each database I have an employee table which differ from each other in the number of columns and column names.

Here is what the table looks in the first database:

 Emp_ID    Name        Father_Name      Family_Name
 1         Max         Stjepan          Neg
 2         John        Chris            James

Here is what the second table looks like which will be in the second database:

 ID        Name        Father_Name      Family_Name   Blood_Type
 1         Max         Stjepan          Neg           ARH+
 2         John        Chris            James         ORH-

Now when creating the foreign table in Second Database I want to store the Emp_ID values from First Database with column name ID and also I want my foreign table to include the blood type from the table which is in the current database Second Database.

Shortly, how can I create a foreign table with different column names than the original table in remote database? It should also include the columns from first table which is in the current database.


texts: Preloading a Java library in DB2, similar to ORACLE's loadjava

texts:
Edit to add details to clarify, based on Chris' comment:

A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when deploying on the server it fails, since it cannot resolve these dependencies at runtime.

Does DB2 have any functionality to automatically preload a library / Java stored procedure?
I'm looking for something similar to ORACLE's loadjava:

loadjava -u myuser/MYPW -resolve mail-1.4.3.jar
loadjava -u myuser/MYPW -resolve aws-java-sdk-1.3.8.jar


texts: MySQL getting result using 3 Tables and case statements

texts:
I have 3 tables  named person,person1, person2. All the 3 table has 2 columns name and phno. Now my requirement is, if i give any particular phno it has to show either yes or no
for the corresponding table.

My sample table is :

Person Table:
Name      phno
abc       1234
def       2345
ghi       3456

and person2 table is

Name      phno
abc       1234
def       2345
ghi

and person3 table is

Name       phno
abc
def        2345
ghi        3456

As you can see, the same name can contain phno in one table and not in the other table. The required output is like :

Output 1:
 Phno            person         person2     person3
 1234              yes            yes         no

Output 2:
phno              person         person2     person3
3456                 yes           no             yes

I have tried something like this..

select a.phno,case when a.phno = '1234'then 'Y' else 'N' end as phone_number1,
case when b.phno = '1234' then 'Y' else 'N' end as phone_number2,
case when c.phno = '1234' then 'Y' else 'N' end as phone_number3
from person as a, person2 as b, person3 as c
where a.phno = '1234' and b.phno = '1234' and c.phno = '1234';

can anyone help me to improve this to achieve what I want.Thanks


texts:
I have 3 servers with an SQL Server instance (S1\PROD, S1\PROD, S3\PROD) in my cluster and I have a couple of DBs hosted on these with AG AO. I have a lot of DBs and they have 2 replicas (1 primary and 1 secondary) and each DB is in a different AG, so for example Database1 is primary on S1\PROD and secondary on S2\PROD in AG1, Database2 is primary on S2\PROD and secondary on S3\PROD in AG2, Database3 is primary on S3\PROD and secondary on S1\PROD in AG3, etc.
I need to generate some dynamic scripts for those DBs which are primary (NOT secondary) on S1\PROD, however, I cannot find anything related to how this can be achieved with this setup.
For example I need to generate the below, for each primary DB that is on S1\PROD:
USE [master]
GO
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'S1\PROD' WITH (FAILOVER_MODE = MANUAL)
GO
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'S2\PROD' WITH (FAILOVER_MODE = MANUAL)
GO

I managed to get the AG name and the server names of where it is primary and where it is secondary with the below query:
WITH AGStatus AS(
SELECT
name as AGname,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '' END AS IsPrimaryServer,
secondary_role_allow_connections_desc AS ReadableSecondary,
[availability_mode]  AS [Synchronous],
failover_mode_desc
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
)

Select
[AGname],
[Replica_server_name],
[IsPrimaryServer]
FROM AGStatus
ORDER BY
AGname ASC,
IsPrimaryServer DESC;

So far I managed the below (the dynamic SQL for printing the statement works, however, I'm not sure whether my IF usage is good and whether I should achieve this in another way):
CREATE TABLE #temptable
(
    AGname varchar(30),
    Replica_server_name varchar(30),
    IsPrimaryServer int
)

INSERT INTO #temptable
SELECT
name as AGname,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

Select
[AGname],
[Replica_server_name],
[IsPrimaryServer]
FROM #temptable
ORDER BY
AGname ASC,
IsPrimaryServer DESC;

DECLARE @currentserver varchar(40) = @@servername

SELECT
(CASE
     WHEN (Replica_server_name = @currentserver)
     THEN  (select 'USE [master]' + char(10) + 'GO' + char(10) + 'ALTER AVAILABILITY GROUP [' + AGname + ']'  + char(10) +
       'MODIFY REPLICA ON N''' + Replica_server_name + ''' WITH (FAILOVER_MODE = MANUAL)'  + char(10) + 'GO'
from #temptable
WHERE IsprimaryServer = 1
UNION
 select 'USE [master]' + char(10) + 'GO' + char(10) + 'ALTER AVAILABILITY GROUP [' + AGname + ']'  + char(10) +
       'MODIFY REPLICA ON N''' + Replica_server_name + ''' WITH (FAILOVER_MODE = MANUAL)'  + char(10) + 'GO'
from #temptable
WHERE IsprimaryServer = 0)

 END)
FROM #temptable
WHERE IsPrimaryServer = 1


texts: Postgres : Out of memory error while importing a .sql file

texts:
I have taken a dump of postgres database from server, let's say abc.sql whose total size is 56 GB. I created a local database in postgres, let's say import_here where I have to import the abc.sql.
I am using the following command to import:
sudo -u postgres psql import_here < abc.sql

I get the following error:

I have tried the solutions from the following link but nothing worked for me:
https://italux.medium.com/postgresql-out-of-memory-3fc1105446d#:~:text=The%20most%20common%20cause%20of,writing%20to%20temporary%20disk%20files
I am using ubuntu 20.04.
I am also sharing disk space status for your reference:


texts: How can I connect to a database after its disk has come back online?

texts:
I have problems with database after reconnecting a hard disk:

.Net application works with database.
Disconnect hard disk with physical files.
Make any request to the database (of course we will have an exception, Unable to open the physical file).
Reconnect hard disk.
Any query to the database in the future will still throw an exception, Unable to open the physical file.

How can I recover connection with physical files without restarting the SQL Server service?


texts: Why cannot connect remote report server instance?

texts:
I have a SQL Server reporting service on ServerR.

I can easily use my local SSMS to connect ServerR's database engine and Reporting Service.

When I try to use my local Reporting Service Configuration Manager, even though I input Server Name as ServerR and click "Find", it shows "No report servers were found."

Why?


texts: Many one-to-many relationships between two tables

texts:
In the process of creating a small database for a therapeutic boarding school with Access 2013, I have a problem when designing the relational database model.

Two times at least, I ran into a situation where there were many one-to-many relationships between two tables.

There are two one-to-many relationships between Parents and Children because
a) some parents have more than one child in the school; b) the father and the mother often have different addresses and we have to contact them separately.
There are three (actually even more) one-to-many relationships between Staff and Children because a) every child has an educator of reference (Ref_Educ); b) every child has a person of reference in the board of directors (Ref_Dir); c) every child has a "godfather" (Ref_Godf). But these rules are soft. Sometimes, the person of reference Ref_Dir is not a member of the board of directors. Sometimes, the educator of reference is also the "godfather" of the child, and so on.

So it appears to me that the idea of setting many one-to-many relationships between these tables is somewhat logical, but on the other hand:

I cannot easily set them in the Relationships window of Access, which makes me fear that I have poorly designed the relationships.
In this similar question, it was suggested to "fix the database design". (However, I think that my problem is actually different, as a given person in the Staff table can be linked several times to the same Children entry.)

Could you give me some piece of advice? Thanks in advance.

Edit 2014-06-26
I followed your pieces of advice and updated the schema as follows:

As you can see, I added join tables, and lookup tables for the consistency of status.

It surely makes more sense and is less limited for the case we would want to add some features in the future.

But in the other hand, is it compatible with the fact that for now, there are a finite number of status (father/mother, ref_educ/ref_dir/ref_godf) that are meant to be individually hardcoded in our "Cartotheque" form?

To make things clearer, I draw a mock-up preview of how it should look:


texts: Why does changing the sort order return a different number of results? Is it a bug?

texts:
I have a query that contains COALESCE in the select list.  If I change the order by clause of the query I get a different number of results.

-- 225 rows - with order by  desc
select coalesce(tdo.orderId, -1) , o.*
FROM dbo.[Order] o
       JOIN dbo.TradeDeskOrder tdo  ON o.orderId = tdo.orderId
       join dbo.CodeValues cv1  ON o.[type] = cv1.iCodeValue
       AND cv1.vchCodeType = 'Order.Type' AND cv1.vchSource = 'Trading.UI'
       JOIN dbo.TradeCentralAccounts am  ON o.iCfnAccountId = am.id
       JOIN dbo.TradeCentralProducts pm  ON o.iProductID = pm.iProductID
order by o.orderid desc

-- 233 rows - order by
select coalesce(tdo.orderId, -1) , o.*
FROM dbo.[Order] o
       JOIN dbo.TradeDeskOrder tdo  ON o.orderId = tdo.orderId
       join dbo.CodeValues cv1  ON o.[type] = cv1.iCodeValue
       AND cv1.vchCodeType = 'Order.Type' AND cv1.vchSource = 'Trading.UI'
       JOIN dbo.TradeCentralAccounts am  ON o.iCfnAccountId = am.id
       JOIN dbo.TradeCentralProducts pm  ON o.iProductID = pm.iProductID
order by o.orderid

If I remove the coalesce from the select list I get 233 rows regardless of sort order.  Is this a bug?  I'm using SQL 2014 SP2-CU13.


texts: Have several 128 elements lists, need to obtain distance between them

texts:
Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way?

I can not save them as a list of floats, since i get: "can't adapt type numpyndarray"

Can not cast a type bytea to type cube, either.

Cube extension is installed, i can convert the list to type bytea using psycopg2, but nothing seems to work.

What am i missing?

I'm using postgresql 10.4, python 3, postgresapp 2.1.4 and postico