texts:
There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full. The mysqldump is 3.2GB. I assue that it will create an issue while restoring this DB.

  1. Can we analyse the Dump / backup inconsistancy without restoring (testing), if so how ?
  I can aware that this error is occurring due to one table named user_analysis.

Even a single table error leads to dump / backup incosistancy which leads to restoration failure. In my case daily backup is completing successfully. This issue might also cause due to insufficient space. But i had aroung 82GB free space in the SAN mounted partition. I could also see innoDB free space in the table.

  blade_server:master_db> show table status like "user_analysis"\G
  *************************** 1. row ***************************
                   Name: user_analysis
                 Engine: InnoDB
                Version: 10
             Row_format: Compact
                   Rows: 1312878
         Avg_row_length: 33
            Data_length: 43581440
        Max_data_length: 0
           Index_length: 19447808
              Data_free: 0
         Auto_increment: 1312171
            Create_time: 2011-11-25 03:00:41
            Update_time: NULL
             Check_time: NULL
              Collation: utf8_general_ci
               Checksum: NULL
         Create_options:
   Comment: InnoDB free: 13190144 kB; (`user_id`) REFER `master_db/users`(`user_id`)
      1 row in set (0.01 sec)

      blade_server:master_db> select (13190144) /1024/1024;
      +-----------------------+
      | (13190144) /1024/1024 |
      +-----------------------+
      |           12.57910156 |
      +-----------------------+
      1 row in set (0.00 sec)

    Where we had around 12.57 GB InnoDBfree space.

 InnoDB is per file :

   mysql> show variables like "innodb_data_file_path"\G
   *************************** 1. row ***************************
      Variable_name: innodb_data_file_path
      Value:

ibdata1:1000M;ibdata2:1000M;ibdata3:1000M;ibdata4:1000M;ibdata5:1000M;ibdata6:1000M;ibdata7:1000M;ibdata8:1000M;ibdata9:1000M;ibdata10:1000M;ibdata11:1000M;ibdata12:1000M

 2. What could be the root cause to this error ? I dont have any diskspace issues.
    Where can we pin point for this error ?

 3. Will table is full" triggering in logs due to too low maximum size
    for"innodb_data_file_path".


texts: Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution

texts:
I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system. The new system will be on a completely new infrastructure when we go live, leaving the current infrastructure available for rollback should we encounter any issues.

The business requires that a rollback option be available for 2 weeks minimum. Therefore in the event of a disaster scenario we would be required to switch back to the SQL Server 2005 database system. So the challenge I have is replicating the data upon going live with the SQL Server 2014 database system.

I am looking for a way to replicate the data (not necessarily the schema) from the SQL Server 2014 system to the SQL Server 2005 system.

I am not really a fan of SQL Server Replication and my preference is not to use this. My understanding is that this is not an option for us anyway due to the versions being too far apart, and therefore not supported.

I want to reach out to the community to find out if any tools are available to achieve this type of replication. It may be possible for us to implement a schema change freeze if this helps in finding the right solution.

Please can you advise what options are available to us, if any? Or should we look to develop a bespoke system internally?

Are you aware of any Microsoft partners that might achieve this for us through a solution already developed?

Thanks in advance for any advice.

Joe


texts: Very slow JOIN + ORDER BY query only in dev environment

texts:
I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query.

This query is taking 75 seconds to run in the new dev environment:

SELECT `orders`.*, `users`.`user_flag`
FROM `orders`
LEFT JOIN `users` ON `users`.`id` = `orders`.`user_id`
WHERE `cancelled` = '0' AND `is_estimate` = '0'
ORDER BY `orders`.`updated_date` DESC, `orders`.`order_date` DESC, `orders`.`creation_date` DESC
LIMIT 30;

It is a new installation of MySQL via MAMP Pro on a Mac
The version of MySQL is 5.7.25

...

Exactly the same query on exactly the same database schema takes only 24 ms on my old machine which has this version:

mysql  Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using  EditLine wrapper

So presumably something about the configuration of MySQL on the new machine is causing this slow query with a join and ORDER BY.

What my.cnf settings should I be looking for?

Further tests

(I cannot change the code as it is production code but did these tests to narrow down the problem)

Removing the JOIN brings the query time down to about 13ms

SELECT `orders`.*
FROM `orders`
WHERE `cancelled` = '0' AND `is_estimate` = '0'
ORDER BY `orders`.`updated_date` DESC, `orders`.`order_date` DESC, `orders`.`creation_date` DESC
LIMIT 30;

Removing the ORDER BY statement brings the time down to about 200ms (still too slow)

SELECT `orders`.*, `users`.`user_flag`
FROM `orders`
LEFT JOIN `users` ON `users`.`id` = `orders`.`user_id`
WHERE `cancelled` = '0' AND `is_estimate` = '0'
LIMIT 30;

(No JOIN or ORDER BY and it runs in 1.5ms)

The old MySQL config contains:

[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

max_allowed_packet = 64M

The new MySQL config has lots in it:

# The following options will be passed to all MySQL clients
[client]
password    = MAMP_root_password_MAMP
#port       = 9999
socket      = /Applications/MAMP/tmp/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
#port       = 9999
socket      = /Applications/MAMP/tmp/mysql/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 64M

explicit_defaults_for_timestamp = 1

table_open_cache = 64

MAMP_bind-address_MAMP

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
MAMP_skip-networking_MAMP

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

secure_file_priv=""

server-id   = 1

innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
sync_binlog = 0

[mysqldump]
password = MAMP_root_password_MAMP
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
myisam_sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout


texts:
I want to import a dump file but I have this error:

Import: Release 11.2.0.4.0 - Production on Mar. Nov. 28 10:12:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connect : Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-39002: invalid operation
ORA-39165: Schema TEST_2 was not found.

My command line:

impdp TEST/TEST@RECT0E DIRECTORY=DUMP_DIR DUMPFILE=Fichier.20171123_151835.dpdmp LOGFILE=TEST_2_27112017.log FULL=N EXCLUDE=STATISTICS REMAP_TABLESPACE=TEST_DATA:TEST_DATA_TEST REMAP_TABLESPACE=TEST_INDEX:TEST_INDEX_TEST REMAP_SCHEMA=TESTPR:TEST_2 SCHEMAS=TEST_2

I confirm my schema exist:

SELECT username FROM all_users ORDER BY username;

Result:

USERNAME
------------------------------
TEST
TEST_2
TEST_3


texts: Store extra, optional information about any entity

texts:
I've done a bit of reading but haven't found anything related. This must be widely discussed, so it's most likely my fault.

I've got a relational database with a number of different tables, each storing their unique "thing". Now I'd like to be able to attach extra information (0 or more) to any entity. A "custom field" if you want.

My first instinct was to have GUID PKs in every table, so that I could reference anything in the database by that GUID. I'd have another table for those extra attachments, where I could attach stuff to those GUIDs, no matter which table they stem from.

I don't want to introduce GUIDs for no good reason. And I fear this isn't one.

An alternative would be to reference the items by "table name" and "PK". This is easy as long as the PK is an IDENTITY INT, but AFAIK forcing a surrogate key is bad practice. Also referencing by "table name" feels wrong.

Am I missing something obvious? Maybe the whole approach of attach stuff to any item in this db is dubious.

EDIT:

I'm talking about a single table that stores the extra information belonging to the "main data" tables.
Let's think of the extra information as a field "comments".
I would utilize an extra table in order to accomplish a one-to-many relationship. One row main data may have N 'comment' entries.
The extra information may be attached to any of the "main" entities.
'use how often': it's non-essential information. The business data should not rely on it. For me, it's mostly an academic question, but if it turns out to be good design, I could imagine putting it to use.
'accuracy': I don't understand - the data should be as safe as the main data.

EDIT2:

Here's a different use-case involving a similar principle:

Users should be able to suggest typo corrections to data fields they encounter. I.e. user Sandy thinks a product name is misspelled, so I want her to add a row to a table corrections. This entry has to reference the original product row as well as the field name. A correction would reference the table, the exact row by PK and the field she wants changed (and -obviously- a new value for it).

Question still remains: how to reference any field/row for meta-information?


texts: How to set ENVREFERENCE when creating a SQL Server job

texts:
SQL Server 2014 (if it matters)

When I am migrating SSIS packages and jobs between environments, I need to update the /ENVREFERENCE parameter to the command for each job step.  Is there anyway to find the correct value and script the job step to use this value.

Pseudocode

Query for ENVREFERENCE where Environment name = N'QA' -- how to do this?
EXEC sp_update_jobstep
    @job_name = N'My Job',
    @step_id = 30,
    @command= first part + /ENVREFERENCE= value from query + last part;


texts:
I have a small scenario here and I think using composite primary keys would be a good solution. I'm doing this in SSMS. Here is the code to create the table:

CREATE TABLE [PSDesk].[dbo].[Local](
    [UserID] [varchar](10) NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [ComputerName] [varchar](15) NOT NULL,
    [IPAddress] [varchar](50) NOT NULL,
    [NETFramework] [varchar](50) NOT NULL,
    [ComputerModel] [varchar](50) NOT NULL,
    [UDrive] [varchar](50) NOT NULL,
    primary key (UserID, ComputerName)
)

I would like the UserID and ComputerName columns to be unique, meaning there will only be one record for each combination of UserID and ComputerName. Is this a good method to get the solution i'm looking for? I don't have much experience so i'm simply looking for advice, I threw this together just using my google-fu


texts:
If we add any new data/collection to the existing(running) shard cluster. Is there any way that the data/collection gets sharded automatically.
If yes, How to achieve that?.


texts: Extended Event not catching queries executed on my .NET application

texts:
I created that extended event to identify long running queries on my database:

CREATE EVENT SESSION [queriesLentas]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE sqlserver.sql_statement_completed.duration > 1000
    )
