texts: How to query the names of encrypted stored procedures?

texts:
What query will identify the names of stored procedures which are encrypted?  Here's what I got so far -

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
-- need another condition here to identify just the encrypted SPROC's
order by ROUTINE_NAME asc

Thanks in advance.


texts: unable to start psql : no pg_hba.conf entry for host "::1" , user "postgres", database "postgres", SSL off

texts:
I am unable to connect using psql to PostgreSQL 9.6 on Windows 10. I saw almost all the StackExchange questions regarding this error. While installing Postgres it asked for superuser password and I set it. Added host all all 0.0.0.0/0 trust and  host all all ::0/0 trust to pg_hba.conf but still no luck. This is the pg_hba.conf contents on my machine:

host    all             all             localhost               trust

host    all             all             127.0.0.1/32            trust

IPv6 local connections:

host    all             all             ::1/128                 trust

host    all             all             ::0/0                   trust

host    all             all             0.0.0.0/0               trust

Any help would be appreciated.


texts: MySQL Replication query - Primary key need

texts:
I have few doubts regarding MySQL replication (Master-Slave).
Is it mandatory for a table to have primary query for replication to function properly?

Referring this Percona link for the above question as it mentions that If there is no primary key or unique key defined then it’s even worse because INSERT may be re-executed and you will get multiple rows with the same data – which again means you’ve got inconsistent data with the master

But with InnoDB as the storage engine, if a table does not have primary or a unique query defined, the engine itself creates hidden clustered index on a synthetic column containing row ID values as per Jeremy Cole's blog

So, even in the case primary or unique not present, the replication should not have any impact it in itself creates a clustered index which should ensure the replication is smooth, correct? I'm not sure on this part.

Would be great if someone can throw some light on the need of Primary key in Master-Slave Replication setup.


texts: Add standby database on different OS version?

texts:
Primary database is installed on RHEL 6.1. Standby database software is installed on RHEL 7.0. I am trying to add new standby database by using Grid Control. Database versions are same 11.0.2.0.3.

I was able to add standby database on RHEL 6.1 using same method.

I used the following link to install ORACLE on RHEL 7.0. Does it have anything to do with oracle group gid is different than RHEL 6.1? On RHEL 7.0, I used oinstall(54321) dba(54322) oper(54323) for gids. On RHEL 6.1, I used oinstall(501) dba(502) oper(503). If it doesnt matter, what should i do?


texts:
I want to update my db records, which are related one to many. Please consider the following example :

A person can have many contacts

Stating above implies that contact table has a person_id column. And also adding to the context the contact table has current_year_id column. Table person has phone and fax columns.

Now I added phone and fax columns to the contact table and I want to copy the values of fax and column to the contact table from person table. However I want to set these values to the contact associated with person which has current_year_id from current_year table.

Here is what I tried so far, and am surprised why is not working :

UPDATE CONTACT SET PHONE = p.PHONE, FAX = p.FAX
      FROM PERSON p
      INNER JOIN CONTACT on CONTACT.PERSON_ID = p.PERSON_ID
      WHERE CONTACT.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
      )

However select brings back the records :

SELECT * FROM PERSON p
INNER JOIN CONTACT on CONTACT.PERSON_ID = p.PERSON_ID
WHERE CONTACT.CURRENT_YEAR_ID = (
        SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
      );

Tried another variant :

UPDATE CONTACT
        SET CONTACT.PHONE = p.PHONE,
            CONTACT.FAX = p.FAX
        FROM PERSON as p
        WHERE p.PERSON_ID = CONTACT.PERSON_ID
        AND CONTACT.CURRENT_YEAR_ID = (
            SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
          );

Another one :

UPDATE CONTACT
        (SELECT PERSON.PHONE as PHONE, PERSON.FAX as FAX
         FROM PERSON
        ) p
        SET CONTACT.PHONE = p.PHONE,
        CONTACT.FAX = p.FAX
        WHERE CONTACT.CURRENT_YEAR_ID = (
                SELECT CURRENT_YEAR_ID FROM CURRENT_YEAR WHERE DEFAULT_YEAR = 1
              );

And get very informative error from sql developer :

SQL Error: ORA-00933: SQL command not properly ended

What can I do to achieve this?


texts:
I'm trying to export data using BCP.
I want my exported file to be encoded ISO-8859-1.
Which code page would I have to use with the -C option ?
Is there anywhere a list with code pages for each encoded file type ?


texts: Constraint creation on a large table is too slow

texts:
There's a table with 723M rows, large amount of data and not partitioned. We're adding a constraint to the table as follows:

ALTER TABLE ONLY my_table ADD CONSTRAINT partial_constraint EXCLUDE USING gist (((gid)::text) WITH =, int4range(start_sequence_number, (end_sequence_number + 1)) WITH &&) WHERE (is_valid);

The above constraint has been running for the past 5 days and has not finished, in the AWS insights I see that the CPU usage for the query is at 96% and IO at 4%. There's no other process running that touches my_table, I see there's an exclusive lock on the table and yet it's taking too long. When we did this in pre-production, it took couple of days.

Is there anything we can do to improve the performance off the constraint addition/see the progress?


texts: "The client and server cannot communicate, because they do not possess a common algorithm" error

texts:
The full error message is:

System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.) ---> System.ComponentModel.Win32Exception (0x80004005): The client and server cannot communicate, because they do not possess a common algorithm

Context information:

.Net console application written in C# running on windows 2016 server standard for testing the connectivity to a SQL Server 2008R2 database installed on Windows 2008r2.

The connection string pattern is: $"Server='{server}'; Database='{db}'; UID='<sqllogin>'; PWD='<pwd>'"
The app simply executes:
private static void TestConnection(string connectionString)
{
  var cn = new SqlConnection(connectionString);
  var msg = connectionString.Substring(0, connectionString.IndexOf("PWD"));
  try
  {
    cn.Open();

    Console.WriteLine("The connection to {0} SUCCEEDED!", msg);
    cn.Close();
  }
  catch (Exception ex)
  {
    Console.WriteLine("The connection to {0} FAILED!", msg);
    Console.WriteLine(ex);

  }
}

I enabled TLS 1.2 on the SQL server box with these settings and I rebooted it.
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

