texts: Should I use DynamoDB or stick with MySQL via webservice for my mobile app syncs

texts:
I asked this earlier at Stack Overflow but a user advised me to ask it here since this website is more relevant to database related questions.

I made an Android app, name of which I'll not mention at this stage. It syncs with a central MySQL database via a webservice on LAMP, which also I created. It all works just great, no problems whatsoever. As the users use the app and perform certain actions, data from their devices gets uploaded to this central server which is running in a data center. As other mobile devices syncs with the database, they get updated with the newer data. At the initial install, app gets a few hundred K of data downloaded via few hundred queries.

Now the problem is that the app has started to get so many downloads that I can foresee I'll not be able to keep up with the sync. For a while I have been optimizing the code both on the Android end and on Web end (with iPhone version still under development), and also using caching techniques, but still, the communication between thousands of mobiles devices and one single database is reaching its limits. Everyday the number of downloads are more than previous day. Now I am extremely worried how to handle this situation.

I have been looking into using Amazon DynamoDB, have also went over its SDK, tutorials, etc. and am ready to spend a few days and switch this whole sync thingy to Amazon. But this will eventually cost me heavily, for which I'll have to charge for the app or use a better advertisement scheme. But that's another issue.

Unfortunately Googling for weeks hasn't helped me on figuring out how to efficiently handle large syncs with thousands of mobile devices, which might get into tens of thousands or probably hundreds of thousands.

In short, what to do to, lets say, deal with 100K devices using my app and in need of keeping in sync with a central database?

Kindly answer if you have faced a similar situation and know how to deal with it in real life.

Thanks in advance.


texts: LIKE to select the independent existence of the word whereever in the text

texts:
I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query

SELECT [ID]
  ,[TextValue]
FROM
   [dbo].[SearchLike]
WHERE
   [TextValue] LIKE '%Search%'

Q.1 How can i modify the query so that only the records having 'Search' in the text is returned and it shouldn't be taking 'LSearch'. i.e as per the image first three records only to be returned.?


texts: Can I find out what version of MySQL from the data files?

texts:
I have a very odd situation going on here. I had a linux box running ubuntu 8.10 and MySQL server with a Wordpress installation on it hosting an internal blog for our company. The machine that this was running on has crapped out, but I was able to put the drive into an enclosure and gain access to the files and recover the MySQL data folder. We are mostly a Windows company and I have very limited linux experience. Before this machine crashed we were experimenting with moving this blog to a Windows Virtual Machine running PHP for IIS; and we have an older copy of the Wordpress database up and running on MySQL 5.1. I am currently trying to retrieve the latest data from the linux MySQL data folder and import it into the current database but I am having problems.

Problems so far:
1) I do not know what version of MySQL was installed on the linux box
2) I see ib_logfile0, ib_logfile1, and ibdata1 on the linux MySQL data folder, but am not sure if it was truly running InnoDB or not as I also have .frm, .myd, and .myi files in the wordpress folder

If I can figure out the version of the linux box MySQL I might be able to create a virtual machine to run linux and MySQL so I can copy the data folder and then do an export dump; unless someone else knows of an easier way to get the data into my current database.

Any help or guidance on this issue would be greatly appreciated.

Thanks,
Nathan


texts: Using AWS DynamoDB Geospatial index with Python

texts:
Amazon's AWS DynamoDB features Geospatial Indexing, which facilitates geo queries:

  Query Support: Box queries return items that fall within a pair of geo
  points that define a rectangle as projected on a sphere. Radius
  queries return items that fall within a given distance from a geo
  point.

The problem is that the boto, the Python library for accessing AWS Service, lacks any reference to geo object. The Java equivalent, on the other hand, has such support.

Is there a way to use the geo features of DynamoDB on AWS using Python?


texts: Stored procedure that accepts an id as a parameter - how to execute for multiple ids list?