ADD TARGET package0.asynchronous_file_target(
  SET filename='C:\queriesLentas.xet', metadatafile = 'C:\queriesLentas.xem'
  )
GO

ALTER EVENT SESSION queriesLentas ON SERVER
STATE = START
GO

But when I start this session, it only gets queries executed on SSMS, and do not get queries executed on my .NET application(so that I can easily track those long queries).

Is there a way to see queries executed from my .NET application through this extended event?


texts: Wrong error in SSIS - [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: “ResultSetType_Rowset”

texts:
I have an Execute SQL task which gives returns only one row with one column - a number. I set my result set to single row. Despite that, the task fails. Why ? How do I fix it ?

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the
ResultSetType: "ResultSetType_Rowset".


texts: My SQLs are taking too long to execute for first time everyday in Oracle 11g

texts: whenever I make a new request in my application it takes to long to respond for the first time and all the subsequent requests are faster. If I do it again after some hours or tomorrow again it takes more time to respond and same subsequent requests respond quickly. I am not sure why this is happening every now and then even though the server is not restarted. I can understand if it is issue of explain plan creation. But the explain plan is created only once for the first time and it should not be slow there after. But I am keep on experiencing periodically even though the server is not restarted.


texts: Reconfigure the registry for new Oracle installation

texts:
I have the following oracle homes installed on my Windows machine

Oracle Developer Suite
Oracle Discoverer
Oracle 10G Client

I installed Oracle 10G database manually by following the following steps

Created the necessary directories
Setup pfile
Created a new database (using the "create database" command)
Setup listener

I am now running the admin scripts (catalog, standard, catproc etc).

The problem I have is I can connect to the database using sqlplus / as sysdba but I can't startup the listener. The problem I think is that the registry is showing that the Oracle 10G database is not the default home for Oracle. The registry is showing that Discoverer is the default home.

How do I change the registry so that the 10G database is the default home? Presumably these are set if you install using dbca or some other automated process?

The environment variables are already set to point to the 10G database.  When installing manually how do I setup the registry to update the Oracle homes?


texts: Transaction Log File corruption on Mirror server SQL 2017

texts:
Wondering if anyone has encountered database transaction log file corruption when database mirroring is enabled in SQL 2017 running on Windows Server 2019, on the Mirror server?

If so, curious what steps where taken to correct it.

Some background on what we are experiencing:

We recently upgraded both our Principal and Mirror servers from Windows Server 2016/SQL 2016 to Windows Server 2019/SQL Server 2017 (14.0.3162.1). We have 23 databases configured for mirroring and since our upgrade, 1 or 2 databases will have mirroring suspended every 24 hours or so.

These events are generated on the Principal and Mirror servers when mirroring is suspended:

Database mirroring will be suspended. Server instance ‘MyServer’ encountered error 9004, state 3, severity 21 when it was acting as a mirroring partner for database ‘MyDatabase’. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

And this additional error is generated on the Mirror Server:

An error occurred while processing the log for database 'MyDatabase'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

When we try to resume the suspended mirroring session, it will throw this error again on the Mirror server:

An error occurred while processing the log for database 'MyDatabase'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Our only course of action to bring mirroring back online, is to rebuild it. E.g. We take a Full and Transaction log backup on the Principal, then restore it to Mirror. Then we can enable mirroring, and everything is fine again.

Some interesting things we have observed:

1.) We have encountered this issue in both High Safety and High-Performance mirroring modes.

2.) We have also encountered this issue, when we are failed over (Principal and Mirror roles are flipped between servers). The normal “principal” server (which is now acting as the mirror) will eventually have transaction log file corruption.

3.) Windows Server 2019 Defender seems to play a role in how often we are seeing mirroring sessions suspended. When Defender is enabled, we experience 2-3 corruptions/mirroring sessions suspended per 24-hour period. When Defender is disabled, we only experience 1 or 2 corruptions/mirroring sessions suspended per 24-hour period.

4.) In our troubleshooting, when we run dbcc checkdb() on the Mirror server’s Database that was suspended, dbcc checkdb() does report corruption errors (b-tree errors). However, when we run dbcc checkdb() on the Principal Server’s Database – dbcc checkdb() reports 0 errors.

5.) We have rebuilt the database transaction log file (on the Principal server) for the database that experienced corruption on the Mirror, before enabling mirroring again. Issue came back roughly 24 hours later.

6.) Windows App, System Events are not reporting any system issues.

7.) SQL Server logs are not reporting any SQL events (other than these mirroring errors)

8.) There doesn’t seem to be a pattern/time of day to when corruption will strike on the Mirror

9.) We log our Principal Server SQL activity and we aren’t seeing anything out of the ordinary when corruption strikes on the Mirror. (Wait stats, query activity - all seem normal)

Any thoughts or troubleshooting advice would be greatly appreciated!


texts:
is there a work around for when you want to put an OR inside a filtered index?

create index FIDX_tblbOrders_sdtmOrdCreated_INCL
on dbo.tblBOrder(sdtmOrdCreated)
INCLUDE (sintMarketID,
         strCurrencyCode,
         sintOrderStatusID
         )
WHERE ((sintMarketId=1)
AND ( (sintOrderStatusId < 9) OR (sintOrderStatusId > 14)))

I am trying to create the index above,
because I am NOT interested in any situation
where sintOrderStatusId IN (9-14)

Of course I can create a view or indexed view, but I was trying to avoid that.

just adding more info:
sintOrderStatusId is a smallint NOT NULL and the possible values range from 1 to 30.
the 9 to 14 are to be avoided, therefore the filtered index.


texts: What are the differences between Cognos and SPSS modeler products?

texts: My school wants to start doing some data mining, and probably go with an IBM product. I've done a fair amount of searching, but the differences between the two products seem cloudy to those of us who haven't had experience with data mining. Can anyone offer some help, or direction, as to what the differences in these products are? Although the main goal is helping with retention and graduation rates, BI features in general are also of interest to the school.


texts: Restore of database failed .bak file SQL Server 2008

texts:
I have faced following error during restoring database:

  TITLE: Microsoft SQL Server Management Studio

  Restore failed for Server  (Microsoft.SqlServer.SmoExtended)

  ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 92382625792 additional free bytes, while only 42311290880 bytes are available. (Microsoft.SqlServer.Smo)

Database .bak file size is 697 MB and we have around 39 GB free space on server C drive.

Please anyone let me know, what should I do?


texts: How do I go about type iterations in a SQL Query?

texts:
I have a Dependency table that has a ParentID, ParentEntityType, ChildID, and ChildEntityType along with additional fields. The EntityTypes refer to a variety of object types (Nodes, Groups, Transactions, etc.) which are all stored on their respective tables with the ID being the key. These tables contains some additional fields that I need to grab (e.g. the Name of the node).

For example: a Dependency record, with omitted fields, looks like