I still get the error. I didn't want to touch the client registry settings.
Any other ideas?
Now that I think of it, on my dev windows 2016 server I never had any connectivity issues to SQL Server 2018R2 in SSMS or in Toad for SQL Server (which is implemented in C#).
Thanks


texts:
How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are trying to build azure database indexes and it has  a limitation of max 2 GB.

My database is in full recovery model.

Table Size:

log size:

from the pictures you can see log generation was very less with online index rebuild operation.Can some one correct me if i am missing anything


texts: SSAS (Analysis Services) 2014 - Cumulative Update 1 for SP1

texts:
I just recently applied CU1 for SQL Server 2014 SP1 (http://support.microsoft.com/en-us/kb/3067839) to my development workstation and one PROD server. Analysis Services continues to report itself as build 12.0.4100.1 (which is SP1) and not build 12.0.4416.0. Given that there are quite a few SSAS fixes in this update, this doesn't seem to be right.

When I look at the file information for CU 8 (http://support.microsoft.com/en-us/kb/3067836), I see a whole host of SSAS file updates that I do not see in CU 1 for Service Pack 1.  From my outside-of-Microsoft perspective, it seems that the SSAS updates were not included in this update as advertised.

Can anyone corroborate?

Thanks!


texts: Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

texts:
I have the following tables:

MySQL table: jti_member_interact

CREATE TABLE `jti_member_interact` (
  `INT_MEMBER_ID` int(11) NOT NULL,
  `INT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `INT_SOURCE` varchar(1) NOT NULL,
  `INT_DATE` datetime NOT NULL,
  `INT_TYPE` varchar(255) NOT NULL,
  `COPY_TO_STG` varchar(12) DEFAULT 'NO',
  `NEW_STG_SEQ` int(11) DEFAULT NULL,
  `COPY_TO_STG_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=43670 DEFAULT CHARSET=utf8$$

Oracle table: MEMBER_INTERACT_MYSQL_STG

  CREATE TABLE "JTI_HTP"."MEMBER_INTERACT_MYSQL_STG"
   (    "INT_MEMBER_ID" NUMBER(10,0) NOT NULL ENABLE,
    "INT_ID" NUMBER(10,0),
    "INT_SOURCE" NVARCHAR2(1) NOT NULL ENABLE,
    "INT_DATE" DATE,
    "INT_TYPE" NVARCHAR2(30) NOT NULL ENABLE,
    "INSERTING_DATE" DATE,
    "MYSQL_ID" NUMBER(12,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

Basically, I needed to copy jti_member_interact into member_interact_mysql_stg with adding new columns as follow:

MEMBER_INTERACT_MYSQL_STG.STG_INT_ID = NEW_SEQ
MEMBER_INTERACT_MYSQL_STG.MYSQL_INT_ID = MEMBER_INTERACT.INT_ID
MEMBER_INTERACT.COPY_TO_STG = 'YES', once the copy operation is completed.
MEMBER_INTERACT.NEW_STG_SEQ = MEMBER_INTERACT_MYSQL_STG.STG_INT_ID

I have created the following procedure:

CREATE OR REPLACE PROCEDURE COPY_MYSQL_WEB_INT_TO_STG(
P_BATCH_NO IN NUMBER)
IS
BEGIN
    INSERT INTO MEMBER_INTERACT_MYSQL_STG
    SELECT "INT_MEMBER_ID",
      STG_SEQ.NEXTVAL,
      "INT_SOURCE",
      "INT_DATE",
      "INT_TYPE",
      CURRENT_DATE,
      "INT_ID"
    FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" des
    WHERE "NEW_STG_SEQ" IS NULL;
    COMMIT;
    FOR REC IN (SELECT  STG_INT_ID, INSERTING_DATE, MYSQL_INT_ID FROM MEMBER_INTERACT_MYSQL_STG)
    LOOP
        UPDATE "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" SRC
        SET "COPY_TO_STG"     = 'YES',
            "NEW_STG_SEQ"     = REC.STG_INT_ID,
            "COPY_TO_STG_DATE" = REC.INSERTING_DATE
        WHERE SRC.INT_ID = REC.MYSQL_INT_ID;
    END LOOP;
--    COMMIT;
END;

The procedure compiles successfully. However, when I run the procedure, I get the following error:

[MySQL][ODBC 5.1 Driver][mysqld-5.5.31-30.3-log]Unknown column 'jtipartn_mydb.jti_member_interact.INT_ID' in 'where clause' {42S22,NativeErr = 1054}
ORA-02063: preceding 2 lines from JTIPARTN_WEBSITE

Apparently, the error I get has to do with MySQL table jti_member_interact specially, the column INT_ID. I double checked the name, it is correct! I tried to change it to something else but I got the same error.

I search this error and I found it quite common in some MySQL versions (version 5). I could not find a way to fix my problem (I do not use any trigger here or USING statement). In addition, my knowledge in MySQL is very limited. More interesting is that I ran the same procedure on another MySQL table that has identical structure and column names and it worked perfectly!

When I query the column in normal query such as:

SELECT INT_ID
FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE";

I get NO errors on both DB's (oracle & MySQL)! Only UPDATE statement cause errors.
In addition, I tried instead of SRC.INT_ID in where clause to use "INT_ID". I got the same error.
Using without double quote has the same the problem.
I have created another identical MySQL table and tried to update it and I got the same error.
The only difference between the table that I can update successfully and the the table I cannot, is that they belong to different MySQL databases (in different locations). But I do not know how to solve this problem.


texts:
I have a query witch is selecting some data between two dates:

SELECT * FROM rk.p_reports
WHERE project_id = 1 AND engine_id = 1 AND type_id = 1
  AND created_at::date BETWEEN now()::date - 14 AND now()::date

I have tried creating various indexes to speedup the queries but without any luck

First index: project_id, engine_id, type_id, created_at ASC, created_at DESC

Second index: project_id, engine_id, type_id, created_at ASC AND project_id, engine_id, type_id, created_at DESC

Third index: project_id, engine_id, type_id, created_at ASC

And other combinations, and non of the index was picked without turning enable_seqscan = off

At the moment it will take around 3 sec to return my rows, this partition has around 1.5 mil rows. I am using Postgresql 9.4 on the DB server has 8 Gb ram and 6 cores.

Is there any way I can reduce this to under a sec?

Seq Scan on p_reports_1  (cost=0.00..57649.04 rows=4322 width=40) (actual time=614.221..696.583 rows=92607 loops=1)
Filter: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND ((created_at)::date <= (now())::date) AND ((created_at)::date >= ((now())::date - 14)))
Rows Removed by Filter: 1063344
Planning time: 0.200 ms
Execution time: 703.493 ms

Update, query modified as @ypercube sugested:

Bitmap Heap Scan on p_reports_1  (cost=2952.28..17478.45 rows=89005 width=40) (actual time=17.485..34.569 rows=87712 loops=1)
Recheck Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= ((now())::date - 14)) AND (created_at <= (now())::date))
Heap Blocks: exact=1180
 ->  Bitmap Index Scan on p_reports_1_asc_created_at  (cost=0.00..2930.03 rows=89005 width=0) (actual time=17.236..17.236 rows=87712 loops=1)
    Index Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= ((now())::date - 14)) AND (created_at <= (now())::date))
Planning time: 0.319 ms
Execution time: 41.569 ms


texts:
I have got a question relating to SQL server log files in a production environment,  however what I am trying to do is first run some testing on a smaller Test Database (E10Test) before I move on to the production database. However I am having some issues on shrinking the Log file.

Here are the Database properties of E10Test

Logical Name | File Type | Filegroup | Initial Size (MB) | Autogrowth / Maxsize

E10Test | Rows Data | PRIMARY | 7337 | By 100 MB, Limited to 10248 MB

E10Test_log | Log | Not Applicable | 256 | By 20 MB, Limited to 2048 MB

When running DBCC sqlperf ('logspace')
Here is the output

E10Test | 255.875 | 0.1463651 | 0

Log_reuse_wait = 0
Log_reuse_wait_Desc = NOTHING

Looking at the information above the log is not even 1% used. In theory I should be able to shrink the log file to a smaller size right?

Db is on Full recovery mode and I have done a full backup of Db and Log file backup.

I have tried the following without much luck:

Changing the size from the Db properties (Outcome is the same as before)
Using both script and GUI to shrink the Log file (Outcome is the same as before)
Set Db to simple recovery and repeat the above (Outcome is the same as before)

No matter what I do the log is still on 256Mb and it does not go any lower.
Is this something caused by Microsoft or is there something I have missed out on?


texts: I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql or c#.PFB screen shot for more details.


texts: Attempting to compress an existing table results in no change to the table size

texts:
There is an existing table in SQL 2012 Enterprise DB, which is ~120MB in size with ~2000 records. Its largest field is nvarchar(max) containing spongy XML (ranging from 20 to 300 kB) which on its own compresses roughly by 50-75% in 7Zip or RAR.

I tried the following commands which return instantaneously, even though simply querying * from the table takes quite a few seconds:

alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=ROW)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=NONE)

But the size is not changing after any of them, even though the properties of the table correctly show the compression type.

Is the server actually applying and removing compression to the rows/pages? It seemingly does not have time to accomplish that in the split second that it takes each command to execute.


texts:
In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on the view).

Here's more detailed info. Our views look like,

A
--
id, name
1, afoo
2, abar

B
--
id, name
3, bfoo
4, bbar

So our view C looks like

C
--
1, afoo
2, abar
3, bfoo
4, bbar

We did a CREATE TRIGGER delete_trigger INSTEAD OF DELETE C FOR EACH ROW DO delete_trigger(), which delete the rows by id which basically its like

-- our function trigger():
$$
delete from A where id=old.id;
GET DIAGNOSTICS deletedA = ROW_COUNT;

delete from B where id=old.id;
GET DIAGNOSTICS deletedB = ROW_COUNT;

raise notice 'deleted % records from A, % records from B', deletedA , deletedB;

$$

If we issue a command like delete * from C;, our ideal goal would be to have a single message that said

deleted 2 records from A, 2 records from B

But instead we have - because we don't know better -,

deleted 1 records from A, 0 records from B
deleted 1 records from A, 0 records from B
deleted 0 records from A, 1 records from B
deleted 0 records from A, 0 records from B

Is it possible to get a single sum of deleted records?

We're also worried about the performance of this.

Any and all help greatly appreciated!


texts:
I have the following xml:

<root>
 <123 type="line">
  <aa type="item">
   <value>1</value>
  </aa>
 </123>
 <234 type="line">
  <bb type="item">
   <value>2</value>
  </bb>
 </234>
</root>

How can I from bulk loading the xml into a database table (field xmldata)
query the xml so I get all values under type="line" and then all values under type="item" so I can then get to the value attribute. I can't hard code the attribute (eg 123) because it changes all the time, but the type value will remain constant.


texts: SQL Server 2008 Convert String to Datetime question

texts:
I have a string column in a table that display data as "CXL P/D 08/15/13"

I'm trying to convert this column to datetime, but I can't figure out how to extract only date and change the data type.

Cast(RIGHT(RTRIM(Trade_Date) ,8)as datetime) I'm trying this statement but it doesn't work

Conversion failed when converting date and/or time from character string.

Thank you


texts: SSIS Error: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher

texts:
SQL Server 2016 SP1 Enterprise Edition Data Engine (with SQL Agent) installed on an FCI called inst1.
SSIS 2016 SP1 Enterprise Edition installed on comp2. (comp2 is not in the inst1 cluster)
Filesystem SSIS packages on comp2.
SQL Agent job using the SSIS step type on inst1 calls the package store on comp2.

When I run the job on inst1, I get this error:

  To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher.

I have run the edition upgrade wizard, just in case the wrong edition was installed. The error persists.

Any ideas?


texts: Find rows with specific string inside column text

texts:
I'm new in working with SQL Server and I'm trying to select all rows which has a specific string inside a column.
The field has a big string of characters, for example:

  0x8F28EA0000000000000094EFFF00275F0A7F0000F86FFF00275EF57F0000

I tried using the simple

SELECT * FROM DB WHERE COLUMN LIKE '%0F86FFF00%'

But as I have read that SQL Server doesn't have this syntax so how could I do it differently but get the same result?


texts:
This is a new MySQL Server install. I created my root password as part of the setup (on Centos 6.4).

When I connect to the server through a terminal, I can connect to MySQL and issue commands, using my root password.

select current_user;

gives me:

+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+

If I do:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxxxx' WITH GRANT OPTION

I get:

Query OK, 0 rows affected (0.00 sec)

But when I do:

SHOW GRANTS;

Here's what I get:

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+

Am I not supposed to see another line for root@% in addition to root@localhost?

