texts: Setting PostgreSQL data_directory in postgresql.conf not working

texts:
I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According to the documentation, I should be able to set the PGDATA variable to the directory that contains postgresql.conf, and then specify the data_directory from within postgresql.conf to point to the directory that contains my data.

I installed PostgreSQL 9.3 via these instructions.

Steps I followed to configure Postgres (/data is a different partition from the OS):

mkdir -p /data/pgsql/data && mkdir /data/pgsql/config && sudo chown -R postgres:postgres /data/pgsql
sudo su postgres -c '/usr/pgsql-9.3/bin/initdb -D /data/pgsql/data'
sudo mv /data/pgsql/data/postgresql.conf /data/pgsql/config/ && sudo mv /data/pgsql/data/pg_hba.conf /data/pgsql/config/ && sudo mv /data/pgsql/data/pg_ident.conf /data/pgsql/config/
Edit /data/pgsql/config/postgresql.conf, making sure these variables are set as so:

hba_file = '/data/pgsql/config/pg_hba.conf'`
ident_file = '/data/pgsql/config/pg_ident.conf'
data_directory = '/data/pgsql/data'

sudo sh -c 'echo "export PGDATA=/data/pgsql/config" > /etc/sysconfig/pgsql/postgresql-9.3'
sudo service postgresql-9.3 start

This results in the error message:

/data/pgsql/config is missing. Use "service postgresql-9.3 initdb" to initialize the cluster first.

I looked at the init script (/etc/init.d/postgresql-9.3) and noticed this:

# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
    ...
else
    # No existing PGDATA! Warn the user to initdb it.

    echo
    echo "$PGDATA is missing. Use \"service $NAME initdb\" to initialize the cluster first."
    echo_failure
    echo
    exit 1
fi

I added an echo and PGDATA is set to /data/pgsql/config, which I would expect, but  I'm not sure how setting PGDATA and data_directory could even work, because the init script isn't checking postgresql.conf to get the data_directory like the documentation says it should. Am I doing something wrong? Is the documentation wrong?


texts: Can I create a view to edit ID mapping table without seeing the IDs?

texts:
I have 2 tables, A and B with an int primary key and a string name.

I have a 3rd table, C, with 2 columns that map the IDs of A and B together. I want to create a view to edit the mappings in table C, but I want the view to show the Name value rather than the IDs. The view would be edited via SQL Server Management Studio.

For example, the view should allow the user to type in "Item4" in the item column, and "Accessory1" in the accessory column, and table C gets updated with a new row mapping "4" to "1".

I am wondering if I can make this an editable view so I don't have to go to the trouble of writing a whole application to manage this.

If I go to the view in Microsoft SQL Server Management Studio, and right click, I can edit the top 200 rows. I know the constraint on this feature is that I can only edit if the change is to a single table. I would only be editing a single table, so there might be a way to get this to work. Is there a way? (My users are people (traders) who are familiar with SQL Server and prefer to have direct access to their data).


texts:
I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all memory. Finally i encountered with one table with the extention .ibd has almost 20GB. When i hit sudo du -a | sort -nr | head command, i finally found actual table. it was like this:

20937708        ./var/lib/mysql/databasename/tablename.ibd

I don't know what is going on. Please help me out. My server stopped almost due to the storage issue.


texts:
I read a lot of optimal number of innodb_buffer_pool_size and people say it must be larger with 10% than the innodb tables size, what is 60M. Accordyngly this the optimal size of the buffer is about 66M im my case.
Should I set this value more than 66M, for example 256M or 1G to achieve better performance? I have enough memory to add more to innodb buffer pool if that improve the performance.
Thanks


texts: Trying SQL query with subquery in WHERE clause

texts:
For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one.

Now, we have linked all participants to conversations. So on creating a new one, we count the number of people marked as participants (included current user (:totalUserCount)), en then would like a query to check if a conversation with x participants and all marked user(name)s exist before creating a new conversation.

After a few attempts we have a query that executes, but the result is not correct.

SELECT `app_company_user_cons`.*
FROM (`app_company_user_cons`)
LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_user_cons`.`id` = `link_company_user_cons_company_users`.`company_user_conversation_id`
LEFT OUTER JOIN `app_company_users` app_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id`

WHERE :totalUserCount = (SELECT
    COUNT(*)
    FROM (`app_company_users`)
    LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id`
    WHERE link_company_user_cons_company_users.company_user_conversation_id = app_company_user_cons.id
)
AND `link_company_user_cons_company_users`.`company_user_id` =  :currentUserID
AND `app_company_users`.`name` =  :otherUserID2
AND `app_company_users`.`name` =  :otherUserID3
AND `app_company_users`.`name` =  :otherUserIDn
LIMIT 1

The EXPLAIN function shows me:

+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| id | select_type        | table                                | type   | possible_keys | key     | key_len | ref                                                               | rows | Extra                                               |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 1  | PRIMARY            | NULL                                 | NULL   | NULL          | NULL    | NULL    | NULL                                                              | NULL | Impossible WHERE noticed after reading const tables |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | link_company_user_cons_company_users | ALL    | NULL          | NULL    | NULL    | NULL                                                              | 2    | Using where                                         |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | app_company_users                    | eq_ref | PRIMARY       | PRIMARY | 4       | de1210mo_CRM.link_company_user_cons_company_users.company_user_id | 1    | Using where; Using index                            |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+

Not sure if we are missing something. Any ideas on how to solve this?
Thanks in advance!

(PS: Our app adds AND 'app_company_users'.'name' = for all participants in an array)


texts: Restoring a database that belonged to an AG

texts:
I'm trying to perfect our disaster recovery steps...

We take backups of a database that, in production, is in an AG.  It is the only database in the AG.

If we lose the building, I need to be able to restore it to a new instance.  Initially, for time's sake, this instance would not be running in an AG.

Of course, if you try a normal restore, you get error Msg 3104: "RESTORE cannot operate on database 'SI_EDI_PRD' because it is configured for database mirroring or has joined an availability group."

And, since the instance is not configured for AlwaysOn, you can't try to run the ALTER AVAILABILITY GROUP command.

How can one restore this database??


texts: Setting up default values from another table

texts:
Here is a problem I am trying to solve. Suppose I have a table called States (Name, Population). Let us assume it has one row - (Montana, 2million). Now suppose I have another table called People (Name, State, Profession). In this table I would like to set a default for state as the column value Name of table States, i.e., Montana. Now, doing so, for a tuple in People, (John, NULL, Programmer), I should get (John, Montana, Programmer) when I select this row. Suppose at a later date, I update Montana to Alabama. I should get (John, Alabama, Programmer). Any idea how using default, constraints, trigger or anything like that.

A bit more clarification:

Suppose I did

CREATE TABLE state
  ( state_name VARCHAR(20),
    population INTEGER
  );

INSERT INTO state VALUES ('Montana', 2000000);

Then I did

CREATE TABLE person
  ( person_name VARCHAR(50),
    person_state VARCHAR(20) DEFAULT sate_name(state),
    profession VARCHAR(50)
  );

INSERT INTO person VALUES (NULL, 12000);
INSERT INTO person VALUES ('New York', 145000);
INSERT INTO person VALUES ('Mississippi', 5000);
INSERT INTO person VALUES (NULL, 1000);

I know, I cannot do the DEFAULT as I wrote. But assuming I can do something that will let me achieve the effect is what I am looking for.

After this, if I wrote

SELECT *
FROM person

Should give me

Montana, 12000
New York', 145000
Mississippi', 5000
Montana, 1000

Now if I deleted the tuple ('Montana', 2000000) and then inserted ('Alabama', 50000), the query

SELECT *
FROM person

It should give me

Alabama, 12000
New York', 145000
Mississippi', 5000
Alabama, 1000

Hope it explains. I know about default, and join that you all suggested. Thanks for those comments. But that is not what I am hoping to achieve.

The MOST important thing to observe here is that the default value should come from another table, and cannot be a constant. For example, it cannot be the following statement:

CREATE TABLE person (person_name VARCHAR(50), person_state VARCHAR(20) DEFAULT 'Alabama', profession VARCHAR(50));

Where 'Alabama' is a constant. It must be a column name from another table.


texts:
Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the Simple Recovery model.

Quite often and for certain practical reasons many databases are created using SSMS. However mistakes can be made and the operator can forget to specify the Simple Recovery model. This leads to a "suprise" a few days later when the box is struggling with disk space due to three or four 60GB log files that have never been truncated.

I can make the Simple Recovery model the default setting for new databases by configuring the recovery model on the model database. However, is this recommended, if I do this could it come back and bite me in any way in the future?


texts:
Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this

SELECT * FROM a_table WHERE a_field LIKE '%а_pattern%'

until I change pattern to N'%а_pattern%'. I never had to add this prefix in the past, so I am curious. a_field is defined as nvarchar(255), but I think the reason is something else.


texts:
I have set up sql server 2008 in windows server 2008 r2 and it works fine. However, I have deleted the my user from logins in the sql server accidentally and now I cannot find a way to log in to the sql server and I believe I did not set up any other user well. Think of it as I just installed and removed the user.

Is there anyway that I can add an account?


texts:
On an Oracle 12c database I want to reorganize a LOB segment. I used to following sql to generate the alter move command:

    select 'ALTER TABLE '||s.owner||'.'||l.table_name||' move to('||l.column_name||') store as (TABLESPACE NAME1);'
      from dba_segments s, dba_lobs l
     where s.segment_name = l.segment_name
       and s.tablespace_name = 'NAME1'
       and segment_type='LOBSEGMENT'
       and partition_name is null;

The lob segment I want to rebuild is about 200Gb. Can I use the same tablespace the lob is in? The total free space in that tablespace needs to be 200Gb? During moving the lob is locked?


texts: PostgreSQL - Flatten nested JSONB object array