My question is how to smartly go about going through all the types. What I came up with was a left join for each type filtered with a WHERE and coalescing the resulting mass but that seems awful and would need to be duplicated for the child. I feel there has to be a better way but I'm a bit stumped.

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
    LEFT JOIN (SELECT  c.ContainerID
        ,c.Status
        ,c.Name
        FROM dbo.containers as c) as con on d.ParentNetObjectID = con.ContainerID AND d.ParentEntityType = 'Orion.Groups'
   LEFT JOIN (SELECT n.NodeID... --A left Join for every flavor that looks the same as above
WHERE 1=1 --Not important for this
ORDER BY ParentName


texts: SqlDataAdapter vs SqlDataReader for DBAs

texts:
Speaking to developers about the use of SqlDataAdapter vs SqlDataReader and reading this: https://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader

Yields little in the way of explaining the effect on SQL Server of switching from SqlDataAdapter to SqlDataReader.

I understand SqlDataReader potentially increases the length of time a connection is open to SQL Server, and that certainly is a concern when it comes to efficient use of pooled connections.

Aside from this, what is the effect on SQL Server of using SqlDataReader instead of SqlDataAdapter? Has anyone observed the effect of this change on SQL Server?


texts: Getting value from a table based on one of the possible foreign keys

texts:
I have following tables:

car_type (Possible values - hatchback, sedan etc)
car_model (has car_type and name of car model and brand for eg. Toyota Camry)
car_version (has car_model and version of car for eg. ZXI)

car_panel (name of car panel for eg. Front Door (Left))
car_panel_problem (has car_panel and other problem type for eg. Dent)
car_panel_price (has car_panel_problem and price) - price should be based on one or more of car_type, car_model and car_version

Now, I would like to query and know what is the price for a car_panel knowing it's car_version.

if car panel price is there on car version then output it
else if car panel price is there on car model then output it
else if car panel price there on car type then output it
or null

I am thinking of creating car panel price table with nullable car_type, car_model and car_version columns and a price column. Then get the data using 3 queries.

select * from car_panel_price where car_version = <input version>;
select * from car_panel_price where car_model = <input version.car_model>
select * from car_panel_price where car_type = <input version.car_model.car_type>

Is there a better way to define the schema or query?

Please do not downvote if some information is incomplete. I will add it if you ask.


texts:
I'm trying to teach myself to use the SQL Server 2012 PIVOT function, using the old example pubs database from Microsoft, I have written this code..

SELECT [au_lname], [CA], [OR], [IN]

FROM    (SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')) AS Data

PIVOT   (COUNT([au_id]) FOR au_lname IN([CA], [OR], [IN])) as PvtTbl;

The column [au_lname] has a red wavy line under it, and when I try and execute the code, I get the error message:

  "Msg 207, Level 16, State 1, Line 1
  Invalid column name 'au_lname'."

I've tried refreshing the IntelliSence Local Cache (Ctrl + Shift + R) but this didn't help. The column definitely exists in the [authors] table, and this code returns results as expected:

SELECT [au_lname]
FROM [authors]

Please can someone tell me if there an error in my PIVOT function code, or is there something else going wrong?


texts: MySQL 5.6 Aborted_connects increase without error log

texts:
MySQL server version: 5.6.26
Linux: Linux 2.6.32-431.11.7.el6.ucloud.x86_64 #1 SMP Wed Jan 7 23:42:27 CST 2015 x86_64 x86_64 x86_64 GNU/Linux
Situation:

Aborted_connects increase 5-10 every minutes
log_warnings=2, but no error/warnings in error files
using TCPDUMP 3306 port, but cannot find any aborted connection request.

server status like below:

# Variable_name, Value
Aborted_connects, 7991
Connection_errors_accept, 0
Connection_errors_internal, 0
Connection_errors_max_connections, 0
Connection_errors_peer_address, 3
Connection_errors_select, 0
Connection_errors_tcpwrap, 0
Connections, 8051
Max_used_connections, 24
Performance_schema_session_connect_attrs_lost, 0
Ssl_client_connects, 0
Ssl_connect_renegotiates, 0
Ssl_finished_connects, 0
Threads_connected, 22

some referred server variables like below:

connect_timeout 10
max_allowed_packet  4194304
max_connect_errors  100
table_open_cache    2000
Connection_errors_accept    0
Connection_errors_internal  0
Connection_errors_max_connections   0
Connection_errors_peer_address  3
Connection_errors_select    0
Connection_errors_tcpwrap   0

at least the reason was a ZABBIX setting for mysql template wrong.
Does somebody know why the ZABBIX_AGENT of current server connect error were not be writed in the error log of mysql when log_warning level is 2?


texts: Transfering data from one MongoDB server to another while eliminating users/authentication

texts: I am transferring MongoDB databases from one server to another.  The servers cannot talk to each other, so I must use mongodump/mongorestore.  The 'from' server used authentication for the databases.  The 'to' server is in a secure environment, so I'd like to completely obliterate any security/authentication features from the database.  Is it as simple as just deleting the *users


texts: Getting the next bigserial value in PostgreSQL

texts:
Say I have a table with an id BIGSERIAL column. I just started a new session. I want to know what the id of the next row I insert will be. How can I tell this?

I can't simply SELECT MAX(id) FROM my_table (and return 1 if none found) because if I previously deleted things, the next id will not be 1 but will be higher.

I've already looked at other answers that use currval() and nextval(). They don't work unless I inserted within the current session... And I don't want to insert a row and see what ID I got, haha.

I'm about to lock the table and insert a lot of rows from a Python script on a client that is also keeping track of row IDs in a dictionary. The row IDs must match up, so I need to know what the first one will be. I'm surprised nobody else is dealing with this scenario.

Edit: I was wrong about the functions not working unless I inserted within the current session, but see the comments below.


texts: There is a lot of Client Locks and IPC Locks in PostgreSQL

texts:
I'm monitoring out PostgreSQL server using PostgreSQL Workload Analyzer. before implementing streaming replication there was a lot of IPC locks and now, after replication, there is a lot of  Client Locks that wasn't there before!
I could not find any information on these type of locks. They always appear. The onle explanation in POWA is

IPC: Number of wait events due to inter-processcommunication
Client: Number of wait events due to client activity

Is there a problem?
Are these locks prevent queries from executing? select or update queries?


texts: How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

texts:
I have a procedure to backup SSAS Databases that is working successfully.

I also delete the SSAS backup files that are older than 2 days.

What I am looking forward to implement is:

1) How to verify my SSAS backups? (restore verify only)

2) How to run a DBCC CheckDB in SSAS? (database integrity)

But I have not found any substantial documentation for these.

Any suggestions?

I know about the right-click/ Validate database on Visual Studio but I would like an automated process, not manually clicking.


texts: Features and Patterns for Managing Ordered Lists

texts:
I have a common scenario in app design, where I need to store an ordered list, and make the ordering easily accessible and adjustable via an application UI.

The ordering operations are simple and operate on only one item at a time - move to top, move to bottom, move up one position, or down one position.  These positioning operations may be interspersed in with typical add / edit / delete list operations.  For discussion, this is a single-user app and db store.

Because the objects can be highly complex, I'd like to avoid caching the entire list client-side, and tracking newly created objects in memory until a big save operation occurs.

I am using MS-SQL almost exclusively on these projects, and I'm wondering if there are db features, SQL features, or design patterns I should be using to better support the management of ordered lists.

My current approach is to use a DECIMAL column to store the list positions, which become fractional during the item move process.  Then I sort the list and re-generate integer positions to normalize list.  Following this operation, it's ready for another item move.

In an example implementation, I create a column named Seq for position tracking.  Once decimal place is enough for the .5 storage.

[Seq] [decimal](10, 1) NOT NULL

When an item is re-positioned in the app UI,

I UPDATE that record with new Seq value
I retrieve the full table, sorted ascending by Seq and regenerate all Seq values in the table from a 1-based integer series

The new Seq value is easily calculated-

Move to top = 0
Move to bottom = MAX(Seq) + 1
Move up = subtract 1.5 from the current Seq value
Move down = add 1.5 to the current Seq value

As lists get longer, updates get slower, since I'm doing N UPDATE statements, where N is the number of rows in the table, so it seems only suitable for small tables.

Is there some kind of in-built capability for managing an ordered list in SQL or MS-SQL?
Is there a means to update multiple rows in one call?

A far more performant approach would be to allow more decimal places, and then position at the median of the two adjacent items, with no re-sequencing on the list.

Move to top = Min(Seq) - 1
Move to bottom = MAX(Seq) + 1
Move up = median of the 2 previous item Seq values
Move down = median of the 2 following item Seq values

This approach only requires one SELECT (containing 2 rows max) and one UPDATE.

However with enough re-positions I imagine the field would run out of decimal places. For example, if I start with items at positions 10 and 11, and keep moving items down the list from position 9, they'd be given sequences of 10.5, then 10.25, then 10.125.  After 10 moves, the positions get lengthy... 10.0009765625.

UPDATE #1 - IMPROVED RE-SEQUENCING OF THE LIST

Regarding re-sequencing, I'm more caught up on row_number(), OVER, and WITH capabilities in MS-SQL.  It's nice to find that there's a much smoother way to re-sequence the list in a single UPDATE operation;

with OrderedItems as
(
    select
        ItemID,
        Seq,
        row_number() over (order by Seq asc) NewSeq
    from
        Items
)
update OrderedItems
set Seq = NewSeq


texts:
I am receiving error 666:

  the maximum system-generated unique value for a duplicate group was
  exceeded.

I am experiencing this on an Azure SQL Managed Instance.

However, I shouldn't be getting this error. The index in question is a clustered primary key on a bigint identity column. So there are no duplicates, and should be no uniquifier.

The table starts empty with the current ident of zero. I am attempting to insert 2.9 billion records, letting SQL Server assign the identity key. After receiving error 666, the current ident is 2147483648.

This makes no sense to me, as uniquifier is supposedly only used on non-unique clustered indexes that contain duplicate values. My table does not meet those conditions.

Does anyone have any ideas?

The records are inserted using an INSERT INTO SELECT FROM ORDER BY query. All columns are specified other than the identity column itself. I am absolutely certain the data type of the identity column is BIGINT.

The source table is a heap table with no identity or clustered indexes, so the bigint column in question does not exist there. The destination table has a duplicate definition to the source table, other than having the bigint identity column / clustered primary key added.

I am inserting all records from the source to the destination table, specifying all columns from the source table in the column list / select list for the insert. The bigint column is not specified in the insert because it is an identity column on the destination side only.

I had a thought to pre-populate my source heap table with a unique id column first (like row number), enable identity_insert on the destination table and try batched inserts of 100m at a time. Could that work?

Every time this query fails it takes 15 hours to roll back.

There is an NCI on the heap table in the desired insert order, but it doesn't include all columns so there are bookmark lookups happening. If I do an uncommitted read against the destination table during the load, I can see records, with an increasing identity.

Last execution plan I saw was a table scan followed by a sort.


texts: How do Master / Slave JDBC URL handle broken MySQL Slave?

texts:
I own the following classical MySQL 5.7 set-up :

one master server
one slave server

The master => slave link is done though GTID. I'm monitoring the link by querying show slave status on the slave node every minute: if the content of Slave_IO_Running or Slave_SQL_Running is different than "yes", I send an alert.

Actually, the slave is only a kind of real-time backup but not used for querying. I'd like to enable the JDBC Master/Slave Replication as described in the Connector/J documentation in order to better distribute the use of my hardware resources and improve query performance.

But, before editing my JDBC URL, there is one point I need to ensure. Previously, my Master/Slave link was broken (Slave_SQL_Running was set to "no", they were replication errors). When it happened, it was still possible to query the slave. I had fixed the issue by restoring slave from a master dump and my Master/Slave link was back, but for a time slice (some hours) the slave data was not correct and it was possible to query it.

Do you know how Connector/J handles this case when the Slave is not synchronized with the master but still answers to queries?

What kind of measures can I implement to prevent incoherent data (for example wrong read on slave wrong data and then write on master)?