The real issue I'm having is that I can't connect to MySQL from outside of localhost (as in, I'm logged in using a terminal session), and if the MySQL server is not giving root universal access (root@%), then that would explain the problem.  When I try to connect using PHP (from my local MAC), the following is the returned MySQLi object:

mysqli Object
(
    [affected_rows] =>
    [client_info] =>
    [client_version] => 50008
    [connect_errno] => 2002
    [connect_error] => Connection refused
    [errno] =>
    [error] =>
    [field_count] =>
    [host_info] =>
    [info] =>
    [insert_id] =>
    [server_info] =>
    [server_version] =>
    [stat] =>
    [sqlstate] =>
    [protocol_version] =>
    [thread_id] =>
    [warning_count] =>
)

I realize that granting root access from % is not a great idea, but at this point, I'm trying to figure out why I can't connect to the MySQL server, and when I solve that, I will restrict access.


texts: oracle db 19.3: impdp: use more than one cpu core on linux

texts:
I'm running impdp 19.0.0.0.0 on openSuSE 15.1 vm and processing object types takes far too long. On htop I see that only one CPU core (of totally 8) is used. Doing research for about an hour didn't bring any results. I can state that it's not a Linux misconfiguration.
Does anyone have thoughts on that?


texts: Postgresql vs MySQL - Which is better for join queries & writing data(inserts)

texts:
I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing data(inserts) to the database. There will be less updates and deletes queries.

I have read this article on performace comparison of Postgresql vs MySQL.

Also I have gone through below links as well.

https://stackoverflow.com/questions/8181604/postgres-9-1-vs-mysql-5-6-innodb

https://stackoverflow.com/questions/110927/would-you-recommend-postgresql-over-mysql

https://stackoverflow.com/questions/724867/how-different-is-postgresql-to-mysql

MySQL vs PostgreSQL Wiki

MySQL vs PostgreSQL: Why MySQL Is Superior To PostgreSQL

My problem is some of the links on the stackoverflow is out dated. Some of the people say that Mysql better & vice-versa.

Since I'm more concerned with join queries & writing data to the database Which is better for me? Postgresql vs MySQL? What approachs should I take to design a database like this?

Given that please don't consider this as another Postgresql vs MySQL question. I have done my research and I'm only concerned with join queries & writing data to the database scenario. I also got to know that PostgreSQL is better for GIS data. .


texts: Migrate a schema structure from one server to another MySQL

texts:
We have two Database environment server (Development and Production).
Production DB is already being used in Production(means it already has production data etc.).

My question is, is it possible to migrate databases(structure only) from Development to Production without affecting the data that is already in the Production server?

Note that, there are already changes made in the Development databases, means some of the tables/columns in a table exist in Development but doesn't exist yet in our Production database.

I'm using Workbench Community 6.2 as client.

Our Development database is using MySQL Community and our Production database is using MySQL Enterprise.


texts: Mysql - Average price for top 10% cheapest volume (algorithm help)

texts:
My question is very similar to this but not the same. The table is the same: rows with ID, volume, and price. In short, I need an SQL query that will give me the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows. In even shorter terms, average price for the 10% cheapest volume. For a better explanation, I've written an algorithm in pseudocode on how I would have done it in C++:

Sort table by price (lowest first).
Calculate total volume and store 10% of that value in a variable named 'threshold'.
Declare a variable 'sum' with value 0 and start a loop that will iterate through the aforementioned sorted table.
Check if 'sum' is less than 'threshold'. If true, add the row's volume value to 'sum', and add the examined row to a list. Increase iterator by 1.
Repeat step 4 until condition is false. When false, it should break the loop and return the list.
Make an arithmetic average of the returned row's prices.

I've written the entire thing with three explanations as to make sure people understand and hopefully provide their own insight on how to solve this in SQL. There might be a single function, or a very simple query that can solve this entire thing, so I gave a detailed context to the problem.


texts:
I need to take a copy of a SQL Server database. The database has approx 40 views that link to other databases, and I need sample data from those views too. Also, the views are encrypted so when I try and generate a script from one of the views this is the error:

Property TextHeader is not available for View '[dbo].[TABLE1]'. This property may not
exist for this object, or may not be retrievable due to insufficient access rights.  The
text is encrypted. (Microsoft.SqlServer.Smo)

What would be the best way to get a copy of this database containing data from the views? I was thinking about creating extra tables and populating with sample data but that seems long-winded, so is there a better way?


texts: SQL Server: Remove public role privileges from login account

texts:
I've got a SQL Server (2012) and I need to create a read-only user.  At first, I was just going to create a user, and then give them the db_datareader role.  But while looking around, I saw the public role, and how it had Select, Update, Insert and Delete privileges on all the objects in the db.

Is there a way for me to say "Yes, I know you are a member of the public role, but I'm revoking privileges X,Y,Z"?


texts:
I have database Registered and I backup that database from right click on database and backup and it is store registred.bak file.

After I format the system once again I want to restore that database then I follow the step create new database and right click on that newly created database and select restore and I choose file those already I take as back up file (.bak) file and click on restore the database restoring completed after then in right side of explorer view my database name registered (restoring...).

What is restoring point? Because when I click on this database it says

  The dataase not accessible

What is this please explain me ???


texts: Database stalls on innodb_buffer_pool_wait_free

texts:
I have 4 identical MariaDB 10.0.33 databases that are regularly experiencing database stalls in different situations. I'm trying to understand how to tune the innodb parameters to prevent these stalls. I am aware I can/should add more ram/more disks to better support this workload. However, I really want to understand the implications innodb_lru_scan_depth has on the innodb_buffer_pool_pages_free metric, and what is causing frequent stalls as measured by the innodb_buffer_pool_wait_free metric.

First, some config:

Raid10 x4 SSD, 64gb ram, zero swap
innodb_buffer_pool_size = 48G (yes i know this can be increased a little)
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_log_file_size = 4G
innodb_log_buffer_size = 8M
transaction-isolation = READ-COMMITTED
innodb_flush_neighbors = 0
innodb_io_capacity = 2000 # default 200
innodb_io_capacity_max = 4000 # default 2000
innodb_max_dirty_pages_pct_lwm = 50
innodb_max_dirty_pages_pct = 75
innodb_flushing_avg_loops=90
innodb_lru_scan_depth = 16384 (this is the most important)

Second, some stats:

Disk reads are fairly stable throughout the day at ~3k - 5k, peaks to 10k
Disk writes are fairly stable throughout the day at ~1k peaks to 2k
Disk read latency less than ~0.5ms on average, some spikes to 1ms
Disk write latency less than 3ms on average, some spikes to 10ms
Disk utilization averaging ~80% some spikes to 95-100%
Between 1% and 2% buffer pool cache miss ratio
Consistent 1k innodb pool pages flushed per sec
Consistent 13-18% nnodb pool bytes dirty
Consistent 350k - 420k pages dirty
Frequent drops of innodb_buffer_pool_pages_free from 130k down to 0k
Frequent spikes of innodb_buffer_pool_wait_free to as high as 25k

Increasing the innodb_lru_scan_depth from the default of 1024 improved the situation. innodb_buffer_pool_pages_free immediately grows every time I increase it.  In the attached dashboard, I changed from a value of 16384 to 32768 innodb_lru_scan_depth at 11:40 on two of the four servers, and the pages free jumped up to ~260k from ~130k. There seems to be strong correlation between a lack of free pages, lru depth, and wait free.

My primary question is, what is the relationship between this LRU parameter and the database stalls I am experiencing?

Second question, other than adding more ram/storage bandwidth, from the database perspective, what can I tune to throttle expensive workloads that may be running in parallel, and saturating my disks? There may be a correlation
with the innodb_buffer_pool_pages_old metric increasing when I'm seeing periods of database stalls.  This seems to indicate something is doing a large full table scan, and making the buffer pool less optimal.


texts:
I want to query a jsonb column with the @> operator.

This query gives results:

select * from json_table1
where p_attribute @> '{"age":"37"}'

This query doesn't fail but also doesn't give results:

select * from json_table1
where p_attribute -> 'age' @> '37'::jsonb

The table used in the example:

create table json_table1 (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute jsonb,
    quote_content text
)

The index on p_attribute:

create index gin_idx on json_table1 using gin(p_attribute jsonb_path_ops)


texts: MongoLab: Does hardware effect performance of a find() query

texts:
I asked a pervious question on StackOverflow about if document size affected the performance of fetching that document (the document is 3MB in size)
https://stackoverflow.com/questions/30226219/does-the-size-of-a-document-affect-performance-of-a-find-query

The conclusion was, yes it does. It seems that the larger the document you are trying to fetch (using the find() query) the longer it will take to get a response (because the document will take longer to download given it's larger size)

Currently I'm hosting my database on MongoLab. I'm currently using the free sandbox tier, but I do plan on upgrading to a shared or dedicated plan in a few months.

My question is, will upgrading to a shared or dedicated plan improve the performance of downloading a large document? I'm assuming it will because the paid plans have better hardware, but will the performance improvements be noticeable/significant. Just curious what other thought.


texts:
I have implemented a master slave config several times and each time the process goes well and works for a day or two but soon bombs out saying that replication stopped because such and such table does not exist.

relevant part of my.cnf:

[client]
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock

[mysqld]
server-id                       = 2
master-host                     = 10.10.10.33
master-connect-retry            = 60
master-user                     = repl
master-password                 = redacted
relay-log                       = /var/lib/mysql/mysql-relay-bin
relay-log-index                 = /var/lib/mysql/mysql-relay-bin.index
log-error                       = /var/lib/mysql/mysql.err
master-info-file                = /var/lib/mysql/mysql-master.info
relay-log-info-file             = /var/lib/mysql/mysql-relay-log.info
log_bin                         = /var/lib/mysql/mysql-bin.log
binlog_format                   = ROW
datadir                         = /var/lib/mysql
pid-file                        = /var/lib/mysql/mysql.pid
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock
binlog_format                   = row
max_connections                 = 200
max_connect_errors              = 1000
key_buffer_size                 = 256M

There are numerous instances where the slave reports that such and such table doesnt exist in a given database but in fact it does exist.

Any ideas on what may be causing this or ways to improve stability ? In case it matters, this mysql server is housing 125 databases and is about 60gb in size total.


texts: Insert sometimes takes more than 2 seconds in table with +6 million records

texts:
I have a table in my database that acts like a log to record accesses to some selected endpoints of my application.

That table contains:

id (primary key)
The http method (GET, POST, PUT, etc)
The endpoint, some useful data that might be passed
If the request was made by a logged in user, the user_id. Otherwise user_id is NULL (meaning that the request was made by a guest).

This table is starting to get big (already more than 6 million records) and sometimes the insert statements take more than 2 seconds. We don't perform "intensive" operations in this table: just INSERT and SELECT * FROM table WHERE user_id = xxxx.

Is it normal that sometimes this insert is done quickly and sometimes it takes more than 2 seconds? What can be done to reduce this time?

Thanks in advance!

EDIT

Table definition:

CREATE TABLE `user_actions` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `http_method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
      `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `request_data` text COLLATE utf8mb4_unicode_ci NOT NULL,
      `user_id` int(10) unsigned DEFAULT NULL,
      `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `user_actions_user_id_foreign` (`user_id`),
      CONSTRAINT `user_actions_user_id_foreign`
      FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=6312242 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This is an example of one INSERT statement that took 5.3 seconds:

insert into `user_actions`
   (`http_method`, `route_name`, `query_string`, `ip_address`, `user_agent`, `request_data`, `user_id`, `updated_at`, `created_at`)
values
   ('GET', '19-chars-string', '64-chars-string', '123.123.123.123', '139-chars-string', '[]', 96411, '2018-11-29 18:49:08', '2018-11-29 18:49:08');

Server RAM = 16GB
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_page_cleaners = 12


texts: SQL Server Disk I/O Latency During Specific Timeframe

texts:
We are experiencing high disk I/O latency in a specific timeframe in SQL Server. Every day at exactly 4:30 AM we see a significant difference in disk I/O and it lasts up to 6:30 or 9:30 AM. I already checked with the development and storage teams. They confirmed that everything is running normally.  Storage team declared that there is no issue in LUN and they are blaming SQL Server.

One thing is sure--some job is triggering exactly at 4:30 AM every day, which causes the high disk I/O. But I am not sure how to find that out.  I attached the screen shot of the SQL error log at 4:30 AM. All the monitoring tools and the logs are stating that the transactions are taking longer that usual due to high disk I/O. But nowhere is it mentioned what is causing it.

Ours is 3 node Always On environment. P1 - primary, P2 - Stand only, P3 - Read only.

The issue we are facing now is from P3 (Read only node).


texts:
I have a while loop that is running for days (bydesign) see related Is there a limit to how many backups can be appended to a single file?

I have a local variable that displays the loop count, when I run it in a query window I can display the count with print @counter

I am now running the while loop in a job, and I want to display the @countervalue.  After some research I tried RAISERROR (Transact-SQL) which should display in the SQL error log, but it is not working.  I read Using RAISERROR I think I have the code correct, but not sure it can be used in While loop, with a local variable.

Create the database and prep it

USE [master]
GO
Create database T_test

USE [master]
GO
ALTER DATABASE [T_test] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE T_test to disk= N'K:\Test\T_test.bak' with noinit --Create first full
GO

Try running in a query window to test

DECLARE @counter int
SET @counter = 0
WHILE 1=1
BEGIN
BACKUP DATABASE [T_test] TO
DISK = N'K:\Test\T_Test.diff' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'T_Test-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION
SET @counter = @counter + 1;
RAISERROR(N'Count equals :%d', 16, 1, @counter );
print @counter
END

Stop after a couple of loops and check the SQL logs and the query message

The error is not in the SQL logs, but is displayed in the query message.

I don't think you can use GO 5000000 with a local variable, at least in my attempts it resets every time it is re-declared.

How can I display the local variable in a while loop, real time?


texts:
Below is query ,i want to convert it into sub query
    declare @FirstDOM datetime, @LastDOM datetime
set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())+30),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))

--First Query

SELECT SUM(QTY) AS QTY, SUM(IWeight) AS Weight, EntryDate
FROM     ConIssuance
where EntryDate between @FirstDOM and @LastDOM
GROUP BY EntryDate
order by EntryDate asc

--Second Query
SELECT SUM(s.prdqty) AS qty, SUM(i.weight) AS Expr1, s.EntryDate
FROM     Probale AS s INNER JOIN
                  ItemMasterFile AS i ON i.CodeItem = s.Codeitem
                  where S.EntryDate between @FirstDOM and @LastDOM
GROUP BY s.EntryDate

--Third Query

SELECT SUM(Bweight) AS weight, SUM(Bpqty) AS qty, EntryDate
FROM     Bigbalprd
 where EntryDate between @FirstDOM and @LastDOM
GROUP BY EntryDate

Output  Horizontally.


texts: Is there a way to create a user in Oracle 19c with a specific UID? We're going through a major upgrade and would like to preserve the UID's of our existing users.


texts: Identify faulty sub-query (subquery returned more than 1 value)

texts:
I am trying to resolve an issue with a query that fails with this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This error isn't detailed enough for me as the query in question has several sub queries of which some have another level of sub queries.

I found the query itself using SQL Server Profiler but I want to know if there is a tool I can use to identify which sub-query this fails at.

The only other way I can think of resolving this is by brute forcing: remove each sub query until the outer query works. However, given the amount of brute forcing I would have to do, it seems like must be a better way to find out.


texts: advice : restore a table with PostgreSQL in a database

texts:
(dba beginner)
As an exercise, I test different methods to restore all or part of a database.
I have 2 dumps of a table: the first one in sql format, the second one in custom format. So I use psql in the first case, pg_restore in the second.
So far, I haven't been able to restore the table. I would like to create it in another schema or database, so that I can compare the one that has been restored and the one that exists, for example.
The table has a primary key, triggers and constraints.
Does my goal make sense?
If yes, what is the correct order/track? Thanks

EDIT :
I tried in both cases (sql, backup) to restore the table in another database.
table in a database test :

pg_restore -h localhost -p 5433 -U admin -d test -v c:\blabla.backup

14 errors beginning with the table can't be created because the sequence of the primary key doesn't exist.

psql -d test - h localhost -p 5433 -U admin -f c:/blabla.sql

same message about sequence then error : relation _mytable_ doesn't exist then psql:c:/.../: commande \N invalide

Also, I created in the db test the schema my table belongs to before trying to restore. It failed as well.

I did not use -a option because the empty table has to created before.
Here's what I did the most.


texts:
I'm trying to set the default schema to a user in Redshift with alter option, because I do not want it to write to the public schema by default. I am using the following order:

alter user user_1234 set search_path to atc_schema;

It does not give me any error, but when I create a table with the user without specifying the schema, it is not created in the schema indicated in the previous statement.


texts: Data Modeling Problem (Tasks / Timerecords / Users)

texts:
It's late here an I'm having troubles wrapping my head around it.

I have 3 tables: Tasks, Timerecords, Users

A task is assigned to a user and always has an "estimated duration".
Timerecords are created by users and always link to a task. They always have a "logged duration".

I now want to get a list of all users with their sum of estimated duration (over all tasks) and their sum of logged duration (over all tasks), but I can't really figure out how I can make this possible with these 3 tables and avoiding circular relationships. But it wouldn't be a problem if I'd have to copy a table (it's for a data analysis project).


texts: How do I debug an ETL process that is unable to commit any records?

texts:
I used a DB link to connect to another DB and delete a couple of records. Later I ran an Informatica Workflow (ETL tool) to load data into that database using a DB connection that connects directly to it.

The Workflow hasn't committed any records since 2hrs (it should run just for 30mins or so..) so I am guessing it is because I was not able to run "commit" using the DB link. The ETL tools logs don't provide any error or debug information at this point.

Any ideas how I could debug this..? I ran another ETL process to  update a table and then run commit. But even after that the first process keeps on running without committing any records. I am not a DBA expert so probably am misinterpreting the DB behavior.. would really appreciate any suggestions. Thanks!


texts:
I followed the tutorial here to build a very simple task and was able to get that task to work.

Here are my current tools:

Sql Server 2012
Visual Studio 2013 (project is compiled in .net 4.0)
Sql Server Data Tools (from Sql Server 2012)

What I want to do, however, is use the default IU with the property window that you see inside Sql Server Data Tools.  Here is a tutorial to do so, but it is quite old.

The problem I seem to be having is that I can't seem to locate Microsoft.DataTransformationServices.Controls.  I tried adding the compatibility tools, but that did not bring the dll into my GAC.

I would really like to be able to pull in that dll and use DTSBaseTaskUI so that I can inherit the default look of the UI within Sql Data Tools without recreating the already used UI.


texts:
I have inherited maintenance of an in-house application along with a couple of views in our ERP system's database.

I have begun to suspect that my predecessor was drunk as a skunk when writing these views, or was making the queries deliberately complicated as a form job protection (if so, the joke is on me, because the guy quit).

One of the things that I find highly confusing about the views is that the WHERE-clause contains a sub-clause that looks a little bit like this:

SELECT ...
WHERE ...
OR ( (('*') IS NULL)
     AND Project.Invalid = 0
     AND ... )
OR ...

From my understanding, that ('*') IS NULL part should basically be a no-op. Yet, when I comment that line out, the number of rows returned by the query jumps from 460 to ~350,000.

I am fairly confused by this, I would have expected no change at all in the number of results.
In the same query, another branch of the WHERE-clause contained a similar sub-clause, ((-255) IS NULL), and when I commented that one out, the number of results stayed the same.

What is going on here? Is there something funky going on, or am I missing something obvious?

Thank you very, very much for any input!!!


texts:
I'm working on a simple company database design but i can't figure out why i can't create foreign keys for some tables. Let me give a screenshot of the design;

I'm trying to create foreign key for customer_bills to sales table. Because a sale record must be created for printing a bill. Samething applies waybills too.

Is is a software bug(mysql workbench) or a mysql rule that i never heard :)

Edit: I forget to write what is the problem. It doesn't allow me to select sales.id field. It just shows idsales (which is not a field in table). I think it's just recommening to create that field but why?


texts: LTREE extension does not exist after CREATE EXTENSION

texts:
I am trying to create a trigger that uses ltree column.

CREATE EXTENSION IF NOT EXISTS ltree;

Running

SELECT * FROM pg_catalog.pg_extension

gives the following output:

extname     |extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
------------|--------|------------|--------------|----------|---------|------------|
plpgsql     |      10|          11|false         |1.0       |NULL     |NULL        |
ltree       |      10|      361964|true          |1.1       |NULL     |NULL        |
postgres_fdw|      10|      361970|true          |1.0       |NULL     |NULL        |

I cannot drop the extension because I get an error that there are dependencies. I was also able to create a table that has a column with data type ltree without an error.

When trying to create a trigger, I get the following error:

SQL Error [42704]: ERROR: type "ltree" does not exist
  Position: 252

This is a snippet of the trigger and the problem seems to be happeing when I declare a variable of type ltree:

CREATE OR REPLACE FUNCTION asset.update_asset_path()

RETURNS TRIGGER

AS $$
    DECLARE
        db_path_ltree ltree;
        single_asset INT;
        s3path TEXT = '';
        ltree_array TEXT[];
        v_asset_name TEXT;
        i_last_element INT;

What is confusing, is this is only happening in one environment for dev. Every other postgres server does not have this problem and is using the same code.

Any ideas why this is happening?


texts: Redshift table occupies exponentially more disk space in production cluster

texts:
Production Cluster details:

Node Type dc1.8xlarge
Nodes 25
2.56TB SSD storage per node

Test Cluster details:

Node Type ds2.xlarge
Nodes 6
2TB HDD storage per node

When same table with exactly same DDL & encoding is unloaded and copied from production cluster to test cluster, its disk footprint reduces exponentially. This has been tested with multiple tables with different distribution styles and sort key patterns.

Example:
Table A (No sort key, DISTSYLE EVEN) - Size in production: 60GB; Size in test: 0.6 GB

Table B (Sort key, DISTSTYLE KEY) - Size in production: 96GB 100% sorted; Size in test: 1.4 GB 100% sorted

Any ideas what can result in this discrepancy? I have read most of redshift forums but not able to find a reason for this issue. I am using the admin view v_space_used_per_tbl (provided by AWS) for calculating size of the table.


texts: Why could query performance improve enormous after db restore on new server with worse hardware?

texts:
A asp.net page doing very slow to load a list from SQL Server has increased it's performance very much after the whole database has been restored on a new SQL Server. The asp.net app has moved from and to the same servers.

The page's performance has improved to something about 1/5 of the previous load time.

The new system even has less Memory (12 GB instead of 30 GB).
- on the old SQL Server is restricted to use 20 GB of 30 GB and it claims 20 GB.
- on the new SQL Server is restricted to use 8 GB of 12 GB and it actually claims 4.5 GB only

The new system has an Intel Xeon E CPU while the old has an Intel Xeon X CPU.

Users say the performance has improved since the moving in general.

Unfortunately I have no benchmark of the page and/or the queries I am investigating on and also not for the involved servers at all.

This behavior is the opposite of my expectations!

So the question is, what could possibly have improved this?

Do statistics possibly play a role here? I would guess they are part of the db and so included in the restored db - or have they been created from scratch? Any thoughts and ideas are very welcome.

EDIT 11-March 2016:

I tested disk performance of both systems: The old disk performance made 0,3 MB/S to 55 MB/s with Average of 30 Mb/s. The new system shows 141 Mb/s to 1690 Mb/s with average 803 Mb/s. So since the disk performance seems to be more than 10x better, I guess this maybe a good reason for better performance. We are having a lot of disk usage because of bad design (select *) and lots of blobs and (n)TEXT types. So lots of data is not in cache and has to be loaded repetetively from storage. Additional the transaction log has been separated from the data files to another drive (Average 800 Mb/s), this was not the case on the old server).
Would anyone agree that we can assume this is (besides all your interesting thoughts) the main reason for improvement?


texts: SQL 2016/SQL 2017 CE Performance on Information_Schema inferior to SQL 2014 out of the box

texts:
I have a Database with 2300 Tables/Views. When i run this query to do a simple select on information_schema, i get over 2 minutes on SQL 2016/2017 but under 5 seconds in SQL 2014. When i enable the trace flag 9481. It works. Here is the query. The difference is the Cardinality Estimator, however my question is why would it not work out of the box like in SQL 2014.

SELECT
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_SCHEMA,
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME,
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME as CONSTRAINT_KEYS,
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as REFERENCE_SCHEMA,
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME as REFERENCE_TABLE,
  unique_usage.COLUMN_NAME as REFERENCE_COLUMN
FROM
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INNER JOIN  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
ON
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME =
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
INNER  JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ON
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME
INNER JOIN       INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE unique_usage
ON
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
  unique_usage.CONSTRAINT_NAME

If any of you has a database with large number of tables.You are welcome to try it and see what you see. Our adhoc reporting tool depends on these queries and it is proving a roadblock to upgrade to SQL 2017.


texts: Is there a way to force an index to stay in memory with SQL Server 2008?

texts:
I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster (around 1s). After a few hours, a slow/then fast cycle starts again.

I've checked in my execution plan that all the needed index were present and appropriately used, and I assume the performance difference is due to the fact that the index are actually in memory for the subsequent queries (am I right, or are there other possible causes?)

I'm also running a lot of other queries using indexes as well, but those queries are less time-consuming and their performance is less critical, so I'm concerned those indexes are actually pushing my critical index out of the memory cache.

Apart from the obvious 'add more RAM' fix, I've been thinking about scripting dummy queries to run every hour to force the index back in memory.

Is there a more elegant way to do this? Like a way to hint SQLServer that if it only has enough memory to keep one single index cached, it should be that one ?

I know that usually the best thing is not to mess up wich SQLServer with regards to that kind of things, but the unusual nature of my query (runs very rarely, but time-critical) makes me believe it would make sense (if possible).

I'm also curious to know if there's a way to know which indexes are cached in memory at a given time ?


texts:
I'm trying to run a report for my clients in real-time. I created a new eximstats user (renamed the old one) with:

update mysql.user set password=password("XXXXXXXXX") where user="eximstats";

In mysql.db I'm showing eximstats user and DB.

My connection:

  $exim_connect = new mysqli($exim->host, $exim->user, $exim->password, $exim->db);

This is my result with and without a password:

  EXIM Connection failed: Access denied for user 'eximstats'@'localhost'
  (using password: YES)

Is this even possible? If yes, what I'm I missing?

cpsrvd 11.52.0.22
Database client version: libmysql - 5.1.73
PHP extension: mysqli


texts: Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

texts:
System:

I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total).

Issue:

During the Index Rebuild (~20 minutes), we fail to insert data into the DB

Question:

How can I reduce the downtime during the Maintenance Plan? I was thinking about ensuring the Transaction Log size is small (Truncating it 4 times a day), but I don't think that will help.

I'm also uncertain if Rebuilding the Indexes more often will help. (Since I don't know if this is a "bulk" operation that will benefit from recently Re-Indexed Tables).

Note: Upgrading to SQL Server 2005 Enterprise for the Live Re-Indexing feature is not an option.


texts: in SSMS, different result sets for a same query in Azure

texts:
I have a complex stored procedure that calls different other stored procedures.
I've never seen this behaviour in over 20 years of dba work (since Sql 7).
When I call the stored procedure with about 100 parameters it returns 13 records.
When I do exactly the same call but with "actual execution plan" selected, I get 26 records...
The records are all boubled.
Has anyone witnessed this behavior in their past experience?
This is happening on Azure, I tried it with SSMS c18.5.1 and Azure data studio 1.21.0 and get the same behavior.
It might be something under the hood that the actual execution plan is doing on Azure...
I just need some guidance from anyone that would have witnessed this in the past.
Continuing the troubleshooting I can now confirm that this happens when SET STATISTICS XML ON is set.
Another observation, when running the stored procedure with the SET STATISTICS XML ON, every run just adds a set of 13 records from the previous run as if a table wasn't truncated before running... but as mentioned previously, when I call the stored procedure multiple times without SET STATISTICS XML ON or with SET STATISTICS XML OFF, I don't get the same behavior... I get 13 records everytiime.
I found the problem but still don't understand the behavior...
declare @a NVARCHAR(10)=NULL
print isnull(@a,N'999999999')
if @a is null
begin
drop table ##mytest
create table ##mytest (...)
end

To my surprise, when I run something like this with SET STATISTICS XML ON, it prints '999999999' but for some odd reason doesn't go into the if statement...
But if I run the same thing with SET STATISTICS XML OFF, it does...
Any idea?
The problem is that I can't reproduce this behavior with code such as
SET STATISTICS PROFILE on
SET STATISTICS xml on
declare @a NVARCHAR(10)=NULL
print isnull(@a,N'999999999')
if @a is null
begin
select 'hello'
drop table ##mytest
create table ##mytest (a int, b varchar(5))
end


texts: Backup a large postgres database for PITR

texts:
I have one postgres 9.1 master and 2 slaves setup with streaming replication with WAL archive.

Now, I am trying to set up for PITR for at least a window of 1 week. This is to cover for any administrative errors (like dropping an important table by mistake). Here is what I understand of PITR

We need a base backup of the database at a known point in time.
We need a WAL archive from the time of base backup to the point of error.

Using the above 2 we can rebuild the database to any point, in between.

Database involved is large, approx 1TB, growing at 30 GB a week

I want to understand what is the best way to achieve fast base backup creation and low performance overhead on the running instances. I can think of following alternatives but not sure

if there are any pros and cons to each
if there are other better alternatives

Is it better to take a base backup once every week and accumulate
WAL until the next base backup OR take just one base backup (say
now) and backup only WAL week-after-week.
In case we do base backup every week, is it better to use
pg_basebackup or pg_start_backup + rsync + pg_stop_backup. Will the
second option be significantly faster if I use the same destination
disk/volume/folder week-after-week, because of rsync ?
Is it better to run base backup against the master or is it possible
to take a disk snapshot of the postgres cluster after stopping one
of the slaves temporarily and resuming the replication after the
snapshot. Will this snapshot be usable to setup an instance in
recovery mode in case I want to do PITR or will there be any data
consistency or time line issues ?

Thanks for any insights.


texts:
We have a MySQL database on an IBM i (as/400) that has many people with admin access. We would like to audit who is making changes, and when they are made. On SQL Server we would use DDL Triggers, but those don't exist on MySQL. There is a counter proposal to do this via CREATE EVENT, but that is also unimplemented.

What options do I have to monitor DDL? Most people are using MySQL admin. I'm using MySQL Workbench, and inside the network, and inside the network all parties have direct access to the MySQL database because Microsoft Access accesses it via linked tables.


texts: What is the "other miscellaneous information" in the Shared Global Area (oracle database)?

texts:
I'm studying Oracle database ,and it says here that the SGA contains these data structures :

Database buffer cache
Redo log buffer
Shared pool
Java pool
Large pool (optional)
Data dictionary cache
Other miscellaneous information

What is that "    Other miscellaneous information" that is referred to?

thanks


texts: Will exporting tables in one db server to another db server release space in server?

texts:
Our production db server mount points are 95% full.There are a few tables which are not required. Hence if I export those tables to our test db server and drop those tables, will it release space in production db server?

Thanks


texts: Calculate number of consecutive years (not gapless)

texts:
I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this:

Order_year, Contact
2015, 123
2014, 123
2013, 123
2011, 123
2010, 123
2015, 234
2014, 234
2011, 234

I want to calculate for each year how many consecutive order years each contact_id has. The problem is, there are gaps such as contact_id = 123, it has 2010-2011, then 2013-2015.

The output would be:

report_year, contact, consecutive_years
2015, 123, 3
2014, 123, 2
2013, 123, 1
2012, 123, 0
2011, 123, 2
2010, 123, 1
2015, 234, 3
2014, 234, 2
2013, 234, 1
2012, 234, 0
2011, 234, 1

Or if you just want to calculate the max order year with the max consecutive years, that's fine, such as:

order_year, contact, consecutive_years
2015, 123, 3
2011, 123, 2
2015, 234, 2


texts:
We have an instance in Oracle 10g. Only one schema is used for a web application (Internet-facing), which is only user for reading information, no adding or modifying data.  The rest of schemas are not used.

Archived Redo Logs is activated.  What is strange for us is that archived files are generated every day, including Sundays and holidays, when nobody comes to work.

I thought Archived Redo Logs are generated when inserting, deleting, or updating data, never because of select statements.

Is that correct?


texts:
I read: Use composite type to create new table

I have a table called locations, e.g. representing objects with lat. and long. coordinates.

In another table, I declared a column of type locations (just for fun, not trying to be smart), i.e.

CREATE TABLE XXX (..., some_column locations, ...);

And now I'm asking myself what this means and if I could store a locations object in there.

And here's what I tried to do:

SELECT pg_typeof(ROW(x)) FROM locations x LIMIT 1;

which returns record. I tried casting this to locations, i.e.

SELECT ROW(x)::locations FROM locations X LIMIT 1;

which yields

  ERROR:  cannot cast type record to locations

Next I tried defining a composite type type_location based on the columns of the locations table, and created a typed table (CREATE TABLE ... OF ...) based on it. Still I am unable to do ROW(x)::locations.

Ultimately I'm trying to get a value to store into table XXX of type locations (or type_location) but I don't understand which part my reasoning is fallacious.

PS: I'm not trying to create a sound database design using this construction but really only just toying around with Postgresql and its type system.


texts: MonetDB ODBC driver memory allocation error on Windows

texts:
The driver is the latest from a month ago: 2014-SP2: 11.17.17, 64-bit.
Windows is Server 2012.

I have limited options to test whether earlier versions of the driver would work or this version works on other systems. I also found no other resource on this version of Windows whether ODBC works outside of Stata (ODBC Data Sources does see the database, but there is no way to test or query it).  I received little tech support about troubleshooting this error:

To be clear, I noticed that MonetDB.org instructs us to install both the 64-bit and the 32-bit ODBC driver. I did that. The following screenshot shows that the MonetDB server is running, and Windows sees both the 32-bit and 64-bit data sources I set up (I only changed the names of the database to ifn, the rest are the defaults of MonetDB).

I understand this is not a coding issue, slightly off topic, though probably relevant for the DBA community.


texts: Does a change in a query executed with sp_executesql cause recompilations?

texts:
I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure.

Sometimes the query changes (not the parameters, but the query itself). I know this causes  SQL Server to invalidate the plan.

If I change this query:

exec sp_executesql
        N'SELECT nome
        from customer
        where name = @name'
    ,N' @name varchar(50)'
    , @name=N'rafael';

to

exec sp_executesql
        N'SELECT nome, address, phone
        from customer
        where name = @name'
    ,N' @name varchar(50)'
    , @name=N'rafael';

The plan will be invalidated.

Does this also cause a re-compilation?

Those queries are not the queries I am running, they are only examples.

EDIT

I am not using #temp tables. Bellow are 2 queries that are being recompiled:

SELECT TOP 1000 X, Y, Z
FROM MyTable
WHERE Contains(VarcharMaxField, @Clause)
    AND XXX IN (SELECT data FROM fn_ArrayTable(@Pub))
    AND YYY = 1 AND ZZZ < DateAdd(n, -10, GetDate())
ORDER BY A, B, C;

SELECT TOP 1000 A, B, C
FROM MyTable
WHERE Contains(VarcharMaxField, @Clause)
    AND (XXX IN (@Pub0,@Pub1))
    AND (AAA= 1)
    AND (BBB< DateAdd(n, -10, GetDate()))
ORDER BY A, B C;

I had to change the fields due to an internal company policy. The fn_ArrayTable is a function that returns a comma separated string as a table.


texts: Are idx_scan statistics reset automatically (default)?

texts:
I was looking at the tables (pg_stat_user_indexes and pg_stat_user_tables) and discovered many indices that are not being used.

But before I think about doing any operations to remove these indices, I need to understand what period was the analysis of this data (idx_scan), has it been since the database was created?

In the pg_stat_database (stats_reset) table is there a date that normally is today or up to 15 days ago, but does this process interfere with the tables I mentioned above?

No command of% reset () was executed.

Do the% reset () commands clear the tables (pg_stat_user_indexes and pg_stat_user_tables)?

My goal is to understand the period of data collected so that I can make a decision.


texts: MS SQL Server using SSL Connection over the Web?

texts:
I'm looking to have an external SQL Server 2008R2 Express instance and be able to connect to this via SSL using a 'sql.my-domain.com' address. So far, I've looked at a few Microsoft links on the subject and also a blog found on here (links towards the bottom), but I'm not having a great amount of luck with this.

My main issue appears to be getting the actual certificate to be recognised by the server. I have installed this into the MMC > Certificates location, applied permissions for the account which is running the SQL Express instance and I am still unable to even select the account when I am in SQL Configuration Manager and trying to apply the certificate to the instance.

I'm running SQL Express as this is needed as a proof-of-concept that we can get it working, but we'll be moving onto SQL Standard for the live environment.

Does anyone have an explanation in everyday terms, any knowledge of the pitfalls or any other web resources I can check out to try and get this service running?

EDIT - I need more than 10 rep to post all the links. Here's two I've used.

http://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx

http://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-server/


texts: How to check in a trigger if the entire row remains the same after an update?

texts:
Of course i could do it for every column comparing like this:

if (old.column1 = new.column1 and old.column2 = new.column2...)

But it would b hard coded and hard to maintain if I add another column in the future, for example.

Is there a way to check if all the columns remain the same without checking every individual columns manually?


texts: Simultaneous money withdrawls from a distributed/replicated database

texts:
Lets say I have $10 in my bank account. The database is replicated in North America and Asia. At the exact same time I do a withdrawl of $10 in N. America and in Asia, meaning that my withdraw would do work on two different copies of the data. How can I be prevented from ending up with a balance of -$10 and $20 in my pocket?

Is it possible to produce the proper result with NoSQL?

(I'm not a database expert, just curious)

Thanks!


texts: Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?


texts: Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN?

texts:
Background

We're running this query:

SELECT COUNT(*) AS "__count"
FROM "xfiler_document"
INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id")
WHERE (
    "xfiler_document"."added_on" <= '2015-06-28T23:59:59.999999-04:00'::timestamptz
    AND "xfiler_tx"."company_id" = 1
    AND "xfiler_document"."added_on" >= '2015-06-01T00:00:00-04:00'::timestamptz
)

The result of EXPLAIN ANALYZE is @ https://explain.depesz.com/s/voMo

The table is large (+30 million records), so we've run into a performance problem with a report we're running (this can be seen at the URL above; +25 second query).

Question

It seems to me, based on the analyze results, that the performance issue here is due to the fact that Postgres is comparing the added_on values (step 4), and then re-checking them for some reason (step 3) for every row from the query's added_on time frame, rather than only checking the 18,515 rows that apparently match the company_id comparison in step 7.

Firstly, am I understanding / explaining the performance issue correctly?

Secondly, is there a way to solve this (other than denormalizing company_id)?

Database

Postgres 9.6.1

xfiler_document

       Column        |            Type             |                          Modifiers
---------------------+-----------------------------+--------------------------------------------------------------
 id                  | integer                     | not null default nextval('xfiler_document_id_seq'::regclass)
 tx_id               | integer                     | not null
 doc                 | character varying(255)      |
 added_on            | timestamp with time zone    | not null

Indexes:
    "xfiler_document_pkey" PRIMARY KEY, btree (id)
    "xfiler_document_tx_id" btree (tx_id)
    "xfiler_document_added_on" btree (added_on)
Foreign-key constraints:
    "xfiler_document_tx_id_fkey" FOREIGN KEY (tx_id) REFERENCES xfiler_tx(id) DEFERRABLE INITIALLY DEFERRED

xfiler_tx

           Column           |           Type           |                       Modifiers
----------------------------+--------------------------+--------------------------------------------------------
 id                         | integer                  | not null default nextval('xfiler_tx_id_seq'::regclass)
 name                       | character varying(255)   | not null
 company_id                 | integer                  | not null

Indexes:
    "xfiler_tx_pkey" PRIMARY KEY, btree (id)
    "xfiler_tx_company_id" btree (company_id)
    "xfiler_tx_name" btree (name)
Foreign-key constraints:
    "xfiler_tx_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company_company(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "xfiler_document" CONSTRAINT "xfiler_document_tx_id_fkey" FOREIGN KEY (tx_id) REFERENCES xfiler_tx(id) DEFERRABLE INITIALLY DEFERRED


texts: "ERROR: column rel.relhasoids does not exist" with PostgreSQL 12 and PgAdmin 4.3

texts:
I get the following error when trying to select rows from a table:
ERROR:  column rel.relhasoids does not exist
LINE 1: SELECT rel.relhasoids AS has_oids

Configuration is:

Linux RHL8
NAME="Red Hat Enterprise Linux"
VERSION="8.2 (Ootpa)"
postgresql12-server-12.3
psql (12.3)
PgAdmin 4.3 en 4,5


texts: Is there any way to turn off the "loading" message when a page refreshes in Reporting Services?

texts:
I have a report that auto-refreshes every 30 seconds.  Usually this happens fast enough that you don't even notice.  Sometimes, however, the load takes a bit longer.  When this happens, the existing data is "greyed out" a bit, and a animated box that says "Loading" appears.

Is there any way to prevent the data from beign "greyed out"? I find it very distracting.

The report is part of a monitoring tool.  We have it on a jumbo-tron.  Under certain conditions, it changes color, which catches people's attention.  The "greying out" also catches people's attention but for the wrong reason.


texts: How to know the user doing a delete action on an audit table, when using a shared login?

texts:
Background information:

I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app.
Audit records are created via triggers.
DML in my app's database will generally come from a login that a service uses to get into the database. Because of this, I think the result from SYSTEM_USER will always be the same when called in a trigger.
My app does not store user data currently, though a string UserId is given to it each time DML is to done (done exclusively in stored procedures).

The problem I ran into is that when a user deletes a record, I want to know who did it. Because it will done by the same login, I don't want to see that all action were done by service, I want to see which user did it. This isn't an issue on an update, because we have ModifiedBy columns that will be updated via a sent in UserId on updates.

The question is: Is there a way to set the SYSTEM_USER or otherwise get the user information into the trigger when a delete is run?

The "best" idea I have right now, though I'm not sure if it is a good idea yet, is that in the service I check to see if the current UserId is in the database as a user, and if not create a user object for them. Then run stored procedures with EXECUTE AS User = @UserId. Then when DML is done in the stored procedure and the trigger fires, SYSTEM_USER should return the user from the EXECUTE AS.


texts: MySQL: Does access to table data not in an index via a join causes a file-sort when ordered

texts:
The inclusion of category.name in the SELECT, causes the query to perform a
Using index; Using temporary; Using filesort, and also the rows scanned are not affected by the LIMIT 0,1.

CREATE TABLE `item` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `category` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `feature` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` INT(10) UNSIGNED DEFAULT NULL,
  `category_id` INT(10) UNSIGNED DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tbl_feature_id_item_id_foreign` (`item_id`),
  KEY `tbl_feature_id_category_id_foreign` (`category_id`),
  CONSTRAINT `tbl_feature_id_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`),
  CONSTRAINT `tbl_feature_id_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `item` (`id`) VALUES (1),(2),(3),(4),(5);

INSERT INTO `category` (`id`, `name`, `created_at`, `updated_at`)
  VALUES
    (1, 'a', '2016-12-02 22:49:46', NULL),
    (2, 'b', '2016-12-02 22:49:48', NULL)
;

INSERT INTO `feature` (`id`, `item_id`, `category_id`, `start_date`, `created_at`, `updated_at`)
VALUES
(1,  1, 1, '2016-12-01', NOW(), NOW()),
(2,  1, 2, '2016-12-02', NOW(), NOW()),
(3,  2, 1, '2016-12-01', NOW(), NOW()),
(4,  2, 2, '2016-12-02', NOW(), NOW()),
(5,  3, 1, '2016-12-01', NOW(), NOW()),
(6,  3, 2, '2016-12-02', NOW(), NOW()),
(7,  4, 1, '2016-12-01', NOW(), NOW()),
(8,  4, 2, '2016-12-02', NOW(), NOW()),
(9,  5, 1, '2016-12-01', NOW(), NOW()),
(10, 5, 2, '2016-12-02', NOW(), NOW())
;

EXPLAIN EXTENDED
SELECT
    item.id
  , feature.id
  , category.id
  , category.name -- Is the cause of the temporary table; file-sort;
FROM
  item
LEFT JOIN feature ON (
  feature.item_id = item.id
  AND feature.start_date = (
    SELECT
      MAX(start_date) AS start_date
    FROM
      feature
    WHERE
      feature.item_id = item.id
  )
)
LEFT JOIN category ON (
  category.id = feature.category_id
)
ORDER BY item.id DESC -- or ASC
LIMIT 0, 1 -- Is ignored in the table scan
;

MySQL 5.7.16

I believe it may be because of the need to access the source table after the sort is done. because the file-sort is performed in Mode 2

http://s.petrunia.net/blog/?p=24

Mode 2: sort  pairs and produce a sequence of rowids which one can use to get source table’s rows in the required order (but this will be essentially hit the table in random order and is not very fast)

Adding an index to category.name does resolve the issue. Is this is because category.name is now part of the index?

Can anybody confirm, and possibly provide a better solution than adding the index, as in reality, it does not resolve the issue as the point in time construct is repeated for multiple other features i.e. feature_a, feature_b.

Why is Using index; Using temporary; Using filesort performed?

Why does the LIMIT 0,1 not affect the rows scanned?


texts: Why Oracle sessions are still alive after two weeks being Inactive

texts:
DB: Oracle 10g

O/S: Windows Server 2003 64 bits

I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe)

select * from V$session
where UPPER(program) LIKE '%W3%'
order by logon_time

According to Logon_Time, why are still alive sessions from 31/07/2012 or 01/08/2012 or any session before today (21/08/2012)?

I have configured on sqlnet.ora:
SQLNET.EXPIRE_TIME= 20
So it means that every 20 min Oracle is checking if connections are still active.

All user-schemas have default profile. It would means that no session would never expired or die?

Added in response of Phil's comment:

Added in response of Phil's answer:


texts: What is the ideal disk allocation size using our Nimble SAN?

texts:
I have seen many recommendations to use 64K disk allocation size for data drives such as from the SQL Server Setup Checklist on BrentOzar.com.

The best practice guide for using our Nimble SAN with SQL Server at http://www.scribd.com/doc/275409689/Nimble-Storage-SQL-Server states to use 8K allocation because SQL Server uses 8K pages.

I know SQL Server uses 8K pages, but I thought it actually worked with extents which are 8 x 8k pages = 64K. The Nimble SAN only supports up to 32K allocation and not 64K.

Should we use 32K for data and log files to align with the SAN when creating the drives in Windows, or use their recommendation of 8K?


texts: WIN10 -Unable to run MySql server after installation - 5.7

texts:
I previously had MySql 5.7 installed and it worked fine.  I last used it 3-5 months ago.  When trying to run the server/daemon I ran into the following error:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld
mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 5.7\data\' (Errcode: 2 - No such file or directory)
2017-10-09T23:44:49.897072Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-10-09T23:44:49.897072Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-10-09T23:44:49.898074Z 0 [Note] mysqld (mysqld 5.7.19) starting as process 39932 ...
2017-10-09T23:44:49.901585Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\MSI.lower-test
2017-10-09T23:44:49.901585Z 0 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.7\data\MSI.lower-test
2017-10-09T23:44:49.902588Z 0 [ERROR] failed to set datadir to C:\Program Files\MySQL\MySQL Server 5.7\data\
2017-10-09T23:44:49.903592Z 0 [ERROR] Aborting

2017-10-09T23:44:49.904597Z 0 [Note] Binlog end
2017-10-09T23:44:49.905096Z 0 [Note] mysqld: Shutdown complete

I uninstalled all MYSQL programs via Control Panel and then reinstalled from the site but I still receive the exact same error messages.  Does anyone have any advice for what to do?

Thanks in advance


texts: Predict MySQL errors when turning on STRICT mode(s)

texts:
I've got a database which hasn't been very well designed. For example, there are DATETIME fields (not null) which have values like 0000-00-00 00:00:00.

I want to turn on STRICT_TRANS_TABLES mode. The database functions but the apps often fail with broken queries (often INSERT/UDPATE)

Are there any tools to recommend what should be fixed by looking at the data in tables & structure?

Specifically looking for:

Built in warnings (if any?)
Asking the mysql server if it thinks there is anything wrong with the structure & table data


texts:
I have a big performance problem beetwen my local mysql instance and the server.
I have this request :

DELETE a
FROM a
LEFT OUTER JOIN
( SELECT MAX(id) as id,
`v_id`,
`v_statut`,
`c_controle`,
`c_d_controle`
 FROM `a`
 GROUP BY `v_id`,
 `v_statut`,
 `c_controle`,
 `c_d_controle`
 ) AS t1 ON `a`.id = t1.id
 WHERE t1.id IS NULL;

The purpose of the request is to remove duplicate identified by the GROUP BY. I have already created key for the group by.

In my local server (5.6.17) this request is about 15 seconds on 700 000 lines, on the server(5.5.43) this request is endless.

Is there any mysql tuning or SQL request optimization that can help me.

Thanks.

CREATE TABLE :

Create Table"
temp_import_vigipresse  "CREATE TABLE `temp_import_vigipresse` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_creation` datetime DEFAULT NULL,
`vigipresse_id` int(11) NOT NULL,
`vigipresse_tele_id` varchar(8) DEFAULT NULL,
`inutile_date` date DEFAULT NULL,
`vigipresse_statut_depot` tinyint(4) NOT NULL,
`vigipresse_commentaire` varchar(255) DEFAULT NULL,
`vigipresse_type_parution` tinyint(4) DEFAULT NULL,
`vigipresse_admission_id` int(11) DEFAULT NULL,
`date_depot` datetime DEFAULT NULL,
`etablissement_code_regate` varchar(6) DEFAULT NULL,
`etat_date_film` tinyint(4) DEFAULT NULL,
`date_film` date DEFAULT NULL,
`vigipresse_code_hld` tinyint(4) DEFAULT NULL,
`vigipresse_lot_stp` tinyint(4) DEFAULT NULL,
`code_statut_resultat` tinyint(4) DEFAULT NULL,
`commentaire_resultat_controle` varchar(255) DEFAULT NULL,
`valeur_resultat_controle` varchar(255) DEFAULT NULL,
`code_critere_controle` tinyint(4) NOT NULL,
`code_departement_postal_controle` varchar(2) DEFAULT NULL,
`numero_oqs_saisi` smallint(6) DEFAULT NULL,
`vigipresse_numero_parution_constate` int(11) DEFAULT NULL,
`inutile_numero_parution` varchar(255) DEFAULT NULL,
`vigipresse_type_parution_declare` tinyint(4) DEFAULT NULL,
`vigipresse_date_depot_prevue` date DEFAULT NULL,
`vigipresse_quantite_articles` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `vigipresse_id_statut_code`

(`vigipresse_id`,`vigipresse_statut_depot`,`code_critere_controle`),
KEY `vigipresse_id_statut` (`vigipresse_id`,`vigipresse_statut_depot`),
KEY `vigi_unique`
(`vigipresse_id`,
`vigipresse_statut_depot`,
`code_critere_controle`,
`code_departement_postal_controle`)
) ENGINE=MyISAM AUTO_INCREMENT=730705 DEFAULT CHARSET=utf8"