texts:
Im my database with Postgres 11, i have this JSON in my rows stored in a JSONB field, and i need to flatten out the list of objects that are over "ports" key, so, instead of having a json output i need to have them in a "flattended table" way like below
port_name, port_description, port_status_int, port_status, port_speed_int, ports_speed
...
"GigabitEthernet0/0", "", 2, "Not Connect", 1000000000, "1G"
"Bluetooth0/4", "", 2, "Not Connect", 1000000000, "1G"
...

Note that the lenght of the "ports" object is not static and may vary.
As far i know, this it my solution...i have some doubts: is this solution ok? it can be a bottleneck? it can be optimized?
SELECT  mytable.measurement_id,
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_name' as "port_name",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_description' as "port_description",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_status_int' as "port_status_int",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_status' as "port_status",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_speed_int' as "port_speed_int",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'ports_speed' as "ports_speed"
from mytable where device_id = 20;

Any toughts?


texts: Insert a range of numbers from a call to sp_sequence_get_range

texts:
Given the following code:

CREATE SEQUENCE dbo.NextTestId AS [bigint]
 START WITH 10 INCREMENT BY 2 NO CACHE
GO

DECLARE
  @variableNumberOfIdsNeeded INT = 7, -- This will change for each call
  @FirstSeqNum SQL_VARIANT , @LastSeqNum sql_variant, @SeqIncr sql_variant;

EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.NextTestId',
            @range_size = @variableNumberOfIdsNeeded,
            @range_first_value = @FirstSeqNum OUTPUT,
            @range_last_value = @LastSeqNum OUTPUT,
            @sequence_increment = @SeqIncr OUTPUT;

-- The following statement returns the output values
SELECT @FirstSeqNum AS FirstVal, @LastSeqNum AS LastVal, @SeqIncr AS SeqIncrement;

I get a result like this:

FirstVal    LastVal    SeqIncrement
-------     -------    --------------
38          50         2

I would like to have a set based way to take these results and insert each value (skipping by SeqIncrement) into this table:

DECLARE @newIds TABLE (IdType VARCHAR(100), [NewId] BIGINT)

So when I am done, a SELECT * from @newIds would return:

IdType    NewId
-------   -------
TestId    38
TestId    40
TestId    42
TestId    44
TestId    46
TestId    48
TestId    50

NOTE: I don't want to use a loop if possible.  Also, I will need to get a variable amount of results (this one shows 7, but it will change each call).

I think there may be a cross apply or some such thing that can do this.  But I can't seem to figure it.


texts: How to do rolling upgrade MariaDB 10.0 -> 10.1 on ubuntu 14

texts:
I'm trying to figure out how to do a rolling upgrade of our galera cluster running on MariaDB on Ubuntu 14.04.  In testing, the first node I upgraded started complaining about the timestamp column in mysql.innodb_table_stats having the wrong type, INT vs Binary(4).
I then tried an actual un-install -> install type upgrade, and it seemed to work until it connected to the cluster, and then it started with the same error as before.  All the documents that I've found talk about this process on a standalone server, but not one that is part of a cluster.

I did look over this similar post, but, in the first comment, you can see that Ubuntu does it just enough differently, that I'm not sure how to do this.

Any Ideas?
Thanks


texts: Why would sp_helprolemember show different MemberSID for same member?

texts:
Have two databases on same SQL Server instance.

If I run EXEC sp_helprolemember I get different MemberSID values for dbo on each. (the 2nd half of the SID is different, the start is the same)

db #1

DbRole = db_owner, MemberName = dbo,
MemberSID = 0x0105000000000005150000003D85B1B0F17DF0097CD12B

db #2

DbRole = db_owner, MemberName = dbo,
MemberSID = 0x0105000000000005150000008641B613459B05E16185E7

(note 2nd half of SID mangled by removing last 10 chars in case its sensitive)

What does the second half of the SID signify - in this case (dbo being a 'legacy style sql server login')

Would this SID difference account for any permission differences between the two databases?


texts: Export SQL Agent jobs with steps and scripts

texts:
I recently saw some kind of export of SQL jobs configured under SQL Server Agent that I would like to achieve as well.
Unfortunately I don't have any trace to author of that report to ask him\her about report creation, hence looking for help here as I had no luck googling it nor searching in here.

Report was in excel (so I guess originally was some csv or maybe directly xls) and contained every single job from SQL instance with one step per row and most importantly containing also script content of each job step and some further details.

As for an example as SQL has default job syspolicy_purge_history only, export will contain 3 rows for each of it's step.

I can't seem to find it as any build in export, so I guess some query maybe or I have missed this somewhere

Anyone aware of any such report?  Any help is welcome


texts:
I have come across an awkward situation where I have got CSV files with | as the column delimiter. In these files, I don't have Text column qualifier. (i.e. ").
My problem is when I am importing these CSV files using BULK INSERT, if the last column of a row has a line break in it, the text continues into the next row.
I am unable to use SSIS so I am looking for a pure T-SQL solution.

It would be great if someone could guide me in the right direction. I tried reading files using openrowset but that's not changing anything either.


texts: SQL Server Database recovery from corrupt database

texts:
Last week I backed up my SQL Server by using Backup Exec 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.

Does anyone know what causes this? I'm suspecting corruption here (it's just a suspicion as I'm not sure). Please help me. The copies of my databases are very crucial.

Many thanks in advance.


texts: Will an index that is a partial key of the begining of another index perform any better than the longer index?

texts:
If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches the simpler index. For example if I had a non clustered index (TransactionDate, ClientID, State) and my search criteria is only TransactionDate and ClientID what search performance gains would I achieve by creating a second index of just TransactionDate and ClientID. Index maintenance (inserts, updates, deletes) and disk space usage is of no consideration. Low level detail of how sql server implements and utilizes indexes would be greatly appreciated.

The data types are TransactionDate DATETIME with a zero timestamp, ClientID VARCHAR(6), and State CHAR(2). The table has about 16 million rows, 100 dates with about 160,000 records for each day and 500 clients. The table is loaded daily with all the records having a new date.


texts: Not able to see the master gtid in gtid_executed_set slave status after switching master. But replication is working fine

texts:
I recently switched source of a slave(C) from a existing master. The old architecture was B<--A-->C, A being the primary source for B and C. After switching master, now it is working as A-->B-->C. B became a intermediate master. From the show slave status of C, I am not able to see the gtid from the B. It is still getting gtid from master A.
Version-  mysql 5.7.28

mysql> show slave status\G -- ON C
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: IP OF B
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000672
          Read_Master_Log_Pos: 70366432
               Relay_Log_File: relay-bin.000016
                Relay_Log_Pos: 53188067
        Relay_Master_Log_File: mysql-bin.000672
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 70366432
              Relay_Log_Space: 70367146
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10704145
                  Master_UUID: df099cf3-a19e-11eb-b1e2-fa162eeb7312
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 37459d18-f34e-11e6-99c6-fa163e412d1c:21687008938-21687811445
            Executed_Gtid_Set: 1c8a0d8b-1e61-11e6-a6b1-0242faa6889b:1-3551278267,
37459d18-f34e-11e6-99c6-fa163e412d1c:1-21687811445,
43863d38-818d-11eb-8b95-fa162e09f6b3:1-6462,
876dbf6e-f427-11e6-b618-fa162e498e29:1-5511063062,
f3957247-7828-11eb-934f-fa163e53d6d7:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

For switching master I have followed these steps.
1) stop slave on C and then B.
2) START SLAVE UNTIL SQL_BEFORE_GTIDS ='37459d18-f34e-11e6-99c6-fa163e412d1c:21687008938';--to sync B and  C
3) CHANGE MASTER TO MASTER_HOST='IP OF B', MASTER_USER='user', MASTER_PASSWORD='password';--ON C
4) start slave; --ON C

What is the wrong in my configuration steps.
Thanks.


texts:
My centOS 7 server had an unexpected shutdown. For some reason one of the mysql database shows that all tables are empty, but if I browse the files in /var/lib/mysq/database I can see the files(.frm and .ibd and the other files) opening them with a text editor I can read some of the data in it.

What's wrong here? why is one of my database not reading the files or loading the data?

Edit: I still have all my files related to the database such as ibdata1, all other databases/schemas are working fine except for one. That one database I can browse the tables but loading the data shows empty tables.


texts: Self referencing scalar function nesting level exceeded when adding a select

texts:
Purpose

When trying to create a test example of a self referencing function, one version fails while another one succeeds.

The only difference being an added SELECT to the function body resulting in a different execution plan for both.

The function that works

CREATE FUNCTION dbo.test5(@i int)
RETURNS INT
AS
BEGIN
RETURN(
SELECT TOP 1
CASE
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN  dbo.test5(1) + dbo.test5(2)
END
)
END;

Calling the function

SELECT dbo.test5(3);

Returns

(No column name)
3

The function that does not work

CREATE FUNCTION dbo.test6(@i int)
RETURNS INT
AS
BEGIN
RETURN(
SELECT TOP 1
CASE
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
END
)END;

Calling the function

SELECT dbo.test6(3);

or

SELECT dbo.test6(2);

Results in the error

  Maximum stored procedure, function, trigger, or view nesting level
  exceeded (limit 32).

Guessing the cause

There is an additional compute scalar on the estimated plan of the failed function, calling

<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END">

And expr1000 being

<ColumnReference Column="Expr1000" />
<ScalarOperator ScalarString="[dbo].[test6]((1))+[dbo].[test6]((2))">

Which could explain the recursive references exceeding 32.

The actual question

The added SELECT makes the function call itself over and over, resulting in an endless loop, but why is adding a SELECT giving this result?

Additional info

Estimated execution plans

DB<>Fiddle

Build version:
14.0.3045.24

Tested on compatibility_levels 100 and 140


texts: Restore failed for server (Microsoft.SqlServer.SmoExtended)

texts:
I am using SQL Server 2008. While I am restoring database from backup file I got the error.

  Restore failed for Server 'WIN-TUT3YRM1MMN\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: RESTORE detected an error on page (44262:41495) in database "KBCLDBNEW" as read from the backup set. (Microsoft.SqlServer.Smo)