texts:
when I run the following query, I get the error "Incorrect syntax near 'UNSIGNED'". Can you please help me figure out what's going on? Thanks!

create table  employees (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT primary key ,
    firstname varchar(25) not null,
    lastname varchar(25), not null,
    email varchar(45) not null,
    phone varchar(10) not null);


texts:
I have to generate automated script for alert log file, which will display the contents of alert log file. Here is the script for that.

while :
clear
do
echo "\n"
echo "ORA - Error"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n"
sed -n "/ORA-/p" "alert log fiel path"
sleep 4
clear

My issue is that I am getting all the errors. I only want errors which occured today. How can I do this?


texts: How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?

texts:
SQL Server 2012 optimizer does not get it right.

Test case, summary:

This is a simplified test scenario. DDL statements at the bottom.

I have two tables for data logging, A and B. There is a 1:n relationship - A has header records with a datetime called a_timeand B has detail records, with a field B.akeyare referencing A.id, and fields name and (data).

A has approx. 25,000,000 records, B has aprox. 500,000,000 records. B has roughly 200 records referencing
each record in A. One A and approx. 200 B records are inserted at a time together every
five minutes, reflected by A.a_time.

Clustered indexes are the primary keys, id, type int identity.

B has one non-clustered index, named IX_B_akey, on B.akey.

A.a_time is (non-clustered) indexed, too.

Now this query:

 SELECT A.a_time, B.*
 FROM B
   join A on B.akey = A.id
 where
    A.a_time > '2017-01-13T01:30:00' and A.a_time < '2017-01-14T07:30:00'
       and B.name in ('name33', 'name55', 'name66')

takes about 3 minutes on my database server.  Execution plan: here (see below for more exact execution plans)

When I add a simple hint to use
IX_B_akey:

 SELECT A.a_time, B.*
 FROM B
   with (index(IX_B_akey))
   join A on B.akey = A.id
 where
    A.a_time > '2017-01-13T01:30:00' and A.a_time < '2017-01-14T07:30:00'
       and B.name in ('name33', 'name55', 'name66')

it runs in less than one second. Execution plan:here (see below for more exact execution plans)

This does not change when I manually update statistics on both tables.

The query plan for the query without the hint shows the server will do a table
scan on B, looking for matching names. It is no surprise that this will take a while. With the hint, it uses the index and does a lookup via index for the
B records referencing matching A records. This is much faster.

I do not want to put query optimizer code into my software. Also, I use NHibernate.
Although it is possible, it would be ugly to use NHibernate interceptors and edit its
SQL.

Maybe the optimizer does not know that all B records referencing one A record are
physically next to each other. They are next to each other because they have been inserted
at the same time. If they were scattered throughout the database, it may be more expensive
to do all the lookups.

Question: How do I help the optimizer to choose the fast plan without a hint in the query? Can I add specific statistics to help here? Do I need a stored query plan?

For reference, here are the DDL statements used to create the tables.

create table A (
    id int not null identity(1,1),
    a_time datetime,
    constraint pkA primary key (id)
)

create table B (
    id int not null identity(1,1),
    akey int not null references A (id),
    name nvarchar(50),
    d decimal(5,3),
    constraint pkB primary key (id)
    )

create index IX_B_akey on B (akey)
create index IX_A_a_time on A (a_time)

Update: Adding name to the index IX_B_Akey would probably help, but it would also nearly double the data volume. This is not a good option.

Update on execution plans: After posting the question, I created another test scenario with the same data structure but more data. The queries are the same, but the date range which is queried has been extended. The database contains 1 mio records in A and 200 mio records in B. This allows me to privide actual execution plans:

Query without hint, takes 27s, time is reproducable

Query with hint, takes 3s, also reproducable


texts: Manually installing sample schemas in Oracle 11g

texts:
I am trying to manually install HR schema in my oracle environment by following the steps specified by oracle Help Center (11.2). Web link: https://docs.oracle.com/cd/E11882_01/server.112/e10831/installation.htm#COMSC001
However, I have run into some problems. It is specified that I only need to call hr_main.sql script (located in human_resources directory)using this command @?/demo/schema/human_resources/hr_main.sql.
My human_resources has only a script named hr_code and when I run it I do not get any type of installation procedure (entering password, tablespace etc as oracle specifies in the procedure).
Any help as well as other possible solutions would be very much appreciated.
Alternatively, could it be more feasible if I created a new database using database configuration assistant (thus installing the schemas during the installation procedure itself)?


texts:
I work with SQL Server 2008R2 and the server is rebooted in a monthly basis due to Windows patching. Usually the reboot takes place in the last Sunday of each month during the morning period. However every time that happens, some critical jobs that are executed in a daily basis late at night become incredible slow taking more than the double of the time to complete. After a few days, they get back to their usual duration.
I understand that the caches are cleared and some queries will recompile but would that make queries to take the double of the time (from usual 4 to 8 or more hours) ?
And why they get back to the normal behavior without no intervention at all after a few days?
I wonder what could be done to minimize (if not completely avoid) the impact of this monthly reboots.
Thoughts?


texts: Alter All Data Types To Different Data Type

texts:
I have a database that has multiple tables with datetime2 fields.  I need to convert all of these to datetime fields.  I ran this syntax that produced over 300 tables that need to be altered with roughly 1200 fields.

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME2')
ORDER BY so.name ASC

Now, for speed, I thought I would write some syntax to do this for me, but I must have set-up something incorrectly as I allowed this to run for 30 minutes and it was still running.  If I run the print statements everything prints out as it should (at least to my semi-trained eye).  Is > 30 minutes acceptable time for these alter table statements to complete or is something slowing me down/should I not use a cursor?

Declare @sql nvarchar(max), @tablename varchar(100), @fieldname varchar(100)

Select
table_name
,column_name
FROM #Helper
Order by table_name asc

DECLARE cursor1 CURSOR FOR

Select
table_name
,column_name
FROM #Helper
ORDER BY table_name asc

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @tablename, @fieldname

WHILE @@FETCH_STATUS = 0
BEGIN

Set @SQL = 'Alter Table '+@tablename+' Alter Column '+@fieldname+' datetime'
PRINT @SQL
--EXEC sp_executesql @sql;
FETCH NEXT FROM cursor1 INTO @tablename, @fieldname

END

CLOSE cursor1

DEALLOCATE cursor1

DROP TABLE #Helper

EDIT
I let the statement run a bit longer and discovered that it is actually hitting an error with one of the constraints that (could or could not) be causing the process to take so long.  I am trying to run this statement

Alter Table TestTable Alter Column datecreate datetime

And this error message is thrown:

  Msg 5074, Level 16, State 1, Line 1
  The object 'DF__Test_Table___datec__0D7A0286' is dependent on column 'datecreate'.
  Msg 4922, Level 16, State 9, Line 1
  ALTER TABLE ALTER COLUMN datecreate failed because one or more objects access this column.

Now if I script constraint as - create to this is the output that is generated:

ALTER TABLE [dbo].[TestTable] ADD  DEFAULT (getdate()) FOR [datecreate]

Nothing in there (to me) seems to be keeping this column as a datetime2 data type.  Do I need to add an extra check in my syntax to determine if the column has a constraint, and if it does, then drop the constraint and add it back?


texts:
I bet this may be a duplicate and I'm not forming the question correctly, so feel free to point me in the right direction.

Background

We have a SQL Server with some legacy data. They're all addresses, but stored in separate tables (I know, ugh.) so, we have a tblDefenseCounsel, tblPlaintiffCounsel, tblVendor, tblInsurer, etc., all with addresses. I don't have the ability to change that currently unfortunately.

We are creating a new table for a feature that has to store a contact -- except the contact can be from any of those locations.

Goal

Organize this generic contact table in a way that will cause us the least amount of agony.

Choices

As I see it, we have the following choices:

Create a Table such as | TableReferenceID | ContactID | with a lookup table that stores the database tables its from
Create a table such as | TableName | ContactID |, which stores the table name in text
Some third option?

Question

What is the proper way to store data of this nature, or what are the valid options? Are there additional options besides those I've listed?


texts: How to concatenate these two statements (RIGHT and LEFT)? I am trying to trim two characters off both the beginning and end of cell

texts:
Is it possible to either combine or concatenate the following two statements?

RIGHT(wp_postmeta.meta_value, LENGTH(meta_value) -2)

LEFT(wp_postmeta.meta_value, LENGTH(meta_value) -2)

What I am ultimately trying to do is strip the first two and last two characters from a field (for display or output only...I am not trying to modify or replace anything in my database).

Below is my full query, which works great...except the output of wp_postmeta.meta_value is a URL encased with [" and "].

SELECT wp_download_log.ID, wp_download_log.download_date, wp_posts.post_title,

(SELECT group_concat(wp_terms.name separator ', ') FROM wp_terms
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'dlm_download_category' and wp_posts.ID = wpr.object_id ) AS "Categories",

wp_postmeta.meta_value, wp_download_log.download_id, wp_download_log.version_id, wp_download_log.user_id, wp_download_log.user_ip, wp_download_log.user_agent
FROM wp_posts
LEFT JOIN wp_download_log ON wp_posts.ID = wp_download_log.download_id
LEFT JOIN wp_postmeta ON wp_download_log.version_id = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '_files'

In there you will see that one column I am trying to output is wp_postmeta.meta_value where wp_postmeta.meta_key = '_files'.

Inserting one of my two statements I am trying to combine in place of wp_postmeta.meta_value works great. Inserting both works too...I just have two columns.

With my search for a unified solution coming up dry, I have been trying for the past hour or two to concatenate the two to get the contents of wp_postmeta.meta_value WHERE meta_key = '_files' into a single field, sans the first two and last two characters, but I have yet to figure out how.