EXPLAIN LOCAL (5.6):

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,PRIMARY,temp_import_vigipresse,ALL,NULL,NULL,NULL,NULL,228470,NULL
1,PRIMARY,,ref,,,5,qspresse.temp_import_vigipresse.id,10,"Using where"
2,DERIVED,temp_import_vigipresse,ALL,vigi_unique,NULL,NULL,NULL,228470,"Using temporary; Using filesort"

EXPLAIN SERVER (5.5)

Endless request


texts: How to INSERT INTO ... RETURNING for matrices in PostgreSQL?

texts:
Approximate size and type of c_matrix_data is 2k x 3k double which is equivalent max to rand(5000,5000) in Matlab 2016b.
The random set selection of such data corresponds max 1 GB size (so BYTEA ok) for many measurements, presenting about 5% of all measurements.
Queries to CREATE and INSERT bulk with RETURNING

DROP TABLE IF EXISTS measurements, c_matrices;
CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        db_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        channel_id INTEGER NOT NULL,
        c_id INTEGER NOT NULL,
        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT no_duplicate_measurements UNIQUE (time),
        CONSTRAINT no_same_measurements UNIQUE (db_id, patient_id, channel_id, c_id)
);

CREATE TABLE c_matrices (
        c_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL,
        c_matrix_data BYTEA NOT NULL,
        c_size_in_bytes INTEGER NOT NULL,
        UNIQUE (measurement_id, event_index_start, event_index_end)
);

