2

I'm running my database on a Sybase ASE 15.7 server.
A simple delete statement fails without printing any error , I presume there is no permissions problem as I used to delete rows by the past. Here is what I did from an isql console.

1> select count(*) from LOGIN where login="336959686@login.com"
2> go

 -----------
       1

(1 row affected)
1> delete from LOGIN where login="336959686@login.com"
2> go
(0 rows affected)

The row wasn't deleted, am I missing something?

LOGIN table definition :

1> sp_help LOGIN
2> go
 Name  Owner Object_type Object_status Create_date
 ----- ----- ----------- ------------- -------------------
 LOGIN xxx   user table   -- none --   Dec  7 2010  2:13PM

(1 row affected)
 Column_name            Type     Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ---------------------- -------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ----------
 login_oidval           numeric       8   16     0     0              0 NULL         NULL      NULL             NULL                            0
 login                  varchar     165 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 creation_date          datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 last_modification_date datetime      8 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
 idp_oidval             smallint      2 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0
Object has the following indexes

 index_name index_keys         index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local
 ---------- ------------------ -------------------- ----------------------- ---------------- -------------------- ------------------- ------------
 PK_LOGIN    login_oidval      nonclustered, unique                       0                0                    0 Dec 10 2010  7:10PM Global Index
 login_idx1  login, idp_oidval nonclustered, unique                       0                0                    0 Jun 10 2011  2:55PM Global Index

(2 rows affected)
 index_ptn_name       index_ptn_seg
 -------------------- -------------
 PK_LOGIN_448001596   default
 login_idx1_448001596 default

(2 rows affected)
No defined keys for this object.
 name  type       partition_type partitions partition_keys
 ----- ---------- -------------- ---------- --------------
 LOGIN base table roundrobin              1 NULL

(1 row affected)

 partition_name  partition_id compression_level pages row_count segment create_date
 --------------- ------------ ----------------- ----- --------- ------- -------------------
 LOGIN_448001596    448001596 none                863     25497 default Feb 12 2014  3:01PM

 Partition_Conditions
 --------------------
 NULL

 Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)              Ratio(Min/Avg)
 ----------- ----------- ----------- --------------------------- ---------------------------
         863         863         863                    1.000000                    1.000000
Table LOB compression level
Lock scheme Datarows
The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

 exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts
 ------------ -------------- ---------- ----------------- ------------ -----------
            0              0          0                 0            0           0

(1 row affected)
 concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
 ------------------------- --------------------- -------------------
                        15                     0                   0
(return status = 0)
  • Isn't there any dependency on LOGIN table? FK? – McNets Apr 13 '17 at 13:50
  • No, there is no dependencies violation, I used to get an error message in that case .. very strange ! – storm Apr 13 '17 at 13:57
  • 2
    Show us the table definition. Does the table have any triggers? – ypercubeᵀᴹ Apr 13 '17 at 13:58
  • ^Details added to the question – storm Apr 13 '17 at 14:40
  • 1
    Does select * from LOGIN where login = '336959686@login.com'; return 1 row? – ypercubeᵀᴹ Apr 13 '17 at 15:20
4

Instead of using double-quotes, try using single-quotes:

DELETE 
FROM LOGIN 
WHERE login = '336959686@login.com';

If the delete statement produces no error but doesn't appear to delete the row affected, that means one of the following:

  1. The criteria (the WHERE clause) is not matching the desired row.
  2. You have a transaction that is rolling back the delete, or has not yet committed the delete. Check the transaction chaining mode using SELECT @@tranchained; Perhaps try this:

    SELECT *
    FROM LOGIN 
    WHERE login = '336959686@login.com';
    BEGIN TRANSACTION
    DELETE 
    FROM LOGIN 
    WHERE login = '336959686@login.com';
    COMMIT TRANSACTION
    SELECT *
    FROM LOGIN 
    WHERE login = '336959686@login.com';
    
  3. Undetected database corruption - this is extremely rare. Have you performed DBCC CHECKDB lately? Make certain you have a reliable backup. Test the backup by restoring it onto another instance of Sybase ASE. If you have done that, running DBCC CHECKDB should be performed. It may heavily impact I/O, so I would run it during non-peak hours.

  • 1. The clause is matching when SELECT , I can't see why it does not match for a DELETE 2. The row is still there 3. No I've never done it before , does I need to perform a check , is it safe ? – storm Apr 13 '17 at 14:14
  • sp_help won't show if there is a trigger on the table. Run 'set triggers off' first to ensure no trigger can kick in. I don't think the issue is transactional however: if it was, then DELETE would show @@rowcount = 1 but the row would later appear not to be deleted; however, we see @@rowcount = 0 so it does not get deleted in the first place. – RobV Apr 14 '17 at 11:00
  • It would be helpful if you could post the query plan and I/O statistics from both the SELECT and the DELETE. – RobV Apr 14 '17 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.