Surely there is a SQL solution here without having to manually process the column in Excel with each export?


texts:
I just started getting into AWS and until now I used my computer as a DB instance(both SSD and Ramdisk).
On my computer I get 4.000 to 10.000 inserts/sec of random generated data.

I modified the same program(20 threads each up to 50.000 random inserts) to connect to a db.t2.micro instance (1 vCPU, 1GB ram, 20 GB SSD), I got ~80 inserts/sec max.

I switched to a db.r3.4xlarge (16 vCPU, 122GB ram, -unknown storage) and still could barely get up to 150 inserts/sec max.

Should I add more threads to my application or is this linked to write latency( currently 1ms)?

Any ideas on how could I improve insert speed?

Thank you!


texts: Open source tools for Oracle schema compares

texts: I'm looking for an open source tool to generate DDL scripts.  Many tools call these synchronization scripts.  We essentially want to compare schema of different environments (ie: DEV to QA, QA to PROD) to manage object migrations/deployments a little easier.  Do open source tools exist like this for Oracle?


texts: MySQL-load data infile- invalid data validation

texts:
Is it possible to validate the data and load it in a table using  load data infile?

This is the query

load data local infile 'C:/Users/970685/Desktop/SFcheck.csv'
into table wow_macro.starting_footage_stg
fields terminated by ','
IGNORE 1 LINES
FIELDS ESCAPED BY \N
(store_id,category_id,footage,plan_id,@merch_id) ;

This is data in CSV:

store_id    category-id footage plan_id merch_id
1           1           3       1       fdfd

All the fields are INT in the table but when I execute the query the value 'fdfd' is not validated but saved as 0. (I have also given not null in the table and have not set any default value)


texts: Find which SESSION_CONTEXT key-value pairs was set

texts:
I found that the following query shows how many session context variables was set:

SELECT cache_address
      ,name
      ,pages_kb
      ,pages_in_use_kb
      ,entries_count
      ,entries_in_use_count
FROM   sys.dm_os_memory_cache_counters
WHERE  TYPE = 'CACHESTORE_SESSION_CONTEXT';

But now I would like to know which key-value pairs of SESSION_CONTEXT() was set -- if any.

After doing extensive research on microsoft sites I didn't found anything related.

Thanks in advance.


texts:
I have 3 nodes 'master master' cluster and using PXC 5.6

But I am only using a single node for both read and write.

Before using pxc, i was using standalone Mariadb 5.5 and I was taking backup using xtrabackup.

My question is, how should I take backup of PXC?
Should i take full and incremental backup for the node which is NOT used for read and write without any additional precautions just like i was taking for mariadb 5.5?

For e.g:
xtrabackup --backup --target-dir=/data/backup

Is there any officially recommended way to safely take backups of pxc?

I want to take backup so that if there is database corruption, I can start my cluster from scratch using that backup.


texts: Improve querying of Extended Events target file

texts:
As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I'd like to periodically (Every couple of minutes) query the event file to collect the data. I have been using the below to query data using xQuery, but it seems to be quite slow. I am aware of using a fileoffset as an option to optimise, but beyond that, are there any ways in which I can better improve predicates as been below?

    SELECT
         event_data
        ,n.value('@timestamp', 'DATETIME2') DtTimeStamp
        ,n.value('(action[@name="collect_cpu_cycle_time"]/value)[1]', 'bigINT') CollectCpuCycleTime
        ,n.value('(action[@name="collect_system_time"]/value)[1]', 'DATETIME2') CollectSystemTime
        ,n.value('(action[@name="last_error"]/value)[1]', 'varchar(255)') LastError
        ,n.value('(action[@name="collect_system_time"]/value)[1]', 'datetime2')             CollectSystemTime
        ,n.value('(action[@name="task_time"]/value)[1]', 'bigint')                          TaskTime
        ,n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(255)')              ClientAppName
        ,n.value('(action[@name="client_hostname"]/value)[1]', 'varchar(255)')              ClientHostName
        ,n.value('(action[@name="database_name"]/value)[1]', 'varchar(255)')                DatabaseName
        ,n.value('(action[@name="nt_username"]/value)[1]', 'varchar(255)')                  NtUserName
        ,n.value('(action[@name="server_instance_name"]/value)[1]', 'varchar(255)')         InstanceName
        ,n.value('(action[@name="session_id"]/value)[1]', 'INT')                            SessionID
        ,n.value('(action[@name="client_pid"]/value)[1]', 'INT')                            ClientPID
        ,n.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)')                         SQLText
    FROM
        (
            SELECT
                CAST(event_data as XML) event_data
            FROM
                sys.fn_xe_file_target_read_file('C:\Temp\EE_QueryWarnings*.xel', null, null, null)
        ) ed
    OUTER APPLY
        ed.event_data.nodes('event') (n)
    WHERE
        n.value('@name', 'varchar(MAX)')    = 'missing_column_statistics'
    AND
        n.value('@timestamp', 'DATETIME2')   >= DATEADD(MINUTE,-10,GETUTCDATE());


texts: How to perform a while loop on the query result

texts:
I created a trigger AFTER  an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine.

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
FOR EACH ROW IF new.order_status_id = "15"
THEN
UPDATE push_voorraad SET actie = '1';
END IF

But now I need to do some more SQL when new.order_status_id matches 15. I don't know how to do this in pure SQL, but I do now how to in php. So I hope with posting my php code, someone can tell me how to do this in SQL.

$query = "SELECT
            oc_order_product.quantity,
            oc_order_option.option_value_id,
            product.id
          FROM oc_order_product
            JOIN oc_order_option
              ON oc_order_option.order_id = oc_order_product.order_id
            JOIN product
              ON product.oc_product_id = oc_order_product.model
          WHERE oc_order_product.order_id = '25' AND oc_order_product.store_url = 'http://www.someurl.com/'";

$result = $mysqli->query($query);
while($row = $result->fetch_object()){
  $query = "UPDATE product_option_value SET quantity = quantity - {$row->quantity} WHERE product_id='{$row->id}' AND option_value_id='{$row->option_value_id}'";
  $mysqli->query($query);
}

25 in the query above should actually be new.order_id.

http://www.someurl.com/ in the query above should actually be new.store_url


texts: Copy a mongo db collection without locking whole mongod instance

texts:
I am trying to run mongodb copy collection on same server.
Requirement is we delete database as per our archival policy but we need to keep one collection from this data as a backup in separate db on same server.
I run copyCollection but it takes lock on whole mongo instance and block all other operations.
Collection size is 10 GB so it takes considerable time while copying and system is unavailable for duration.
Please suggest a way to achieve this without locking whole instance.


texts: How to Retrieve value from a User-Defined Table Type (UDTT)

texts:
Goal:
I want to reach and display the value of @data inside of [wwtest].

Problem:
The UDTT is from the database [test] and I receive an error message:

  The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.

I don't know how to retrieve the data from the database [test] in a query currently in the database [wwtest]?

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'),
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'),
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'),
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL
)
GO

DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol])
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);


texts: Is there a particular name for the type of structure of this database?

texts:
I'm trying to set up a data structure for an internal database of skills within a company, but I'm not entirely sure what "model" this fits as I'm quite new to databases and data structuring in general.

I've made an example of what I'm trying to do in excel:

Basically I'd like to have entries (users, in this case) which can exist in several different branches and are recognized as the same user, but have variables distinct for each branch in which they exist. Specifically I require the option to then sort by entry and see in which branches they exist, along with that branch' specific values for that user.

I think what this boils down to is a network structure, but again, I'm really quite new to this. If anything I would love some keywords for me to start googling with, because as of right now I can't see the forest for the trees


texts: Should I add multiple secondary indexes to search

texts:
I have a search screen on my app where the users can use up to 4 parameters to search.
I have written a stored procedure to facilitate the search.

Select
    ID,
    FirstName,
    LastName,
    CountryCd,
    State,
    Zip,
    Data1,
    Data2,
    Data3
From
    Customer
Where
    ((FirstName like @paramfname) OR (@paramfname IS NULL) )
    AND ((LastName like @paramlname) OR (@paramlname IS NULL))
    AND CountryCd = @paramcountry
    AND ((Zip = @paramzip) OR  (@paramzip IS NULL))

I have added one secondary index that includes the 4 columns FirstName, LastName, CountryCd and Zip.

CREATE NONCLUSTERED INDEX [idx_Cust_FN_Ctry]
ON [dbo].[Customer]([FirstName] ASC, [LastName] ASC, [CountryCd] ASC, [Zip] ASC)

My question: is this one index enough for efficient search ?

If the user runs a search by using only FirstName and Country, does SQL Server know
how to use the index efficiently?

Or do I need to add 4 separate secondary indexes on each of these columns?

Thanks


texts: Nesting inner to sum inner query's result

texts:
Suppose I have a query select a,b from xyz

The result would be:

a          b
m1     10
m2     20
m3     30

Can I somehow sum all b's something like:

select sum(b) from (select a,b from xyz)

And it will give me result 60. Possible? Stupid question?


texts: Designing a database for a business context involving orders, payment methods and shipping addresses

texts:
Our group has been developing a webapp to take it live in the future. We got stuck at normalizing a few tables, as it could involve redundancy.

We have some tables that I describe as follows:

shipping table: the address attributes and ID being the primary key.
order table: few attributes along with address attributes and oID being the primary key.
payment/card table: few attributes along with address attributes and pID being the primary key.

Is this in normalized state or do we have redundancy here?


texts: Activities to be perform after Table Partitioning