-- http://dba.stackexchange.com/a/107062/69807
WITH ins1 AS (
   INSERT INTO measurements (measurement_id, db_id, patient_id, channel_id) -- TODO not sure since this autogenerated
   VALUES (1, 2, 3, 4) -- if data comes from client app
   -- SELECT ... if data from other db TODO  how to do this here SELECT c_id FROM c_matrices WHERE measurement_id==777
   RETURNING measurement_id  -- generating a serial ID
   )
INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
    VALUES (1, 2, 3, 'testBinaryData', 1234)
    RETURNING c_id -- generating a serial id -- TODO right?
    FROM   ins1;

I run psql data -f creates.sql and get where the error comes from INSERT INTO ... RETURNING

DROP TABLE
CREATE TABLE
CREATE TABLE
psql:creates.sql:38: ERROR:  syntax error at or near "FROM"
LINE 10: FROM   ins1;

PostgreSQL: 9.4
OS: Debian 8.5 64 bit
Client application: Matlab 2016b
Linux kernel: 4.6 of backports
Hardware: Asus Zenbook UX303UA


texts: Information_Schema.Tables reports different result in MySQL 5.5 and MySQL 5.7

texts:
I have started testing some MySQL 5.7 databases to replace our ageing 5.5 Databases, and there seems to be a significant change in the way the information_schema.tables Table records the data size.