texts:
Hi I have the following stored procedure that works for a single id (field22):

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_procedure`(IN f22  INT)
BEGIN

UPDATE Table1 p join Table2 b set p.f1= (p.f1-  (LEAST(p.f2,b.amount)*coalesce((select rate from CurrencyExchangeRate where   fromCurrency=b.currency and toCurrency=p.currency and date(date)='2017-01-  15'),1))) ,
 p.f5= (p.f5- (LEAST(p.f2,b.amount)*coalesce((select f6 from   CurrencyExchangeRate where fromCurrency=b.currency and toCurrency=p.currency and   date(date)='2017-01-15'),1))),
p.f7= (p.f7- (LEAST(p.f8,b.amount)*coalesce((select rate from    CurrencyExchangeRate where fromCurrency=b.currency and toCurrency=p.currency and date(date)='2017-01-15'),1))) where status!=2 and p.id=b.f13 and b.id=f22;

UPDATE Tabl2 SET status=2, f1=3,changeDate=now() where id=f22 and status!=2;

INSERT INTO Table4 (type,f1,f2,date,f3,amount,f5,f6) select 11,2,tr.id,now(),tx.f11,tr.f12,tr.amount-tr.f13+tr.f14,tx.id from Table6 tr join  Table7 tx where tr.id=tx.f77 and tr.id=f22;

END

I would like to execute this stored procedure for a known list of ids i have:

  [1,22,443, 2112 ...];

Whats the quickest way to go about it?


texts:
Got a bit stuck, need a hand.

I need to store events at the database, and events may happen at

different location
multiple locations
at the same time

so these are my tables

CREATE TABLE location(id, name, address)

and

CREATE TABLE event (id, name, date)

Where should I add the location in this design?

The simple (id, name, date, location_id REFERENCES location) won't do, because one event may happen at different locations and I don't want to store 2+ rows of the same event since event is unique.


texts: Remote mysql only works from my computer, but no others

texts:
I need a direct connection to a MySQL server on a remote web server. So I've added to cpanel access hosts list an IP of the computer I wish to connect to the remote web server. But I can't connect, tried: telnet remote IP 3306, but no luck. I've tried on several computers with no luck. Only from my computer, it works. Am I missing something?

UPDATE:

I've contacted hosting company and they confirmed that they have firewall on, but how on Earth I could connect from my computer then? I think it will remain a mystery...


texts: MySQL generic trigger to save identification data for later accessing of the changed row

texts:
I am pretty inexperienced with this.

I need a generic trigger, able to create and save in a fixed table some sort of identification data for a changed row from generic (any) table. The identification data should be used later to SELECT the changed item in the given table.

Can be this done without previously knowing the table structure?

The only idea I had, but it's way too inefficient in my opinion, also requires previous knowledge of the table column names, is to save a hash by:

MD5(concat(NEW.field1, NEW.field2, NEW.field3, ...))

then

SELECT * FROM chaged_table WHERE hash = MD5(concat(field1, field2, field3, ...))

to identify the changed row in the table which triggered the change.

I would greatly appreciate any help or suggestions!


texts: Does updating a partially indexed field add or remove it from an index in Postgres

texts:
We can create a partial index for a field in Postgres.

I am looking at using this to stop rows which are archived from being in an index on a created_at field:

First we create the index:

test=# CREATE INDEX idx_my_table_created_at ON my_table(created_at) WHERE NOT(archived);

We insert two rows, one which gets in idx_my_table_created_at and one which does not:

 id |         created_at         | archived
----+----------------------------+----------
  2 | 2012-10-12 17:39:17.28511  | t
  1 | 2012-10-12 17:38:55.531278 | f

Now we UPDATE a row which is presently in the index, such that it shouldn't be in the index:

test=# UPDATE my_table SET archived = true WHERE id = 1;

My question: Is id = 1 still in idx_my_table_created_at?

This question follows on from an answer to a previous question I asked on how to handle rows which won't be queried once they are old, hence archived.


texts:
I came across a script written by one of my coworkers and found a line like the following

SELECT SUM(A) + SUM(B) FROM TABLE

I proposed a different approach

SELECT SUM( A + B ) FROM TABLE

Which line is most efficient?


texts: Changing a JSON object parsed with APEX_JSON

texts:
I would like to be able to change a JSON object parsed with the  APEX_JSON.parse() function, but I found nothing in the official documentation, no tips or examples about that.

In the code below I putted comments in the specific points in order to give a clear idea about my need.

SET SERVEROUTPUT ON

DECLARE
    j apex_json.t_values;
    l_paths apex_t_varchar2;
    writers clob;
    json_object_result clob;
BEGIN

    writers := '[
        {
            "id": null,
            "hashCode": "7ef605fc8dba5425d6965fbd4c8fbe1f",
            "description": "Machado de Assis",
            "base64Img": "8eNZ28Dh5rZQIPhNEfwqoo1LCVx..."
        },
        {
            "id": 151,
            "hashCode": "a8f15eda80c50adb0e71943adc8015cf",
            "description": "José Alencar",
            "base64Img": "/XIQIHCol8eNZ28Dh5rZQIPhNEfwqoo1LCVx/9k=..."
        }
    ]';

    apex_json.parse(j, writers);

    l_paths := apex_json.find_paths_like (
        p_values         => j,
        p_return_path => '[%]',
        p_subpath       => '.hashCode',
        p_value    => '7ef605fc8dba5425d6965fbd4c8fbe1f'
    );

    dbms_output.put_line('Itens found: ' || l_paths.count);

    if (l_paths.count = 1) then
        dbms_output.put_line('id: ' || apex_json.get_varchar2(p_values => j, p_path => l_paths(1) || '.id'));
        dbms_output.put_line('hashCode: ' || apex_json.get_varchar2(p_values => j, p_path => l_paths(1) || '.hashCode'));
        dbms_output.put_line('description: ' || apex_json.get_varchar2(p_values => j, p_path => l_paths(1) || '.description'));
        dbms_output.put_line('base64Img: ' || apex_json.get_varchar2(p_values => j, p_path => l_paths(1) || '.base64Img'));
--      Here I would like to nullify base64Img attribute value
        json_object_result := 'here goes the object matched in the search with the base64Img attribute value null';
    end if;

END;

In my json_object_result variable I would like to save the JSON object matched in the apex_json.find_paths_like() with its base64Img attribute null. In my example the result would be

{
  "id": null,
  "hashCode": "7ef605fc8dba5425d6965fbd4c8fbe1f",
  "description": "Machado de Assis",
  "base64Img": ""
}

or

{
  "id": null,
  "hashCode": "7ef605fc8dba5425d6965fbd4c8fbe1f",
  "description": "Machado de Assis"
}

Is it possible to make something like that with APEX_JSON?


texts: Query to Access a specfic data in the row using python

texts:
Am using wordpress and phpmyadmin for handling my database
Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as desired.

import MySQLdb as mdb
con = mdb.connect('localhost', 'root', 'password', 'WordpressDB', unix_socket="/opt/lampp/var/mysql/mysql.sock");
with con:
    cur = con.cursor()
    cur.execute("SELECT data FROM `wp_form_builder_entries` WHERE entries_id = 5")
    rows = cur.fetchall()

Output generated by this code is as follows:

Works just as desired but I don't want all the data. I just need to know whether its checked or unchecked. If the user has checked value is "s:9:'checked'" else its "s:9:'unchecked'"

What query should I use to retrieve "s:9:'checked'"?


texts:
Why does this query

DELETE FROM test
WHERE id = ( SELECT id
             FROM (SELECT * FROM test) temp
             ORDER BY RAND()
             LIMIT 1
           );

sometimes delete 1 row, sometimes 2 rows and sometimes nothing?

If I write it in this form:

SET @var = ( SELECT id
             FROM (SELECT * FROM test) temp
             ORDER BY RAND()
             LIMIT 1
           );
DELETE FROM test
WHERE id=@var;

then it work correctly - is problem in subquery?


texts: How locks in Postgres behave when quitting the JVM abnormally

texts:
I have a table 'car', as follows

                 Table "public.car"
  Column  |  Type  | Collation | Nullable | Default
----------+--------+-----------+----------+---------
 car_no   | bigint |           | not null |
 car_name | text   |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)

Table content:

 car_no |  car_name
--------+------------
      1 | first_car
      2 | second_car

I have a JAVA code, which locks a row in database and quits abnormally as follows,

public static void main(String[] args) throws SQLException {
        Connection connection =
                DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "root", "root");

        // begin transaction
        connection.setAutoCommit(false);

        System.out.println("waiting for lock "+new Date(System.currentTimeMillis()));
        // getting lock
        connection.createStatement().executeQuery("select * from car where car_no=2 for update");
        System.out.println("lock acquired "+new Date(System.currentTimeMillis()));
        System.out.println("press enter to quit abnormally [System.exit(1)]:");
        Scanner scanner = new Scanner(System.in);
        scanner.nextLine();
        System.out.println("terminating execution "+new Date(System.currentTimeMillis()));

        // quiting JVM abnormally
        System.exit(1);

        System.out.println("this doesn't prints");
}

When i executed the code snippet concurrently (two times), the first execution takes the row lock (waiting for System.exit), the second execution waits for the row lock. When first one quits abnormally, the second execution got the required lock. I wonder what happened to locks acquired by first execution, are they returned to database since abnormal termination or something else. I need some insights on row locking during these kind of scenario. Does it depends on transaction isolation level?


texts: How to import Dump of 12.2 to 12.1.0.2.0 (Higher to lower version)?

texts:
I took a backup of one database using expdp utility. Unfortunately, this database had been corrupted. I have another oracle server installed on another machine. I want to import available dump to this database. But this available database is in lower version 12.1.0.2.0.
ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 5.1 in dump file "D:\DB Administration\Backup Guides\Oracle Docs\dump\BACKUP_FULL_20200625.DMP"
I have tried expdp and sql developer to import in oracle 12.1.0.2.0. Found not a valid export.
OS: Windows server 2016
Utility : expdp


texts:
I have an array that I can update from multiple workers as follows:
UPDATE my_table SET arr = array_append(my_table.arr, element).
The problem pops up when two workers try to update the array at the same time.
For instance, both workers see that at the moment of the update, the array is empty and try to append an element as follows:
array_append(empty_arr, element_a) and array_append(empty_arr, element_b). In the end, the final content of the array will be: arr[element_a] or arr[element_b] because one of the workers will finish updating the array earlier and its value will be overwritten with the value from the other worker.
How can I ensure that both values are written in the array: arr[element_a, element_b]?


texts: Recover Oracle database after drive loss Interview Question

texts:
How would you answer this as an interview question?

  The database crashed and you lost a
  drive containing one of your control
  files and another drive containing an
  entire redo log group. You have a
  level 0 backup from yesterday morning,
  a differential backup from yesterday
  evening, and a cumulative backup from
  this morning. In addition three days
  of redo logs are on disk. You backed
  up the control file to trace just an
  hour before the crash. How do you
  recover the database?


texts: Running MongoDB and SQL Server on the same host- memory pressure

texts:
We have a new application that is making use of both traditional MS SQL and MongoDB for data storage. We're running both instances on the same Windows 2008R2 x64 machine, with 64GB RAM.

There have been some anecdotal reports of poor performance that I've begun to investigate.

I can see that SQL Server is not consuming as much memory as I would expect, and is showing signs of memory pressure.

Eg.

AppDomain 16 (mssqlsystemresource.dbo[runtime].15) is marked for unload due to memory pressure

In contrast, MongoDB is using a huge working set, which is expected behavior based on the documentation

It relies on Windows to manage memory use.

It seems that SQL server isn't able to, or isn't requesting use any of the memory in the Mongo working set and that is impacting performance. The server is under very low load at the moment.

Page Life Expectancy seems high at: 1536
and Buffer cache hit ratio: 404002
Free list stalls/sec: 695
Free pages: 26529

Has anyone come across this issue? What it the expected behavior of SQL server in this scenario? I'm considering using the min server memory configuration option.


texts: How to create read-only user for schemas which can be created in the future?

texts:
SO and Internet contains a lot of answers to question how to create read only user (for example see this article), BUT all of them promote the following way:

Create user
Grant USAGE privilege to all existing schemas
Grant SELECT privilege to all existing tables in all existing schemas
Grant default SELECT privilegie to all tables (which can be created in the future) for all existing schemas

But what to do if some schemas can be created in the future too?

I mean I want to grant read only permissions to these schemas (and tables in these schemans) created in the future without any additional configuration. Is it possible?


texts: EMCA insists that it cannot connect to the service

texts:
Really new to Oracle and being a DBA in general. I am trying to set up a development environment so that I can play an learn oracle better. Enterprise Mananger failed to configure itself when I first created the database using the Database Configuration Assistant. No biggie. Just need to user emca.exe

I had some issues with the Listener but those might have just been me being impatient in waiting for the service to register or the service not running. Right now my issue is this from the emca log:

CONFIG: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

According to post here and on SO it should just be an issue with the service name. Problem is it looks right to me.

Listener.ora

ADR_BASE_VLISTENER = D:\app\Administrator

VLISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dvp-oracle)(PORT = 1521))
    )
  )

lsnrctl status

.... truncated ....
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dvp-oracle)(PORT=1521)))
Services Summary...
Service "prodbkp" has 1 instance(s).
  Instance "prodbkp", status READY, has 1 handler(s) for this service...
Service "prodbkpXDB" has 1 instance(s).
  Instance "prodbkp", status READY, has 1 handler(s) for this service...
The command completed successfully

That tells me that the database registered itself dynamically correctly. prodbkp is my SID. This might be a case issue since I named the DB "PRODbkp" but everything else seems to be fine since I can connect with sqlplus sys@prodbkp as sysdba just fine. Case should not be an issue with service names as per docs.oracle.com

tsnnames.ora

PRODBKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dvp-oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodbkp)
    )
  )

Which also looks OK. Most of the solutions point to SERVICE_NAME mismatches. However I don't appear to have one.

v$parameter output

SQL> select value from v$parameter where name='service_names';

VALUE
--------------------------------------------------------------------------------

prodbkp

That gap you see above prodkbp is in the output as well. Not sure if that matters either. My production server has that anomaly as well so I can't imagine that has anything to do with it.

Basically everything looks right but something is still wrong. I cannot install Enterprise Manager

Environment

Windows Server 2008R2
Oracle 11g


texts: Why are NoSQL databases not ACID compliant?

texts: Not having ACID properties means that the database works well on clusters. But ACID is something very fundamental. How can a database work well if there is no atomicity, consistency, isolation and durability (ACID)?


texts: What is the effect of number of CPU cores on update operations in Postgres?

texts:
I have batch update operations being sent to the PostgreSQL server to be executed. Each operation (batch) will be updating rows in the same table but it is assured that they will be updating different rows.
Given this, is it safe to assume that the a 2 fold increase in throughput can be countered by doubling the number of CPU cores?


texts:
I am working on a web app for an ESL institue. The app will be used only by the staff members (Not the students) to manage classes, payments, and student data.
I have gathered all of the data from the client, and according to the data I have came up with the following database schema.

I would be happy if someone takes a look and points out any flaws in the design or gives me recommendations to simplify it / make it more efficient.


texts: SQL Server Distributed Availability Group databases not syncing after a server reboot

texts:
We're getting ready to perform a large upgrade on our SQL Servers and are noticing some unusual behavior with Distributed Availability Groups that I'm trying to resolve before moving forward.

Last month, I upgraded a remote secondary server from SQL Server 2016 to SQL Server 2017. This server is a part of multiple Distributed Availability Groups (DAGs) and a separate Availability Group (AG). When we upgraded this server, we were unaware that it would get into an unreadable state, so during the past month we've solely been relying on the primary server.

As a part of the upcoming upgrade, I applied the CU 4 patch to the server and rebooted it. When the server came back online, the just-patched secondary showed all of the DAGs/AGs were syncing without any issues.

However, the primary was showing a very different story. It was reporting that

the separate AG was syncing without any issues
but the DAGs were in a Not Synchronzing / Not Healthy state

After initially panicking, I attempted the following things to get things synchronizing again in the DAGs:

From the primary, I stopped and resumed the data movement. This did not start syncing the data.
On the secondary (the one I just patched) I ran ALTER DATABASE [<database] SET HADR RESUME; - which execute without errors, but did not resume any syncing

My last attempt at syncing the data again was to login to the secondary, and manually restart the SQL Server service. Manually restarting the service seems a bit extreme, as I'd expect the server being rebooted would have been enough.

Has anyone run into this issue where a DAG doesn't start syncing to a secondary after a reboot? If so, how was it resolved?

I checked both the SQL Server error log, and the event viewer on the secondary server, there was nothing out of the ordinary that I could see.


texts: InnoDB engine seems lock the table automatically in Mysql5.7

texts:
I have a table named Students like:

mysql> select * from students;
+----+-------+---------+
| id | name  | classId |
+----+-------+---------+
| 1  | Kris  |       7 |
| 2  | Tom   |       7 |
| 3  | Mary  |       2 |
| 4  | Leon  |       2 |
| 5  | Mario |       3 |
+----+-------+---------+
5 rows in set (0.00 sec)

I execute the following sql statement, but don't give the commit statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 1;

After that I will open another session and execute the following sql statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 2;

Now, the whole shell screen will block and not respond. After a while it will give me an error, similar to this one:

1205 Lock wait timeout exceeded; try restarting transaction

So, I am confused as to why does the InnoDB engine give me a row-level lock on the table? In this case, why does MySQL block my updates to another row?


texts: Conditional compilation of SQL Server stored procedure

texts:
Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010?

I'm using SQL Server Data Tools in Visual Studio 2010 to work on an experimental SQL Server Express database. The eventual destination if things work well would be an enterprise SQL Server platform. I have both a 2008 instance on one box and a 2012 instance on another, because my company is in the process of migrating from 2008 to 2012 for the many enterprise databases as well.

In other programming languages I have used, preprocessor directives make it easy to conditionally compile parts of a code base. The most common uses for this are to have different code for different platforms in restricted sections or to excluded debugging output code from release builds.

Both of these could be very helpful in some store procedures I'm working on. Is there anything like this available? I know I can use sqlcmd variables to swap out specific values during deployment, but I can't figure how to use that to include or exclude subsequent chunks of code.

Example:

#IF $(DebugVersion) = 'True'
    -- A bunch of useful PRINTs and what not
#ELSE
    SET NOCOUNT ON
#ENDIF

#IF $(SSVersion) = '2012'
    SET @pretty_date = FORMAT(@some_date, 'dddd, MMM dd, yyyy')
#ELSE
    SET @pretty_date = CAST(@some_date AS nvarchar(12))
#ENDIF


texts: Error Correction: Conversion Failed when converting the nvarchar value 'ALPHANUMERICVALUE' to data type int

texts:
I am running into this error whenever I run my query.

I believe to fix this issue I have to Format the column which holds those values into a number by itself. I have already tried the Format() and Replace() functions, but they did not work for me. Any other suggestions?

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
   AS 'Previous Odometer', so.odometer - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) = 0, so.odometer,
   CASE
      WHEN
         COALESCE( Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) AS 'Miles Traveled', sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
   AS 'Previous Hours', sh.hour - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) = 0, sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons AS [Total Gallons], fta.fifototalcost AS [Total Cost]

The e.AccountingCode column contains the value that is at error.


texts: function to receive a character input and return date format (with incorrect input)

texts:
I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect inputs like this "2012ABCD". In that case, I want the function to return a fix date such as 2020-01-01.  What I've written so far is:
Create Function ReturnDate
(@date varchar(8))

Returns date

  as

    begin
       declare @result date

          set @result = (select convert(date , @date,111))
                if(@@ROWCOUNT>0) return @result
                 else return '2020-01-01'
       return @result
    end

This doesn't work and I just don't know how to handle the second part (when the input is incorrect).


texts: Does spreading table data across multiple files (in the filegroup) improve performance?

texts:
From the product documentation on filegroups:

For example: Data1.ndf, Data2.ndf, and Data3.ndf, can be created on
three disk drives, respectively, and assigned to the filegroup
fgroup1. A table can then be created specifically on the filegroup
fgroup1. Queries for data from the table will be spread across the
three disks; it will improve performance.

I understand that if there is a very large table, and if I place it in a file group that has a single file and if this is on a separate disk, then it will improve performance because all queries to this large table will go to the separate disk.
I also know about table partitioning using multiple filegroups; each filegroup with one or more files. However this question is specifically about file group in a scenario of an unpartitioned large table.
In the above example, the author says that the performance is improved even if the file group contains multiple files. I am confused about this because if a table is spread across multiple files, then will this not cause slow performance (because the rows will be spread across the files and so to go from one row to the next row which maybe on another file, wont it take a hit on performance)?


texts: Caveats of OUTER JOIN on nested JSON value

texts:
I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I right join on that query to get what I need, and this works well:
select v.id from (
    select distinct json_data ->> 'elementId' as elementId
    from content
    and json_data->> 'elementId' in ('id1', 'id2', 'id3')
) as a
right join (values('id1'), ('id2'), ('id3')) as v(id)
on v.id = a.elementId
where a.elementId is null

The above query works perfect except for the fact that I want to I should be able to reduce the nested query to a regular select if I do the comparison on json_data ->> 'elementId' directly.
My attempt:
select v.id
from content a
right join (values('id1'), ('id2'), ('id3')) as v(id)
on json_data ->> 'elementId' = v.id

After some debugging I realized that this will never work because the content table will always contain a row even if json_data ->>'elementId' is null.
Edit: I had an extra WHERE statement which wasn't stated in the question, once I moved this after the ON my query was fixed
My question is; Is there a way to avoid using a nested query when wanted to do a left join or right join  on JSON data?


texts:
I have a problem with the following query executing from the application (Microsoft Dynamics AX):

DECLARE @p1 INT;
SET @p1 = NULL;
DECLARE @p2 INT;
SET @p2 = 0;
DECLARE @p5 INT;
SET @p5 = 2 + 4096;
DECLARE @p6 INT;
SET @p6 = 8193;
DECLARE @p7 INT;
SET @p7 = 0;
EXEC sp_cursorprepexec
     @p1 OUTPUT,
     @p2 OUTPUT,
     N'@P1 nvarchar(5),@P2 int,@P3 nvarchar(5),@P4 int,@P5 nvarchar(5),@P6 nvarchar(5),@P7 datetime,@P8 nvarchar(21),@P9 numeric(28, 12)',
     N'SELECT A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ FROM INVENTTRANS A,INVENTTRANS B,INVENTTABLE C WHERE ((A.DATAAREAID=@P1) AND (A.TRANSTYPE=@P2)) AND ((B.DATAAREAID=@P3) AND (((B.INVENTTRANSID=A.INVENTTRANSIDFATHER) AND (B.TRANSREFID=A.TRANSREFID)) AND (B.TRANSTYPE=@P4))) AND ((C.DATAAREAID=@P5) AND (C.ITEMID=B.ITEMID))
AND EXISTS (SELECT ''x'' FROM INVENTSETTLEMENT D WHERE ((D.DATAAREAID=@P6) AND ((((D.TRANSDATE=@P7) AND (D.VOUCHER=@P8)) AND (D.TRANSRECID=A.RECID)) AND (D.COSTAMOUNTADJUSTMENT<>@P9))))
 GROUP BY A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ ORDER BY B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_',
     @p5 OUTPUT,
     @p6 OUTPUT,
     @p7 OUTPUT,
     N'dat',
     8,
     N'dat',
     2,
     N'dat',
     N'dat',
     '2017-05-17 00:00:00.000',
     N'IM17008141934',
     '0.000000000000';
EXEC sp_cursorfetch
     @p2,
     2,
     1,
     1;

The estimated plan for this query is here

XML version here

The query keeps running for almost 3 hours. I have also captured metrics from WhoIsActive sp for this queries session:

wait_info                       (2ms)PAGEIOLATCH_SH:ecc_wrk:4(*)
CPU                             88,765
CPU_delta                       47
tempdb_allocations              0
tempdb_current                  0
tempdb_allocations_delta        0
tempdb_current_delta            0
blocking_session_id             NULL
blocked_session_count           16
reads                           26,610,593
reads_delta                     20,903
writes                          0
writes_delta                    0
physical_reads                  1,212,764
physical_reads_delta            418
used_memory used_memory_delta   276
status                          0
open_tran_count                 1
host_name                       ***
database_name                   ***
program_name                    Microsoft Dynamics AX

As I can see it reads huge amount of data and executes for enormous amount of time.
I also captured the query and executed it in management studio where it took 1 second to complete. The actual execution plan is here:

XML version here

I cannot understand what the problem might me and where to dig. Any help on what to do next will be deeply appreciated.


texts: Is there any sense in using Postgres array of JSONB values instead of only JSONB?

texts:
Is there any reason to use array of JSONB values instead of just raw JSONB?

My use case is that I need to store custom forms definition in list of JSON objects. And my question is whether it would be better to store it as raw JSON value or split this into separate JSONB objects.

What are pros and cons of each solution?


texts: Compress JSON String Stored in PostgreSQL, such as MessagePack?

texts:
JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string:

{"0.000295":1544.2,"0.000324":1050,"0.000325":40.1,"0.000348":0}

Question: Is it possible to store it in a format that consumes lesser space? Using some CPU cycles to serialize/deserialize the JSON string is acceptable as a compromise for using lesser storage space. The JSON data does not need to be searchable. The JSON object keys are almost always different in different rows.

I am particularly interested in using JSON encoding/compression algorithms like MessagePack with zlib, but how can we use this when inserting the record into the PostgreSQL table?

Note: Also using Node.js with Knex.js to communicate with PostgreSQL. Currently converting JSON objects to strings using Node's JSON.stringify function.


texts:
How can I create a constraint on a sub-field of composite type?

Pseudocode

create type axis(
    major_axis float,
    minor_axis float,
    angle float constraint angle_constraint check(angle between -90 and 90)
);

create table sample(
    axis1 axis,
    axis2 axis
);

Is this possible in PostgreSQL 9.2? It seems to be not possible in 9.1 as mentioned here.


texts: Get the nth distinct column value ordered by another column

texts:
I want to get the row with the nth distinct value in a column, as ordered by another column.

In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the email_address column does not have a unique constraint.

I believe I have done this correctly with the following query:

SELECT `email_address`
    FROM footable
    GROUP BY `email_address`
    ORDER BY `created_at` ASC
    LIMIT 1
    OFFSET 1000

But I am not totally confident that is giving me the right value.

Is the above query correct for this purpose? If not, how should I solve this?


texts: sysadmin security error creating Reporting Services cache refresh plan in Report Manager

texts:
On SQL Server 2012 (SP1) Reporting Services (native), when creating a cache refresh plan in Report Manager, I receive the error:

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.

This is unexpected because:

Isn't it creating a job, not updating or deleting an existing one?
The owner_sid of all the existing jobs in the "Reporting Services" category is that of NT Service\ReportServer. (Not sure how these were ever successfully created, somebody else must have created them.)
NT Service\ReportServer is in the SQLAgentOperatorRole in msdb.
The service is indeed running as NT Service\ReportServer.

I can "fix" this by giving NT Service\ReportServer the sysadmin (thus verifying the login being used is really NT Service\ReportServer). However, that level of access shouldn't be necessary given the facts above.


texts: DB comparer field by field for all tables

texts:
I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added fields and tables and so on. I want to be able to detect those changes.

Could you please let me know some famous tools that are free and does this job for me so I don't have to remember or write the changes in notepad and later on apply them.


texts: Can SQL use an index while creating another?

texts:
I just had a situation where I had to include a column in an index. This required dropping the index, and recreating it. This got me thinking, surely that's a lot of unnecessary work.

Say I had instead created the new index first, then dropped the old one. Let's also assume that I have some way of indicating that the old index will not be dropped until after the new one has been created.

Would the server get any performance benefit by using the old index while creating the new one?


texts: Azure SQL: Azure SQL Database traffic migration to newer Gateways

texts:
I have been assigned a task to look at this: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-gateway-migration. I got an email about it:

I have a pretty standard setup, with a Azure SQL server and one database, that my services connect to through a connectionstring in some resoruce groups in Azure. Pretty standard and straight forward. I don't think I am affected by this, but still, I got the email, and now I have find out. I have never heard of gateways and such.

Do I need to do anything if my services and databases is completely hosted in Azure? I cannot read that from the email.


texts: Improving query performance on mysql star catalog

texts:
I have a mysql database that contains a table with roughly 645 million records. I am trying to improve the query performance. Below is the SQL for the table as well as the indexes I use.

CREATE TABLE `Catalog` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`sourceFile` CHAR(5) NOT NULL,
`starID` BIGINT(20) UNSIGNED NOT NULL,
`ra` DOUBLE NOT NULL,
`decl` DOUBLE NOT NULL,
`muRA` FLOAT NOT NULL,
`muDecl` FLOAT NOT NULL,
`magB` FLOAT NOT NULL,
`magV` FLOAT NOT NULL,
`magU` FLOAT NOT NULL,
`magG` FLOAT NOT NULL,
`magR` FLOAT NOT NULL,
`magI` FLOAT NOT NULL,
`magZ` FLOAT NOT NULL,
`magY` FLOAT NOT NULL,
`magJ` FLOAT NOT NULL,
`magH` FLOAT NOT NULL,
`magK` FLOAT NOT NULL,
`w1` FLOAT NOT NULL,
`w2` FLOAT NOT NULL,
`w3` FLOAT NOT NULL,
`w4` FLOAT NOT NULL,
`magSST` FLOAT NOT NULL,
`flag` INT(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `UFilterIndex` (`magU`, `decl`, `ra`),
INDEX `GFilterIndex` (`magG`, `decl`, `ra`),
INDEX `RFilterIndex` (`magR`, `decl`, `ra`),
INDEX `IFilterIndex` (`magI`, `decl`, `ra`),
INDEX `ZFilterIndex` (`magZ`, `decl`, `ra`),
INDEX `YFilterIndex` (`magY`, `decl`, `ra`)) ENGINE=MyISAM;

My typical query looks something similar to this:

SELECT * FROM `Catalog`
WHERE
`magG` >= 7.32 AND `magG` <= 15.68 AND
`decl` <= -1 AND `decl` >= -2 AND
`ra` >= 75 AND `ra` <= 75.23

It takes on average 17 seconds to execute this query. When I EXPLAIN the query I can see that it is using the GFilterIndex using index condition. The types of queries I perform will always involve magG and decl. The queries will also involve ra the vast majority of the time. So I think my index is doing what I want it to do. When I attempt to query without the index I get query times of 8min+.

This database won't change (no inserts or updates). I will be performing thousands of these queries and at ~17 seconds each it will be very painful. Is there something I can do to further improve the query performance? Thank you for any help you can offer.


texts: Does SA account need to be called "SA" or can I rename it safely?

texts:
We have some applications on our network (from before my time) that use SA account in their SQL Server connection string. It's hard-coded in the source code, and for whatever reason, we can't change it - my question isn't about why we should change it, but about working around it.

I'm considering renaming the SA account to something else (like "SysAccount" or something like that, and giving it a new password) and then creating a new account called "SA" with the old password and granting it rights appropriate for the application (obviously not membership in the sysadmin role). Are there any pitfalls to doing this? Are there known problems SQL Server will have if the account called SA isn't actually a sysadmin?

I assume that since I'm just renaming the account, I'm safe - it will still have a uid of 0x01, and I've tested this and it's physically possible and appears to work properly in testing, but I want to make sure I'm not overlooking anything that will clearly break as a result. If it breaks things,  I can always delete the new SA and rename the old one back to undo the damage, but hoping to avoid trying and failing.

I'm using SQL Server 2012, though I suspect the same answer will apply to any modern version. I've seen the bug where the upgrade from 2005 to 2008 can break if you've done this, but I imagine that's long since resolved.


texts: Importing dbatools module into Azure Automation Account

texts:
Has anyone managed to import dbatools module into Azure Automation Account? I tried to do it in several ways, but every time the installation fails:

different versions checked from 0.9.* to the latest 1.0.9
from portal using Automation Accounts -> Automation modules -> Browse Gallery
from portal using Automation Accounts -> Automation modules -> import from file

Every time I got:

  Error importing the module dbatools. Import failed with the following
  error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: An
  error occurred during module validation. When importing the module to
  an internal PowerShell session, it was not able to be loaded by
  PowerShell. There is likely an issue with the contents of the module
  that results in PowerShell's not being able to load it. Please verify
  that the module imports successfully in a local PowerShell session,
  correct any issues, and then try importing again.

As you can see the error message doesn't tell much (and obviously I am able to import it locally), thus I wonder if anyone managed to go over it somehow.


texts:
Let's say you have a table of invoices

CREATE TABLE Invoice
(  InvoiceID int IDENTITY(1,1) PRIMARY KEY,
   InvoiceDate DateTime CONSTRAINT [df_invoicedate] DEFAULT GETDATE(),
   ....
)

The invoice table has an InvoiceDate which is always the current date/time when the record is inserted.

Let's say you index this date

CREATE INDEX idx_InvoiceDate ON Invoice(InvoiceDate)

Note this is a non-clustered index (held in a separate file).

Let's say we know the RDBMS uses B+tree format for index files.

Bearing in mind that all inserts will hit the same page of the index file (because they are always at the end of the sort order), and that inserts into a single location are worst-case for B+tree insert, what special considerations should be observed?

Will this index end up lopsided?  Should it be reindexed regularly?  Should I use a low, normal, or high fill factor?

Should we try to find something else to put into the index prior to the date, e.g. an invoice type, to get the tree to balance out more evenly?

Will there be issues with hotspots or index page contention, because all inserts hits the same part of the index?  What can I do to mitigate that?

I understand that for an ascending surrogate key, a reverse key index might be better.  But this is a date and I may want to perform scans of a date range.  Is there anything I can do that will alleviate any of the issues that reverse key indices are meant to address?

Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?


texts: How to import a varbinary(max) column with bulk import?

texts: I have a varbinary(max) column that I need to bulk import.  The data I'm putting into this column is a byte[] in C#.  How do I format the data in the comma-delimited file used for bulk import?  In other words, how do I convert from a byte[] to a string in a way the bcp utility will accept?


texts:
I have a server using MongoDB Community but my client wants additional features that are only able using the Enterprise version.

We're about to buy it, however, I was wondering what should I do to upgrade from Community to Enteprise, and guarantee that all additional features are available.

Is there a license file to add? Should I install from scratch and restore backup? How should I proceed?

Thanks,

Vinicius


texts:
I've been reading about MySQL's file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression.

My server is currently using Antelope, as it is the default of MySQL.
I've had many times issues with memory due to the large database I have. My database is increasing every day.

It seems Compression is giving benefits to a few people, like:
http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

I understand memory and disk space can be lower, but I'm not sure if I understand this (quoted from the article):
"~5% CPU load according to top (from 80-100% mostly waiting for I/O)
0.01 sec average lookup time by primary key (from 1-20 sec before the conversion)"

I thought these two things would NOT improve, because if the data is compressed, the server has to uncompress in order to get the original data again, so doesn't that make sense that CPU usage would increase?

Does that benefit you in read/write intensive applications? Would you recommend me to change to Barracuda and Compression?

Are you aware of any issues of Barracuda?
It seems the answer of the following question points a few issues, but since it's from 2011, I'd say they are fixed by now: https://serverfault.com/questions/258022/mysql-innodb-how-to-switch-to-barracuda-format


texts: Solution for a readonly copy of a standalone MongoDB server

texts:
I store some data in many databases/collections on a standalone MongoDB server. This server is used for production.

I need to access those data (or at least nightly build data, and not all databases/collections), for data analysis purpose : readonly, possibly high consumption queries. I don't want to operate data analysis on the production server.

For now, I use mongoexport and mongoimport to export and re-import the data into another standalone server. This has many drawbacks :

indexes are not kept,
night export/re-import is a long-time process that may fail,
data is not continuously synced (not absolutely necessary).

I studied the MongoDb replica set documentation, but some questions are still unanswered to me :

Can I choose which databases/collections are replicated ?
Is a 2-members (a primary and a secondary) replica set eligible ?
Can I make our production application servers "always" read-write on the primary, and our data-analysis server "always" read on the secondary ?

I know I have many questions and it might break Stackoverflow Q/A principles, but I hope the question label could be useful for many others.


texts: Why am I getting this network error when setting up database mirroring?

texts:
I have two servers, one in the Portsmouth, NH datacenter, one in the Kansas City datacenter, same domain.

Trying to set up mirroring. First, does it matter that one server has Windows 2008R2 server and the other has 2012R2 server on it?

Have opened the firewall ports between the two servers, can telnet back and forth between the two.

Go through all the mirroring steps and get the generic Microsoft error:

  The server network address "TCP://--------.lm.lmig.com:15025" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

The SQL Server version is 2008 SP3 Standard Edition.


texts:
Using SQL Server 2017, when I try to manually failover AG to the secondary node by using Failover wizard in SSMS, I got following error

Cannot failover an availability replica for availability group since it has CLUSTER_TYPE = NONE. Only force failover is supported in this version of SQL Server. (Microsoft SQL Server, Error: 47122)

Failover Readiness shows "No data loss" and I used Automatic seeding to initialize the database on secondary, Readable secondary is set to yes on the secondary.

When I tried manual failover on the secondary the status of AG is not Synchronizing with the following warning:

The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state.

Any help is appreciated.


texts: When was the syslogin's password last updated?

texts:
In SQL Server, I see there's a field in syslogins titled "updatedate" which is a timestamp of the last time the login was updated.

What constitutes an "update"?
My goal is to only get a timestamp of the last time a password was changed.


texts:
I was using SQL in all my previous application and to understand the Parse.com db storage, I wanted to know NOSQL.

I tried searching the differences between SQL and NOSQL for past 2 days & I still don't understand how a relation works in NOSQL.

I learned that NOSQL is schemaless and there's no structure like SQL. Let me brief my question in an example.

Example :

In a SQL application for School management, I would create a table students which stores students' information. For marks, I'll have a marks table which has a column user_id that refers to students table. I'll have one more table subjects which will hold sub_id, sub_code and subject_name.

So, if I change sub_code in the subjects table, that will simply reflect to the other tables where FOREIGN KEY is used by using ON UPDATE DELETE CASCADE.

Now my question is,

  since NOSQL doesn't use NORMALIZATION, how will the data be stored?
  Should a single table (document in NOSQL) hold all the
  information that is in students table, marks table and subjects table?

  If we dump all the information into one single table, how efficient will it be?

If we want to change a subject code, then we need to update all the documents, right? (In SQL, we simply change subject code in one table, since other columns in other table which are referencing this column subject code will simply update right rather than updating each single entries?)


texts: (MySQL) Finding the first, second, ...., nth movie directed by a group of directors

texts:
There's a table titled "movies". The table can be found here. (Please scroll down to the bottom of the page to see the table).

I want to find the list of first movies directed by each director in the list.

I've written the following query

SELECT director, title AS first_movie, MIN(year) AS year FROM movies GROUP BY director;

I did get the desired results but I'm wondering if there's any better way to do this.

Same way I want to find the list of second movies directed by all the directors in the list. How do i do it?

And finally, is there a general way to arrive at the list of nth movie directed by all the directors in the list?


texts: PostgreSQL multidimensional array - ok to use or misdesign in this case?

texts:
I have a table called listings. For each listing, the user can upload 5 images. In the table listings, I have a multidimensional array images text[][]. Each multidimensional element will hold [ a base64 string, filename of image].

The only time I would do a search on the images[][] array is if the user removes a image, I would search for the filename of image in the images[][] array and remove it.

I would really like to avoid having a table just for images. No image will ever belong to more than a single listing I can not justify the cost of foreign key looks ups for what there is really no benefit.

I read in PostgreSQL documentation:

  Arrays are not sets; searching for specific array elements can be a sign of database misdesign.

Since the only time I am searching for a specific array element is when a user deletes a image... does this constitute a database misdesign? I was really hoping to use denormalization for the images of a listing.


texts:
Hello all I referred to this https://stackoverflow.com/a/5613926/842112

In that they mentioned this

  if your pictures or document are typically over 1 MB in size, storing
  them in the filesystem is more efficient (and with SQL Server 2008's
  FILESTREAM attribute, they're still under transactional control and
  part of the database)

Now my question is how to use File stream attribute in SQL Server 2008? I referred to http://msdn.microsoft.com/en-us/library/cc949109.aspx but I don't get how to use this. So please suggest me how to use this I want to store images in a database table. Thank you.


texts: VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

texts:
I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have space to restore a copy on another server connected to the same storage.

I need to be able to run checkcatalog, checkalloc and checktable but even for small tables it doesn't appear to be able to create the internal sparse file dbcc snapshot.

So I was wondering if I can create a SQL database snapshot on another drive and then run those commands against that and then drop the snapshot db once complete.

Thanks!

--Todd


texts:
I'm trying to migrate MySQL user ids to MongoDB. user ids are incremental values like 1,2,3,4...

Is there a way to store int in ObjectID?


texts: SERIALIZABLE transaction information leak via serial

texts:
I'm trying to implement a deterministic configuration for database sessions, and one problem seems to be with sequences. The following example demonstrates:

CREATE TABLE foo (id serial, n integer);

-- connection 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

-- connection 2
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
insert into foo (n) values (1),(2),(3);

-- connection 1
select nextval('fun_id_seq') - (select count(*) from fun) as leak;

leak
----------
       4
(1 row)

This looks like a dirty read. If I was to run the transactions serially I would not get this result. Why is this then considered SERIALIZABLE?

Thanks


texts: What type of primary key we need to use for specific length of bits in Cassandra?

texts:
I want to know in which situations which type of primary key I need to use.
Situations:
1. If I need 64 bit key than is bigint is the best for it? If I will chose text type will performance will be the same if I will put keys which is not more then 64 bits?
2. If I need a primary key which is more then 64 bits and less then 128 bits (let say 80 bits) then which data type I have to use for primary key?


texts: Good practices for proactively preventing queries from randomly becoming slow

texts:
We support several deployments of an in-house application based on SQL Server.
We've had a problem several times now where some query that used to be quick and small suddenly becomes very slow or erratic, while not necessarily producing more data or being run on a larger dataset.
Every time this happens, it is a different query that suddenly starts running slowly.  Whenever this happens, we have to spend some development time to debug the problem, maybe write the query again and benchmark against the previous version, etc. Yet the original query often worked just fine for years, and we had no prior indication that its execution time might explode.
What are some good practices that would prevent issues of queries randomly becoming slow?
What I am after is not "how to fix a query that we already observe being slow", but how to reduce the likelihood of queries becoming slow in the first place—a proactive approach, as opposed to reactive.  I need a way to force SQL Server to not try to optimize the query too much: I don't really need fast, I need consistent.  If there were some choices to have SQL Server be conservative in building the query plan, as opposed to trying to take advantage of data distributions and such, I'd be after them.


texts: Database denormalization: multiple rows into single column. How bad is it?

texts:
We have few join-heavy queries to our SQL Server 2008 database and in an attempt to optimize them I thought of having a view that would produce this

OrderID     Number    Traveller
1           102       1015;John Doe|1016;Jon Appleseed

out of

OrderID     Number
1           102

TravellerID    OrderID    FullName
1015           1          John Doe
1016           1          Jon Appleseed

My question is: how bad is it from a DBA's standpoint? On the one hand, it gets the job done, but this solution really bothers the purist side of me.


texts: Installing Patches on MS SQL Server Tips

texts:
What is preventing Agent Service from starting after:

Failing to install CU.
Running a successful repair after disabling HIPS.

Background:
Installing CU's for Microsoft SQL Server I found I have an issue with the security software on my system.
After disabling the offending security software preventing my installation of patches and the repair process afterwards, I now have an issue with restarting the agent service.
Error during repair states:
Microsoft SQL Server #### Setup
The Following error has occured:
The process cannot access the file 'C:\windows\system32\perf-MSSQL13.MSSQLSERVER-sqlagtctr.dll'
because it is being used by another process.
Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

I am thinking I do not need to repair the service just yet, but something will need to be fixed.
New thoughts?


texts: Trans Replication and Always On Listener Group Name

texts:
We have currently (SQL Server 2012 STD) which is continuously replicating from 8 different database server for reporting purpose to server which is on SQL 2008R2. We would like to create an Always On Availability group for this reporting database so we can have multiple replicas. We have 8 database servers as our 2000 stores are round robin on these 8 server. To get data into one reporting database with multiple replica, we would like to do the following:

Create Transactional Replication from this 8 database server (i.e.SQL Server 2012) to 1 SQL Server 2012 database server which would be the Primary Replica for AOG
Create multiple replicas from this Primary using normal AOG system

Is there any way to make the Primary Replica as a Subscriber coming 8 database servers?
I tried to create the AOG Listener group and tried to use that name while adding Subscriber to replication, but it says it needs a Server Name.
Can somebody please help me on this?

Thank you,
HP


texts: COUNT(*) gives more than 1 with LIMIT 1?

texts:
I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case?

SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1;

 count
--------
1160
(1 row)

I expected a result of 1 or 0, but it gives 1160. Why?


texts: Log File Size near 100% - How to find Active Transaction

texts:
The transaction log file size is nearing 100% on one database.
I run the below and both return 0 so no active transaction running.

The last query tells me that the database is in SIMPLE mode, but 'ACTIVE_TRANSACTION' is returned in log_reuse_wait_desc column.

However, I cannot see any running transaction! I have tried querying sys.dm_tran_database_transactions but do not see any transaction for this specific database.

How do I find this transaction?

SELECT @@TRANCOUNT;

SELECT XACT_STATE();

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;


texts:
I'm running a data migration. The process is moving data from a query on a SQL Server 2005 database to a SQL Server 2008 R2 database single table destination.

The source query is a join between 2 tables. I cannot change the query or the app that is running the data migration. The process is moving around 70 million rows from the source server to the destination.

The process runs very well for 2 hours, moving around 400,000 rows per minute and looks like the total processing time will be about 2 hours. At around 46 million rows the process slows to around 5,000 rows per minute (almost 100 times slower).

Why would this happen?

I've looked at the query plan and I don't see anything that I can tune. I have been seeing CXPACKET waits (using Adam Mechanics sp_WhoIsActive).

(2x: 9078ms) CXPACKET:1,
(1x: 1078ms) ASYNC_NETWORK_IO).

I changed the MaxDOP of the server to 1 in order to resolve the CXPacket but I haven't seen any change.


texts: Postgresql - Segmentation fault (core dumped)

texts:
I'm working on postgresql code (server side).
I installed a version from source code following the official tutorial:
http://www.postgresql.org/docs/8.4/interactive/install-short.html

I did same modification on code, so I need to install again postgresql, but this time my version.

I renamed the /usr/local/pgsql/ in /usr/local/pgsql2/ and I did:

make distclean
./configure
make
sudo make install
sudo mkdir /usr/local/pgsql/data
sudo chown darkcoffeelinux /usr/local/pgsql/data

until now everything went well, but with the command

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

I got this error:

The files belonging to this database system will be owned by user "darkcoffeelinux".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... Segmentation fault (core dumped)
child process exited with exit code 139
initdb: removing contents of data directory "/usr/local/pgsql/data"

Additional information: I'm using kubuntu, postgresql 8.4.15


texts: Can't connect to remote SQL server from some machines

texts:
Our web server uses SQL Server 2008 R2. I have one development machine that connects to it with the IP address through SSMS and when testing my aspx website. I have several other machines though that can't connect to the IP through SSMS. I'm getting this error:

  A Network-related or instance-specific error occurred while establishing a
  connection to SQL Server.
  The server was not found or was not accessible. Verify the instance name is correct and the SQL Server is configured to allow remote connection.
  (provider: Named Pipes provider:40-could not open a connection to SQL Server)
  (Microsoft SQL Server, Error:53)

I've got TCP/IP enabled, port 1433 open, all of that. Remember that I can connect from one development machine, but not others.


texts: Does adding page compression to a table also compress existing indexes?

texts:
I am creating a series of new tables that will have page level compression enabled.

Will this affect the indexes as well, or will I have to also specify compression when creating the indexes?

create table Message
(
  pk_Message   int identity  NOT NULL constraint PK_Message primary key,
  [Message]    varchar(900)  NOT NULL constraint UK_Message__Message unique,
  DateCreated  datetime      NOT NULL default getdate()
) with (data_compression = page)
go

AS you can see in my example, I plan on creating a unique constraint on a rather large data type, and want to take advantage of compression.


texts:
I have the following query. I use phpmyadmin for testing and it executes perfectly. However when I try to use it in my server application I always get null on rank

SET @_c = 0;
SELECT kills,deaths,revives,damage,levels,rank,skill
FROM (
    SELECT
        *,
        (@_c \:= @_c + 1) AS `rank`,
        ((kills + damage + levels)/(deaths+revives+1)) AS skill
    FROM leaderboard
    ORDER BY skill DESC) AS newtable
WHERE soldier_id = 1(example)

I am looking either for another implementation or idea how to fix it.

Edit: I put (example) because soldier_id can be 1,2, or 3000. It not constant that what i am trying to say. And i have to use : because i use hibernate in java which requires it.


texts: How to get full query text from performance_schema.threads?

texts:
According to documentation:
Access to the threads table does not require a mutex
and has minimal impact on server  performance. The other
sources have negative performance consequences because they require a mutex.

So yes, I want to use threads table to examine running queries. The trouble is, the PROCESSLIST_INFO column does not contain the full query. It just contains a truncated version of the query. There is a flag viz performance_schema_max_sql_text_length which controls how many bytes are allocated for the SQL query. But it seems to affect the events_statements_history_long table. There is no change in the thread table output. Am I missing something?


texts: can I exclude or delete .pem files in backup data myqdl?

texts:
I am making a backup of the mysql data folder. In this folder are the databases. The method I am using is described in this post which uses ROBOCOPY

During installation, mysql (tested in 5.7.30) generates the following .pem files by default (at least in the wampserver version. I'm not sure if in the other versions):

private_key.pem
public_key.pem
server-cert.pem
server-key.pem
ca-key.pem
client-cert.pem
client-key.pem

the question is: What happens if I don't backup these files or delete them?

tests performed:
I have stopped the mysql service and I have removed all the .pem files and when I start mysql again only these .pem files appear

private_key.pem
public_key.pem

What about the rest?

thanks


texts: MySQL replication slave of slave not receiving updates

texts:
I have 4 servers: A, B, C and D.

A and B are in a master-master setup.

C is a slave to B and D is a slave to C.

Changes on A or B get to all nodes other than D. I need them to get to D

Changes on C get to D but obviously they don't go to A or B which is fine.

After some reading I found that I needed the log-slave-updates option set on C, this way C would log the changes replicated to it and then D could read those and make the same updates.

I have set the option to on and D is waiting for it's master to send events but changes from A or B are still not getting to D.


texts: Is adding index on bit column significantly slow down inserts?

texts:
I have a table with about 1 million to 5 million records. Small part of that records have one of bit column set to 'TRUE'. Need to fast find that records. I think that index can speed up searching on this column, but i afraid about INSERTs. Hence my question.

Database is working like sort of data warehouse, so there is many SELECTs and small (up to 10-20 per day) but quite large INSERTs (up 200 thousand records at once). I'm afraid about longer time of that imports to database.


texts:
Using this contrived example structure:

Base table data

{
  "uid": "b12345",
  "nested": ["n12345", "n34567"]
}

Nested table data

[
  { "uid": "n12345", "message": "Hello world" },
  { "uid": "n34567", "message": "Hello world" }
]

I'm trying to join the tables on the nested array such that each uid is replaced with its corresponding record:

{
  "uid": "b12345",
  "nested": [
    { "uid": "n12345", "message": "Hello world" },
    { "uid": "n34567", "message": "Hello world" }
  ]
}

The solution in this post looks very close to what I need, but it seems like the main difference/blocker is that the nested array here is initially flat.

Here is the SQL I've been using to test it, including a query closely modeled on the above post. I'll appreciate any help!


texts: How do I stop, tune and restart a MariaDB Galera cluster?

texts:
So I have 4 nodes running on some fast but smallist (ram wise) vps machines. This is very cool but they are using 100% of the memory available. I need to turn off performance schema and possibly lower some other settings to get this running at or near 7-800 mbs.

Before I try to do service mysqld stop, I thought I better ask.


texts: CASE WHEN DATE not providing correct results

texts:
When using the below query the "Result_field" returns "NO" even if the date is greater than 12/31/2020.  How can I get it to show "YES" when the date is greater than 12/31/2020 and "NO" When Less Than?  Been trying to research this for a while and now need help.  This may be a simple query, but I'm kinda new at this.  Thanks in advance.

SELECT Date_field,
CASE
    WHEN Date_field < '2020-12-31' THEN 'NO' ELSE 'YES'
    end as Result_field
FROM Yearly_Report;

This is the result I get:


texts: SQL Server 2008R2 Migration to new Platform - What Steps Required?

texts:
We are migrating our SQL Server 2008R2 instance from one Windows Server platform to another. SQL Server will remain as 2008R2. The new plaform will have more disk space, memory and CPU.

I am doing up a tasklist of what is required to migrate SQL Server. Below is an outline of what I think are the steps involved.
Are these "best practice" or am I missing some requirements?

Thanks!

1) Backup and Restore Databases - Is this the best option? Use Ola Hallengren Script

2) Migrate Logins - use Microsoft KB? kb/918992

3) Migrate Credentials/Certificates - what is required for this?

4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)

5) Migrate SSIS Packages - (How to do this?)

6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)

7) Recreate assemblies

8) Recreate Linked Servers

NOTE: System Databases - do these need to be migrated?


texts: What happens when CREATE INDEX gets interrupted (manually or accidentally)?

texts:
It takes hours or even days to create an index for a table of millions of rows.

What happens to the database / data when CREATE INDEX gets interrupted? Such as when I manually used Ctrl-C or when there's an accident like a power-failure.

Does it do harm to the database / data?

If so, how do we recover from such an interruption?


texts: How can I write queries as fast as possible in MySQL?

texts:
I'm looking for two things:

Awesome manual about writing fast MySQL queries.
Tool which will decompose my query into discrete steps, and give estimations of each step.

I've seen the next tool, but I don't know how to use it.


texts:
I am having trouble using the LOAD DATA LOCAL INFILE statement in Datagrip. Suppose I have source_data.csv such as:

rating,statement
-2,"I'm hungry!"
5,"I'm satisfied."
1,"Ok, but could be better."

After running the following code block in DataGrip:

# loading_sample_table.sql

USE sample_db;

DROP TABLE IF EXISTS sample_table;
CREATE TABLE sample_table (rating int, statement varchar(50), id serial);

LOAD DATA
    LOCAL INFILE 'absolute/path/to/my/source_data.csv'
    INTO TABLE sample_table
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY  '\''
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (rating, statement);

I receive the following error:

[42000][1148] The used command is not allowed with this MySQL version

Running SHOW VARIABLES LIKE "%version%"; in either MySQL shell or DataGrip produces

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.18                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.18                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | macos10.14                    |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+

Then, if I try the same same command above but just drop LOCAL from the code block, I receive the following error:

[HY000][13] Can't get stat of 'absolute/path/to/my/source_data.csv' (OS errno 13 - Permission denied)

Also, I checked SHOW GLOBAL VARIABLES LIKE 'local_infile'; and can see:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

However, if from my terminal I run:

mysql --local-infile=1 -uroot -p < 'absolute/path/to/my/loading_sample_table.sql'

After entering my root password, the file runs successfully. Ideally, I would like to do everything in DataGrip. Unsure on how to debug this issue.


texts: Patching strategy when Windows and SQL Server patches not applied for over 3 years

texts:
The company I'm at hasn't patched their production servers for a while, neither SQL 2012 or Windows 2012. When I say "for a while" I mean, well, actually "never" - no patches or updates since the installation in late 2012. They are using synchronized AGs with auto-failover for redundancy so we can upgrade one node at a time.

The plan is to disable auto-failover, update the second node, failover the databases then update the first node.

Question: How do you recommend approaching this? Apply 3 years of DB and OS patches all at once to the second node, then failover to that node?
How much out of sync can the nodes be and still expect a trouble-free failover of AG databases? Is it better to do the Windows update first, or the SQL CU?

The goal is to minimize downtime and the number of failover/failbacks.


texts: Failing to configure SymmetricDS for SQL Server 2016

texts:
I am failing to configure and access SymmetricDS 3.12 for SQL Server 2016 and access it over the web. I have configured all required values engines properties and run sym.bat. I have configured SQL Server engines properties port to 1433, but still failing to access the web interface. I am getting the following errors on the web:
URI:    /
STATUS: 403
MESSAGE:    Forbidden
SERVLET:    default
I wanted to attach my properties and log file here but i can seem to find how.


texts:
I am working on limiting input in certain fields in my Postgres database, and I have a working solution using ENUM like so:

CREATE TYPE label AS ENUM ('foo','bar');

However, I was browsing around and looking at alternative ways of doing this using:

constraints (eg. CHECK (type IN ('foo','bar')) )
triggers (slow, from what I've read)
foreign keys

What are the advantages/disadvantages of using either of these methods, would any of these be more advisable? I am not necessarily asking which one is the 'best' method, only what kind of issues may arise from using these, ie. I know that using triggers might be quite slow compared to the other methods, but perhaps could deal with some more complex input conditions.


texts: Conversion fails when converting date average to date in sql

texts:
I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string.

sql query executed as:

select
CONVERT
(
    DATE,
    LEFT(
          convert(
                  varchar(10),
                  AVG(
                        convert(
                                 int,
                                 convert(
                                           varchar(10),
                                           OnMarkDate,
                                           112
                                         )
                                 )
                       )
                  ),8
          )
)
from @tbl_XMLResult

Column values are as:

   '2001-10-11',
   '2001-10-03',
   '2001-09-24',
   '2000-05-31',
   '2001-10-15',
   '2008-01-01'

with datatype date.


texts: Create New Database user to login to SQL Server Management Studio

texts:
Please see the T-SQL below:

create database db;
go

use [db]

create login [standard] with password ='dkdkdkqajHGgbh56'
CREATE USER [standard] FROM LOGIN [standard];

exec sp_addrolemember 'db_owner', 'standard'

After running this I try to login to SQL Server Management Studio as standard and I am prompted with this error:

  login failed for user: standard

What am I doing wrong?

PS: I cannot even login as sa. I have enabled 'SQL Server Windows Authentication mode'. I am a developer (not a DBA) trying to setup a local instance of SQL Server.


texts: SQL logs showing login that I can't find

texts:
Within my SQL server logs, I am showing a successful login to a database from an account (SERVER\loginname) that I cannot find in either Security > Logins OR Database > Security > Users. This login is being used by a program to execute a task (which it does successfully), so it's not a security compromise, but I cannot for the life of me figure out where it is and I need to find it to make an adjustment.

A couple more notes: I do remember adding this specific login a while back and there was something special about how I had to do it because it is not a domain account. Instead, it uses pass through authentication (same local account name on the app and SQL server with the same password). I cannot use it to login via SSMS. It is not a part of a group membership. It is not showing when I do a SELECT from the master syslogins table.

Does anyone have any ideas for my brain-fart?


texts:
In Java we use Joda-Time to get the seconds of a day as an int value (despite the date):

date: 10-10-2014 00:00:30 -> second 30 of day
date: 11-10-2014 00:01:30 -> second 90 of day
date: 12-10-2014 00:02:00 -> second 120 of day

Is there a way to do the same in PostgresSQL?


texts: Nested table as condition prevents using index


texts:
The mysql version that I use is :

'aurora_version','1.17.98'
'innodb_version','1.2.10'
'protocol_version','10'
'slave_type_conversions',''
'version','5.6.10-log'
'version_comment','MySQL Community Server (GPL)'
'version_compile_machine','x86_64'
'version_compile_os','Linux'

This is the db table structure (relevant parts) that I m working with (table name access_logs) :

id| EventTime | EventType | EventName ... | event_log_domain_id

the table has approximately 11mil records.

These are the common ways that the data is queried :

1) Using in statement with event_log_domain_id and performing count

SELECT COUNT(*) FROM `access_logs ` WHERE `access_logs`.`event_log_domain_id ` IN (8, 59, 920, 1054, 2227) AND `access_logs `.`EventType ` = 1

2) Not Using in statement with event_log_domain_id and performing count

SELECT  COUNT(*)
    FROM  access_logs USE INDEX(event_log_domain_id)
    WHERE  `access_logs`.`event_log_domain_id ` = 1304
      AND  `access_logs`.`EventType ` = 1

3) Not performing the count

SELECT  `access_logs `.* FROM access_logs WHERE `access_logs `.`event_log_domain_id ` = 1304 AND `access_logs `.`EventType ` IN (1, 5)  ORDER BY id desc LIMIT 50

I have indexes on both event_log_domain_id and EventType.

Count performance is by far the worst. It seems like count queries don't even use the indexing. Even when I provide the index name, for ex :

SELECT  COUNT(*)
    FROM   `access_logs` USE INDEX(event_log_domain_id)
    WHERE  `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227 )
      AND  `access_logs`.`EventType` = 1