I have tried restoring in new database but still gives the error. I am cannot find the what is the problem. Thanks for helping me.


texts: AlwaysOn Availability Groups: Confusion with some concepts

texts:
In Sql Server 2012, I am trying to install Failover Clustering to configure AlwaysOn Availability Groups. When I run Cluster Validation test wizard, it comes back with a lot of errors. One of them points to not having privileges to create 'Computer Object' in our Active Directory. So, I tried to gather any documentation that would help me understand what this means and I found this article on MSDN

Now, I have a question:

In the MSDN article, the paragraph right above this diagram talks about some 'Computer Account' and 'Computer Account for Clustered Service'. I re-read the paragraphs several times, but I am not sure if I can distinguish between these two. Could someone with experience on AoAG, shed some light on what this means.


texts:
A server SERVER_A has some data.

A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this:

CREATE view myview as
select * from
openquery ( SERVER_A, select .... )

A server SERVER_C defines B as a linked server.

I have full access to SERVER_C, I can ask for some changes to SERVER_B, but not to SERVER_A. All my applications will access server SERVER_C, and need the data from SERVER_A. The views on SERVER_B are quite complex, and they cause timeout before returning anything.

I think that to improve the performance on these queries I need to turn the views on SERVER_B into "indexed views". To index a view, I must create a clustered index.

Questions:

1) Where should I define the clustered index? Should it be at SERVER_B, or I can somehow define it on SERVER_C?

2) Will the use of an indexed view on SERVER_B affect in any way the performance of SERVER_A even when the view is not being used?


texts: Installing SQL Server 2008 R2 Service Pack 1

texts:
I have SQL Server 2008 R2 installed on my computer. I want to install Service Pack 1 for SQL Server 2008 R2. Do I need to select upgrade option or new installation during installation of service pack or remove current installation and install SQL Server again.

I am trying to select "new installation or add new features" on first page of installation and then  selecting current instance on next page but It is giving some error like registry value invalid.


texts:
I created a stored procedure that I got on Erin Stellato's course on Pluralsight to report if there are any configuration changes on the server as a nightly job as follows:

CREATEPROCEDURE dbo.usp_SysConfigReport1
(
    @RecentDate DATETIME,
    @OlderDate DATETIME
)

AS

BEGIN;
    IF
    @RecentDate IS NULL
    OR @OlderDate IS NULL
    BEGIN;
    RAISERROR('Input parameters cannot be NULL', 16, 1);
RETURN;
END;
SELECT
        @@SERVERNAME ServerName,
        CaptureDate,
        [O].[Name],
        [O].[Value] AS "OlderValue",
        [O].[ValueInUse] AS"OlderValueInUse",
        [R].[Value] AS "RecentValue",
        [R].[ValueInUse] AS "RecentValueInUse"

    FROM [dbo].[ConfigData] O

    JOIN

        (SELECT [ConfigurationID], [Value], [ValueInUse]

        FROM [dbo].[ConfigData]

        WHERE [CaptureDate] = @RecentDate) R on [O].[ConfigurationID] = [R].[ConfigurationID]

    WHERE [O].[CaptureDate] = @OlderDate

    AND (([R].[Value] <> [O].[Value]) OR ([R].[ValueInUse] <> [O].[ValueInUse]))

END

--------
--Capture the capture dates into #temp table1
SELECT  DISTINCT
[CaptureDate]
INTO #CapturedDates
FROM [dbo].[ConfigData]
ORDER BY [CaptureDate]

    --Create temp table2
CREATE TABLE #CapturedDates2 (
         ServerName NVARCHAR(20)
        ,[CaptureDate] DATETIMEOFFSET
        ,Name NVARCHAR(1000)
        ,[OlderValue] INT
        ,[OlderValueInUse] INT
        ,[RecentValue] INT
        ,RecentValueInUse INT
)

------Inserting into Temp2 gin=ving error message as below

DECLARE @MINCapturedDate DATETIME
DECLARE @MAXCapturedDate DATETIME