I run the following query:

SELECT
    table_schema AS table_schema,
    table_name AS table_name,
    (data_length + index_length + data_free) AS table_size,
    ROUND((data_length + index_length + data_free)/POWER(1024,3),2) AS table_sizeGB,
    t.data_length,
    t.index_length,
    t.data_free
FROM information_schema.TABLES t
WHERE table_schema = 'myDb'
ORDER BY table_size DESC, table_name
LIMIT 10;

This gives me the 10 largest tables. I run this daily to record the growth over time. On MySQL 5.5 it takes apx 5 seconds, on MySQL 5.7 it is instant.

On MySQL 5.5 I get the following result for the largest table:

myDb | myTable | 319383535616 | 297.45 | 319116279808 | 262012928 | 5242880

On MySQL 5.7 I get:

myDB | myTable | 270051115008 | 251.50 | 269824819200 | 226295808 | 0

Both Databases were created a few days apart. The 5.7 Database is a Slave of the 5.5 Database. Neither are active yet (just sitting as a slave of the Active System).

If I look on the server, the tables .ibd file is 302GB on the 5.5 Database, and 292GB on the 5.7 Database.

I expected a small discrepancy (one is using Antelope, on is Barracuda File format - both are innodb). Data is being constantly added to the database, and very little (if any) is being removed.