texts:
I am applying partitioning on few of the tables from my DB.
Need clarification of below things. Unable to find this on net.

Do we need to rebuild all indexes on the partitioned table?
Suppose, I am creating a new partitioned table and copying data from existing table in to it and renaming it to original table name. Then do the Proc cache will get altered as I am changing the table physically.


texts: how to reset value of auto incremented id to a specific value in mysql?

texts: i am migrating data from one db to another. A table is getting populated from combination of 4 tables. I am using stored procedures to populate new table. When i run it first time with some condition(where clause) it populates the table but when i run it second time with another condition, it skips few values in Id(auto incremented).


texts:
I'm using SQL Server 2008 R2.  Someone else set everything up.  I encountered an error today: Autogrow of file 'ASPState_log' in database 'ASPState' was cancelled by user or timed out after 1748 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

This was a very large transaction log (much bigger than the table) trying to grow and as a result it shutdown our website.  The recovery model is set to Full but the logs are not being backed up and truncated.  But somehow the database is being backed up nightly as there is a timestamp under Last DB Backup.  However, under Management there are no maintenance plans...so I'm not sure how the DB is getting backed up nightly unless there is an outside task or server doing it (which I couldn't find).

Should I do a full backup including logs and then truncate them?  And where can I find how I am currently being backed up?  Thanks, my first question.


texts: Help me to map Column types betwenn SQL and TopSpeed Clarion Tables

texts:
I am tasked to convert an old Clarion App to new one under SQL Server 2012.

All I have is 200 tables with TPS extension which I can open only by program called TopScan.
(No odbc, no technical documentation, no support).

I am making an C# program which is going to extract data to SQL.

Structure of data I can see in texts like this

                   FILE,DRIVER('TOPSPEED'),NAME('C:\bIRA2013\BIRA_ARHIMED\PART.TPS')
PART:K_SIFPAR        KEY(+PART:SIFPAR),NOCASE,PRIMARY
PART:K_ZIRO          KEY(+PART:ZIRO),DUP,NOCASE
PART:K_NAZIV1        KEY(+PART:NAZIV1),DUP,NOCASE
PART:K_GRUPA         KEY(+PART:GRUPA),DUP,NOCASE
PART:K_PORBR         KEY(+PART:PORBR),DUP,NOCASE
PART:K_KOMER_SIFPAR  KEY(+PART:KOMER,+PART:SIFPAR),DUP,NOCASE
PART:K_BANKA_SIFPAR  KEY(+PART:BANKA,+PART:SIFPAR),DUP,NOCASE
PART:K_GRAD_SIFPAR   KEY(+PART:GRAD,+PART:SIFPAR),DUP,NOCASE
PART:K_NACPL_SIFPAR  KEY(+PART:NACPL,+PART:SIFPAR),DUP,NOCASE
PART:K_NACPLD_SIFPAR KEY(+PART:NACPLD,+PART:SIFPAR),DUP,NOCASE
PART:OPIS            MEMO(2000)
RECORD               RECORD
PART:SIFPAR            STRING(10)
PART:ZIRO              STRING(20)
PART:PORBR             STRING(13)
PART:PORBRBIH          STRING(10)
PART:NAZIV1            STRING(40)
PART:NAZIV2            STRING(40)
PART:GRUPA             STRING(10)
PART:KUP               STRING(2)
PART:DOB               STRING(2)
PART:ADRESA            STRING(30)
PART:GRAD              STRING(25)
PART:PBROJ             STRING(5)
PART:TEL1              STRING(15)
PART:TEL2              STRING(15)
PART:FAX               STRING(15)
PART:OSOBA             STRING(30)
PART:RABAT             DECIMAL(5,2)
PART:RABATD            DECIMAL(5,2)
PART:CASSA             DECIMAL(5,2)
PART:CASSAD            DECIMAL(5,2)
PART:NACPL             STRING(2)
PART:NACPLD            STRING(2)
PART:KOEF              DECIMAL(5,4)
PART:DVO               LONG
PART:BANKA             STRING(6)
PART:VEZDOK            STRING(10)
PART:IZJAVA            STRING(10)
PART:KOMER             STRING(3)
PART:EMAIL             STRING(40)
PART:WWW               STRING(60)
PART:ID                STRING(2)
PART:D                 LONG
                     END
                   END

I think that ROWS between RECORD and END representing structure of table.
When I run through all tables I find following types of data as in list

STRING(10) --many sizes (10,20,100,200)
CSTRING(256) --many sizes (2000,3000)
LONG
DECIMAL(7,4)
BYTE
REAL
GROUP

How should I map these types to SQL Server types?

For me DECIMAL is only direct hit, but I am not so shure even for that.

Why I am so puzzled is that I can't find corresponding type for DATE.

Sub question is , How Clarion holds dates in tables ?


texts:
I am a new DBA and my friend told me the best way to learn about becoming a DBA is to setup your own server (virtually or physically).

I don't have a spare machine laying around so I will need to set one up virtually.

My job uses RHEL 7.2 so I downloaded the same version of the OS.

My work uses Oracle 10, 11 and 12. Should I use one of these versions or should I install the latest?

Here's where my question

What size should I create the disk drive for this environment? (I plan on using it to practice RMAN, ASM, DataGaurd and RAC).
What should the size of the partitions be? (for education and will not be used for production)
What should my RAM, CPU config be (if it really matters). I know this stuff can be adjusted later on the fly. I am more concerned with the first one since changing partition sizes is an involved process.

If I am missing anything, please feel free to leave a comment or "answer the question".


texts: Security implications of guest account and database chaining on sql server?

texts:
I have another database Ydb accessing data in database Kdb.  They both have the same owner.
In order to allow cross database chaining, it seems I need the guest user to be enabled in Kdb.

I did so like this:

USE [master];
GO
ALTER DATABASE Ydb SET DB_CHAINING ON;
ALTER DATABASE Kdb SET DB_CHAINING ON;
GO
USE [Kdb]
GO
GRANT CONNECT TO guest;

I realize this means stored procedures in Ydb created by Ydb's owner may freely access objects in Kdb when created by the same login.  What kinds of attacks does this open up?


texts: MariaDB Multi-source Replication SQL_SLAVE_SKIP_COUNTER

texts:
When an error occurs on a slave while executing a statement from the master, it is sometimes useful to use SQL_SLAVE_SKIP_COUNTER variable to skip the execution of the query, like this:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

While this works for main replication thread, I wonder how this is possible on a MariaDB Multi-source replication thread?


texts: Multiple Database in One Server Problems

texts:
Our company has 5 different databases. None of the multiple databases relate or utilize cross database joins.
We have ample resourcing and budget.

Our company is debating between:

5 databases on 1 server instance,
or 5 databases on 5 different servers.

Question: What are some specific problems, obstacle issues which can occur, if we place everything on 1 single server? My background is not DBA, so would like to learn examples I can discuss with team.

Background:

The argument for 1 server instance at company: We have "Resource governor" and "AlwaysOn Availability Groups" with Failover clustering.
Resource governor will handle Memory, Cpu, io workloads between databases. So along with AlwaysOn DR, it can handle runaway queries, bad design, memory issues.

Databases are 100GB each, critical company information, 50 million rows minimum

We process 300 transactions/sec

RTO = 10 min


texts: Where are PostgreSQL databases stored on my computer?

texts:
So this might be a stupid question, but I'm really new to this.
So I'm making a Postgres database on the postgres server, that I started up with this command:

sudo -u postgres psql

And then I did the command:

CREATE DATABASE database;

Which supposedly created the database.
But where is this database? Where can I find it on my computer?


texts: how to use fn_xe_file_target_read_file to read files over network

texts:
We are running Microsoft SQL Server 2014, and SQL Server Analysis Services 2014.

I am trying to setup a system to provide us with analytics concerning our SSAS OLAP cubes adoption and usage. I have setup a an Extended Event trace with the events that I need to track and it is creating a bunch of .xel files on the server like it should.

The issue I am having now is that we do not have a SQL instance on that server, so I need to load the files into a database on a different server. I can load the files if I copy them over to the SQL server and call the sys.fn_xe_file_target_read_file function with a local path, but I get an error when I try to read the file with a UNC path.

  The log file name "\\SERVERNAME\f$\ExtendedEvents\MyTrace_0_12345.xel" is invalid.  Verify that the file exists and that the SQL Server service account has access to it.

I have configured folder security on the SSAS server to allow the domain account that our SQL server is running under to have access to the folder. So that shouldn't be a problem. I am making the assumption that this function does not like UNC paths, so my next step would be to map a network drive on the SQL server machine so I can pass a local path to this function.

Before I request this from our DBAs I wanted to reach out to see if my assumption about the UNC paths is correct, and if any one else has worked around a similar issue by mapping a network drive to the remote share. I have read through the MSDN documentation for the function, but I cannot see anything to indicate how I would read a file on a network share.


texts: MySQL: Merge Same Database on Several Location into Single Location

texts:
I have a registration form, which is exactly the same for several locations. Each of them has a REG column, which is unique for each inserted row. Basically, each data input has a different REG number for it, which is auto incremented.

The location's form references a location's table which has the same structure for each individual location.

However, we have one central database which pulls all the data from several locations. This central database has the job of manipulating and populate ing all the location's databases.

Let's say, I have 3 locations, A, B and C. All those locations are using same database structure to save the data of each individual location.

What I need help on here, is how must I change or configure the database table or column so I can "restore" each individual database from the different location's into one single database on the central location. If the structure is same, then the previous data will be replaced by the new one when restore/add to the database in the central location.