SELECT @MINCapturedDate =(SELECT CAST(MIN([CaptureDate]) AS DATETIME) FROM #CapturedDates)
SELECT @MAXCapturedDate =(SELECT CAST(MAX([CaptureDate]) AS DATETIME) FROM #CapturedDates)

INSERT INTO #CapturedDates2
EXEC SYSDBA.dbo.usp_SysConfigReport1 @MINCapturedDate ,@MAXCapturedDate

But it gives me the following error message:

  Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19
  Implicit conversion from data type sql_variant to int is not allowed.
  Use the CONVERT function to run this query.

EDIT: Include definition of ConfigData

CREATE TABLE [dbo].[ConfigData]
   ( [ConfigurationID] [INT] NOT NULL,
     [Name] [NVARCHAR](35) NOT NULL,
     [Value] [SQL_VARIANT] NULL,
     [ValueInUse] [SQL_VARIANT] NULL,
     [CaptureDate] [DATETIME] NULL ) ON [PRIMARY]


texts:
I have a table with payment informations, like this:

ID COSTUMER_ID DATEPAID     AMOUNT
1    1        2012-01-01      50
2    1        2012-02-01      100
3    1        2012-03-01      70
4    2        2012-03-01     6000
4    2        2012-09-01     3000
4    2        2014-04-01     8000
5    3        2013-03-01     9000
6    3        2013-08-01     3000

etc.
I need to know wich costumers paid more than 10k in a 12 months period. (in the exemple, only costumer 3 fits). Don't mind when, the only criteria is SUM(AMOUNT) > 10000 in any 12 consecutive months combination.

But i'm stuck on it. Any ideas?
I'm using SQL Server 2012.


texts:
at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could be look like:

ID | Name       | N    | M    | Value
------------------------------------------------
1  | Customer   | NULL | NULL | NULL
2  | LastName   | NULL | NULL | NULL
3  | FirstName  | NULL | NULL | NULL
4  | NULL       | 1    | 2    | Thomson
5  | NULL       | 1    | 3    | Steve
6  | House      | NULL | NULL | NULL
7  | Color      | NULL | NULL | Blue
8  | Color      | NULL | NULL | Red
9  | Blue House | 6    | 7    | NULL
10 | Red House  | 6    | 8    | NULL

Make it sense to design a database in this kind or should it be "classic"?

Thanks for any response.


texts: Determine Index Compression Candidates Online

texts:
By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression:

ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE;
SELECT Opt_Cmpr_PctSave, Opt_Cmpr_Count FROM Index_Stats;

The problem is that when OFFLINE is changed to ONLINE the Index_Stats view does not get populated.  Is there an online way to determine the benefit of compressing an index and/or the number of columns that will produce optimal compression?

Update:

http://jonathanlewis.wordpress.com/index-definitions/ indicates that if Distinct_Keys from DBA_Indexes is "a lot smaller" than num_rows then the index is a good candidate for compression.  This helps some, but isn't definitive and doesn't help determine the number of columns.  He does give some guidelines for that, but nothing that can be determined programatically without a bunch of dynamic SQL.


texts: How to find the 'Instance ID' for PowerShell Invoke-TroubleshootingPack?

texts:
I was doing a PowerShell training, that said use the PowerShell help to run a "Networking troubleshooting pack"

I found, ran and read

Get-Help Get-TroubleshootingPack -full
Get-Help Invoke-TroubleshootingPack -full
Get-Help about_Remote_Troubleshooting -full

Thinking I had it mostly figured out I ran

PS C:\> Get-TroubleshootingPack -Path 'C:\Windows\diagnostics\system\Networking' | Invoke-TroubleshootingPack

And that gives me

Starting network diagnostics...

Instance ID
Not to be specified outside of MSDT application.
:

So I enter the Instance

MSSQLSERVER

And that gets me this error

Invoke-TroubleshootingPack : An error occurred when running a PowerShell script. (Exception from HRESULT:
0x803C0101)
At line:1 char:76
+ Get-TroubleshootingPack -Path "C:\Windows\diagnostics\system\Networking" | Invok ...
+                                                                            ~~~~~
    + CategoryInfo          : InvalidArgument: (Microsoft.Windows.Diagnosis.DiagPack:DiagPack) [Invoke-Trou
   bleshootingPack], COMException
    + FullyQualifiedErrorId : PackageDiagnoseFailed,Microsoft.Windows.Diagnosis.Commands.InvokeTroubleshoot
   ingPack

I try several options for the Instance ID: Server Name, IP, 0, 1. They all give the same error

Then I go looking for a way to identify the Instance ID, I find the two questions below but they don't give me anything to get around the error.

SQL Server - difference between instance name and instance id
SQL Server Instance ID

I search some more and found This Blog which is giving step by step to do everything I already figured out.  It shows the place to enter the Instance ID. But gives no clue what the value should be.  More searching just, leads to more frustration.


texts: How to get SHOW CREATE TABLE without table borders made of dashes?


texts:
how to convert a bytea  column to text in PostgreSQL so that I can read the column properly in PGADMIN?
I have the following SQL query in the PGADMIN's query editor:
SELECT event_type, created_at, encode(metadata::bytea, 'escape')
FROM public.events
ORDER BY created_at DESC
LIMIT 100

However, it produces an encoded column with each records more or less ressembling the following output:
\203t\00000some_textd\000some_other_textd\0000

How can I get rid of this encoded, so that I only see the original value of the column, in the text format:
some_text some_data

What I have also tried:
SELECT event_id, event_type, created_at, decode((encode(metadata, 'escape')::text), 'escape')
FROM public.events
ORDER BY created_at DESC
LIMIT 100

But in the above case, the query returns a decode column of type bytea and I only see the field [binary data] for each record of the column.
I have also tried the first two answers mentioned here without success and can't properly translate the last answer to my query.


texts:
I am facing the following challenge.
I want to have a central MySql server having all the data and several satellites servers that hold part of the data.

Satellites servers will hold some original data which should be inserted at the central server. Something like this:

For other tables I need to have a two way sync. Something like this:

Of course any time I want to be able to add another server(station 4).
   Also because network is not 100% guaranteed I need to have asynchronous communication.
I know that I could make my services that could do the job I am just looking if there is a ready solution.
I have found that for MS-SQL they suggest to use the service broker, but I need something for mySQL, if is a tool that can do the job


texts: MS SQL -Date and Time Style conversion problem

texts:
I need to convert Date and Time Style from USA format to German and than cast that into int data type.
So for variable in case below this works fine if I want to display it in Standard 112 (ISO)

DECLARE @LoopDate datetime
SET @LoopDate = '12/23/2015'
SELECT @LoopDate AS Original,
CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) AS int;

But when I put 104 which suppose to gives me error:

  Conversion failed when converting the varchar value '23.10.2015' to
  data type int.

It looks like conversion is actually working but casting into int is not.


texts: MySQL Workbench: How to detach reverse-engineered EER diagram from existing database?

texts:
MySQL Workbench provides the option to 'Create EER Model From Existing Database'. Trying to remove a table from the resulting diagram leads to a dialog asking:

  Should corresponding database objects be deleted with the figures?

As the database must be left untouched in any case, my question is now: How to safely detach the EER model / diagram from the underlying database?


texts:
This question has been moved from Game Development Stackoverflow

I am trying to create a new type of MMORTS game.

Current Situation

Everyday each user deletes and creates :

1,500 rows of data in diff. tables but in 1 database (all tables data are inter-related to data of other tables)
Each Row estimated size : 1KB
Max user per database: 100,000 users
Total Space Consumed everyday : 0.15 TB

Actual Users in the Game : 10,000,000 users (split in different databases)

Problem

According to me if i delete a row in SQL it is not being deleted permanently and will still consume the data in the database. If this happens, then in near future all my server are likely to crash.

Can anyone tell me if i am going wrong anywhere or suggest me any solution to this problem? I know this question cannot be understood easily so please feel free to ask any doubts you have regarding this question.


texts:
I've got a table with multiple columns, APGRPID, VIRTKEY, LFDNR and PARAMETERLIST. I want to change a part of some strings in the PARAMETERLIST column.
In the PARAMETERLIST I want to replace the "value" for the PATH= setting.
Table:

APGRPID
VIRTKEY
LFDNR
PARAMETERLIST

1091.000000
121.000000
1
KF=138;SCANANDSEND=TRUE;ENDBARCODE=999999999999;PATH=K:\SSTEST;BARCODELENGTH=8

2092.000000
130.000000
8
KF=138;  PATH=s:\

2101.000000
114.000000
3
DESIGNATION=JOB;  KF=81;  PATH=w:;  SENDALLDATA=TRUE

2102.000000
116.000000
3
KF=76;  PATH=\srv05\L3OERAPMFC;  SENDALLDATA=TRUE

2303.000000
114.000000
2
KF=386;  PATH=W:;  SENDALLDATA=TRUE

The main problem I have is the randomness of the position of the PATH= parameter in the PARAMETERLIST column and the variability of the number of elements in the column if I try to CROSS APPLY a STRING_SPLIT() or anything.
Platform: Microsoft SQL Server Enterprise (Ver. 14.0.1000.169)


texts: Failover multiple Basic Availability Groups

texts:
We are planning to create multiple BAGs for SQL Server 2016 Standard Edition. My question is, should I need to same listener for all BAG?

Let's say we have 3-4 AG's i.e AG1, AG2, AG3 and so on.

If one of our AG1 failed, I need to failover all AGs to other node not just one.
How can i achieve the same using script or job?


texts: Can I activate PITR after the DB has been used

texts:
My Postgres-Server is running for quite a while and contains probably 4-5 GB of data.

Backups are done using dumps, and this works ok. Though not most efficient, it just works.

Due to future growth I like to activate PITR backups (like RMAN in Oracle).

Is that possible and/or recommended? Or would it be better for me to recreate the DB with the correct settings upfront?


texts: Building Oracle XE docker image in Windows 10 Pro

texts:
I'm trying to install Oracle XE 18c in a docker container in Windows 10. I have docker already installed, and I need to build the image as explained here.

I cloned the git repository, but I'm stuck on this step, as what I cloned are Linux commands and not Windows.

After cloning with:

git clone https://github.com/oracle/docker-images.git

If I cd to docker-images\OracleDatabase\SingleInstance\dockerfiles\18.4.0 to build the image I see the following sh comands, instead of exe or bat:

12/28/2019  01:36 PM             1,137 checkDBStatus.sh
12/28/2019  01:36 PM               905 checkSpace.sh
12/28/2019  01:36 PM                75 Checksum.xe
12/28/2019  01:36 PM             2,752 Dockerfile.xe
12/28/2019  01:36 PM               533 oracle-xe-18c.conf
12/28/2019  01:36 PM             6,132 runOracle.sh
12/28/2019  01:36 PM               775 setPassword.sh

How to build this image in Windows?


texts: Is it correct to dynamically add rows to a table in sqlite, or should I use csv? (I'm working with objective c.)

texts:
Just like when adding a new contact in the ios contact app, you can add multiple phone numbers, and it doesn't know how many there will be, so therefore they don't know how many rows they should initially add in the table.
So how does that work, do they dynamically add rows to the table, or do they use csv?


texts: How do I measure the overhead of a Postgres extended statistics object?

texts:
Some solutions to poor query performance suggest creating extended statistics objects to help the query planner better understand the shape of the data. Because these statistics objects are not created by default, I assume that they have some non-neglible overhead.
What kinds of overhead are present, what statistics object parameters factor in to the overhead, and can I quantitatively measure what the overhead of a specific statistics object is?
Some possible kinds of overhead I've thought of:

Increased CPU usage

when inserting
when querying
when analyzing

Increased memory usage
Increased disk usage


texts: Secure postgres 9.5 database packed on Windows desktop application

texts:
My desktop app includes a postgres database and a front app. I need the db to be accessed only by my front app. The credentials of this user are going to reside in the front app binaries.

I realized it's possible for anyone to use createuser.exe with -s to create a superuser. After that, it would be enough to create a db with createdb.exe, connect to it, and change ownership of my db to the new user, or do whatever.

The only effective way to avoid illegal access I know is to change template0 and template1 names, so that createdb.exe throws 'template0 not found'. That way anyone could create superusers, but no db's to connect to.

In addition, it would be needed to drop postgres database and postgres role, and execute these sentences:

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;

REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;

REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;

Can I do something else?


texts: How to read configuration option into variable?

texts:
I want to read configuration option as TEXT but SHOW statement is strange because it does not return any rows.

create or replace function get_param()
returns text as $$
begin
  return query show custom.param; -- does not work, because show does not return setof
end;
$$ language plpgsql;

set custom.param = 'custom value';
select get_param() as param;

Is there any other way of doing this?

Actually I found answer in a question asked by me :) set session - custom variable to store user id :

SELECT current_setting('custom.param');


texts:
I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the results ordered by the best match of asked query.

I'll try to describe that by pseudo code

select
*
from
articles
where item_nale like '%'+@user_input +'%'
order by "BEST MATCH"


texts: How to access the text definition of sql indexes

texts:
I am able to access the text definition of stored procedures, views, triggers and functions from sys.sql_modules. I want to get the text definition of indexes.

How can I get the text definition of indexes in SQL Server?


texts: PostgreSQL count 0 using WHEN and GROUP BY with more than one column

texts:
I am trying to make a new table that shows the number of Active Credit Cards each customer has in my dataset using:

SELECT
    sk_id_curr,
    credit_type,
    credit_active,
    COUNT(credit_type)
FROM
    BUREAU
WHERE
    credit_active='Active' AND credit_type='Credit card'
GROUP BY
    sk_id_curr,
    credit_type,
    credit_active

The problem is that it removes ID's where there are no Active Credit Cards. I want it to return ID's with no credit cards with count 0. How would I do this?

Ex:

Original Data

-------------------------------------------------
id              type                   activity
--------------------------------------------------
10001        Consumer Credit           Active
10002         Credit Card               Active
10002        Credit Card               Active
10003         Credit Card              Closed

Current result

-----------------------------------------------------------------------------
id,                type,              activity,                  count
------------------------------------------------------------------------------
10002         Credit Card               Active                2

desired result

-----------------------------------------------------------------------------
id                type                 activity            count
------------------------------------------------------------------------------
10001         Credit Card              Active               0
10002         Credit Card               Active              2
10003         Credit Card               Active               0


texts: Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

texts:
This morning i was woken up by a transaction log full alert on one of our database.   This server is an alwayson cluster and also a transactional replication subscriber.
I checked log_reuse_wait_desc and it showed logbackup.  Someone had accidentally disabled the logbackup jobs 4 days earlier, I re-enabled the log backup job and the log got cleared.  Since it was 4am I thought I will go to office later that morning and shirnk the log as it has grown to 400GB.

10AM-  Im in office and I check the log usage before shrinking and it was around 16%.  I was surprised and check the log_reuse_wait_desc, which showed replication.  I was confused cause this was a replication subscriber.  We then saw that the db was enabled for CDC and thought that might be the cause, so disabled CDC and now the log_reuse_wait_desc shows AVAILABILITY_REPLICA.

The log usage meanwhile still steadily growing and its at 17% now.  I check the alwayson dashboard and check the sent and redo queue and both are virtually zero.  I am not sure why the log reuse is showing as   AVAILABILITY_REPLICA and unable to clear the log.

Any idea why this is happening?


texts: SQL Server Error 18483 - When Setting Up SQL Distribution

texts:
I'm trying to synchronize a couple tables in one SQL Server 2000 database into another SQL Server 2005 database using replication.

When I am attempting to set up my SQL Server as a distributor, I'm getting the following error:

  SQL Server Enterprise Manager could not configure 'VIPER' as the Distributor for 'VIPER' .
  Error 18483: Could not connect to server 'VIPER' because 'distributor_admin' is not defined as a remote login at the server.

In doing research, I found several sources and articles that show that my machine name, and the actual name of the server are not the same.  When I check the sysservers view in master, I see that:

0   1089    VMSSERV SQL Server  SQLOLEDB    VMSSERV         4/23/2008 9:50:41 AM
1   1601    repl_distributor    SQL Server  SQLOLEDB    VIPER           10/12/2011 1:23:18 PM
2   1184    CHARLIE SQL Server  SQLOLEDB    CHARLIE         2/18/2011 1:32:14 PM

The first line is the original database that was setup, and that I intend on using. You can see (kinda) that the original name is VMSSERV and the actual machine name is VIPER.

So, from the results of my extensive web search, I ran this in Query Analyzer:

USE MASTER
GO
SELECT @@SERVERNAME, SERVERPROPERTY('ServerName')

This returns:

VMSSERV VIPER

Most of my web searches show that I should refer to http://support.microsoft.com/kb/818334, which instructs to use:

-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername  varchar(100),
    @servername             varchar(100)

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'

And finally my actual question...

Would my correct format to fixing this be on the last line of the above sequence:

EXEC sp_addserver @server = @serverproperty_servername, @local = 'VIPER'

?

Once I perform this task, if there are further errors, I will reply back.  Hopefully this ONE item will fix my issue...


texts: Would using varchar(5000) be bad compared to varchar(255)?

texts: Since varchar anyways allocate space dynamically, my question is whether using varchar(255) be more efficient or save more space compared to using varchar(5000).  If yes, why?


texts: query to determine forceencryption state

texts:
I'm trying to confirm whether or not force encryption is currently set and/or if the current connection is secure or not.

Is there a SQL query I can run that will give me that information or do I need to log onto the machine and interrogate it from the OS?


texts: Should partitioning increase performance of indexed accesses and updates?

texts:
I often hear that partitioning huge tables should significantly increase query speed and insert/update speed, because indexes are proportionally smaller.
I'm struggling to understand why that should be the case.
In my understanding operations on an index should be of log(N) complexity, so even if we partition a huge table into 100 smaller items then we just partitioned a single index into 100 smaller ones.
If queries frequently access items from the entire dataset, then these 100 indexes will compete for processor cache anyway, so I don't see why database will be hitting the disk less often.
If we are usually only requesting some portion of the data - for example if we have a table with timestamped data and are more often interested in latest data - then the B-tree node count that we have to traverse will be 1 or 2 less. But in un-partitioned table scenario top-most nodes of B-tree will be cached anyway, so the fact that B-tree is slightly higher shouldn't have significant impact on performance.
Variation on previous scenario: if we are commonly accessing ranges of data that are on the same "axis" as the partition key - again, if we have a table paritioned by timestamp and run reports on date ranges - then partitions will provide natural clustering for the data, thus reducing disk accesses and improving query performance. But I don't see how this will improve insert/update performance? And why should it improve performance on queries that do not filter by partition key?
I realize that partitions are useful in some other cases (dropping old data, lock contention with many users, backups, aforementioned clustering, etc) - but I commonly hear that query or update performance should increase as well, hence this question.
I'm most familiar with PostgreSQL, but I suspect the underlying concepts are similar between many relational databases.


texts: The CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backupbackup true or false?

texts:
I have a question that related to SQL Server Checkpoint Log Sequence Number.

I've read some articles and they say that the CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup, but I can't find the confirmation on the https://technet.microsoft.com page.

I have a doubt about it because I read that the only possible way to anchor LOG backup to DIFF is: DIFF.DatabaseBackupLSN =< LOG.DatabaseBackupLSN, DIFF.LastLSN < LOG.LastLSN

Also, I have made the following test and it seems that the CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup.

So is it correct?


texts: Unexpectively slow data loading into Oracle

texts:
Big amount (4.000.000+) of files (1-4Mb each) are loading into Oracle 10g for about several days.
Table is allocated in big tablespace, which was created on special volume (labeled BIG_VOL) over hardware RAID (Adaptec 3805 , 8 HDD x 2Tb, raid level 6).

RedoLogs was moved on two another SSD drives. No archivelog.
There are only DBF and control files on BIG_VOL.

Transaction closed (by COMMIT) after every loaded file.

Perfomance Monitor shows ~6-9Mb per second on writing for this BIG_VOL !
Simple copy any big file shows about 80+Mb per second!

Can I do something to improve speed?


texts: As an application what are differences between sqlplus.exe and sqlplusw.exe?

texts: Currently i am using Oracle 10g and i can use both sqlplus and sqlplusw. But is there any difference in using them. And, is any of the is deprecated in newer versions?


texts: Calculate row value based on previous and actual row values

texts:
Hi everyone and thanks for your help.
I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double)