On the MySQL 5.5 database, I can see the numbers increase each time I run the query, even just seconds apart. But on MySQL 5.7, the numbers haven't changed since I started looking at it, a week ago.

Is this correct behaviour? Is there a better way to monitor this in MySQL 5.7 that I have missed?

-- UDPATED 2018-10-16

After three weeks, the result of the query is still virtually the same on MySQL 5.7:

5.5 -- myDb | myTable | 327890632704 | 305.37 | 327616036864 | 268304384 |6291456
5.7 -- myDb | myTable | 270055309312 | 251.51 | 269824819200 | 226295808 | 4194304

The Physical size on the server is 310GB on MySQL 5.5, and 300GB on MySQL 5.7.

Both Tables are created identically.

Neither database is active yet, so the only queries running (besides my testing) is from replication.

Both databases uses innodb_file_per_table.

The MySQL 5.5 was copied from an active slave using mysqldump.

The 5.7 database was copied from the MySQL 5.5 database using mysqldump + mysql_upgrade.

Optimize table has never been run on these tables.


texts:
I ran the sp_BlitzCache script from the Brent Ozar toolkit on a server that I am monitoring (I am new at the company for about 3 weeks). The result of the procedure has the following values:
╔═════════╦═════════════╦═══════════════════╦══════════════════╦════════════════╦══════════════╦════════════╦═════════════════════╦═══════════════════╦═════════════════╦═════════════╦═══════════╦═════════════╗
║  Cost   ║ #Executions ║ Executions/Minute ║ Execution Weight ║ Total CPU (ms) ║ Avg CPU (ms) ║ CPU Weight ║ Total Duration (ms) ║ Avg Duration (ms) ║ Duration Weight ║ Total Reads ║ Avg Reads ║ Read Weight ║
╠═════════╬═════════════╬═══════════════════╬══════════════════╬════════════════╬══════════════╬════════════╬═════════════════════╬═══════════════════╬═════════════════╬═════════════╬═══════════╬═════════════╣
║ 47,4959 ║      212068 ║ 416,6365          ║ 10,9146          ║ 21883508.2710  ║ 103.1910     ║ 61,4143    ║ 47839177.6180       ║ 225.5841          ║ 73,0647         ║ 15372027688 ║     72486 ║ 66,6277     ║
╚═════════╩═════════════╩═══════════════════╩══════════════════╩════════════════╩══════════════╩════════════╩═════════════════════╩═══════════════════╩═════════════════╩═════════════╩═══════════╩═════════════╝