I have a column called CODE which stored specific hard code for each location, it is just a simple character either A, B or C based on where the location of the database is saved.

So, at the specific time, from 3 different locations, the form table will be backed up and sent to the central database. And there, it will be restored or added to the database to populate all from those 3 locations.

Any idea and little help, please? Thanks in advance.

Here the database layout I would like to be done.

And the engine is InnoDB.


texts: MS Access: automatic creation of row in a table based on field in another table

texts:
I have a Table A with an ID field and a Question field. The question has a yes/no answer.

I want MS Access to automatically create a new observation (row) in another table, Table B, if I answer yes. How do I do this?

Ideally, I would also like the link between the two tables to be the 'ID' field, which is the primary key of Table A.

This question is vaguely similar, but is not in MS Access.


texts: SQL Server audit query that fires a trigger

texts:
Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it?

I need to know the SQL query that fires a trigger over a database without a profiler.

Thanks


texts: Days in between two dates group by month

texts:
I'm trying to create a lost time report in SSRS. I found a script that gives me what I need for a single date range, but I need this for multiple dates, and I need the total for the range.

So if I have 6 date ranges, I just need the total for the month/year combination and type combination (either L or R).

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20161209',  @e = '20170113';
    ;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

My table is called HRAL and the main fields are BeginDate (inclusive), EndDate (exclusive) and Type.

Sample Data

StartDate   EndDate    Type
2017-09-28  2017-10-02 L
2017-10-03  2017-10-10 R
2016-11-10  2016-11-11 L
2017-08-17  2017-12-25 R

Results

Date        Days  Type
2017-09-01  3     L
2017-10-01  1     L
2017-10-01  38    R
2017-11-01  1     L
2017-08-01  15    R
2017-09-01  30    R
2017-11-01  30    R
2017-12-01  24    R

I assume the solution needs recursion?


texts: Allowing certain users to edit drop-down list values

texts:
Current situation:

I've created a purchase orders database application for a company in MS Access.

The data is stored in a back-end database on a server.
There is a front-end master copy on a shared drive. Local copies are automatically pushed out to users when they login. Users use their own local front-end application.

As well as the two main data tables, there are several other tables containing lists of suppliers, project codes etc., which are used as suggestions in the drop-down lists on the forms. At first, I had these as linked tables as well, but performance was slow. Each drop-down box would take a few seconds to load.

Now, these tables are embedded into the front-end. My idea was that people who need to edit these tables could do so within the master copy of the front end. I have built forms to add / remove codes and suppliers from the lists.

However, this solution is not going to work as-is. The master file is a pre-compiled .accde file, so when I need to create a new version (from my master .accdb file) any changes made to the lists will be lost.

Current idea:

To have two copies of the 'auxiliary' tables in the database - one linked locally ("C:\Databases") and one to a master copy on the server. The forms would populate from the local copy. The editing section would alter the server copy. On login, the server auxiliary tables would be pushed to everyone's local drives along with the app.

This seems awfully complex for what isn't a complex situation. It also brings about strange consequences for the user; editing the tables doesn't actually do anything to the application until they next login (and sync the tables).

Am I going about this the right way? Surely there is a better way of handling this sort of requirement?


texts: How does PostgreSQL decide what to name its tablespace directories?

texts:
Using the psql CLI tool with a PostgreSQL 9.6.1 server running on localhost, I created a new tablespace with

create tablespace my_tablespace location /tmp/data;

and found that this created a directory called PG_9.6_201608131.

I can see that 9.6 is the version, but what is the last string of numbers? I'd like to use this to create databases in a Makefile, so it will be helpful to know how that number is derived (rather than just hard-coding it).


texts:
The online documentation defines nullif() as a function which returns NULL if the argument values are equal. But why does it return NULL even if the argument values are not equal, as in the case below:

select nullif(convert(varchar(10), ' '), convert(varchar(10), ''))

----------
NULL

(1 row affected)


texts:
I currently have some SQL Servers that sit on a sub-domain. We are eliminating that sub-domain and moving everything to the regular domain.

As far as SQL Server configuration goes, is there anything I need to change aside from potentially using different service accounts?

Note this is simply a domain change - the server name should be the same.


texts: Oracle alter table modify column from NVARCHAR2 to VARCHAR2

texts:
Having a column FIRSTNAME NVARCHAR2(30) converted from VARCHAR2(30) with

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- worked!

I tried to restore the original. However

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- fails!

gives an Ora-01439 error, that the values must be empty.

First introducing a new column FIRSTNAME2 VARCHAR2(30), copying and renaming did not succeed, maybe due to failing Oracle experience?


texts: Possible to ship logs to database with different name, but same structure?

texts:
I'm struggling here. Using SQL Server 2008 Standard

Our environment has a prod SQL Server and a Test SQL Server. What I'm looking to do is take some processing power off of the production server and create a log shipped instance on the test server for reporting purposes. This database is located on both production and test. The test system is also used to test, so I can't restore the logs to the same database name as production because the database will be in a constant state of restoring or in read only mode and we need full read/write capability. I have another database in test called ProdDatabaseNameReporting that is a carbon copy of the one I'm trying to get the logs shipped from.

Here's what I'm trying to do and what I told it to do

ProdDatabaseName --------> ProdDatabaseNameReporting

Here's what is happening

ProdDatabaseName --------> ProdDatabaseName

Everything looks to be setup correctly. Then the log shipping procedure begins and it starts to overwrite the database with the same name instead of the database I told it to log ship to.

I know I can restore the database manually from production to test and it will restore correctly.


texts: Json append results in error of - "No operator matches the given name and argument types"

texts:
clusters_v2=# UPDATE cluster SET "clusterCALogs" = "clusterCALogs" || '["test"]'::jsonb WHERE "clusterId" = 9;
ERROR:  operator does not exist: json || jsonb
LINE 1: ...ATE cluster SET "clusterCALogs" = "clusterCALogs" || '["test...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

According to https://stackoverflow.com/a/42233548/1663462 I should be able to append to the JSON array? Why am I getting this error?


texts: Populate mysql db to Shotgun server using Python

texts:
I've an internal mysql database. On each creation, modification or deletion of the content of this db, I need to run a python script to replicate those information into another software web server.

What's the more efficient way to achieve this?

using triggers to run python? I think it's going to be too slow
create some logs and live process them? like an event loop or something?


texts:
I am trying to activate Performance Diagnostic on a Postgresql 12 advanced server from PEM on a windows 10 machine. I am trying to install EDB Wait States module by changing the postresql.conf and adding
shared_preload_libraries = '$libdir/edb_wait_states.dll' but when i try to restart the edb service it fails.


texts: Group Database Entries by time difference


texts: MongoDB: Aggregation workaround by helper collection

texts:
Since I regularly run against mongodb's document size limit and also aggregation framework's group limitations >16M, depending on the amount of data I need to process, I look for a simple workaround.
The solution must also fit for single nodes (and must not require multiple mongos).
Saying in few words, it's an aggregation for finding

(a) the number (count) of uniques in a field and group the number by another field
(b) the frequency or count how often a unique id appeared.

An example could make the task easier to understand: Lets say there is a collection with 2 fields, the last and surname.

(ex a) How many different surnames exist for each last name? Result could be: There are 1000 unique (distinct) surnames for last name "smith", 500 different surnames for people with last name "schulz" etc.

=> this could also be solved with distinct count and/or mapreduce etc. no limitation is exceeded. anyways, it seems to be a slow table scan and aggregation framework does the task much faster.

(ex b) How popular are surnames for each last name? Result: With the last name "smith" there are different 500 sur names which appear only once, 200 sur names which appear 2 times, 45 are listed 3 times our data, ...

=> here it is very comfortable to work with mongodb's aggregation framework which handles this task fast and properly. However, trying the task with lots of uniques in a 15GB collection, it worries about the 16M limitation.
The first workaround was successfull but is only a quick'n'dirt non-scaleable trick:
Instead of processing all the data and $group afterwards, I filter ($match) for each $group-item. With the example from above, I look up all distinct last names, run one aggregation per lastname and reduce the pipeline data before aggregation starts.
This avoids the 16M limit issue if the amount of data for a single $group-item (last name) is not too much. => only quick'n'dirty workaround.
Another idea is a help collection for the COUNTs.
I could iterate each input document and write into another collection the surnames and one field per lastname with the counter.
E.g. surname=John count_with_Lastname.smith=10,  count_with_Lastname.brown=2, ...
Building this helper collection is very slow, isn't it? When incrementing +1 which is 1 update/write per input document, I fear this could take forever :-(
Is there a best-practice or tips for solving this task (withouth multiple nodes and without Hadoop or alternatives)?
Please let me know


texts: How to better backup MySQL 5.6 database?

texts:
Today I had an issue where I accidentally deleted a whole table (which cascaded to a few other tables) due to a funky piece of MySQL syntax, see here.

Luckily I had a backup from midnight, however all data was lost between midnight and to when I deleted all the rows in my table.

How can I better back up my database to be more up to date with the live system, or perhaps better just lagging a hour behind it?  I've seen people mention replication and binary logs but this is all kind of new to me.  From my understanding, having a replication setup, deleting my table in the master would also delete in the slave database?  Would I be better off trying to take hourly differential backups?  Or should i just do full backups every hour?  Our database is tiny, less than a gig, and we have room so it's feasible, but I would rather do things a proper way that would work for larger databases as well.


texts: Strange sort behavior on a partitioned table with a prefix index

texts:
Edit: Filed bug report at MySQL: link

I'm getting strange ordering behavior from MySQL when trying to order a partitioned table with a prefix index.

Problem can be reduced to the following dataset:

CREATE TABLE `test` (
  `id` int unsigned NOT NULL,
  `data` varchar(2) DEFAULT NULL,
  KEY `data_idx` (`data`(1),`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p10 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (20) ENGINE = InnoDB) */;

INSERT INTO `test` VALUES
    (6, 'ab'),
    (4, 'ab'),
    (5, 'ab'),
    (16, 'ab'),
    (14, 'ab'),
    (15, 'ab'),
/*The following values cause the weird behavior*/
    (5, 'ac'),
    (15, 'aa')
;

Trying to order on id with a search on data gives the following:

mysql> SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+
| id |
+----+
|  4 |
|  5 |
| 14 |
| 15 |
| 16 |
|  6 |
+----+

mysql> SELECT id FROM test WHERE data = 'ab' ORDER BY id DESC;
+----+
| id |
+----+
| 16 |
|  6 |
|  5 |
|  4 |
| 15 |
| 14 |
+----+

Tried on both 5.6 and 5.7, with the same results.

Funnily enough, removing the ORDER BY gets me a neatly ordered result set:

mysql> SELECT id FROM test WHERE data = 'ab';
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
| 14 |
| 15 |
| 16 |
+----+

Running an EXPLAIN doesn't appear to yield me anything interesting either:

mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | p10,p20    | ref  | data_idx      | data_idx | 6       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab' ORDER BY id ASC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | p10,p20    | ref  | data_idx      | data_idx | 6       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

mysql> EXPLAIN SELECT id FROM test WHERE data = 'ab' ORDER BY id DESC;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | p10,p20    | ref  | data_idx      | data_idx | 6       | const |    8 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

Any ideas?


texts:
I'm trying to insert the result set from:

SELECT * FROM sys.database_scoped_configurations

into a temp table, because I want to check the settings for all databases on my server.
So I wrote this code:

DROP TABLE IF EXISTS #h
CREATE TABLE #h(dbname sysname, configuration_id INT, name sysname,     value SQL_VARIANT,  value_for_secondary SQL_VARIANT)
EXEC sys.sp_MSforeachdb 'USE ?; insert into #h(dbname, configuration_id, name, value,value_for_secondary)  SELECT ''?'' as dbname, * FROM sys.database_scoped_configurations  D'
SELECT * FROM #h H

But then there will only be one row per database, not the four rows that I expect from running a plain select in each database.

I know there are better ways to code this than using sp_MSForEachDB, and I tried several. But I still only get one row per database.
I've tried this on both SQL Server 2016 RTM and SP1

Is this a bug with SQL Server 2016, or am I doing something wrong?


texts: Track all modifications to a PostgreSQL table

texts:
We have a table which has only roughly 500 rows, but it is very important for us.

I want see all changes which happen to this table. The changes should get tracked with a timestamp.

I don't want the tracking to happen in the application code, since I want to track changes which happen via psql shell, too.

I am happy with a PostgreSQL specific solution, since I don't use a different DB in this context.


texts: How to return different statements in where clause ? (oracle)

texts:
I mean:

CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER) RETURN NUMBER
BEGIN
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
WHERE COLUMN1<= Y AND (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE COLUMN2>COLUMN3 END),
AND COLUMN 3 = XYZ;

RETURN SOMETHING.

END;

I know case statement does not return an expression but a value. How can i use some kind of "conditional statements" or something to return expressions ? I use to do it easily in mysql with "IF conditional" but i don't know how to do it in Oracle 11g.
EDIT: Does it work the same, not only for where statemen but for select ?


texts: T-SQL SELECT use multiple indexes for no reason

texts:
I have a question about indexer and execution plans in T-SQL.

My database is SQL Server 2008.

I have a simple three-table db schema:

The InverterData table is very large and partitioned (28,880,436 rows).

The Date column is calculated like this:

[Date] AS (ISNULL(CONVERT([date], [TimeStamp]), CONVERT([DATE], '19000101', (112)))) PERSISTED NOT NULL,

There is also a index for this column:

CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC)

Select query #1:

I now want to make a simple select that include all three tables and the 'Date' column in a where clause:

SELECT
    [TimeStamp], [ACPower], [DCPower]
FROM
    [InverterData]
JOIN
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE
    [InverterData].[Date] = '05.01.2016'
    AND [DataLogger].[ProjectID] = 20686

It took round about 19 seconds on me current database (result ~80 rows).

This is the execution plan:

Select query #2:

In the first select I detected that there is a long duration index seek for the 'Date' column. So I run a second select that only include the primary key column 'TimeStamp'.

This is the second select:

SELECT
    [TimeStamp], [ACPower], [DCPower]
FROM
    [InverterData]
JOIN
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE
    [TimeStamp] >= '05.01.2016' AND [TimeStamp] < '06.01.2016'
    AND [DataLogger].[ProjectID] = 20686

It took only about 2 seconds on me current database.

This is the execution plan:

Question:

Why are there two index seeks? I included all used columns from select 1 in one index. Why did it took so much longer?

Update 1:

If someone needed the complete schema I will add it to a SQL fiddle and post it.

Update 2:

Tooltip of index seek


texts: Best Way to Grant Read All Permission in SQL Server 2008 R2?

texts:
What is the best method to grant read access to all current and future databases to a user (AD Group in this case)?

Currently I have a flaky scheduled job that does this using  (going to start debugging the following, but maybe I am doing it wrong anyways):

sp_msforeachdb '
USE [?]
CREATE USER [MY_DOMAIN\SQL-READ-ALL] FOR LOGIN [MY_DOMAIN\SQL-READ-ALL]
USE [?]
EXEC sp_addrolemember N''db_datareader'', N''MY_DOMAIN\SQL-READ-ALL''
'

Perhaps there is a better way?


texts: What is the best partition technique for large table with TEXT column?

texts:
MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column?

There is a FULLTEXT index on the TEXT column, which is very essential for my searching operation.


texts: Same query on same same table, different response time

texts:
I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:

* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available;
* OS Debian 9;
* Mysql 5.6.47;

To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed.
I had abilited the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE `MALICIOUS_TABLE` (
  `column_1` int(11) NOT NULL AUTO_INCREMENT,
  `column_2` varchar(8) NOT NULL,
  `column_3` datetime NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) DEFAULT NULL,
  `column_6` int(11) DEFAULT NULL,
  `column_7` int(11) DEFAULT NULL,
  `column_8` tinyint(1) DEFAULT NULL,
  `column_9` datetime DEFAULT NULL,
  `column_10` int(11) DEFAULT NULL,
  `column_11` varchar(2048) DEFAULT 'column_11',
  `column_12` tinyint(1) DEFAULT NULL,
  `column_13` datetime DEFAULT NULL,
  `column_14` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`column_1`),
  KEY `fk_ual_aut_idx` (`column_2`),
  KEY `fk_aul_c_idx` (`column_4`),
  KEY `kf_ual_po_idx` (`column_5`),
  KEY `fk_ual_ute_idx` (`column_10`),
  KEY `column_1` (`column_1`),
  KEY `column_2` (`column_2`),
  CONSTRAINT `fk_aul_c` FOREIGN KEY (`column_4`) REFERENCES `t_table2` (`column_4`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_aut` FOREIGN KEY (`column_2`) REFERENCES `t_tabl3` (`column_2`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_po` FOREIGN KEY (`column_5`) REFERENCES `t_table4` (`column_5`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_ute` FOREIGN KEY (`column_10`) REFERENCES `t_table5` (`column_10`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from `MALICIOUS_TABLE`;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time.  The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no.
Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself?


texts: Do SELECT (without ORDER BY) return the same message as RECEIVE?

texts:
My current client needs to be able to PEEK messages in a SQL Server Service Broker queue. The current implementation uses RECEIVE and rollback to peek.

As that generates writes to the transaction log I'm wondering if a SELECT TOP(1) would return the same row as the RECEIVE command?

There are several writers but just one reader to that queue.


texts: Storing email,phone,fax and url in same table

texts:
Is it ok to store email,phone,fax and url in same table?
I want user to be able to add multiple email,phone,fax or website url on their profile.

Here's my current table

Contacts

id

user_id

val (eg. test@mail.com,09222229291, http://facebook.com/test,432-2233)

contact_id

ContactTypes

id

name (eg. mobile,tel,email,url,fax)


texts: Unable to connect to Oracle 11g server remotely using SQL developer ORA-12520

texts:
Hello everyone I'm unable to connect to my Oracle Server remotely using SQL developer ....while I can connect from SQL developer when I'm in the server directly ....I get the following error when I try to test my TNS listener

ORA-12520: TNS:listener could not find available handler for requested type of server

I followed the Oracle help ...and I found out that My Oracle service ORCL is registered

this is from Oracle support

ORA-12520: TNS:listener could not find available handler for requested type of server

Cause: None of the known and available service handlers for requested type of server (dedicated or shared) are appropriate for the client connection.
Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener and that the appropriate handlers are accepting connections.

this is the output of lsnrctl services

`

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 15-
     SEP-2017 16:49
     :00
     Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: OSAMA-PC, pid: 3240>
         (ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=49212))
    The command completed successfully

Lnsrctl status output
`

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-SEP-2017
18:2
:26

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 -
Prod
ction
Start Date                17-SEP-2017 17:39:16
Uptime                    0 days 0 hr. 48 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\list
ner.ora
Listener Log File         c:\app\mod\diag\tnslsnr\osama-
PC\listener\alert\log.x
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Windows\system32>

tnsping orcl result

C:\Windows\system32>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-
SEP-2
017 18:29:11

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)
(HOST = osama-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (20 msec)

C:\Windows\system32>