I want to calculate the balance following these rules:

The first row balance (chronologically) = debit - credit and for the rest of the rows

current row balance = chronologically previous row balance + current row debit - current row credit

As you can see on the picture above the rows are not arranged by date and that's why I used the word chronologically twice to emphasize on the importance of the stmnt_date value.

Thank you very much for your help.


texts: How do I get the remote hostname of the server?

texts:
Can I use the hostname of the server in a PostgreSQL SELECT statement? The following may be close but apparently does not quite work:

\set x \! hostname
select :x;

This is for PostgreSQL 9.6 on Debian.


texts: "Regular Expression" to find doubled words in a string

texts:
I have a table with below structure:
create table EMPLOYEE_INFO
( e_number            NUMBER,
  e_name              VARCHAR2(50),
  e_personal_interest VARCHAR2(2000))

Some example data :
e_number  |  e_name |  e_personal_interest
------------------------------------------
  1       |   a     |  This person likes likes to play piano piano
  2       |   b     |  this this person likes chila chila
  3       |   c     |  person apears apears to be 10 years old
  4       |   d     |  she is is 10 years old old

As you can see in e_personal_interest column of the table , some words are repeated twice (for instance for the first record we have 'piano piano'), what I want to do is to find those records that have this problem . I used powerful Regular Expression and what I've written so far is below query :
select t.e_number,
       t.e_name,
       regexp_substr(t.e_personal_interest,
                     '(^|[[:space:][:punct:]]+)([[:alpha:]]+)' ||
                     '[[:space:][:punct:]]+\2' ||
                     '($|[[:space:][:punct:]]+)') doubled_word
  from employee_info t
 where regexp_like(e_personal_interest,
                   '(^|[[:space:][:punct:]]+)([[:alpha:]]+)' ||
                   '[[:space:][:punct:]]+\2' || '($|[[:space:][:punct:]]+)');

The problem with above query is that it just shows the first set of words that are repeated after each other for example it just shows "this this" for the second record . I want to have all sets of words that are repeated after each other for all records, "this this" and also "chila chila" for the second record for instance.
I was wondering if you could help me with this.
thanks in advance.


texts: Building A SQL table horizontally VS vertically

texts:
What is a better design principle when designing SQL Server tables.

One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows)

I have a situation when there are normally a number of items, say 2- 12 that are generally related to one another.
Just to keep it simple, they would have the same values in 4 of their columns but 3 would be different.

Would it be better to now add the 12 entries in their own row, or would it be better to say add additional columns that would help me combine all the entries into one row, but be able to identify which one was which.

I dont think I have explained the situation well enough to get a specific answer relating to my situation.

But I am asking in general  which is better practice?


texts:
Suppose I have the following configuration for mysql 5.6 replication.
Nodes
A
B
C
D
Nodes A,B,C have wsrep_cluster_address set to respective ip's of nodes A,B and C. Then node D has wsrep_cluster_address set to respective ip's of nodes A,B,C and D.
Then when data is written to nodes A,B,C it is replicated to A,B,C,D, but when data write happens on D - it won't get replicated to A,B,C - is that right ?
Also, what happens when data is written on D and same record is changed on either A,B or C - does it get overwritten based on time of the actual change ?
Really the goal of D is just to have a constantly replicated copy in another zone in case a disaster happens.


texts: Error 2003: Setting up MySQL Replication

texts:
Trying to setup MySQL Replication using this guide here : http://wpguru.co.uk/2013/05/how-to-setup-mysql-masterslave-replication-with-existing-data/ I was able to do this on two random VMs running the same version of MySQL and it worked perfectly fine. I am trying to do this in a production environment and it doesn't seem to be working. The Slave is unable to connect to the master even though I can connect just fine using the mysql client.

140821 11:53:11 [ERROR] Slave I/O: error connecting to master 'repl@<host-ip>:7' - retry-time: 3306  retries: 86400, Error_code: 2003

The Slave is running 5.5.38-0ubuntu0.14.04.1 and the Master Server is running 5.5.34-0ubuntu0.12.04.1-log. I can telnet from the slave to the master. I can also connect using mysql -u repl -p -h <master-host-ip> just fine. I am currently stuck at this point and out of ideas.


texts:
I'm a beginner in SQL. I hope my question is not too stupid.

I'm trying to write a query in MS Query and want to see the results in Excel.
The problem is that MS Query doesn't accept my code. I get the message:

  table '(select' couldn't be added.

Here's the code:

SELECT
    T1.Jahr,
        T1.Monat,
        T1.Land_ID,
        T1.Kanal,
        T1.Items_per_HC,
        T1.Planstand,
        T1.Zeitraum,
        T2.Zeitraum,
        T2.LAND_ID,
        T2.KANAL,
        T2.IST_ITEMS_PER_HC_EXCL_EASY
FROM
(SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal,
        tPLAN.Items_per_HC, tPLAN.Planstand, tZ.Zeitraum
    FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr
    AND tPLAN.Monat = tZ.Monat) as T1
INNER JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2
  ON (T1.Zeitraum=T2.Zeitraum
  AND T1.Land_ID=T2.LAND_ID
  AND T1.Kanal=T2.KANAL)