I took these values about 4pm. I have a column that indicates the plan was created about 8am (8 hours window).
I verified that we have the total reads value = to "15372027688". Is this possible? In the space of 8 hours, I have about 15 billion reads? I have the CPYU weight with the value = 61,4143 and the cost = 47,4959
Can anyone explain to me better the cost of the query, what it means? Is about the cost threshold of parallelism?
Also have the warnings running the procedure:
Warnings

Missing Indexes (3)
Parallel
Downlevel CE (the database is in Compatibility Level 110 while running on a SQL Server 2014 instance) and is running OUTSYSTEMS databases)

I also run performance counters on the server and have about 1400 connections into the SQL Server instance. On the network adapter traffic I have the total bytes in about 92 000 000, 00/sec.


texts: Learning Oracle, does it matter if I install 10g or 11g?

texts:
I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g.

Is 11g backwards compatible?

As in, just for learning, will I be able to not use the added 11g features?


texts:
How to split the huge mysql table (size around 200GB) internally?
Is there any mechanism other than partition?
The table size is daily growing. Table created using innodb with indices.
Thanks in advance.

Table have around 30 columns. Most of the time, i use select * as i need to show all the field. Then there is no problem in fetching data. Problem is size of table. ibd file size is now around 200gb. There are many tables like this. So i need to split the table internally as i don't have access to modify the code.


texts: On Sql Server can you disable an account at the database level?

texts:
Let's say I have an account at the server level and they have access to database1, database2, and database3 on the server.

If I run ALTER LOGIN "account_name" DISABLE this will disable the account and they can not access any of the databases on the server.

If I want to disable the account from database2, is the only way to do that by DROP USER "account_name"? Is there something similar to the server disable that can be done at the database level?


texts: Where can I download older versions of Oracle Instantclient?

texts:
Version 10.2 is linked from here, but I need 10.1.  Any clues where to get older copies of the instantclient packages?

http://www.oracle.com/technetwork/database/features/instant-client/index.html


texts: Execute code ONLY if we have the ability to do full text search on database (mysql >= 5.6.4)

texts:
I found this answer for how to have conditional logic for execution of mysql statements (https://stackoverflow.com/a/7537348/627473). Seems to work great.

But I need to find a way to execute code for a very specific condition. The code can only execute if the database supports FULL TEXT indexing for innodb. This happens in mysql 5.6.4 or greater. BUT I also need this to work for maria db other forks of mysql.

I am sure I could find a way for mysql version; but I don't really like relying on a version and would rather relying on feature detection.

Is there a way to do this?

EDIT: Here is code. I just have on more error that I am not sure can be fixed.

In phpMyAdmin the error "#2014 - Commands out of sync; you can't run this command now" is displayed. This does NOT happen from CLI. Is there a way around this?

delimiter //

create procedure update_phppos()

begin
set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size');

if  @supports_ft > 0 then

    REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('version', '15.0');

    else
        SELECT 'An error has occured. You MUST have mysql 5.6.4 or higher to update php point of sale' as 'ERROR';
    end if;
end;

//

delimiter ;

-- Execute the procedure
call update_phppos();

-- Drop the procedure
drop procedure update_phppos;


texts:
Kindly help me understand my below concern.

In our current business requirement. We have to pull the complete data everytime. We have to truncate out table and load it with 20million of data.

I want to know instead of truncating. If I do a drop and load the table with select * into. Will this frequent dropping of table have any adverse affect on database performance in long term. Like high fragmentation or something related to pages.

Be noted that we cannot use merge as per our current data.

Just wanted to know the adverse affect of frequent drooping and creation of tables.


texts: Database schema for passing requests to role based users

texts:
I am working on a Database schema where the scenario should be:

There are role based Users; Role 1, Role 2, Role 3 and Role 4
There is a Request model (or class, or table)
User with role Role 1 will create a new Request
This Request needs to be executed by a single User from each Role. Execution means passing on a Request from one User to an other and entering comments.
Role 1 will decide which User from each Role will execute the Request
The workflow of execution will be predefined, for now suppose Role 1 > Role 2 > Role 3 > Role 4 and then again > Role 1. The workflow will be cyclic.
But a User associated with a particular Role to execute a Request may change in an other cycle
I need to track the history with who executed a Request with what comments
Workflow should be changeable

I don't know whether I am clearly able to explain my requirement or not. But will request you to try to understand and/or discuss this question with me and help creating me this schema.

The current Db schema which I am thinking of, but is not very useful and scalable:

Request table

- id
- name
- some_other_stuff
- currently_assigned_to

Assignment table

- id
- request_id
- role_1
- role_2
- role_3
- role_4

Workflow table

- id
- request_id
- executed_by
- comment

The Assignment table will handle which User has to execute the Request based on which Role they are assigned to. Role 1 User will execute and the Request will be passed to Role 2 User and further on.

The Workflow table will save the history.

Your help is really appreciated.


texts:
I have a mysql running with bind-address "10.4.0.211".

From the documentation, I understand it like, the server accepts connections only from the IP - 10.4.0.211.

But I could able to connect to this mysql from another new server with the below configurations.

eth0      inet addr:10.8.0.7
eth1      inet addr:10.11.0.50
lo        inet addr:127.0.0.1

None of the above  has the bind address in them, but how come I am able to connect to the service. Please clarify.

I am suing the below connections string from the new server.

mysql -u<<username>> -p<<password>>  -h<<hostname of the mysql running server>> -A