Does not help improve the performance much, may even make it worse.

On the other hand the select query(#3) does perform better when providing USE IDEX.

I was also thinking about adding composite index on both event_log_domain_id and EventType. Rebuilding indexes takes somewhat long so I don't want to just randomly try stuff out without understanding the performance implications first.

Is there a special index I can create for the count operations? At this point for the worst case scenario the query #1 and #2 runtime is about 240 sec or 120 each.


texts: In MySQL, how exactly does data flows from query to disk?

texts:
I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then how about bin_log_cache is used.

I have googled this but didn't find much on this topic. I hope to have some explanantion here among the between people of this community.


texts: Why is MariaDB 10 refusing to use my index on a date field?

texts:
I have the following table definition:

CREATE TABLE `bets` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_mp` bigint(20) NOT NULL DEFAULT '0',
  `coin` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `game_id` tinyint(4) NOT NULL DEFAULT '1',
  `group_turn_id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `direction` tinyint(1) NOT NULL DEFAULT '0',
  `bet_amount` double NOT NULL,
  `multiplier` double NOT NULL,
  `target` double NOT NULL DEFAULT '0',
  `result` double NOT NULL,
  `profit` double NOT NULL,
  `win` tinyint(1) NOT NULL,
  `seed` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `bets_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=1189677 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You'll note that there is a primary key as well as a secondary index on the 'time' field. Mysteriously though, the following query refuses to use the bets_time index:

EXPLAIN SELECT COUNT(1) AS num_bets, SUM(bet_amount) AS total_wager FROM bets WHERE `time` > (NOW() - INTERVAL 24 HOUR)

yields the following:

+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | bets  | ALL  | bets_time     | NULL | NULL    | NULL | 1132313 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+

As you can see it sees bet_time as a possible index, but does not use it. Any ideas as to why this might be happening? I guess I could use FORCE INDEX on the query, but I'd rather avoid hardcoding such query logic into the SQL and prefer an 'proper' solution.

Thanks a lot for any ideas.


texts: MongoDb replica set: How do I verify data replication on other servers?

texts:
I have a 3 member replica set running. It's geo-located on 3 separate VPSs. On primary I can do rs.status() and see it's working. I can't execute any mongo shell commands on the secondaries. How do I verify data replication on other servers? I see nothing indicating data being copied in the logs.

Thanks a lot for any help :-)

Edit 1:

Tried db.printSlaveReplicationInfo(). Logged in as user with full admin role...

Edit 2:


texts:
I'm having problem ( I have problems with cursor since ever and I really need to understand how the hell it works ).

I would like to run this command in all databases:

SELECT db_name() as  Dtabase_name,
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

I can easily do this with a lot of ways, but I want to pratice cursors.

I made this, but it gives me sintax error:

declare @dbname varchar(max)

declare dbcursor cursor for
select [name] from sys.databases

open dbcursor
fetch next from dbcursor into dbname

while @@fetch_status=0

use @dbname
SELECT db_name() as  Dtabase_name,
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

close dbcursor
deallocate dbcursor

Where should I put the database variable for the loop in a cursor?

Thanks

EDIT1:

With this, it runs forever. god dammit:

declare @dbname varchar(max)
declare @string varchar(max)

declare dbcursor cursor for
select [name] from sys.databases

open dbcursor
fetch next from dbcursor into @dbname

while @@fetch_status=0

select @string =
'
use '+@dbname+'
SELECT db_name() as  Dtabase_name,
dbtables.[name] as ''Table'',
dbindexes.[name] as ''Index'',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'

exec sp_executesql @string

close dbcursor
deallocate dbcursor


texts: Connect to SQL Server 2012 from an Windows XP client

texts:
We have an SQL Server 2012 Standard database, to connect to it we use SQL Server 2008 Management Studio (since 2012 SSMS is not supported on Windows XP).
The problem is that SQL Server 2008 gives all the time an exception (Index outside the bounds of the array) and it's not possible to edit table in visual editor or open table. Client can work now only with database objects through queries.
So the question is - is there any solution on Windows XP to work properly with 2012 database ?


texts:
After windows patching (windows 2008 R2 , SQL Server 2012 Sp2). I could not start SQL Server Service . I get error 1067 and this from the error log* The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x13, state: 2. Administrator should deregister this SPN manually to avoid client authentication errors."
Yes it is a production system and any help is welcome. I tried using domain account that has sysadmin and local admin privilege.

Thanks


texts: Debugging why postgres is snowballing with system cpu usage

texts:
I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database.

This is a dual 6 core machine with 128 GB of ram.

max_connections = 609
shared_buffers = 23923MB
work_mem = 78MB
maintenance_work_mem = 4784MB
wal_buffers = 16MB
checkpoint_segments = 10
effective_cache_size = 71769MB

I have hit a wall in debugging this issue trying to correlate this with any cron tasks or heavy work that utilizes the db.

When I am looking at htop while this is occurring I see a lot of process like:

CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) authentication
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) authentication
CPU 99-100% postgres: user database ip(pid) idle
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) startup
CPU 99-100% postgres: user database ip(pid) SELECT
CPU 99-100% postgres: user database ip(pid) startup
...

pg_stat_activity is not swamped with more queries than normal or queries that take a particularly long time to run when this issue is not occurring. All queries during an occurrence are taking 10's of seconds to process each. Even queries that just check to see if the table exists or not.

I would love it if I could be pointed in a good direction to further debug this issue.


texts: Psql, cte twice for same thing return bad value

texts:
What I need.
Same values for category tree as in first case, first case is for name (cte), second is for id (cte2).

What I tried.

with recursive

cte as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte.category_name, ' > ', s_category.category_name)
from s_category,cte
where cte.category_id = s_category.category_parent
),

cte2 as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte2.category_id, ' : ', s_category.category_id)
from s_category,cte2
where cte2.category_id = s_category.category_parent
)

select
cte.category_name as "category",
cte2.category_id as "hierarchy",

...

cte return this (correct),

Bydlení > Barvy a laky > Vnitřní barvy

cte2 return this (not correct, did it by same way, but it return only last id),

6669

instead

6111 : 6333 : 6669

Where is the catch?

Thanks.

Update
Here is Akina's working solution. Thank you.

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
concat('Úvod > ',cte.category_name) as "category",
s_category.category_id as "categoryID",
cte.category_ids as "hierarchy",

...

from

...

left join cte on s_product.category_id = cte.category_id

...

group by

...

cte.category_name,
cte.category_ids


texts: mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

texts:
I do not understand why MySQL is not using the index for the below query.

I have a table named users and its create table is

this table contains data.

Now I am running SQL select statements and it's checking its query execution plan.

for the statement

desc select *
     from   users
     where  users.created_at between '2019-05-04' and '2019-05-06';

output is

but which I am using DateDiff function query is not using indexes.

desc select *
     from   users
     where  DATEDIFF(users.created_at,'2019-05-05 23:11:56.547') =  0;

and output for this is

according to the MySQL documentation page "A function is nondeterministic if, given fixed values for its arguments, it can return different results for different invocations".

According to doc, DateDiff is a deterministic function and I am not understanding why MySQL is not taking indexes for the query.


texts: Sharded Azure SQL Database Split-Merge Tool

texts:
Background

I am developing a multi-tenant application that supports each Tenant having its own database and/or sharing a database with other Tenants.

The design is similar to this, though I doubt it follows the exact design Microsoft would prefer for use with Azure Elastic Database Tools (image source)

My "Host" database (Catalog in the diagram) knows each Tenants' TenantId and the Connection String associated with it. When a user navigates to a particular Tenant's login page, the application finds the associated Connection String and changes the context of the application to this Connection String.

Every record across all of the databases and tables references the TenantId that owns it. This is what allows for database-per-tenant and/or shared databases.

Each table within each database uses an auto-incrementing Integer Primary Key. I build the Foreign Key references between tables in the database using these auto-incrementing Primary Keys.

Question

My understanding is that it is easy (given my design) to split a particular Tenant out into its own database and then update the Connection String in the Catalog. However, I am just now realizing that merging a database with auto-incrementing Primary Keys will be a huge mess unless I re-calculate the Primary Keys and update all of the Foreign Key references before doing a merge of some sort.

This Stack Overflow question suggests that for the Azure Elastic Database Merge Tools "The values of autoincrement columns are not copied over, they will be regenerated at the destination. So new ids will be assigned to these rows." Unfortunately I can't find any additional information to back up this statement.

My question is this: Is the Azure Split-Merge tool actually smart enough to regenerate auto-incrementing Primary Keys and, if so, will it also update the Foreign Key references accordingly?

edit: I should mention that my application was not specifically designed to be dependent on Azure Elastic Database tools. If I can use them to merge/split, great. If not, that's ok too.


texts: SSMS: possible to script 'file per object' into categorised folders like in object explorer?

texts:
e.g. if you run the 'generate scripts' function (by right clicking on a db in object explorer) to script out an entire database, it creates all the files in one folder and annoyingly names them as [schema.objectname].[objecttype].sql

(at least if it were type.name it might be a little more readable)

Fine but I would rather mirror the object explorer convention of having Tables, Views, etc on a per folder basis containing only those object types and the file having exactly the same name as the object

Is there a simple way to achieve this?


texts:
I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer.

My first approach is to have a fact table with all products by client each day.

Is this the best approach?
This fact table will grow at the same rate that the products are added to costumers list.

btw i'm using Pentaho.


texts: Is it possible to audit query results in SQL Server?

texts:
I want to find out if it is possible to audit the results of any query that gets executed on an instance of SQL Server. We currently have C2 auditing enabled, but this only showed what query was executed, not what was returned.

Since this project is health-information related, we want to ensure that we are auditing to the most granularity we can obtain to meet HIPPA requirements. Previously this was not required, but the nature of our project is changing.

The goal is: if a user logs into our system and executes a query, we want to know every patient that was returned from that query.

Is there either a built-in feature (triggers?) or a third-party application that we can use to achieve this? We looked into ApexSQL, but their customer service relayed that their software does not audit at that granularity.

  Could this audit be restricted to the Patients table only?

Unfortunately no, due to the fact that if you were to query a different table, there is patient-related information; so if you happen to know that patient's foreign key, you could "get around" the audit by looking up individual clinical records.

We're not concerned about space or speed at this point in time.