What is wrong?

I have to see the content of table t_Plan_Items_per_HC and table a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC in only one table. They should be joined by Land_ID, Zeitraum, and KANAL.

UPDATE: Now I tried the same code in SQL Developer and it works. It seems to be an MS Query-specific problem. Unfortunately I have to solve this problem in MS Query so I still need help.


texts: How to write a query, with multiple foreign key in mysql

texts:
I am working on a simple PHP and MySQL college level clinic management system. There 4 main table which relation with foreign key. I'm little bit confusing multiple foreign key in table .. what would be MySQL query according tables specially Fee Transaction table ... help will appreciated...thanks

I did try this:

CREATE TABLE patient_appointment
(
appointment_ID int NOT NULL,
Date int NOT NULL,
time int,
PRIMARY KEY (appointment_ID),
CONSTRAINT fk_P_id FOREIGN KEY (patient_id)
REFERENCES patient(patient_id)
)

Double level . please check its right or wrong

CREATE TABLE patient_treatment
(
patient_treatment_no int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (patient_treatment_no),
CONSTRAINT fk_P_id FOREIGN KEY (Patient_id)
REFERENCES Patient(Patient_ID),
CONSTRAINT fk_t_id FOREIGN KEY (treatment_id)
REFERENCES treatment(treatment_id)
)

CREATE TABLE IF NOT EXISTS `patient` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `fn` varchar(100) NOT NULL,
  `ln` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `sex` varchar(20) NOT NULL,
  `DOB` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

=====================

CREATE TABLE IF NOT EXISTS `patient_treatment` (
  `p_t_no` int(11) NOT NULL AUTO_INCREMENT,
  `treatment_Fee` varchar(200) NOT NULL,
  PRIMARY KEY (`p_t_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `treatments` (
  `t-id` int(111) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `fee` varchar(20) NOT NULL,
  PRIMARY KEY (`t-id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Fee Transaction
Contains record of all the fee transactions carried out for each appointment by each patient.
-   Transaction_ID
-   Appointment_ID (Foreign Key)
-   Patient_ID (Foreign Key)
-   Treatment_ID (Foreign Key)
-   Treatment_Fee**

I don't know how to make table... help me please


texts: Improving `GROUP BY` query performance in sqlite3

texts:
I have a little web-application that is using sqlite3 as it's DB (the db is fairly small).

Right now, I am generating some content to display using the following query:

SELECT dbId,
        dlState,
        retreivalTime,
        seriesName,
        <snip irrelevant columns>
        FROM DataItems
        GROUP BY seriesName
        ORDER BY retreivalTime DESC
        LIMIT ?
        OFFSET ?;

Where limit is typically ~200, and offset is 0 (they drive a pagination mechanism).

Anyways, right now, this one query is completely killing my performance. It takes approximately 800 milliseconds to execute on a table with ~67K rows.

I have indexes on both seriesName and retreivalTime.

sqlite> SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;
<snip irrelevant indexes>
DataItems_seriesName_index
DataItems_time_index           // This is the index on retreivalTime. Yeah, it's poorly named

However, EXPLAIN QUERY PLAN seems to indicate they're not being used:

sqlite> EXPLAIN QUERY PLAN SELECT dbId,
                                  dlState,
                                  retreivalTime,
                                  seriesName
                                  FROM
                                      DataItems
                                  GROUP BY
                                      seriesName
                                  ORDER BY
                                      retreivalTime
                                  DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

The index on seriesName is COLLATE NOCASE, if that's relevant.

If I drop the GROUP BY, it behaves as expected:

sqlite> EXPLAIN QUERY PLAN SELECT dbId, dlState, retreivalTime, seriesName FROM DataItems ORDER BY retreivalTime DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems USING INDEX DataItems_time_index

Basically, my naive assumption would be that the best way to perform this query would be to walk backwards from latest value in retreivalTime, and every time a new value for seriesName is seen, append it to a temporary list, and finally return that value.  That would have somewhat poor performance for cases where OFFSET is large, but that happens very rarely in this application.

How can I optimize this query? I can provide the raw query operations if needed.

Insert performance is not critical here, so if I need to create an additional index or two, that's fine.

My current thoughts are a commit-hook that updates a separate table that is used to track only unique items, but that seems like overkill.


texts: Is there any way to find out when a table was created in PostgreSQL or Greenplum, other than finding the underlying table and looking at the OS creation timestamp?


texts: What will be the relation between Employees and Players, given that some players are also Employees?

texts:
I have a requirement where I need to store information on Employees in a company that owns(?) a sports team and also store information on Players from other teams that are not Employees of this company. The Players in the team(i.e owned by the company) are also considered Employees hired by the company.
For the design, what I have done is create two Entities called Employees and Players and have a Player id key as FK in the Employees Entity. The Employee Entity also has Employees that are just employees and not players. With the player id fk, Employees that are Players will have a player id fk, while other employees will not. That way I have information about Employees, player or just normal employees, in Employee entity. And in the Players entity I only have information specific to all the players hired by the company or just other players from other teams(not owned by the company). Is this the right way to go? I haven't shown the subclass/subtype relation between Employees and Players entity because not every player in the Players entity is an Employee. Am I in the right direction?
I know this is not the very best explanation I guess but please do ask for any clarification. Thank you


texts:
We have a database that is 3TB+ in size, and don't have enough space to restore it.

However, we want to retrieve code (stored procs) from a 15 day old backup.

Is is possible to restore the structure only from an Oracle backup?


texts: Always On Availability Groups as a Secondary Read Only Database

texts:
I am working on setting up a secondary copy of our prod DB for Data Warehousing and even something we can possibly use to offload other processes and reporting on. Basically just a read only copy of prod. Issues is, I am running into options that breaks our point in time backup strategies and that can reliably handle 20 TB of data.

After reading this post, Always On Availability Groups looks promising. But I have a few questions and I am new to DBA and most of Microsoft's documentation on the subject is another language to me at this point.

Does Always On Availability Groups mean the database will basically be mirrored and that I can use that server to offload the heavy reporting to?
Does that break point in time transaction logs backup of the prod server?
Can this cause a strain on the prod server?
Will this cause prod traffic to be redirected to this server? I do not want this to become HA DB server, just want a read only copy of a few prod DBs.


texts:
Say, we have a query like this:

select a.*,b.*
from
a join b
on a.col1=b.col1
and len(a.col1)=10

Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len(a.col1)=10.

But while going through another example, I could see both the probe and the residual to be the same column. Below is an elaboration on what I am trying to say:

Query:

select *
from T1 join T2 on T1.a = T2.a

Execution plan, with probe and residual highlighted:

Test data:

create table T1 (a int, b int, x char(200))
create table T2 (a int, b int, x char(200))

set nocount on
declare @i int
set @i = 0
while @i < 1000
  begin
      insert T1 values (@i * 2, @i * 5, @i)
    set @i = @i + 1
  end

declare @i int
set @i = 0
while @i < 10000
  begin
    insert T2 values (@i * 3, @i * 7, @i)
    set @i = @i + 1
  end

Question:

How can a probe and a residual be the same column? Why can't SQL Server use the probe column only? Why does it have to use the same column as a residual to filter rows again?

References for test data:

Hash Join (Craig Freedman's SQL Server Blog)


texts: PostgreSQL vacuum duration on a big table

texts: I have a very big table in my postgreSQL (over 300 million rows) that has never been vacuumed. Yesterday I tried a vacuum and analyze (not a full one) and it took about 7 hours to complete. Will it take 7 hours if I do it every day, or will the duration become smaller due to frequent maintenance?


texts: PostgreSQL:- How to continue execution even INSERT INTO SELECT FROM fails and how to work with refcursor as out param with other output params

texts:
In this post i am asking 2 questions (with my limited knowledge of PostgreSQL and PLPGSQL)

I have a function inside which there is a query that inserts records to a table using INSERT INTO SELECT FROM. This query is failing the whole function when there is no record returned from the SELECT. Just like SQL Server I don’t want this query to fail, even if there is no record, I want the execution to be continued. What should I do to achieve such functionality.
I have a refcursor to be returned as output however along with it i have few more params (2 or 3) to be returned as output such as bigint or char type. This refcursor would be getting data from a dynamic SQL which is getting formed inside function. So is my function inputs and outputs are in order and whether this will work?

e.g.
CREATE OR REPLACE FUNCTION public.xyz(input1 INT, out1 OUT BIGINT, P_refcursor out REFCURSOR) RETURNS RECORD
AS
$body$
BEGIN

<some code>

/*This step fails the whole function and nothing executes after that*/
INSERT INTO tbl
SELECT * FROM
<joins>
WEHRE <condition>;

<some code>

SELECT aaa INTO out1 FROM <somefunction> ;

/*refcursor to be returned to the calling mechanism*/
open p_refcursor for EXECUTE sql_stmt;

exception
        when others then
          Raise Notice '% - % ', SQLSTATE, SQLERRM;
END;
$body$
Language PLPGSQL VOLATILE;

EDIT-1: Below could be answer to Q2... Answer to Q1 is still pending...
I figured out that there could be different options to handle situation mentioned in Q2.

Write these singular (non-cursor) params into some table and then access from outside the function to know about last execution value
Include these other params as one of the column in REFCURSOR and get their values in the output when you access it
What i did: Removed all the out params, converted the RETURN type of function as SETOF RECORD (my output was dynamic hence couldn't convert it to TABLE) and included the other singular out params (non-cursor) in the RECORD when forming my dynamic query. I was then able to access these out params when I called the function with field list (as my Return Type was SETOF RECORD). The downside of this would be these out param values would be repeated for all rows of the output and you wont have them when dynamic query returns no rows.


texts: Why is SYSDATETIMEOFFSET precision decreased on Azure SQL?

texts:
SELECT SYSDATETIMEOFFSET()

When I run the query on a local SQL Server 2019 instance, I get values like this:
2021-03-24 14:32:32.5811589 -05:00
2021-03-24 14:32:37.8169216 -05:00
2021-03-24 14:32:42.6465055 -05:00

When I run the query on an Azure SQL database, I get values like this:
2021-03-24 19:31:04.6733333 +00:00
2021-03-24 19:31:15.8300000 +00:00
2021-03-24 19:31:20.4866666 +00:00

In the latter, the last 5 digits are always repeating values. The value is less precise (or do I mean less accurate?) when the function runs on Azure SQL. Why is the behavior different?
The documentation for SYSDATETIMEOFFSET says:

SQL Server obtains the date and time values by using the
GetSystemTimeAsFileTime() Windows API. The accuracy depends on the
computer hardware and version of Windows on which the instance of SQL
Server is running. The precision of this API is fixed at 100
nanoseconds. The accuracy can be determined by using the
GetSystemTimeAdjustment() Windows API.

But if the hardware matters, I don't expect I'll be able to know that information for Azure SQL.
I am working around this issue by instead using this sort of query to get a more precise datetimeoffset value:
SELECT (SYSUTCDATETIME() AT TIME ZONE 'UTC')

For some reason SYSUTCDATETIME doesn't suffer from the "last 5 digits repeat" issue when I run it on Azure SQL. The documentation for SYSUTCDATETIME does say:

SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision
than GETDATE and GETUTCDATE.

but it doesn't mention having more precision than SYSDATETIMEOFFSET.
I just want to understand this issue better and know what I precision I can rely upon from SYSDATETIMEOFFSET and SYSUTCDATETIME on Azure SQL.


texts: Oracle: Full database (user) restore requires drop?

texts:
In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc.

We are using expdp/impdp to perform our backup/restores.

For back-ups we run...

expdp 'sys/password@orcl as sysdba' schemas=CurrentUser directory=DirLabel Dumpfile=MyDatabase.dmp logfile=MyDatabase.log

For restores we have been dropping the user (database) manually, and then running

impdp 'sys/password@orcl as sysdba' directory=DirLabel Dumpfile=MyOldDatabase.dmp logfile=MyRestore.log remap_schema=OLDUser:CurrentUser

We are now trying to automate this process and have a question.

Do we have to drop the current user (database) prior to the restore or is there some option/flag on impdb that can be used to force a complete restore?  I looked at TABLE_EXISTS_ACTION=REPLACE, but I noticed I still get the same "errors" during restore if I leave the option off the command-line.

Thoughts?


texts:
Our SQL is configured to use max of 704 threads,some times we get alerts stating only 10 threads are left,so i am not able to understand whether SQL keeps the thread open ,since again creating a new thread is expensive.

so my question is

How to know whether a thread is available for new request or currently busy with other requests.
I am trying to link task_address from sys.dm_exec_requests with sys.dm_os_tasks and worker_address like below

select * from sys.dm_exec_requests ec
join
sys.dm_os_tasks tsk
on tsk.task_state=ec.task_address
join
sys.dm_os_workers wrk
on wrk.worker_address=tsk.worker_address

I don't see any output,does this mean i can assume all threads are free

Currently below is the state of my worker threads,What does this suspended mean ?.I could see count more than 500 with suspended status.I don't see any blockings

4.Can i use below query to find that i have worker thread starvation

select status from sys.dm_Exec_requests

if status is pending,can i assume SQL is waiting for new worker threads

Currently I see one session,is having more than 250 rows in sysprocesses,when I query sys.dm_os_Waiting_tasks,I could see more than 2186 rows and 90% of them are for same session.So my question here is how can a query span these many threads

Will I get available worker count using below query,is this correct ?

select (
        select max_workers_count
        from sys.dm_os_sys_info
        ) - (
        select sum(active_workers_count)
        from sys.dm_os_Schedulers
        )


texts:
I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables:

CREATE TABLE `default_tb_envio` (
  `codigo` varchar(7) NOT NULL,
  `fecha` date NOT NULL,
  `id_modalidad` int(10) NOT NULL,
  `costo` decimal(28,6) NOT NULL,
  `id_agencia` int(10) DEFAULT NULL,
  `no_envio` int(10) NOT NULL,
  `id_forma_pago` int(10) DEFAULT NULL,
  `bl` text,
  `id_destino` int(10) DEFAULT NULL,
  `id_persona_envia` varchar(255) DEFAULT NULL,
  `id_persona_recibe` varchar(255) DEFAULT NULL,
  `id_organismo` int(10) DEFAULT '1',
   PRIMARY KEY (`codigo`),
   UNIQUE KEY `envio_pkey` (`codigo`),
   KEY `fki_` (`id_persona_envia`),
   KEY `fki_1` (`id_persona_recibe`),
   KEY `envio_modalidad_fkey` (`id_modalidad`),
   KEY `id_foma_pago_fkey` (`id_forma_pago`),
   KEY `id_organismo_fk` (`id_organismo`),
   KEY `ndestino_fkey` (`id_destino`),
   KEY `tb_envio_id_agencia_fkey` (`id_agencia`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `default_tb_persona` (
  `nombre_apellido` varchar(255) NOT NULL,
  `ci` varchar(15) NOT NULL,
  `telefono` varchar(255) DEFAULT NULL,
  `direccion` text,
  `correo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ci`),
  UNIQUE KEY `tb_persona_pkey` (`ci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm trying to get non equal or repeat results from default_tb_persona using a JOIN sentence, this is what I've made for that:

SELECT DISTINCT * FROM
  `default_tb_persona`
LEFT JOIN
  `default_tb_envio`
ON
  `default_tb_persona`.`ci` = `default_tb_envio`.`id_persona_envia`

But I'm getting repeated results. Is that query right? If I have two records pointing to the same record on tb_persona should the query return those two records? I've a test data if any can help me, just say me where to send and I will to solve this problem. Also it's possible to get only fields from default_tb_persona and not from both tables?


texts:
DECLARE @SubDesc1 nvarchar(100)
SET @SubDesc1 = null

I would like to do something like that.

Query 1

SELECT *
From Table1
where Category = 'Category1'
  AND [Description] ='ABC'
  AND SubDescription1 IS NULL
   OR SubDescription1 = @SubDesc1

Query 2

SELECT *
FROM Table1
WHERE Category = 'Category1'
  AND [Description] ='ABC'
  AND SubDescription1 = (Case WHEN @SubDesc1 IS NOT NULL
                              Then @SubDesc1
                              ELSE SubDescription1
                              END)

None of them solved if my @SubDesc1 = 'def'

However, it will get me both records where SubDescription1 Is null and SubDescription1 = 'def'

How can I do this?


texts: My node js server stops listening on mysql db

texts:
I implemented a listener on a table in a MySQL database with a MySQL event that is based on listening on binlog.

However, the problem is that after a while my node.js server stops listening on the database and I am getting this error when I check the db log errors:

  2018-04-20T10:34:43.347312Z 21979 [Note] Start binlog_dump to
  master_thread_id(21979) slave_server(12000), pos(, 453964)
  2018-04-20T10:34:43.349066Z 21978 [Note] Aborted connection 21978 to
  db: 'information_schema' user: '*****' host: '*****' (Got an error
  reading communication packets)

What could be the cause of this?


texts:
I have InnoDB assertion failure during the trx rollback

160316 10:47:31  InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo
160316 10:47:31  InnoDB: Assertion failure in thread 140611160659712 in file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.bof

I've managed to start MySQL with innodb_force_recovery = 3 (SRV_FORCE_NO_TRX_UNDO). Now I can see this transaction in INFORMATION_SCHEMA:

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 5ADBE4173
                 trx_state: RUNNING
               trx_started: 2016-03-16 11:16:08
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 1
       trx_mysql_thread_id: 0
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)

mysql>

It also appears in SHOW ENGINE InnoDB STATUS:

TRANSACTIONS
------------
Trx id counter 5ADBE4303
Purge done for trx's n:o < 0 undo n:o < 0
History list length 1897
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 56285, OS thread handle 0x7f31cdca4700, query id 174313 localhost root
show engine innodb status
---TRANSACTION 5ADBE4173, ACTIVE 590 sec recovered trx
, undo log entries 1
----------------------------
END OF INNODB MONITOR OUTPUT
============================

MySQL documentation states that "You cannot cancel transactions that are in the process of being rolled back."
So is there any way to know which tables it uses? And if it is, can I just drop the offensive tables and restart server back with innodb_force_shutdown = 0?


texts: CTE query doesn't print the right structure?

texts:
I have this table which represents hierarchy :

childID  parentID          NAME          siblingOrder
1          1               a               0
212        1               ab              1
112        1               ac              2
412        1               ad              3
-912       112             aca             0

The structure is:

a
+----ab
+----ac
     +---aca
+----ad

(The siblingOrdercolumn is for controlling the order of ab,ac,ad  )

I already have this solution which uses the  siblingOrder :

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)  AS VARCHAR(1024))  AS PATH
   FROM   @myTable
   WHERE   childID = parentID
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)AS VARCHAR(1024) )
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

So order by PATH actually do the job  :

The problem:

The problem is that I Must(!) put values in the siblingOrder in order for it to work !

Otherwise  , For example :

If I put 0 in all siblingOrder this is the result :

(yes,  now sorting by path  - doesn't work...)

I need  that aca will always be under ac

(The only reason I added the siblingOrder is to order siblings !) and I don't want to enforce adding siblingOrder when not needed

Question :

Is it possible to enhance the query so that siblingOrder will affect only to siblings ?

I mean , If I don't care about the order of the siblings ( by putting 0) , I still expect the aca to be under ac

Sqlonline : with siblingOrder

Sqlonline - without siblingOrder


texts: How full is my innodb shared table space?

texts:
How can I find out how much data is in the innodb shared table space.

I have a massive shared idb file but I suspect that it contains only about 350 Gigs of data. I need to know how full it is so I can prevent it from expanding as I have very little disk space left. Any ideas ?


texts: How to test permissions on an empty database?

texts:
It's usual in an organization I work in that I need to request a database creation to the DBA group (I am a developer). As such, they will also create a user for me to use and a database dedicated to my application.

When they do so, they ask which types of permissions and I answer.

When the work is done, they request that I test that everything is fine as to give my approval on the configuration. However, at this point, the database is completely empty. I can easily test for connectivity and access permissions, but how should I test my own read/write/execute permissions into a specific database, if it´s just been created and it is completely empty, using a non-admin user?

If the database wasn't empty I would try selecting data from a table and updating it.


texts: SQL Server Internal Inconsistency on GROUP BY

texts:
I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS.

Now I can select records from a table-valued function to retrieve sales records, but using a GROUP BY on the same data causes an "internal inconsistency" error.

Running DBCC CHECKDB returns zero inconsistency errors.

I have run everything I know to detect any torn pages, but have found none.

How do I find the torn page or force a rebuild?


texts:
Recently I started to get this error from time to time on my nightly SSIS jobs:
Message

  Executed as user: "my user name". Microsoft (R) SQL Server Execute Package Utility  Version 13.0.1601.5 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  00:01:01  Failed to execute IS server package because of error 0x80131904. Server: "my server name", Package path: "my package path.dtsx", Environment reference Id: NULL.  Description: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  Source: .Net SqlClient Data Provider  Started:  00:01:01  Finished: 00:01:31  Elapsed:  30.61 seconds.  The package execution failed.  The step failed.

Usually my job finishes successfully but this keeps happening from time to time.

I am using SQL Server agent.

On my package all timeouts are set to 0 (no timeout). Server remote timeout is set to 600.

No jobs are running at the time I want to start this one except SSIS Server Maintenance Job (same situation on failures and successful job finishes).

There are some similar questions but most of them are not answered and the rest failed to help me.

Thank You in advance!


texts:
I am using MySQL database and using Ubuntu linux machine.

I noticed that my database, db_test, is under the path /var/lib/mysql/db_test

There are files with the suffix .frm, .MYD, .MYI like the following:

/var/lib/mysql/db_test# ls

cars.frm
cars.MYD
cars.MYI

customers.frm
customers.MYD
customers.MYI

departments.frm
departments.MYD
departments.MYI

...

Seems each .frm, .MYD, .MYI files group mapped with one table in the database.

I have following two questions to ask:

What are the three files doing exactly?
If I create a new directory under path /var/lib/mysql/ say db_test_2 , and copy every file from db_test_1 directory to db_test_2 , will it also create a new database db_test_2 which has exactly the same contents(tables) as db_test_1's ?

Does this physically database files moving action create the same result as following command-line actions:

dump the database db_test_1 out
create a new database db_test_2
then dump the db_test_1 database back into the new database db_test_2?

If so, it seems moving files are much faster then using mysqldump to copy databases. Any opinions on this?


texts:
I am creating a database system using MySQL for a group in my school (a University). I am new to MySQL and its usage.I have started with the data modelling process, since this process will be the main foundation on which others will be built, It is necessary for me to know if I am heading towards the right direction.

Below is the business rule from the group.

1. They need a database that will store their members information. The
information should include Name and Level (thus their level of
study,we have level 100,200,300 and 400).

2. The database must store information about their executives. The information should
include the names of president, secretary and
treasurer for every year.

3. A member could hold an executive position for one year,He/She can hold one position
for more than one year ( so a member can be the
president for more than one year before he/she hands over to another
person), A member could also hold different executive position for
more than one year (so a member who is the secretary in year 1 can
be the president for year 2.

Below is the data model for the information above.

MEMBERS
--------------
MemID: SMALLINT

Name : Varchar(40)

Level: enum(100,200,300,400)

POSITIONS
----------------------
PosID: SMALLINT

PosName: Varchar(20)

EXECUTIVES
-----------------
MemID: SMALLINT-FK1

PosID: SMALLINT-FK2

AcademicYear: Year


texts: Creating SQL Assembly error Method parent has circular class type parameter constraints

texts:
I'm trying to add an assembly to SQL server 2012 but I'm getting the following errors. I've tested the dll and it works fine from C# so don't think it's a code issue.  Any suggestions?

Msg 6218, Level 16, State 2, Line 6
CREATE ASSEMBLY for assembly 'HMDACalculateULICheckDigit' failed because assembly 'HMDACalculateULICheckDigit' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : CalculateULICheckDigitCLR::.ctor][mdToken=0x6000002][offset 0x00000000] Method parent has circular class type parameter constraints.
[ : CalculateULICheckDigitCLR::CalculateULICheckDigitHMDA][mdToken=0x6000001][offset 0x00000000] Method parent has circular class type parameter constraints.

The C# itself is pretty simple.

public class CalculateULICheckDigitCLR
{
public static int CalculateULICheckDigitHMDA(string val)
{
    int result;

    result =  (int)(BigInteger.Parse(val) % 97);
    return result;
}
}

Example of value I'm passing in 548322619182814292318016211461767828260500


texts: Is it necessary to disable strict name checking in Microsoft SQL 2014?

texts:
I want to connect to a new Microsoft SQL 2014 server using CNAME records. I have not yet followed any of the steps to disable strict name checking, such as editing the registry or adding an SPN. However, I am not having any issues connecting to the server using CNAME aliases I have created, using either Windows or SQL authentication.

Is it still necessary to specifically disable strict name checking to avoid some unforeseen issues later on? Is the fact that I am already able to connect to this server using an alias due to a difference between SQL 2014 and earlier versions?

The server is running Microsoft SQL Server 2014 Enterprise on Windows Server 2012 R2 Standard. Before it goes into production, it will become a node in an AlwaysOn failover cluster, so the CNAME records will actually be pointing at the cluster Virtual Network Name, not the server's host name, if this makes any difference?


texts: How to do data migration between different schemas?

texts:
My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data from the source (third party database), transforms it into our schema, and inserts into the target (our application database). Unfortunately there are multiple issues with this method:

Inefficient. Our current customer has a huge amount of data and the total time for migrating the data is nearly 6 days of processing time.
Customer Downtime. The data is only is current up to the time we begin the migration. This means the customer must cease use of their current system during this time, leaving us a limited amount of time to perform the migration, with little room for error.
Error prone. The customer's data is full of inconsistencies that need handling. We also mostly rewrite this application to fit each customer, as there is little overlap in the systems they use. This requires large amounts of developer and QA time.

This must be a fairly common problem, as companies often move between applications and need to migrate their current data. Some possible solutions I am looking into are:

Oracle Data Integrator.
SQL Server Integration Services
Informatica.

Our own web application is designed to use either Oracle or SQL Server, so I am open to using products used with both Oracle and SQL Server databases.

Is there a solution that would allow continuous replication of a customer's source data into our target database while transforming the data into our schema?


texts: How to get latest date from fees table and compare it to current date in mysql

texts:
I have two tables members and fees ,fees table has a foreign key memberid which is a primary key in members .
I want to get those members who have not yet paid. When a member pays there are two fields fromdate and todate and both they are datetime.
So those fields helps us to know that the member paid from which date to which date .
So I want to pull out those members whose todate is older than the current date , remember since they pay monthly I want to pull out only
one latest todate  for each member  then I compare it with current date . Any help will be appreciated . bellow is what I have tried .

 select  members.name , fees.todate ,
    fees.usercode
    from members
    INNER JOIN fees
    ON members.memberid  =  fees.memberid
    WHERE
     fees.usercode ='KMTS472895'
    AND
    fees.todate < curdate()
    AND
    fees.todate = (SELECT MAX(fees.todate))
    AND
    todate !='0000-00-00 00:00:00'
    GROUP BY fees.memberid
    ORDER BY fees.memberid

///////////////////////////////////////////

CREATE TABLE `members` (
  `memberid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `companyid` int(11) unsigned DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `initials` varchar(5) NOT NULL,
  `surname` varchar(30) NOT NULL,
  `datejoined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `usercode` varchar(12) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `cell` varchar(15) DEFAULT NULL,
  `cell2` varchar(15) DEFAULT NULL,
  `pic` varchar(100) DEFAULT NULL,
  `idnumber` varchar(30) DEFAULT NULL,
  `rank` varchar(30) DEFAULT NULL,
  `gender` varchar(12) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address2` varchar(50) DEFAULT NULL,
  `branch` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `Company_name` varchar(100) DEFAULT NULL,
  `website` varchar(100) DEFAULT NULL,
  `Comments` varchar(500) DEFAULT NULL,
  `Company_reg_number` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `next_of_kin` varchar(100) DEFAULT NULL,
  `next_of_kin_number` varchar(100) DEFAULT NULL,
  `patient_code` varchar(50) DEFAULT NULL,
  `medNumber` varchar(100) DEFAULT NULL,
  `medPlan` varbinary(100) DEFAULT NULL,
  PRIMARY KEY (`memberid`),
  KEY `companyid` (`companyid`),
  CONSTRAINT `members_ibfk_1` FOREIGN KEY (`companyid`) REFERENCES `company` (`companyid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;

///////////////////////////////////////////////////

CREATE TABLE IF NOT EXISTS `fees` (
  `feesid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feestype` varchar(50) NOT NULL,
  `amountpaid` decimal(10,2) DEFAULT NULL,
  `memberid` int(11) unsigned DEFAULT NULL,
  `companyid` int(11) unsigned DEFAULT NULL,
  `paidfor` varchar(100) NOT NULL,
  `paiddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fromdate` datetime NOT NULL,
  `todate` datetime NOT NULL,
  `usercode` varchar(12) NOT NULL,
  `paidby` varchar(30) NOT NULL,
  `recieved_by` varchar(30) NOT NULL,
  `qty` int(5) NOT NULL,
  PRIMARY KEY (`feesid`),
  KEY `memberid` (`memberid`),
  KEY `companyid` (`companyid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1595 ;